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

Question

我正在开发一个网站，其中数据库非常庞大。当前表格中有100万条记录。当我执行查询时，需要很长时间才能完成。以下是一个示例查询：

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

每个查询需要超过一分钟的时间，但是当我将表中的记录减少到10,000条时，这个查询就可以快速执行。

据我所知，在数据库表中，1百万条记录并不会有问题，因为表中的大记录并不会成为问题。

我已经按照Stack Overflow上的问题如何向MySQL表中添加索引？对表中的id进行了索引，但仍然遇到了同样的问题。

***我正在使用CodeIgniter进行项目开发。

- M Razwan

---

你的表中是否使用了索引？此外，对于MySQL来说，100万条记录并不算很大。 - D4V1D

索引是否就位？您正在获取500行，而不是100万行（或一半）。 - Drew

是的，使用表索引是一个好的编程实践。 - JCm

你能给出查询的执行计划快照吗？这对我们所有人都会有所帮助。 - Abhay Chauhan

SELECT *存在问题，ORDER BY LIMIT X, y 只能通过扫描来完成，必须在排序之前获取所有行。 - Mihai

Answer 1

请注意，这并不意味着建议使用MyISAM。我只是使用它来使我的ids、min、max和count对齐。所以请忽略引擎。

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;

我现在有4.7百万行

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子句。 不能排除死锁的可能性。

- Drew

---

通过使用where条件和order by语句，它可以正常工作。谢谢。 - M Razwan

">=" 只有在 ID 没有间隙的情况下才能工作！（是的，在这种情况下，InnoDB 和 MyISAM 应该工作类似。） - Rick James

@RickJames 选择了该引擎，因为在自我插入期间InnoDB会导致范围分配间隙。 - Drew

一个人总是可以像这里那样搞乱innodb模式，但我目前基本上不想这么做。 - Drew

或者一个 ALTER TABLE table_name ENGINE=InnoDB; - Drew

Answer 2

清单：

• 在“id”列上创建了按ASC顺序排序的聚集索引（index）
• 从命令行检查您的查询时间，请看这里
• 检查您的PHP代码（我看到您正在使用 CodeIgniter ）。关于您的算法：您是否使用了CodeIgniter数据库库请看这里？
  如果您使用原始查询，请检查您的循环。

另一种方法是尝试使用活动索引进行查询：

select * from `ratings` where id>=456789 limit 500;

- ZenithS

Answer 3

首先检查查询的执行计划，以识别瓶颈并在必要时创建索引。我认为您至少应该在 Id 列上建立索引。

还有许多因素可能影响查询性能：

1. 数据页面的碎片化
2. 表的统计信息未更新
3. 许多请求并行运行

还有其他更多因素....

按照以下链接获取执行计划并识别性能下降因素： http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

如何基于 EXPLAIN 计划优化 MySQL 查询

如果遇到任何问题，请告诉我。

- Abhay Chauhan

Answer 4

检查索引。每个表中应该有一个主键，并且您的查询的where子句中可能存在不属于主索引或聚集索引的属性。

- user2967688

---

我在查询中没有使用where条件。 - M Razwan

Answer 5

如果您正在使用InnoDB引擎来创建表，请将其更改为MyISAM。它对于读操作更快。何时使用MyISAM和InnoDB。

此外，如果您有带有搜索条件的查询，请确保经常使用的列已经建立索引。

- Genoud Magloire

Answer 6

这是 Tuts-plus 的链接，它将帮助您解决问题。

最有可能的解决方案是对数据库进行索引，并且在查询中使用列名而不是 (*)。更好的查询方式

- sapphire code