実行計画
実行計画のとり方
SQLの前に EXPLAIN をつける
実行計画の見方
フルスキャン
例えば、PRIMARYでないカラムを条件としたSELECT文で実施。
テーブルに含まれているレコードを全部読み取る。
また、possible_keys, keys がnullかつ typeがALL
mysql > EXPLAIN SELECT id FROM pet WHERE price='10000000';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pet | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
レンジスキャン
- primary keyを条件としたSELECT文で実施。
- テーブルの一部のレコードにのみアクセスする。
- keyに
PRIMARY、typeにrange
>EXPLAIN SELECT id FROM pet WHERE id between '1020' AND '1027';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | pet | NULL | range | PRIMARY | PRIMARY | 100 | NULL | 8 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
インデックス
インデックスを作ってみる
インデックス前
フルスキャンなことがわかる
EXPLAIN SELECT id FROM pet WHERE price between '100000' AND '20000';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pet | NULL | ALL | NULL | NULL | NULL | NULL | 51 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
インデックスを作成後
- インデックスを作る
> CREATE INDEX p_index ON test(price);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
- インデックスを貼ったあとの実行計画
>EXPLAIN SELECT id FROM pet WHERE price between '100000' AND '200000';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | pet | NULL | range | p_index | p_index | 5 | NULL | 4 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
- type がrange, possible_keysがindexになっている->indexがオプティマイザによって採用されていることがわかる
Index
なぜIndexを使うのか
- データ量が大きくなると処理速度が向上するから
- Indexを貼ることで線形探索から2分探索になるから。2分探索はデータ量の増加について対数的に線形探索に対して有利になる。
- テーブルのデータに影響を与えない
- SQL文に変更が必要でない。
注意点
- Indexを作りすぎると、Indexの更新自体に時間がかかる。
- 意図したものとは異なるIndexがオプティマイザによって利用される懸念がある。
- Indexはカーディナリティの高い列に作る
- カーディナリティ：値の分散度。例えば都道府県は47パターンしかないため、カーディナリティは低い。
参考
- 『おうちで学べる データベースんの基本』 ミックら著
- MySQLのMVCC - Qiita