Почему CTE быстрее, чем соединения

Я читал, что производительность оператора With в некоторых случаях намного выше, чем у соединений. Однако при присоединении к varchars (обычно я этого избегаю) я увидел значительное улучшение скорости, когда заменил соединение на With.

Например, у меня есть три таблицы, к которым я хочу присоединиться: Customer, CustomerNickname, Address (не настоящий пример, но подойдет для вопроса)

Обычно я использую следующий запрос для получения данных:

select * from customer c
inner join address a on c.addressid = a.id
left join CustomerNickname cn on cn.originalname = c.name --name and original name are varchars

Если в этих таблицах много данных, производительность будет очень низкой из-за объединения varchar. Однако, если я заменю запрос на:

  with Nicknames as (
         select * from CustomerNickname
   )
    select * from customer c
    inner join address a on c.addressid = a.id
    left join Nicknames cn on cn.originalname = c.name

производительность значительно улучшится. В моем примере (здесь я его не добавлял, потому что таблиц слишком много) запрос ушел с минуты на секунду. Кроме того, я заметил в плане выполнения исходного запроса, что есть оператор SORT со стоимостью 95%. Откуда это взялось?

Итак, два вопроса:

  • Почему With намного быстрее?

  • Откуда в плане выполнения берется оператор SORT.


sql join common-table-expression with-statement
person sTodorov    schedule 29.01.2014    source источник
comment
Покажите планы выполнения для обоих, а также СТАТИСТИКУ ввода-вывода, и тогда будет намного проще сказать вам, почему один из них быстрее другого или похож. Потому что часто все сводится к тому, как оптимизатор может сократить данные для перемещения между операциями.   -  person Allan S. Hansen    schedule 29.01.2014
comment
@AllanS.Hansen, к сожалению, я не могу включить это, потому что у меня больше нет доступа к запросу/базе данных.   -  person sTodorov    schedule 29.01.2014

Ответы (1)


arrow_upward
0
arrow_downward

Запрос присоединяется к varchar, поэтому будет выполняться операция сканирования. это довольно дорого даже с индексами. Почему Mysql использует полное сканирование таблицы для таблицы A при присоединении к другой таблице B? Я подозреваю, что CTE хранит dynaset в оперативной памяти на сервере и поэтому работает намного быстрее. (весь набор результатов для CTE загружается с диска в оперативную память в несортированном виде).

person Community    schedule 26.09.2018