レコード数が増えていくと、INDEXを張りたくなりますよね。
INDEXのあり/なしでレスポンスが大きく変わります。
でも、「サービスを止めたくない！」
そんなWebエンジニアの方のために、メンテなしでテーブルにINDEXを張る方法を・・・。
答えは簡単。
データベースを「MySQL5.6以上」にすることです。
MySQL5.6からオンラインでのDDLが可能となりました。
つまり、「オンラインでINDEXを張ること」ができます！！
意外と知られていない・・・？！
- 参考：
CREATE INDEX、ADD INDEX項目
検証環境
DBサーバのスペック
- CPU: 2
- Memory: 15GB
対象のテーブルのレコード数
-
4,793,440件
テーブル構成
hogeテーブル
| カラム名 | データ型 | 制約 |
|---|---|---|
| id | BIGINT(20) | AUTO_INCREMENT |
| foo_number | VARCHAR(255) | - |
| bar_id | BIGINT(20) | NOT NULL |
| bar_status | INT(11) | NOT NULL, DEFAULT '1' |
| name | VARCHAR(255) | NOT NULL |
| created_at | DATETIME | NOT NULL |
| updated_at | DATETIME | NOT NULL |
DDLの準備
実行環境に依存するので、各自の環境に合わせてお使いください。
今回は、migrationファイルを使い、複合INDEXを作成します。
- SQLを発行する（
ALTER TABLEを使う） - migrationファイルを使う（Railsの
ActiveRecordを利用する）
SQLの場合
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
migrationファイルを使う場合
migrationファイル例
class AddIndexToHoge < ActiveRecord::Migration
def change
# テーブル：hoge
# カラム: foo_id, bar_id, bar_status
# 単体のINDEX
# add_index :hoge, :bar_status
# 複合INDEXを作成
add_index :hoge, [:foo_number, :bar_id, :bar_status]
end
end
開発環境でDDLを実行
事前準備
- テーブルに本番と同等のレコードを挿入する
（SQLを1件、1件作成していたら、大変なので・・・できる限り一括でデータを入れます）
開発環境に、1件くらいデータ作ってありますよね・・・？
下記のSQLを使えば、実行回数の2乗で件数が増えます。
/** 自動採番のプライマリーキーは、NULLにしましょう（エラーで怒られなくて済みます） **/
INSERT INTO テーブル名
SELECT NULL（プライマリキー）,カラム名1,...,カラムn FROM テーブル名;
DDLを実行
- 本番でのおおよその実行時間を予測するために、開発環境でDDLを実行します。
（RDSを搭載したサーバのスペックにも依存しますが・・・）
テスト用のデータが作成できたら、開発環境で実行してみましょう。
今回はmigrationファイルなので、railsのコマンドを使います。
- SQLの場合は、Mysqlサーバに入ってSQLを実行してください。
$ bundle exec rake db:migrate RAILS_ENV=development
本番環境でDDLを実行
さて、いよいよ本番環境で・・・。
$ bundle exec rake db:migrate RAILS_ENV=production
==================================================
== 20161128017930 AddIndexToHoge: migrating
-- add_index(:companies, [:foo_number, :bar_id, :bar_status], {:name=>"index_hoge_on_foo_number_and_bar_id_and_bar_status"})
-> 52.6168s
== 20161128017930 AddIndexToHoge: migrated (52.6169s)
==================================================
実行には、1分かからない程度で終わりました。
多めに見積もっても実行時間は、50秒〜1分半をみとけば良いでしょう。
まとめ
約500万のレコードがあるテーブルに、オンラインでINDEXを張りました。
INDEXを張るために、「サービスを止めなきゃ！」と思っているWebエンジニアの方に参考になれば・・・と思います。
本番で実行する前に、開発環境で実行するのを忘れずに！✨