😂
その SQL、インデックス効いてないかも？
SQLのパフォーマンスに大きく関わってくるインデックス。
SQLの書き方によってはインデックスが効かないケースがあるようです。
今回はSQLを実行しながら、気をつけるべきSQLを検証していきます。
意図せず遅いSQLを実行してしまわないように気をつけましょう。
インデックスについては他の記事でも書いていますのでよろしければ見てみてください。
動作確認にはこのようなテーブルを用いました。
mysql> desc users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
+-------+--------------+------+-----+---------+----------------+
インデックスを持つ文字列型のname列と数値型のage列を使ってインデックスが効いているかどうかを確認していきます。
mysql> show index from users;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 120 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | age_index | 1 | age | A | 71 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | name_index | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
※ MySQL8.0.33 を利用しています
自分の環境とは異なる場合（バージョンが異なる場合や異なるDBMSを利用している場合）は同じような動作にならないケースもあると思います。
EXPLAIN を利用して実行計画を確認
SQLにインデックスが効いているかどうかは、EXPLAINで確認していきます。
EXPLAINはSQL実行計画の情報を教えてくれます。
今回はひとまずインデックスが効いているかどうかとどの列に対して実行されているかが分かればよいので、
typeとkeyにだけ注目すればよさそうです。
type: クエリがどのようにテーブルからデータを取得するか
mysql> explain select * from users where ...;
# インデックス効いてない（フルテーブルスキャン）
+------+
| type |
+------+
| ALL |
+------+
# インデックス効いてる
+-------+
| type |
+-------+
| range |
+-------+
+------+
| type |
+------+
| ref |
+------+
key: データ取得の際に利用されたインデックス
mysql> explain select * from users where ...;
# name 列の name_index が利用されている
+------------+
| key |
+------------+
| name_index |
+------------+
# age 列の age_index が利用されている
+------------+
| key |
+------------+
| age_index |
+------------+
EXPLAINについてもう少し詳しく知りたい場合はMySQLのドキュメントを読むことをおすすめします。
■ インデックスが効かない SQL
◎ インデックス列に演算を行っている
インデックスを作成した列は「素」の状態で用いるのが原則。
演算を行ってしまうとその列の値に対してインデックスが効かなくなってしまいます。
mysql> explain select * from users where age*1.1 > 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 120 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
◯ 対処法
式を変形させて、列に対して演算を行わないようにする。
explain select * from users where age > 100/1.1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | age_index | age_index | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
◎ インデックス列に対して SQL 関数を適用している
演算同様インデックスを作成した列は「素」の状態で用いるのが原則なので、
インデックス列に対して SQL 関数を実行してもインデックスが効いてくれません。
explain select * from users where substr(name, 1, 1)='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 120 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
◯ 対処法
同様の処理を SQL 関数を利用しない形に修正する必要があります。
explain select * from users where name like 'a%';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | name_index | name_index | 403 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
◎ 否定形を利用している
否定形 <>, != を用いるとインデックスが効きません。
これは、利用したとしても検索範囲が広すぎて効果を発揮しないからのようです。
explain select * from users where age <> 20;
explain select * from users where age != 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | age_index | NULL | NULL | NULL | 120 | 93.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
◎ 後方一致、中間一致のLIKEを利用している
LIKEを利用する際は、前方一致検索の場合のみにインデックスが適用されるようです。
❌ LIKE '%文字列'
❌ LIKE '%文字列%'
mysql> explain select * from users where name like '%a';
mysql> explain select * from users where name like '%a%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 120 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
⭕ LIKE '文字列%'
mysql> explain select * from users where name like 'a%';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | name_index | name_index | 403 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
◎ 暗黙の型変換を行っている
インデックスがある列のデータ型が暗黙的に型変換されてしまうとインデックスが効きません。
下記の例では文字列型のnameが暗黙的に数値型に変換されてしまっており、インデックスが効いてません。
explain select * from users where name = 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | name_index | NULL | NULL | NULL | 120 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
◯ 対処法
これを回避するためには、インデックスのある列が型変換されないように修正する必要があります。
# 条件に使用している値を列のデータ型に合わせる
mysql> explain select * from users where name = '10';
mysql> explain select * from users where name = cast(10 as char(2));
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | name_index | name_index | 403 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
■ DBMS によりインデックスが効かないもの
今回自分の実行環境MySQL8.0.33ではインデックスが適用された。
しかし、バージョンが異なる場合や異なるDBMSを利用している場合はインデックスが適用されないケースがあるものを２つ紹介します。
◎ IS NULL, IS NOT NULL を使っている
explain select * from users where name is null;
explain select * from users where name is not null;
MySQL のドキュメントには、
MySQL は、インデックスと範囲を使用して、IS NULL を含む NULL を検索できます。
と書いてありました。
◎ OR を利用している
explain select * from users where age=20 or age=30;
これは、INを利用することで回避できるようです。
explain select * from users where age in(20, 30);
参考資料
Discussion