升级到MySQL5.7后开发有什么坑

2025-03-24 技术教程

这篇文章主要介绍升级到MySQL5.7后开发有什么坑，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

前言

前段时间，将线上MySQL数据库升级到了5.7。考虑到可能产生的不兼容性，在升级之前，确实也是战战兢兢，虽然测试环境，开发环境早在半年前就已提前升级。

基于前期的调研和朋友的反馈，与开发相关的主要有两点：

sql_mode

MySQL 5.6中，其默认值为"NO_ENGINE_SU BSTITUTION"，可理解为非严格模式，譬如，对自增主键插入空字符串''，虽然提示warning，但并不影响自增主键的生成。

但在MySQL 5.7中，其就调整为了严格模式，对于上面这个，其不会提示warning，而是直接报错。

分组求最值

分组求最值的某些写法在MySQL5.7中得不到预期结果，这点，相对来说比较隐蔽。

其中，第一点是可控的，毕竟可以调整参数。而第二点，却是不可控的，没有参数与之相关，需要开发Review代码。

下面具体来看看

测试数据

mysql>select*fromemp;+-------+----------+--------+--------+|empno|ename|sal|deptno|+-------+----------+--------+--------+|1001|emp_1001|100.00|10||1002|emp_1002|200.00|10||1003|emp_1003|300.00|20||1004|emp_1004|400.00|20||1005|emp_1005|500.00|30||1006|emp_1006|600.00|30|+-------+----------+--------+--------+rowsinset(0.00sec)

其中，empno是员工编号，ename是员工姓名，sal是工资，deptno是员工所在部门号。

业务的需求是，求出每个部门中工资最高的员工的相关信息。

在MySQL5.6中，我们可以通过下面这个SQL来实现，

SELECTdeptno,ename,salFROM(SELECT*FROMempORDERBYsalDESC)tGROUPBYdeptno;

结果如下，可以看到，其确实实现了预期效果。

+--------+----------+--------+|deptno|ename|sal|+--------+----------+--------+|10|emp_1002|200.00||20|emp_1004|400.00||30|emp_1006|600.00|+--------+----------+--------+

再来看看MySQL5.7的结果，竟然不一样。

+--------+----------+--------+|deptno|ename|sal|+--------+----------+--------+|10|emp_1001|100.00||20|emp_1003|300.00||30|emp_1005|500.00|+--------+----------+--------+

实际上，在MySQL5.7中，对该SQL进行了改写，改写后的SQL可通过explain(extended) + show warnings查看。

mysql>explainselectdeptno,ename,salfrom(select*fromemporderbysaldesc)tgroupbydeptno;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|SIMPLE|emp|NULL|ALL|NULL|NULL|NULL|NULL|6|100.00|Usingtemporary|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+rowinset,1warning(0.00sec)mysql>showwarnings\G***************************1.row***************************Level:NoteCode:1003Message:/*select#1*/select`slowtech`.`emp`.`deptno`AS`deptno`,`slowtech`.`emp`.`ename`AS`ename`,`slowtech`.`emp`.`sal`AS`sal`from`slowtech`.`emp`groupby`slowtech`.`emp`.`deptno`rowinset(0.00sec)

从改写后的SQL来看，其消除了子查询，导致结果未能实现预期效果，官方也证实了这一点，https://bugs.mysql.com/bug.php?id=80131

很多人可能不以为然，认为没人会这样写，但在大名鼎鼎的stackoverflow中，该实现的点赞数就有116个-由此可见其受众之广，仅次于后面提到的“方法二”（点赞数206个）。

https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results

需要注意的是，该SQL在5.7中是不能直接运行的，其会提示如下错误：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个与sql_mode有关，在MySQL 5.7中，sql_mode调整为了

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

其中，ONLY_FULL_GROUP_BY与group by语句有关，其要求select列表里只能出现分组列（即group by后面的列）和聚合函数（sum，avg，max等），这也是SQL92的标准。

但在工作中，却经常看到开发写出下面这种SQL。

mysql>selectdeptno,ename,max(sal)fromempgroupbydeptno;+--------+----------+----------+|deptno|ename|max(sal)|+--------+----------+----------+|10|emp_1001|200.00||20|emp_1003|400.00||30|emp_1005|600.00|+--------+----------+----------+rowsinset(0.01sec)

实在不明白，这里的ename在业务层有何意义，毕竟，他并不是工资最高的那位员工。

分组求最值，MySQL的实现方式

其实分组求最值是一个很普遍的需求。在工作中，也经常被开发同事问到。下面具体来看看，MySQL中有哪些实现方式。

方法1

SELECTe.deptno,ename,salFROMempe,(SELECTdeptno,max(sal)maxsalFROMempGROUPBYdeptno)tWHEREe.deptno=t.deptnoANDe.sal=t.maxsal;

方法2

SELECTa.deptno,a.ename,a.salFROMempaLEFTJOINempbONa.deptno=b.deptnoANDa.sal<b.salWHEREb.salISNULL;

这两种实现方式，其实是通用的，不仅适用于MySQL，也适用于其它主流关系型数据库。

方法3

MySQL 8.0推出了分析函数，其也可实现类似功能。

