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

小虫子在刷牙

我试图弄清楚为什么我的查询之一变慢以及如何解决它，但是我对结果有些困惑。
我有一个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 |
+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+

解决方案:


            索引不一定能提高性能。为了更好地了解正在发生的事情，如果您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则将使用最后两列。

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

小虫子在刷牙 LV1