为什么在查询中使用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
% F! [* v# C6 e- ~5 v5 K在4.5秒内返回38行
7 @% i$ p% W  z! ]7 S: D9 b9 U删除时，ORDER BY我得到了很好的改进：
3 I3 t( T) o- `0 ~SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL LIMIT 200
0.30秒内38行
但是，如果在LIMIT不触摸的情况下删除，ORDER BY我会得到更好的结果：
3 h2 u6 {# f, a5 ?0 g' @# rSELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY
9 l3 H8 b, L; |! e. w  V. T/ }creation_date DESC
0 l$ o8 r6 _  Z  `0.10s（??）中的38行
% ~/ R' N3 F/ {" [) g9 ?( ~, [) R为什么我的LIMIT这么饿？
继续前进
8 W" e& y" @, O! X- L( M3 G在尝试发送答案之前，我尝试了一些尝试，并注意到我有一个索引creation_date（是datetime），因此我删除了它，并且第一个查询现在以0.10s的速度运行。这是为什么
？
5 X# F( f! u$ Z3 i; X8 P" b0 ]编辑
很好的猜测，我在where的其他列上有索引。
8 t! ?6 f1 v6 a( |mysql> explain SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC LIMIT 200;
4 [8 x3 p3 G5 ^5 Q+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
# N, O( L! t8 q* w| id | select_type | table  | type  | possible_keys          | key        | key_len | ref  | rows | Extra       |
) p+ r$ i+ T% a* M! }- \0 N+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | index | id_state_idx,id_mp_idx | creation_date | 5       | NULL | 1719 | Using where |
& s$ i0 v: u1 Q" w9 B9 T1 c+----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+
: X+ k( S# H, G6 G* i1 B: L设置1行（0.00秒）
9 `1 B- H  c4 `' \9 xmysql> explain SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC;
+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+
) }( f& L) @; f6 I* U4 C| 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 |
, q+ F- I7 E! S6 ?7 t( a+----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+
. e  Z+ ?3 A4 N7 A0 U$ p. }               
* h5 X4 @) p" m) I& _; _, @. c解决方案:
8 V' Y- Z& D6 N/ G               
- P+ d7 G8 W: M7 Q
8 B  X1 B1 ?$ e0 @
! {* B! l6 ~- r4 B! p5 ]                索引不一定能提高性能。为了更好地了解正在发生的事情，如果您explain为不同的查询包括了，这将有所帮助。
我最好的猜测是您有一个索引，id_state甚至id_state, id_mp可以用来满足该where子句。如果是这样，则不order
by使用的第一个查询将使用此索引。它应该很快。即使没有索引，这也需要对orders表中的页面进行顺序扫描，这仍然可以非常快。
然后当您添加索引时creation_date，MySQL决定使用该索引代替order
: o9 s/ }, B, h( y1 D% ?by。这需要读取索引中的每一行，然后获取相应的数据页以检查where条件并返回列（如果存在匹配项）。该读取效率极低，因为它不是按“页面”顺序，而是按索引指定的顺序。随机读取可能效率很低。
) ]6 u  J' I6 t$ {( x更糟糕的是，即使您有一个limit，您仍然必须读取 整个
' O6 J2 B1 W1 {9 e表，因为需要整个结果集。尽管您已经保存了38条记录的排序，但是您创建了一个效率非常低下的查询。
顺便说一句，如果orders表无法容纳在可用内存中，这种情况将变得更加严重。然后，您有一个称为“崩溃”的条件，其中每个新记录都倾向于生成一个新的I /
O读取。因此，如果一个页面上有100条记录，则该页面可能必须被读取100次。
: C' b4 W. V. Q0 N1 \6 F1 R! z  i通过在上添加索引，可以使所有这些查询的运行速度更快orders(id_state, id_mp,
9 x5 @! [9 P: H0 hcreation_date)。该where子句将使用前两列，而order by则将使用最后两列。