mysql优化详解

前言：

  在进行优化讲解之前，先请大家记住不要听信你看到的关于优化的“绝对真理”，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。

1. 优化方向

  从上图中可以看出，我们把数据库优化分为四个纬度：硬件，系统配置，数据库表结构，SQL 及索引

• 硬件： CPU、内存、存储、网络设备等

• 系统配置： 服务器系统、数据库服务参数等

• 数据库表结构： 高可用、分库分表、读写分离、存储引擎、表设计等

• Sql及索引： sql 语句、索引使用等

1. 从优化成本进行考虑：硬件 > 系统配置 > 数据库表结构 > SQL 及索引

2. 从优化效果进行考虑：硬件 < 系统配置 < 数据库表结构 < SQL 及索引

  本文就从优化成本最低，效果最好的SQL及索引来讲解

2. 数据页

  B+Tree 是为磁盘等外存储设备设计的一种平衡查找树，InnoDB 存储引擎就是用 B+Tree 实现其索引结构

  在了解 B+Tree 结构之前先解了这2个概念：

• 磁盘块：系统从磁盘读取数据到内存是以 磁盘块(block) 为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是要什么取什么。

• 数据页：InnoDB 是使用页来作为管理存储空间的基本单位，InnoDB 在把磁盘数据读入到内存时也是以页为基本单位，InnoDB 存储引擎中默认每个页的大小为16KB，可以修改为4K、8K、16K。数据页的概念和磁盘块类似，数据也页由多个磁盘块组成。

2.1. 数据页结构

  一个数据页从新建，到插入数据的过程演变如下所示，记录则是数据库表中的一条数据，所有的记录都存在一个数据页的 Free Space 中。

  当Free Space 全部用完后，还有新的记录插入则申请新的数据页。

1. User Records：一条数据记录，它的结构如图所示：

名称	大小(bit)	描述
预留位	1	先不用
delete_mask	1	标记该记录是否删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前组拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录
next_record	16	表示下一条记录的相对位置

2. Page Directory(页目录)：是对多个 User Records 进行管理的一个目录

• 将所有的正常记录（ Infimum 键值最小记录作为单独一组及 Supermum 键值最大记录）进行分组

• 每个组的最后一条记录（组内键值最大的那条记录）的头信息中 n_owned 记录所在的组共有几条记录

• 每个组最后一条记录的地址偏移量按顺序存储在一个目录，这个目录就是 Page Directory，目录中的这些地址偏移量被称为槽（slot）

3. Infimum + Supermum + User Records + Page Directory 结构图如下：

  当从磁盘上加载了一个数据页到内存中，怎么通过 id 查找出对应的 行记录呢？用上图数据模拟一个数据页中，查找 id = 6 的过程：（二分法查找slot，再遍历slot槽）

1. 计算中间槽的位置：(0 + 3) / 2 = 1，查看 slot1 对应的主键值是4，因为 主键4 小于 主键6。

   设 low = 1，high = 3 不变；

2. 重新计算中间槽位置：（1 + 3）/ 2 = 2，查看 slot2 对应的之间值是8，因为 主键8 大于 主键6

   low = 1 不变，设 high = 2

3. 因为 high - low = 1, 所以确定主键6记录在 slot2 位置。通过 slot1 找到该组最大主键4，该记录的 next_recode 记录了slot2 主键值5 的地址偏移量，遍历 slot2 对应的组，找到 主键6 的记录。

3 B+Tree数据结构

  各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录(Page Directory)，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，再遍历该槽对应分组中的记录即可快速找到指定的记录。

  简略图：

3.1 无索引查找

  当不构建 B + Tree 索引，直接以这样的双向链表查找数据有啥问题呢？

• 以主键id为搜索条件：假设要找记录为2的数据，先遍历第一个数据页 page 0，在一个数据页中，可以在 Page Directory 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录为2的数据；但是假如有1000个数据页，要找记录id=10000的数据，它在page500，那么就得 500次IO 加载 page0 到 page500 才能拿到数据。那么我们可不可以通过一种数据结构，根据 id 快速找到所在的数据页呢？ (解决方案：B + tree )

