Общие правила упрощения операторов SQL

Ответ 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:

• Эмуляция FULL OUTER JOIN в 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

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 в одном предложении).

Посмотрите эту статью в своем блоге о том, как это сделать:

• Временные рамки сглаживания: 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 с.

• Преобразование валют

Ответ 2

Здесь несколько из работы с Oracle 8 и 9 (разумеется, иногда выполнение противоположного может сделать запрос проще или быстрее):

Скобки могут быть удалены, если они не используются для переопределения приоритета оператора. Простым примером является то, что все логические операторы в вашем предложении where одинаковы: where ((a or b) or c) эквивалентно where a or b or c.

Подпроцесс часто (если не всегда) может быть объединен с основным запросом, чтобы упростить его. По моему опыту, это часто значительно улучшает производительность:

select foo.a,
       bar.a
  from foomatic  foo,
       bartastic bar
 where foo.id = bar.id and
       bar.id = (
         select ban.id
           from bantabulous ban
          where ban.bandana = 42
       )
;

эквивалентно

select foo.a,
       bar.a
  from foomatic    foo,
       bartastic   bar,
       bantabulous ban
 where foo.id = bar.id and
       bar.id = ban.id and
       ban.bandana = 42
;

Использование ANSI-соединений отделяет много логики "кодовой обезьяны" от действительно интересных частей предложения where: предыдущий запрос эквивалентен

select foo.a,
       bar.a
  from foomatic    foo
  join bartastic   bar on bar.id = foo.id
  join bantabulous ban on ban.id = bar.id
 where ban.bandana = 42
;

Если вы хотите проверить наличие строки, не используйте count (*), вместо этого используйте либо rownum = 1, либо поместите запрос в предложение where exists, чтобы извлечь только одну строку вместо всех.

Ответ 3

• Я полагаю, что очевидным является поиск любых курсоров, которые могут быть заменены операцией SQL "Set".
• Далее в моем списке найдите любые коррелированные подзапросы, которые можно переписать в виде некоррелированного запроса
• В длинных хранимых процедурах разворачивайте отдельные инструкции SQL в свои собственные хранимые процедуры. Таким образом, они получат собственный план кеширования запросов.
• Посмотрите на транзакции, которые могут сократить область действия. Я регулярно нахожу заявления внутри транзакции, которая может безопасно находиться снаружи.
• Подвыборки часто могут быть переписаны как прямое соединение (современные оптимизаторы хорошо разбираются в простых).

Как отметил @Quassnoi, оптимизатор часто делает хорошую работу. Один из способов помочь ему в том, чтобы обновить индексы и статистику и найти подходящие индексы для рабочей нагрузки вашего запроса.

Ответ 4

Мне нравится заменять все подзадачи на запрос соединения.

Это очевидно:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

по

SELECT  mo.*
FROM    mytable mo inner join othertable o on o.othercol = mo.col

И это под оценкой:

SELECT  *
FROM    mytable mo
WHERE   NOT EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

по

SELECT  mo.*
FROM    mytable mo left outer join othertable o on o.othercol = mo.col
WHERE   o.othercol is null

Это может помочь СУБД выбрать хороший план выполнения в большом запросе.

Ответ 5

Мне нравится каждый в команде следовать ряду стандартов, чтобы сделать код читаемым, поддерживаемым, понятным, моющимся и т.д.:)

• каждый использует один и тот же псевдоним
• нет курсоров. нет циклов
• зачем даже думать о IN, когда вы можете EXISTS
• INDENT
• Согласованность стиля кодирования

здесь есть еще несколько вещей Каковы некоторые из ваших наиболее полезных стандартов базы данных?

Ответ 6

Учитывая природу SQL, вы абсолютно должны знать о последствиях производительности любого рефакторинга. Рефакторинг приложений SQL - хороший ресурс по рефакторингу с большим вниманием к производительности (см. главу 5).

Ответ 7

Хотя упрощение может не совпадать с оптимизацией, упрощение может иметь важное значение при написании читаемого кода SQL, что в свою очередь имеет решающее значение для проверки вашего кода SQL для концептуальной корректности (а не синтаксической корректности, которую ваша среда разработки должна проверить для вас), Мне кажется, что в идеальном мире мы будем писать самый простой, читаемый код SQL, а затем оптимизатор переписывал бы тот код SQL, который будет в любой форме (возможно, более подробный), будет работать быстрее.

Я обнаружил, что мышление операторов SQL, основанных на заданной логике, очень полезно, особенно если мне нужно объединить предложения или выяснить сложное отрицание предложения where. В этом случае я использую законы булевой алгебры.

Наиболее важными для упрощения предложения where являются, вероятно, законы DeMorgan (обратите внимание, что "·" есть "AND" и "+" - "OR" ):

• NOT (x · y) = NOT x + NOT y
• NOT (x + y) = NOT x · NOT y

Это означает, что SQL:

NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2

Эти законы могут быть очень полезны для упрощения where where с множеством вложенных частей AND и OR.

Также полезно помнить, что оператор field1 IN (value1, value2, ...) эквивалентен field1 = value1 OR field1 = value2 OR .... Это позволяет отменить IN () один из двух способов:

NOT field1 IN (value1, value2)  -- for longer lists
NOT field1 = value1 AND NOT field1 = value2  -- for shorter lists

Аналогичным образом может быть рассмотрен подзапрос. Например, это отрицание where where:

NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

можно переписать как:

NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

Эти законы не говорят вам, как преобразовать SQL-запрос с использованием подзапроса в один, используя объединение, но логическая логика может помочь вам понять типы соединений и то, что должен возвращать ваш запрос. Например, с таблицами A и B, INNER JOIN похож на A AND B, a LEFT OUTER JOIN похож на (A AND NOT B) OR (A AND B), который упрощается до A OR (A AND B), а FULL OUTER JOIN - A OR (A AND B) OR B, что упрощает до A OR B.

Ответ 8

Мой подход заключается в изучении реляционной теории вообще и реляционной алгебры в частности. Затем научитесь определять конструкции, используемые в SQL, для реализации операторов из реляционной алгебры (например, универсальное квантование a.k.a.) и исчисления (например, экзистенциальная квантификация). Исход состоит в том, что SQL имеет функции, не найденные в реляционной модели, например. nulls, которые, вероятно, лучше всего реорганизуются. Рекомендуемое чтение: SQL и реляционная теория: как написать точный код SQL по C. J. Date.

В этом ключе я не уверен, что "тот факт, что большинство SUBSELECT могут быть переписаны как JOIN", представляет собой упрощение.

Возьмите этот запрос, например:

SELECT c 
  FROM T1 
 WHERE c NOT IN ( SELECT c FROM T2 );

Переписать с помощью JOIN

SELECT DISTINCT T1.c 
  FROM T1 NATURAL LEFT OUTER JOIN T2 
 WHERE T2.c IS NULL;

Соединение более подробное!

В качестве альтернативы, признать, что конструкция реализует антиоион на проекции c, например. псевдо algrbra

T1 { c } antijoin T2 { c }

Упрощение с использованием реляционных операторов:

SELECT c FROM T1 EXCEPT SELECT c FROM T2;