今回は、MySQLのインデックスについて書きます。文章だけでは分かりにくいので、実際に100万件のデータをインサートしてインデックスを貼って効果をみてみます。

インデックスとは

インデックスとは、データベース内から特定のデータを素早く見つけるために使われるものです。インデックスが無い状態よりも、検索を高速化することができます。ちなみに主キー(PRIMARY KEY)は、インデックスは自動で作成されますので、手動での設定は不要です。

インデックスにはデメリットもあり、データの登録(INSERT)、変更(UPDATE)の処理が重くなったり、インデックスを作成する領域が必要となります。そのため、インデックスを不用意に多用することは避けた方が良いです。

インデックスを検証するために大量のデータを投入する

実際に検証するためにDB名【testdb】と、テーブル名【test】を作成します。testテーブルには【id】【count】【flag】の3つのカラムを用意しました。idカラムが主キーです。そして、以下のようにRubyで100万件のデータをインサートします。

require 'mysql'
client = Mysql::connect('xxx.xxx.xx.xxx', 'username', 'passwd', 'testdb')
client.charset = 'utf8'

1000000.times do |i|
  stmt = client.prepare("INSERT INTO test (count, flag) VALUES (?, ?)")
  res = stmt.execute("#{rand(100..999)}", "#{rand(1..2)}")
end

testテーブルのcountカラムには、3桁のランダムな数字をインサートしています。そしてflagカラムには、1または2のいずれかをインサートしました。(結局、この記事ではflagカラムは使いませんでした)

テーブルの中身はこんな感じです。

mysql> SELECT * FROM test LIMIT 10;

mysql-index7

インデックスを確認する

SHOW INDEXコマンドを使い、既に貼られているインデックスを確認します。

SHOW INDEX FROM データベース名.テーブル名;
SHOW INDEX FROM testdb.test;

mysql-index3
インデックスはまだ貼っていませんので、主キー(PRIMARY KEY)のみ表示されています。主キーが表示されている理由は、主キーはインデックスが自動で作成されるためです。

インデックスを貼る対象を探す

インデックスを貼る対象を探すには、EXPLAINコマンドを使います。EXPLAINコマンドは、実行計画を取得するコマンドです。

実行計画とは「どのインデックスを使って(あるいはインデックスを使わずにテーブルスキャンで)クエリーを処理するか」をMySQLが判断した結果のことです。「インデックスはちゃんと使われているだろうか」「インデックスでどこまでクエリーを効率的に処理できているだろうか」という疑問が湧いた時には、「とりあえずEXPLAINで」となりますよね。

出典:SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう

簡単に言うと、データへのアクセス方法がわかるものです。使い方は、SQL文の先頭に「EXPLAIN」を追加して使用します。

EXPLAIN SELECT * FROM test WHERE count = 100;

mysql-index2

出力された結果を表にして見てみます。

項目出力結果結果に対する説明
select_typeSIMPLE単一のテーブル
tabletest対象テーブルの名称
partitionsNULL -
typeALL全部検索しているので重い。インデックスが使用されていない状態
possible_keysNULL -
keyNULLクエリに使用されたインデックスを表示。NULLなのでインデックスは使われていない
key_lenNULL -
refNULL -
rows998510検索対象のカラム数(推定値)
filtered10.00テーブル条件によってフィルタ処理される行の推定の割合
ExtraUsing where追加情報。Using whereはあまり気にする必要はない

この結果で確認する項目は、主に【type】です。

  • const:一意の値。一番高速
  • ref:インデックスが貼ってある状態
  • index(フルインデックススキャン):インデックス全体をスキャン。速度は遅い
  • ALL(フルテーブルスキャン):全部検索するので遅い

上記のEXPLAINコマンドの結果から、typeがALLになっているので検索が遅いことが分かります。次に、実際にインデックスを貼って効果を確認します。

インデックスの効果を確認

インデックスを貼る前と貼った後の数値の比較をして、本当に速度が改善されたのか確かめます。

インデックスを貼る前

まずは、インデックスを貼る前の速度を確認します。
mysql-index4
結果:0.20sec

次にインデックスを貼ります。インデックスを貼るコマンドは、以下のとおりです。

ALTER TABLE DB名.テーブル名 ADD INDEX インデックス名(カラム名);
ALTER TABLE testdb.test ADD INDEX index_0(count);

インデックスを貼った後

mysql-index6
結果:0.00sec

インデックスを貼ったことで【0.20sec】だったのが【0.00sec】になり、検索速度が速くなったことが分かりました。

EXPLAIN、SHOW INDEXコマンドを再度実行してみる

インデックスを貼って速度が改善されたことが確認できました。それでは、EXPLAINやSHOW INDEXコマンドをもう一度使うと、どのように変わるのでしょうか。

EXPLAIN

再度、EXPLAINコマンドを使って内容を確認します。

EXPLAIN SELECT * FROM test WHERE count = 100;

mysql-index8
typeがALLから、refに変わっているのが分かります。

SHOW INDEX

SHOW INDEXコマンドも、再度実行します。

SHOW INDEX FROM testdb.test;

mysql-index9
countカラムにインデックスを追加したので、結果が2行で出力されました。

最後に

今回の検証ではインデックスを貼ったことで、実際に速度が改善ことが数値で確認できました。今回は単一のインデックスだったため単純な内容でしたが、複数のカラムの場合は様々な制約があります。

例えばMySQLでは1つのクエリを実行するとき1つのテーブルにつき1つのインデックスしか使用できません。つまり、単に2つのインデックスを作成してもどちらか片方しか使われません。

複数のカラムにインデックスを使用できるようにするためには、マルチカラムインデックスを使います。この記事では、マルチカラムインデックスの詳細については割愛します。

以上です。

参考

MySQL 5.6 リファレンスマニュアル
MySQL EXPLAINのそれぞれの項目についての覚書
MySQLでインデックスの追加・確認・削除を行う方法