mysql5.7官网直译SQL语句优化--派生表和试图引用的优化

2.3 Optimizing Derived Tables and View References 派生表和试图引用的优化
优化器能够对派生表(from条件中的子查询)使用两种策略：
>合并派生表到外部查询块中
>物化派生表到一个内部临时表中。
优化器可以使用相同的策略来处理试图引用
例子1：
SELECT * FROM (SELECT * FROM t1) AS derived_t1;
通过合并，查询可以类似如下执行：
SELECT * FROM t1;
例子2：
SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
通过合并，查询可以类似如下执行：
SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;
通过物化，derived_t1和derived_t2被作为独立的表在各自的查询中使用。
优化器以同样的方式来处理派生表和试图引用：不论何时，它都会避免不必要的物化，通过压入将外查询加入到派生表从而生成更有效的执行计划。(具体案例，请看8.2.2.2的通过物化来优化子查询)
如果合并会导致一个外部查询块引用了超过61张基本表，优化器会选择物化来代替合并。
优化器会将一个order by条件在派生表中或者是在试图引用中的，转变到外部查询块中，如果这个条件总是true的话：
1）外部查询没有被分组和聚合
2）外部查询没有特别的DISTINCT,HAVING,或者是ORDER BY.
3) 外部查询将派生表或试图引用作为唯一的来源在from条件中。
否则，优化器会忽略ORDER BY条件。
下面的说明是可以用来影响优化器是否合并派生表和试图引用到外部查询块：
>来自optimizer_switch系统变量的derived_merge标记能够被使用，假设没有其他规则阻止合并。具体看8.9.3的优化开关。默认，标记是允许合并。标记关闭会阻止合并并且避免ER_UPDATE_TABLE_USED错误。
derived_merge标记也能用于试图，视图中没有使用算法因素。这样，如果一个ER_UPDATE_TABLE_USED发生了那么是在一个试图引用中使用一个等价的表达式在子查询中，添加ALGORITHM=TEMPTABLE到试图引用的定义中来阻止合并，并优先于derived_merge的值。
这是可能的，在子查询中使用任何阻止合并的手段来使得合并无效，虽然这并没有明显的影响到物化。阻止合并的结构也同样适用派生表和试图应用：
1）聚合函数(SUM(),MIN(),MAX(),COUNT()等等)
2）DISTINCT
3) GROUP BY
4) HAVING 
5) LIMIT
6) UNION OR UNION ALL
7) 在select 查询列表中的子查询
8）归属于用户的变量
9）只对文字值的引用(在这种情况下，没有底层表)
（于上面重复的一段话，也不知道是不是官方写错了)derived_merge标记也能用于试图，视图中没有使用算法因素。这样，如果一个ER_UPDATE_TABLE_USED发生了那么是在一个试图引用中使用一个等价的表达式在子查询中，添加ALGORITHM=TEMPTABLE到试图引用的定义中来阻止合并，并优先于derived_merge的值。
如果优化器选择了物化策略，而不是合并派生表，则处理查询如下：
>优化器延迟派生表的物化，直到他的内容不再执行查询期间使用。这提高了性能，因为延迟物化的结果是很少的数据才需要物化。考虑一个查询其中派生表连接另外一张表：如果优化器首先处理另外的表，并发现没有数据要返回，则连接就不需要进一步执行了，并且优化器完全不需要物化派生表。
>通过查询计划，优化器也许添加一个索引到派生表来提高查询速度。
考虑如下的EXPLAIN语句，其中一个自查询是一个select查询在from条件中：
EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;
优化器会避免物化子查询通过延迟手段直到结果需要再SELECT执行计划中使用才去物化。在这种情况下，查询不会被执行(因为查询是一个EXPLAIN语句)，所以结果永远不需要。
即使查询被执行，延迟物化操作也能够使得优化器避免产生完整的物化。如果需要，查询会快速并且准时的完成物化。考虑如下的查询，其中一个在from条件中的子查询结果与另一张表连接:
SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
如果优化器首先处理t1，并且where条件产生一个空集合，连接一定是一个空并且子查询不需要被物化。
对于当一个派生表需要被物化时，优化器也许会加一个索引到物化表中去提高访问它的速度。如果一个索引能够以ref方式访问表，它能够极大的减少查询需要读取的数据量。考虑如下的查询：
SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;
优化器会在derived_t2表中的列f1中造成一个索引，如果成功，则可以使用ref方式来用最低代价来执行查询。添加索引之后，优化器能够使用索引来访问表像对待常规表一样对待被物化的表，同样受益于被创建的索引。创建索引的代价于没有使用索引执行查询的代价比起来微不足道。如果ref方式的访问比其他一些方法有更大的代价。优化器不会创建索引，也不会失去任何东西。
为了优化器跟踪输出，一个被合并的派生表或者试图引用不会被展示位一个节点。只有它的底层表出现在一个顶级查询的计划中。
到此关于优化派生表和试图引用的说明就结束了，接下来要说明的是2.4Optimizing Subqueries with the EXISTS Strategy 使用存在策略来优化子查询。