カーディナリティが低いINDEXのお掃除をしました

こんにちは！サーバーサイドエンジニアをやっている @otukutun です。

最近slow query起因のレイテンシアラートが発生していてRDB(MySQL)が不安定になってしまう事象が発生し、それはカーディナリティが低いINDEXが使われたことが原因で発生していました。今回は、その対応としてカーディナリティが低いINDEXのお掃除を行ったのでそれについて書きたいと思います。

なぜカーディナリティが低いINDEXを削除したのか?

一般的にカーディナリティが低いINDEXの弊害などはいろんな方が言われていますが、

効果が低いのでSELECTの性能向上にはつながらない
INSERT時にINDEXの更新がされるのでINDEX数が多いほどINSERTが遅くなる(USE THE INDEX, LUKE「インデックスを作れば作るほど、INSERTが遅くなる」より引用)

などがあると思っております。詳しくはSQLアンチパターン「12章インデックスショットガン」をみてみてください。

今回はカーディナリティが低いINDEXを削除した理由ですが、

カーディナリティの統計情報がおかしくなっていた。flag的なフィールドで20ほどの値しかないはずだが、数値はその数百倍の7000ほどになっていた
そのINDEXがまれに使われて、実態としては検索パフォーマンスをあげないのでslow queryになっていた
その結果、MySQLのパフォーマンスが悪化しサービスが不安定になることが稀にあった

などがあります。対処方法ですが、OPTIMIZE TABLEを実行しカーディナリティを更新することも考えましたが、今回はDROP INDEXすることにしました。OPTIMIZE TABLEとDROP INDEXは5.6.17以降はIn Placeで実行されるため実行時の挙動は同じですが、Tableの再構築が行われるかの違いがあります。今回は元々が効果がないINDEXであることはSQLとアプリケーションコードから把握できていたこと、Tableの再構築は必要ないことからDROP INDEXすることにしました。

やったこと

まずは一旦、一次対応としてインデックスヒントを与える対応をしました。その後、DROP INDEXの準備を進めていきました。

前述したようにMySQL5.6.17以降(InnoDB)ではDROP INDEXはオンラインDDL対応でIn Placeで実行されるため、テーブルロックはされません(公式ページより)。ただ、レプリカでのbinlogの反映の影響が遅延することを考慮してアクセスが少ない早朝に作業することにしました。今回はslow queryの原因になっているINDEXと他2つのカーディナリティがとても低いものを合わせた3つのINDEXをDROP対応しました。

その際,以下手順で順次DROP INDEX対応を行いました。

DROP INDEXの実行
AWS上のアラートの確認
念の為 SHOW SLAVE STATUS でSeconds_Behind_Masterの値を確認

最終的に何も問題なく(replica lagも発生しませんでした)、1時間ほどで作業を終えることができました。

おわりに

slow queryの原因が解消され、無事平穏な生活が戻ってきました。(ただし、INSERTクエリの改善は前後比較だと明らかな改善は見られませんでした)

今回はslow queryを発生させているINDEXのDROP対応をしました。他にも効果的でないINDEXが行われている箇所が見つかったので削除していけたらと思います。INDEXはRDBを扱う上で便利な機能ですが、無駄なINDEXを張ってしまうことでの弊害もあります。用法要領を守って正しくお使いくださいませ。