MySQL 执行原理（1）

一.单表访问之索引合并

MySQL一般情况下执行一个查询最多只使用单个二级索引。

但在特殊情况下，一个查询中使用多个二级索引。称为索引合并/index merge。

索引合并分为下面3种情况。

1. Intersection 索引合并

Intersection：交集。

这里说某个查询可能使用多个二级索引(MySQL优化器自行选择是否使用)，将从多个二级索引中查询到的结果取交集，比如

使用索引合并，会在Extra显示Using union(idx_num, idx_expire_time)，数据多会呈现效果

-- DDL
create table d (
id int(10) primary key auto_increment,
num varchar(10) default '' not null,
remark varchar(20) default '' not null,
status tinyint(3) default 1 not null,
expire_time datetime
)
-- 索引创建
create index idx_num on d (num);
create index idx_expire_time on d (expire_time);


-- SQL查询
explain select * from d where num = '1' and expire_time = '2024-08-29 14:18:24'

若这个查询使用Intersection合并的方式执行的话，过程如下：

1. 从idx_num二级索引对应的B+Tree取出num=1的相关记录。
2. 从idx_expire_time二级索引对应的B+Tree取出expire_time = '2024-08-29 14:18:24'相关记录

二级索引的记录由索引列和主键构成，所以可计算出2个结果集id的交集。

按照上一步生成id值列表进行回表操作，也就是从聚簇索引中把指定id值完整用户记录取出来，返回给用户。

⭐️为什么不使用某个搜索条件去读一个二级索引，然后回表过滤另外一个搜索条件？

需分析2种查询执行方式之间需要的成本代价。

1. 只读一个索引

   1. 按照某个搜索条件读取一个二级索引
   2. 按照从该二级索引得到的主键值进行回表操作
   3. 过滤其他搜索条件

2. 读多个二级索引之后取交集成本

   1. 按照不同搜索条件分别读取不同二级索引
   2. 将从多个二级索引得到的主键值取交集
   3. 最后根据主键值进行回表操作

虽然读取多个二级索引比读一个二级索引消耗性能，但大部分情况下读取二级索引的操作是顺序IO，而回表操作是随机IO，所以如果只读一个二级索引时需要回表记录数特别多，而读多个二级索引之后取交集的记录非常少。所以MySQL优化器会选择回表性能成本判断，来进行选择读一个索引还是多个索引（这也是前面我提出数据量少不会走索引合并的原因）。

MySQL在以下特性情况会使用Intersection索引合并。

1.1 等值匹配

二级索引列必须是等值匹配情况。

对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现之匹配部分列的情况。

下面的2种情况不能进行索引合并

create index idx_num on d (num);
create index idx_expire_time on d (expire_time);
create index idx_multi on d (status, remark);

-- 情况1
select * from d where num > 1 and expire_time = '2024-08-29 14:18:24';

-- 情况2
select 8 from d where num = 1 and status = 1;

🌔情况1：使用了范围查询。不能使用索引合并

🌔情况2：status在idx_multi联合索引中，而idx_multi其余字段没有出现在搜索条件中。不能使用索引合并

1.2 主键列范围匹配

比如用到了聚簇索引和idx_multi，会产生索引合并。

select * from d where id > 100 and status = 1;

主键索引是有序的，按照有序的主键去回表取记录。（Rowid Orderred Retrieval 简称ROR）。

二级索引为什么在等值情况下可能使用索引合并？
因为这种情况，二级索引查询出的结果集是按照主键值排序的。

索引合并会从多个二级索引查询主键值交集，若从各个二级索引查询到的结果集本身已经按照主键排好序，求交集很容易。

但是否使用索引合并取决于MySQL优化器。

优化器根据查询，导致索引回表开销过大，通过索引合并开销减少才会使用索引合并。

2. Union索引合并

Union是并集，适用于不同索引的搜索条件之间使用or连接的情况

select * from d where num = 1 or expire_time = '2024-08-29 14:18:24';

2.1 等值匹配

和Intersection索引合并一样

2.2 主键列范围匹配

和Intersection索引合并一样

