MySQL-查询-SQL简化与子查询的物化

条件化简

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

## 移除不必要的括号
((a = 5 AND b = c) OR ((a > c) AND (c < 5))) 改 (a = 5 and b = c) OR (a > c AND c < 5)
## 常量传递（constant_propagation）
a = 5 AND b > a  改 a = 5 AND b > 5
## 移除没用的条件（trivial_condition_removal）
(a < 1 and b = b) OR (a = 6 OR 5 != 5)
很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的：
a < 1 OR a = 6
## 表达式计算 
a = 5 + 1 改成 a=6 表达式不会对带有符号或者函数的进行化简,只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引
##HAVING子句和WHERE子句的合并
如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。

外连接消除

具体可以先看连接查询

内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。 这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却 无法优化表的连接顺序。

外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表 记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃

凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子 句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了！

子查询

1
2
3

## 写法
[NOT] IN/ANY/SOME/ALL子查询
ANY/SOME相同

子查询查询方式

1
2
3
4

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);
它的执行方式和年少的我想的一样：
先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = ...。

IN子查询优化

在MySQL5.5以及之前的版本没有引进semi-join和物化的方式优化子查询时，优化器都会把IN子查询转换为EXISTS子查询，所以当时好多声音都是建议大家把子查询转为连接，不过随着MySQL的发展，最近的版本中引入了非常多的子查询优化策略，大家可以稍微 放心的使用子查询了，内部的转换工作优化器会为大家自动实现。

1

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

1. 不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里 (称为物化)
2. 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。
3. 如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

最后如果物化表没重复值甚至还会与物化表进行连接查询 SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

下面条件是会转半连接SEMI JOIN 的情况

对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

1. 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。
2. 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
3. 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
4. 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数