MySQL语句执行需要超过一分钟的时间?mysql-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

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

保持可爱mmm 2020-05-17 12:34:14 24

我正在一个数据库很大的网站上工作。当时表中有1百万条记录。当我执行查询时,这将花费太多时间来执行。以下是一个示例查询:

select * from ratings order by id limit 499500, 500 每个查询都需要一分钟以上的时间,但是当我将表放到1万条记录中时,该查询就会快速执行。

正如我所读过的,在一个表中有一百万条记录没有问题,因为在数据库表中没有大记录的问题。

我已经通过堆栈溢出问题在表中使用了ID索引,如何向MySQL表添加索引?,但仍然有同样的问题。

***我正在为该项目使用CodeIgniter。

关系型数据库 MySQL 数据库 索引
分享到
取消 提交回答
全部回答(1)
  • 保持可爱mmm
    2020-05-17 12:36:49

    请注意,这并不是建议您过一会儿再使用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

    0 0
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题