表参道.rb #5 の発表資料です
作ったgem
- rubygems.org: https://rubygems.org/gems/index_shotgun
- GitHub: https://github.com/sue445/index_shotgun
スライド板
エントリ版
【今月のgem】1 index_shotgunを作った #omotesandorb
sue445
2015/10/01 表参道.rb #5
自己紹介
【今期の嫁】キュアトゥインクル
【本妻】キュアピース
Agenda
- 前置き
- gemの紹介
- 使い方
- 重複index検出ロジック
- 不要なindexを消す理由
- まとめ
前置き
- DBの容量圧迫してたので削除できる不要indexの調査してた
- 本来ならアプリで発行されるSQLを全部explainをとって1つずつ精査すべきだが、大変そうだったので機械的に抽出するできるものを削除する方向にした
最初に作ったスクリプト
bundle exec rails r scripts/search_duplicate_indexes.rb
pt-duplicate-key-checker
- これを作った後に WEB+DB PRESS Vol.88 を読んで全く同じ用途の pt-duplicate-key-checker があることを知ったｗ
- 実際よかった
- 結果もだいたい同じ
- 詳しいこと：Macでpercona-toolkitを使う方法 - くりにっき
index_shotgun 
- さっきのスクリプトをベースにした、重複indexを抽出するためのgemです
- active_recordベースなので、pt-duplicate-key-checker とは違いMySQL以外でも使えます
- そのDBで本当に重複indexであるか妥当かどうかは厳密には精査していない
- 名前の由来は SQLアンチパターン の1つの「インデックスショットガン（闇雲インデックス）」です
使い方（Gemfileに書く方法）
group :development do
gem 'index_shotgun'
end
rake index_shotgun:fire
タスク名が 厨二病 カッコいい
実行結果
# =============================
# user_stocks
# =============================
# index_user_stocks_on_user_id is a left-prefix of index_user_stocks_on_user_id_and_article_id
# To remove this duplicate index, execute:
ALTER TABLE `user_stocks` DROP INDEX `index_user_stocks_on_user_id`;
# =============================
# user_stocks
# =============================
# index_user_stocks_on_user_id_and_article_id_and_already_read has unnecessary column already_read (index_user_stocks_on_user_id_and_article_id is unique index!)
# To remove this duplicate index, execute:
ALTER TABLE `user_stocks` DROP INDEX `index_user_stocks_on_user_id_and_article_id_and_already_read`;
# =============================
# user_stocks
# =============================
# index_user_stocks_on_user_id is a left-prefix of index_user_stocks_on_user_id_and_article_id_and_already_read
# To remove this duplicate index, execute:
ALTER TABLE `user_stocks` DROP INDEX `index_user_stocks_on_user_id`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Total Duplicate Indexes 3
# Total Indexes 6
# Total Tables 5
使い方（コマンドラインから使う方法）
gem install して
index_shotgun mysql --database=DATABASE
index_shotgun postgresql --database=DATABASE
index_shotgun sqlite3 --database=DATABASE
- 出力形式はさっきと同じ
- 別途 mysql2, pg, sqlite3などのgemが必要
重複index検出ロジック
基本は前方一致
- 同じテーブルに [column_1, column_2] と [column_1, column_2, column_3] のindexが混在していた時は前者を削除（多い方の前方一致でまかなえるため）
- 前者がunique indexだった場合、cardinalityによっては後者のindexが削除できる
- 例：column_3がbooleanだったので後者を消した（NULLを考慮しても3パターンしか値をとらないｗ）
不要なindexを消す理由
- ストレージの容量を喰う
- 2.2億レコードのテーブルで不要なindexを1つ消すだけで18GB削減されたｗ
- ただしDROP INDEXだけだと容量減らないことがあるので注意（MySQLだとDROP INDEXした後にOPTIMIZEしないと容量減らない）2
- INSERTやUPDATE時にそのindexも更新されるのでその分遅くなる
- （MySQLだと）同じクエリでも意図しないindexが使われて遅くなることがある
- user_idによって使われるindexが変わって遅くなることがあった
まとめ
不要なindexはこまめにチェックしよう