order by limit走错了索引

问题和解决

今天看到这么一个sql：

select *
from
  restaurant_restaurant_unit this_
where
  this_.name LIKE '张三%'
  and this_.restaurant_unit_type <> 2
  and this_.is_valid = 1
  and this_.status = 1
order by
  this_.created_at DESC 
limit
  15;

这个sql的执行时间竟然达到了令人发指的9s，这里补充一下前提知识：

数据表有name的索引、created_at的索引，主建是id
is_valid和status都可以视为有效状态
restaurant_unit_type是节点类型，一共就3种:1是中间节点，2是叶子节点（绑定的门店），3是根节点。数据分布规模来说，2的数据远大于1和3.
sql查出来结果只有14条

打开这个慢sql的工单，映入眼帘的第一个字符就是<>，果然数据库执行计划执行一下，结果如下：

果然，走的是create_at的索引。开心的打开左边数据库信息，索引信息打开：

结果restaurant_unit_type没有索引，name有索引，理论上是不会影响走name索引的呀。

既然不是<>导致的慢sql问题，那么到底是啥呢？凭借“菜就多练”的原则，我执行了下面几个和慢sql类似的执行计划:

sql1：去掉order by limit
	select
	  *
	from
	  restaurant_restaurant_unit this_
	where 
	  this_.name <> '张三%'
	  and this_.restaurant_unit_type <> 2
	  and this_.is_valid = 1
	  and this_.status = 1;

执行计划：

这次执行时间只有8ms，快了很多。比加上order by limit的有了重大提高。看来是order by limit导致的？

sql2：去掉<>
	select
	  *
	from
	  restaurant_restaurant_unit this_
	where 
	  this_.name like '张三%'
	  -- and this_.restaurant_unit_type <> 2  //把这个条件注释掉
	  and this_.is_valid = 1
	  and this_.status = 1
	order by
	  this_.created_at DESC
	limit
	  15;

执行计划：

可以看到此时并不会走name索引，走了create_at的索引，执行时间虽然有所增加，但是也远远达不到9m的程度。最后只能咨询dba的同学,dba说：where语句和order by limit语句中都包含了索引列时，mysql优化器可能会选择错误的索引导致更加慢。

从上面的sql也能看出，加入order by limit后，索引从name变成了create_at。注意的是，只是可能慢，具体慢不慢得看数据的分布情况。

分析下上面的慢sql为啥是慢sql:

首先不带order by limit的sql执行计划如下：

之前更多关注走索引，其实还有一个关键量，扫描行数：16810。

这个值是mysql优化器认为走name索引需要扫描的行数，这个是正经索引下探方法得到的值，相对准确。

但是加入order by limit A后，此时存在了两个索引create_at 和 name。对于优化器来说，其目标是选择扫描行数最少的索引是最佳的，所以需要计算create_at的扫描行数。而这就是问题的关键：order by limit A 是如何计算扫描行的。计算公式:

1	统计信息里面的表行数/where语句索引的扫描行数 * limit的值A = 扫描行数

根据这个公式：原理就是统计信息里面的表行数/where语句索引的扫描行数 就是mysql认为平均扫描一条数据的成本，再乘A就是A条数据的扫描成本。

首先获得统计信息里面的表行数：2564001

刚刚上面得到name索引的扫描行数：16810，而limit值为15，则计算得到：2564001/16810*15 = 2287.9。

这个值是远小于name索引的行，所以会走create_at索引。

但是为啥会慢呢？？？

原因就是因为前提说的，restaurant_unit_type是分布极其不均匀的。2是叶子节点为绑定的门店，都是先有叶子节点再有门店，所以叶子节点的create_at普遍较大，又是逆序遍历，所以基本上遍历到后面，甚至全表后才找到足够的数据。这也是为啥restaurant_unit_type去掉后能够较快返回的原因。

说了这么一大堆，咋解决呢？

其实问题分析下来，根本原因就是因为优化器错误的选择了order by后的索引嘛，那就不让他选嘛，所以就有如下的解决方案:

● 直接强force index 指定要走的索引 -》这个太粗暴了
● limit值扩大到上面的计算结果远大于where语句后的扫描 -》开发阶段难以评估，且limit值大多数业务固定
● 不让order by 后面有索引。 –比较合理（实测有效）
○ order by create DESC/Asc —》order by create DESC/ASC， id asc/desc
○ order by id -》 order by id + 0

另外一个参考文章

order by limit易走错索引，发生在5.6/5.7/XDB版本，这个是MYSQL设计时优化器不严谨的地方，原因如下:

首先sql执行计划会把sql分成2部分，select ...from...where...作为第一部分，order by...limit...作为第二部分。先对select ...from...where...做分析，得出最佳路径best_access_path的索引best_index、扫描量row_estimate_first、pk的选择度Cardinality_pk。
当sql有order by limit，同时limit的值< best_access_path的row_estimate，同时order by字段有单列索引或在联合索引最左列，触发执行计划recheck("recheck_reason": "low_limit")。
recheck后有可能选择order by字段的索引，如果选择了order by索引，同时数据分布不均匀或者实际符合条件的记录条数（即返回行数）小于limit值的话，那么就需要通过索引扫描所有的行回表，访问效率将比全表扫描还慢几倍。

order by触发recheck具体算法如下：

Cardinality_pk/row_estimate_first获得best_index字段所选值平均每条记录的间距（扫描量）row_estimate_second（这里是优化器不严谨的地方，因为优化器直接认为数据是均匀分布的）
如果row_estimate_second * limit值 < row_estimate_first,则选择order by字段索引，反之选择best_access_path索引best_index

怎么确认哪些order by limit sql会走错索引：

对where条件的列数据量group by分析，确认记录最多的列值，这里记为value_a；记录limit的值，这里记为limit_b
去掉order by limit字段执行explain获取访问value_a的best_access_path索引的扫描量，这里记为row_estimate_c
通过show index from table_name获取pk的选择度，这里记为Cardinality_d
如果Cardinality_d/row_estimate_c*limit_b<row_estimate_c,即Cardinality_d*limit_b<row_estimate_c*row_estimate_c就会触发走错索引。这里可以看出如果best_access_path索引访问的某个列值记录越多，即数据越不均匀，越容易触发走错索引。

更快捷方式：因为value_a约等于row_estimate_c（有误差，一般2 * value_a >= row_estimate_c），方程式可以转换为：表记录数 * limit值 < value_a * value_a，即只要知道表记录数，记录最多的列值，limit值，即可计算是否会走错索引（考虑误差和数据增长，建议当表记录数 * limit值 < 4 * value_a * value_a即可认为会可能走错索引）。