2.3 使用Intersection索引合并搜索条件

某些搜索条件部分使用Intersection索引合并得到主键集合，和其他方式得到主键集合取交集。

select * from d where status = 1 and remark = 'a' or (num = 1 and expire_time = '2024-08-29 14:18:24');

优化器执行流程：

1. 先按照搜索条件num = 1和 expire_time = '2024-08-29 14:18:24'从索引idx_num和idx_expire_time中使用Intersection索引合并方式得到一个主键集合。
2. 按照status = 1 and remark = 'a'从联合索引idx_multi中得到另外一个主键集合。
3. 采用Union索引合并方式把上述两个主键集合取并集，并进行回表操作，将结果返回。

当然查询符合这些情况，需要通过优化器自行选择，看开销成本选择是否走Intersection索引合并和Union索引合并。

3. Sort-Union合并

Union索引合并的前提是各个二级索引进行等值匹配才有可能被用到。

下方就没有使用Union索引合并

select * from d where num > 1 or expire_time < '2024-08-29 14:18:24';

num>1从idx_num索引中获取的二级索引记录的主键值不是排好序的，同理expire_time < 'xxx'也不是排好序的。

可以通过num>1条件获取记录后，按照主键排序，expire_time < 'xx'同理，剩下就有可能走Union索引合并。具体还要看优化器的选择。

4. 联合索引替代Intersection索引合并

select * from d where status = 1 and remark = '1';

走联合索引，而不是索引合并。

二. 连接查询

1. 连接本质

🌔SQL准备

CREATE TABLE e1 (m1 int, n1 char(1));
CREATE TABLE e2 (m2 int, n2 char(1));
INSERT INTO e1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO e2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

连接的本质：把各个连接表中的记录都取出来依次匹配，并把匹配的组合加入结果集返回。

连接查询的结果集：一个表中的每个记录与另外一个表的每个记录相互匹配的组合，也称为笛卡尔积。

如e1有3条，e2有3条，笛卡尔积为3 * 3 = 9条记录。

2个表连接查询的形式

select * from e1 join e2;

select * from e1, e2;

2. 连接过程

如果连接的表的数量是未知的，那这些表连接起来产生的笛卡尔积非常巨大，如3个100行记录的表，产生的笛卡尔积为100 * 100 * 100 = 100w数据。所以在连接时需要条件过滤特定记录是必要的。

select * from e1, e2 where e1.m1 > 1 and e1.m1 = e2.m2 and e2.n2 < 'd';

🚪涉及单表的过滤

• e1.m1 > 1
• e2.n2 < ‘d’

🚪涉及多表过滤

• e1.m1 = e2.m2

也就是所谓3个条件进行过滤，那连接查询的过程如下：

确定驱动表

确定第一个需要查询的表（驱动表）。单表中执行查询语句只需选取代价最小的那种访问方法去执行单表查询语句（通过执行计划，来看哪个表的type，由const，ref，ref_or_null，range，index，all等来看代价小的作为驱动表）。

遍历驱动表结果，进行被驱动表匹配记录

针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到e2表查找匹配到记录。

若e1获取了2条记录，那么需要查询2次e2表。

e1.m1 = e2.m2搜索条件的流程

当e1.m1 = 2，通过e1.m1 = e2.m2相当于e2.m2 = 2，此时e2表相当于有2个过滤条件e2.m2 = 2和e2.n2 < 'd'，并进行e2表查询。

当e1.m1 = 3时，同理。

可看出，查询e1表一次，而e2表查询是2次。

可以理解为驱动表访问一次，被驱动表访问多次。

3. 内连接和外连接

⭐️执行过程先on后where

WHERE子句中的过滤条件

WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

ON子句中的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

3.1 左（外）连接

选取左侧为驱动表

SELECT * FROM t1 LEFT [OUTER] JOIN e2 [on 连接条件] [where 普通条件]

3.2 右（外）连接

选取右侧为驱动表

SELECT * FROM t1 RIGHT [OUTER] JOIN e2 [on 连接条件] [where 普通条件]

3.3 内连接语法

