MySQL-原理篇

学习核心

• 掌握SQL的执行顺序

  • 查询语句的执行顺序
  • 新增语句的执行顺序
  • 删除语句的执行顺序

• 掌握核心关键字的工作原理、核心流程

  • order by的工作原理
  • join的工作原理
  • count的工作原理
  • 子查询的工作原理
  • group by的工作原理

学习资料

• MySQL 实战45讲
• MySQL 是怎样运行的：从根儿理解MySQL
• MySQL必知必会

SQL的执行顺序

1.标准查询语句SQL分析

# 标准查询语句SQL定义
select -> 普通查询、聚合函数、distinct
from 表
join 子句
where 条件过滤
group by 分组
order by 排序
having 过滤

标准查询语句SQL执行顺序分析

• 确定表关系并获取初筛数据：先执行from、join来确定表之间的连接关系，得到初步的数据
• where数据过滤：执行 where 子句对数据进行初筛
• 分组：执行 group by 子句进行分组
• 分组过滤：各组分别执行having子句进行普通筛选或者聚合函数筛选
• 获取select列信息：根据筛选出的数据，select要获取的字段（可以是普通查询、聚合函数结果等）
• 去重：如果有distinct限定，则需要将查询结果进行去重
• 合并并排序：最后合并各组的查询结果，并按照order by的条件进行排序

案例分析

