レコード数が増えていくと、INDEXを張りたくなりますよね。
INDEXのあり/なしでレスポンスが大きく変わります。
でも、「サービスを止めたくない！」
そんなWebエンジニアの方のために、メンテなしでテーブルにINDEXを張る方法を・・・。

答えは簡単。

データベースを「MySQL5.6以上」にすることです。
MySQL5.6からオンラインでのDDLが可能となりました。
つまり、「オンラインでINDEXを張ること」ができます！！

意外と知られていない・・・？！

参考： CREATE INDEX、ADD INDEX項目
- MySQL 5.6 リファレンスマニュアル 14.11.1 オンライン DDL の概要

検証環境

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エンジニアの方に参考になれば・・・と思います。

本番で実行する前に、開発環境で実行するのを忘れずに！✨

【MySQL5.6以上】Webエンジニア向け！メンテなしで500万件レコード入りのテーブルにINDEXを張る実行時間の目安