3.2 主键索引

  主索引：键值是主键id，data 是一行数据

  有如下 sql，它的执行计划是怎样的？

select * from table where id =  20;

  查找过程：

1. 读取根节点 page0，将数据页从磁盘加载到内存中，根据二分查找槽及遍历组。找到p1.

2. 读取 page1，加载到内存中，根据二分查找槽及遍历组，找到 p4.

3. 读取 page4，加载到内存中，根据二分查找槽及遍历组，找到key = 20的记录。

  mysql 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，对于高度为3的 b+tree ，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O 操作。

高度为3的B+tree的树，正常情况下能寸多少数据？

  一个数据页默认16KB，一般表的主键类型为INT（占用4个字节）或 BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16B * 1024 / (8B+8B)=1024个键值。

• 深度为3的 B+tree 主键索引：1024x1024x100约等于1亿条数据

• 深度为3的 B+tree 辅助索引：1024x1024x1024约等于10亿条数据

3.3 二级索引

  二级索引：键值是非主键字段，data是行数据的主键id。

  有如下 sql，它的执行计划是怎样的？

select id from table where key = 12;

  查找过程：

1. 读取根节点 page0，将数据从磁盘加载到内存中，根据二分查找槽及遍历组，找到 p1
2. 读取 page1，根据二分查找槽及遍历组，找到 p4
3. 读取 page4，page4 加载到内存。 因为 key 不是唯一索引，所有可能存在多条，同意二分查找槽及遍历组原则，可能会招到多条。这里找到 key = 12 两条记录（id = 5 和 id = 6）

  这里有个问题，为什么辅助索引的记录数据是主键id呢？

• 数据页大小有限制，当data数据过大，会导致一个数据页存储的key值数量小，也就意味着要查找等量的数据，要加载的数据页也更多，IO次数越多。

• 如果也记录行数据，相当于每建立一棵B+tree 都需要把所有的用户记录再都拷贝一遍，太浪费存储空间了。

3.4 组合索引

  组合索引是由2个字段组成，按最左列字段进行记录排序，如果相同，则按次字段排序。

组合索引字段顺序怎么选择呢？《⾼性能MySQL》⼀书中提到的⼀个经验法则：将选择性最⾼的列放到索引最前列。
“选择性最⾼”指的是差异性最⼤，也即这⼀列的重复值最少，这样建⽴起来的索引树，查找所需要的值时才会速度更快。

B + tree 在线构建工具

Mysql 的 B+tree 是对原生的有部分改造的。

4. 索引其它知识

4.1 索引覆盖

  索引覆盖：只需要在一棵索引树上，通过走索引方式就能获取SQL所需的所有列数据，无需回表，速度更快。

  现有 t_user 表，字段有：主键id、age、name、class_id，age 创建了二级索引。有如下sql：

select id from t_user where age = 10

  由于 age 字段创建了二级索引，并且二级索引的叶子节点的 data数据是主键id。所以该 sql 完全只需要通过一颗索引树查找就可以获取结果值，这就是索引覆盖

  那什么情况下索引不覆盖呢？现有如下sql：

select id, name from t_user where age = 10

  我们知道 age 创建的索引树上没有 name 的数据，要想获得 name 的数据，在通过 age 二级索引树找到对应主键id 后，再通过主键id去主键索引查询name的数据。这里要查询结果值需要经过两颗树，这就是索引未覆盖。那有办法让它变成索引覆盖吗？

  创建 （age, name）的组合索引 idx_age_name，该 sql 只需要走一颗 idx_age_name 组合索引即可获取结果值，这样又变成了索引覆盖。

4.2 回表

  回表：SQL经过二级索引树查询后，还需要经过主键索引树才能获取所需要的结果集。

  从这里看，其实回表和索引覆盖是互斥的。

  还是以上面的表为例子，比如现有如下 sql：

