• 售前

  • 售后

热门帖子
入门百科

升级到MySQL5.7后开发不得不注意的一些坑

[复制链接]
索支较 显示全部楼层 发表于 2021-10-26 13:47:16 |阅读模式 打印 上一主题 下一主题
媒介
前段时间,将线上MySQL数据库升级到了5.7。考虑到大概产生的不兼容性,在升级之前,确实也是战战兢兢,固然测试情况,开发情况早在半年前就已提前升级。
基于前期的调研和朋友的反馈,与开发相关的主要有两点:
sql_mode
MySQL 5.6中,其默认值为"NO_ENGINE_SU BSTITUTION",可明白为非严格模式,譬如,对自增主键插入空字符串'',固然提示warning,但并不影响自增主键的生成。
但在MySQL 5.7中,其就调整为了严格模式,对于上面这个,其不会提示warning,而是直接报错。
分组求最值
分组求最值的某些写法在MySQL5.7中得不到预期结果,这点,相对来说比较潜伏。
此中,第一点是可控的,毕竟可以调整参数。而第二点,却是不可控的,没有参数与之相关,需要开发Review代码。
下面具体来看看
测试数据
  1. mysql> select * from emp;
  2. +-------+----------+--------+--------+
  3. | empno | ename | sal | deptno |
  4. +-------+----------+--------+--------+
  5. | 1001 | emp_1001 | 100.00 | 10 |
  6. | 1002 | emp_1002 | 200.00 | 10 |
  7. | 1003 | emp_1003 | 300.00 | 20 |
  8. | 1004 | emp_1004 | 400.00 | 20 |
  9. | 1005 | emp_1005 | 500.00 | 30 |
  10. | 1006 | emp_1006 | 600.00 | 30 |
  11. +-------+----------+--------+--------+
  12. rows in set (0.00 sec)
复制代码
此中,empno是员工编号,ename是员工姓名,sal是工资,deptno是员工所在部分号。
业务的需求是,求出每个部分中工资最高的员工的相关信息。
在MySQL5.6中,我们可以通过下面这个SQL来实现,
  1. SELECT
  2. deptno,ename,sal
  3. FROM
  4. ( SELECT * FROM emp ORDER BY sal DESC ) t
  5. GROUP BY
  6. deptno;
复制代码
结果如下,可以看到,其确实实现了预期结果。
  1. +--------+----------+--------+
  2. | deptno | ename | sal |
  3. +--------+----------+--------+
  4. | 10 | emp_1002 | 200.00 |
  5. | 20 | emp_1004 | 400.00 |
  6. | 30 | emp_1006 | 600.00 |
  7. +--------+----------+--------+
复制代码
再来看看MySQL5.7的结果,竟然不一样。
  1. +--------+----------+--------+
  2. | deptno | ename | sal |
  3. +--------+----------+--------+
  4. | 10 | emp_1001 | 100.00 |
  5. | 20 | emp_1003 | 300.00 |
  6. | 30 | emp_1005 | 500.00 |
  7. +--------+----------+--------+