SELECTdeptno,ename,salFROM(SELECTdeptno,ename,sal,LAST_VALUE(sal)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)maxsalFROMemp)aWHEREsal=maxsal;

三种实现方式的性能对比

因上面测试案例的数据量太小，三种实现方式的结果都是秒出，仅凭执行计划很难直观地看出实现方式的优劣。

下面换上数据量更大的测试数据，官方示例数据库employees中的dept_emp表，https://github.com/datacharmer/test_db

表的相关信息如下，其中emp_no是员工编号，dept_no是部门编号，from_date是入职日期。

mysql>showcreatetabledept_emp\G***************************1.row***************************Table:dept_empCreateTable:CREATETABLE`dept_emp`(`emp_no`int(11)NOTNULL,`dept_no`char(4)NOTNULL,`from_date`dateNOTNULL,`to_date`dateNOTNULL,KEY`dept_no`(`dept_no`,`from_date`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_cirowinset(0.00sec)mysql>selectcount(*)fromdept_emp;+----------+|count(*)|+----------+|331603|+----------+rowinset(0.09sec)mysql>select*fromdept_emplimit1;+--------+---------+------------+------------+|emp_no|dept_no|from_date|to_date|+--------+---------+------------+------------+|10001|d005|1986-06-26|9999-01-01|+--------+---------+------------+------------+rowinset(0.00sec)

方法1

mysql>selectd.dept_no,d.emp_no,d.from_datefromdept_empd,(selectdept_no,max(from_date)max_hiredatefromdept_empgroupbydept_no)twhered.dept_no=t.dept_noandd.from_date=t.max_hiredate;…rowsinset(0.00sec)mysql>explainselectd.dept_no,d.emp_no,d.from_datefromdept_empd,(selectdept_no,max(from_date)max_hiredatefromdept_empgroupbydept_no)twhered.dept_no=t.dept_noandd.from_date=t.max_hiredate;+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|9|100.00|Usingwhere|1|PRIMARY|d|NULL|ref|dept_no|dept_no|19|t.dept_no,t.max_hiredate|5|100.00|NULL|2|DERIVED|dept_emp|NULL|range|dept_no|dept_no|16|NULL|9|100.00|Usingindexforgroup-by+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------

方法2

mysql>explainselecta.dept_no,a.emp_no,a.from_datefromdept_empaleftjoindept_empbona.dept_no=b.dept_noanda.from_date<b.from_datewhereb.from_dateisnull;+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+|1|SIMPLE|a|NULL|ALL|NULL|NULL|NULL|NULL|331008|100.00|NULL||1|SIMPLE|b|NULL|ref|dept_no|dept_no|16|slowtech.a.dept_no|41376|19.00|Usingwhere;Usingindex|+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+rowsinset,1warning(0.00sec)

方法3

mysql>selectdept_no,emp_no,from_datefrom(selectdept_no,emp_no,from_date,last_value(from_date)over(partitionbydept_noorderbyfrom_daterowsbetweenunboundedprecedingandunboundedfollowing)max_hiredatefromdept_emp)awherefrom_date=max_hiredate;…rowsinset(1.57sec)mysql>descselectdept_no,emp_no,from_datefrom(selectdept_no,emp_no,from_date,last_value(from_date)over(partitionbydept_noorderbyfrom_daterowsbetweenunboundedprecedingandunboundedfollowing)max_hiredatefromdept_emp)awherefrom_date=max_hiredate;+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|331008|100.00|Usingwhere||2|DERIVED|dept_emp|NULL|ALL|NULL|NULL|NULL|NULL|331008|100.00|Usingfilesort|+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+rowsinset,2warnings(0.00sec)

从执行时间上看，

方法1的时间最短，在有复合索引(deptno, fromdate)的情况下，结果瞬间就出来了，即使在没有索引的情况下，也只消耗了0.75s。

方法2的时间最长，3个小时还是没出结果。同样的数据，同样的SQL，放到Oracle查，也消耗了87分49秒。

方法3的时间比较固定，无论是否存在索引，都维持在1.5s左右，比方法1的耗时要久。

这里，对之前提到的，MySQL 5.7中不再兼容的实现方式也做了个测试，在没有任何索引的情况下，其稳定在0.7s（性能并不弱，怪不得有人使用），而同等情况下，方法1稳定在0.5s（哈，MySQL 5.6竟然比8.0还快）。但与方法1不同的是，其无法通过索引进行优化。

从执行计划上看，

方法1，先将group by的结果放到临时表中，然后再将该临时表作为驱动表，来和dept_emp表进行关联查询。驱动表小（只有9条记录），关联列又有索引，无怪乎，结果能秒出。

方法2，两表关联。其犯了SQL优化中的两个大忌。

1. 驱动表太大，其有331603条记录。

2. 被驱动表虽然也有索引，但从执行计划上看，其只使用了复合索引 (dept_no, from_date)中的dept_no，而dept_no的选择率又太低，毕竟只有9个部门。

方法3，先把分析的结果放到一个临时表中，然后再对该临时表进行处理。其进行了两次全表扫描，一次是针对dept_emp表，一次是针对临时表。

所以，对于分组求最值的需求，建议使用方法1，其不仅符合SQL规范，查询性能上也是最好的，尤其是在联合索引的情况下。