mysql - 应该使用哪个查询？从 MySQL Explain 推导

Question

O'reilly Optimizing SQL Statments Book的Explaining MySQL Explain章节，最后有这个问题。

The following is an example of a business need that retrieves orphaned parent records in a parent/child relationship. This SQL query can be written in three different ways. While the output produces the same results, the QEP shows three different paths.

mysql> EXPLAIN SELECT p.*
    -> FROM parent p
    -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 160
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: index_subquery
possible_keys: parent_id
          key: parent_id
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)



mysql> EXPLAIN SELECT p.*
    -> FROM parent p
    -> LEFT JOIN child c ON p.id = c.parent_id
    -> WHERE c.child_id IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 160
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: parent_id
          key: parent_id
      key_len: 4
          ref: test.p.id
         rows: 1
        Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)



mysql> EXPLAIN SELECT p.*
    -> FROM parent p
    -> WHERE NOT EXISTS
    -> SELECT parent_id FROM child c WHERE c.parent_id = p.id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 160
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: ref
possible_keys: parent_id
          key: parent_id
      key_len: 4
          ref: test.p.id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

Which is best? Will data growth over time cause a different QEP to perform better?

据我所知，书上或网上都没有答案。

Answer 1

有一个old article from 2009我在 stackoverflow 上看到过很多次链接。那里的测试表明，NOT EXISTS查询比其他两个查询(LEFT JOIN 和 NOT IN)慢 27%(实际上是 26%)。

但是，优化器已经从一个版本到另一个版本进行了改进。完美的优化器会为所有三个查询创建相同的执行计划。但只要优化器不完美，“哪个查询更快？”的答案就会消失。可以取决于实际设置(包括版本、设置和数据)。

我过去做过类似的测试，我只记得 LEFT JOIN从来没有比任何其他方法慢得多。但出于好奇，我刚刚使用默认设置在 MariaDB 10.3.13 可移植 Windows 版本上创建了一个新测试。

虚拟数据:

set @parents = 1000;

drop table if exists parent;
create table parent(
    parent_id mediumint unsigned primary key
);
insert into parent(parent_id)
    select seq
    from seq_1_to_1000000
    where seq <= @parents
;

drop table if exists child;
create table child(
    child_id mediumint unsigned primary key,
    parent_id mediumint unsigned not null,
    index (parent_id)
);
insert into child(child_id, parent_id)
    select seq as child_id
    , floor(rand(1)*@parents)+1 as parent_id
    from seq_1_to_1000000
;

不在:

set @start = TIME(SYSDATE(6));

select count(*) into @cnt
from parent p
where p.parent_id not in (select parent_id from child c);

select @cnt, TIMEDIFF(TIME(SYSDATE(6)), @start);

左连接:

set @start = TIME(SYSDATE(6));

select count(*) into @cnt
from parent p
left join child c on c.parent_id = p.parent_id
where c.parent_id is null;

select @cnt, TIMEDIFF(TIME(SYSDATE(6)), @start);

不存在:

set @start = TIME(SYSDATE(6));

select count(*) into @cnt
from parent p
where not exists (
    select *
    from child c
    where c.parent_id = p.parent_id
);

select @cnt, TIMEDIFF(TIME(SYSDATE(6)), @start);

以毫秒为单位的执行时间:

@parents   | 1000 | 10000 | 100000 | 1000000
-----------|------|-------|--------|--------
NOT IN     |   21 |    38 |    175 |    4459
LEFT JOIN  |   24 |    40 |    183 |    1508
NOT EXISTS |   26 |    44 |    180 |    4463

我已多次执行查询并获得最少的时间值。和 SYSDATE可能不是衡量执行时间的最佳方法 - 所以不要认为这些数字是准确的。但是，我们可以看到最多100K的父行，没有太大区别，NOT IN方法有点快。但是对于 1M 的父行，LEFT JOIN快三倍。

结论

那么答案是什么？我只能说:“LEFT JOIN”获胜。但事实是——这个测试什么也证明不了。答案是(多次):“视情况而定”。当性能很重要时，您能做的最好的事情就是使用针对真实数据的真实查询来运行您自己的测试。如果您(还)没有真实数据，您应该创建具有您期望将来拥有的数量和分布的虚拟数据。