前言
前段時(shí)間,將線上MySQL數(shù)據(jù)庫升級到了5.7??紤]到可能產(chǎn)生的不兼容性,在升級之前,確實(shí)也是戰(zhàn)戰(zhàn)兢兢,雖然測試環(huán)境,開發(fā)環(huán)境早在半年前就已提前升級。
基于前期的調(diào)研和朋友的反饋,與開發(fā)相關(guān)的主要有兩點(diǎn):
sql_mode
MySQL 5.6中,其默認(rèn)值為"NO_ENGINE_SU BSTITUTION",可理解為非嚴(yán)格模式,譬如,對自增主鍵插入空字符串'',雖然提示warning,但并不影響自增主鍵的生成。
但在MySQL 5.7中,其就調(diào)整為了嚴(yán)格模式,對于上面這個(gè),其不會提示warning,而是直接報(bào)錯(cuò)。
分組求最值
分組求最值的某些寫法在MySQL5.7中得不到預(yù)期結(jié)果,這點(diǎn),相對來說比較隱蔽。
其中,第一點(diǎn)是可控的,畢竟可以調(diào)整參數(shù)。而第二點(diǎn),卻是不可控的,沒有參數(shù)與之相關(guān),需要開發(fā)Review代碼。
下面具體來看看
測試數(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是員工編號,ename是員工姓名,sal是工資,deptno是員工所在部門號。
業(yè)務(wù)的需求是,求出每個(gè)部門中工資最高的員工的相關(guān)信息。
在MySQL5.6中,我們可以通過下面這個(gè)SQL來實(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 | +--------+----------+--------+
再來看看MySQL5.7的結(jié)果,竟然不一樣。
+--------+----------+--------+ | deptno | ename | sal | +--------+----------+--------+ | 10 | emp_1001 | 100.00 | | 20 | emp_1003 | 300.00 | | 30 | emp_1005 | 500.00 | +--------+----------+--------+
實(shí)際上,在MySQL5.7中,對該SQL進(jìn)行了改寫,改寫后的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來看,其消除了子查詢,導(dǎo)致結(jié)果未能實(shí)現(xiàn)預(yù)期效果,官方也證實(shí)了這一點(diǎn),https://bugs.mysql.com/bug.php?id=80131
很多人可能不以為然,認(rèn)為沒人會這樣寫,但在大名鼎鼎的stackoverflow中,該實(shí)現(xiàn)的點(diǎn)贊數(shù)就有116個(gè)-由此可見其受眾之廣,僅次于后面提到的“方法二”(點(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)行的,其會提示如下錯(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語句有關(guān),其要求select列表里只能出現(xiàn)分組列(即group by后面的列)和聚合函數(shù)(sum,avg,max等),這也是SQL92的標(biāo)準(zhǔn)。
但在工作中,卻經(jīng)??吹介_發(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)常被開發(fā)同事問到。 下面具體來看看,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ù)庫。
方法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)方式的性能對比
因上面測試案例的數(shù)據(jù)量太小,三種實(shí)現(xiàn)方式的結(jié)果都是秒出,僅憑執(zhí)行計(jì)劃很難直觀地看出實(shí)現(xiàn)方式的優(yōu)劣。
下面換上數(shù)據(jù)量更大的測試數(shù)據(jù),官方示例數(shù)據(jù)庫employees中的dept_emp表,https://github.com/datacharmer/test_db
表的相關(guān)信息如下,其中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)
從執(zhí)行時(shí)間上看,
方法1的時(shí)間最短,在有復(fù)合索引(deptno, fromdate)的情況下,結(jié)果瞬間就出來了,即使在沒有索引的情況下,也只消耗了0.75s。
方法2的時(shí)間最長,3個(gè)小時(shí)還是沒出結(jié)果。同樣的數(shù)據(jù),同樣的SQL,放到Oracle查,也消耗了87分49秒。
方法3的時(shí)間比較固定,無論是否存在索引,都維持在1.5s左右,比方法1的耗時(shí)要久。
這里,對之前提到的,MySQL 5.7中不再兼容的實(shí)現(xiàn)方式也做了個(gè)測試,在沒有任何索引的情況下,其穩(wěn)定在0.7s(性能并不弱,怪不得有人使用),而同等情況下,方法1穩(wěn)定在0.5s(哈,MySQL 5.6竟然比8.0還快)。但與方法1不同的是,其無法通過索引進(jìn)行優(yōu)化。
從執(zhí)行計(jì)劃上看,
方法1, 先將group by的結(jié)果放到臨時(shí)表中,然后再將該臨時(shí)表作為驅(qū)動表,來和dept_emp表進(jìn)行關(guān)聯(lián)查詢。驅(qū)動表?。ㄖ挥?條記錄),關(guān)聯(lián)列又有索引,無怪乎,結(jié)果能秒出。
方法2, 兩表關(guān)聯(lián)。其犯了SQL優(yōu)化中的兩個(gè)大忌。
1. 驅(qū)動表太大,其有331603條記錄。
2. 被驅(qū)動表雖然也有索引,但從執(zhí)行計(jì)劃上看,其只使用了復(fù)合索引 (dept_no, from_date)中的dept_no,而dept_no的選擇率又太低,畢竟只有9個(gè)部門。
方法3, 先把分析的結(jié)果放到一個(gè)臨時(shí)表中,然后再對該臨時(shí)表進(jìn)行處理。其進(jìn)行了兩次全表掃描,一次是針對dept_emp表,一次是針對臨時(shí)表。
所以,對于分組求最值的需求,建議使用方法1,其不僅符合SQL規(guī)范,查詢性能上也是最好的,尤其是在聯(lián)合索引的情況下。
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。
標(biāo)簽:贛州 三門峽 巴中 南陽 新余 商丘 揚(yáng)州 貴州
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《升級到MySQL5.7后開發(fā)不得不注意的一些坑》,本文關(guān)鍵詞 升級,到,MySQL5.7,后,開發(fā),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。