Ответ 1
Чтобы указать другое: имея (сложный) запрос с JOINs, SUBSELECTs, UNIONs, возможно (или нет) уменьшить его до более простого эквивалентного оператора SQL, который производит тот же результат, используя некоторые правила преобразования
То, что оптимизаторы делают для жизни (не то, что я говорю, что они всегда делают это хорошо).
Так как SQL - это язык, основанный на наборе, обычно существует несколько способов преобразования одного запроса в другой.
Подобно этому запросу:
SELECT *
FROM mytable
WHERE col1 > @value1 OR col2 < @value2
можно преобразовать в это:
SELECT *
FROM mytable
WHERE col1 > @value1
UNION
SELECT *
FROM mytable
WHERE col2 < @value2
или это:
SELECT mo.*
FROM (
SELECT id
FROM mytable
WHERE col1 > @value1
UNION
SELECT id
FROM mytable
WHERE col2 < @value2
) mi
JOIN mytable mo
ON mo.id = mi.id
которые выглядят более уродливыми, но могут дать лучшие планы выполнения.
Одна из самых распространенных вещей - замена этого запроса:
SELECT *
FROM mytable
WHERE col IN
(
SELECT othercol
FROM othertable
)
с этим:
SELECT *
FROM mytable mo
WHERE EXISTS
(
SELECT NULL
FROM othertable o
WHERE o.othercol = mo.col
)
В некоторых RDBMS (например, PostgreSQL), DISTINCT и GROUP BY используют разные планы выполнения, поэтому иногда лучше заменить один на другой:
SELECT mo.grouper,
(
SELECT SUM(col)
FROM mytable mi
WHERE mi.grouper = mo.grouper
)
FROM (
SELECT DISTINCT grouper
FROM mytable
) mo
против.
SELECT mo.grouper, SUM(col)
FROM mytable
GROUP BY
mo.grouper
В PostgreSQL, DISTINCT сортируется и GROUP BY хэши.
MySQL отсутствует FULL OUTER JOIN, поэтому его можно переписать следующим образом:
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.id = t2.id
против.
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT JOIN
table2 t2
ON t1.id = t2.id
UNION ALL
SELECT NULL, t2.col2
FROM table1 t1
RIGHT JOIN
table2 t2
ON t1.id = t2.id
WHERE t1.id IS NULL
но см. эту статью в своем блоге о том, как сделать это более эффективно в MySQL:
Этот иерархический запрос в Oracle:
SELECT DISTINCT(animal_id) AS animal_id
FROM animal
START WITH
animal_id = :id
CONNECT BY
PRIOR animal_id IN (father, mother)
ORDER BY
animal_id
можно преобразовать в это:
SELECT DISTINCT(animal_id) AS animal_id
FROM (
SELECT 0 AS gender, animal_id, father AS parent
FROM animal
UNION ALL
SELECT 1, animal_id, mother
FROM animal
)
START WITH
animal_id = :id
CONNECT BY
parent = PRIOR animal_id
ORDER BY
animal_id
последний из которых более эффективен.
См. эту статью в своем блоге для деталей плана выполнения:
Чтобы найти все диапазоны, которые перекрывают данный диапазон, вы можете использовать следующий запрос:
SELECT *
FROM ranges
WHERE end_date >= @start
AND start_date <= @end
но в SQL Server этот более сложный запрос дает одни и те же результаты быстрее:
SELECT *
FROM ranges
WHERE (start_date > @start AND start_date <= @end)
OR (@start BETWEEN start_date AND end_date)
и верьте или нет, у меня также есть статья в моем блоге:
SQL Server также не хватает эффективного способа создания совокупных агрегатов, поэтому этот запрос:
SELECT mi.id, SUM(mo.value) AS running_sum
FROM mytable mi
JOIN mytable mo
ON mo.id <= mi.id
GROUP BY
mi.id
можно более эффективно переписать с помощью, помогите мне, курсоры (вы слышали меня правильно: cursors, more efficiently и SQL Server в одном предложении).
Посмотрите эту статью в своем блоге о том, как это сделать:
Существует определенный тип запроса, который обычно встречается в финансовых приложениях, которые ищут эффективную ставку для валюты, например, в Oracle:
SELECT TO_CHAR(SUM(xac_amount * rte_rate), 'FM999G999G999G999G999G999D999999')
FROM t_transaction x
JOIN t_rate r
ON (rte_currency, rte_date) IN
(
SELECT xac_currency, MAX(rte_date)
FROM t_rate
WHERE rte_currency = xac_currency
AND rte_date <= xac_date
)
Этот запрос может быть сильно переписан для использования условия равенства, которое позволяет HASH JOIN вместо NESTED LOOPS:
WITH v_rate AS
(
SELECT cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate
FROM (
SELECT cur_id, dte_date,
(
SELECT MAX(rte_date)
FROM t_rate ri
WHERE rte_currency = cur_id
AND rte_date <= dte_date
) AS rte_effdate
FROM (
SELECT (
SELECT MAX(rte_date)
FROM t_rate
) - level + 1 AS dte_date
FROM dual
CONNECT BY
level <=
(
SELECT MAX(rte_date) - MIN(rte_date)
FROM t_rate
)
) v_date,
(
SELECT 1 AS cur_id
FROM dual
UNION ALL
SELECT 2 AS cur_id
FROM dual
) v_currency
) v_eff
LEFT JOIN
t_rate
ON rte_currency = cur_id
AND rte_date = rte_effdate
)
SELECT TO_CHAR(SUM(xac_amount * eff_rate), 'FM999G999G999G999G999G999D999999')
FROM (
SELECT xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt
FROM t_transaction x
GROUP BY
xac_currency, TRUNC(xac_date)
)
JOIN v_rate
ON eff_currency = xac_currency
AND eff_date = xac_date
Несмотря на громоздку, последний запрос 6 раз быстрее.
Основная идея здесь заключается в замене <= на =, что требует построения таблицы календаря в памяти. до JOIN с.