なーんか、SQLが遅いんだよなーってことは開発中ならば日常茶飯事の出来事と思います
複雑なクエリについては、チューニングのアプローチは多々あると思いますが、非常に単純なクエリなんだけど、データ量が多すぎてどうしようもなく遅いってこともあります
そんな時はとりあえずexplainしてみて、インデックスが効いているかを確認してみましょう
explainで調べる
おそーいクエリがあったとき、とりあえずexplainしてみる、くらいチューニングに必須です
例として以下のようなemployeeというテーブルがあったとします
-- （１）employeeの構成 mysql> describe employee; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | NO | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.03 sec) -- （２）employeeのレコード mysql> select * from employee; +----+-----------+ | id | name | +----+-----------+ | 1 | satou | | 2 | suzuki | | 3 | takahashi | | 4 | tanaka | | 5 | itou | +----+-----------+ 5 rows in set (0.03 sec)
↑の例では、ほとんどデータが入っていないので、実際は爆速なんですが、仮にコイツが非常に遅かったと仮定して説明しますね
explainの仕方は簡単です、調べたいSQLの前にexplainとくっつけて実行するだけです
すると以下のように表示されます
mysql> explain select * from employee where name="satou"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
上記のカラムもそれぞれ意味があるのですが、取り急ぎ注目したいところはtypeです
こいつがALLかindexになっているとクエリが遅くなる可能性が高いです
ALLは文字通り、全件検索、フルスキャンです
レコードを総なめにしているのでガッツリ遅い、チューニング必至！
indexもパっと見インデックスが効いているような感じがしますが、
フルインデックススキャンという状態、インデックス全体をスキャンするので非常に遅いです
このSQLおっせーんだよなぁ、と思っていて、この2つのどちらかが見つかってしまったらば、
インデックスを追加するか、複雑なクエリだったらチューニングするなどを検討しましょう
typeには他にもあるので、以下にまとめておきます
| typeの種類 | 説明 |
|---|---|
| ALL | フルスキャン、レコード総なめ、チューニング必至 |
| index | フルインデックススキャン、こいつも遅いので要チューニング |
| const | PRIMARY KEYかUNIQUEのインデックスを使って検索している、速いので問題ない |
| eq_ref | ↑のと類似、JOIN時に使われているとeq_ref |
| ref | ユニークじゃないインデックスを使って検索が行われた時 |
| range | インデックスが使われた範囲検索 |
以降はインデックスを追加して、どう変わっていくかを説明していきます
インデックスを貼ってみる
上述の例でemployeeのtypeがALLになっていました
nameカラムにはインデックスは全く貼られていないので、フルスキャンになっています
試しにnameカラムにインデックスを貼ってみましょう
コマンドは↓
ALTER TABLE `employee` ADD INDEX index_1(`name`);
index_1と表記しているところはインデックスの名前です、ここはお好きな名前で。
では実際に貼る前後でインデックスを確認しながら実行します
-- （１）貼る前 mysql> show index from employee; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employee | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) -- （２）インデックスを貼る mysql> ALTER TABLE `employee` ADD INDEX index_1(`name`); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 -- （３）貼った後 mysql> show index from employee; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employee | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | employee | 1 | index_1 | 1 | name | A | 4 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
Key_nameがindex_1、Column_nameがnameのインデックスが増えています。
これを踏まえて先ほどのtypeがALLだったSQLのexplainをもっかい見てみます
mysql> explain select * from employee where name="satou"; +----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+ | 1 | SIMPLE | employee | ref | index_1 | index_1 | 767 | const | 1 | Using where; Using index | +----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
typeがrefになりましたね
ユニークでないインデックスを使って検索されることを意味しています
インデックス素晴らしいやん、ガンガン貼りまくるでぇ～
みたいな先走ってしまうあなたのために、最後にインデックスを貼る際の注意点を書いておきます
インデックスを貼るときの注意点
一般にインデックスを貼ると以下のデメリットが発生します
- インデックス用のデータを確保するため、データ量が増大する
- INSERTなどの更新処理が遅くなる
の2点です
1つ目は、考えてみれば当たり前ですね
書籍や辞書などの本を考えてみれば分かりやすいかもしれません
例えば、プログラミング関連の書籍で、変数や型などの目次があったら、目次に沿って該当ページにたどり着きやすいですよね
関数名の索引などが巻末にあったとしたら、調べたい関数にサッと辿り着けます
ただ、プログラミングにあまり関係のない単語、例えば接続詞だったりなんでもない日本語だったり、
が巻末索引に用意されていたらどうですかね？特に調べたくもないので、その索引ページは見ないですよね
でも、索引のページは必要なので、本が分厚くなってしまいます
2つ目も似たような例えで分かりやすいでしょうか
新たにレコードを追加するということは本のページが増えるのと似ています
追加されたページに索引に関する単語があれば巻末の索引も追加修正が必要になるでしょう
なので、ガンガンに更新されるようなテーブルであれば逆に遅くなる可能性もあるので注意が必要です
また、インデックスを追加すべきものについても、
一概には言い切れないのですが、一般的に以下のように言われています
- 一意性が高いカラムに設定する
- 表が大規模
- WHERE句やJOIN句で頻繁に使用されるカラムに設定する
- 外部キーに使われる
あたりでしょうか。
- 一意性が高いカラムに設定する
は、例えば、男・女の2種類が50%・50%で格納されているカラムがあったとしても
インデックスではレコードを特定することがほぼ出来ません
なので、よりユニークになっている（一意性が高い）カラムに設定しましょう
- 表が大規模
インデックスは小規模なテーブルには設定しなくてもほぼ問題ないです
ただ、大規模だとデータ量もその分多くなるので、その点は留意です
- WHERE句やJOIN句で頻繁に使用されるカラムに設定する
- 外部キーに使われる
WHERE句などの絞りこみなどで頻繁に使用されているものは設定しておくのがよいです
