MySQL 语句执行时间超过一分钟 [英] MySQL statement takes more than minute to execute

问题描述

我正在一个数据库很大的网站上工作.当时表中有 100 万条记录.当我执行查询时，执行时间太长.下面给出了一个示例查询:

I am working on a website in which the database is huge. 1 million records are in the table at the time. When I perform a query then it takes too much time to execute. One sample query is given below:

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

每个查询需要一分钟以上，但是当我将表删除到 10,000 条记录时，此查询执行得很快.

Every query takes more than one minute, but when I drop the table to 10 thousand records then this query executes fastly.

据我所知，一个表中的 100 万条记录没有问题，因为在数据库表中，不存在大记录的问题.

As I have read that there is not problem for 1 million records in a table because in database tables, there is not problem of big records.

我在 Stack Overflow 问题的帮助下使用了表中 id 的索引 如何向 MySQL 表添加索引?，但我还是遇到了同样的问题.

I have used indexing of id in the table by the help of Stack Overflow question How do I add indices to MySQL tables?, but still I got the same problem.

*** 我正在为项目使用 CodeIgniter.

*** I am using CodeIgniter for the project.

推荐答案

注意，这不是建议使用 MyISAM.我使用它只是为了让我的 ids、min、max 和 count 对齐.所以请忽略引擎.

Note, this is not suggesting for a minute to use MyISAM. I use that only to get my ids, min,max, and count to line up. So ignore the engine, please.

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 子句.

Moral of the story may be to use a where clause.

不能排除死锁的可能性.

One cannot rule out the possibility of a deadlock.

这篇关于MySQL 语句执行时间超过一分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！