データベースの実行計画とインデックスについて

実行計画

実行計画のとり方

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