MySQL 5.7 和 8.0 几处细节上的差异
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/