MySQL の order by と index
MySQLの order by と index の仕組みがわからなくなったので調査。
前提の自分の仮定
- MySQLは降順インデックスをサポートしないので order by desc にインデックスを使用できない
(user_id, point)という複合インデックスがあれば、where user_id = ? order by point ascというクエリはインデックスを最大限に使用できる
準備
MySQLのバージョンは 5.1.61
CREATE TABLE `sample` ( `id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `point` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `i1` (`user_id`,`point`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> select count(*) from sample; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.22 sec)
試す
mysql> explain select * from sample where user_id = 50; +----+-------------+--------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | sample | ref | i1 | i1 | 4 | const | 98 | Using index | +----+-------------+--------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
user_id を where 句に指定すると当然i1インデックスを使用してselectできる。
key_lenはint型なので4
mysql> explain select * from sample where user_id = 50 and point = 1000; +----+-------------+--------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | sample | ref | i1 | i1 | 8 | const,const | 1 | Using index | +----+-------------+--------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
user_id 及び point を指定しても、i1インデックスを最大限利用して
select できる。key_lenが8なので、user_id及びpointまで
インデックスを使用している。
mysql> explain select * from sample order by user_id asc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 10 | Using index | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
user_idをascで order by。limitに10を指定してrowsが10なので
インデックスを使用して絞っていることがわかる。
mysql> explain select * from sample order by user_id desc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 10 | Using index | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
ascでなくdescでも同様。
mysql> explain select * from sample order by user_id asc, point asc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 10 | Using index | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
(user_id, point)の複合キーなので、order by に user_id と pointを
指定しても絞り込める。
mysql> explain select * from sample order by user_id desc, point desc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 10 | Using index | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
こちらもdescでも同様。
mysql> explain select * from sample order by user_id asc, point desc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 1049137 | Using index; Using filesort | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ 1 row in set (0.00 sec)
ascとdescが混合するとインデックスが有効に活用されない。
mysql> explain select * from sample order by user_id desc, point asc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 1049137 | Using index; Using filesort | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ 1 row in set (0.00 sec)
desc asc の順でも同様。
mysql> explain select * from sample order by user_id asc, id asc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 1049137 | Using index; Using filesort | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ 1 row in set (0.00 sec)
pointでなくidをorder by に含めるとインデックスが効かない
mysql> explain select * from sample order by id asc,user_id asc limit 10; +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ | 1 | SIMPLE | sample | index | NULL | i1 | 8 | NULL | 1049137 | Using index; Using filesort | +----+-------------+--------+-------+---------------+------+---------+------+---------+-----------------------------+ 1 row in set (0.00 sec)
order by の順序を逆にしてもしかり。
mysql> explain select * from sample order by id asc limit 10; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | sample | index | NULL | PRIMARY | 4 | NULL | 10 | | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ 1 row in set (0.00 sec)
idをorder by に指定すると、idはPRIMARY KEY なのでそっちの
インデックスが使用される。
mysql> explain select * from sample where user_id = 50 order by point limit 10; +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | sample | ref | i1 | i1 | 4 | const | 98 | Using where; Using index | +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
where 句にuser_id, order by にpointを使用すると、user_idだけ
インデックスを使用して探索される。
mysql> explain select * from sample where user_id = 50 order by point desc limit 10; +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | sample | ref | i1 | i1 | 4 | const | 98 | Using where; Using index | +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
descを使用しても同様の結果。
結果
- MySQLは降順インデックスをサポートしないが order by desc にインデックスを使用できる
(user_id, point)という複合インデックスがあっても、where user_id = ? order by point ascというクエリは user_id 部分までしかインデックスが使用されない
