mysqlのexplainを使って、パフォーマンスを改善してみる
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)
詳細な項目は下記を参考にしてください。
上記を見て、「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については、使い方がまだまだだと思うので、積極的に調べていきたいと思います。