MySQL优化器和SemiJoin优化
1.1. MySQL执行流程
MySQL的执行过程包括多个子阶段：语法分析、语义检查、逻辑优化、物理优化和执行。其中逻辑优化和物理优化统称为查询优化。一个查询优化器的输入是查询树，输出是查询执行计划。
逻辑优化也称为基于规则的查询优化（Rule Based Optimization，简称RBO）。主要是对查询进行逻辑上的等价变换，目的是通过这些变换提高查询的性能。 物理优化也称为基于代价的查询优化（Cost-based Optimization，简称CBO）。主要是通过一些模型，预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
1.2. SemiJoin定义
SemiJoin是表上的一个运算。假定R和S是两个表，R SemiJoin S可记为R ? S，结果是在S中满足条件的元组。例如下面的例子是“雇员”和“部门”和它们的半连接的表：
从上面的结果可以得出如下结论： ①半连接的结果是左表的子集。 ②增加右表一条重复记录，结果并不随之增多。
2.1 优化方法
通用的转换格式如下：
如上格式的子查询被转换为semijoin的格式如下（ot表示外表，it表示内表）：
从上面的结构可以看到，转化是支持多列的。
2.2 为什么可以转化：
当执行如下SQL时，也是先遍历雇员表，然后在部门表里查找是否匹配。实际上和上面SemiJoin的语义是一致的，结果也一样。
具体Mysql将In转化成SemiJoin的代码逻辑实现在如下函数里：
2.3 一个子查询能够转换成SemiJoin还需满足如下条件：
1. 必须是In或any子查询
2. 不包含UNION 按照扁平化规则后，UNION会导致结果不一致。比如：t1,t2和t3表定义和数据如下：
则执行如下SQL：select?a?from?t1?where?a?in?(select?b?from?t2?UNION?select?c?from?t3); 正确结果应该为：
但是SemiJoin转换后的SQL为：select?a?from?t1?semijoin?t2?where?t1.a=t2.b?UNION?select?a?from?t1?semijoin?t3?where?t1.a=t3.c; 由于UNION的去重，导致结果为：
另外即使是UNION ALL，如果t2与t3存在相同记录，结果也是不正确的。
3. 不包含GROUP BY 或 HAVING 含有groupby的SQL，转换后（即扁平化后），导致与原来语义不一致了。比如 原来是：select a from t1 where b in (select b from t2 group by b) 按照规则转换成semijoin后为：select a from t1 semijoin (t2) where t1.b=t2.b group by t2.b; 由于a1不在group by内，语法错误。
4. 不包含聚集函数。通过测试，确实没有走SemiJoin。
5. 不包含LIMIT。通过测试，控制台直接报错。
6.?子查询有ORDER?BY时，转换后去掉ORDER?BY
7.? 外部查询没有STRAIGHT_JOIN（错） 关键字STRAIGHT_JOIN表明，该查询的JOIN顺序不需要优化，按照原来SQL的顺序依次进行。 MYSQL官方文档说有限制，经测试没有限制。比如，如下依然能转换为semijoin：
8. 是否相关子查询均支持。
注：测试所用的数据参考如下 数据库名为ldy，表如下：
2.4 举例
例子1: 二重In子查询嵌套SQL通过两次SemiJoin优化成单层SQL，参考如下：
例子2: 并列in查询，and条件下能够转成SemiJoin：
例子3，并列in查询，or条件下不能转成semijoin：
例子4: 支持多列SemiJoin：
3. SemiJoin的几种执行策略
转换成SemiJoin后，MySQL将要根据权重，采用如下执行策略（以下策略也能通过Mysql的修改系统变量optimizer_switch，将某些策略关闭或打开）。假设有两个表：国家表（Country）和城市表（City），查找存在某一城市人口大于该国人口33%的所有国家。查询语句如下：
1. Duplicate Weedout：将semi-join当做普通innerJoin，之后使用临时表将重复项去除。 举例：Country SemiJoin City的执行过程如下：
Country与City内连接后有German有三条记录，通过建立临时表，由于临时表的主键是Country表的RowId，重复项将不能插入。具体可以看到，去重过程如下：
2. FirstMatch：执行join只挑选第一次匹配的项，非常类似于In语义。 举例，Country InnerJoin City的嵌套循环流程如下，先遍历Country再遍历City：
当是SemiJoin时，在City里找到一个，就不需要继续找了。
3. LooseScan：sj-inner表将重复项分组，选出一个即返回。 查询拥有人造卫星的国家，数据库有国家表（Country）和人造卫星表（Satellite）。查询SQL如下：
假如Satellite.country_code是索引，通过按索引顺序遍历，发现已经是分组的了。
利用索引很容易从一个组调到另一组，而一组内只需要判断第一条记录即可，具体如下：
4. Materialize：
假如不是相关子查询，可以先物化内表为一个临时表，由于该临时表在条件字段上采用了索引，保证了唯一性（即消除了重复字段），SemiJoin的结果就和innerJoin相同了。该临时表与外表执行JOIN。
根据临时表与外表Join过程，采用类似NestedLoop形式，根据先遍历临时表还是外表，进一步包括Lookup和Scan两种。Lookup为物化inner表（该表包含一个索引，该索引既可以删除重复元素，也可用于后面执行Join运算），先循环遍历outer表，并在物化表查找。
Scan为物化inner表（该表包含一个索引，该索引既可以删除重复元素，也可用于后面执行Join运算），先循环遍历物化表，并在outer表查找。
https://mariadb.com/kb/en/library/optimization-strategies/ https://dev.mysql.com/doc/refman/8.0/en/semi-joins.html
腾讯数据库技术团队对内支持微信红包，彩票、数据银行等集团内部业务，对外为腾讯云提供各种数据库产品，如CDB、CTSDB、CKV、CMongo， 腾讯数据库技术团队专注于增强数据库内核功能，提升数据库性能，保证系统稳定性并解决用户在生产过程中遇到的问题，并对生产环境中遇到的问题及知识进行分享。
var first_sceen__time=(+new Date());if (""==1 && document.getElementById('js_content')) { document.getElementById('js_content').addEventListener("selectstart",function(e){ e.preventDefault(); }); } (function(){ if (navigator.userAgent.indexOf("WindowsWechat") !=-1){ var link=document.createElement('link'); var head=document.getElementsByTagName('head')[0]; link.rel='stylesheet'; link.type='text/css'; link.href=https://cloud.tencent.com/developer/article/"//res.wx.qq.com/mmbizwap/zh_CN/htmledition/style/page/appmsg_new/winwx45ba31.css"; head.appendChild(link); } })();
腾讯数据库技术
长按二维码向我转账
受苹果公司新规定影响，微信 iOS 版的赞赏功能被关闭，可通过二维码转账支持公众号。
阅读
分享 在看
已同步到看一看
朋友会在“发现-看一看”看到你“在看”的内容
确定
已同步到看一看写下你的想法
最多200字，当前共字 发送
已发送
朋友将在看一看看到
确定
写下你的想法...
取消
发布到看一看
确定
最多200字，当前共字
发送中
微信扫一扫 关注该公众号
微信扫一扫 使用小程序
即将打开""小程序
