?
最近看了下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）<op> 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$ <max>(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只能为单列和单值。
?
?
?