同表Insert Select语句优化：子查询与JOIN方案对比分析

我需要从同一张表的现有记录插入新租户数据，新租户要继承父租户0904ba44-9d41-418a-bbd8-2c70506c3432的域名。我感觉用JOIN的方案更好，想确认这个判断对不对，以此提升自己的SQL优化能力。下面是两个可行的INSERT语句和对应的执行计划：

子查询方案 #

INSERT INTO tenant (tenant_uuid, secondary_domain)
VALUES (
  UUID_TO_BIN(UUID()),
  (SELECT secondary_domain FROM (SELECT top_level_domain FROM tenant WHERE tenant_uuid = UUID_to_bin('0904ba44-9d41-418a-bbd8-2c70506c3432')) as b ));

执行计划：

+-----+--------------+---------------+-------------+-----------+------------------------+------------+----------+----------+-------+-----------+--------+
| id  |  select_type |     table     |  partitions |    type   |      possible_keys     |     key    |  key_len |    ref   |  rows |  filtered |  Extra |
+-----+--------------+---------------+-------------+-----------+------------------------+------------+----------+----------+-------+-----------+--------+
| '1' |  'INSERT'    |  'tenant'     |  NULL       |  'ALL'    |  NULL                  |  NULL      |  NULL    |  NULL    |  NULL |  NULL     |  NULL  |
| '2' |  'SUBQUERY'  |  '<derived3>' |  NULL       |  'system' |  NULL                  |  NULL      |  NULL    |  NULL    |  '1'  |  '100.00' |  NULL  |
| '3' |  'DERIVED'   |  'tenant'     |  NULL       |  'const'  |  'PRIMARY tenant_uuid' |  'PRIMARY' |  '16'    |  'const' |  '1'  |  '100.00' |  NULL  |
+-----+--------------+---------------+-------------+-----------+------------------------+------------+----------+----------+-------+-----------+--------+

JOIN方案 #

INSERT INTO tenant (tenant_uuid, secondary_domain)
VALUES (
  UUID_TO_BIN(UUID()),
  (SELECT b.secondary_domain FROM tenant a INNER JOIN tenant b on a.tenant_uuid = b.tenant_uuid WHERE b.tenant_uuid = UUID_to_bin('0904ba44-9d41-418a-bbd8-2c70506c3432'));

执行计划：

+------+--------------+-----------+-------------+----------+------------------------+------------+----------+----------+-------+-----------+----------------+
| # id |  select_type |   table   |  partitions |   type   |      possible_keys     |     key    |  key_len |    ref   |  rows |  filtered |      Extra     |
+------+--------------+-----------+-------------+----------+------------------------+------------+----------+----------+-------+-----------+----------------+
| '1'  |  'INSERT'    |  'tenant' |  NULL       |  'ALL'   |  NULL                  |  NULL      |  NULL    |  NULL    |  NULL |  NULL     |  NULL          |
| '2'  |  'SUBQUERY'  |  'a'      |  NULL       |  'const' |  'PRIMARY tenant_uuid' |  'PRIMARY' |  '16'    |  'const' |  '1'  |  '100.00' |  'Using index' |
| '2'  |  'SUBQUERY'  |  'b'      |  NULL       |  'const' |  'PRIMARY tenant_uuid' |  'PRIMARY' |  '16'    |  'const' |  '1'  |  '100.00' |  NULL          |
+------+--------------+-----------+-------------+----------+------------------------+------------+----------+----------+-------+-----------+----------------+

方案对比与结论 #

你的直觉不正确，子查询方案的效率反而略优，原因如下：

• 执行开销：子查询仅对tenant表做了一次主键查询（id=3的DERIVED查询，type为const，直接命中主键），后续派生表的处理几乎无开销；而JOIN方案做了两次主键查询（表a和表b各一次），额外的自关联逻辑会产生不必要的微小开销。
• 逻辑合理性：这个场景只需要获取单个租户的域名，完全不需要自关联JOIN，子查询的写法更贴合需求逻辑，也更简洁。

另外，你的子查询写法可以进一步简化，去掉多余的嵌套：

INSERT INTO tenant (tenant_uuid, secondary_domain)
VALUES (
  UUID_TO_BIN(UUID()),
  (SELECT secondary_domain FROM tenant WHERE tenant_uuid = UUID_to_bin('0904ba44-9d41-418a-bbd8-2c70506c3432'))
);

简化后的版本执行计划会更高效，直接通过一次const主键查询就能拿到结果，省去了派生表的额外处理步骤。

内容的提问来源于stack exchange，提问作者williamohara