Я хотел получить план выполнения запроса в MySQL 8.0, но он дает мне план неполный.
Отредактировано 8 сентября 2018 г .:
После попытки показать упрощенный пример исходный запрос закончился подзапросами, которые не извлекали ни одной строки. Кажется, что оптимизатор 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
. Где это находится?
Было бы полезно, если бы вы включили вывод SHOW CREATE TABLE
для каждой таблицы, по крайней мере, для столбцов, указанных в запросе. Я догадался, но было бы полезно увидеть ваше.
Я пробовал протестировать ваш запрос, но ни в одной из таблиц у меня не было строк. EXPLAIN показывает «Невозможно ГДЕ замечено после чтения константных таблиц», что означает, что нет строк, удовлетворяющих условиям запроса.
В моем тесте я вижу 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
Если я исключу это соединение из запроса, я получу EXPLAIN без примечания «Impossible WHERE», и у него будут все другие имена корреляции:
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+
| 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 не показывает оптимизации. Но по крайней мере появляются все корреляционные имена.
Спасибо. Ваш ответ дал мне ключ к разгадке. Стремясь показать упрощенный пример, я получил подзапросы, которые не извлекали ни одной строки. Кажется, что оптимизатор MySQL упрощает эти запросы до такой степени, что полностью удаляет части запроса.
Как вы получаете ОБЪЯСНЕНИЕ? В клиенте
mysql
? Или в другом клиенте вроде MySQL Workbench?