为什么在查询中使用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行
0 z/ r/ ~/ o6 F7 ?: K: C: k删除时，ORDER BY我得到了很好的改进：
SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL LIMIT 200
0.30秒内38行
, h# B' ]/ i7 k8 d% k$ l4 ~但是，如果在LIMIT不触摸的情况下删除，ORDER BY我会得到更好的结果：
7 v* Z' g3 ?; U: A& t1 J9 oSELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY
creation_date DESC
0.10s（??）中的38行
( }) a4 D7 v  U7 }- z% Y8 i为什么我的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       |
6 r% Z3 X3 @8 p+ v+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | index | id_state_idx,id_mp_idx | creation_date | 5       | NULL | 1719 | Using where |
+ ~0 w' L7 ^+ [: i) X+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
设置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                                              |
( `+ C) x8 b' E0 x) H$ @& [+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+
, d& S5 L7 U5 s/ N. ?- t, n0 Q|  1 | SIMPLE      | orders | range | id_state_idx,id_mp_idx | id_mp_idx | 3       | NULL | 87502 | Using index condition; Using where; Using filesort |
+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+
) k2 X) y+ e0 s2 \! h               
解决方案:
               
9 N: k/ {+ K% x+ G
3 [5 Y+ u* y0 P, X9 B
* M8 B, s/ y. w" d, X4 F                索引不一定能提高性能。为了更好地了解正在发生的事情，如果您explain为不同的查询包括了，这将有所帮助。
我最好的猜测是您有一个索引，id_state甚至id_state, id_mp可以用来满足该where子句。如果是这样，则不order
by使用的第一个查询将使用此索引。它应该很快。即使没有索引，这也需要对orders表中的页面进行顺序扫描，这仍然可以非常快。
/ ^& T9 v- s# V+ a- z& q然后当您添加索引时creation_date，MySQL决定使用该索引代替order
by。这需要读取索引中的每一行，然后获取相应的数据页以检查where条件并返回列（如果存在匹配项）。该读取效率极低，因为它不是按“页面”顺序，而是按索引指定的顺序。随机读取可能效率很低。
更糟糕的是，即使您有一个limit，您仍然必须读取 整个
表，因为需要整个结果集。尽管您已经保存了38条记录的排序，但是您创建了一个效率非常低下的查询。
顺便说一句，如果orders表无法容纳在可用内存中，这种情况将变得更加严重。然后，您有一个称为“崩溃”的条件，其中每个新记录都倾向于生成一个新的I /
) m& s- J' ~: }6 B$ vO读取。因此，如果一个页面上有100条记录，则该页面可能必须被读取100次。
- v7 C5 m, T" o5 h  u* u& ~通过在上添加索引，可以使所有这些查询的运行速度更快orders(id_state, id_mp,
8 \. H; p7 Q- p$ C1 qcreation_date)。该where子句将使用前两列，而order by则将使用最后两列。