アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < 転職してフロントエンド勉強中 あとFE聖戦1周クリアしてトラキアやりながらSQX待ち

結果サイズによるインデックスの効き具合

MySQLInnoDBにて。

以下のようなテーブル「test」があるとします。

カラム名 インデックス
id int(11) PRIMARY
ymd date ymd

ymdにはインデックスが張ってあります。
また、レコードは約1万8千件。

これに対して、以下4つのクエリのEXPLAINを比較してみました。

  • クエリ1
SELECT *  FROM `test`  WHERE `ymd` <= CURDATE();
  • クエリ2
SELECT *  FROM `test`  WHERE `ymd` = CURDATE();
  • クエリ3
SELECT *  FROM `test`  WHERE `ymd` >= CURDATE();
  • クエリ4
SELECT *  FROM `test`  WHERE `ymd` <= CURDATE()  LIMIT  5;

WHERE句の比較演算子やLIMITのみの、微妙な違いですね。
そしてEXPLAINしてみた結果がこちら。

  • EXPLAIN1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test ALL ymd NULL NULL NULL 16567 Using where
  • EXPLAIN2
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test ref ymd ymd 3 const 1 NULL
  • EXPLAIN3
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test range ymd ymd 3 NULL 187 Using index condition
  • EXPLAIN4
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test range ymd ymd 3 NULL 8283 Using index condition


2~4は期待通りの結果ですが、1だけインデックスが使われていません。
しかも「ymdってインデックスが使えそうだけど…」までは考えてくれるのに、使ってくれません。

調べてみたところ、どうやらインデックスの張られているカラムであっても
それで絞り込んだレコード数が多すぎる場合
(正確に言うと、必要な領域がメモリ上で確保できずに
物理ファイルに書き出しを行う必要がある場合)
には、インデックスが使われないようです。
なので、4のようにLIMITで絞り込む場合は問題ないのですね。


…という前提をもとに、
今回、このことに気付いた原因のクエリをチューニングしたかったのですが…

SELECT     SQL_CALC_FOUND_ROWS *
FROM       `test`
INNER JOIN `test_a` ON ...
LEFT JOIN  `test_b` ON ...
WHERE      `ymd` <= CURDATE()
ORDER BY   `ymd` DESC
LIMIT      5;

こんな感じ。
色々試してみた結果、例えば「SQL_CALC_FOUND_ROWS」や「ORDER BY `ymd` DESC」を除くとインデックスを使ってくれます。(いや、除くわけにはいかないんですけど)
「LIMITしない場合の件数をカウントする」とか「(件数が多い状態で)ソートする」というのがメモリ上ではできないんでしょうね…。
JOIN部分を2つとも消した場合は変わらず。件数が減るわけではないからでしょうかね。
機能を完全に維持したままの改善は難しそうなので諦めました。
今のところ、参照される頻度が高い割には「ごくまれに」スロークエリログに出てくる、というレベルで済んでいるところなので見なかったことにします。

参考記事
MySQLのIndexをはるコツ - Qiita
漢(オトコ)のコンピュータ道: Using filesort
SQL_CALC_FOUND_ROWSを使うとインデックスが使われない? | 創作メモ帳