内连接和外连接根本区别：驱动表中记录不符合on子句中的连接条件时不会把该记录加入到最后的结果集，一种简单内连接语法，把需要连接的多个表都放到from子句后面，针对内连接，MySQL提供不同语法支持

select * from t1 [inner | cross] join e2 [on 连接条件] [where 普通条件]

select * from t1 join t2;

select * from t1 inner join t2;

select * from t1 cross join t2;

4. MySQL连接执行

4.1 嵌套循环连接（Nested-Loop Join）

驱动表只访问一次，被驱动表访问多次（根据驱动表的记录条数决定）。

对于内连接来说，选择哪个表作为驱动表都没关系，而外连接的驱动表是固定的。

如果有3个表进行连接的话，那么首先两表连接得到的结果集作为新驱动表，第三个表作为被驱动表。

for each row in t1 { # t1查询
	for each row in t2{ # t1的结果，遍历t2
		for each row in t3{ # t1和t2表的结果，遍历t3
		
		}
	}
}

这个过程类似嵌套循环，访问次数取决于驱动表执行单表查询后的结果集中的记录条数的连接执行称之为嵌套循环连接（Nested-Loop Join）。

4.2 索引加快连接速度

在嵌套循环连接的地方需要多次访问被驱动表，如果访问被驱动表的方式都是全表扫描，性能消耗严重。

查询t2表相当于一次单表查询，可利用索引加快查询速度

SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';

我们使用的是嵌套连接算法执行的连接查询，

驱动表e1通过结果集获取结果后，通过嵌套连接算法对被驱动表e2进行查询2次

-- 第一次
SELECT * FROM e2 WHERE e2.m2 = 2 AND e2.n2 < 'd';
-- 第二次
SELECT * FROM e2 WHERE e2.m2 = 3 AND e2.n2 < 'd';

可看到，e1.m1 = e2.m2设计两个表的过滤条件在针对e2表做查询时，e1.m1条件已经确定了，所以我们对e2查询优化即可，上述两个查询条件用到m2和n2两个条件，我们可在e2添加索引

create index idx_m2 on e2 (m2);

因为对m2列是等值查找，所以使用到ref访问方法。

如果在n2单独加索引，需要通过MySQL优化器计算索引成本，进行选择使用哪个合适的索引，而且也许会走全表

可以建立

create index idx_m_n on e2 (m2, n2);

4.3 基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程：把表从磁盘加载到内存，通过内存比较匹配条件是否满足。

对于大表而言，内存里是不可能存放下表中所有记录，所以在扫描表前边记录的时候，后面的记录可能还在磁盘里。扫描到后边记录时，需要把前边的记录在内存中释放掉。

采用嵌套循环连接算法的两个表连接过程，被驱动表需要被访问多次，若被驱动表数据特别多并且不能使用索引访问时，相当于从磁盘读多次表数据，IO性能消耗巨大。

在内存中每一条记录会和驱动表结果集做一条记录匹配，之后从内存清除掉，然后在从驱动表结果集拿下一条记录，再把被驱动表记录加载到内存中，周而复始，驱动表结果集有多少条记录，被驱动表从磁盘加载到内存中就有多少次。

若当被驱动表记录加载到内存时，一次性和多条驱动表记录做匹配，就可以减少重复性从磁盘加载被驱动表。

MySQL提出join buffer概念，join buffer再执行连接查询前申请到一块固定大小内存，先把若干条驱动表结果记录放到join buffer里，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，匹配过程都是在内存中完成，所以会显著减少被驱动表的IO代价。

最好的情况是join buffer足够大，容纳驱动表结果集的所有记录。

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

配置

show variables like 'join_buffer_size';

对于被驱动表查询来说，最好为被驱动表加上效率高的索引。如果不能使用索引，并且机器内存比较大，可以尝试调大join_buffer_size的值进行连接优化。

驱动表的记录并不是所有列都放到join_buffer中，只有查询列表中的列和过滤条件中的列才会放入join buffer里，所以查询时减少*的使用，而是采用所需字段的查询，会带来join buffer可放置更多的记录。