MySQL 8.0 相对于 MySQL 5.7
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/