MySQLのインデックスが効いていない？

Question

いつもお世話になります。
MySQLのインデックスについて不明な点があったのでご質問させていただきます。

以下のようなテーブル内にMAIN_CATEGORY_ID：10000のデータが4件、MAIN_CATEGORY_ID：10004のデータが1件あります。

MAIN_CATEGORY_ID：10000のデータを取得しようとしたのですが、Explainの結果がALLになってしまいました。
尚、MAIN_CATEGORY_ID：10000のデータを取得した時は効いているようでした。

クエリ、もしくはインデックスの設定におかしいところがありましたらご指摘いただけると幸いです。。

###テーブル

CREATE TABLE `SUPPORT_MST` (
  `ID` int(11) NOT NULL,
  `MAIN_CATEGORY_ID` int(11) NOT NULL,
  `SUB_CATEGORY_ID` int(11) NOT NULL,
  `SUPPORT_CONTENT` text,
  `TITLE` text NOT NULL,
  `BODY` text NOT NULL,
  `REMARKS` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `SUPPORT_MST`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `SUB_CATEGORY_ID` (`SUB_CATEGORY_ID`),
  ADD KEY `MAIN_CATEGORY_ID` (`MAIN_CATEGORY_ID`);

###Explain（MAIN_CATEGORY_ID：10000のデータ取得）

mysql> EXPLAIN SELECT * FROM SUPPORT_MST WHERE MAIN_CATEGORY_ID = 10000;
+----+-------------+-------------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | SUPPORT_MST | ALL  | MAIN_CATEGORY_ID | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

###Explain（MAIN_CATEGORY_ID：10004のデータ取得）

mysql> EXPLAIN SELECT * FROM SUPPORT_MST WHERE MAIN_CATEGORY_ID = 10004;                                                                                                                                            
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table       | type | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | SUPPORT_MST | ref  | MAIN_CATEGORY_ID | MAIN_CATEGORY_ID | 4       | const |    1 | NULL  |
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

###バージョン

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.36-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

Answer 1

テーブルのレコード数が少ないからでしょう。
データのばらつきも含めて本番想定のデータを用意して検証しないと効果が薄いです。

フルテーブルスキャンを回避する方法

MySQL がフルテーブルスキャンを使用してクエリーを解決する場合、EXPLAIN からの出力には type カラムに ALL と示されます。これは通常は次の条件で発生します。

テーブルがきわめて小さいため、キールックアップで煩わされるよりもテーブルスキャンを実行する方が速くなります。これは、10 行未満の行や短い行長のテーブルによくあります。

Comments

[momon-ga]

あと、見た感じでは、特にインデックスの設定とかに不備はなさそうなので、
データを増やして検証してみてください。

[mayoi_maimai]

早急なご回答と詳しいご説明ありがとうございました！

Answer 2

おそらくですが、インデックスを使うよりフルスキャンして不要なデータを捨てたほうが早いと判断されているようです。

5件中4件のデータを引くためにインデックスを使うと

1. インデックスでレコードを特定する
2. レコードの実データを4件読みこむ（ランダムアクセス）

と、1+4 の5回 I/O が発生します。
一方フルスキャンの場合はシーケンシャルアクセスで全レコードを読むので、I/O は2回（インデックス取得1回＋全レコード読み込み1回）です。

このI/O回数の比較でフルスキャンを選択したのではないかと。

試しにレコードの実データを読む必要がない SELECT だと、きちんと MAIN_CATEGORY_ID をキーにしてインデックスを使いますね。

mysql> explain select * from support_mst where main_category_id = 10000;
+----+-------------+-------------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | support_mst | ALL  | MAIN_CATEGORY_ID | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id from support_mst where main_category_id = 10000;
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table       | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | support_mst | ref  | MAIN_CATEGORY_ID | MAIN_CATEGORY_ID | 4       | const |    4 | Using index |
+----+-------------+-------------+------+------------------+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Comments

[mayoi_maimai]

早速のご回答と詳しいご説明ありがとうございました！

Answer 3

MAIN_CATEGORY_IDはuniqueではなくindexですよね？
ということはメインIDに複数のデータが存在する可能性がありconstにヒットすることは
ないのでインデックスの効果もさほど期待できません
とくにselect *をするかぎり、複合インデックスなどの恩恵も得られません
データ量がすくなければインデックスを利用しないほうが速い場合もあるので
そんなに気にすることもないでしょう。

どうしてもindexを参照したのであれば強制的に使用する手もあります

EXPLAIN SELECT * FROM SUPPORT_MST force index(MAIN_CATEGORY_ID) WHERE MAIN_CATEGORY_ID = 10000;

また、IDとメインカテゴリに複合インデックスをはって、その2項目だけ抽出するなら効果は高くなります

ALTER TABLE `SUPPORT_MST` ADD INDEX `ID_MAIN` (`ID`,`MAIN_CATEGORY_ID`);

EXPLAIN SELECT ID,MAIN_CATEGORY_ID FROM SUPPORT_MST force index(`ID_MAIN`) WHERE MAIN_CATEGORY_ID = 10000;

Comments

[mayoi_maimai]

ご回答ありがとうございました！
また、強制的にインデックス使用する方法があることは知りませんでした。。
色々と勉強になりましたm(__)m