這篇文章主要介紹升級(jí)到MySQL5.7后開(kāi)發(fā)有什么坑，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、遷西網(wǎng)絡(luò)推廣、小程序開(kāi)發(fā)、遷西網(wǎng)絡(luò)營(yíng)銷、遷西企業(yè)策劃、遷西品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供遷西建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com

前言

前段時(shí)間，將線上MySQL數(shù)據(jù)庫(kù)升級(jí)到了5.7?？紤]到可能產(chǎn)生的不兼容性，在升級(jí)之前，確實(shí)也是戰(zhàn)戰(zhàn)兢兢，雖然測(cè)試環(huán)境，開(kāi)發(fā)環(huán)境早在半年前就已提前升級(jí)。

基于前期的調(diào)研和朋友的反饋，與開(kāi)發(fā)相關(guān)的主要有兩點(diǎn)：

sql_mode

MySQL 5.6中，其默認(rèn)值為"NO_ENGINE_SU BSTITUTION"，可理解為非嚴(yán)格模式，譬如，對(duì)自增主鍵插入空字符串''，雖然提示warning，但并不影響自增主鍵的生成。

但在MySQL 5.7中，其就調(diào)整為了嚴(yán)格模式，對(duì)于上面這個(gè)，其不會(huì)提示warning，而是直接報(bào)錯(cuò)。

分組求最值

分組求最值的某些寫法在MySQL5.7中得不到預(yù)期結(jié)果，這點(diǎn)，相對(duì)來(lái)說(shuō)比較隱蔽。

其中，第一點(diǎn)是可控的，畢竟可以調(diào)整參數(shù)。而第二點(diǎn)，卻是不可控的，沒(méi)有參數(shù)與之相關(guān)，需要開(kāi)發(fā)Review代碼。

下面具體來(lái)看看

測(cè)試數(shù)據(jù)

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是員工編號(hào)，ename是員工姓名，sal是工資，deptno是員工所在部門號(hào)。

業(yè)務(wù)的需求是，求出每個(gè)部門中工資最高的員工的相關(guān)信息。

在MySQL5.6中，我們可以通過(guò)下面這個(gè)SQL來(lái)實(shí)現(xiàn)，

SELECT
 deptno,ename,sal 
FROM
 ( SELECT * FROM emp ORDER BY sal DESC ) t 
GROUP BY
 deptno;

結(jié)果如下，可以看到，其確實(shí)實(shí)現(xiàn)了預(yù)期效果。

+--------+----------+--------+
| deptno | ename | sal |
+--------+----------+--------+
| 10 | emp_1002 | 200.00 |
| 20 | emp_1004 | 400.00 |
| 30 | emp_1006 | 600.00 |
+--------+----------+--------+

再來(lái)看看MySQL5.7的結(jié)果，竟然不一樣。

+--------+----------+--------+
| deptno | ename | sal |
+--------+----------+--------+
| 10 | emp_1001 | 100.00 |
| 20 | emp_1003 | 300.00 |
| 30 | emp_1005 | 500.00 |
+--------+----------+--------+

實(shí)際上，在MySQL5.7中，對(duì)該SQL進(jìn)行了改寫，改寫后的SQL可通過(guò)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來(lái)看，其消除了子查詢，導(dǎo)致結(jié)果未能實(shí)現(xiàn)預(yù)期效果，官方也證實(shí)了這一點(diǎn)，https://bugs.mysql.com/bug.php?id=80131

很多人可能不以為然，認(rèn)為沒(méi)人會(huì)這樣寫，但在大名鼎鼎的stackoverflow中，該實(shí)現(xiàn)的點(diǎn)贊數(shù)就有116個(gè)-由此可見(jiàn)其受眾之廣，僅次于后面提到的“方法二”（點(diǎn)贊數(shù)206個(gè)）。

https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results

需要注意的是，該SQL在5.7中是不能直接運(yùn)行的，其會(huì)提示如下錯(cuò)誤：

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

這個(gè)與sql_mode有關(guān)，在MySQL 5.7中，sql_mode調(diào)整為了

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語(yǔ)句有關(guān)，其要求select列表里只能出現(xiàn)分組列（即group by后面的列）和聚合函數(shù)（sum，avg，max等），這也是SQL92的標(biāo)準(zhǔn)。

