2learn

Запросы и соединения

Что это такое

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

Почему это важно

Нормализованная схема становится пригодной к работе именно благодаря соединению. Здесь же мышление множествами расходится с обычным процедурным программированием сильнее всего: цикл по строкам с отдельным запросом на каждую даёт тот же ответ, но работает в тысячи раз медленнее, потому что база рассчитана на объединение множеств за один проход. Чтобы не получить неверных результатов, нужно понимать, что именно соединение даёт. То же понимание объясняет и неожиданную стоимость: когда обе стороны совпадают несколько раз, строки размножаются.

Ключевые идеи

Запрос — это конвейер, и порядок этапов фиксирован

Сначала строки фильтруются, потом группируются, потом к группам применяются условия, и лишь затем идут выборка и сортировка: группировка происходит после фильтрации, поэтому агрегат нельзя использовать в условии where, а сортировка идёт последней и потому не уменьшает число обработанных строк. Снимает почти всю путаницу простой приём — читайте запрос как последовательность операций над множествами, а не как сценарий.

from -> where -> group by -> having -> select -> order by -> limit

Соединение сопоставляет строки по условию и может размножить их

Внутреннее соединение оставляет только совпавшие комбинации, внешнее сохраняет несовпавшие строки с одной стороны и подставляет пустые значения с другой. Если одна строка слева совпадает с несколькими справа, в результате окажется несколько строк, и это правильно. Именно поэтому сумма по соединённому результату бывает больше суммы по исходной таблице. Такое размножение — обычное объяснение слишком большого итога.

Агрегатные функции сворачивают группы, а пустые значения требуют внимания

Подсчёт, сумма, среднее, минимум и максимум работают по группам и дают по одной строке на группу. Ловушка здесь одна — пустое значение: среднее его игнорирует, подсчёт по столбцу тоже, а подсчёт строк — нет, и сравнение с пустым значением даёт не «истину», а «неизвестно». Тихо неверных итогов не будет, если явно решить, какое поведение нужно.

Подзапросы и общие табличные выражения делают запрос читаемым

Вложенный запрос можно использовать как значение, как фильтр или как таблицу. Дайте ему имя через общее табличное выражение — и глубоко вложенная конструкция превратится в последовательность читаемых шагов; планировщик может обработать её точно так же, а сам запрос останется сопровождаемым.

Операции над множествами объединяют результаты, а не таблицы

Объединение, пересечение и разность работают с результатами двух запросов, у которых совместимы столбцы. Однако объединение ведёт себя иначе: дубликаты оно удаляет, если явно не сказано обратного, а это тихая разница и в производительности, и в корректности. Когда нужно свести строки одинаковой формы из разных источников, подходящий инструмент — именно они.

Запрос, который вы написали, и запрос, который выполняется, — не одно и то же

Планировщик переписывает запрос и переупорядочивает этапы, выбирая среди способов доступа к каждой таблице. Быстрым или медленным запрос делает статистика и индексы, а не то, как он написан, поэтому дальше и разбирают чтение плана — без него не выяснить, что произошло.

Практика

Сведите три таблицы в один отчёт

Напишите запрос, который перечисляет каждый заказ с его клиентом и числом позиций в нём: понадобятся соединения и группировка.

Готово, когда: число строк совпадает с числом заказов, а счётчик позиций — с ручным подсчётом для одного заказа.

Найдите соединение, размножающее строки

Возьмите запрос, итог которого больше ожидаемого, и объясните, какое соединение размножило строки.

Готово, когда: вы можете назвать отношение «один ко многим», ответственное за это, и описать, как агрегировать до соединения, чтобы исправить результат.

Частые ошибки

  • Цикл по строкам с запросом на каждую — стоимость растёт вместе с размером результата; исправить: выразите операцию одним соединением или запросом над множествами.
  • Уверенность, что соединение не может добавить строк — при связи «один ко многим» левая сторона размножается; исправить: агрегируйте до соединения, если левая строка должна остаться одной.
  • Игнорирование семантики пустых значений — сравнение даёт «неизвестно», а агрегат пропускает значения; исправить: обрабатывайте пустые значения явно и проверяйте запрос на них.
  • Использование объединения там, где дубликаты нужны — строки молча исчезают; исправить: берите вариант, который их сохраняет.
  • Чтение медленного запроса как проблемы написания — планировщик выбрал другой способ выполнения; исправить: сначала прочитайте план, а уже потом переписывайте.

Что читать дальше

Разобранные примеры

Файлы cookie аналитики ставятся только с вашего согласия. До вашего выбора ничего не загружается и никуда не отправляется.