背景
最近工作中遇到一些mysql相关的问题，感觉值得记录，特此写文整理下
问题
一、Mysql order by与limit混用陷阱
场景描述
情况是这样的，有一个列表页，需要排序并作分页，这没什么特殊的，很正常的需求，然而发现了原有代码中一个问题，那就是，当多页数据的时候，存在重复数据，这个肯定不符合预期
问题排查步骤
- 在检查逻辑时候，并没有发现什么错误
- 但是发现只有一页数据的时候，并没有这个问题
- 所以这个问题肯定是跟分页有关
- 那么会影响分页的，limit自然不用说，还有一个就是order by，这个决定展示顺序，虽然看似无关
- 找寻相关资料
- 发现order by 和limit混用确实有陷阱
出现问题的原理
在我们设想中，order by limit 混用时，数据库应该先取出来结果，再排序，再limit，其实不一定是
这里面涉及到两个内容，
- 一是，
order by 的条件没有索引的时候，排序速度较慢，mysql为了提高查询速度，在做limit时候，并没有全部对全部结果排序，而是找到limit 的偏移量之后，剩下的记录将不再排序
这个其实是，没有索引时候，无索引排序（Using filesort，并不是文件排序）速度很慢，而绝大部分记录是有序的，所以不再排序剩下的部分 - 二是，order by 的条件有重复值的时候，mysql并没有固定的策略决定哪条在前哪条在后，所以，
重复值的记录的顺序是随机的，不用多说了吧
解决方法
order by 的条件最好是索引，而且是唯一、主键索引，实在不行，可以order by xxx,id 嘛，总之就是避免无索引排序和重复值
二、Using temporary ; Using filesort优化
场景描述
其实这个问题是在做一个sql优化时候需要的问题的抽象，表面问题是distinct的优化（Using temporary）和模糊查询like的优化（Using filesort）
问题排查步骤
这个问题排查就比较简单，不多说了
出现问题的原理
- 先说like，这个前置%啊、加索引啊（手机号模糊匹配）之类的这种问题大家应该都知道的，不多说了，主要来看下一个
- distinct引发的临时表。这里主要想说的是比较不常见的使用到临时表的操作
GROUP BY, DISTINCT or ORDER BY，并不是说这三个一定会触发Using temporary，而是在额外的对结果数据进行排序时才会触发，怎么理解呢，就是，正常查询到数据后，需要做一些操作，比如distinct，这个时候，如果你的结果集并非有序，那么就需要排序后（毕竟需要去重嘛，运用排序快速达到去重目的也是算法的一个关键思路哦）才能得到distinct之后的结果，所以，这个时候，问题就很清晰，处理方案也就很明显了
解决方法
distinct用在查询语句用到的索引列上
参考文章
EXPLAIN sql优化方法（2） Using temporary ; Using filesort
Using temporary ; Using filesort优化
Mysql order by与limit混用陷阱
拓展阅读
MySQL limit 工作机制
limit优化（英文）
MySQL order by 排序原理
浅析 Impossible WHERE noticed after reading const tables
欢迎大家关注我的公众号
半亩房顶
