mysql优化器预处理_MYSQL 优化器预处理阶段对子查询的优化

最近看了下MYSQL优化器预处理阶段对子查询的优化, 同时还有些疑问发出来向大家请教下。

sql_resolver.cc

JOIN::prepare() 预处理阶段

1) remove_redundant_subquery_clauses()方法 去除子查询中冗余语句

MySQL 中的子查询类型：enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS, EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS};

MySQL 会对EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS 这四种类型的子查询过滤冗余语句，规则如下：

a) 如果有order by 直接去掉, 因为这里排序是没有意义的 例如：

select * from t1 where name in ( select name from t2 where t2.col = xxx order by name) -->

select * from t1 where name in ( select name from t2 where t2.col = xxx )

b) 如果有distinct 直接去掉,其实这里有个疑问：

如果子查询有100W条记录，去重后只剩下100条记录了这样比较100条记录，否则 id > all 的话需要比较100w条记录呢，这是否说明distinct还是有效果的呢？

例如：

select * from t1 where id > all ( select distinct(name) from t2 where t2.col = xxx ) -->

select * from t1 where id > all ( select name from t2 where t2.col = xxx )

c) 如果子查询有group by且不包含聚合函数和having的条件 直接去掉。

为何要加上两个非条件呢?

聚合函数说明需要输出统计信息所以group by是有用的，having使用过滤分组的同样这样的group by也是有用的,所以这这种情况的group by需要保留。

例如：

select * from t1 where id in ( select id from t2  where t2.col = xxx group by t2.name) -- >

select * from t1 where id in ( select id from t2  where t2.col = xxx )

2) resovle_subquery()

a)转化子查询到半连接。

优点：子查询中全表扫描外层表的每条记录和子查询做join(5.6之前)，半连接(上拉/扁平化)将子查询的表上拉到外面做join操作

这样可以充分使用join的优化策略。

注意 下面的这个例子还有个对外表去重操作，至于如何去重取决于优化器的半连接优化策略。

1. 重复剔除:join结果到临时表但是在join列上加上主键，这样临时表中外层表就不会有重复的记录了

2. 松散扫描：子查询结果有序且子查询的join列有索引，则对子查询做分组且join每组的第一条记录

3. 第一次匹配：外层表join到子查询的第一条符合记录就退出这条记录的join操作，接着做外层表的下一条记录join子查询

4. 索引式物化：子查询物化到临时表，(MySQL自动为临时表创建索引)，可以把临时表当成内表利用临时的索引来做join。

5. 扫描式物化：子查询物化到临时表，但是子查询小结果集的临时表被当成了外表，所以做全表扫描。

例如：

select t1.* from t1 where t1.xxx in ( select t2.xxx from t2 )  -->

select t1.* from t1 join t2 on t1.xxx = t2.xxx

前置条件：

/*

Check if we're in subquery that is a candidate for flattening into a

semi-join (which is done in flatten_subqueries()). The requirements are:

1. Subquery predicate is an IN/=ANY subquery predicate

2. Subquery is a single SELECT (not a UNION)

3. Subquery does not have GROUP BY

4. Subquery does not use aggregate functions or HAVING

5. Subquery predicate is at the AND-top-level of ON/WHERE clause

a) select xxx from t1 where t1.xxx in ()

b) select t1.xx from t1 join t2 on  t2.xx2 in ( )

6. We are not in a subquery of a single table UPDATE/DELETE that

doesn't have a JOIN (TODO: We should handle this at some

point by switching to multi-table UPDATE/DELETE)

rm 单表的UPDATE/DELETE的子查询是不行，因为UPDATE/DELETE没有join一说

7. We're not in a confluent table-less subquery, like "SELECT 1".

rm  无表子句不处理

8. No execution method was already chosen (by a prepared statement)

9. Parent select is not a confluent table-less select

rm  父表不能是无表查询 如 select 1

10. Neither parent nor child select have STRAIGHT_JOIN option.

*/

b)物化标识子查询

将非相关子查询保存到临时表中或者缓存中。

c) 执行in 向 exists转换(优点：这样exists就可以使用半连接的优化策略了)

例子：

outer_expr IN (SELECT inner_expr FROM … WHERE subquery_where) -->

EXISTS (SELECT 1 FROM … WHERE subquery_where AND outer_expr=inner_expr)

但是还有两种情况需要考虑outer_expr为null的情况和inner_expr为null的情况，这两点也是我比较疑惑的地方

例如，有如下表a1：

我执行select * from a1 where id in ( select df from a1)  -->无记录

select * from a1 as t1 where exists ( select 1 from a1 t2 where (t1.id = t2.df or t2.df is null ) )  --> 三条记录

结果竟然不一样！这是为什么呢？

d) ALL/ANY/SOME 向MIN MAX转换 OP为大于或者小于操作,例子会将子查询转化成下面两种其中的一种。

例子:(oe 外表的表达式， ie 内表的表达式， cmp为 ">  

oe $cmp$ (SELECT ie FROM ... WHERE subq_where ... HAVING subq_having) -->

oe $cmp$ (SELECT MAX(...) )  // handled by Item_singlerow_subselect

oe $cmp$ (SELECT ...)   // handled by Item_maxmin_subselect

前置条件：

/*

If this is an ALL/ANY single-value subquery predicate, try to rewrite

it with a MIN/MAX subquery.

E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten

with SELECT * FROM t1 WHERE b > (SELECT MIN(a) FROM t2).

A predicate may be transformed to use a MIN/MAX subquery if it:

1. has a greater than/less than comparison operator, and

2. is not correlated with the outer query, and

rm  非相关子查询(与外表无关) select t1.xx from t1 where t1.xxx > any ( select xxx from t2 where t2.xx = 123 )

3. UNKNOWN results are treated as FALSE, or can never be generated, and

rm 未知结果被当成false处理，或者永远不会生成未知结果

*/

e) 使用值替代标量子查询

(标量子查询：select (subquery) from table)其中subquery只能为单列和单值。