select * from t_user where age = 10

   * 是获取表中所有字段的数据，该 sql 通过 age 建立的二级索引拿到主键id，在把主键id作为主键索引条件搜索主键索引，读取全字段数据，这就是一个典型的回表现象。

4.3 索引下推

  索引下推（index condition pushdown ）简称 ICP，在 Mysql5.6 的版本上推出，用于优化查询；

注意：

• 索引下推发生在组合索引
• 索引下推触发条件必须 sql 会回表

  在不使用 ICP 的情况下，一个 sql 走二级索引查询到 n 行数据后，再拿这 n 行数据去回表查询得到 m 行数据，再经过 Sql_Server 的 where 条件过滤得到 m - o 行数据（我们假设 o 是 where 条件过滤的行数据）。
  在使用 ICP 的情况下，一个 sql 走二级索引查询到 n 行数据后，如果 where 字段的条件可以在 InnoDB 层过滤的话，会先在 InnoDb 层过滤，得到行数据 n - o 行，再拿 n - o 行数据去回表查询得到 m - o 行数据。

从上面我门可以看出什么？经过索引下推，可以减少回表的次数！

示例：
建表语句

CREATE TABLE `test_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int NOT NULL COMMENT '本地网',
  `region_id` int NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `i_lanId_regId` (`lan_id`,`region_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1010001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

经典问题？

select id from test_user where lan_id = 25 and region_id > 20;

select * from test_user where  lan_id = 25 and region_id > 20;

select id from test_user where lan_id > 26 and region_id = 20;

select * from test_user where lan_id > 26 and region_id = 20;

  以上四个 SQL，它们索引计划是怎样的？会走索引下推吗？

1. sql1

select id from test_user where lan_id = 25 and region_id > 20;

  where 后面条件满足组合索引 i_lanId_regId (lan_id,region_id) 排序规则，lan_id 字段和 region_id 字段都会用到索引。

  InnoDB 加载 page1 后，检索匹配 lan_id = 25 and region_id > 20 条件后，只需要加载 page 5，即可获取所要的数据。

1	SIMPLE	test_user	range	i_lanId_regId  Using where; Using index

  explain 执行命令如上，Using where; Using index，为什么还有 Using where 存在，不是2个字段都走索引吗？确实两个字段都走了索引，至于为啥显示 Using where，sqlServer 针对 >、<、<=、>= 条件还会再 sqlServer 层再暴力过滤一遍。

2. sql2

select * from test_user where  lan_id = 25 and region_id > 20;

  where 后面条件满足组合索引 i_lanId_regId (lan_id,region_id) 排序规则，lan_id 字段和 region_id 字段都会用到索引，同时 select * 会走回表操作。

1	SIMPLE	test_user  range  i_lanId_regId	Using index condition

  explain 执行命令如上，Using index condition，索引下推？这里显示的索引下推并不准确，因为 lan_id = 25 and region_id > 20 条件满足组合索引 i_lanId_regId (lan_id,region_id) 排序规则，都会走索引，不需要索引下推。因为 >条件即便走了索引也会展示 Using where;，再回表的话就会显示 Using index condition。

3. sql3

select id from test_user where lan_id > 26 and region_id = 20;

  where 后面条件 lan_id > 25 按组合索引 i_lanId_regId (lan_id,region_id) 匹配后，region_id = 20并不能满足顺序规则，所以 lan_id > 25能走索引匹配，而 region_id = 20 则会在 sqlServer 层进行 where 过滤。

  InnoDB 加载 page1 后，只能根据lan_id > 26条件检索匹配，无法匹配 region_id = 20 条件在哪个数据页，所以它会加载全部符合 lan_id > 26 的数据页，即 page5、page6，之后这些数据交给 sqlServer 层过滤 region_id = 20 条件。有人会说，我们开启索引下推， region_id = 20 条件可以在 InnoDB 层过滤不是吗？可以，InnoDB 没这样做，因为这里 region_id = 20 条件在 InnoDB 层或者 SqlServer 层过滤已经不重要了，page5 和 page 6 数据已经通过慢操作 IO 从磁盘加载到内存；而 InnoDB 层和 SqlServer 层交互是内存接口级别，不涉及IO，所以 region_id = 20 条件在哪过滤都不会影响 sql 性能。

