表参道.rb #5 の発表資料です

作ったgem

rubygems.org: https://rubygems.org/gems/index_shotgun
GitHub: https://github.com/sue445/index_shotgun

スライド板

エントリ版

【今月のgem】¹ index_shotgunを作った #omotesandorb

sue445

2015/10/01 表参道.rb #5

自己紹介

sue445
株式会社ドリコム所属
サーバサイド全般の雑用
- インフラ、アプリ、ライブラリ、社内ツールetc
- 最近はアプリの改善系がメイン
TDDおじさん
プリキュアおじさん

【今期の嫁】キュアトゥインクル

【本妻】キュアピース

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しないと容量減らない）²
INSERTやUPDATE時にそのindexも更新されるのでその分遅くなる
（MySQLだと）同じクエリでも意図しないindexが使われて遅くなることがある
- user_idによって使われるindexが変わって遅くなることがあった

まとめ

不要なindexはこまめにチェックしよう

スライドには書き忘れましたが、だいたい月1個ペースでgemを作ってるので勝手に「今月のgem」ってつけています ↩
ちなみにFusionIOのioDriveという爆速のストレージ使ってますが、それでもOPTIMIZEだけで1時間以上かかりました。。。 ↩