読者です 読者をやめる 読者になる 読者になる

mysqlのexplainを使って、パフォーマンスを改善してみる

mysql rails

sqlのパフォーマンスで実験していたので、試した結果を載せます。

前提条件

Speakerモデルというのがあります。

テーブルの状態は下記のようになります。

show columns from speakers;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(255) | NO   |     | NULL    |                |
| last_name  | varchar(255) | NO   |     | NULL    |                |
| type       | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

データは50,000件入れており、下記のような状態になっています。

first_name: 山田1〜50000
last_name: 太郎

こちらの状態から、first_name + last_nameに関する検索条件を行います。

改善前

first_name:「山田30000」+ last_name:「太郎」を検索します。

railsのwhereで検索してみます。

Player.where(first_name: '山田30000')
  Player Load (32.9ms)  SELECT `speakers`.* FROM `speakers` WHERE `speakers`.`type` IN ('Player') AND `speakers`.`first_name` = '山田30000'
=> [#<Player:0x007fe85ebd4798
  id: 40011,
  first_name: "山田30000",
  last_name: "太郎",
  type: "Player",
  created_at: Sun, 17 Jul 2016 14:35:24 JST +09:00,
  updated_at: Sun, 17 Jul 2016 14:35:24 JST +09:00>]

結果を出すのに、0.0329秒かかっていることになります。

もう一つ、複合で検索する場合を想定してみます。

山田30???という場合です。

Player.find_by_sql("select * from speakers  where first_name like '%山田30%' and last_name = '太郎'")
  Player Load (26.3ms)  select * from speakers  where first_name like '%山田30%' and last_name = '太郎'

こちらは結果を出すのに、0.026秒になります。

explainでsqlの状態を図る

mysqlでexplainという便利な関数があります。

こちらは、sqlの状態を調べてくれるもになります。

まずは前者のsqlを使用してみます。

 explain SELECT `speakers`.* FROM `speakers` WHERE `speakers`.`type` IN ('Player') AND `speakers`.`first_name` = '山田30000';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | speakers | ALL  | NULL          | NULL | NULL    | NULL | 50156 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

後者になります。

explain select * from speakers  where first_name like '%山田30%' and last_name = '太郎';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | speakers | ALL  | NULL          | NULL | NULL    | NULL | 50156 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

詳細な項目は下記を参考にしてください。

nippondanji.blogspot.jp

上記を見て、「possible_keys」がnullになっております。

これはindexが貼られていない状態になります。

まずはindexをつけてみましょう。

indexを貼ってみる

class AddIndexFirstNameAndLastNameToSpeakers < ActiveRecord::Migration
  def change
    add_index :speakers, :first_name
    add_index :speakers, :last_name
  end
end

indexをつけて、先程の結果を見てみます。

まずは前者から行きます。

Player.where("first_name like '山田30000' ", last_name: '太郎')
  Player Load (0.4ms)  SELECT `speakers`.* FROM `speakers` WHERE `speakers`.`type` IN ('Player') AND (first_name like '山田30000' )

前者は、「32.9ms」→「0.4ms」と劇的にパフォーマンスが上がりました。

これが噂のindexの効果か・・・

後者を測ってみます。

Player.find_by_sql("select * from speakers  where first_name like '%山田30%' and last_name = '太郎'")
  Player Load (74.4ms)  select * from speakers  where first_name like '%山田30%' and last_name = '太郎'

後者「26.3ms」→「74.4ms」と悪化しました。

あれ、悪化している・・・orz

この原因はlikeの曖昧検索で先頭に「%」が付いているから、indexが効かないようになっているようです。

なので、修正してみます。

Player.find_by_sql("select * from speakers  where first_name like '山田30%' and last_name = '太郎'")
  Player Load (3.1ms)  select * from speakers  where first_name like '山田30%' and last_name = '太郎'

3.1msとそれなりに早くなりました。

ちなみにindexがない状態です。

Player.find_by_sql("select * from speakers  where first_name like '山田30%' and last_name = '太郎'")
  Player Load (22.9ms)  select * from speakers  where first_name like '山田30%' and last_name = '太郎'

「22.9ms」→「3.1ms」単純なindexだけでもこんな効果があるとは・・・

ちなみにexplainの結果です。

前者です。

explain SELECT `speakers`.* FROM `speakers` WHERE `speakers`.`type` IN ('Player') AND `speakers`.`first_name` = '山田30000';
+----+-------------+----------+------+------------------------------+------------------------------+---------+-------+------+------------------------------------+
| id | select_type | table    | type | possible_keys                | key                          | key_len | ref   | rows | Extra                              |
+----+-------------+----------+------+------------------------------+------------------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | speakers | ref  | index_speakers_on_first_name | index_speakers_on_first_name | 767     | const |    1 | Using index condition; Using where |
+----+-------------+----------+------+------------------------------+------------------------------+---------+-------+------+------------------------------------+

後者です。

explain select * from speakers  where first_name like '%山田30' and last_name = '太郎';
+----+-------------+----------+------+-----------------------------+-----------------------------+---------+-------+-------+------------------------------------+
| id | select_type | table    | type | possible_keys               | key                         | key_len | ref   | rows  | Extra                              |
+----+-------------+----------+------+-----------------------------+-----------------------------+---------+-------+-------+------------------------------------+
|  1 | SIMPLE      | speakers | ref  | index_speakers_on_last_name | index_speakers_on_last_name | 767     | const | 25078 | Using index condition; Using where |
+----+-------------+----------+------+-----------------------------+-----------------------------+---------+-------+-------+------------------------------------+

possible_keys, keyにindexが効いているように変化していますね。

explainについては、使い方がまだまだだと思うので、積極的に調べていきたいと思います。