升級 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 |
+——-+———-+——–+——–+
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 warningsG
*************************** 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_empG
*************************** 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_unicode_520_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    | | 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    | | 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;