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

一尘不染

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

mysql

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

select * from `ratings` order by id limit 499500, 500

每个查询都需要一分钟以上的时间，但是当我将表放到1万条记录中时，该查询就会快速执行。

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

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

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

阅读 270

2020-05-17

共1个答案

一尘不染

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

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

2020-05-17