Рассказываем о сложных стратегиях оптимизации запросов, продвинутых типах соединений и тонкостях использования SELECT.
Знание изложенных ниже методов работы позволит вам:
- Составлять сложные и эффективные запросы.
- Оптимизировать производительность базы данных.
Продвинутые техники оптимизации запросов
Оптимизация SQL-запросов — первый необходимый навык для каждого администратора БД и бэкенд-разработчика. Продвинутая оптимизация выходит за рамки индексации и рефакторинга запросов — перечислим методы, которые помогут сделать запросы по-настоящему эффективными.
1. План выполнения запроса
План выполнения запроса имеет решающее значение для оптимизации — он показывает, как движок SQL выполняет запрос, и раскрывает проблемные фрагменты.
- EXPLAIN — предоставляет информацию о том, как будет выполняться запрос, позволяя выявлять неэффективные участки:
- ANALYZE — при использовании вместе с EXPLAIN выполняет запрос и предоставляет статистику по времени выполнения. Это помогает оценить производительность запроса:
💻 Библиотека программиста
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»
2. Оптимизация подзапросов
В некоторых случаях подзапросы можно заменить на более эффективные соединения или обобщенные табличные выражения с оператором WITH.
- Замена подзапросов соединениями:
- Использование обобщенных табличных выражений:
Статья по теме
📜 15 лучших практик SQL после 20 лет программирования
3. Стратегии индексирования
Продвинутые стратегии индексирования включают использование составных индексов и покрывающих индексов.
- Составной индекс включаeт несколько столбцов и может ускорить запросы, использующие фильтрацию по этим столбцам:
- Покрывающий индекс включает все столбцы, извлекаемые запросом, и может значительно улучшить производительность:
4. Партиционирование (секционирование)
Партиционирование (разделение большой таблицы на более мелкие) улучшает производительность (за счет сокращения объема данных, который необходимо сканировать), а также упрощает управление данными, например, архивирование или удаление.
- Диапазонное секционирование эффективно для данных с временным или последовательным ключом (даты или номера заказов):
- Секционирование по хэшу распределяет данные по указанному количеству партиций, используя хеш-функцию для достижения равномерности, что может улучшить производительность при вставке и чтении:
- Секционирование по списку применяется, когда значения столбца принадлежат определенным категориям или группам:
Статья по теме
🗄️ Лучшие стратегии по работе с РСУБД: индексы, транзакции и уровни изоляции
5. Материализованные представления
Материализованные представления физически хранят результат выполнения запроса и могут периодически обновляться. Это улучшает производительность для сложных запросов, которые выполняются часто.
- Создание материализованного представления:
- Обновление материализованного представления:
Материализованные представления в MySQL
MySQL не располагает встроенной поддержкой материализованных представлений, но при желании такую фичу можно реализовать вручную, двумя способами — с помощью отдельной таблицы и запланированных заданий, либо с использованием такой же таблицы и триггеров.
С использованием таблиц и запланированных обновлений
Этот подход заключается в создании таблицы, хранящей результаты запроса, и ее периодическом обновлении с помощью запланированных событий (cron-заданий).
Сначала создайте таблицу для хранения результатов:
Используйте запланированное событие для периодического обновления таблицы. В этом примере используется событие MySQL для обновления таблицы каждый час:
Это событие каждый час очищает и заново заполняет таблицу MaterializedActiveCustomers последними активными клиентами.
С использованием триггеров
При этом подходе для синхронизации таблицы-представления с основными таблицами используются триггеры. По сравнению с первым методом этот способ сложнее, и к тому же менее эффективен для больших наборов данных.
Сначала создайте таблицу:
Создайте триггеры для обновления материализованной таблицы:
Эти триггеры обеспечат актуальность таблицы MaterializedActiveCustomers при изменениях в таблице Customers.
Продвинутые техники использования соединений
Простые соединения позволяют получать данные из нескольких таблиц сразу, а продвинутые методы помогают делать более сложные выборки и производить ряд дополнительных манипуляций.
1. Самосоединения
Такие соединения позволяют объединить строки из одной и той же таблицы, используя разные псевдонимы. В этом примере извлекаются сотрудники и имена их менеджеров:
2. Латеральные соединения
Ключевое слово LATERAL позволяет подзапросам ссылаться на столбцы из предыдущих таблиц, указанных в FROM. В этом примере для каждой строки из table1 выполняется подзапрос, который извлекает одну строку из table2, соответствующую условию b.column1 = a.column1, отсортированную по b.column2 в порядке убывания:
3. Полные внешние соединения с COALESCE
Эта техника позволяет избежать получения NULL значений в результате полного внешнего соединения. Функция COALESCE возвращает первое ненулевое значение из списка. В данном случае, если a.column1 не является NULL, возвращается его значение, иначе возвращается b.column1:
4. Продвинутые фильтры соединений
Для более точной фильтрации результатов в соединениях можно применять сложные условия. В этом примере соединение производится с дополнительным условием на a.date_column, что позволяет отфильтровать результаты по диапазону дат:
5. Антисоединения и полусоединения
Эти типы соединений полезны для запросов исключения и включения соответственно.
В этом примере антисоединение извлекает строки из левой таблицы, которые не имеют соответствующих строк в правой таблице:
А здесь полусоединение извлекает строки из левой таблицы, для которых существует одна или более соответствующих строк в правой таблице:
Продвинутые техники использования SELECT
Возможности оператора SELECT легко расширить дополнительными функциями — это помогает решать самые сложные задачи по извлечению данных.
1. Оконные функции
Оконные функции выполняют вычисления по набору строк таблицы, связанных с текущей строкой, и предоставляют мощные аналитические возможности.
- Нумерация строк:
- Накопительный итог:
- Ранжирование:
- Скользящее среднее:
2. Рекурсивные обобщенные табличные выражения
Такие ОТВ позволяют выполнять рекурсивные запросы, что полезно при работе с иерархическими данными:
3. JSON функции
Современные СУБД часто включают функции, позволяющие хранить и запрашивать JSON документы.
- Извлечение значений из JSON:
- Агрегация в JSON:
- Обновление JSON данных:
4. Пивотирование данных
Пивотирование преобразует строки в столбцы, пoзволяя реорганизовать и суммировать данные для составления отчетов. Для пивотирования используют CASE выражения:
5. Динамический SQL
Динамический SQL позволяет на лету конструировать SQL выражения для выполнения сложных запросов.
- Выполнение динамического SQL:
- Использование подготовленных выражений:
Подведем итоги
Продвинутые SQL-техники позволяют:
- Решать самые сложные задачи по манипуляции и извлечению данных.
- Обеспечивать эффективную работу приложений, обрабатывающих большие объемы данных.
Понимание планов выполнения, использование продвинутых соединений, применение сложных выражений с SELECT и реализация стратегий индексации — необходимые навыки для любого специалиста, работающего с базами данных: администратора, бэкенд-разработчика, аналитика. Интеграция этих техник в рабочий процесс поможет значительно улучшить производительность и масштабируемость сложных приложений, использующих базу данных.
***
При подготовке статьи использовалась публикация Advanced SQL: Mastering Query Optimization and Complex Joins.