复制代码
实际上,在MySQL5.7中,对该SQL举行了改写,改写后的SQL可通过explain(extended) + show warnings查看。
  1. mysql> explain select deptno,ename,sal from (select * from emp order by sal desc) t group by deptno;
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  5. | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  7. row in set, 1 warning (0.00 sec)
  8. mysql> show warnings\G
  9. *************************** 1. row ***************************
  10. Level: Note
  11. Code: 1003
  12. 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`
  13. 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中是不能直接运行的,其会提示如下错误:
  1. 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调整为了
  1. 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。
  1. mysql> select deptno,ename,max(sal) from emp group by deptno;
  2. +--------+----------+----------+
  3. | deptno | ename | max(sal) |
  4. +--------+----------+----------+
  5. | 10 | emp_1001 | 200.00 |
  6. | 20 | emp_1003 | 400.00 |
  7. | 30 | emp_1005 | 600.00 |
  8. +--------+----------+----------+
  9. rows in set (0.01 sec)
复制代码
着实不明白,这里的ename在业务层有何意义,毕竟,他并不是工资最高的那位员工。
分组求最值,MySQL的实现方式
着实分组求最值是一个很广泛的需求。在工作中,也经常被开发同事问到。 下面具体来看看,MySQL中有哪些实现方式。
方法1
  1. SELECT
  2. e.deptno,
  3. ename,
  4. sal
  5. FROM
  6. emp e,
  7. ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t
  8. WHERE
  9. e.deptno = t.deptno
  10. AND e.sal = t.maxsal;
复制代码
方法2
  1. SELECT
  2. a.deptno,
  3. a.ename,
  4. a.sal
  5. FROM
  6. emp a
  7. LEFT JOIN emp b ON a.deptno = b.deptno
  8. AND a.sal < b.sal
  9. WHERE
  10. b.sal IS NULL;
复制代码
这两种实现方式,着实是通用的,不仅实用于MySQL,也实用于其它主流关系型数据库。
方法3

MySQL 8.0推出了分析函数,其也可实现类似功能。
  1. SELECT
  2. deptno,
  3. ename,
  4. sal
  5. FROM
  6. (
  7. SELECT
  8. deptno,
  9. ename,
  10. sal,
  11. LAST_VALUE ( sal ) OVER ( PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) maxsal
  12. FROM
  13. emp
  14. ) a
  15. WHERE
  16. sal = maxsal;
复制代码
三种实现方式的性能对比
因上面测试案例的数据量太小,三种实现方式的结果都是秒出,仅凭实行筹划很难直观地看出实现方式的优劣。
下面换上数据量更大的测试数据,官方示例数据库employees中的dept_emp表,https://github.com/datacharmer/test_db
表的相关信息如下,此中emp_no是员工编号,dept_no是部分编号,from_date是入职日期。
  1. mysql> show create table dept_emp\G
  2. *************************** 1. row ***************************
  3. Table: dept_emp
  4. Create Table: CREATE TABLE `dept_emp` (
  5. `emp_no` int(11) NOT NULL,
  6. `dept_no` char(4) NOT NULL,
  7. `from_date` date NOT NULL,
  8. `to_date` date NOT NULL,
  9. KEY `dept_no` (`dept_no`,`from_date`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  11. row in set (0.00 sec)
  12. mysql> select count(*) from dept_emp;
  13. +----------+
  14. | count(*) |
  15. +----------+
  16. | 331603 |
  17. +----------+
  18. row in set (0.09 sec)
  19. mysql> select * from dept_emp limit 1;
  20. +--------+---------+------------+------------+
  21. | emp_no | dept_no | from_date | to_date |
  22. +--------+---------+------------+------------+
  23. | 10001 | d005 | 1986-06-26 | 9999-01-01 |
  24. +--------+---------+------------+------------+
  25. row in set (0.00 sec)
复制代码
方法1
  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;
  2. rows in set (0.00 sec)
  3. 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;
  4. +----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
  5. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra  
  6. +----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
  7. | 1 | PRIMARY | <derived2> | NULL | ALL | NULL  | NULL | NULL | NULL   | 9 | 100.00 | Using where  
  8. | 1 | PRIMARY | d  | NULL | ref | dept_no | dept_no | 19 | t.dept_no,t.max_hiredate | 5 | 100.00 | NULL   
  9. | 2 | DERIVED | dept_emp | NULL | range | dept_no | dept_no | 16 | NULL   | 9 | 100.00 | Using index for group-by
  10. +----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
复制代码
方法2
  1. 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;
  2. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra   |
  4. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
  5. | 1 | SIMPLE | a | NULL | ALL | NULL  | NULL | NULL | NULL  | 331008 | 100.00 | NULL   |
  6. | 1 | SIMPLE | b | NULL | ref | dept_no | dept_no | 16 | slowtech.a.dept_no | 41376 | 19.00 | Using where; Using index |
  7. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
  8. rows in set, 1 warning (0.00 sec)
复制代码
方法3
  1. 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;
  2. rows in set (1.57 sec)
  3. 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;
  4. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  5. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  |
  6. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  7. | 1 | PRIMARY | <derived2> | NULL | ALL | NULL  | NULL | NULL | NULL | 331008 | 100.00 | Using where |
  8. | 2 | DERIVED | dept_emp | NULL | ALL | NULL  | NULL | NULL | NULL | 331008 | 100.00 | Using filesort |
  9. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  10. 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规范,查询性能上也是最好的,尤其是在团结索引的情况下。
总结
以上就是这篇文章的全部内容了,盼望本文的内容对各人的学习大概工作具有肯定的参考学习价值,如果有疑问各人可以留言交流,谢谢各人对草根技术分享的支持。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作