# 数据表构建：员工表、薪资表
CREATE TABLE `t_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `name` varchar(5) DEFAULT NULL COMMENT '员工姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2024000 DEFAULT CHARSET=utf8 COMMENT='员工信息表';

CREATE TABLE `t_salary` (
  `sid` int(11) NOT NULL COMMENT '员工ID',
  `salary` int(11) NOT NULL COMMENT '员工工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资信息表';

# 填充数据
INSERT INTO `t_staff` (`id`, `name`)
VALUES
	(2024000, '张三'),
	(2024001, '李四'),
	(2024002, '王五'),
	(2024003, '赵六'),
	(2024004, '钱七'),
	(2024005, '孙八'),
	(2024006, '吴九'),
	(2024007, '白十');
INSERT INTO `t_salary` (`sid`, `salary`)
VALUES
	(2024000, 8000),
	(2024001, 4000),
	(2024002, 5000),
	(2024003, 10000),
	(2024004, 5000),
	(2024005, 3000),
	(2024006, 20000),
	(2024007, 7500);

# SQL分析（此处仅为了剖析SQL的执行，无实际业务含义，具体要结合业务场景去扩展理解）

# 1.from、left join 获取初步结果
select s.*,sa.*
from t_staff s
left join t_salary sa on s.`id` = sa.`sid`

# 2.where 过滤数据
select s.*,sa.*
from t_staff s
left join t_salary sa on s.`id` = sa.`sid`
where sa.`salary` > 6000

# 3.分组
select s.id
from t_staff s
left join t_salary sa on s.`id` = sa.`sid`
where sa.`salary` > 6000
group by s.`id` 

# 4.分组过滤
select s.id
from t_staff s
left join t_salary sa on s.`id` = sa.`sid`
where sa.`salary` > 6000
group by s.`id` 
having s.`id` > 2024005

# 5.排序
select s.id
from t_staff s
left join t_salary sa on s.`id` = sa.`sid`
where sa.`salary` > 6000
group by s.`id` 
having s.`id` > 2024005
order by s.id desc

# 6.指定返回记录数（先排序后截取记录）
select s.id
from t_staff s
left join t_salary sa on s.`id` = sa.`sid`
where sa.`salary` > 6000
group by s.`id` 
having s.`id` > 2024005
order by s.id desc
limit 1

• where & having 的使用
  • where 中只能使用普通函数；having 中可以是普通条件筛选或聚合函数
  • 一般情况下有having可以不写where，可以将where条件放在having中
• 分组和条件过滤的先后
  • 使用where再group by：先将不满足where条件的数据过滤掉，然后再去分组
  • 使用group by再having：先进行分组，然后再将不满足having条件的数据过滤掉
  • 这两种方式实现的效果其实没有区别

核心关键字的工作原理（MySQL-5.7.44）

1.order by的工作原理

MySQL实战45讲-16.order by是如何工作的

• 理解全字段排序、rowid排序的概念和场景应用
• 理解如何优化order by语句

案例准备

# 创建表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

# 检索：筛选城市为杭州的记录，并按照name排序（选出1000条数据）
select city,name,age from t where city='杭州' order by name limit 1000  ;

​ 案例中为了避免全表扫描，为city字段加上了索引。通过explain命令查看语句的执行情况

explain select city,name,age from t where city='杭州' order by name limit 1000  ;

​ 结合查询结果进行分析，Extra中的Using filesort表示需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer

# 模拟创建数据

# 第一步：创建函数
delimiter //

DROP PROCEDURE
IF
    EXISTS proc_buildata;
CREATE PROCEDURE proc_buildata ( IN loop_times INT ) BEGIN
DECLARE var INT DEFAULT 0;
WHILE
    var < loop_times DO

    SET var = var + 1;
INSERT INTO `t` ( `id`, `city`, `name`, `age`, `addr`)
VALUES
    ( var, '杭州', 'name' , 18, '浙江杭州' );

END WHILE;

END // delimiter;

# 第二步：调用上面生成的函数，即可插入数据，建议大家造点随机的数据。比如改改城市和订单数量
CALL proc_buildata(4000);

排序算法1：全字段排序

核心流程：检索满足条件的数据 =》按照name字段对记录排序 =》根据排序结果返回前1000行数据

执行流程原理分析：暂且将这个流程称为全字段排序

• 【1】初始化sort_buffer放入name、city、age三个字段
• 【2】根据索引city找到第一个满足city='杭州'条件的主键ID（对应图中ID_X）
• 【3】到对应的主键ID索引取出整行数据，择取其中的name、city、age值并存入sort_buffer中
  • 继续根据索引city取下一个记录的主键ID，以此类推依次取值，直到city的值不满足查询条件位置（对应例图中检索到ID_Y的位置）
• 【4】所有满足条件的数据检索完成，则对soft_buffer中的数据按照字段name进行快速排序
• 【5】按照排序结果取前1000行数据返回

​

​ 其中步骤【4】按照name排序这个动作可能在内存中完成，也可能需要外部排序，这取决于排序所需的内存和参数sort_buffer_size

​ sort_buffer_size：MySQL为排序开辟的内存（sort_buffer）的大小

• 如果要排序的数量小于sort_buffer_size，排序就在内存中完成
• 如果要排序的数量大于sort_buffer_size，内存放不下，则需要借助磁盘临时文件辅助排序

# 确认是否使用了临时文件

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;
 
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

​ 查看TRACE结果，跟踪filesort_summary内容，可以根据number_of_tmp_files字段确认是否使用了临时文件

​ number_of_tmp_files：表示排序过程中使用的临时文件数，如果sort_buffer_size大于需要排序的数据量，则表示排序可以在内存中完成，不需要使用到临时文件，因此number_of_tmp_files为0

​ 如果sort_buffer_size小于排序的数据量，则需要引入临时文件辅助排序。外部排序一般使用的是归并算法，MySQL需要将排序的数据分成若干份，每一份单独排序后存储在临时文件中，然后再将若干个有序文件合并成一个有序的大文件。（sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大）

​ 使用函数模拟插入1w条数据，再执行上述语句查看TRACE内容：

"filesort_summary": {
    "rows": 1001,
    "examined_rows": 10000,
    "number_of_tmp_files": 0,
    "sort_buffer_size": 178184,
    "sort_mode": "<sort_key, additional_fields>"
}
// examined_rows：表示参与排序的行数是1w行

​ 对select @b-@a;的分析，此处返回的结果是10001（正常情况下是返回10000，表示整个执行过程扫描了10000行），此处多了1是因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1。

排序算法2：rowid排序

​ 基于上述全字段排序算法，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法存在一个问题：全字段排序在单行内容较大的场景下排序效率低下，即如果查询要返回的字段很多的话，则sort_buffer 要存放的内容太多，容易导致内存不够用，则可能需要借助多个临时文件辅助排序，就会导致排序性能较差

​ 基于上述场景，MySQL可以通过设定max_length_for_sort_data 参数来决定是否要切换排序算法。

​ max_length_for_sort_data 参数是MySQL 中专门控制用于排序的行数据的长度的一个参数。其表示如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法

SET max_length_for_sort_data = 16;

​ 设置完成，此时city、name、age三个字段的总定义长度超出16则MySQL会切换排序算法，跟踪排序信息如下：

"filesort_summary": {
    "rows": 1001,
    "examined_rows": 10000,
    "number_of_tmp_files": 0,
    "sort_buffer_size": 44048,
    "sort_mode": "<sort_key, rowid>"
}
// sort_mode：变成rowid，表示参与排序策略的只有name、id两个字段，相应地number_of_tmp_files也会结合实际场景变化

​ rowid 排序流程分析如下：

• 【1】初始化sort_buffer放入name、id 两个字段
• 【2】根据索引city找到第一个满足city='杭州'条件的主键ID（对应图中ID_X）
• 【3】到对应的主键ID索引取出整行数据，择取其中的name、id 值并存入sort_buffer中
  • 继续根据索引city取下一个记录的主键ID，以此类推依次取值，直到city的值不满足查询条件位置（对应例图中检索到ID_Y的位置）
• 【4】所有满足条件的数据检索完成，则对soft_buffer中的数据按照字段name进行快速排序
• 【5】按照排序结果取前1000行数据，并按照id的值回到原表中取出city、name、age三个字段返回到客户端

​ 对比全字段排序，此处rowid排序算法主要在于sort_buffer的存储内容（存储id和需要排序的字段）、多了一次回表检索操作（多访问了一次表T的主键索引）

​ 需要说明的是，最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的

​ 此时的examined_rows为10000（表示用于排序的数据是10000行），select @b-@a;执行结果是11001，是因为基于rowid算法策略：除了排序之外，还要根据id去原表取值（因为limit 1000，则会多读1000行），多了1 在前面也解释了是因为 internal_tmp_disk_storage_engine

全字段排序 VS rowid 排序

​ 两种排序算法的演进是基于MySQL的设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

​ 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不需要回表读取数据

​ 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，在排序过程中一次可以排序更多行，但是需要再回到原表去取数据

​ 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择

不是所有的order by语句都需要排序操作

​ MySQL 做排序是一个成本比较高的操作。但实际上并不是所有的 order by 都需要排序操作。如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

​ 从上面分析的执行过程，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，**其原因是原来筛选的数据都是无序的。**如果设想一种情况：能够保证从 city 这个索引上取出来的行就是按照 name 递增排序的话，则减少了排序的耗损。

​ 方案：在这个市民表上创建一个 city 和 name 的联合索引

alter table t add index city_user(city, name);

​ 在这个索引里面，依然可以用树搜索的方式定位到第一个满足 city='杭州'的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的，因此整个查询流程就会变成：

• 【1】从索引 (city,name) 找到第一个满足 city=‘杭州’条件的主键 id；
• 【2】到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
• 【3】从索引 (city,name) 取下一个记录主键 id；
• 【4】重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=‘杭州’条件时循环结束

​ 基于此流程，无需额外的排序操作，直接将从表中检索的数据作为结果集的一部分直接返回。可以通过explain分析其内容，Extra 中没有Using filesort ，说明此处已经不需要排序了，是因为上述创建的联合索引(city,name)本身有序，因此这个查询也无需将所有行都读一遍（10000行），只需要找到满足条件的前1000条记录即可完结操作（select @b-@a;执行结果是1001），即只需要扫描1000次。

如何进一步简化该语句的执行流程？

​ 此处引入覆盖索引概念，覆盖索引：索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

​ 基于覆盖索引，可以进一步优化这个查询语句的执行流程，例如针对这条查询语句，可以再创建一个city、name、age的联合索引

alter table t add index city_user_age(city, name, age);

​ 此时对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了：

• 【1】从索引 (city,name,age) 找到第一个满足 city=‘杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
• 【2】从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
• 【3】重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=‘杭州’条件时循环结束

​ 继续分析explain执行结果（查询语句的执行计划）：Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多

​ 此处仅仅是一个优化思路，但并不是说为了要每个查询都用上覆盖索引就要把语句中涉及到的字段都建上联合索引（这是一个误区），因为索引是有一定的维护代价的，要权衡实际场景考究

2.join 的工作原理

连接查询原理

案例准备

# 创建表并填充数据
create table t1(m1 int,n1 char(1));
create table t2(m2 int,n2 char(1));
insert into t1 values(1,'a'),(2,'b'),(3,'c');
insert into t2 values(2,'b'),(3,'c'),(4,'d');

​ 连接查询：连接查询的过程就是根据匹配规则将不同表记录组合起来连接成一个更大的记录。例如此处为笛卡尔积操作

select * from t1,t2;

​ 在不设定条件限制的情况下，可以自由组合连接任意张数量的表，这些表连接起来产生的笛卡尔积可能是非常巨大的（例如3个100行记录产生的笛卡尔积：100×100×100），因此在构建连接的时候过滤掉特定记录组合是非常有必要的，在连接查询中过滤条件有两种：

• 涉及单表的条件：例如搜索条件过滤等（针对某个表记录的条件过滤，例如t1.m1>1）

• 涉及多表的条件：多个表之间的条件判断（例如t1.m1=t2.m2）

携带过滤条件的连接查询（优先以简单案例入手，梳理基础概念，然后再思考如何提升连接查询的性能）

# 案例解析
select * from t1,t2 where t1.m1>1 and t1.m1 = t2.m2 and t2.n2<'d'

# SQL拆解：此处涉及3个过滤条件，分别为
t1.m1>1 
t1.m1 = t2.m2 
t2.n2<'d'

• 【1】驱动表检索：先确定第一个需要查询的表（称之为驱动表），选取代价最小的访问方法执行单表查询语句（涉及单表查询语句的执行原理，简单来说就是从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询）
  • 此处假设使用t1作为驱动表进行检索（按照单表查询规则去分析执行流程，由于此处数据量较少没有创建二级索引，因此其访问是采用全表扫描的方式来执行单表查询），此处筛选的数据是满足t1.m1>1 的记录（即匹配的记录：符合过滤条件的记录）
• 【2】匹配被驱动表：针对步骤【1】产生的结果集中的每一条记录，需要到t2表中查找匹配记录（因为是根据t1表去找t2表的记录，因此t2表可以理解为被驱动表）。依次根据步骤【1】的产生的结果集进行关联检索，在这个匹配过程中会用到t1.m1 = t2.m2 这个过滤条件
  • 当t1.m1=2时，过滤条件t1.m1 = t2.m2 = 2，则相当于此时针对t2表的过滤条件有两个（t2.m2=2、t2.n2<'d'），据此来执行t2的单表查询操作
  • 依次类推，继续遍历下一个记录，根据匹配的过滤条件，执行t2的单表查询操作
• 【3】返回结果集

​ ==在两表连接查询中，驱动表只需要访问1次，而被驱动表可能访问多次（取决于从驱动表中过滤出匹配记录数）==结合上述步骤说明，在步骤【1】中查询驱动表1次，过滤出符合匹配条件的记录。随后在步骤【2】中根据步骤【1】过滤出的结果集再进行检索，其检索次数依托于前面获取到的结果集记录数（例如步骤【1】检索出2条符合的记录，则在步骤【2】中就需要访问被驱动表2次）

内连接和外连接

（1）核心概念

案例准备

# 创建student、score表
CREATE TABLE student(
	number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  name VARCHAR(5) COMMENT '姓名',
  major VARCHAR(30) COMMENT '专业',
  PRIMARY KEY(number)
)Engine=InnoDB CHARSET=utf8 COMMENT'学生信息表';

CREATE TABLE score(
	number INT COMMENT '学号',
  subject VARCHAR(30) COMMENT '科目',
	score TINYINT COMMENT '成绩',
	PRIMARY KEY(number, score)
)Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表'

# 数据插入
INSERT INTO `student` (`number`, `name`, `major`)
VALUES
	(20240101, '小红', '软件学院'),
	(20240102, '小白', '计算机科学与技术'),
	(20240103, '小张', '计算机科学与技术');

INSERT INTO `score` (`number`, `subject`, `score`)
VALUES
	(20240101, '微积分', 50),
	(20240101, '高等数学', 80),
	(20240102, '微积分', 33),
	(20240102, '高等数学', 95);

​ 需求分析：查询每个学生的考试成绩（关联查找：通过number学号进行连接）

# 1.连接查询
select *
from student,score
where student.number = score.number

# SQL执行检索结果
number	name	major	number	subject	score
20240102	小白	计算机科学与技术	20240102	高等数学	95
20240102	小白	计算机科学与技术	20240102	微积分	33
20240101	小红	软件学院	20240101	微积分	50
20240101	小红	软件学院	20240101	高等数学	80


# 2.进一步简化SQL，筛选需要的关键字
select s.number,s.name,sc.subject,sc.score
from student s,score sc
where s.number = sc.number

number	name	subject	score
20240101	小红	微积分	50
20240101	小红	高等数学	80
20240102	小白	微积分	33
20240102	小白	高等数学	95

​ ==内连接、外连接概念的引入：==结合上述案例分析，记录只筛选出了满足s.numer=sc.number的数据。但是实际场景中可能小张没有参加考试所以没有成绩信息，但还是系统可以获取到小张的数据（即希望驱动表中的记录即使在被驱动表中没有匹配的记录，也需要加入到结果集）。因此基于此场景则引入内连接、外连接的概念：

• ==内连接：==对于内连接的两个表，驱动表中的记录如果在被驱动表中没有匹配的记录，则不会加入结果集
• ==外连接：==对于外连接的两个表，就算驱动表中的记录在被驱动表中没有匹配的记录，也会将记录加入结果集
  • 左外连接：选取左边的表为驱动表
  • 右外连接：选取右边的表为驱动表

​ 继续分析新的场景应用，如果有时候并不希望通过外连接将所有的记录返回到结果集，有时匹配失败要加入结果集有时又不用加，针对这种场景考虑将过滤条件拆分为两种情况应用

• where子句中的过滤条件：不管是内、外连接，不满足where过滤条件的记录都不会被加入到结果集

• on子句中的过滤条件：

  • 对于外连接的驱动表记录，如果无法在被驱动表中找到匹配on子句中的过滤条件记录，记录仍然会被加入结果集，对应被驱动表记录相关字段使用NULL进行填充
  • 如果将on子句放在内连接中，MySQL会将其和where子句一样对待（即内连接中的where子句和on子句是等价的）

  一般场景下，将涉及单表的过滤条件放到where子句中，将涉及两表的过滤条件放到on子句中（称之为连接条件）

左连接

# 左连接
select s.number,s.name,sc.subject,sc.score
from student s left join score sc
on s.number = sc.number

# 检索结果（将小张的记录也检索出来，关联的记录不存在则使用NULL填充）
number	name	subject	score
20240101	小红	微积分	50
20240101	小红	高等数学	80
20240102	小白	微积分	33
20240102	小白	高等数学	95
20240103	小张	NULL	NULL

右连接

# 右连接
select s.number,s.name,sc.subject,sc.score
from student s right join score sc
on s.number = sc.number

# 检索结果（以score为驱动表，关联检索学生信息）
number	name	subject	score
20240101	小红	微积分	50
20240101	小红	高等数学	80
20240102	小白	微积分	33
20240102	小白	高等数学	95

内连接

# 内连接：基础语法（将要进行内连接的表都放在from关键字后面）
select s.number,s.name,sc.subject,sc.score
from student s,score sc
where s.number = sc.number

# 内连接：其他语法
// join
select s.number,s.name,sc.subject,sc.score
from student s join score sc
on s.number = sc.number

// inner join
select s.number,s.name,sc.subject,sc.score
from student s inner join score sc
on s.number = sc.number

// cross join
select s.number,s.name,sc.subject,sc.score
from student s cross join score sc
on s.number = sc.number

// 检索结果
20240101	小红	微积分	50
20240101	小红	高等数学	80
20240102	小白	微积分	33
20240102	小白	高等数学	95

（2）原理分析

• MySQL实战：到底可不可以写JOIN

基础：简单嵌套循环连接（Simple Nested-Loop Join：SNLJ）

​ 结合上述案例分析，t1、t2表的内连接执行流程可以概括为如下：

• 步骤【1】：选择驱动表，根据过滤条件过滤记录，选择最低代价的访问方法执行单表查询，得到一个初步的结果集
• 步骤【2】：根据步骤【1】得到的结果集，依次循环遍历，分别到对应的被驱动表中查找匹配的记录

​ 如果存在3个表进行连接的话，则步骤【2】中得到一个结果集，在步骤【3】中根据这个结果集进一步匹配数据后返回

for each row in rl{     # 针对根据过滤条件对t1执行单表查询筛选出来的记录进行循环
  for each row in r2{   # 针对基于r1结果集，对匹配t2单表查询条件的记录进行循环
  	for each row in r3{ # 针对基于r1+r2记录组合，对匹配t3单表查询条件的记录进行循环
         - 如果匹配条件则加入结果集
		}
	}
}

​ ==这个过程类似于一个嵌套的循环：==驱动表只访问一次，但被驱动表可能访问多次，其访问次数取决于对驱动表执行单表查询操作后结果集的记录数，这种连接执行方式称为嵌套循环连接（这是最简单、最笨拙的一种连接查询方法）

优化1：基于索引的嵌套循环（Indexed Nested-Loop Join：INLJ）

​ 结合前面的t1、t2案例进行分析，在对驱动表t1进行单表查询后，会根据获取到的初步结果集进一步匹配被驱动表的记录

select * from t1,t2 where t1.m1>1 and t1.m1 = t2.m2 and t2.n2<'d'

• 【1】驱动表检索：t1作为驱动表，筛选的数据是满足t1.m1>1 的记录，得到初步结果集
• 【2】匹配被驱动表：针对步骤【1】产生的结果集中的每一条记录，根据指定的过滤条件到t2表中查找匹配记录
  • 当t1.m1=2时，过滤条件t1.m1 = t2.m2 = 2，则相当于此时针对t2表的过滤条件有两个（t2.m2=2、t2.n2<'d'），据此来执行t2的单表查询操作
  • 依次类推，继续遍历下一个记录，根据匹配的过滤条件，执行t2的单表查询操作
• 【3】返回结果集

​ 从上述步骤分析，t1.m1 = t2.m2这个条件在步骤【2】执行循环的过程中就已经明确了（根据t1.m1），因此此处只需要优化对t2表的查询操作。进一步分析此处需要获取到t2的m2、n2列，且需满足t2.n2<'d'条件。因此可以考虑构建索引来进一步提升查找性能

• 在m2列创建索引：
  • 对m2列的条件及基于等值查找（例如t2.m2=2、t2.m2=3等），所以可能用到ref访问方法。如果使用的是ref方法执行对t2的查询，则需要回表之后再判断t2.n2<'d'条件是否成立
  • 如果m2列是t2表的主键或唯一二级索引列，则在使用t2.m2=常数这样的条件从t2表中查找记录的过程代价是常数级别的
    • 单表中使用主键或唯一二级索引列进行等值查找的方式称为const
    • 在连接查询中使用主键或唯一二级索引列进行等值查找的方式称为eq_ref
• 在n2列创建索引：涉及到t2.n2<'d'条件，可能会用到range的访问方法。假设使用range的访问方法对t2表进行查询，需要回表之后再判断m2列上的条件是否成立
• 如果m2、n2列都建立索引，则会从其中挑选一个代价更低的去执行对t2的单表查询操作。

建立的索引不一定会被使用：只有在二级索引+回表的代价比全表扫描的代价更低的时候才会使用索引

有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用 eg ref 、 ref 、ref or null或者 range 这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描（即使用 index 的访问方法来查询被驱动表）。一般建议在真实工作中最好不要使用*作为查询列表，要把真实用到的列作为查询列表

优化2：基于块的嵌套循环连接（Block Nested-Loop Join）

​ 扫描一个表的过程，实际上是先将这个表从磁盘上加载到内存中，然后在内存中比较匹配条件是否满足。但在实际应用场景中，不免存在被驱动表特别大的情况，则可能出现内存中并不能完全存放得下表中的所有记录。在扫描表前面记录的时候后边的记录可能还在磁盘上，等到扫描到后面记录的时候可能存在内存不足的情况，因此要将前面的记录从内存中释放

​ 在 嵌套循环连接 算法的两表连接过程中，被驱动表可能会被访问多次，如果这个被驱动表中的数据特别多而目不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O 代价就非常大了，因此需想办法尽量减少访问被驱动表的次数。

​ 每次访问被驱动表，被驱动表的记录就会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，匹配完成则从内存中清除。依次类推，循环取出驱动表结果集的记录，然后再一次将被驱动表的记录加载到内存依次进行比较。驱动表结果集有多少条记录，就要将被驱动表加载到内存多少次。因此考虑是否可以在加载被驱动表到内存的时候让其一次性和多条驱动表结果集进行匹配，以大大减少重复从磁盘上加载被驱动表的代价。

​ MySQL通过引入join buffer概念，在执行连接查询前先申请的一块固定大小的内存，先将若干条驱动表结果集的记录数存放在这个join buffer中，然后在扫描/访问被驱动表的时候，一次性和join buffer中的多条记录做匹配，以显著减少被驱动表的IO代价

​ 这种引入了join buffer的嵌套循环连接算法被称为基于块的嵌套连接（Block Nested-Loop Join）算法

​ 最理想的情况是join buffer足够大，能够一次性容纳驱动表结果集的所有记录，则只需要访问一次被驱动表即可完成连接操作。

​ join buffer的大小可以通过启动参数或者系统变量``join_buffer_size`进行配置，默认大小（256KB：262144字节），最小可以设置为128字节