但在工作中，卻經(jīng)?？吹介_(kāi)發(fā)寫出下面這種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)

 實(shí)在不明白，這里的ename在業(yè)務(wù)層有何意義，畢竟，他并不是工資最高的那位員工。 

分組求最值，MySQL的實(shí)現(xiàn)方式

其實(shí)分組求最值是一個(gè)很普遍的需求。在工作中，也經(jīng)常被開(kāi)發(fā)同事問(wèn)到。 下面具體來(lái)看看，MySQL中有哪些實(shí)現(xiàn)方式。

方法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;

這兩種實(shí)現(xiàn)方式，其實(shí)是通用的，不僅適用于MySQL，也適用于其它主流關(guān)系型數(shù)據(jù)庫(kù)。

方法3

MySQL 8.0推出了分析函數(shù)，其也可實(shí)現(xiàn)類似功能。

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;

三種實(shí)現(xiàn)方式的性能對(duì)比

因上面測(cè)試案例的數(shù)據(jù)量太小，三種實(shí)現(xiàn)方式的結(jié)果都是秒出，僅憑執(zhí)行計(jì)劃很難直觀地看出實(shí)現(xiàn)方式的優(yōu)劣。

下面換上數(shù)據(jù)量更大的測(cè)試數(shù)據(jù)，官方示例數(shù)據(jù)庫(kù)employees中的dept_emp表，https://github.com/datacharmer/test_db

表的相關(guān)信息如下，其中emp_no是員工編號(hào)，dept_no是部門編號(hào)，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 |  | 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 |  | 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)

從執(zhí)行時(shí)間上看，

方法1的時(shí)間最短，在有復(fù)合索引(deptno, fromdate)的情況下，結(jié)果瞬間就出來(lái)了，即使在沒(méi)有索引的情況下，也只消耗了0.75s。

方法2的時(shí)間最長(zhǎng)，3個(gè)小時(shí)還是沒(méi)出結(jié)果。同樣的數(shù)據(jù)，同樣的SQL，放到Oracle查，也消耗了87分49秒。

方法3的時(shí)間比較固定，無(wú)論是否存在索引，都維持在1.5s左右，比方法1的耗時(shí)要久。

這里，對(duì)之前提到的，MySQL 5.7中不再兼容的實(shí)現(xiàn)方式也做了個(gè)測(cè)試，在沒(méi)有任何索引的情況下，其穩(wěn)定在0.7s（性能并不弱，怪不得有人使用），而同等情況下，方法1穩(wěn)定在0.5s（哈，MySQL 5.6竟然比8.0還快）。但與方法1不同的是，其無(wú)法通過(guò)索引進(jìn)行優(yōu)化。

從執(zhí)行計(jì)劃上看，

方法1， 先將group by的結(jié)果放到臨時(shí)表中，然后再將該臨時(shí)表作為驅(qū)動(dòng)表，來(lái)和dept_emp表進(jìn)行關(guān)聯(lián)查詢。驅(qū)動(dòng)表?。ㄖ挥?條記錄），關(guān)聯(lián)列又有索引，無(wú)怪乎，結(jié)果能秒出。

方法2， 兩表關(guān)聯(lián)。其犯了SQL優(yōu)化中的兩個(gè)大忌。

   1. 驅(qū)動(dòng)表太大，其有331603條記錄。

   2. 被驅(qū)動(dòng)表雖然也有索引，但從執(zhí)行計(jì)劃上看，其只使用了復(fù)合索引  (dept_no, from_date)中的dept_no，而dept_no的選擇率又太低，畢竟只有9個(gè)部門。

方法3， 先把分析的結(jié)果放到一個(gè)臨時(shí)表中，然后再對(duì)該臨時(shí)表進(jìn)行處理。其進(jìn)行了兩次全表掃描，一次是針對(duì)dept_emp表，一次是針對(duì)臨時(shí)表。

所以，對(duì)于分組求最值的需求，建議使用方法1，其不僅符合SQL規(guī)范，查詢性能上也是最好的，尤其是在聯(lián)合索引的情況下。

以上是“升級(jí)到MySQL5.7后開(kāi)發(fā)有什么坑”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！