MySQLに関して、インデックスの機能を確認したいです

Question

###知りたいこと
インデックスについて確認させていただきたいことがございます。

仮にインデックスを次のように３つセットした場合ですが、

CREATE TABLE search_cache (
	ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	service_id TINYINT(1) UNSIGNED NOT NULL,
	q VARCHAR(100),
	color VARCHAR(10),
	updated_at DATETIME(6) NOT NULL,
	PRIMARY KEY  (ID),

	# インデックスをセット
	INDEX idx_search_cache_01(service_id),
	INDEX idx_search_cache_02(q),
	INDEX idx_search_cache_03(color)
);

この場合、次のSELECTでインデックスは効かないのではないでしょうか？

SELECT * 
FROM cache_datas 
WHERE service_id=1 AND q='りんご いちご' AND color='red'

###なぜこう考えたのか
インデックスは
idx_search_cache_01 (service_idが対象カラム)
idx_search_cache_02 (qが対象カラム )
idx_search_cache_03 (colorが対象カラム)
という３つでセットしています。

WHEREの最初にservice_idを指定した場合、その時点でまず、検索対象データはidx_search_cache_01となりますよね。

このidx_search_cache_01に対しさらにAND q='りんご いちご'と指定していますが、idx_search_cache_01のインデックスの中にはqに対するインデックスはありませんから、idx_search_cache_01の中ではqの並び順は不明瞭ですよね。

そうであれば、qに対してセットしたidx_search_cache_02は、上記SELECTのように

一番目にidx_search_cache_01を指定したSELECT
（つまり一番目にWHERE service_id=1と指定したSELECT)

では効かないのではないかと考えたのですが…

###対策
上記の考え方があっていれば、ひょっとして、複合インデックスというのはこのための機能なのでしょうか？

つまりインデックスは3つでなく次のように1つに複合して、
INDEX idx_search_cache_01(service_id,q,color)
こうすることではじめて、上記SELECTでもインデックスが効くという仕組みですか？

###さらなる疑問
上記の考え方と対策が正しい場合ですが、そうすると検索のパターンが複数ある場合、そのパターンだけ複合インデックスを貼るということになるのでしょうか？

たとえばSELECTしたいパターンとして次の【A】と【B】２つがあった場合

# パターン【A】
SELECT * 
FROM search_cache
WHERE service_id=1 AND q='りんご いちご' AND color='red'

# パターン【B】
SELECT * 
FROM search_cache
WHERE service_id=1 AND color='red'

この場合は複合インデックスも次のように貼るということになるのでしょうか？
【A】のためのインデックス → INDEX idx_search_cache_01(service_id,q,color)
【B】のためのインデックス → INDEX idx_search_cache_02(service_id,color)

Answer 1

FROM句の一つのテーブルに対して使用されるインデックスは一つです。
また、インデックスを使用するかどうかは、統計情報を元にオプチマイザーが判断します。

マルチカラムインデックスで項目が全て揃っているからと言って使用されるわけでもありません。
項目の並びが逆の場合に非効率と判断されて使用されない事もあります。

質問の例で使用されないのは、総じて件数が少ないから、全表検索が効率的とオプチマイザーが判断したのでしょう。

検索のパターンが複数ある場合、そのパターンだけ複合インデックスを貼るということになるのでしょうか？

SQLが違うなら、当然適切なインデックスも変わります。
但し、「大は小を兼ねる」事が出来ますので、兼用できるようにインデックスを構成します。
項目の並びは、カーディナリティの低いものから並べるのがコツです。

Comments

[yambejp]

MySQLの場合、使用するインデックスを強制的に指定ができますね
ただおっしゃるとおりオプティマイザが選ぶインデックスが効率的だとは思いますが。

[sazi]

他のDBMSでもインデックスの指定はできますけど、余程でない限り使わないですね。

[tacsheaven]

もう10年以上昔の話ですが、下手にインデックス使う／使わないで処理時間が大きく食い違うのを避けるため、「full scan ヒント句書け」ってのを DBA に言われたことはあります（ｗ　まあ母数が億のオーダーならそうもなりますね（ｗ
※バッチ処理（しかも後続がいる）だったので、処理時間が極端に増減されるよりはマシなのです

[sazi]

遅い方に平準化ですね。
開発技量にムラがあると、DBAとしてはそうも言いたくなるんでしょうね。

[tacsheaven]

開発技量と言うより、システムの（ハード的な部分も含めての）性能限界を前提として、とにかく規定時間に終ることを優先させたらそうなったという感じですね。早く終る分には後続の開始を待つだけですが、遅く終ると後続が遅くなって全処理終了のタイムリミットを超過してしまいかねないので。
※で、なんとか3時間で終らせる目処が立ったら、「後続がどうしても遅いので処理時間を30分繰り上げられない？」とされたという（ｗ

[sazi]

full scanに対応したインデックスを作成しかつfull scanを指定するって事ですか。
その上、後から早くしろって言う(w
そのDBAが手抜きしたとしか思えませんね。

[tacsheaven]

逐次処理の方で必要なインデックスはあるけど、バッチでそれを使うとかえって遅くなるから full scan 固定で、って話でしてね。full scan なら対象のレコード数の想定からだいたい性能が見積もれるので。
でまあ……後から早くするのは DBA のせいというより全体の設計の話で。どうにか頑張って2時間ぐらいまで処理性能を上げて、余裕を作りましたけどね……処理の一部をオンメモリ化するとか苦労させられました。まだオンメモリDBのオプションがない時代だったんで、フルスクラッチですわ（汗

なおそのときに DBA の手が足りないからと言うことで、なぜか自分が（チーム内どころか他チームまで）チェックとチューニングをする羽目になったという苦い思い出が（汗

[sazi]

夜間バッチが1日じゃ終わらないというのは、あるあるですね。
チューニングの際に、試行錯誤で無駄と思える処理が性能に貢献するというのを何度か経験しました。
何れ改善されるだろうから無駄な知識でしかないですけどね。

Answer 2

３カラムにまたいだ検索が前提なら、３カラムを複合インデックスで貼ってください

Comments

[yambejp]

q='りんご いちご'も微妙ですね。
完全一致でその内容がかかれているならOKですが、一部一致など
考えるとデータの羅列は検索しづらいです

[yakan]

＞３カラムにまたいだ検索が前提なら、３カラムを複合インデックスで貼ってください
えーっと、それは「対策」にある通りですが、「なぜこう考えたのか」があっているからという意味ですか？

[yakan]

＞q='りんご いちご'も微妙ですね。
qは検索ワードを保存するカラムで、ソートされているので完全一致でいいかと思っています。

[yambejp]

別に３カラム複合で２カラムしか検索条件に使わなくてもインデックスは有効です

[yakan]

ありがとうございます