MySQLでORDER BYの項目にインデックスを適用してみる
MySQLでORDER BYの項目にインデックスを適用してみる
今回使用するテーブル
サンプルのテーブルに、レコードが1000万件入った状態。
pointには1〜100、 typeには1〜10、flagには0,1が入っている。
mysql> show create table sample; +--------+---------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------+ | sample | CREATE TABLE `sample` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `point` int(11) NOT NULL, `type` smallint(6) NOT NULL, `flag` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10026856 DEFAULT CHARSET=utf8 | +--------+---------------------------------------------------+
mysql> select count(*) from sample; +----------+ | count(*) | +----------+ | 10000000 | +----------+
mysql> select * from sample limit 20; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 24 | 5 | 1 | | 2 | suzuki | 75 | 9 | 1 | | 3 | takahashi | 5 | 6 | 0 | | 4 | tanaka | 90 | 10 | 0 | | 5 | ito | 41 | 2 | 1 | | 6 | yamamoto | 99 | 9 | 0 | | 7 | watanabe | 96 | 6 | 1 | | 8 | nakamura | 90 | 8 | 1 | | 9 | kobayashi | 66 | 4 | 1 | | 10 | kato | 18 | 4 | 0 | | 11 | sato | 70 | 1 | 0 | | 12 | suzuki | 44 | 9 | 1 | | 13 | takahashi | 33 | 7 | 0 | | 14 | tanaka | 4 | 10 | 1 | | 15 | ito | 41 | 1 | 0 | | 16 | yamamoto | 13 | 5 | 1 | | 17 | watanabe | 13 | 10 | 0 | | 18 | nakamura | 26 | 7 | 1 | | 19 | kobayashi | 31 | 5 | 1 | | 20 | kato | 36 | 1 | 0 | +----+-----------+-------+------+------+
このテーブルを使って、order byのインデックスを試してみる。
order byの項目でインデックスを作成してみる
1件とるだけなら早い検索も
mysql> select * from sample limit 1; +----+------+-------+------+------+ | id | name | point | type | flag | +----+------+-------+------+------+ | 1 | sato | 24 | 5 | 1 | +----+------+-------+------+------+ 1 row in set (0.00 sec)
order byをつけるととたんに遅くなる。
mysql> select * from sample order by point limit 1; +----+----------+-------+------+------+ | id | name | point | type | flag | +----+----------+-------+------+------+ | 78 | nakamura | 1 | 10 | 1 | +----+----------+-------+------+------+ 1 row in set (5.08 sec)
インデックスを追加すると、
mysql> alter table sample add index idx1 (point);
0.01秒で検索できるようになった。
mysql> select * from sample order by point limit 1; +----+----------+-------+------+------+ | id | name | point | type | flag | +----+----------+-------+------+------+ | 78 | nakamura | 1 | 10 | 1 | +----+----------+-------+------+------+ 1 row in set (0.01 sec)
explainの結果を見ても、インデックスが使われているのが分かる。
mysql> explain select * from sample order by point limit 1; +----+-------------+--------+-------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | sample | index | NULL | idx1 | 4 | NULL | 1 | | +----+-------------+--------+-------+---------------+------+---------+------+------+-------+
order byの一部のみインデックスを適用することはできない
pointのインデックスが作成された状態で、pointとtypeをorder byに指定すると
インデックスが効かない。
mysql> select * from sample order by point, type limit 1; +-----+-----------+-------+------+------+ | id | name | point | type | flag | +-----+-----------+-------+------+------+ | 499 | kobayashi | 1 | 1 | 0 | +-----+-----------+-------+------+------+ 1 row in set (5.30 sec)
explainの結果を見ても、インデックスが効いてないのがわかる。
mysql> explain select * from sample order by point, type limit 1; +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+ | 1 | SIMPLE | sample | ALL | NULL | NULL | NULL | NULL | 10000571 | Using filesort | +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+
pointとtypeへの複合インデックスを作成する。
mysql> alter table sample add index idx2 (point, type);
mysql> show index from sample; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sample | 0 | PRIMARY | 1 | id | A | 10000461 | NULL | NULL | | BTREE | | | sample | 1 | idx1 | 1 | point | A | 18 | NULL | NULL | | BTREE | | | sample | 1 | idx2 | 1 | point | A | 18 | NULL | NULL | | BTREE | | | sample | 1 | idx2 | 2 | type | A | 2650 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
0.01秒で検索できるようになった。
mysql> select * from sample order by point, type limit 1; +-----+-----------+-------+------+------+ | id | name | point | type | flag | +-----+-----------+-------+------+------+ | 499 | kobayashi | 1 | 1 | 0 | +-----+-----------+-------+------+------+ 1 row in set (0.01 sec)
explainの結果を見ても、インデックスが効いてるのがわかる。
mysql> explain select * from sample order by point, type limit 1; +----+-------------+--------+-------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | sample | index | NULL | idx2 | 6 | NULL | 1 | | +----+-------------+--------+-------+---------------+------+---------+------+------+-------+
複合インデックスの一部をorder byに適用することは可
一旦、pointのみのインデックスを削除して、
pointとtypeの複合インデックスのみにする。
mysql> alter table sample drop index idx1;
mysql> show index from sample; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sample | 0 | PRIMARY | 1 | id | A | 10000461 | NULL | NULL | | BTREE | | | sample | 1 | idx2 | 1 | point | A | 18 | NULL | NULL | | BTREE | | | sample | 1 | idx2 | 2 | type | A | 5154 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
この状態で、order byにpointのみを指定してもインデックスは効く。
mysql> select * from sample order by point limit 1; +-----+-----------+-------+------+------+ | id | name | point | type | flag | +-----+-----------+-------+------+------+ | 499 | kobayashi | 1 | 1 | 0 | +-----+-----------+-------+------+------+ 1 row in set (0.00 sec)
mysql> explain select * from sample order by point limit 1; +----+-------------+--------+-------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | sample | index | NULL | idx2 | 6 | NULL | 1 | | +----+-------------+--------+-------+---------------+------+---------+------+------+-------+
order byの順にインデックスが作成されていないと効かない
複合インデックスが作成されていても、
複合インデックスの順にorder byが指定されないとインデックスは効かない。
point,typeの複合インデックスがある状態で、
order byにtype,pointと指定しても、インデックスは効かない。
mysql> select * from sample order by type, point limit 1; +-----+-----------+-------+------+------+ | id | name | point | type | flag | +-----+-----------+-------+------+------+ | 499 | kobayashi | 1 | 1 | 0 | +-----+-----------+-------+------+------+ 1 row in set (5.09 sec)
mysql> explain select * from sample order by type, point limit 1; +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+ | 1 | SIMPLE | sample | ALL | NULL | NULL | NULL | NULL | 10000363 | Using filesort | +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+
ASCとDESCが混在しているとインデックスが効かない
複合インデックスが作成されていても、
ASCとDESCが混在しているとインデックスは効かない。
mysql> select * from sample order by point asc, type desc limit 1; +----+----------+-------+------+------+ | id | name | point | type | flag | +----+----------+-------+------+------+ | 78 | nakamura | 1 | 10 | 1 | +----+----------+-------+------+------+ 1 row in set (5.08 sec)
両方descは問題ない。
mysql> select * from sample order by point desc, type desc limit 1; +---------+-----------+-------+------+------+ | id | name | point | type | flag | +---------+-----------+-------+------+------+ | 9999299 | kobayashi | 100 | 10 | 1 | +---------+-----------+-------+------+------+ 1 row in set (0.00 sec)
whereとorder byの項目に複合インデックス
一旦、インデックスを削除し、pointのインデックスのみにする。
mysql> alter table sample drop index idx2; mysql> alter table sample add index idx1 (point); mysql> show index from sample; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sample | 0 | PRIMARY | 1 | id | A | 10000461 | NULL | NULL | | BTREE | | | sample | 1 | idx1 | 1 | point | A | 18 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
この状態で、検索条件にpointを指定して件数をカウントすると、
当然のことながらインデックスが効く。
mysql> select count(*) from sample where point = 100; +----------+ | count(*) | +----------+ | 99450 | +----------+ 1 row in set (0.03 sec)
order byにtypeを指定すると、ソートが行われる分、少し検索に時間がかかるようになる。
mysql> select count(*) from sample where point = 100 order by type; +----------+ | count(*) | +----------+ | 99450 | +----------+ 1 row in set (0.29 sec)
pointとtypeの複合インデックスを作成すると、
mysql> alter table sample add index idx2 (point, type);
検索がすぐに終わる。
mysql> select count(*) from sample where point = 100 order by type; +----------+ | count(*) | +----------+ | 99450 | +----------+ 1 row in set (0.03 sec)
whereを指定した場合のインデックスの挙動がよく分からない
複合インデックスを削除して、pointのインデックスのみにする。
mysql> alter table sample drop index idx2; mysql> show index from sample; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sample | 0 | PRIMARY | 1 | id | A | 10000461 | NULL | NULL | | BTREE | | | sample | 1 | idx1 | 1 | point | A | 18 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
この状態で、
pointでソートして件数をカウントすると、約２秒ほどかかる。
pointのインデックスが効いている。
mysql> select count(*) from sample order by point; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (2.22 sec)
mysql> explain select count(*) from sample order by point; +----+-------------+--------+-------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | sample | index | NULL | idx1 | 4 | NULL | 10000402 | Using index | +----+-------------+--------+-------+---------------+------+---------+------+----------+-------------+ 1 row in set (0.00 sec)
条件にflag = 1を指定して検索すると、約３秒ほどかかる。
そして、インデックスは何も効かなくなる。
mysql> select count(*) from sample where flag = 1 order by point; +----------+ | count(*) | +----------+ | 5001302 | +----------+ 1 row in set (3.45 sec)
mysql> explain select count(*) from sample where flag = 1 order by point; +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | sample | ALL | NULL | NULL | NULL | NULL | 10000402 | Using where | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ 1 row in set (0.00 sec)
次に、flagのインデックスを作成する。
mysql> alter table sample add index idx3 (flag); mysql> show index from sample; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sample | 0 | PRIMARY | 1 | id | A | 10000461 | NULL | NULL | | BTREE | | | sample | 1 | idx1 | 1 | point | A | 2044 | NULL | NULL | | BTREE | | | sample | 1 | idx3 | 1 | flag | A | 18 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
前回と同様に、条件にflag = 1を指定して検索すると、
なぜか約20秒以上かかるようになった。
mysql> select count(*) from sample where flag = 1 order by point; +----------+ | count(*) | +----------+ | 5001302 | +----------+ 1 row in set (22.55 sec)
インデックスは、flagのインデックスが効いている。
mysql> explain select count(*) from sample where flag = 1 order by point; +----+-------------+--------+------+---------------+------+---------+-------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------+---------+-------+ | 1 | SIMPLE | sample | ref | idx3 | idx3 | 1 | const | 5000230 | | +----+-------------+--------+------+---------------+------+---------+-------+---------+-------+
どういう仕様なんだろうか？
よく分からない。
【参考】 MySQL :: MySQL 4.1 リファレンスマニュアル :: 5.2.8 MySQL による ORDER BY の最適化 http://dev.mysql.com/doc/refman/4.1/ja/order-by-optimisation.html 漢(オトコ)のコンピュータ道: オトコのソートテクニック2008 http://nippondanji.blogspot.jp/2008/12/2008.html
