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

Question

ion10

87

87もっと見る

100pt

コンピュータウェブ制作

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

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ion10 2009/03/03 14:49:42

投稿時に文字数制限にひっかかり、何も考えずスペース抜いてしまいました。
少しは必要なとこを追加したつもりでしたが、全然駄目でした。
読みにくくてスイマセン。
ion10 2009/03/03 16:09:22

改めて考えてみると、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は利用しないよ！﹂
と判断したということなのかな？と理解しました。

このような場合、どのようにするのが王道なのでしょうか？
そもそも重複を取り除いたテーブルを用意する事自体が間違い？
chuken_kenkou 2009/03/03 16:39:30

>これにより、MySQLは
>﹁データが分散しまくっている︵Cardinalityが大きい︶から、INDEXは利用しないよ！﹂
>と判断したということなのかな？と理解しました。

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

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

インデクスを定義する目的は、何かを考えてみましょう。

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

といったものです。

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

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

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

▼解決方法
クエリーを分割し、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倍以上早くなりました。

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

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

リクエスト送信済

回答リクエストを送信したユーザーはいません

chuken_kenkou · Accepted Answer · 2009-03-03T15:45:41+09:00

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を使用して、クエリを最適化する

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

ベストアンサー

chuken_kenkou722542009/03/03 15:45:41

コメント（5件)

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