继上一篇wherefieldin(...)开篇的准备工作后，本篇正式开启子查询系列。本系列将介绍子查询的各种执行策略。该计划包括以下主题：不相关子查询（Subquery）相关子查询（DependentSubquery）嵌套循环连接（BlockedNestedLoopJoin）散列连接（HashJoin）表拉出（TablePullout）首次匹配（FirstMatch）松散扫描（LooseScan）)上面列表中的重复值消除（DuplicateWeedout）子查询物化（Materialize），从表拉出（TablePullout）开始的5种执行策略都是用Join实现的，所以嵌套循环连接和哈希连接也包含在这个系列中。子查询系列文章的主题在写作过程中可能会根据情况进行调整，也可能会插入其他不属于本系列的文章。这篇文章，我们先来看看不相关的子查询是如何执行的？本文内容基于MySQL8.0.29源码。1.概述从现有的子查询执行策略来看，在加入半连接（Semijoin）之前，对于不相关的子查询有两种执行策略：策略一，子查询物化，即将子查询的执行结果存储在临时表中，这个临时表表称为化学表。说明select_type=SUBQUERY表示使用物化策略执行子查询，如下：+----+------------+---------+----------+--------+------------------------+----------------+--------+--------+------+----------+--------------------------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+------------+--------+------------+-------+--------------------+----------------+---------+--------+------+----------+------------------------+|1|初级|城市|<空>|所有|<空>|<空>|<空>|<空>|600|33.33|使用哪里||2|地址|<空>|范围|主要，idx_fk_city_id|idx_fk_city_id|2|<空>|9|100.0|在哪里使用；使用索引|+----+------------+--------+------------+-------+-------------------------+---------------+--------+--------+------+----------+-----------------------+策略2，转为相关子查询，解释select_type=DEPENDENTSUBQUERY，如下：+----+-------------------+--------+------------+----------------+------------------------+--------+---------+--------+-----+--------+------------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+--------------------+--------+-------------+----------------+------------------------+---------+--------+--------+------+--------+-------------+|1|初级|城市|<空>|所有|<空>|<空>|<空>|<空>|600|33.33|使用哪里||2|依赖子查询|地址|<空>|唯一子查询|主要，idx_fk_city_id|初级|2|功能|1|5.0|使用where|+----+-------------------+------------+------------+----------------+--------------------+--------+--------+--------+------+------------+------------+本文要介绍的是使用物化策略的过程执行不相关的子查询。将不相关子查询转换为相关子查询的执行过程留到下一篇文章2.执行过程我们介绍的执行过程并不是整个SQL的完整执行过程，只是与子查询相关的那些步骤。在查询优化阶段，MySQL在确定要使用物化策略执行子查询后，会创建一个临时表。有关创建临时表的更多信息，稍后将单独一节。在执行阶段，server层从存储引擎中读取到主查询的第一条记录后，需要判断这条记录是否符合where条件。在判断包含子查询的where条件字段时，如果发现子查询需要物化，则执行子查询。为了描述方便，我们给包含子查询的where条件字段起一个名字：sub_field，后面需要的时候会用到。执行子查询的过程就是从存储引擎中一条一条的读取子查询表中的记录。每次读取记录时，都会将其写入临时表。子查询的记录写入临时表后，从主查询记录中获取sub_field字段值，在临时表中查找。如果找到记录，则sub_field字段条件的结果为真，否则为假。主查询的所有where条件判断完毕后，如果每个where条件都为真，则将记录返回给客户端，否则继续读取下一条记录。server层从存储引擎中读取主查询的第2~N条记录，在判断记录是否符合where条件时，可以直接使用sub_field字段值查询临时表中是否有对应的记录判断sub_field字段条件是否成立。从上面可以看出，子查询物化只会执行一次。3、创建临时表临时表是在查询优化阶段创建的，也是一个正经表。既然是正经表，就要确定它使用的是什么存储引擎。临时表会优先使用内存存储引擎。MySQL8有两个内存存储引擎：MEMORY引擎继承自5.7。8.0新增TempTable引擎。当你有选择的时候，你不得不担心，MySQL会选择哪个引擎？这个就交给我们了，我们可以通过系统变量internal_tmp_mem_storage_engine来告诉MySQL选择哪个引擎，它的可选值有TempTable（默认值），MEMORY。但是，internal_tmp_mem_storage_engine指定的引擎不一定是最终选择。有两种情况会导致临时表使用磁盘存储引擎InnoDB。两种情况如下：情况1，如果我们指定MEMORY引擎，子查询结果包含BLOB字段，临时表只能使用InnoDB引擎。为什么？因为MEMORY引擎不支持BLOB字段。情况2，如果系统变量big_tables的值为ON，并且子查询中没有指定SQL_SMALL_RESULTHint，临时表只能使用InnoDB引擎。big_tables的默认值为OFF。为什么？因为big_tables=ON就是告诉MySQL，我们要执行的SQL都是有很多记录的，临时表需要用到InnoDB引擎。但是，随着时间的推移，如果有一天我们发现有一个频繁执行的SQL，虽然使用了临时表，但是记录数比较少，使用内存存储引擎就足够了。此时，我们可以通过Hint告诉MySQL，这条SQL的结果记录数很少，MySQL可以理解并直接使用internal_tmp_mem_storage_engine中指定的内存引擎。SQL可以这样指定Hint：SELECT*FROMcityWHEREcountry_idIN(SELECTSQL_SMALL_RESULTaddress_idFROMaddressWHEREcity_id<10)ANDcity<'China'明确选择存储引擎的逻辑，下一步就是字段，会是什么临时表包含字段？这里没有复杂的逻辑要解释。临时表将只包含子查询的SELECT子句中的字段。例如，在上面的示例SQL中，临时表包含字段address_id。使用临时表来存储子查询的结果是为了提高整个SQL的执行效率。如果临时表中的记录数较多，根据主查询字段的值在临时表中查找记录的成本会比较高。因此，MySQL也会为临时表中的字段创建索引。索引有两个作用：提高临时表的查询效率。保证临时表中记录的唯一性，也就是说创建的索引是唯一索引。说完了字段，再来看看索引结构，这要看临时表最终选择了哪种存储引擎：MEMORY和TempTable引擎都使用HASH索引。InnoDB引擎，使用BTREE索引。4、自动优化为了让SQL执行的更快，MySQL对很多细节进行了优化，其中之一就是对包含子查询的where条件判断的优化。在介绍这个优化之前，我们先准备一条SQL：SELECT*FROMcityWHEREcountry_idIN(SELECTaddress_idFROMaddressWHEREcity_id<10)ANDcity<'China'主查询city表有如下记录：在示例SQLwhere条件,country_id条件包含子查询。如果不优化where条件，从city表中读取每条记录后，先获取country_id字段的值，然后在临时表中查找记录，判断条件是否成立。从上面city表中的记录可以看出，对于city_id=73~78的记录，country_id字段的值为44，从city表中读取city_id=73的记录后，得到country_id的值为44,到临时表中查找记录。不管是否找到记录，都会有一个结果。为了描述方便，我们假设结果为真。接下来从city表中读取city_id=74~78的记录，因为它们的country_id字段值都是44，其实不需要去临时表中查找记录，直接复用判断结果ofcity_id=73是的，这样可以节省多次去临时表找记录的时间。综上所述，我们总结一下MySQL的优化逻辑：对于包含子查询的where条件字段，如果连续几条记录的字段值相同，则根据where条件字段的值查找表中是否有对应的记录，本组剩余记录直接复用第一条记录的判断结果。5.手动优化上一节介绍了MySQL已经做的优化，但是还有一些优化可以做但不能做。我们在写SQL的时候，可以自己优化，也就是手动优化。我们还是用前面的例子SQL来引入手动优化：主查询有两个where条件，所以判断where条件是否成立有两个执行顺序：先判断country_id条件，如果结果为真再判断城市状况。先判断city条件，如果结果为真，再判断country_id条件。MySQL会根据where条件出现的先后顺序进行判断，也就是我们写哪个条件在前面，MySQL就判断哪个先出现。对于示例SQL，它是上面列出的第一个执行顺序。为了更好的比较两种执行顺序的优劣，我们用量化数据来说明。根据country_id字段的值在子查询临时表中查找记录的成本会高于判断city字段的值是否小于China的成本。因此，假设执行country_id条件判断的代价为5，执行city条件判断的代价为1。对于主查询中的一条记录，假设country_id条件为真，city条件不为真，两次执行顺序的代价如下：先判断country_id条件，代价为5，再判断city条件，成本为1，总成本为5+1=6。首先判断city条件，成本为1，因为条件不为真，不需要判断country_id条件，总成本为1.上面列举的场景，第一个执行顺序的开销比第二个执行顺序的开销要高，MySQL使用第一个执行顺序。MySQL没有针对这种情况进行优化。我们可以手动优化它。写SQL的时候，把包含子查询的where条件放在最后，这样MySQL就可以尽量少做无用功，让SQL执行的快一点。6、小结对于where条件中包含子查询的SQL，我们可以做一点优化，就是把这种where条件放到最后，这样MySQL就可以少做无用功，提高SQL的执行效率。本文转载自微信公众号“一树一溪”，可通过以下二维码关注。转载本文请联系艺书艺熙公众号。
