MySQLの自動Index選択にどこまで任せるべきか

こんにちは。エキサイト株式会社の三浦です。

MySQLでは、SQL実行時に適切なIndexを自動的に選んでくれる機能（オプティマイザ）があります。ただこれは、万能というわけではありません。

今回は、手動でIndexを選んだ方が早い例を挙げ、そのことを確認していきます。

なお今回は、 MySQL5.7 環境にて確認を行っています。

MySQLのオプティマイザ

MySQLでは、こちらから何も指定しなくても、実行するSQLをもとに検索対象のテーブルに存在するIndexを自動的に選択してくれます。これは、オプティマイザという機能によって実現されています。

実行するSQL

EXPLAIN
SELECT
    *
FROM
    test
WHERE
    test.code = "test_code";

Explain結果

id	select_type	table	partitions	type	possible_keys
1	SIMPLE	test		const	test_index

key	key_len	ref	rows	filtered	Extra
test_index	302	const	1	100.00

上記の例の場合は、 SQLが test.code カラムを定数で検索するものであり、かつ test.code カラムにIndexが存在するため、自動的に test.code カラムのIndexを選択してくれています。

このように簡単なSQLであれば適切にIndexを選択してくれますし、ある程度複雑でもオプティマイザに任せておけば問題ない場面は多いです。

ただし、常にオプティマイザに任せていればいいかというとそうではありません。

オプティマイザでは不適切なパターン例

例えば、「指定期間内に公開されている、特定の提供元・カテゴリの記事を取得するSQL」を考えてみます。

実行するSQL

EXPLAIN
SELECT
    article.*

FROM article

# packageという単位で提供元・カテゴリを組み合わせている
INNER JOIN master_package_list
    ON article.source = master_package_list.source
    AND article.category = master_package_list.category

WHERE
    # 指定期間内で有効な記事を取得する
    article.deleted_at IS NULL
    AND article.status = 1
    AND article.publish_start_date BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW()
    AND article.publish_end_date >= NOW()

    AND master_package_list.package = "sample_package"

# 最後に記事公開日順に並び替える
ORDER BY article.publish_start_date DESC

LIMIT 10;

オプティマイザに任せると以下のようなExplain結果になります。

Explain結果

（わかりやすさのため、 possible_keys や ref は一部省略しています）

id	select_type	table	partitions	type	possible_keys
1	SIMPLE	master_package_list		ref	package_index,source_category_index
1	SIMPLE	article		ref	sample_index,publish_start_date_index

key	key_len	ref	rows	filtered	Extra
package_index	62	const	40	100.00	Using temporary; Using filesort
sample_index	206	master_package_list.source,master_package_list.category,const,const	885	9.23	Using index condition; Using where

rows や filtered を見る限り、そこまで大きな問題があるようには見えません。

ただしこのSQLでは ORDER BY article.publish_start_date DESC の並び替えを行っており、このカラムが今回のIndexには適切に含まれていないために、 Using temporary; Using filesort （一時テーブルに保存して並び替え）が動いてしまっています。

では今度は、手動でIndexを指定してみましょう。なお変更部分は、 USE INDEX 部分のみです。

実行するSQL

EXPLAIN
SELECT
    article.*

FROM article
    # article.publish_start_date だけのIndexを指定
    USE INDEX (publish_start_date_index)

INNER JOIN master_package_list
    ON article.source = master_package_list.source
    AND article.category = master_package_list.category

WHERE
    article.deleted_at IS NULL
    AND article.status = 1
    AND article.publish_start_date BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW()
    AND article.publish_end_date >= NOW()

    AND master_package_list.package = "sample_package"

ORDER BY article.publish_start_date DESC

LIMIT 10;

Explainは以下のようになります。

Explain結果

（わかりやすさのため、 possible_keys や ref は一部省略しています）

id	select_type	table	partitions	type	possible_keys
1	SIMPLE	article		range	publish_start_date_index
1	SIMPLE	master_package_list		ref	package_index,source_category_index

key	key_len	ref	rows	filtered	Extra
publish_start_date_index	5		99670	0.33	Using index condition; Using where
source_category_index	199	article.source,article.category	1	3.10	Using where

rows や filtered だけを見ると、先程のExplain結果に比べて明らかに悪化しているように見えます。改善点で言えば、 Using temporary; Using filesort がなくなったくらいでしょうか。

上記の2つを見比べると、一見前者のIndexを選ぶのは妥当だと思うかもしれません。

ですが実際は、 SQLの実行速度は圧倒的に後者が早い という結果になるのです。

これは、今回のテーブルの内容であれば「最後の並び替えのコスト」が「最初・途中の絞り込みのコスト」に比べて大きいために、絞り込みを効率化するよりも最初からソートされているIndexを使った方が実行速度としては早かった、ということが原因として考えられます。

もちろんこれは逆に言えば、 article テーブルに保存されているレコード数や master_package_list のデータ構造が異なっていれば、同じSQLであってもオプティマイザの選定の方がSQLの速度が早い場合もありえるということになります。

ただ、この結果から、

オプティマイザはある程度合理的にIndexを選択するが、それが必ずしもSQL実行速度を最速化する結果につながるとは限らない

ということがわかります。

最後に

オプティマイザは、MySQLにおいてとても有用な機能です。ですが、SQL文やそこで使用するテーブルのデータ構造、レコード数等によっては、手動でIndexを指定したほうが実行速度が早くなる場合も存在します。

SQLを実行してみて速度が遅いと感じた場合は、「手動でIndexを選択することも手の一つである」として認識しておくと良いのではないでしょうか。