MySQL5.7 分组排序

2018-08-14 23:29:40
1280次阅读
0个评论
sql_mode
MySQL 5.6中,其默认值为"NO_ENGINE_SU BSTITUTION",可理解为非严格模式,譬如,对自增主键插入空字符串'',虽然提示warning,但并不影响自增主键的生成。
但在MySQL 5.7中,其就调整为了严格模式,对于上面这个,其不会提示warning,而是直接报错。
分组求最值

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


下面具体来看看
测试数据

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;


收藏00

登录 后评论。没有帐号? 注册 一个。