MySQLのインデックスの使われ方について

Question

TABLE : customer

id	name	age	insert_at	update_at
1	鈴木	18	2018-11-28 10:00:00	2018-11-29 18:00:00
2	佐藤	20	2018-11-28 21:00:00	2018-11-29 18:00:00
3	渡辺	23	2018-11-29 15:00:00	2018-11-29 18:00:00

INDEX :

キー名	カラム
PRIMARY	id
index_age	age
index_insert_at	insert_at

MySQLは１テーブルにつき１インデックスしか使用されないと聞きます。

テーブルを作成する際、プライマリーキーには自動的にインデクスが貼られるかと思いますが、下記の様なSQLにはPRIMARYインデックスは使われないのでしょうか？

SELECT * FROM customer WHERE age >= 20 ORDER BY id DESC

Answer 1

あれこれ悩むぐらいなら、まずはEXPLAINしてみましょう。

ごく小さな表の場合、全部読んでも負担にならないということで、インデックスなしで処理されることもあります。また、age >= 20を満たすカーディナリティ次第で状況も変化します（ほとんどがage >= 20であれば、id順で見て違うのを除外していったほうが効率的なので、そのようにしょりされる可能性もあります）。

Comments

[teratailmacr2]

ご回答ありがとうございます。

条件により結果が変わる為、この場合は必ずこれ！と言う解はないので答えられないと言う事でしょうか？

Answer 2

PRIMARY KEY に張られたインデクスが使用されるのは、where の条件に PRIMARY KEY に関わるものがある場合です。

SELECT * FROM customer WHERE age >= 20 ORDER BY id DESC

この場合なら PRIMARY KEY は WHERE に出てきませんから実行計画から考えても出てきません。

使用される可能性があるのは index_age ですが、age >= 20 であるレコードの方が多い場合など、index を使うとかえって遅くなる場合があり得るので、常に使うとは限りません。

※index を使ってレコードを取得する場合、インデックスを調べてレコードのユニークIDを得る→そのレコードを取得していく（ランダムリード）、となるため、全件を一度一気に読み込んで（シーケンシャルリード）から対象外を振り落とす full scan した方が（ストレージへのアクセス的に）早い場合があります

Comments

[teratailmacr2]

ご回答ありがとうございます。

なるほど。
と言う事は、ageとidに対して複合インデックスを貼っておくのが最適解と言う事でしょうか？
（ageのみはいらない？）
ただそれが実際に使われるかどうかはカーディナリティの低さによって変わると言う事でしょうか。

[tacsheaven]

age のみのインデックスに意味はあるかと言うことですが、それはデータの分布によるのです。
例えば age >= 20 では full scan になったとしても、逆の age < 20 なら index scan になる可能性があります。age = 20 ならインデックスが使われるでしょう（データのほとんどが age = 20 とかでない限り）。

複合インデックスを張ったとしても使われるのは where 条件が複合する場合に限られますので、あまり意味はないでしょう（その場合でも必ず使うとは限らない）

経験則としては、全体の一割以下まで絞り込むことが可能な抽出条件の組み合わせが可能であれば、それについてはインデックスがあってもよいかとは思います。ただこれも母数となるレコード数によります。

[teratailmacr2]

order句にもindexは使わるとありますので、ageとidの複合indexを貼っておけばwhereのageにもorder byのidにもインデックスが使われるし、ageだけの時も使われるし、逆にageに使われなかった場合はPRIMARY KEYが使われるので、ageだけのindexは必要ないと言う事にはならないのでしょうか？（例のSQLしか使わない場合）

Answer 3

実行計画を確認 することでインデックスが使われているかわかります。