Стратегия DuplicateWeedout

DuplicateWeedout — это стратегия выполнения для подзапросов с полусвязями.

Идея

Идея заключается в выполнении полусвязи (запрос, использующий WHERE X IN (SELECT Y FROM ...)) как обычного внутреннего соединения, а затем в удалении дублирующих комбинаций записей с помощью временной таблицы.

Предположим, у вас есть запрос, в котором вы ищете страны, в которых более 33% населения проживает в одном крупном городе:

select * 
from Country 
where 
   Country.code IN (select City.Country
                    from City 
                    where 
                      City.Population > 0.33 * Country.Population and 
                      City.Population > 1*1000*1000);

Сначала выполним обычное внутреннее соединение между таблицами City и Country.

Внутреннее соединение создаёт дубликаты. Германия встречается три раза, потому что у неё три крупных города. Теперь рассмотрим DuplicateWeedout.

Здесь видно, что для предотвращения создания нескольких записей с 'Германия' была использована временная таблица с первичным ключом.

Стратегия DuplicateWeedout в действии

Start temporary и End temporary с последней диаграммы показаны в выходных данных EXPLAIN.

explain select * from Country where Country.code IN 
  (select City.Country from City where City.Population > 0.33 * Country.Population 
   and City.Population > 1*1000*1000)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: City
         type: range
possible_keys: Population,Country
          key: Population
      key_len: 4
          ref: NULL
         rows: 238
        Extra: Using index condition; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.City.Country
         rows: 1
        Extra: Using where; End temporary
2 rows in set (0.00 sec)

Этот запрос будет читать 238 строк из таблицы City, и для каждой из них будет выполняться поиск по первичному ключу в таблице Country, что даёт ещё 238 строк. Это даёт в общей сложности 476 строк, и вам нужно добавить 238 запросов в временную таблицу (которые обычно *намного* быстрее, так как временная таблица находится в оперативной памяти).

Если мы выполним тот же EXPLAIN в MySQL, получим:

explain select * from Country where Country.code IN 
  (select City.Country from City where City.Population > 0.33 * Country.Population 
    and City.Population > 1*1000*1000)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: City
         type: index_subquery
possible_keys: Population,Country
          key: Country
      key_len: 3
          ref: func
         rows: 18
        Extra: Using where
2 rows in set (0.00 sec)

Этот план будет читать (239 + 239*18) = 4541 строк, что намного медленнее.

Справочная информация

• DuplicateWeedout показан как "Начать временную/Закончить временную" в EXPLAIN.
• Стратегия может обрабатывать коррелированные подзапросы.
• Но она не может быть применена, если подзапрос имеет осмысленные GROUP BY и/или агрегатные функции.
• DuplicateWeedout позволяет оптимизатору свободно смешивать таблицы подзапроса и таблицы родительского запроса.
• Нет отдельного флага @@optimizer_switch для DuplicateWeedout. Стратегию можно отключить, выключив все оптимизации полусвязей с помощью команды SET @@optimizer_switch='optimizer_semijoin=off'.

См. также

• Что такое MariaDB 5.3
• Карта оптимизации подзапросов