前言
前段时间，将线上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来看，其消除了子查询，导致结果未能实现预期效果，官方也证实了这一点，https://bugs.mysql.com/bug.php"external nofollow" target="_blank" href="https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results">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 | +--------+----------+----------+ 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 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规范，查询性能上也是最好的，尤其是在联合索引的情况下。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。
一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。
另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。
稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！
昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？