MySQL Where и производительность INNER JOIN
Мне хотелось бы понять, почему следующие два запроса, которые выбирают дубликаты из таблицы companies, имеют разное время выполнения. Второй запрос с JOIN выполняется гораздо быстрее.
Запрос выполняется:
- таблица
companiesс ~200 тыс. записей - из них 195 тыс. уникальных значений столбцов
name(каждый запрос возвращает около 1500 результатов, а именно дубликатов) - в столбце
nameindexесть индексname
ГДЕ (0,31142875 с):
SELECT *
FROM `companies`
WHERE `name` IN (
SELECT `name`
FROM `companies`
GROUP BY `name`
HAVING COUNT(`name`) > 1
);
ПРИСОЕДИНЯЙТЕСЬ (0,07034850 с):
SELECT *
FROM `companies`
INNER JOIN (
SELECT `name`
FROM `companies`
GROUP BY `name`
HAVING COUNT(`name`) > 1
) AS `duplicate_names` ON `companies`.`name` = `duplicate_names`.`name`;
Обратите внимание, что подзапросы абсолютно одинаковы. Почему в этой конкретной настройке второй запрос выполняется быстрее?
Результат EXPLAIN:
ГДЕ запрос:
ПРИСОЕДИНЯЙТЕСЬ к запросу:
WHERE IN в большинстве случаев является худшим вариантом - используйте INNER JOIN или WHERE NOT EXISTS (зависит от статистики данных).
Почему в этой конкретной настройке второй запрос выполняется быстрее? WHERE IN может либо сканировать все строки, собранные в подзапросе, либо скрыто индексировать их — сортировка, которая используется во время обработки этого подзапроса, теряется из подзапроса. INNER JOIN запоминает, что набор строк отсортирован, и использует нечто похожее на слияние отсортированных списков. ПС. Это не совсем правильно, но это близко.
@Akina, есть ли способ заставить вариант WHERE вести себя так же, как второй? Добавление руководства ORDER BY name к подзапросу ничего не дает (думаю, этого и следовало ожидать, поскольку я никогда не слышал, чтобы MySQL мог определять порядок на основе этого). Может быть, принудительно индексировать? Я думаю, что объединение отсортированных списков действительно применяется только в случае JOIN ON, а не в случае WHERE IN.
Добавление имени ORDER BY вручную к подзапросу ничего не дает. Фраза «сортировка, которая используется, пока обработка этого подзапроса теряется из подзапроса» означает как сортировку, которая используется для группировки, так и явный ORDER BY. есть ли способ заставить вариант WHERE вести себя так же, как второй? Я сомневаюсь.
Какая версия MySQL?
Ответы 2
Похоже, что материализация подзапроса была бы более эффективной для первого оператора, но оптимизатор выбирает SUBQUERY стратегию выполнения.
Статистика собирается? Если нет, попробуйте собрать их: ANALYZE TABLE companies и повторно выполнить первый оператор.
Если вы используете MySQL 8.0 или новее, вы можете использовать подсказку оптимизатора:
SELECT *
FROM `companies`
WHERE `name` IN (
SELECT /*+ SUBQUERY(MATERIALIZATION) */ `name`
FROM `companies`
GROUP BY `name`
HAVING COUNT(`name`) > 1
);
На мой взгляд, выбор плана исполнения, как и предпочтение того или иного плана, сильно зависит от избирательности индекса.
Я попробовал создать тестовую таблицу с разным количеством дубликатов и сравнил планы выполнения и производительность запросов по этой таблице.
Результаты:
Для нижней части дубликатов. В таблице ОП 1500/300000=0,5%
Планируйте JOIN
-> Nested loop inner join (cost=12028 rows=14888) (actual time=10.8..11.7 rows=124 loops=1)
-> Table scan on duplicate_names (cost=3843..3950 rows=8410) (actual time=10.7..10.8 rows=62 loops=1)
-> Materialize (cost=3843..3843 rows=8410) (actual time=10.7..10.7 rows=62 loops=1)
-> Filter: (count(0) > 1) (cost=3002 rows=8410) (actual time=0.0488..10.7 rows=62 loops=1)
-> Group aggregate: count(0) (cost=3002 rows=8410) (actual time=0.045..9.72 rows=14938 loops=1)
-> Covering index scan on companies using ix_name (cost=1513 rows=14888) (actual time=0.0379..4.67 rows=15000 loops=1)
-> Index lookup on companies using ix_name (name=duplicate_names.`name`) (cost=0.443 rows=1.77) (actual time=0.014..0.0152 rows=2 loops=62)
Для IN
-> Filter: <in_optimizer>(companies.`name`,companies.`name` in (select #2)) (cost=1513 rows=14888) (actual time=11.1..37.2 rows=124 loops=1)
-> Table scan on companies (cost=1513 rows=14888) (actual time=0.0463..6.53 rows=15000 loops=1)
-> Select #2 (subquery in condition; run only once)
-> Filter: ((companies.`name` = `<materialized_subquery>`.`name`)) (cost=3843..3843 rows=1) (actual time=0.00161..0.00161 rows=0.00827 loops=15001)
-> Limit: 1 row(s) (cost=3843..3843 rows=1) (actual time=0.00146..0.00146 rows=0.00827 loops=15001)
-> Index lookup on <materialized_subquery> using <auto_distinct_key> (name=companies.`name`) (actual time=0.00132..0.00132 rows=0.00827 loops=15001)
-> Materialize with deduplication (cost=3843..3843 rows=8410) (actual time=10.9..10.9 rows=62 loops=1)
-> Filter: (count(0) > 1) (cost=3002 rows=8410) (actual time=0.0351..10.8 rows=62 loops=1)
-> Group aggregate: count(0) (cost=3002 rows=8410) (actual time=0.0314..9.89 rows=14938 loops=1)
-> Covering index scan on companies using ix_name (cost=1513 rows=14888) (actual time=0.0265..4.67 rows=15000 loops=1)
Хотя часть повторяющихся имен велика, план другой.
ПРИСОЕДИНИТЬСЯ
EXPLAIN
-> Nested loop inner join (cost=13500 rows=5000) (actual time=10.5..31.4 rows=4972 loops=1)
-> Table scan on companies (cost=500 rows=5000) (actual time=0.0496..4.7 rows=5000 loops=1)
-> Covering index lookup on duplicate_names using <auto_key0> (name=companies.`name`) (cost=1001..1003 rows=10) (actual time=0.0046..0.00508 rows=0.994 loops=5000)
-> Materialize (cost=1000..1000 rows=1) (actual time=10.3..10.3 rows=967 loops=1)
-> Filter: (count(0) > 1) (cost=1000 rows=1) (actual time=0.0435..5.78 rows=967 loops=1)
-> Group aggregate: count(0) (cost=1000 rows=1) (actual time=0.0396..5.51 rows=995 loops=1)
-> Covering index scan on companies using ix_name (cost=500 rows=5000) (actual time=0.0329..2.33 rows=5000 loops=1)
В
EXPLAIN
-> Filter: <in_optimizer>(companies.`name`,companies.`name` in (select #2)) (cost=500 rows=5000) (actual time=8.01..28 rows=4972 loops=1)
-> Table scan on companies (cost=500 rows=5000) (actual time=0.0621..3.37 rows=5000 loops=1)
-> Select #2 (subquery in condition; run only once)
-> Filter: ((companies.`name` = `<materialized_subquery>`.`name`)) (cost=1000..1000 rows=1) (actual time=0.00407..0.00407 rows=0.994 loops=4994)
-> Limit: 1 row(s) (cost=1000..1000 rows=1) (actual time=0.00313..0.00313 rows=0.994 loops=4994)
-> Index lookup on <materialized_subquery> using <auto_distinct_key> (name=companies.`name`) (actual time=0.0029..0.0029 rows=0.994 loops=4994)
-> Materialize with deduplication (cost=1000..1000 rows=1) (actual time=7.79..7.79 rows=967 loops=1)
-> Filter: (count(0) > 1) (cost=1000 rows=1) (actual time=0.107..7.02 rows=967 loops=1)
-> Group aggregate: count(0) (cost=1000 rows=1) (actual time=0.103..6.82 rows=995 loops=1)
-> Covering index scan on companies using ix_name (cost=500 rows=5000) (actual time=0.0936..3.33 rows=5000 loops=1)
Время выполнения запроса зависит и от других факторов, особенно для такой маленькой таблицы в 5000 строк. Но, тем не менее, предположение о том, что тот или иной план выполнения предпочтительнее избирательности данных, подтверждается.
Тестовая таблица
create table companies (id int primary key auto_increment
, name varchar(50) not null,Address varchar(100)
,LegalName varchar(100));
create index ix_name on companies (name);
Низкая производительность связана с отсутствием использования индекса в таблице PRIMARY Companies в первом запросе. Он даже не позволит вам принудительно использовать индекс, если индекс не покрывает внешний запрос.