はじめに
SQLアンチパターンの闇雲インデックスを輪読した際に実際なにが闇雲なんだろうとか、中規模程度のレコード数になるとやはりきちんとSQL知らないとパフォーマンスが上がらないなということで改めてインデックスについて勉強しました。テスト用のテーブルは下記の通りです。
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| test1 | int(11) | YES | MUL | NULL | |
| test2 | int(11) | YES | | NULL | |
| test3 | varchar(255) | YES | MUL | NULL | |
| test4 | varchar(255) | YES | | NULL | |
| test5 | tinyint(1) | YES | MUL | NULL | |
| test6 | tinyint(1) | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tests | 0 | PRIMARY | 1 | id | A | 15679382 | NULL | NULL | | BTREE | | |
| tests | 1 | index_tests_on_test1 | 1 | test1 | A | 7958267 | NULL | NULL | YES | BTREE | | |
| tests | 1 | index_tests_on_test3 | 1 | test3 | A | 6844140 | NULL | NULL | YES | BTREE | | |
| tests | 1 | index_tests_on_test5 | 1 | test5 | A | 1 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
インデックスが有効活用されない例
1. ワイドレンジな検索
インデックスを使ったrange_scanはヒットした件数分データファイルにランダムアクセスします。
つまりヒット数が多くなるとそれだけI/O数も増えインデックスを使ったにも関わらずパフォーマンスが落ちてしまう、これを避けるためMySQLではヒット数が一定の割合を越えるとテーブルをフルスキャンするようになります。
以下range < 200000ではtypeがrangeなのに対して< 2000000ではALLになっています。
mysql> explain select * from tests where 0 <= test1 and test1 < 200000;
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | tests | NULL | range | index_tests_on_test1 | index_tests_on_test1 | 5 | NULL | 665276 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tests where 0 <= test1 and test1 < 2000000;
+----+-------------+-------+------------+------+----------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | tests | NULL | ALL | index_tests_on_test1 | NULL | NULL | NULL | 15839050 | 43.14 | Using where |
+----+-------------+-------+------------+------+----------------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
2. 中間一致、後方一致
インデックスを貼った文字列を検索する場合によくあるパターン。
カラムはa~zのアルファベット順にソートされているため、前方一致以外では検索にインデックスが利用できません。
前方一致以外はフルテーブルスキャンが行われていることがわかります。
mysql> explain select * from tests where test3 like 'abc%';
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tests | NULL | range | index_tests_on_test3 | index_tests_on_test3 | 768 | NULL | 973 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tests where test3 like '%abc%';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | tests | NULL | ALL | NULL | NULL | NULL | NULL | 15839050 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tests where test3 like '%abc';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | tests | NULL | ALL | NULL | NULL | NULL | NULL | 15839050 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3. whereとorder byで異なるカラムを利用
test1 > 1000000だけだとrange。
mysql> explain select count(*) from tests where test1 > 1000000;
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+---------+----------+--------------------------+
| 1 | SIMPLE | tests | NULL | range | index_tests_on_test1 | index_tests_on_test1 | 5 | NULL | 7919525 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
下記が追加でtest2に対してorder byをかけた例、
mysql> explain select count(*) from tests where test1 > 1000000 order by test2;
+----+-------------+-------+------------+------+----------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | tests | NULL | ALL | index_tests_on_test1 | NULL | NULL | NULL | 15839050 | 50.00 | Using where |
+----+-------------+-------+------------+------+----------------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
これについては正直完全に理解しているわけではないので有識者の方いらっしゃったら捕捉をお願いしたいのですが、where,orderに異なるインデックスカラムが指定されているとき、どちらのインデックスが優先的に利用されるかはオプティマイザにより決定されorderが優先される場合はフルスキャンになるという認識です。
4. 闇雲低カーディナリティインデックス
カーディナリティ（多様性）が低いカラムに対して闇雲にインデックスが貼られているパターン。
インデックスはカーディナリティが高いほど絞り込みの効果が大きいので、特にbooleanとかに貼られていると結構意味合いが薄くなる。
下記test5,test6に対してそれぞれselect count(*) from tests where カラム=trueなクエリを発行した例。速度の改善は見られるが効果は低い。
5. 闇雲複合インデックス
sqlちゃんと知っている人なら割と有名なやつですが、闇雲に複合インデックスを貼ってしまったパターンです。
インデックスを張り替えて以下の通りです。
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tests | 0 | PRIMARY | 1 | id | A | 15679382 | NULL | NULL | | BTREE | | |
| tests | 1 | index_tests_on_test5 | 1 | test5 | A | 1618 | NULL | NULL | YES | BTREE | | |
| tests | 1 | index_tests_on_test3 | 1 | test3 | A | 6844140 | NULL | NULL | YES | BTREE | | |
| tests | 1 | multi_index_on_test1_test2 | 1 | test1 | A | 7971266 | NULL | NULL | YES | BTREE | | |
| tests | 1 | multi_index_on_test1_test2 | 2 | test2 | A | 15679382 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
今回の場合test1,test2が複合インデックスになっておりtest1優先。複合インデックスは姓,名のように必ず対になって存在するものに対して貼ると認識していますが、それぞれ別々に本来インデックスマージで取り扱うべきものに複合インデックスを貼ってしまうとアンチパターンになりやすいです。
上記の場合test2単体での検索をすると当然フルスキャンになります。
mysql> explain select * From tests where test2=313131;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | tests | NULL | ALL | NULL | NULL | NULL | NULL | 15679382 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.02 sec)
まとめ
どれもよくありそうなパターンです。近年どのフレームワークでもORMが存在するので発行するSQLに意識が低くなりがちですが、発行されるSQLの実行計画等観察してパフォーマンスをあげていきたいです。時間があるときにどんなときにインデックスを貼るべきかみたいな記事を投稿しようかと思います。