​ 此处需注意join buffer中存放的内容，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，因此再一次提醒在实际应用场景中不要将*作为查询列表

`

优化3：哈希连接（Hash Join）

​ MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联，分别是：Nested Loop Join、Hash Join

​ 通常认为，在 OLTP 业务中，因为查询数据量较小、语句相对简单，大多使用索引连接表之间的数据。这种情况下，优化器大多会用 Nested Loop Join 算法；而 OLAP 业务中的查询数据量较大，关联表的数量非常多，所以用 Hash Join 算法，直接扫描全表效率会更高。

Hash Join

​ Hash Join用于两张表之间连接条件没有索引的情况。对于 OLAP 业务查询来说，Hash Join 是必不可少的功能，MySQL 8.0 版本开始支持 Hash Join 算法，加强了对于 OLAP 业务的支持。

​ Hash Join会扫描关联的两张表：

• 首先会在扫描驱动表的过程中创建一张哈希表
• 接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录

​ Hash Join 选择驱动表和 Nested Loop Join 算法大致一样，都是较小的表作为驱动表。如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘

​ MySQL 数据库中支持 JOIN 连接的算法有 Nested Loop Join 和 Hash Join 两种，前者通常用于 OLTP 业务，后者用于 OLAP 业务。在 OLTP 可以写 JOIN，优化器会自动选择最优的执行计划。但若使用 JOIN，要确保 SQL 的执行计划使用了正确的索引以及索引覆盖，因此索引设计显得尤为重要，这也是DBA在架构设计方面的重要工作之一

JOIN能否在业务场景中应用？

​ 结合join的工作原理，分析在一定的业务场景下如何更好地使用join

• 到底可不可以使用join

• join语句如何优化

实际生产中join的问题

• DBA不让用join，使用join会有什么问题？
• 如果有两个大小不同的表做join，应该选择哪个表做驱动

案例准备

# 1.创建t2表，并插入数据
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;
 
// drop procedure idata; 删除存储过程

# 2.创建存储过程（实现循环初始化数据）
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;

# 3.调用存储过程
call idata();

# 4.创建t1表，并同样初始化数据
create table t1 like t2;
insert into t1 (select * from t2 where id<=100);


# 执行结果说明：
t1、t2表都有一个主键索引id和一个索引a，字段b上没有索引
存储过程往t2表中插入了1000条数据，t1从t2中复制了100条数据

Index Nested-Loop Join

Index Nested-Loop Join：基于索引的嵌套循环连接

select * from t1 straight_join t2 on (t1.a=t2.a);

# 查看语句执行计划
explain select * from t1 straight_join t2 on (t1.a=t2.a);

​ straight_join：如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，会影响学习过程中分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，通过使用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表

​ 在这条语句中，被驱动表t2的字段a上有索引，join过程中用了索引，因此这个语句的执行流程分析如下：

• 【1】从驱动表t1读取数据获取到一个初步的结果集（此处针对t1没有设定过滤条件，因此这个结果集即t1表）=》执行：全表扫描100行
• 【2】依次遍历步骤【1】中获取到的结果集，根据a字段依次对t2表中的数据进行匹配（因为t2设定了索引，因此可以根据索引a、索引id检索到相应记录）=》执行：根据a字段查找t2表记录进行匹配，走树搜索过程（由于构造的数据都是一一对应，因此每次的搜索过程只扫描一行，总扫描100行）
• 【3】将匹配结果中获取到的指定列存入最终的结果集

问题1：业务开发中能不能使用join

基于此场景：不使用join的情况，如果不使用join，则只能使用单表查询，其执行思路如下：

【1】执行select * from t1查询所有t1表数据

【2】循环遍历步骤【1】获取的数据

• 从每一行R中取出字段a的值$R.a
• 执行select * from t2 where a=$R.a
• 将返回的结果和R构成结果集的一行

【3】最终返回封装好的结果集

基于上述操作分析，执行了200次扫描，但是执行了101条语句，比直接join多了100次交互，且客户端还需要直接拼接SQL语句和结果。

设想一种场景，如果说通过程序代码来执行这一过程，那么就会出现频繁多次访问数据库，就会造成程序性能低下的问题

问题2：如何选择驱动表？

在上述join执行过程中，驱动表走全表扫描，被驱动表走树搜索。

​ 假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

​ 假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。

​ 因此整个执行过程，近似复杂度是 N + N2log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表

​ 如果对这个数据没有特别的概念，可以试着将数据放大，然后对比其区别（N 扩大 1000 倍的话，扫描行数就会扩大 1000 倍；而 M 扩大 1000 倍，扫描行数扩大不到 10 倍）

结论分析：

• 在可以使用被驱动表的索引的基础上，使用join语句性能上比多个单表执行SQL的性能要好
• 如果使用join语句，需要让小表做驱动表以提升性能

Simple Nested-Loop Join

Simple Nested-Loop Join：基于简单的嵌套连接

# 调整SQL语句
select * from t1 straight_join t2 on (t1.a=t2.b);

​ 由于t2的字段没有索引，因此在进行循环匹配的时候，每次都要做一次全表扫描，其执行流程分析如下：

• 【1】从驱动表t1读取数据获取到一个初步的结果集（此处针对t1没有设定过滤条件，因此这个结果集即t1表）=》执行：全表扫描100行
• 【2】依次遍历步骤【1】中获取到的结果集，每次都要进行一次全表扫描进行数据匹配 =》执行：根据t1.a字段查找t2表记录中b字段是否匹配，走全表扫描（每次循环都要扫描1000行）
• 【3】将匹配结果中获取到的指定列存入最终的结果集 =》最终扫描次数：100 * 1000 = 10w行

​ 很显然，这种连接方式是非常笨重的，因此在此基础上MySQL提供了其他的优化连接算法对连接过程进行优化以提升性能。（例如基于索引的嵌套连接、基于块的嵌套连接、Hash Join等）

Block Nested-Loop Join

explain select * from t1 straight_join t2 on (t1.a=t2.b);

场景1：join buffer足够一次性容纳驱动表筛选出来的结果集

​ job buffer存储的列是需要检索的列、涉及条件匹配的列

​ 此处因为驱动表的数据量比较小，可以一次性放入join buffer，因此其执行流程分析如下

​ 可以看到，MySQL为语句选择了优化的join算法，此处使用的是Block Nested-Loop。由于没有使用到索引，因此在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次。基于这种场景，虽然时间复杂度和SNLJ一样，但所有的判断操作都是内存操作，速度和性能上都会好很多。

​ 分析此处驱动表的选择：假设小表的行数是 N，大表的行数是 M，那么在这个算法里：

• 两个表都做一次全表扫描，所以总的扫描行数是 M+N；

• 内存中的判断次数是 M*N

​ 可以看到，M和N调换的影响并没有什么区别，基于这种场景（join buffer足够一次性容纳驱动表筛选出来的结果集）无论是选择大表还是小表作为驱动表，其执行耗时是一样的。

场景2：join buffer无法一次性容纳驱动表筛选出来的结果集，需拆分多次

​ join buffer的大小是由join_buffer_size设定的，默认是256K。可以试着将join_buffer_size调小，查看不同场景下SQL的执行耗时

# 查看join buffer size大小
SHOW VARIABLES LIKE 'join_buffer_size';

Variable_name	Value
join_buffer_size	262144

​ 基于这种场景（需要分段使用join buffer空间），SQL执行流程分析如下：

• 【1】扫描驱动表t1，将满足条件的数据放入join buffer（直至其空间满）
• 【2】扫描被驱动表t2，将其数据装配到内存中，并和join buffer中的数据进行比较，满足条件则作为结果集的一部分进行返回
• 【3】清空join buffer，以此类推，继续扫描t1 重复【1】、【2】步骤操作直至驱动表中满足条件的记录都匹配完成

​ 图示中的4、5标记表示join buffer的重复使用（复用）

​ 分析其复杂度：驱动表的数据行数是 N，需要分 K 段才能完成算法流程（K=N*λ，λ取值范围0-1），被驱动表的数据行数是 M ，在这个算法的执行过程中：

• 扫描行数是 N + K × M =》N（1+λ×M）
• 内存判断 N×M 次

​ 此处内存判断此时并不受选择谁作为驱动表而受影响；针对考虑到扫描行数，当N固定的时候，如果分段K越大就会导致扫描行数越多，因此最理想情况就是K为1（即上述join buffer可以一次性存入从驱动表筛选出的结果集的情况），分段的次数越少，对非驱动表的扫描次数就会越少，这也是一些MySQL优化场景中提示如果join语句特别慢，则可以考虑适当加大join_buffer_size来达成优化效果

JOIN业务场景使用总结

​ 综合上述join算法场景分析，继续围绕前面提到的两个问题进行总结说明：

问题1：能不能使用 join 语句？

• 如果使用的是 Index Nested-Loop Join 算法（可以使用被驱动表上的索引的场景下），使用join的性能会更好；

• 如果使用的是 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，可能会多次扫描被驱动表，会占用大量的系统资源。这种场景下反而不建议使用 join

​ 因此在判断要不要使用 join 语句时，即关注 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样

问题2：如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；

• 如果是 Block Nested-Loop Join 算法：

  • 在 join_buffer_size 足够大的时候，是一样的；

  • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表

​ 因此建议在实际业务场景下要使用小表做驱动表

如何理解小表概念？

​ 所谓小表，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表

# SQL 分析
select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

​ 在上述的条件中只设定了t2数据的过滤，且没有走索引，因此走的是BNLJ算法。已知在t1中的总记录数是100，t2的总记录数1000。

​ 但上述SQL中t2设定了条件过滤检索出来的数据是50条（因为此处案例设定比较特殊，实际要结合实际的场景确认条件过滤后的记录数），所以此处t2被认定为“小表”，将其作为驱动表

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

​ 在此案例中t1、t2经过过滤之后都是100行数据参与join操作，，但是这两条语句每次查询放入join buffer中的数据是不一样的

• 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，则 join_buffer 中只需要放入 b 的值
• 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字段 id、a 和 b

​ 则此处应该考虑选择t1作为驱动表（引入如果每行数据占用的join buffer空间越少则一次能够存放更多的行，能一定程度上有效减少被驱动表的扫描次数）

JOIN的优化

案例准备

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
 
end;;
delimiter ;
call idata();

# t1 中插入1000行数据
# t2 中插入100w行数据

Multi-Range Read 优化

​ Multi-Range Read 优化 (MRR)，该优化的目的是尽量使用顺序读盘

​ “回表”指的是InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。

​ 主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。因此，回表肯定是一行行搜索主键索引的，基本流程如图所示

​ 如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。**因为大多数的数据都是按照主键递增顺序插入得到的，所以可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。**这就是 MRR 优化的设计思路。

​ 原语句执行计划说明：

explain select * from t2 where a>=100 and a<=200;

​ 此时，语句的执行流程变成了这样：

• 【1】根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
• 【2】将 read_rnd_buffer 中的 id 进行递增排序；
• 【3】排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

​ read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

​ 另外需要说明的是，如果想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，则设定固定使用 MRR ）

set optimizer_switch="mrr_cost_based=off";
explain select * from t2 where a>=100 and a<=200;

​ MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势

Batched Key Access(BKA)

​ 理解了 MRR 性能提升的原理，进一步理解 MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法。这个 BKA 算法，其实就是对 NLJ 算法的优化。（可以理解为BKA即 join buffer + Multi-Range Read）

​ 如果要使用BKA优化算法，则相应需要进行配置（启用mrr，并开启BKA）

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BNL （Block Nested-Loop）算法对系统的影响主要包括三个方面：

• 可能会多次扫描被驱动表，占用磁盘 IO 资源；
• 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
• 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

​ 在执行语句前，需要通过理论分析和查看 explain 结果的方式，确认是否要使用 BNL 算法。如果确认优化器会使用 BNL 算法，就需要做优化。优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。

​ 在一些情况下，可以直接在被驱动表上建索引，此时可以直接转成 BKA 算法了。

​ 但是，有时候确实会碰到一些不适合在被驱动表上建索引的情况。举例分析说明如下：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

​ t2 中插入了 100 万行数据，但是经过 where 条件过滤后，需要参与 join 的只有 2000 行数据。如果这条语句同时是一个低频的 SQL 语句，那么再为这个语句在表 t2 的字段 b 上创建一个索引就很浪费了。

​ 在表 t2 的字段 b 上创建索引会浪费资源，但是不创建索引的话这个语句的等值条件要判断 10 亿次，可以考虑使用临时表

• 把表 t2 中满足条件的数据放在临时表 tmp_t 中；
• 为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；
• 让表 t1 和 tmp_t 做 join 操作

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

​ 引入临时表后的执行效果：总执行时间还不到1s（对比前面的50s），其性能得到大幅提升

• 【1】执行 insert 语句构造 temp_t 表并插入数据的过程中，对表 t2 做了全表扫描，这里扫描行数是 100 万。
• 【2】之后的 join 语句，扫描表 t1，这里的扫描行数是 1000；join 比较过程中，做了 1000 次带索引的查询。相比于优化前的 join 语句需要做 10 亿次条件判断来说，这个优化效果还是很明显的。

总体来看，不论是在原表上加索引，还是用有索引的临时表，核心思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。

hash join

​ 对照前面的案例，上面计算 10 亿次那个操作，看上去有点儿傻。如果 join_buffer 里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是 10 亿次判断，而是 100 万次 hash 查找，整条语句的执行速度就快多了

​ 在以往的MySQL版本中并不支持hash join，因此也是其优化器和执行器一直被诟病的一个原因：不支持哈希 join。（在MySQL8版本中已经提供支持）

实际上，这个优化思路，可以自己实现在业务端。实现流程大致如下：（其实现核心是分别检索数据，然后通过代码层进行匹配）

【1】select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。

【2】select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。

【3】把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

join优化总结

join 优化核心思路

• BKA优化算法的引入：实际上是BNL + MRR 概念（引入join buffer + MRR），而BNL转化为BKA算法优化的核心方向即给被驱动表的关联字段加上索引
• 基于临时表的优化方案：对于能够提前过滤出小数据的 join 语句来说，效果还是很好的
• Hash Join：可以通过业务层模拟hash join，也可通过升级MySQL版本引入Hash Join

3.count 的工作原理

• 哪种count 的性能最好

count 核心

​ count 性能比较：count(*) = count(1) > count(主键字段) > count(字段)

​ count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个

​ select count(name) from t_order;：表示统计t_order表中，name字段不为NULL的记录有多少个

​ select count(1) from t_order;：表示统计t_order表中有多少个记录（ t_order 表中，1 这个表达式不为 NULL 的记录，1为纯数字用不为null，则说明其是用于统计t_order的记录数）

案例准备

CREATE TABLE `t_order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT NOT NULL,
  `order_date` DATETIME NOT NULL,
  `status` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_order` (`customer_id`, `order_date`, `status`) VALUES
(1, '2023-01-01 10:00:00', 'Pending'),
(2, '2023-01-02 11:00:00', 'Completed'),
(3, '2023-01-03 12:00:00', 'Cancelled');

总结说明

• count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描
• 统计效率：count(*) = count(1) > count(主键字段) > count(字段)

count（主键字段）执行过程

explain select count(id) from t_order;

​ 在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。

​ server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环，最后将 count 变量的值发送给客户端。

​ InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据

​ 如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。此时explain中的key为index_order

​ 这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引

count（1）执行过程

explain select count(1) from t_order;

​ 如果表里只有主键索引，没有二级索引时。InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

​ count(1)比count(主键字段)少了一个读取记录中字段值的步骤，因此一般情况下count(1)的执行效率会比count(主键字段)高

​ 但是，如果表里有二级索引时，InnoDB 循环遍历的对象就是二级索引（explain中key为index_order）

count（*）执行过程

​ 对于 selete * 这条语句来说表示查询所有字段，但是在 count(*) 中并不是这个意思。count(*) 其实等于 count(0)，即在使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理，因此count(*)的执行过程和count(1)基本一样，性能没有什么差异

explain select count(*) from t_order;
show warnings;

​ 且MySQL 会对count(*)和 count(1) 有个优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。只有当没有二级索引的时候，才会采用主键索引来进行统计

count（字段）执行过程

​ count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的

// customer_id不是索引，普通字段
select count(customer_id) from t_order;

count（*）的优化

（1）近似值

​ 上述分析中提到可以通过构建二级索引来优化统计性能，但对于大表的统计还可结合实际业务场景进一步进行优化。

​ 近似值概念的引入是基于业务对于统计个数不需要很精确，比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值。

​ 因此估算可以通过show table status或者explain命令进行估算

（2）额外保存计数值

​ 在实际业务场景中，可以将统计相关数据放到一张表中，但需注意其维护成本，以及是否需要经常动态联动修改

​ 当在关联的业务表中处理一条数据记录，则关联的统计表也要联动做调整（注意其时效性，是否需要联动变更等）

4.group by 的工作原理

​ 在前面关键字的原理学习中，理解了sort buffer、内部临时表、join buffer的引入，这三个数据结构都是用于存放执行过程中的中间数据，用于辅助SQL语句的执行。其中sort buffer用于排序（sort语句），join buffer用于连接（join语句）

​ 下面介绍内部临时表的引入场景：union、group by

union执行流程

案例准备

# 创建t1表并构建存储过程插入数据
create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;
 
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

语句分析

# 使用union获取到两个子查询结果的并集（重复的行只保留一行）
(select 1000 as f) union (select id from t1 order by id desc limit 2);

# output
f
1000
999

• 第2行：key为PRIMARY，说明子句2中使用到了索引id
• 第3行：Extra字段，在对子查询的结果集做union操作的时候，使用到了临时表（Using temporary）

语句执行流程分析：

• 【1】创建一个内存临时表，这个临时表只有一个整形字段f，f为主键字段
• 【2】执行第1个子查询，得到1000这个值，将其存入临时表中
• 【3】执行第2个子查询：（根据过滤条件此处获取到2条数据）
  • 获取到第1行id=1000，试图将其插入临时表中，但是1000已经存在于临时表，违反了唯一性约束，插入失败，继续向下执行
  • 获取到第2行id=999，插入临时表成功
• 【4】从临时表中按行取出数据，返回结果集，并删除临时表（结果集包含两行数据分别是1000、999）

​ 结合上述执行流程分析，此处内存临时表起到了暂存数据的作用，在计算过程中使用了临时表主键id的唯一性约束，进而实现union的语义，试着将union改为union all，进一步分析执行计划

​ 综合上述执行流程分析，由于union all不涉及去重，在执行的时候依次执行子查询，将得到的结果直接作为结果集的一部分返回，因此不需要使用内部临时表。

group by执行流程

语句分析1：group by

select id%10 as m, count(*) as c from t1 group by m;

分析Extra：

• Using index：语句使用了覆盖索引，选择了索引a，不需要回表
• Using temporary：使用了内部临时表
• Using filesort：需要进行排序

执行流程分析：

• 【1】创建内存临时表，存储字段m、c，其中m为主键
• 【2】扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；
  • 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);
  • 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；
• 【3】遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端

​ 其中对内存临时表的排序，涉及到order by的执行原理（其中内存临时表的排序参考下图中虚线框的内容）

语句分析2：不需要排序

​ 如果需求不需要对结果进行排序，可以在SQL语句末尾增加order by null子句，进一步分析其执行流程

select id%10 as m, count(*) as c from t1 group by m order by null;

​ 由于t1表中的id是从1开始的，因此返回的第一行结果集是1，直到扫描到id为10其值为0（由于指定了不排序，因此展示上述结果）

内存临时表的大小是有限制的，使用tmp_table_size控制内存大小，默认是16M

# 查看tmp_table_size大小
show variables like 'tmp_table_size'
# 设置tmp_table_size大小（此变量设置后可能需要重启MySQL服务使其生效）
SET GLOBAL tmp_table_size = 64*1024*1024; -- 设置为64MB

语句分析3：测试tmp_table_size大小对SQL执行流程的影响

​ 将内存临时表的大小限制为最大 1024 字节，并把语句改成 id % 100，测试如果内存临时表大小存不下检索数据的场景。

# 修改tmp_table_size
SET GLOBAL tmp_table_size = 1024;

# mac中查看mysql服务信息，修改tmp_table_size后重启服务
brew list | grep mysql

brew services restart mysql@5.7

# PS:如果在客户端修改后发现检索结果没有生效，重启服务后再次确认（可能是Query窗口缓存问题，建议重开Query窗口），使用Termninal命令行直接set后查看可以立马跟踪到检索效果，自行确认

​ 此处返回结果里有 100 行数据。但是此时的内存临时表大小不够存下这 100 行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024 字节）。那么，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。如果这个场景下t1数据量很大，这个查询需要的磁盘临时表就会占用大量的磁盘空间。

# tmp_table_size足够大的场景分析
set tmp_table_size = 1024*1024*60;
select id%10 as m, count(*) as c from t1 group by m order by null limit 10;

# tmp_table_size设置小一点的场景分析
set tmp_table_size = 1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

group by 优化方法 - 索引

​ 不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上述案例中的 group by 语句执行起来就会很慢，进一步思考一下有什么优化方法。

​ 要解决 group by 语句的优化问题，可以先思考一下这个问题：执行 group by 语句为什么需要临时表？

​ 基于上述案例分析：group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以需要有一个临时表，来记录并统计结果。如果扫描过程中可以保证出现的数据是有序的，则简化了执行流程？

​ 如果可以确保输入的数据是有序的，那么在计算group by的时候，只需要从左到右进行顺序扫描、依次累加，按照相应逻辑执行，扫描到整个输入数据结束则可直接获取到group by的结果，此时不需要临时表、也不需要引入额外的排序

​ InnoDB的索引可以满足这个输入有序的条件：MySQL5.7版本支持了generated column机制，用于实现列数据的关联更新。可以构建案例进一步理解分析：基于上述案例t1表中创建一个z列（并在z列上创建一个索引，如果是MySQL5.6及之前的版本可以创建普通列和索引来解决这个问题）

# 新增z列（在z列构建索引）
alter table t1 add column z int generated always as(id % 100), add index(z);

# 原SQL语句
select id%100 as m, count(*) as c from t1 group by m;

# SQL语句优化
select z, count(*) as c from t1 group by z;

​ 分析优化后的SQL执行计划，这个语句的执行不需要使用到临时表，也不需要进行额外的排序。

​ ==基于索引的group by优化策略核心：==通过设定一个冗余列，构建相关索引确保group by字段关联数据的有序性，进而达到优化效果

group by 优化方法 - 直接排序

​ 一般场景下，如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，还是要老老实实做排序的。那么，这时候的 group by 要怎么优化呢？

​ 如果在已知场景中，明明知道一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，这个操作看上去就会有点傻且多余。

​ 因此，基于这个方向思考，MySQL 有没有提供直接走磁盘临时表的方法？

​ ==优化方案：==在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），明确告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

​ MySQL 的优化器在执行的时候会进一步分析选择最优方案，首先思考磁盘临时表是 B+ 树存储，存储效率不如数组来得高，因此当其接收到这个语句涉及的数据量很大的信息，就会从磁盘空间考虑，选择用数组来进行存储。

# 优化思路：
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

结合该语句的执行流程分析，这个语句的执行没有使用内部临时表，而是直接使用了排序算法

分析其执行流程：

• 【1】初始化 sort_buffer，确定放入一个整型字段，记为 m；
• 【2】扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；
• 【3】扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
• 【4】排序完成后，就得到了一个有序数组

根据有序数组，得到数组里面的不同值，以及每个值的出现次数。这一步的逻辑，参考group by 优化方法-索引案例的原理分析

内存临时表引入总结

​ 结合上述场景案例分析，拆解了union、union all、group by语句的执行过程，理解在什么场景下会使用到内部临时表？

• join buffer是无序数组、sort buffer是有序数组、临时表是二维表结构

• 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果

  • union：在执行过程中需要去重，因此需要引入内存临时表临时存储结果集
  • union all：在执行过程中不需要去重，因此不需要使用到内存临时表

• 如果执行逻辑需要用到二维表特性，则优先考虑使用临时表

  • 例如案例中：union需要使用到唯一约束、group by需要用额外的字段累计计数

group by 使用总结

• 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
• 索引优化场景：尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
• 需要统计的数据量大小方面考究
  • 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
  • 如果数据量实在太大，使用 SQL_BIG_RESULT ，来告诉优化器直接使用排序算法得到 group by 的结果

5.子查询的工作原理

• MySQL是怎样运行的：从根儿上理解MySQL 14.3 子查询优化

案例分析

​ 需求说明：找出1993年，没有下过订单的客户数量

​ 转化思路：先过滤出1993年所有的订单记录，然后只需确认客户id是否存在于订单表中

• 子查询思路：
  • 先过滤出1993年所有的订单记录，作为子查询条件
  • 检索客户表，检索出不存在于订单表的客户记录并统计即可
• left join思路：左连接查询
  • 先检索出所有客户在1993年关联的订单记录；由于设定是左连接，如果客户没有关联订单记录的话其订单记录会用NULL填充
  • 添加过滤条件，过滤出关联订单记录为NULL的客户记录，即对应为满足指定条件下没有下过订单的客户信息

# 方式1：子查询 in 写法
SELECT
    COUNT(c_custkey) cnt
FROM customer
WHERE
    c_custkey NOT IN (
        SELECT o_custkey
        FROM orders
        WHERE o_orderdate >=  '1993-01-01' AND o_orderdate <  '1994-01-01'
	);


# 方式2：等价left join写法
SELECT
    COUNT(c_custkey) cnt
FROM customer c
LEFT JOIN orders o ON c.c_custkey = o.o_custkey AND o.o_orderdate >=  '1993-01-01' AND o.o_orderdate <  '1994-01-01'
WHERE o.o_custkey IS NULL

​ 在MySQL8.0版本中，上述两条SQL的最终的执行计划都是相同的，最终会被转化为Nested-Loop Join（可以理解为MySQL8.0中，MySQL优化器会自动将IN子查询进行优化，优化为最佳的JOIN执行计划，显著提升性能）

子查询中的IN VS EXISTS，哪个性能更好？

# 方式1：子查询 exist 写法
SELECT
    COUNT(c_custkey) cnt
FROM customer
WHERE
    c_custkey NOT EXISTS (
        SELECT 1
        FROM orders
        WHERE o_orderdate >=  '1993-01-01' AND o_orderdate <  '1994-01-01' AND c_custkey = o_custkey
	);

​ 所有的性能判断不要轻易断言哪种写法性能更好，而是要关注SQL的执行计划，如果SQL的执行计划一样，则其性能没有任何差别

​

依赖子查询的优化

​ 在 MySQL 8.0 版本之前，MySQL 对于子查询的优化并不充分。所以在子查询的执行计划中会看到 DEPENDENT SUBQUERY 的提示，这表示是一个依赖子查询，子查询需要依赖外部表的关联。

​ 如果看到这样的提示，就要警惕， 因为 DEPENDENT SUBQUERY 执行速度可能非常慢，大部分时候需要手动把它转化成两张表之间的连接。

# SQL分析：子查询：计算出每个员工最后成交的订单时间，最外层SQL返回订单详情
SELECT *
FROM orders
WHERE (o_clerk , o_orderdate) IN (
        SELECT o_clerk, MAX(o_orderdate)
        FROM orders
        GROUP BY o_clerk);

MySQL8.0中，通过命令 EXPLAIN FORMAT=tree 输出执行计划

第 3 行：Select #2 (subquery in condition; run only once)。这表示子查询只执行了一次，然后把最终的结果保存起来了

第 6 行：Index lookup on ，表示对表 orders 和子查询结果所得到的表进行 JOIN 连接，最后返回结果

所以，当前这个执行计划是对表 orders 做2次扫描，每次扫描约 5587618 条记录：

• 第 1 次扫描，用于内部的子查询操作，计算出每个员工最后一次成交的时间；（关注第1行信息提示）

• 第 2 次表 oders 扫描，查询并返回每个员工的订单信息，即返回每个员工最后一笔成交的订单信息（关注第2行信息提示）

• MySQL8.0中该SQL的执行过程

• 老版本中该SQL的执行过程

​ 老版本中的执行计划：先查询每个员工的订单信息，然后对每条记录内部的子查询进行依赖判断（先进行外表扫描，然后做依赖子查询的判断），据此关联的依赖子查询就会被执行5587618*5587618次

引入派生表

​ 对依赖子查询的优化，就是要避免子查询由于对外部的依赖，而需要对子查询扫描多次的情况。所以可以通过派生表的方式，将外表和子查询的派生表进行连接，从而降低对于子查询表的扫描，从而提升 SQL 查询的性能。

何为派生表

​ 可以将子查询的查询结果当做是一个表，通过as指定别名（即表名），MySQL的设计者将这种由子查询结果集组成的表称为派生表

子查询分类

• 根据结果集的类型进行分类：

  • 标量子查询：返回一个单一的值

  • 行子查询：返回一条记录（一行中可有多个列字段）

  • 列子查询：返回一列记录（一列中可有多行数据）

  • 表子查询：返回多行多列记录

• 根据外层查询关系来进行分类：

  • 不相关子查询：子查询可以单独运行出结果，而不依赖于外层查询
  • 相关子查询：子查询的执行需要依赖外层查询的值（例如select * from t1 where m1 in (select m2 from t2 where n1=n2)）

基于派生表优化

​ 其优化SQL重写如下：

SELECT * 
FROM orders o1,
(
    SELECT o_clerk, MAX(o_orderdate)
    FROM orders
    GROUP BY o_clerk
) o2
WHERE o1.o_clerk = o2.o_clerk AND o1.o_orderdate = o2.orderdate;

​ 可以看到上述改造思路：将子查询改写为派生表o2，然后将派生表与外部表进行关联。其执行计划分析如下：

​ 可以看到派生表的SQL改写其执行计划和MySQL8的版本优化后的执行计划非常相似，可分别对比三种场景下的SQL执行速度：独立子查询（MySQL8.0版本）、依赖子查询（MySQL8.0之前未优化的版本）、派生表关联（重写SQL未派生表方式连接）

子查询 使用总结

子查询的执行过程

​ select * from t1 where id in (select id from t2 where name = 'xxx')

​ MySQL5.7.44版本中的子查询，其内部执行器执行的过程是先查外表，再匹配内表（不要陷入误区，并不是惯性思维地认为会先查出整个内表作为临时表给外表使用）：

• 先从外表 t1 取出一条数据，然后查询内表，从内表查询的结果中匹配记录
• 以此类推，依次一条条取、一条条查，循环反复

尽量避免使用子查询这句话完全正确吗？

​ 基于上述场景案例分析，可以结合不同版本优化来决定是否要使用子查询。且SQL的执行性能应当要关注的是其内部的执行计划，而不局限于自认为的概念理论

• MySQL8.0优化版本可以“毫无顾忌”地写子查询（因为相应版本的MySQL优化器对子查询的优化已经相对完备，大幅提升性能）
• 对于老版本的MySQL，要慎用子查询，务必Review所有子查询的SQL执行计划，对于出现DEPENDENT SUBQUERY的提示，务必进行优化，否则会对业务造成重大的性能影响
  • 针对DEPENDENT SUBQUERY的优化，一般是重写为派生表进行连接，进而达到优化效果（本质上是避免子查询由于需要对外部的依赖而需要对子查询扫描多次的情况）