※ MySQLって書いてますがその互換の Aurora で検証しています。
MySQLのレコード数が大きなテーブルからデータを検索するとき、インデックスは処理時間の短縮において重要な役割を果たしてくれます。
ただ、一個のテーブルにインデックスを何個も作成しているとSQLによっては最適なインデックスが使われないことがあります。このような場合、インデックスヒントを使うことでクエリを最適化できる可能性があります。
参考: MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.9.4 インデックスヒント
自分の事例で言うと、昔仕事で扱っていたDBでインデックスが10個以上張られているテーブルがあり、きちんと指定してあげないと正しく使ってくれないものがあったことがあります。
また、最近は私用で個人的に集めているデータを溜めているテーブルにおいても、レコード数が増えるにれて正しいインデックスを使ってくれない事例が発生するようになりました。
自分の事例で言うと、どうもMySQLによってインデックスによる絞り込み効果が低いと判断されるとそれが使われないってことがあるようですね。半年分のデータを取得しようとすると正しく実行されないが、1ヶ月分のデータを取得しようとすると正しく使われる、といった事例をよく見ます。
テーブルに貼られているインデックスの確認方法
まずはどんなインデックスが使いうるのか知らないと話にならないのでその確認方法です。
SHOW CREATE TABLE {テーブル名};
でテーブル定義を丸ごと確認するか、インデックス情報取得専用の構文である、
SHOW INDEX FROM {テーブル名};
を使うことで確認できます。
僕個人が分析用に溜めている株価のテーブルだと次のようになります。code(証券コード)とdate(日付)を主キーとしていて、code, date 個別にもインデックスを貼っています。
select
SHOW INDEX FROM price
-- 結果
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 price 0 PRIMARY 1 code A 4561 None None BTREE
1 price 0 PRIMARY 2 date A 7336473 None None BTREE
2 price 1 code 1 code A 4383 None None BTREE
3 price 1 date 1 date A 3166 None None BTREE key_name列で、primary, code, date の3種類のインデックスがあることが確認できますね。
クエリが利用しようとしているインデックスの確認方法
おそらく処理時間とかを計測したりてこのクエリは正しくインデクス使ってないな、って気づくことになると思うのですが、実際にどのインデックスを使っているかは、そのクエリの先頭に EXPLAIN をつけて実行することで確認できます。
例えば、次のような感じです。possible_keys が利用可能なインデックスで、key が実際に使うインデックスです。
EXPLAIN SELECT
*
FROM
price
WHERE
date >= '2023-01-01'
-- 結果 (これはkeyがNoneなのでインデックスを使ってない)
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
0 1 SIMPLE price None ALL date None None None 7336473 25.53 Using where
EXPLAIN SELECT
*
FROM
price
WHERE
date >= '2023-12-01'
-- 結果 (これはdateをインデックスとして使っている)
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
0 1 SIMPLE price None range date date 3 None 176076 100.0 Using index conditionさて、上記の上の例でもdateをインデックスとして使って欲しい場合にインデックスヒントを使います。
インデックスヒントの使い方
インデックスヒントを使う場合、特定のインデックスを強制する USE INDEX, 特定のインデックスの利用を防ぐ IGNORE INDEX, USE INDEXよりも強力に指定したインデックスを強制する FORCE INDEX の3種類があります。
使い方は同じで、FROM句のテーブル名の後ろに、
テーブル名 USE INDEX (インデックス名)
のように書きます。カッコを忘れないように注意してください。
FORCE IDNEXでやってみます。
EXPLAIN SELECT
*
FROM
price FORCE INDEX(date)
WHERE
date >= '2023-01-01'
-- 結果 (FORCEしたインデックスが使われている)
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
0 1 SIMPLE price None range date date 3 None 176076 100.0 Using index condition最後に
インデックスヒントを使うことでMySQLが利用するインデックスをある程度制御できるようにあります。ただ、実際にパフォーマスが改善するかどうかはまた別の問題です。というのもMySQLが選んだキーの方が効率的である可能性というのも十分あり得るからです。
このクエリはチューニングが必要だなと感じることがあった場合にインデックスヒントを使うことになると思いますが、処理時間が本当に改善してるかどうかはきちんと計測するようにしましょう。