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

8.2.2 Optimizing Subqueries,Derived Tables,and View References 优化子查询，派生表和试图引用
8.2.2.1 通过Semi-join转变来优化子查询，派生表和试图引用
8.2.2.2 通过物化来优化子查询
8.2.2.3 优化派生表和试图引用
8.2.2.4 通过EXISTS策略来优化子查询
mysql 查询优化有不同的策略可用于评估子查询，例如对IN(或者是ANY)子查询，优化器的可选项是：
>Semi-join
>Materialization
>EXISTS strategy
对于NOT IN(或者是<>ALL)子查询，优化器可选项是：
>Materialization
>EXISTS strategy
对于派生表(在from条件中的子查询)，优化器的可选项是：
>合并派生表到外层查询块
>物化派生表到一个内部临时表
对于试图引用，优化器可选项和派生表是相同的。
下面具体讨论关于优化策略的处理。
注意：
在UPDATE和DELETE语句中用子查询去修改一个表有限制，优化器不能使用semi-join,materialization 两种策略来优化。作为一个工作区，试着重写他们通过多表UPATE和DELETE语句 然后使用一个连接来操作而不是子查询。
2.1 Optimizing Subqueries,Derived Table,and View References with Semi-Join Transformations 通过Semi-join(半连接)转变来优化子查询，派生表和试图引用.
这部分将描述关于优化器选择semi-join策略来提高子查询的执行效果。
对于使用inner join连接的两张表，连接会返回第一张表中的一行数据很多次，具体由其和第二张表中匹配的行数来决定。但是问题是，信息的关键是是否是一个匹配，而不是一批量的匹配。假设存在表名为class，roster的表，列出有爵士鼓课程的各个班级和班级花名册(在每一个班级的学生)。为了列出有学生报名的班级，你必须使用这样的连接：
SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;
然而，结果对于班级中的每个学生，都会列出班级一次。对于要查询的问题，这是一个没有必要复制的信息。
假设class_num是class表中的主键，那么我们使用SELECT DISTINCT也许可以阻止复制操作，但是对于所有行的匹配操作并无影响，只是对之后的复制操作有排除。
同样复制花费的结果能够通过使用子查询来获取：
SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);
这里，优化器能够识别IN查询条件，子查询返回的是从roster表中获取到的每一个班级数目的一个实例。在这种情况下，查询能够使用一个semi-join;也就是，一个操作，他返回了只有class表中每一行与roster表中匹配上的的一个实例。外连接和内连接的语法特别是在外部查询中是被允许的，并且表引用可以是基本表，派生表，或者是试图引用。
在mysql中，一个子查询必须满足这些准则才能被作为semi-join(半连接)来处理：
1)必须是一个in(或者是=ANY)的子查询，出现在点击的where或者是on条件中，可能作为一个条件在一个AND表达式中。例如：
SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
其中，ot_i和it_i代表了查询的外部和内部，并且oe_i和ie_i代表了查询的外部和内部的列的表达式。
2)必须是一个简单的SELECT没有UNION结构。
3)必须没有group by或者是having条件的查询。
4)必须不能隐藏着分组(不能有聚合函数)。
5)必须没有order by和limit限制。
6)查询语句在外部查询中不能有直接连接(STRAIGHT_JOIN)的连接类型。
7)必须没有直连的调节器
8)内部表和外部表中的数目必须少于在一个连接中被允许的最大数目。
子查询可能是正确的，也可能是不正确的。DISTINCT是允许的，作为一个LIMIT条件，除非查询中已经有了ORDER BY则不允许。
如果一个子查询满足先前说的准则，mysql转变其为一个半连接并且作为转变选择的花费依据策略如下：
>转变一个子查询为一个连接，或者是使用表选拔(pullout)并运行查询为一个内连接在子查询表和外部表。表选拔从子查询中选择一张表到外部表。
>重复清除：像连接一样运行半连接并且移除重复的行记录通过使用一个临时表
>第一匹配：当行对比扫描到内部表并且对于给出的值分组有多个实例，选择一个而不是返回所有。这个快捷扫描将消除不必要的行。
>松散扫描：扫描一个子查询使用一个索引使得一个单一值从子查询中的分组值中只有一个单一值。
>物化一个子查询到一个有索引的临时表用于完成一个连接，其中索引被用于移除重复。索引页能够在之后继续用于查询连接外部表和临时表时；如果不得话，将会是一个表扫描。更多信息关于materialization。请看8.2.2.2的使用materialization来优化子查询。
这些策略中的每一个都能够或者不能使用通过使用下面的optimizer_switch系统变量来标记：
1）semijoin标记用来控制semi-joins是否可用
2）如果semijoin是可用的，firstmatch,loosescan,duplicateweedout,和materialization标记能够更好的控制上述被允许的半连接策略。
3）如果重复清除半连接策略不可用，只要其他可用策略有一个可用，则不会使用该方法。
4）如果重复清除策略不可用，有时优化器可能会生成一个和优化完全不同的查询计划。这种情况是因为贪婪式搜索启发性裁剪造成的。可以通过设置optimizer_prune_level=0来避免。
这些都设置了默认值，具体请看8.9.3的优化开关。
优化器尽力来减少对试图和派生表(在from中的子查询)的处理的不同。这影响查询，其中使用了STRAIGHT_JOIN调节器并且在IN子查询中的试图能够被转换为一个半连接。下面的查询说明了这一点,因为处理的改变引起一个转变的改变，并且这是一个不同的执行策略：
CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);


SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;
优化器首先扫描试图并转换in子查询为一个半连接，然后检查是否能够合并一个试图到外部查询。因为直连调节器在外部查询中阻止了半连接，优化器拒绝合并，导致使用了一个物化的表来评估派生表。
EXPLAIN的输出如下表示使用了半连接策略：
>被半连接的表展示在外连接。对于被扩展的EXPLAIN输出，通过SHOW WARNINGS来展示重写的查询，其中展示了半连接结构。(请看8.8.3被扩展的EXPLAIN输出格式)。从这点你能得到思路关于哪张表被拉出来做半连接。如果一个子查询被转变为半连接，你能够看到子查询表示已经没有了，他的表和where条件被合并到外部查询列表和where条件中了。
>用于重复消除的临时表，被展示在Extra列的Start temporary和End temporary。没有拽出的表和在EXplAIN输出行中覆盖了Start temporary和End temporary的行数据的id到临时表。
>FirstMatch(tb1_name)在Extra列说明连接被简化
>LooseScan(m..n)在Extra列说明使用了松散扫描策略。m和n是key的一部分数据。
>临时表用于物化通过一个select_type列的值为MATERIALIZED来表示，并且表中行的数值是<subqueryN>.
到此关于2.1的半连接优化子查询，派生表和试图引用的优化就结束了，接下来我们要说的是2.2的Optimizing subqueries with Materialization.