41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

インデックスマージ、とても良い

MySQL

MySQLのインデックス

世の中がMySQL8.0.18のハッシュJOINに盛り上がってる中、オジサンは古いけど自分にとっては新しいマージインデックスの話をします。

複数のインデックスがテーブルに作られていても、使えるのは一つ

というのがMySQLでは原則ですが、これの 例外 があります。

それがタイトルのインデックスマージというものです。

インデックスマージとは？

名前の通りインデックス（の結果）をマージするというものです。

MySQLのドキュメント曰く

複数の range スキャンによって、行を取得しそれらの結果を 1 つにマージするために使用されます。

The Index Merge access method retrieves rows with multiple range scans and merges their results into one.

MySQL5.1では使える機能なんで、それ以降の5.5、5.6、5.7、8.0でも使えます。

1 つのテーブルからのインデックススキャンをマージします。複数のテーブルにわたるスキャンはマージしません。

This access method merges index scans from a single table only, not scans across multiple tables.

というわけで、JOINしたテーブル間からはマージしないとのこと。

単体のテーブルの条件内でのみ発動すると。

詳しくはMySQLのドキュメント読んでもらえばわかりますが、要するに1つのテーブル内で2つのインデックスを使ってORで条件指定している場合にこれが発動する可能性があるということです。

社内のDBのスロークエリを眺めていたところ…

WHERE句にこんな感じの条件が書かれているクエリがありました。

SELECT count(*)
FROM A_table
WHERE
(A_table.last_update_time >= '2019-10-16' OR A_table.saigono_koushin_jikan >= '2019-10-16')
AND A_table.last_update_time <= '2019-10-17 17:00:00'
AND A_table.saigono_koushin_jikan <= '2019-10-17 17:00:00'
AND …
;

（※本当はもうちょっと複雑なんですが、大人の事情でカラム名とか色々変えて、雰囲気だけ伝わるようにしていますｗ）

last_update_time というカラムと saigono_koushin_jikan というカラムでいずれかが前日以降の日付が入っていて、いずれのカラムも今日の取得時点の日付までというレンジスキャンをしています。

いずれのカラムにもインデックスは張られておらず、別のインデックスを使っていました。

（ぶっちゃけほぼインデックスフルスキャンでした）

EXPLAINを見るとこんな感じ

f:id:next4us-ti:20191017192913p:plain

（見せられない色々なものを隠していますが、ご了承ください）

この2つのインデックス、張るとマージインデックス効くんじゃね？と見つけて思った自分はさっそくステージング環境にインデックスを張ってみました。

alter table A_table add key i_saigono_koushin_jikan(saigono_koushin_jikan);
alter table A_table add key i_last_update_time(last_update_time);

そして実行計画確認したところ、コスト爆下がりでした！

f:id:next4us-ti:20191017192934p:plain

（今回のクエリでは17万が7になりましたｗ）

本番で4.4秒かかってたクエリなんですが、ステージングではこのくらいでした。

追加前（秒） 追加後（秒）
3.234 0.0072

これは良い！

まとめ

もちろん、インデックスマージで結果が大きかったらインデックスを張った意味はあまりないかもしれないですし、逆にインデックスを張ったことで更新コストが上がる可能性もあります。

（テーブルにあれこれインデックスが増えないほうが良いと思っている人でもあるので）

（インデックスが増えすぎているということはテーブルが正規化されるべきなのかもしれないというのは別の話）

とはいえ、OR句を使ってて、絞り込まれる条件がそこにあるのなら検討する余地はあります。

インデックスのテクニックとして

  • PKやUKで検索するのが最速
  • セカンダリインデックスはインデックス内にPK情報を持つ
  • カバリングインデックスで複合インデックスは効率よく全て使えるようにしましょう
  • 実行計画（EXPLAIN）を見てExtraにUsing whereがあったらインデックスをうまく使えてないかも？
  • インデックスは張りすぎると更新処理が遅くなる

といったことに加え、今回のインデックスマージを使えるようになるとまたMySQLが好きになるかもしれません。

原則（使えるインデックスは１つ）は頭に入れつつも、ORを見かけたら「もしかして？」と立ち止まって見てみましょう。