MySQL基于规则优化

1. 条件化简：移除不必要括号、常熟传递、等值传递、移除无用条件、表达式计算、HAVING子句和WHERE子句合并、常量表检测
2. 外连接消除：空值拒绝（外连接中通过where子句过滤NULL值），符合条件后，内外连接可以互相转换，优化器可以评估表的不同连接顺序降低查询成本
3. 子查询优化： 按返回的结果集不同子查询分为标量子查询（查询单一值）、行子查询、列子查询和表子查询 按与外层查询关系分为不相关子查询（子查询不依赖外层查询结果）和相关子查询 子查询在布尔表达式的使用：
4. 对于<、>、=之类操作符来说，子查询只能是标量子查询或行子查询
5. 对于[NOT] IN（判断某个操作数是否在某个子查询结果集中）/ANY/SOME （只要子查询结果集中存在某个值和给定操作数比较为true，则表达式为true）/ALL（操作数必须与结果集全部匹配）来说，子查询是一个集合
6. EXIST，仅需要判断子查询是否有记录。

注：

1. 子查询必须用小括号扩起来。
2. 在SELECT子句中的子查询必须是标量子查询。
3. 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量。
4. 对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句。
5. ORDER BY子句、DISTINCT语句以及没有聚集函数以及HAVING子句的GROUP BY子句在子查询中无意义，查询优化器会直接删除。
6. 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。

物化表：不直接将不相关子查询的结果集作为外层查询的参数，而是将结果集写入一个临时表中，写入记录会被去重，表的列就是结果集的列。

物化表查询相当于子查询与外层查询的内连接。

半连接：对于s1中的某条记录，只关心在s2中是否有记录与之匹配，而不关心具体有多少条记录与之匹配，只保留s1表的记录。

半连接实现策略，查询优化器会选择以下成本最低的策略：

1. Table pullout （子查询中的表上拉）：子查询查询条件只有主键或唯一索引时，转连接查询。
2. DuplicateWeedout execution strategy （重复值消除）：加入结果集前，尝试加入到只包含主键的临时表，根据能否插入判断是否重复。
3. LooseScan execution strategy （松散扫描）：对于多条相同值索引，只取第一条查询记录，匹配成功后，把同索引均加入结果集。
4. Semi-join Materialization execution strategy（半连接物化策略）：不想管子查询物化后连接查询
5. FirstMatch execution strategy （首次匹配）：取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉，重复上述过程

semi-join的适用条件：

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

对于不能转换semi-join的子查询：

1. 对于不相关子查询，可以将其物化后再参与查询。
2. 不管是相关子查询还是不相关子查询，都可以将IN子查询转换为EXISTS子查询。

MySQL不相关子查询的优化方式多为子查询转连接

子查询结果相当于一个派生表，对于派生表的处理，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。