MySQL 不相关子查询怎么执行？

1. 概述

从现存的子查询执行策略来看，半连接 (Semijoin) 加入之前，不相关子查询有两种执行策略：

策略 1，子查询物化，也就是把子查询的执行结果存入临时表，这个临时表叫作物化表。

explain select_type = SUBQUERY 就表示使用了物化策略执行子查询，如下：

+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys          | key            | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+
| 1  | PRIMARY     | city    | <null>     | ALL   | <null>                 | <null>         | <null>  | <null> | 600  |  33.33   | Using where              |
| 2  | SUBQUERY    | address | <null>     | range | PRIMARY,idx_fk_city_id | idx_fk_city_id | 2       | <null> | 9    | 100.0    | Using where; Using index |
+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+

策略 2，转换为相关子查询，explain select_type = DEPENDENT SUBQUERY，如下：

+----+--------------------+---------+------------+-----------------+------------------------+---------+---------