MySQLのINDEXに関する質問です。アドバイスお願いいたします。

以下のテストを実施したところ、Query2ではINDEXが使われない状況となってしまいました。
MySQL4,5ともに同じ状況です。

▼検証対象テーブル
CREATETABLEIFNOTEXISTSitems(
idBIGINT(20)UNSIGNEDNOTNULLauto_increment,
prefecture_idINT(11)UNSIGNEDdefaultNULL,
item_type_idINT(11)UNSIGNEDdefaultNULL,
item_category_idINT(11)UNSIGNEDdefaultNULL,
data_idsLONGBLOBdefaultNULL,
PRIMARYKEY(id)
)ENGINE=MyISAM;

▼作成したINDEX
ALTER TABLE items ADD INDEX index_all_keys (prefecture_id,item_type_id,item_category_id);

▼検証を行ったクエリー
>Query1:全てのキーを条件指定→INDEXを利用
mysql>EXPLAIN SELECT id,data_ids FROM items WHERE prefecture_id IN (10,11,12,15,16,17,19,24,31)ANDitem_type_id=(1)ANDitem_category_idISNULL;

|1|SIMPLE|ITEM|range|index_all_keys|index_all_keys|20|NULL|11|Usingwhere|

>Query2:全てのキーを条件指定ただし、IN演算子で全て複数→INDEXを利用しない!なぜ?
mysql>EXPLAIN SELECT id,data_ids FROM items WHERE prefecture_id IN(1,2,3,4,5,6,7,10,15,20,26,41,46)ANDitem_type_idIN(1,5,6,8,9,10,11)ANDitem_category_idIN(1,2,3,4,5,9,10);

|1|SIMPLE|ITEM|ALL|index_all_keys|NULL|NULL|NULL|13311|Usingwhere|

回答の条件
  • 1人10回まで
  • 登録:2009/03/03 14:17:42
  • 終了:2009/03/03 19:39:42

ベストアンサー

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542009/03/03 15:45:41

ポイント100pt

MySQL4,5ともに同じ状況

MySQLは4.0と4.1では、かなりの機能追加、一部の仕様変更があります。

また、MySQL 5.0と5.1でも、4.0と4.1程ではないにしても、違いがあります。

バージョンは、明記するようにしてください。


2番目のクエリは、絞込みができない(13311件が対象)ため、MySQL側がテーブルスキャンした方が早いと判断されたのだと思います。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.2.1 EXPLAINを使用して、クエリを最適化する

id:ion10

ご回答ありがとうございます。

そして、バージョンの件、失礼しました。

手前の環境は以下のとおりです。

MySQL4 => 4.0.27

MySQL5 => 5.0.67

また、教えて頂いたリファレンスは既知でしたが、改めて読み込んでみました。

どうやら私のケースではINDEXが効かない気もしてきました。

詳しくは、コメントに改めて記載いたしますので、よろしければまたアドバイスお願いいたします。

2009/03/03 15:59:01
  • id:ion10
    投稿時に文字数制限にひっかかり、何も考えずスペース抜いてしまいました。
    少しは必要なとこを追加したつもりでしたが、全然駄目でした。
    読みにくくてスイマセン。
  • id:ion10
    改めて考えてみると、INDEXが効かない構造になっているような気もしてきました。
    何かアイデアあれば、アドバイスお願いします。

    ▼同テーブルに格納されている件数は
    mysql> select count(*) from items;
    +----------+
    | count(*) |
    +----------+
    | 13311 |
    +----------+

    ▼そして同テーブル内のデータは重複除去済み(高速化を目指し対応した ← 中間データ生成処理を事前に実施)
    mysql> select count(*) from items group by prefecture_id,item_type_id,item_category_id;
    +----------+
    | count(*) |
    +----------+
    | 1 |
    .....
    | 1 |
    +----------+
    13311 rows in set (0.09 sec)


    これにより、MySQLは
    「データが分散しまくっている(Cardinalityが大きい)から、INDEXは利用しないよ!」
    と判断したということなのかな?と理解しました。


    このような場合、どのようにするのが王道なのでしょうか?
    そもそも重複を取り除いたテーブルを用意する事自体が間違い?
  • id:chuken_kenkou
    >これにより、MySQLは
    >「データが分散しまくっている(Cardinalityが大きい)から、INDEXは利用しないよ!」
    >と判断したということなのかな?と理解しました。

    検索条件の指定がなかったり、指定があっても全件ヒットするような条件だから、「インデクスを使わず、テーブルスキャンした方が速い」とMySQLにより解釈されているのです。

    つまり、全件検索でなく、一部のデータを検索するような条件であれば、適切なインデクスがあれば有効利用されます。
    また、「ORDER BY」や「GROUP BY」などのソートが必要な操作があった場合、その指定とインデクス定義が一致していた場合、インデクスを活用することで、「作業ファイルや作業メモリを使用したソートを抑止」できます。このソート抑止は、処理時間が半分、1/10になったりと、大きな性能向上に繋がる場合があります。
  • id:chuken_kenkou
    インデクスを定義する目的は、何かを考えてみましょう。

    (1)母体データから、特定のデータ検索のを高速化
    (2)「order by」や「group by」などソートが必要な処理を高速化
    (3)count、sumなどの集計(集合)関数の高速化
    (4)重複チェックの高速化

    といったものです。

    「重複データがない」というのは、母体から特定のデータを検索する場合は、検索条件にもよりますがインデクスを効率的に使えます。
    逆に全件ヒットするような検索では、インデクスからデータを見つけるのは、インデクスを使う分だけオーバーヘッドになります。そのため、そういったケースでは、RDBMS側は「テーブルスキャンした方が速い」と解釈するのです。


  • id:ion10
    細かくご指南ありがとうございます。御陰さまで理解が深まりました。

    さて、ご指南を受けての解決方法ですが、以下のようにいたしました。
    蛇足だとは思いますが、一応ご報告まで。


    ▼解決方法
    クエリーを分割し、INDEXを最大限利用する形に。
    また、これに伴いINDEXも追加。


    ▼具体的なクエリー
    >Query1
    SELECT id FROM items WHERE prefecture_id IN(1,2,3,4,5,6,7,10,15,20,26,41,46);

    >Query2
    SELECT id FROM items WHERE item_type_id IN (1,5,6,8,9,10,11) AND id IN (<Query1の結果>);

    >Query3
    SELECT data_ids FROM items WHERE item_category_id IN (1,2,3,4,5,9,10) AND id IN (<Query2の結果>);


    ▼結果として
    クエリーの処理時間は90%以上短縮
    体感で「テーブルスキャンした場合」と比べ10倍以上早くなりました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません