sql - MySQL 8 中不完整的执行计划

Question

我想在 MySQL 8.0 中获取查询的执行计划，但它给了我一个不完整的计划。

2018 年 9 月 8 日编辑:

通过展示一个简化示例，原始查询以未检索任何行的子查询结束。似乎 MySQL 的优化器将这些查询简化到完全修剪部分查询的程度。我修改了查询以获取有关子查询的数据。这是示例:

create table branch (
  id int primary key not null,
  name varchar(30) not null
);
insert into branch (id, name) values (101, 'California');
insert into branch (id, name) values (102, 'Ohio');
insert into branch (id, name) values (103, 'Delaware');

create table account (
  id int primary key not null auto_increment,
  balance int
);
insert into account (id, balance) values (1001, 120);
insert into account (id, balance) values (1004, 500);
insert into account (id, balance) values (1005, 45);

create table transaction (
  tx_id int primary key not null auto_increment,
  account_id int not null,
  amount int not null,
  branch_id int references branch (id)
);
insert into transaction (account_id, amount, branch_id) values
  (1001,  10, 101),
  (1001, 150, 101),
  (1001, 200, 101),
  (1001, -70, 102),
  (1001, -20, 102),
  (1001,-150, 102),
  (1004,  50, 103),
  (1004, 300, 101),
  (1004, 150, 102),
  (1005, 100, 102),
  (1005, -55, 101);

现在的查询是:

explain
select *
from account a
  join transaction t4 on t4.account_id = a.id
  join branch b5 on b5.id = t4.branch_id
  join (select account_id as account_id from transaction t7 where amount > 0) t6
    on t6.account_id = a.id
  where a.balance < 7 * (
    select avg(amount) from transaction t
      join branch b on b.id = t.branch_id
      where t.account_id = a.id
        and b.name in (select name from branch b7
                       where name like '%a%')
  )
  and a.balance < 5 * (
    select max(amount)
      from transaction t2
      join branch b2 on b2.id = t2.branch_id
      where b2.name not in (select name from branch b8
                            where name like '%i%')
  );

现在显示(传统方案):

id select_type         table type   key     key_len ref rows filtered Extra
-- ------------------- ----- ------ ------- ------- --- ---- -------- -----
1  PRIMARY             a     ALL                        3    33.33    Using where
1  PRIMARY             t7    ALL                        11   9.09     Using where
1  PRIMARY             t4    ALL                        11   10       Using where
1  PRIMARY             b5    eq_ref PRIMARY 4       ... 1    100      
5  SUBQUERY            b2    ALL                        3    100      Using where
5  SUBQUERY            t2    ALL                        11   10       Using where
6  DEPENDENT SUBQUERY  b8    ALL                        3    33.33    Using where
3  DEPENDENT SUBQUERY  b7    ALL                        3    33.33    Using where
3  DEPENDENT SUBQUERY  t     ALL                        11   10       Using where
3  DEPENDENT SUBQUERY  b     eq_ref PRIMARY 4       ... 1    33.33    Using where

它现在显示除标量子查询 t6 之外的所有表的信息。它在哪里？

Answer 1

我尝试测试您的查询，但我在任何表中都有零行。 EXPLAIN 显示“Impossible WHERE noticeed after reading const tables”，这意味着没有满足查询条件的行。

在我的测试中，我看到了 t2、b2、b8、t、b、b7、t7，但没有看到 a、t4、b5、t6。如果不读取表，它似乎会从 EXPLAIN 中省略表，因为查询条件意味着读取它们没有意义，因为它们保证不匹配任何行。

我看不出这个条款有任何合乎逻辑的目的:

join (select max(account_id) as account_id from transaction t7) t6 
    on t6.account_id = a.id

如果我从查询中取出这个连接，我会得到一个没有“Impossible WHERE”注释的 EXPLAIN，并且它具有所有其他相关名称:

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                             |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+
|  1 | PRIMARY            | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 | Using where                                                       |
|  1 | PRIMARY            | t4    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | PRIMARY            | b5    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  4 | SUBQUERY           | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                                              |
|  4 | SUBQUERY           | b2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  5 | DEPENDENT SUBQUERY | b8    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                                       |
|  2 | DEPENDENT SUBQUERY | t     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                                       |
|  2 | DEPENDENT SUBQUERY | b     | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  2 | DEPENDENT SUBQUERY | b7    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; FirstMatch(b); Using join buffer (Block Nested Loop) |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+

我没有像我在你的表中猜测的那样创建任何索引，所以这个 EXPLAIN 没有显示优化。但至少所有相关名称都出现了。