升级MySQL5.7，开发不得不注意的坑

221次阅读

没有评论

共计 9451 个字符，预计需要花费 24 分钟才能阅读完成。

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

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

sql_mode

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

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

分组求最值

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

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

下面具体来看看

测试数据

mysql> select * from emp;
+——-+———-+——–+——–+
| 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 |
+——-+———-+——–+——–+
6 rows in set (0.00 sec)

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

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

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

SELECT
deptno,ename,sal
FROM
(SELECT * FROM emp ORDER BY sal DESC) t
GROUP BY
deptno;

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

+——–+———-+——–+
| 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> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `slowtech`.`emp`.`deptno` AS `deptno`,`slowtech`.`emp`.`ename` AS `ename`,`slowtech`.`emp`.`sal` AS `sal` from `slowtech`.`emp` group by `slowtech`.`emp`.`deptno`
1 row in set (0.00 sec)

从改写后的 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> select deptno,ename,max(sal) from emp group by deptno;
+——–+———-+———-+
| deptno | ename | max(sal) |
+——–+———-+———-+
| 10 | emp_1001 | 200.00 |
| 20 | emp_1003 | 400.00 |
| 30 | emp_1005 | 600.00 |
+——–+———-+———-+
3 rows in set (0.01 sec)

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

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

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

方法 1

SELECT
e.deptno,
ename,
sal
FROM
emp e,
(SELECT deptno, max( sal) maxsal FROM emp GROUP BY deptno ) t
WHERE
e.deptno = t.deptno
AND e.sal = t.maxsal;

方法 2

SELECT
a.deptno,
a.ename,
a.sal
FROM
emp a
LEFT JOIN emp b ON a.deptno = b.deptno
AND a.sal < b.sal
WHERE
b.sal IS NULL;

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

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

SELECT
deptno,
ename,
sal
FROM
(
SELECT
deptno,
ename,
sal,
LAST_VALUE (sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) maxsal
FROM
emp
) a
WHERE
sal = maxsal;

三种实现方式的性能对比

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

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

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

mysql> show create table dept_emp\G
*************************** 1. row ***************************
Table: dept_emp
Create Table: CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
KEY `dept_no` (`dept_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select count(*) from dept_emp;
+———-+
| count(*) |
+———-+
| 331603 |
+———-+
1 row in set (0.09 sec)

mysql> select * from dept_emp limit 1;
+——–+———+————+————+
| emp_no | dept_no | from_date | to_date |
+——–+———+————+————+
| 10001 | d005 | 1986-06-26 | 9999-01-01 |
+——–+———+————+————+
1 row in set (0.00 sec)

方法 1

mysql> select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;
…
12 rows in set (0.00 sec)

方法 2

方法 3

mysql> select dept_no,emp_no,from_date from (select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
…
12 rows in set (1.57 sec)

从执行时间上看，

方法 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 规范，查询性能上也是最好的，尤其是在联合索引的情况下。