Учет производительности с предложением WITH (CTE) и оператором IN
Раньше я использовал отдельный код для MySQL и SQLite при использовании предложения WITH и оператора IN. Я использую IN (TABLE cte) в MySQL и IN cte в SQLite.
Увидев такие обманы, как this и this, у меня появилось некоторое представление о том, как использовать предложение WITH и оператор IN переносимо - используйте предложение SELECT. Вот что я делаю прямо сейчас:
MySQL:
WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN (TABLE cte))
ORDER BY column3
SQLite:
WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN cte)
ORDER BY column3
Однако меня немного беспокоит производительность. Если я сделаю следующее:
Портативный:
WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN (SELECT * FROM cte))
ORDER BY column3
Вопрос 1: Будет ли третье предложение select (такое простое, как IN (SELECT * FROM cte)) обременительным для выполнения, если cte было частью результата предложения WITH? Особенно, когда Table2 очень большой?
Вопрос 2. Применяли ли они какую-либо оптимизацию для MySQL и SQLite?
@Бармар Признателен. Я не очень хорошо разбираюсь в программировании баз данных, не могли бы вы дать полный ответ?
Привет! Если вас беспокоит производительность, я бы просто выполнил эти запросы и посмотрел, что произойдет. Посмотрите на планы EXPLAIN, чтобы увидеть, как они на самом деле выполняются. Поскольку план выполнения (и, следовательно, производительность) может меняться в зависимости от объемов данных, индексации и т. д., рассмотрение базового SQL при рассмотрении производительности вряд ли даст много результатов.
Просто сделайте это, как присоединение к реальному столу. В этом вопросе нет ничего конкретного для CTE.
@Бармар, я сделал это. Хотя я видел выбор строк из «продукта» таблиц в своей книге по теории ИТ, я никогда не писал никаких предложений JOIN. Спасибо!
Ответы 1
Если вам нужна переносимость и производительность, не используйте CTE, IN ( SELECT ... ) или «конструкторы строк». Это, вероятно, имеет лучшую мобильность и производительность:
SELECT table1.*
FROM Table2
JOIN table1 USING (column1, column2)
ORDER BY table1.column3
Если USING не обрабатывается везде, скажем
ON (table1.column1 = table2.column1
AND table1.column2 = table2.column2)
Благодарю за ваш ответ. MRE слишком упрощен по сравнению с тем, что есть в моей производственной среде. Я приму этот ответ, как только смогу его протестировать и развернуть.
Иногда упрощение запроса приводит к неактуальному решению. Надеюсь, мое предложение все равно сработает.
Самая большая проблема с моим упрощением заключалась в том, что были дополнительные строки из Table2, которые нельзя соединить с Table1, я решаю эту проблему с помощью LEFT JOIN.
ПРИСОЕДИНЯЙТЕСЬ к CTE вместо использования
IN.