• ベストアンサー

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

  • MySQL
  • 回答数4
  • ありがとう数2

質問者が選んだベストアンサー

  • ベストアンサー
回答No.4

#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
__LINE__
質問者

お礼

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

その他の回答 (3)

回答No.3

#2回答者です。 オプティマイザの話をするなら、textといった長さが不定、または相当に大きくなるデータ型を指定する発想はやめましょう。 また、条件式で絞り込んだ結果を、さらにソートする場合に、「order by」の話が出てきます。 つまり、今回の質問内容では、 (1)(sex,nation,time)または(nation,sex,time) (2)(sex,nation,time,id)または(nation,sex,time,id) のインデクスを定義していることが、オプティマイザの話をする場合の前提条件です。 そうでないなら、こういった質問には、まったく意味がありません。

回答No.2

あるRDBMSの専門知識を持ち、複数のRDBMSについても多少の知識を持っている者です。 ソート指定（order by）は、検索結果の表示順に関する指定ですから、一番最後に評価されます。 最初に評価されるのは、検索条件です。 今回、提示された条件では、「id」列に対して「primary key」を指定していますが、条件指定している列や「order by」を指定している列にはインデクスがないので、まったく「インデクスによる絞込み」や「インデクスによるソート抑止」ができない指定になっています。 したがって、質問にあるクエリの(A)、(B)は、どちらも性能を出せない定義＆操作になっており、比較する意味がありません。 また、データ型が「text」といったものを使用していますが、多くのRDBMSでは、インデクスを定義でき、性能を出せる文字型は、「varcharで255バイトまで」といった制限を付けています。 「text」、「long varchar」、「lob」といったデータ型は、相当に長い文字データ、事前に長さを予測できなデータを格納するためのものです。それでさらに「条件を絞り込んで性能を出す」というのは、現状のRDBMSでは、実現不可能なことです。

  • Tasuke22
  • ベストアンサー率33% (1799/5383)
回答No.1

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

参考URL：
http://dev.mysql.com/doc/refman/4.1/ja/windows-source-build.html
__LINE__
質問者

お礼

ご回答ありがとうございます。 ソースコードをすらすら読めるスキルがないためか、MySQLのソースコードは量が半端なかったので、最初は比較的小さなBerkeleyDB Java Editionやsqlite等のソースを読もうと試みましたがやはり挫折しました。 そこで大まかな流れだけでも知りたく、質問をした次第です。 IEEE Explorerで論文検索も行っているのですが、データベース初級者のため何をキーワードに検索すればよいかもよくわからずです。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. MySQL

