前段时间, 将线上 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> 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 |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
- 1 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`
- 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)
- 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 |
- +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
- 2 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;
- ...
- 12 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 |
- +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
- 2 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 规范, 查询性能上也是最好的, 尤其是在联合索引的情况下.
PS:
经大神指点, 对之前提到的, MySQL 5.7 中不再兼容的实现方式, 实际可以通过调整 optimizer_switch 来加以规避
set optimizer_switch='derived_merge=off';
derived_merge 是 MySQL 5.7 引入的, 其会试图将 Derived Table(派生表, from 后面的子查询), 视图引用, 公用表表达式 (Common table expressions) 与外层查询进行合并. 如,
- SELECT *
- FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
- ON t1.f2=derived_t2.f1
- WHERE t1.f1> 0;
改写为
- SELECT *
- FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
- ON t1.f1=derived_t2.f1;
来源: http://www.linuxidc.com/Linux/2018-07/153092.htm