MySQL半连接优化优化策略

文
章
目
录

• 前言
• 子查询
• 物化
• 半连接
• FirstMatch
• TablePullout
• LooseScan
• DuplicateWeedout
• 总结

前言

本篇文章重点讲解MySQL中子查询的半连接优化。

为了更好的描述，我们使用班级、学生两张表。

• class表为班级表：class_num为班级的编码，class_name为班级名称
• student表为学生表，其中包含学生信息，还包含class_num（该学生对应哪个班级编码）
• 班级表与学生表处于一对多的关系

子查询

来看这样一条SQL：

SELECT class.class_num, class.class_name
FROM class
INNER JOIN student
WHERE class.class_num = student.class_num;

在内连接中，关联条件on与where的作用一致，该SQL等同于以下SQL

SELECT class.class_num, class.class_name
FROM class
INNER JOIN student
on class.class_num = student.class_num;

对班级表和学生表进行内连接，其中关联条件为班级编码，查询出班级信息。

如果有学生对应相同的班级，那么查询结果就会出现重复班级（比如：小菜和菜菜这两位同学都对应A班级）。

假设优化器选择student表为驱动表，class表为被驱动表，则流程如下图：

如果想要对结果去重，可以转换成以下子查询SQL

SELECT class_num, class_name
FROM class
WHERE class_num IN
(SELECT class_num FROM student);

在子查询SELECT class_num FROM student中会查询学生表中所有的班级编码

当小菜和菜菜都处于A班级时结果还是会出现重复，但是在外层查询使用in进行查询时，相当于作一次去重

从这个案例可以发现：在某些场景下，in的子查询可以被可以被优化为内连接，但是需要解决内连接的重复结果问题

物化

MySQL将子查询结果转换变成临时表称为物化，临时表称为物化表

物化默认是开启的，并不一定所有的子查询都会进行物化，基于成本评估是否物化也是默认开启的

materialization=on 开启物化

subquery_materialization_cost_based=on 开启基于成本评估是否物化

半连接

MySQL将上述这种子查询转化为内连接称为半连接

半连接是一种对子查询的优化，将子查询转换为内连接后，由优化器评估哪个表为驱动表的成本最低

使用半连接不仅需要将子查询转化为内连接，还需要对其进行去除重复记录

MySQL提供几种半连接策略进行去重，默认情况下开启所有半连接策略semijoin=on

FirstMatch

对于一种内层子查询与外层查询有关联的查询

select * 
from a 
where a.id in (select b.id from b where a.a1 = b.b1)

子查询表b中的查询条件需要外层查询表a相关信息

使用FirstMatch策略（firstmatch=on默认开启），循环查找

• 1）从外层表a中获取记录
• 2）拿到该记录的a1去表b中寻找满足条件（a1=b1）的记录
• 3）满足条件则放入结果并停止在表b中寻找（去重）
• 4）找不到则继续遍历外层表a

步骤1-3为循环

TablePullout

如果子查询结果不会出现重复，那么就不需要解决去重了

可以通过主键值或者唯一索引来构建子查询的结果，避免重复

在MySQL中通过主键或唯一索引避免重复的半连接策略称为table pullout

LooseScan

在我们的案例中，班级编码肯定不是唯一的，因此不能使用这种策略

当不能使用唯一值时，就需要通过其他手段进行去重

当物化表作为驱动表并且包含关联条件的二级索引时，可以使用LooseScan策略，loosescan=on默认开启

当student表作为驱动表，并且拥有查询值class_num的二级索引，在索引中class_num就是有序的

当class_num相同时，只需要取第一条相同的记录进行关联，然后跳过后续相同的记录即可（图中第一条和第二天记录）

DuplicateWeedout

duplicateweedout=on 默认开启DuplicateWeedout半连接策略

当无法使用索引时，可以在结果集使用临时表记录来进行判断是否重复

当第三条记录（学生名为小菜）最后加入结果集时，判断tmp临时表中是否已存在class_num为1的值，已存在则不加入结果

因此第三条记录不会被加入结果集中

总结

• 将子查询的结果存储在临时表中的过程为物化，存储子查询结果的临时表为物化表
• 子查询在某些场景下可以转换成内连接，让优化器选择成本低的驱动表，这被称为半连接。
• 使用半连接需要将结果进行去重，提供多种策略对其进行去重。
• FirstMatch通过循环外层查询，从外层查询获取记录，将记录拿到内层表中进行匹配，如果满足条件则放入结果集并停止在内层查找，后续继续循环外层查询，以此保证去重。
• TablePullout通过使用主键值或者唯一索引，让其记录没有重复值来保证去重。
• LooseScan通过物化表为驱动表并且拥有查询列的二级索引，保证查询列有序，当查询列相同时，拿第一条记录进行匹配，后续相同记录跳过，以此保证去重。
• DuplicateWeedout通过使用临时表记录结果，当出现重复时进行过滤不加入结果集，以此保证去重。

以上就是MySQL半连接优化优化策略的全部内容。