MySQL语句执行需要超过一分钟的时间

请注意，这并不是建议您过一会儿再使用MyISAM。我只用它来获取我的ID，最小，最大和计数。因此，请忽略引擎。

create table ratings(   id int auto_increment primary key,    thing int null)engine=MyISAM;insert ratings (thing) values (null),(null),(null),(null),(null),(null),(null),(null),(null);insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;insert ratings (thing) select thing from ratings;

我现在有470万行

select count(*),min(id),max(id) from ratings;+----------+---------+---------+| count(*) | min(id) | max(id) |+----------+---------+---------+|  4718592 |       1 | 4718592 |+----------+---------+---------+select * from `ratings` order by id limit 499500, 500;-- 1 second on a dumpy laptop

。

explain select * from `ratings` order by id limit 499500, 500;+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+|  1 | SIMPLE      | ratings | ALL  | NULL          | NULL | NULL    | NULL | 4718592 | Using filesort |+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

。

explain select * from `ratings` where id>=499501 limit 500;+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra      |+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+|  1 | SIMPLE      | ratings | range | PRIMARY       | PRIMARY | 4       | NULL | 4198581 | Using index condition |+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+

故事的寓意可能是使用where子句。

不能排除出现僵局的可能性。