SELECTクエリの内部動作

質問

以下のテーブルがあるとします
table test (
　id integer primary key, // ID
　sex integer, // 性別(1:男, 2:女, 3:その他)
　time integer, // 登録時間(unix time)
　nation text // 国籍
　name text, // 名前
　index (id),
　index (sex),
　index (time),
　index (group(63)),
　index (name(63))
)

このテーブルに対し、
(A) SELECT * FROM test WHERE sex=2 AND nation='Japan' ORDER BY time
(B) SELECT * FROM test WHERE sex=2 AND nation='Japan' ORDER BY time, id

というクエリを出した際にMySQLの内部でどのように該当行を取り出しているのでしょうか？
そのメカニズムに興味があります。
A, Bどちらかでもかまいません。

例えばAに関して（間違っているかもしれません）
1) timeインデックスのソート済み順序を下に、テーブル順にレコードを取り出していく
2) もし、sex=2であり nation='Japan' であれば、その行を出力
といったアルゴリズム的な部分を知りたいです。

MySQLのドキュメントでそのことについて詳しく触れている箇所や、そのようなアルゴリズム自体をご存知の方、アドバイスをお願いします。

回答No.4

chukenkenkou · ベストアンサー

#2、#3回答者です。

提示された表定義に誤りがあり、私の方でも見落としがあり、少し勘違いしました。

「index (group(63))」とありますが、「index (nation(63))」の誤りですね？
「//」でのコメントも、MySQLでは許されません。「--　」か「# 」で、コメント指定できます。

まず、検索条件やソート指定する列は、textはやめて、varchar(n)にしましょう。「nation(63)」といった指定をしても、MySQLは有効にインデクスを利用してくれません。
→nは、必要最小限のなるべく短い値にする。

そして、10行程度、テスト用のデータを格納しましょう。

インデクスの定義は、次の3パターンで(A)、(B)のクエリそれぞれでexplainの結果を見てみましょう。

＜パターン1＞
index(sex)
index(nation)
index(time)

＜パターン2＞
index(sex,nation,time)

＜パターン3＞
index(sex,nation,time,id)

explainの結果の確認のポイントは、検索条件でのインデクス使用とともに「filesortが発生しているかどうか」です。
「filesort」が発生していると、インデクス利用によるソート抑止ができていないことを意味します。
上記のパターンで、どういった場合、ソート抑止できているか確認してみてください。

また、マニュアルにも、インデクスを有効利用するための記載がありますので、下記URLを参照してみてください。

※id列は、primary keyを指定しているので、indexを指定して単一列のインデクスを定義する意味はありません。

参考URL：

http://dev.mysql.com/doc/refman/4.1/ja/query-speed.html

お礼

ありがとうございました。

回答No.3

chukenkenkou

#2回答者です。

オプティマイザの話をするなら、textといった長さが不定、または相当に大きくなるデータ型を指定する発想はやめましょう。

また、条件式で絞り込んだ結果を、さらにソートする場合に、「order by」の話が出てきます。

つまり、今回の質問内容では、

(1)(sex,nation,time)または(nation,sex,time)
(2)(sex,nation,time,id)または(nation,sex,time,id)

のインデクスを定義していることが、オプティマイザの話をする場合の前提条件です。

そうでないなら、こういった質問には、まったく意味がありません。

回答No.2

chukenkenkou

あるRDBMSの専門知識を持ち、複数のRDBMSについても多少の知識を持っている者です。

ソート指定（order by）は、検索結果の表示順に関する指定ですから、一番最後に評価されます。

最初に評価されるのは、検索条件です。
今回、提示された条件では、「id」列に対して「primary key」を指定していますが、条件指定している列や「order by」を指定している列にはインデクスがないので、まったく「インデクスによる絞込み」や「インデクスによるソート抑止」ができない指定になっています。

したがって、質問にあるクエリの(A)、(B)は、どちらも性能を出せない定義＆操作になっており、比較する意味がありません。

また、データ型が「text」といったものを使用していますが、多くのRDBMSでは、インデクスを定義でき、性能を出せる文字型は、「varcharで255バイトまで」といった制限を付けています。
「text」、「long varchar」、「lob」といったデータ型は、相当に長い文字データ、事前に長さを予測できなデータを格納するためのものです。それでさらに「条件を絞り込んで性能を出す」というのは、現状のRDBMSでは、実現不可能なことです。

回答No.1

Tasuke22

Sourceを読むのが一番でしょう。
参考URLでSourceの入手方法が分かると思います。

参考URL：

http://dev.mysql.com/doc/refman/4.1/ja/windows-source-build.html

お礼

ご回答ありがとうございます。
ソースコードをすらすら読めるスキルがないためか、MySQLのソースコードは量が半端なかったので、最初は比較的小さなBerkeleyDB Java Editionやsqlite等のソースを読もうと試みましたがやはり挫折しました。

そこで大まかな流れだけでも知りたく、質問をした次第です。
IEEE Explorerで論文検索も行っているのですが、データベース初級者のため何をキーワードに検索すればよいかもよくわからずです。