作者介绍
刘华阳，20年经历风霜雨打的 DBA，5年的 DBA 架构和团队管理经验，只要是数据库都喜欢学习。PostgreSQL ACE，MongoDB 狂热者，10年的 MYSQL 工作经验，现在在玩 POLARDB 与时俱进。
故障背景
最近群里一个知名的软件服务商的领导，说他的系统服务的客户很多还在用MySQL5.6,5.7 然后群里说还有用5.5的了。哎MySQL 的老版本的用户不少，大部分还在MySQL 5.7上转悠。说是升级的热情不高，当然这与ORACLE 对MySQL的8.0版本的“不负责”，有关，也与不少用户目前使用MySQL5.7并没有遇到问题有关。
但这里提示能升级MySQL的同学还是进来升级,MySQL到8.018版本以上（不包括8.029）。因为最近我们出现了一个严重的MySQL的故障，版本是MySQL 5.7.28,Official 版本。
系统已经持续运行了有5-6年了，没有问题，但突然一天CPU升高，接近100%，并且持续的高，我们抓取了慢查询日志，发现其中有一个SQL 与这个问题有关，随机我们进行了测试，执行计划完全走了索引，一个这样简单的SQL 竟然要90-120秒，这让我们不可思议。随即我们将SQL 在从库进行了测试，发现从库运行这个SQL的速度异常的快只要0.006秒。
select distinct RES.*, AR.ID_ as VAR_ID_, AR.NAME_ as VAR_NE_, AR._TYPE_ as V_TYPE_, AR._ as VAR_REV_, AR.C_INST_ID_ as VR_PC_ID_, AR.CUT_ID_ as VR_EXECUID_, AR.K_ID_ as R_SK_ID_, AR.EA_ID_ as VAR_BAY_ID_, AR.BLE_ as VAR_DLE_, AR.T_ as VR_XT_, AR.T2_ as VR_TET2_, AR.T_UPDD_TI_ as VAR_ST_TED_ME_, AR.G_ as V_LG_from tcn.AC_HI_TST RE left outer join tcn.NST AR ON RE.PNST_ID_ = AR.EXON_ID_ and VAR.TK_ID_ is nullWHERE RES.ID_ = '173878637'order by VAR.LAST_UED_TIME_ ascLIMIT 20000 OFFSET 0;
这里最大的问题是他有主从库，主库是运行的是不OK的，但是从库运行是非常快的，同样的数据，同样的SQL，进行explain的时候也是同样的执行计划。
到底是为什么，通过show engine innodb status,和各种系统表也分析了当前写库是否有大事务，或者表被霸占，或大事务等情况，统统的没有，就是慢、慢、慢……
冷静下来分析
1、主库和从库数据一致，语句一致，执行计划一致2、在主库实际运行语句最快1分30秒 ，在从库运行0.005秒此时在想，如果是MySQL8 就好了，我们可以使用explain format = 多种显示的方式，并且还能trace 具体的执行计划，而不是在这里看着简单的执行计划，并看着业务部门在问到底怎么回事。在问题分析这块，MYSQL5.7就是一个傻子。
然后只能从语句上下手，尝试，语句中有两个点 1 ORDER BY 2 limit N,M
因为MySQL有一个致命的问题这在8.0后也有类似的问题，但在高版本将这个问题的参数默认给关闭了，ORDER BY LIMIT 执行效率的问题。
问题主要表现在 where condition order by A limit N 这样的语句，由于MYSQL5.7默认是打开 prefer_ordering_index 也就是在操作的时候，由于limit N 的值比较小，导致查询分析器去走ORDER BY 字段上的索引，而放弃更适合的索引。
摆在我面前的有几个方案
1、去设置optimizer_switch='prefer_ordering_index=OFF'2、尝试添加一个比现在索引权重更大的索引 （需要看条件，不是每次都能行）3、 强制语句使用 hint由于这个.28的版本比较老，记得应该是.33后的MySQL才可以添加 prefer_ordering_index参数所以第一个选择的方案不可以。
那么就需要尝试剩下的方案，我先尝试第三个方案，的确在强制hint index后，语句执行的速度┗|｀O′|┛ 嗷，的一下子就快了，0.006秒，看来的确是和语句执行没有走正确的索引有关，配合监控中的执行语句的时候iops就超高，估计是是3千500万的表在进行全表的扫描。尝试了多次，只要语句执行不HINT，IOPS 就超高。
但是让开发去给你改语句，虽然开发说可以，但觉得对不起开发，这数据库的稳定性也忒差了，我尝试第二个方式，正好这个查询中在left join 中带有一个其他的条件，那么我就舱室重建索引，将原来的单字段的索引，改成复合的字段，这样从查询计划的实现上也是有利于数据查询的，在添加这个复合的索引后，再次尝试语句，速度也是┗|｀O′|┛ 嗷的一下子蹦到 0.005秒。
先对这些方案，还是通过添加索引来进行问题的解决，让几方更能接受，后面打算对mysql的小版本进行升级，当然如果能推动往 MYSQL 8.018的版本上进行升级最好是 8.025就更好了，那么后续的一些DDL等操作也会更顺手，同时对于GROUP BY的语句执行的方式有了更大的优化，在低版本MYSQL无法运行的复杂的SQL，在8.025以上的版本运行也会更加的顺畅。
>>>>
官方对此知识的文档
https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html作者丨 liuaustin3
来源丨公众号：AustinDatabases（ID：AustinDatabases）
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
