order by，limit，where 索引的优先级

章鱼哥搞java

已于 2023-09-22 10:37:26 修改

阅读量413

点赞数

分类专栏： java 文章标签： java

于 2023-09-21 22:39:53 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_51437526/article/details/133150428

版权

java 专栏收录该内容

14 篇文章

订阅专栏

问题引入
线上bug出现慢sql，排查语句为

SELECT
  *
FROM
  表1
WHERE
  (  
  c1=?
  ,c2=?
  ,c3=?
  ,c4=?
  ,c5>?
  )
ORDER BY
  c5 DESC,
  c6 DESC
  limit 1

索引情况是两个索引
索引a c1_c2_c3_c4_c5
索引b c4_c6
explain 索引走的第二个，产生疑问
尝试解决去掉limit走索引a，变更 orderby后的字段不命中索引走索引a
猜测结论：同时使用WHERE，ORDER BY，limit，且WHERE，ORDER BY命中相同的索引，会优先使用

继续尝试其他可能
WHERE后删除c1，其他不变，命中索引b
WHERE后删除c1，删除limit，无索引命中
WHERE后删除c1，ORDER BY删除c6，无索引命中
结论：使用limit时，会去ORDER BY后面找是否有索引，有并且字段全部命中会使用索引。

继续尝试其他可能
WHERE后不变，删除limit，命中索引a
结论：
正当我信心满满觉得摸出规律的时候，我改变了一下c1字段的值，发现使用的索引变成了索引 c1_c2_c3_c其他

最终结论：没结论，看mysql心情

但可以看出来的是 orderby 后面会用到索引，但必须用到limit且命中组合索引的所有字段

现在再来看应该是where后面的索引失效了，然后走了orderby后的索引，至于失效原因没有找到，但确实和条件值有关系，并且在改掉limit后字段不走索引后，where后的字段又神奇的走了索引a，多次查询发现，大多数查询用的索引都是c，

请教同事后，
原因是
SELECT
*
FROM
表1
WHERE
(
c1=?
,c2=?
,c3=?
,c4=?
,c5>?
)
捞出的数据过多有7000条，但是你的limit为1，mysql优化器认为数据量太大，即走了orderby后面的
简单验证：修改limit为3500，走了索引a符合预期，结论成立