MySQL语句执行需要超过一分钟 [英] MySQL statement takes more than minute to execute
问题描述
我正在一个数据库很大的网站上工作.当时表中有1百万条记录.当我执行查询时,这将花费太多时间来执行.下面是一个示例查询:
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
每个查询都需要一分钟以上的时间,但是当我将表放到1万条记录中时,该查询就会快速执行.
Every query takes more than one minute, but when I drop the table to 10 thousand records then this query executes fastly.
正如我所读过的那样,表中的一百万条记录没有问题,因为在数据库表中没有大记录的问题.
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.
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.我只使用它来获取我的ID,最小值,最大值和计数以排队.所以请忽略引擎.
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屋!