関連するQ&A

  • 取り出す行数とオフセット指定

    table test ( 　id integer primary key, 　status integer, 　name text ) というテーブルがあります。ここでidは連続とは限りません。 これから 　SELECT * FROM test WHERE status=1 ORDER BY id で取り出される 　行のうち、100番目から300個分だけ取り出したい というクエリをしたいのですが可能でしょうか？ 基本的な質問かと思いますがよろしくお願いします。

    • ベストアンサー
    • MySQL
  • ある条件を含まないというクエリがうまく書けません。

    ある条件を含まないというクエリがうまく書けません。 test_table ID,name ----------- 1,aaa 2,bbb 3,ccc 4,ddd 5,eee test_table2 ID,value ----------- 1,100 1,200 1,300 1,400 2,100 2,200 3,900 3,800 4,400 4,500 4,600 5,100 今まで下記クエリのように、test_tableのIDとtest_table2のIDでジョインして、 test_table2のvalueに"200"を含んでいるtest_tableのレコードを取得していました。 SELECT DISTINCT test_table.* LEFT JOIN test_table2 ON test_table.ID = test_table2.ID WHERE test_table2.value = 200; （test_tableのID:1と2のレコードが返ってきます。） これを、test_table2のvalueに"200"を含まないものを返すようにしたいのです。 （test_tableのID:3と4と5のレコードを返したい） 下記クエリを作ってみましたが、ID:1と2も返ってきてしまいます。 ID:1のvalueの"100","300","400"、ID:2のvalueの"100"に条件が合ってしまうようです。。 SELECT DISTINCT test_table.* LEFT JOIN test_table2 ON test_table.ID = test_table2.ID WHERE test_table2.value != 200; なにが良い方法があればご教授下さい。

    • ベストアンサー
    • MySQL
  • 【ＳＥＬＥＣＴ文】

    baseテーブル winテーブル 041110_1　　　041110_1 041110_2　　　041110_2 041110_3　　　041110_3 041211_1 041211_2 041211_3 上記のようなテーブルがあり、表示させたいことは それぞれを前から７つめまでの文字をグループ化し、 winテーブルにあるものは表示させず、baseテーブルにあるもののみを表示させたいのですが、下記のＳＱＬではうまくいきません。 どのようにしたら、うまくいくのか教えてください。 表示させたい形 041211 select substr(b.id,0,7) as id from base as b, win as w where 1=1 and substr(w.id,0,7) <> substr(b.id,0,7) group by substr(b.id,0,7) order by substr(b.id,0,7) desc;

  • 複数のindexについて

    CREATE TABLE `test` ( `id` INT(8) , `num` INT(8) , 他多数 (省略) ) このようなテーブルがある場合、以下の２種類のインデックスのつけ方にどのような違いがあるのでしょうか？ phpmyadminで確認すると、(1)はインデックスが合体しており、(2)はインデックスが個別に分かれています。 (1)　ALTER TABLE `test` ADD INDEX ( `id` , `num` ) (2)　ALTER TABLE `test` ADD INDEX ( `id` ) 　　ALTER TABLE `test` ADD INDEX ( `num` ) ちなみに、以下のようなSELECT文を用いる場合には、どちらのインデックスが適していますか？ SELECT * FROM test WHERE id='●' and num > '△' ( mysql5,MyISAM )

    • ベストアンサー
    • MySQL
  • クエリの遅さの原因

    下記のクエリーをそれぞれ試してみたところ、圧倒的に下の方が遅くなってしまいました。 $rs = mysql_query("select * from A INNER JOIN B ON B.cat = A.id order by B.id desc LIMIT 1, 10 ;",$con); $rs = mysql_query("select * from A INNER JOIN B ON B.cat = A.id where B.name is not null group by B.area order by B.id desc LIMIT 1, 10 ;",$con); where B.name is not null group by B.area　この処理はそれほど負荷が掛かってしまうのでしょうか。 他に良い書き方（方法）がありましたら教えてください。

    • ベストアンサー
    • MySQL
  • SQLによって計算した変数を次のSQLに代入できますか？

    user(テーブル) id | name | bango 1 | taro | 1001 2 | sato | 1012 3 | miho | 1027 4 | hiro | 1066 *idはautoincrement、bangoはユニークの値 mark(テーブル) id | check | bango 1 | 0 | 1001 2 | 1 | 1001 3 | 1 | 1001 4 | 0 | 1012 5 | 0 | 1012 6 | 1 | 1027 7 | 1 | 1027 8 | 0 | 1066 *idはautoincrement、checkは1か0、bangoはユニークの値 checkが1の確立が高い順にnameを一覧表示したいのですが、 うまく2つのテーブルを繋げることができません。 queryによって計算した変数を次のqueryに代入することはできるのでしょうか？ 具体的には以下のような感じです。 bangoが1001のcheck=1の確立を出す場合 //bangoが1001の数 $test1 = mysql_query("select count(id) from mark where bango='1001';",$conn $row1 = mysql_fetch_array($test1, MYSQL_ASSOC); $totalct1 = $row1["count(id)"]; //bangoが1001かつcheckが1の数 $test2 = mysql_query("select count(id) from mark where bango='1001' and check='1' ;",$conn) $row2 = mysql_fetch_array($test2, MYSQL_ASSOC); $totalct2 = $row2["count(id)"]; //bangoが1001かつcheckが1の確立 if($totalct2==0){ $kakuritu = '0' ; } else{ $kakuritu = $totalct2 / $totalct1 * 100 ; } 上記の変数を下記のように入れ込むことはできないのでしょうか？ $test3 = mysql_query("select name from user order by $kakuritu ;",$conn)

    • ベストアンサー
    • MySQL
  • mySQLの内部結合について

    現在mySQLを使って、SQL文の勉強をしているのですがわからないことがあり、質問させていただきました。 テーブル同士をINNER JOINして結果を取得して、これに対してさらに別の処理（Whereなど)を行いたいのですが、うまくやり方がわかりません。 具体的にSQL文でいうとこんな感じです（ただしエラーが出て動きません）。 SELECT cmaster.comic_id,tags,title FROM mysql.comic_tag as ctag INNER JOIN mysql.comicmaster as cmaster ON ctag.comic_id = cmaster.comic_id AND WHERE tags = 'aa' この処理でやりたいことを説明するとcomic_tagとcomicmasterを結合して、その結果に対してさらにWhereでデータを絞るということしています。 このような場合はどのように処理をしたらいいんでしょうか？またjoinした後のデータを、既存のテーブルのように扱いたいのですが、なにか方法はないのでしょうか？

    • ベストアンサー
    • MySQL
  • 複数のtable

    mysqlとperlを勉強中です 2つのテーブルから同じIDの情報を取得したいです。 hoge1テーブルとhoge2テーブルのIDは共通で一致しています。 （実際にはフィールドはもっと多いですがわかりやすくしています） hoge1テーブルは3つフィールドがあります ID　INT1　INT2 hoge2テーブルは3つフィールドがあります ID　TEXT1　TEXT2 hoge1テーブルで取得するのは数字が一致したIDです SELECT id FROM hoge1 WHERE int1=10 or int2=10 ORDER BY id hoge2テーブルで取得したいのはhoge1テーブルで取得したIDのTEXTです。やりたいことはこんな感じです SELECT TEXT1,TEXT2 FROM hoge2 WHERE id=hoge1テーブルで取得したID ORDER BY id; while (my $rec = $sth->fetchrow_array) { push(@recs, $rec); } perlでは配列で取得して後で、foreachで100個ほどprintしています。 hoge2も同じように取得したいのですが、hoge2はフォームを空白で送信できるようになっているので値がなにもないときがあります 配列に入れてしまうと空白のレコードがあるとその分、配列がずれてしまいます hoge1テーブルで取得したIDとhoge2テーブルで取得したテキストのIDを一致させる方法はありますか？ やりたいのはこうゆうことです。 テーブル1で値が一致しているIDを取得する テーブル2でテーブル1で一致したIDのテキストを取得する リレーションも考えましたが素人なのでピンときませんでした テキストのほうも配列で取得して100個ほどprintしたいのです よろしくお願いします

    • ベストアンサー
    • MySQL
  • SQLの結果が返ってこない

    PHP＋mysqlで以下のようなSQL文で処理を行ったのですがデータが返ってきません。 $sql = 'SELECT * FROM books WHERE id=3'; $recordSet = mysql_query($sql); if(mysql_fetch_assoc($recordSet)){ while ($table = mysql_fetch_assoc($recordSet)) { 　　　　　　　処理 　　　　｝ ｝ mysqlの画面で SELECT * FROM books WHERE id=3 を入力してみるとしっかりと結果が返ってきます。 ちなみに１行目を、違うテーブルの $sql = 'SELECT * FROM podcast WHERE code=3 ORDER BY dcdate DESC LIMIT 0,3'; にしてみると、データが表示されます。 ２日間かけてずっと試行錯誤してみたのですが、完全に行き詰ってしまったので、何か考えられる原因はありませんでしょうか。 よろしくお願いします。

    • ベストアンサー
    • PHP
  • 構文の省略について

    下記をもっと省略（短い構文で）して書きたいのですが、どのようなやり方があるでしょうか。 testというテーブルから指定したidのnameを取り出して並べたいだけなのですが、このような長々としたものしか思い浮かびませんでした。 <?php $n1 = 1; $n2 = 2; $n3 = 3; $rs1 = mysql_query("select * from test where id = '$n1';",$conn); $rec1 = mysql_fetch_array($rs1, MYSQL_ASSOC); echo $rec1['name']; echo <br>; $rs2 = mysql_query("select * from test where id = '$n2';"); $rec2 = mysql_fetch_array($rs2, MYSQL_ASSOC); echo $rec2['name']; echo <br>; $rs3 = mysql_query("select * from test where id = '$n3';"); $rec3 = mysql_fetch_array($rs3, MYSQL_ASSOC); echo $rec3['name']; mysql_free_result($rs1); mysql_free_result($rs2); mysql_free_result($rs3); mysql_close($conn); ?>

    • ベストアンサー
    • PHP

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する