升级到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 |
+-------+----------+--------+--------+
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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
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来看,其消除了子查询,导致结果未能实现预期效果,官方也证实了这一点,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 |
+--------+----------+----------+
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
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)

从执行时间上看,

方法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规范,查询性能上也是最好的,尤其是在联合索引的情况下。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • Mysql5升级到Mysql5.5的方法

    安装5.5依赖安装包 代码如下 yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl* 安装cmake 代码如下 yum install -y cmake 在升级前,建议先将之前5.1的mysql及数据库目录备份一下 停止掉mysql 代码如下 service mysql stop 升级mysql 5.5.36 代码如下 http://download.csdn.ne

  • Win下Mysql5.6升级到5.7的方法

    写在前面 MySQL的升级方式分为两种:原地升级和逻辑升级.这两种升级方式,本质没有什么区别的.只是在对数据文件的处理上有些区别而已.原地升级是直接将数据文件进行拷贝,而逻辑升级对数据文件的处理方式是通过逻辑导出导入,需要用到mysqldump. 逻辑升级大家都理解,这种方式在数据量比较大的情况下花费时间比较长.所以今天我们来讲讲原地升级. 原地升级 1.将现有的mysql关闭.使用cmd窗口,进入到mysql目录下面,将mysql服务移除. X:\Ares\bin\mysql5.6\bin>m

  • mysql版本5.5.x升级到5.6.x步骤分享

    大概步骤是: 把配置文件添加:skip-grant-tables参数,把basedir升级成新版本,启动mysql,执行命令:mysql_upgrade升级一下字典信息,然后flush privileges;刷新授权表,注意:如果是使用共享表空间,最好是把数据导出来再导入新版,如果是使用独立表空间可以使用mysql_upgrade升级.如: 1. shell > service mysqld stop  #把实例停掉 复制代码 代码如下: shell > mv /usr/local/mysql

  • MySQL 升级方法指南大全第1/5页

    如果都是比较新的版本的升级可以参考下面的文章,MySQL数据库的版本更新很快,新的特性也随之不断的更新,更主要的是解决了很多影响我们应用的BUG,为了让我们的MySQL变得更美好,我们有必要去给它升级,尽管你会说它现在已经跑得很好很稳定完全够用了.下面我们来看看几种常用的升级方法. 介绍之前,我们先做一些声明,MySQL采用二进制包来安装,升级都是在同一台DB Server上操作. 第一种,很简单,适用于任何存储引擎. 1. 下载并安装好新版本的MySQL数据库,并将其端口改为3307(避免和旧

  • Centos MySQL 5.7安装、升级教程

    MySQL 5.7安装.升级笔记分享: 卸载当前的 MySQL 查看当前 MySQL 版本: [root@coderknock ~]# mysql -V mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i386) using readline 5.1 停止 MySQL 服务 [root@coderknock ~]# service mysqld stop Stopping mysqld: [ OK ] 备份数据[数据不重要可以忽略] 备

  • win2008下mysql8.0.11升级mysql8.0.17版本详细步骤

    升级背景: 为了解决mysql低版本的漏洞,从mysql5.5升级到了8.0.11版本,再次升级到了8.0.17版本(从版本是2019.7.22发布) mysql8.0.10以上的版本,安装步骤基本一样 经过学习,得知: MySQL的升级方式分为两种:原地升级和逻辑升级.这两种升级方式,本质没有什么区别的.只是在对数据文件的处理上有些区别而已.原地升级是直接将数据文件进行拷贝,而逻辑升级对数据文件的处理方式是通过逻辑导出导入,需要用到mysqldump. 逻辑升级大家都理解,这种方式在数据量比较

  • 实战MySQL升级的最佳方法

    MySQL5.7新增了不少新功能,比如:Online DDL.多源复制.增强半同步.表空间传输.sys 库.Group Replication等.最近终于等到了一个机会,将MySQL升级到5.7,很是兴奋不已. MySQL升级的概述 MySQL升级的实质: 对数据字典的升级 数据字典有:mysql.information_schema.performance_schema.sys schema. MySQL升级的两种方式: in-place upgrade: 适合小版本的升级. 即 关闭当前的M

  • linux下安装升级mysql到新版本(5.1-5.7)

    今天需要把linux服务器上的mysql版本从5.1更新到5.7,那么以下内容作为记录,提供以后安装使用手册 第一步:检查linux的操作系统版本 复制代码 代码如下: cat /etc/issue 第二步:在mysql官网上下载5.7的版本 http://dev.mysql.com/downloads/file.php?id=451627 第三步:检查linux上以前安装的mysql版本 复制代码 代码如下: rpm -qa | grep mysql 第四步:如果出现mysql的一些安装版本,

  • 图解MYSQL的安装和数据升级第1/2页

    如果你是全新安装,就可以跳过这一步啦 找到你的Mysql的数据库目录下的 ragnarok 数据库目录,例如我的Mysql安装在D:\mysql 这个目录下,就到 D:\mysql\data\ 目录下找到 ragnarok 数据库目录把它拷贝一份到其它地方![升级你的ODBC - MySQL ODBC driver ]首先打开你的控制面版,找到 [添加删除程序] 把旧的MySQL ODBC driver 删除然后安装MyODBC-3.51.11-1-win.exe [删除你电脑里的旧版MYSQL

  • Mysql升级到5.7后遇到的group by查询问题解决

    发现问题 最近在将mysql升级到mysql 5.7后,进行一些group by 查询时,比如下面的 SELECT *, count(id) as count FROM `news` GROUP BY `group_id` ORDER BY `inputtime` DESC LIMIT 20 就会报如下错误: SELECT list is not in GROUP BY clause and contains nonaggregated column 'news.id' which is not

随机推荐