MySQL 5.7 和 8.0 几处细节上的差异

数据库交流

发布于 2022-06-15 13:56:43

2.4K0

发布于 2022-06-15 13:56:43

文章被收录于专栏：悦专栏

MySQL 8.0 相对于 MySQL 5.7，有很多新特性，比如：快速加列、原子 DDL、不可见索引、额外端口、角色管理等。这一节内容，就不讲这些新特性了，只来聊聊最近在工作学习过程中遇到的几处细节上的差异。

1 int 字段类型的差异

比如下面的建表语句，在 5.7 能正常执行：

CREATE TABLE `t1` ( 
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

但是在 8.0.17 开始后的版本，执行上面的建表语句，会有如下 warnings：

Integer display width is deprecated and will be removed in a future release.

在上面的建表语句中，int(11) 中的 11 表示最大显示宽度，从 MySQL 8.0.17 开始，int 类型就不推荐使用显示宽度这个属性了。因此 8.0 建议使用单独的 int 来定义整数数据类型，如下：

CREATE TABLE `t1` ( 
`id` int NOT NULL auto_increment,
`a` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2 创建用户和赋权差异

MySQL 5.7，可以直接使用 grant 命令，用户和赋权都能完成。

grant select on test.* to 'test_user'@'127.0.0.1' identified by 'ddafsduGdsag';

8.0 版本下不 create user 的情况下执行 grant 会报如下错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'ddafsduGdsag'' at line 1

因此 MySQL 8.0 如果需要创建用户并赋权，必须要先 create user，再执行 grant 命令，操作如下：

create user 'test_user'@'127.0.0.1' identified with mysql_native_password by 'ddafsduGdsag'; 
grant select on test.* to 'test_user'@'127.0.0.1';

3 Block Nested-Loop Join 算法

为了方便下面的实验，我们首先创建测试表并写入数据：

CREATE DATABASE test; /* 创建测试使用的database，名为test */
use test; /* 使用test这个database */
drop table if exists t1; /* 如果表t1存在则删除表t1 */
CREATE TABLE `t1` ( /* 创建表t1 */
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */

delimiter ;;
create procedure insert_t1() /* 创建存储过程insert_t1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入10000条数据到表t1的存储过程insert_t1 */
call insert_t1(); /* 运行存储过程insert_t1 */
drop table if exists t2; /* 如果表t2存在则删除表t2 */
create table t2 like t1; /* 创建表t2，表结构与t1一致 */
insert into t2 select * from t1 limit 100; /* 将表t1的前100行数据导入到t2 */

对于下面这条 SQL（注意：两张表的 b 字段都没索引）：

select * from t1 inner join t2 on t1.b = t2.b;

在 5.7 版本中的执行计划为：

在 Extra 发现 Using join buffer (Block Nested Loop)，这个就说明该关联查询使用的是 Block Nested Loop 算法（后面简称：BNL 算法）。

BNL 算法的思想是：

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做循环对比，如果满足 join 条件，则返回结果给客户端。所以 BNL 是一个双重循环，时间复杂度为 O(n^2)

如果 join_buffer 放不下的话，那将分成多个块，每个块再进行一次上面的操作。

在 8.0 版本中的执行计划如下：

在 Extra 发现 Using join buffer (hash join)，从 MySQL 8.0.20 开始，hash join 替换了 BNL。详情可参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html。

hash join 算法的思想是：

先把小一点的表采用 hash 函数，将连接键存放到内存的 hash table 中，然后扫描另外一张表，把另外一张表每一行取出来跟 hash table 中的数据做对比，如果满足 join 条件，则返回结果给客户端。与 BNL 算法相比，hash join 只有一次循环，时间复杂读为 O(n)。

当表太大，无法一次性放入内存，就分成多个块，每个块再进行一次上面的操作。

对于上面列子，我们可以使用下面的方式查看 hash join 的使用详情：

explain format=tree select * from t1 inner join t2 on t1.b = t2.b\G

默认情况下，只要 MySQL 版本是 8.0.20 及以后的版本，hash join 默认开启的。

对于 hash join 和 BNL 的性能对比，可以参考：https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/。

4 参考文档

Block Nested-Loop Join Algorithm：https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html
Hash Join Optimization：https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
Hash join in MySQL 8：https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-04-26，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库