質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.09%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 2
  • VIEW 3,690

mayoi_maimai

score 1575

いつもお世話になります。
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                        |
+-------------------------+------------------------------+
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+3

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

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)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/09 11:37

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

    キャンセル

+3

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/09 10:51

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

    キャンセル

  • 2017/06/09 11:39

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

    キャンセル

+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;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/09 11:38

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.09%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る