1	SIMPLE	test_userrange	i_lanId_regId  Using where; Using index

  explain 执行命令如上，没有触发索引下推，没有回表的情况下，触不触发索引下推都没任何意义

4. sql4

select * from test_user where lan_id > 26 and region_id = 20;

  这里 sql4 相较 sql3 多了一个回表操作。可以看作先执行 select id 然后再回表。

1	SIMPLE	test_user	rangei_lanId_regId	Using index condition

  explain 执行命令如上，触发了索引下推。这里有人会说，上面不是说 >条件即便走了索引也会展示 Using where;，再回表的话就会显示 Using index condition。这里的索引下推并不能证明 region_id = 20 条件在 InnoDB 层过滤了。确实是，证明方法可以用 mysql 锁来证明！具体证明方案，后面再出。

• 在 mysql5.6 之前
  

• 在 mysql5.6 版本
  

5. 常用的 sql 分析命令

5.1 explain 执行计划分析

详细请看 explain 执行计划分析

5.2 show warnings 查看真实语句

explain select * from  test_user where user_id in (select user_id from test_user where phone > '18712846978');
SHOW WARNINGS;

同时执行 explain 语句 + SHOW WARNINGS。可以看优化后的真实 sql

/* select#1 */ select `test`.`test_user`.`id` AS `id`,`test`.`test_user`.`user_id` AS `user_id`,`test`.`test_user`.`user_name` AS `user_name`,`test`.`test_user`.`phone` AS `phone`,`test`.`test_user`.`lan_id` AS `lan_id`,`test`.`test_user`.`region_id` AS `region_id`,`test`.`test_user`.`create_time` AS `create_time` from `test`.`test_user` semi join (`test`.`test_user`) where ((`<subquery2>`.`user_id` = `test`.`test_user`.`user_id`) and (`test`.`test_user`.`phone` > '18712846978'))

从这里可以看出，in子查询被优化成 semi join 语句

5.3 trace 分析 SQL 优化器

// TODO

6. MySql优化实战

建表语句：

