MySQL语句执行需要超过一分钟的时间?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子句。

不能排除出现僵局的可能性。来源：stack overflow