deleted_atに単独インデックスを貼る罠¶
チェック¶
- [ ] 本文を確認した
- [ ] 概要を確認した
- [ ] タグを確認した
- [ ]
inbox/直下へ移行した
概要¶
MySQL 8.0 で deleted_at に単独 index を追加した結果、本番 RDS が I/O spike で応答不能になった事例。
staging では複合 index が選ばれていたが、本番では optimizer が deleted_at IS NULL 用の単独 index を選び、大量行を読む plan になった。
NULL が多いカラム、innodb_stats_method=nulls_equal、soft delete、ショットガン index の危険性が主題。
本文¶
対象は soft delete のため deleted_at を持つ tickets table。
約 80 万件のうち 50 万件が deleted_at IS NULL の active record。
既に (assignee_id, deleted_at, priority) の複合 index が存在していた。
遅い query 改善のため、deleted_at 単独 index など複数の index をまとめて追加した。
staging の EXPLAIN では複合 index が選ばれ、問題ないように見えた。
しかし本番では optimizer が idx_tickets_deleted_at を選択し、deleted_at IS NULL の大量行を読む plan になった。
根本には、MySQL 8.0 の innodb_stats_method default nulls_equal と、IS NULL に対する cost calculation の相性がある。
NULL が多い column に単独 index を貼ると、実態より選択性が高いと見積もられ、誤った plan が選ばれることがある。
対策としては、不要な単独 index を貼らない、実データに近い環境で plan を確認する、複合 index の先頭列と条件順を意識する、optimizer trace や cardinality を確認する、index 追加後の本番監視を厚くする、などがある。
要点¶
- NULL が多い
deleted_at単独 index は optimizer trap になりうる。 - staging の EXPLAIN だけでは本番データ分布の差を検出できない。
- ショットガン index は危険。
- Soft delete では複合 index と実データ cardinality を意識する。