发现问题:
最近后台有一个列表查询功能,今年数据量起来后,导致数据库 慢查询,导致线上服务器直接504。
这其中涉及到一个大表,主键id;在查询时候使用了 order by id desc limit 20,explain发现接近全表扫描了。
围绕这个问题,逐步修改语句优化,发现去掉 status条件或者 忽略 status索引,即可。
但是,这个又是远古遗留问题,status索引理应不建立(吐槽以前的小伙伴),如今又不能手动去掉。
可以选择如下解决方案:
1、去掉where status 条件 (不太合适)
2、ignore index(status) 索引条件 (Model层面不好改动)
上面两个解决方案,无法实施以后,求助于网络,发现解决方案,参考如下:
https://blog.csdn.net/gycyxh/article/details/89918261
https://www.jianshu.com/p/caf5818eca81
最终解决方案:
1、sql去掉limit 部分;使用程序去处理多条返回值
2、换用其他 字段作为order by ,比如日期时间。(最优)
成功解决,优化成功。
至于其中原理,还需要参考具体文章详解。
参考总结如下:
总结
- 在order by id的情况下,MySQL由于自身的优化器选择,为了避免某些排序的消耗,可能会走非预期的PRIMARY主键索引;
- order by 和 limit 结合使用,如果where 字段,order by字段都是索引,那么有limit索引会使用order by字段所在的索引,没有limit会使用where 条件的索引;
- 对于数据量比较大,而且执行量很高的分页sql,尽可能将所有的查询字段包括在索引中,同时使用索引来消除排序;
- 多用explain查看是否使用到了最优索引;
- 利用optimizer trace查看优化器执行过程;
- 观察mysql的slow_query_log,及时做排查优化。