インデックスを使った高速化について(ORDERD BYにインデクスが使われない例)

mysql・DB

今回は、MySQLの高速化のメモ - @luke_silvia.diaryの方法に従ってクエリの高速化をした際に、MySQLのインデックスについて分かったことを書いておきます。

高速化対象のクエリ

今回高速化したいクエリは、以下のようなもの。

SELECT users.*, students.school, workers.school 
FROM users LEFT JOIN students ON users.id = students.user_id 
LEFT JOIN workers ON users.id = workers.user_id 
WHERE (users.status= 1 AND ((kind = 0 AND students.school = 'test') OR (kind = 1 AND workers.school = 'test'))) 
ORDER BY users.created_at DESC
LIMIT 0,10;


それぞれのテーブルの関係は次の様になる。
usersテーブルと、そのユーザーのプロフィールを表す2種類のテーブルがある。
プロフィールテーブルはそれぞれ、studentsテーブル、workersテーブルとなっており、ユーザーが学生の場合は、studentsテーブルにプロフィールを持ち、社会人の場合はworkersテーブルにプロフィールを持つ。
ユーザーが学生か社会人かを表すカラムとして、users.kindがあり、学生の場合は0、社会人の場合は1となる。
users.statusは、そのユーザーが有効であることを表す。
これらの上で、上のクエリは、学生と社会人を対象に、学校名で検索を行うというものになる。検索結果は、created_atで降順ソートしている。

EXPLAINしてみる

上記のクエリはかなり遅いのでまずはEXLAINしてみた。結果は次の様になった。

+----+-------------+--------------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+--------------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | users              | ALL  | NULL          | NULL | NULL    | NULL |   19 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | students           | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where                                  |
|  1 | SIMPLE      | workers            | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                  |
+----+-------------+--------------------+------+---------------+------+---------+------+------+----------------------------------------------+


げげ。Extraの所に一番見たくない、Using temporaryとUsing filesortがでてしまった。

■Using filesort
レコードをソートして取り出す方法を決定するには、MySQL はパスを余分に実行しなくてはならないことを示す。 join type に従ってすべてのレコードをスキャンし、WHERE 条件に一致する全てのレコードに、ソートキー + 行ポインタを格納して、ソートは実行される。 その後キーがソートされる。 最後に、ソートされた順にレコードが取り出される。


■Using temporary
クエリの解決に MySQL で結果を保持するテンポラリテーブルの作成が必要であることを示す。これは一般に、GROUP BY を実行したカラムセットと異なるカラムセットに対して ORDER BY を実行した場合に発生する。


この説明とクエリを照らし合わせると、遅い原因は「ORDER BY」にあると言える。試しに、ORDER BYがないクエリをEXPLAINすると次の様になった。

EXPLAIN SELECT users.*, students.school, workers.school 
FROM users LEFT JOIN students ON users.id = students.user_id 
LEFT JOIN workers ON users.id = workers.user_id 
WHERE (users.status= 1 AND ((kind = 0 AND students.school = 'test') OR (kind = 2 AND workers.school = 'test'))) 
LIMIT 0,10;

+----+-------------+--------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users              | ALL  | NULL          | NULL | NULL    | NULL |   19 | Using where |
|  1 | SIMPLE      | students           | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
|  1 | SIMPLE      | workers            | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+------+-------------+

よって、ORDER BYを最適化することにした。EXPLAINにの「possible_keys」がNULLなことから、3つのテーブルにはインデックスを貼っていない。なので、インデックスを使って高速化することにする。

インデックスを貼る

まずは、ORDER BYの対象になっている、users.created_atにインデックスを掛けてみた。このアプリケーションはRailsで動いているので、インデックスを貼るのにはマイグレーションを使った。それは以下のようになる。

class AddIndexToUser < ActiveRecord::Migration
  def self.up
    add_index :users, :created_at
  end

  def self.down
    remove_index :users, :created_at
  end
end


インデックスが貼られたかどうかは、次のクエリで確かめることができる。インデックスが貼られている場合は、インデックステーブルの情報とともに、どのカラムに対するインデックスかが出力される。

SHOW INDEX FROM users;


インデックスを貼った後に、もう一度EXPLAINしてみた。が、結果はさっきのEXPLAINと同じで、「Using temporary; Using filesort」が出てきてしまった。それどころか、「possible_keys」にcreated_atのインデックス情報が出てこなかった。
以下のようなクエリを試したら、きちんとインデックスが使われていたので、このクエリの場合に使えないということみたい。

EXPLAIN SELECT * FROM users ORDER BY created_at;


データサイズが少ないのでインデックス使うまでもないとオプティマイザが判断したということがあると聞き、レコードを増やしてみても結果は同じだったので、やはりクエリに問題があるみたい。
もしかしたら、created_atだけにインデックス貼るだけではダメなのかと思い、(kind,created_at)、(status,created_at)にインデックスを貼ってみることにした。一度の問い合わせに使えるインデックスは１つのみなので、１つずつ試してみることにした。貼り方は次の通り。

class AddIndexToUser < ActiveRecord::Migration
  def self.up
    add_index :users, [:kind,:created_at]
  end

  def self.down
    remove_index :users, [:kind,:created_at]
  end
end


これは複合インデックスと言い、上の様に書くと、検索ではkindのインデックスを使い、ソートにcreated_atのインデックスを使うようになる。ちなみに、kindとcreated_atの別々にインデックスを貼るのとは違い、このインデックスを作成したからと言って、kind唯一のindexを消してしまうと、以下のようなクエリではインデックスが使われなくなる。（実践ハイパフォーマンスMySQL　参照）

SELECT * FROM users WHERE kind = 1;


この後にEXPLAINを書けたら、インデックスが使われているのは分かったが、kindの検索にのみ使われており、やはりソートには使われていない模様。「Using temporaryとUsing filesort」はでたまま（ノд｀）

ORDER BYにインデックスが使われない場合とその対策

調べてみたら、どうやらこのクエリのORDER BYにはインデックスが使われないことがわかった。

extraカラムにUsing filesortが出力された場合は、MySQLでORDER BYの解決にインデックスを使用できません。


ガーン。この場合どうすればいいのかを調べてみた。

この二つだった。
こういった場合、MySQLでの解決策は以下の2点しかない。

・テーブルの非正規化
・スナップショットテーブルの作成


どうやら、今回のクエリ（where句にcreated_atがないため使えないっぽいwhere句にcreated_atがあっても、ORDER BYにインデックスが使われることとは関係なかった。検索時には使われる）の場合は、インデックスを使って解決するのは無理のようだ。上記の他にも、sort_bufferのサイズを大きくすれば、「Using temporary」は消えるかもしれない。



今までインデックスを使わなくとも重いことはなかったが、今回インデックスのことを調べてみてなかなか勉強になりました丶(´▽｀)ノ

追記

この例の場合は、SQLからORDERBYを外し、レコードの集合を取得した後に、sort_byを使った方がずっと速かった。
また、schoolにインデクスを貼り、それを使うために、workersテーブルと、studentsテーブルそれぞれに対して検索をかけ、結果を合わせてからソートした。これで、レコードが２千件の場合で３０倍くらい速くなった。

参考

実践ハイパフォーマンスMySQL

実践ハイパフォーマンスMySQL