INDEXを追加することでWHERE句でのレコードの絞り込みが速くなることは、SQLを少しでも学んだことのある人なら当たり前に知っていることだと思う。
システムを運用していく期間が長くなればなるほどレコード数は膨大になり、WHERE句で絞り込んだとしても結果として返されるレコード数が多くなってしまうこともある。
単純に絞り込んだ結果を並び順関係なく出力させる場合は問題ないが、WEBシステムやアプリなどでは、登録された日付が近いもの順でソートしたい場合や、IDが若い順でソートしたい場合など、出力結果をソートしたい場合が多々ある。
そんな時はクエリにORDER BY句を記述することで対処することがあると思うが、実はこのORDER BY句によるソートがボトルネックの原因になってしまうことがある。
WHERE句で指定しているカラムにINDEXが張ってある場合
以前の記事で利用したsampleテーブルにさらにレコードとrankカラムを追加し、合計1100000レコード用意した。
mysql$ select count(*) from sample;
+----------+
| count(*) |
+----------+
| 1100000 |
+----------+
1 row in set (0.25 sec)
mysql$ select SQL_NO_CACHE * from sample;
+---------+--------+------+
| id | name | rank |
+---------+--------+------+
| 1 | AAAAA | A |
| 2 | BAAAA | A |
| 3 | CAAAA | A |
| ⋮ ⋮ |
| 1131068 | H | NULL |
| 1131069 | I | NULL |
| 1131070 | | NULL |
+---------+--------+------+
1100000 rows in set (0.89 sec)
こんな感じのテーブルが今回の検証対象。rankカラムはほとんどNULLで、’A’, ‘B’, ‘C’が10000レコードずつ入っている。
では、このテーブルからrankカラムにindexが張られている状態で、rankが’A’のレコードだけ絞ってみる。
結果が多いので、今回はlimit 10をつける。
mysql$ explain select SQL_NO_CACHE * from sample where rank = 'A' limit 10;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | sample | NULL | ref | rank_inx | rank_inx | 4 | const | 197082 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql$ select SQL_NO_CACHE * from sample where rank = 'A' limit 10;
+----+-------+------+
| id | name | rank |
+----+-------+------+
| 1 | AAAAA | A |
| 2 | BAAAA | A |
| 3 | CAAAA | A |
| 4 | DAAAA | A |
| 5 | EAAAA | A |
| 6 | FAAAA | A |
| 7 | GAAAA | A |
| 8 | HAAAA | A |
| 9 | IAAAA | A |
| 10 | AAAA | A |
+----+-------+------+
10 rows in set (0.00 sec)
INDEXが効いた状態では即レスポンスが返ってくる。
ではこのクエリに、さらにnameカラムの降順で並べるようORDER BY句を加えてみる。
mysql$ select SQL_NO_CACHE * from sample where rank = 'A' order by name desc limit 10;
+-------+-------+------+
| id | name | rank |
+-------+-------+------+
| 88889 | IIIII | A |
| 78889 | IIIIH | A |
| 68889 | IIIIG | A |
| 58889 | IIIIF | A |
| 48889 | IIIIE | A |
| 38889 | IIIID | A |
| 28889 | IIIIC | A |
| 18889 | IIIIB | A |
| 88890 | IIII | A |
| 88899 | IIII | A |
+-------+-------+------+
10 rows in set (0.21 sec)
レスポンスが遅くなった。レコード数が大した量ではないので、体感できるほどの差は出ていないが、大量にアクセスのあるシステムだった場合は負荷がかかりさらに開くこともある。
この問題を改善するには、WHERE句でもORDER BY句でも利用できるマルチカラムインデックスの作成を検討する。
ORDER BYでもINDEXが効くようマルチカラムインデックスを作成
今回のクエリを元にINDEXを張る場合を考える。
select SQL_NO_CACHE * from sample where rank = 'A' order by name desc limit 10;
この場合はwhere句のrankとorder by句のnameを含むINDEXを作成すれば良いが、指定するカラムの順番が正しくなければ効果を発揮しない。
マルチカラムインデックスはカラムの順番に注意
mysql$ alter table sample add index name_rank(name, rank);
Query OK, 0 rows affected (3.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
この場合、where句、orderby句の両方にindexが適用されることはない。
※ MySQL5.7では改善されているかも
クエリの評価順序はwhere→order byの順番になるので、indexを作る際はwhere句で使われるカラムから指定する必要がある。
mysql$ alter table sample add index rank_name(rank, name);
Query OK, 0 rows affected (4.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql$ explain select SQL_NO_CACHE * from sample where rank = 'A' order by name desc limit 10;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+--------+----------+--------------------------+
| 1 | SIMPLE | sample | NULL | ref | rank_name | rank_name | 4 | const | 201630 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
クエリを投げてみる。
mysql$ select SQL_NO_CACHE * from sample where rank = 'A' order by name desc limit 10;
+-------+-------+------+
| id | name | rank |
+-------+-------+------+
| 88889 | IIIII | A |
| 78889 | IIIIH | A |
| 68889 | IIIIG | A |
| 58889 | IIIIF | A |
| 48889 | IIIIE | A |
| 38889 | IIIID | A |
| 28889 | IIIIC | A |
| 18889 | IIIIB | A |
| 98889 | IIII | A |
| 89889 | IIII | A |
+-------+-------+------+
10 rows in set (0.00 sec)
即返ってくるようになった。
今回の記事は自社プロダクトで利用しているAurora MySQL 5.6.10aを想定して書いているが、最新のMySQL5.7では諸々再現できなかったので、Optimizerの改善でかなり状況が変わっているかもしれない。
コメント