MySQL子查询辨析

 

一 明确IN/EXISTS的语义

1 “expression IN (subquery)”这样的形式,表示一个表达式变量在一个子查询中存在,其结果是布尔值,TRUE或FALSE.

如果subquery 的结果中有重复值,则只要这样的值存在一次,IN格式的子查询结果就为TRUE,所以我们可以把 subquery表意为 “DISTINCT subquery”.

所以,“expression IN (subquery)”这样的形式其实表达的就是半连接的语义.

2 “EXISTS (subquery)”这样的形式,表示在一个子查询中存在子查询指定的情况,其结果是布尔值,TRUE或FALSE.

如果subquery 的结果中有重复值,则只要这样的值存在一次,EXISTS格式的子查询结果就为TRUE,所以我们可以把 subquery表意为 “只要有一个存在就为TRUE”.

所以,“expression IN (subquery)”这样的形式其实表达的也是半连接的语义.

所以,优化器对IN/EXISTS类型的子查询,优化的首选思路就是转为半连接操作. 这点在MySQL中正是这样.

NOT IN/NOT EXISTS同理.

二 MySQL处理子查询的过程

主体过程: 实则分为2种情况

情况一：可优化，被优化为半连接

1 把子查询转为半连接语义,即上拉子查询为“semi join”操作. 这是子查询上拉技术,只是连接语义变为半连接的语义.

2 如果不能转为“semi join”操作,则转为“exists”谓词对应的操作.

不能转为“semi join”操作的条件是: 子查询和父查询中的表的个数之和超出了MySQL所能处理的多表连接的表数(表数是用宏表示的MAX_TABLES的值,最大是63)。所以这个情况通常情况下遇不到。

3 如果被优化为半连接，则后续使用代价估算模型，判断，是否可以再进一步用物化操作来优化半连接中的被连接的表。如果物化的代价低，则使用物化。

4 如果被优化为半连接，后续为半连接去重操做选择5种策略之一。

所以，以上都是归属于上拉子查询为“semi join”操作这种优化方式的（其他的优化方式，属于小的优化，最主要的优化是上拉子查询为“semi join”操作这种优化方式）。

情况二：不可优化

5 如果不能被优化为半连接，则会被标示为“subquery”，看到这个，意味着子查询没有被优化。

6 尽管一些子查询没有被使用上拉技术优化，但其中涉及的表，依然可以判断是否可以被物化优化。所以注意在使用“show warnings;”看到的结果中会有一些“<materialize>”和“materialized-subquery”的提示字样。