MySQL 让你还用5.7 出事了吧，用着用着5.7崩了_MySQL

最近群里一个知名的软件服务商的领导，说他的系统服务的客户很多还在用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 null
WHERE
  RES.ID_ = '173878637'
order by
  VAR.LAST_UED_TIME_ asc
LIMIT
  20000 OFFSET 0;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.



MySQL 让你还用5.7 出事了吧，用着用着5.7崩了_mysql_02

这里最大的问题是他有主从库，主库是运行的是不OK的，但是从库运行是非常快的，同样的数据，同样的SQL，进行explain的时候也是同样的执行计划。

到底是为什么，通过show engine innodb status,和各种系统表也分析了当前写库是否有大事务，或者表被霸占，或大事务等情况，统统的没有，就是慢，慢，慢  fack!!!



MySQL 让你还用5.7 出事了吧，用着用着5.7崩了_mysql_03

冷静下来分析

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



MySQL 让你还用5.7 出事了吧，用着用着5.7崩了_数据库_04

Austindatabases 公众号，主要围绕数据库技术（PostgreSQL, MySQL, Mongodb, Redis, SqlServer，PolarDB, Oceanbase 等）和职业发展，国外数据库大会音译,国外大型IT信息类网站文章翻译，等，希望能和您共同发展。



MySQL 让你还用5.7 出事了吧，用着用着5.7崩了_数据库_05