解读MySQL 5.6 查询优化器新功能

kider

作者：Ranma   来源：TechTarget中国

MySQL 5.6中，Oracle对子查询进行了优化处理。其中涉及到将一个子查询转变为一个半连接操作，然后将它作为另外一个连接操作来加以评估。优化器能够进行识别，即一条IN语句要求子查询从子查询表只返回一个值。在这些场景下，查询是以半连接来执行的，该操作仅返回子查询中每条记录的一个实例，而子查询则是通过记录与外部查询进行匹配的。在本文中，我们将近距离观察MySQL 5.6中新的优化器功能。

　　子查询刷新器

　　一个子查询是跟在IN或是“=”操作符后面用来连接内部查询和外部查询的。例如，当以下所有三个查询都产生相同的结果，许多DBA会认为最后一个在语言层面显得更自然。

　　1A – 无子查询情况:

SELECT DISTINCT n.* 　　FROM nation as n, 　　region AS r 　　WHERE n.region_key = r.region_key;

　　1B:

SELECT nation.* 　　FROM nation INNER JOIN region 　　WHERE nation.region_key = region.region_key;

　　2 – 有子查询情况:

SELECT * 　　FROM nation 　　WHERE region_key IN (SELECT region_key 　　FROM region 　　WHERE name = 'North America');

　　在第一个查询中，SELECT DISTINCT的使用同样是低效的，因为它要先产生所有匹配的记录，只是为了在之后用来去重。

　　半连接转换执行策略

　　如上述，优化器会识别出需要子查询的IN语句以便从区域表返回每个区域键的一个实例。这就导致了MySQL会以半连接的方式执行SELECT语句，所以全局表中每个区域只会有一个实例与记录相匹配。

　　半连接和常规连接之间存在两个非常重要的区别：

• 在半连接中，内表不会导致重复的结果。
• 此操作不会有内表中的字段添加到结果中去。
  

　　因此，半连接的结果常常是来自外表记录的一个子集。从有效性上看，半连接的优化在于有效的消除了来自内表的重复项。

　　MySQL应用了四个不同的半连接执行策略用来去重：

　　将子查询转变为一个连接，或是利用table pullout并将查询作为子查询表和外表之间的一个内连接来执行。Table pullout会为外部查询从子查询抽取出一个表。

• Duplicate Weedout：执行半连接，就如同它是一个连接并利用临时表移除了重复的记录。
• FirstMatch：为了对记录进行合并而在扫描内表，并且对于给定值群组有多个实例时，选择其一而不是将它们全部返回。这为表扫描提供了一个早期退出机制而且还消除了不必要记录的产生。
• LooseScan：利用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。
• Materialization：采用一个索引将子查询的结果存进一个临时表，并且利用这个临时表来执行一个连接。接下来利用索引来去重并在连接临时表和外表的时候进行查找。
  

　　将子查询转换为一个连接，或使用Table Pullout

　　优化MySQL的常见建议是：“如果可能的话，将子查询重写为连接”。现在已经不必如此了，因为在子查询中的字段与主要SELECT语句以及某些特定值相关的情况下，优化器是会替我们完成这一工作的。优化器会尝试为子查询中的每一满足这些要求的字段抽取出父表。

　　去重(Duplicate Weedout)

　　在去重(Duplicate Weedout)策略中，MySQL就像执行一个常规内表连接一样执行半连接，并在接下来利用一个临时表消除重复记录。当此策略需要在临时表中执行查找操作时，相较于那些常规表所消耗的成本会更低廉，因为临时表是存储在内存中的。

　　最先匹配(FirstMatch)

　　半连接的最先匹配(FirstMatch)策略执行子查询的方式与MySQL稍早版本中的IN-TO-EXISTS是非常相似的。对于外表中的每条匹配记录，MySQL都会在内表中进行匹配检查。当发现存在匹配时，它会从外表返回记录。只有在未发现匹配的情况下，引擎才会回退去扫描整个内表。

　　松散扫描(LooseScan)

　　松散扫描(LooseScan)策略采用了分组，子查询中的字段作为一个索引且外部SELECT语句可以可以与很多的内部SELECT记录相匹配。如此便会有通过索引对记录进行分组的效果。例如，以下的查询列出了所有在城市表里已有记录的国家：

select * 　　from Country 　　where Country.code in (select country_code 　　from City)

　　每个国家的所有城市会在一起进行分组，所以不同国家间的城市不会有混在一起的情况发生。这就使得查询引擎很容易就从每个分组里挑选出首个城市，接着就与其对应的国家进行连接从而为各个单独国家生成一个清单。

　　具体化(Materialization)

　　这一策略是用在非相关子查询上的，即可以独立运行于主查询之外。具体化(Materialization)利用子查询的可能值对临时表进行填充，并紧接着和外查询的结果一起执行一个连接。

　　此连接可以从两个方向加以完成，一是由具体化表到外部查询值，或是反其道而行之。

　　第一个方向称作具体化扫描，需要在具体化表上做一个完整扫描。后者则称为具体化查找，而在具体化表中查找一个匹配最快速的方法就是在其主键上执行一个查找。

　　结论

　　在本文中，我们探讨了MySQL 5.6的优化器在处理包含子查询的查询中所使用的策略。在所有论述过的策略中，包含有子查询的SELECT语句是作为半连接执行的，所以子查询中每条记录以最少的实例可能进行返回。这样做可以使对表的扫描最小化并且有利于对内外表的SELECT记录进行匹配。