CREATE TABLE `test_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int NOT NULL COMMENT '本地网',
  `region_id` int NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_uid_phone` (`user_id`,`phone`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3300001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我本地随机生成了 300w 条数据

6.1 limit关键字优化

  现有 test_user 表有300w条数据。有个功能是最简单分页查询，sql如下：

select * from test_user  order by create_time desc , id desc limit 1500000, 100;

响应时间如下：

explain 分析如下：

   当 offer 越大的时候，这条 sql 查询越慢，比如当 offer = 2800000，size = 100. 这条sql已经是1.7s，你有办法优化吗？

优化方向：考虑使用二级索引，减少io；修改sql如下：

select tu1.* from test_user tu1 RIGHT JOIN (select id from test_user  limit 2800000, 100) tu2 on tu1.id = tu2.id;

响应时间如下：

explain 分析如下：

   这条 sql 只要 0.249s，为啥快了这么多？

原因：

1. 假设表中只有主键索引：数据最先是在磁盘上，我只需要第2800000到2800000+ 100的数据，但是执行引擎并不知道第2800000数据是哪条记录。所有这条sql会全表查询，把记录和条件匹配，直到符合条件的2800000+ 100的数据都加载进内存，再舍去前面条，才停止执行。
2. 优化后sql会基于非主键索引，读取2800000 + 100条数据。和上面sql相比：我们知道非主键索引叶子节点能存储的数据比主键索引叶子节点能存储的数据多，也就是加载同样的数据，非主键索引要加载的数据页比主键索引要少很多，等同于需要 IO 次数少很多。接下来拿这100条id再基于join连接查询效率是非常快的。

6.2 in（sql语句）优化

  现有user表 有500w条数据。table表就三条数据（uid: 1,2,3）
现有如下sql：

select * from user where id in (select uid from table1);

  它的执行效率如何，你有办法优化吗？

  主观意识上我们会认为先执行 in里面的语句， 拿到三条uid（1，2，3）数据再对user表进行主键索引查询.这是非常快的，也是我们想要的查询方式。

  在mysql5.5版本：先explain extended分析语句，再执行SHOW WARNINGS; 得到真实的sql如下

SELECT `数据库名`.`user`.`id` AS `id`,`数据库名`.`user `.`name` AS `name`,`数据库名`.`user`.`age` AS `age`
FROM `数据库名`.`user` WHERE <in_optimizer>(`数据库名`.`user`.`id`,<EXISTS>(<primary_index_lookup>(<CACHE>(`数据库名`.`user`.`id`) IN table1 ON PRIMARY)))

  也就是说，执行引擎将 in 语句优化成 exists 语句。再对这条 sql 分析：先执行user表全表扫描，加载500w条数据，然后拿 user 表的 id 去 table1 表进行匹配。user 表的 500w 条数据 导致 table1 表匹配就得 500w 次。这条sql在5.5版本是非常慢的。

  在mysql5.7版本

  同样先执行 explain extended select * from user where id in (select uid from table1) ；

  再执行SHOW WARNINGS；

  得到真实的sql如下

  也就是说，在5.7版本执行引擎将 in 子查询语句优化成 joi n连接，从这条 sql 可以看出，in 里面的表被优化成驱动表，in 的外表被优化成被驱动表，这种连接方式也是符合我们查询意愿的。

6.3 in（常量）优化

select id from test_user where user_id in ('13210956966', '18740773550', '18932193843');

  它的执行计划是遍历 in () 里面的常量，然后分别进行二级索引检索。当 in () 常量数超过二级索引rows阈值比例时，执行计划会变成全表扫描

6.5 count(id)、count(1)、count(*) 区别

一般情况下，是没有区别的。
如果在开发中确实需要用到count()聚合，那么优先考虑count()，因为mysql数据库本身对于count()做了特别的优化处理。

count(*)改进

• 在5.7.18版本之前，InnoDB处理select count(*) 是通过扫描聚簇索引，来获取总记录数。
• 从5.7.18版本开始，InnoDB扫描一个最小的可用的二级索引来获取总记录数，或者由SQL hint来告诉优化器使用哪个索引。如果二级索引不存在，InnoDB将会扫描聚簇索引。

原因：加载同杨的数据量，由于二级索引叶子节点存放的是主键Id，所有走二级索引需要的 io 次数更少

6.7 范围查寻解析

  现有如下sql

1. SELECT * FROM t_class WHERE id <= 6，它的执行计划是？
   首先检索主键索引从最小下id=1拿取记录，然后通过链表关系依次向下找出 id 记录返回给 sqlserver，在 sqlserver 进行判断id=7不符合条件，终止查找。得到结果集合（id = 1,2,3,4,5,6）。

2. SELECT * FROM t_class WHERE id >= 6，它的执行计划是？
   首先检索出主键索引id=6或者大于6且最接近6的id记录。然后通过链表关系依次往下寻找出所有记录数。

6.8 in 和 EXISTS 区别

mysql 版本 8.*

test_user 表数据量 20w

CREATE TABLE `test_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int NOT NULL COMMENT '本地网',
  `region_id` int NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_uid_phone` (`user_id`,`phone`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_phone` (`phone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3300001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

test_user_v2表数据量 300w

CREATE TABLE `test_user_v2` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int NOT NULL COMMENT '本地网',
  `region_id` int NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_uid_phone` (`user_id`,`phone`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_phone` (`phone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3300001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

分表执行下面 4条sql，并看优化后的sql是怎么样的？

1. sql1

explain select t2.* FROM test_user_v2 t2 where EXISTS (select id from test_user t where t.user_id =  t2.user_id);
show WARNINGS;

优化后的sql

/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`user_id` AS `user_id`,`test`.`t2`.`user_name` AS `user_name`,`test`.`t2`.`phone` AS `phone`,`test`.`t2`.`lan_id` AS `lan_id`,`test`.`t2`.`region_id` AS `region_id`,`test`.`t2`.`create_time` AS `create_time` from `test`.`test_user_v2` `t2` semi join (`test`.`test_user` `t`) where (`test`.`t2`.`user_id` = `test`.`t`.`user_id`)

2. sql2

explain select t.* FROM test_user t where EXISTS (select id from test_user_v2 t2 where t2.user_id =  t.user_id);
show WARNINGS;

优化后的sql

/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`user_id` AS `user_id`,`test`.`t`.`user_name` AS `user_name`,`test`.`t`.`phone` AS `phone`,`test`.`t`.`lan_id` AS `lan_id`,`test`.`t`.`region_id` AS `region_id`,`test`.`t`.`create_time` AS `create_time` from `test`.`test_user` `t` semi join (`test`.`test_user_v2` `t2`) where (`test`.`t2`.`user_id` = `test`.`t`.`user_id`)

3. sql3

explain select t.* FROM test_user t where t.user_id in (select user_id from test_user_v2 t2 );
show WARNINGS;

优化后的sql

/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`user_id` AS `user_id`,`test`.`t`.`user_name` AS `user_name`,`test`.`t`.`phone` AS `phone`,`test`.`t`.`lan_id` AS `lan_id`,`test`.`t`.`region_id` AS `region_id`,`test`.`t`.`create_time` AS `create_time` from `test`.`test_user` `t` semi join (`test`.`test_user_v2` `t2`) where (`test`.`t2`.`user_id` = `test`.`t`.`user_id`)

4. sql4

explain select t2.* FROM test_user_v2 t2 where t2.user_id in (select user_id from test_user t );
show WARNINGS;

优化后的sql

/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`user_id` AS `user_id`,`test`.`t2`.`user_name` AS `user_name`,`test`.`t2`.`phone` AS `phone`,`test`.`t2`.`lan_id` AS `lan_id`,`test`.`t2`.`region_id` AS `region_id`,`test`.`t2`.`create_time` AS `create_time` from `test`.`test_user_v2` `t2` semi join (`test`.`test_user` `t`) where (`test`.`t2`.`user_id` = `test`.`t`.`user_id`)

结论：从上面4个优化后的 sql 可以看出，在 8.* 版本mysql 针对 in、exists做了大量优化，4个优化后的sql 执行计划都是一样的，都采用 smei join。
这里你会问什么是 semi join。答：所谓的 semi-join 就是一个子查询，它主要用于去重。可以通俗的认为 semi join 就是 join 后的结果集去重

5. sql5
   再来看第五个sql

explain select t2.* FROM test_user_v2 t2 join test_user t on t.user_id = t2.user_id;
show WARNINGS;

优化后的sql

/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`user_id` AS `user_id`,`test`.`t2`.`user_name` AS `user_name`,`test`.`t2`.`phone` AS `phone`,`test`.`t2`.`lan_id` AS `lan_id`,`test`.`t2`.`region_id` AS `region_id`,`test`.`t2`.`create_time` AS `create_time` from `test`.`test_user_v2` `t2` join `test`.`test_user` `t` where (`test`.`t2`.`user_id` = `test`.`t`.`user_id`)

分表统计上面5个sql的执行时耗

类型
sq1	0.503s
sq2	0.507s
sq3	0.506s
sq4	0.509s
sq5	0.488s
可以看出 semi join 比 join 多了一丢丢去重的耗时