升级到MySQL5.7后开发不得不注意的一些坑

前言

前段时间，将线上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 |
+-------+----------+--------+--------+
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> explain select deptno,ename,sal from (select * from emp order by sal desc) t group by deptno;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 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 | using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
row in set, 1 warning (0.00 sec)

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`
row in set (0.00 sec)

从改写后的sql来看，其消除了子查询，导致结果未能实现预期效果，官方也证实了这一点，

 

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

需要注意的是，该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 |
+--------+----------+----------+
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表，

表的相关信息如下，其中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
row in set (0.00 sec)

mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603 |
+----------+
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 |
+--------+---------+------------+------------+
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;
…
rows in set (0.00 sec)

mysql> explain 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;
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
| 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 | using where  
| 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 | using index for group-by
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------

方法2

mysql> explain select a.dept_no,a.emp_no,a.from_date from dept_emp a left join dept_emp b on a.dept_no=b.dept_no and a.from_date < b.from_date where b.from_date is null;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
| 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 | using where; using index |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
rows in set, 1 warning (0.00 sec)

方法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;
…
rows in set (1.57 sec)

mysql> desc 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;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 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 | using where |
| 2 | derived | dept_emp | null | all | null  | null | null | null | 331008 | 100.00 | using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
rows in set, 2 warnings (0.00 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规范，查询性能上也是最好的，尤其是在联合索引的情况下。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。