読者です 読者をやめる 読者になる 読者になる

まるまるこふこふ

数々の次元が崩壊し、全ての生命が塵と化すのを見てきた。私ほどの闇の心の持ち主でも、そこには何の喜びも無かった。

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_lenint型なので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_len8なので、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_idascで 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)

ascdescが混合するとインデックスが有効に活用されない。

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 部分までしかインデックスが使用されない