为什么在查询中使用LIMIT时MySQL变慢？

Question

我试图弄清楚为什么我的查询之一变慢以及如何解决它，但是我对结果有些困惑。

我有一个orders约80列和775179行的表，并且正在执行以下请求：

SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC LIMIT 200

在4.5秒内返回38行

删除时，ORDER BY我得到了很好的改进：

SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL LIMIT 200

0.30秒内38行

但是，如果在LIMIT不触摸的情况下删除，ORDER BY我会得到更好的结果：

SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC

0.10s（??）中的38行

为什么我的LIMIT这么饿？

继续前进

在尝试发送答案之前，我尝试了一些尝试，并注意到我有一个索引creation_date（是datetime），因此我删除了它，并且第一个查询现在以0.10s的速度运行。这是为什么
？

编辑

很好的猜测，我在where的其他列上有索引。

mysql> explain SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC LIMIT 200;
+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys          | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | index | id_state_idx,id_mp_idx | creation_date | 5       | NULL | 1719 | Using where |
+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+

设置1行（0.00秒）

mysql> explain SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC;
+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+
| id | select_type | table  | type  | possible_keys          | key       | key_len | ref  | rows  | Extra                                              |
+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+
|  1 | SIMPLE      | orders | range | id_state_idx,id_mp_idx | id_mp_idx | 3       | NULL | 87502 | Using index condition; Using where; Using filesort |
+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+

Answer 1

索引不一定能提高性能。为了更好地了解正在发生的事情，如果您explain为不同的查询包括了，这将有所帮助。

我最好的猜测是您有一个索引，id_state甚至id_state, id_mp可以用来满足该where子句。如果是这样，则不order by使用的第一个查询将使用此索引。它应该很快。即使没有索引，这也需要对orders表中的页面进行顺序扫描，这仍然可以非常快。

然后当您添加索引时creation_date，MySQL决定使用该索引代替order by。这需要读取索引中的每一行，然后获取相应的数据页以检查where条件并返回列（如果存在匹配项）。该读取效率极低，因为它不是按“页面”顺序，而是按索引指定的顺序。随机读取可能效率很低。

更糟糕的是，即使您有一个limit，您仍然必须读取 整个
表，因为需要整个结果集。尽管您已经保存了38条记录的排序，但是您创建了一个效率非常低下的查询。

顺便说一句，如果orders表无法容纳在可用内存中，这种情况将变得更加严重。然后，您有一个称为“崩溃”的条件，其中每个新记录都倾向于生成一个新的I /
O读取。因此，如果一个页面上有100条记录，则该页面可能必须被读取100次。

通过在上添加索引，可以使所有这些查询的运行速度更快orders(id_state, id_mp, creation_date)。该where子句将使用前两列，而order by则将使用最后两列。