Наталья Кайда 17 июня 2024

🎓 Продвинутый SQL: секреты профессионалов

Рассказываем о сложных стратегиях оптимизации запросов, продвинутых типах соединений и тонкостях использования SELECT.
🎓 Продвинутый SQL: секреты профессионалов

Знание изложенных ниже методов работы позволит вам:

  • Составлять сложные и эффективные запросы.
  • Оптимизировать производительность базы данных.

Продвинутые техники оптимизации запросов

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

1. План выполнения запроса

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

  • EXPLAIN — предоставляет информацию о том, как будет выполняться запрос, позволяя выявлять неэффективные участки:
        EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;
  • ANALYZE — при использовании вместе с EXPLAIN выполняет запрос и предоставляет статистику по времени выполнения. Это помогает оценить производительность запроса:
        EXPLAIN ANALYZE SELECT column1, column2 FROM table_name WHERE condition;
💻 Библиотека программиста
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

2. Оптимизация подзапросов

В некоторых случаях подзапросы можно заменить на более эффективные соединения или обобщенные табличные выражения с оператором WITH.

  • Замена подзапросов соединениями:
        -- Подзапрос
SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);

-- Эквивалентное соединение
SELECT table1.* FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;
  • Использование обобщенных табличных выражений:
        WITH CTE AS (
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM CTE WHERE another_condition;

3. Стратегии индексирования

Продвинутые стратегии индексирования включают использование составных индексов и покрывающих индексов.

  • Составной индекс включаeт несколько столбцов и может ускорить запросы, использующие фильтрацию по этим столбцам:
        CREATE INDEX idx_composite ON table_name (column1, column2);
  • Покрывающий индекс включает все столбцы, извлекаемые запросом, и может значительно улучшить производительность:
        CREATE INDEX idx_covering ON table_name (column1, column2, column3);

4. Партиционирование (секционирование)

Партиционирование (разделение большой таблицы на более мелкие) улучшает производительность (за счет сокращения объема данных, который необходимо сканировать), а также упрощает управление данными, например, архивирование или удаление.

  • Диапазонное секционирование эффективно для данных с временным или последовательным ключом (даты или номера заказов):
        CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    ...
) PARTITION BY RANGE (order_date) (
    PARTITION p0 VALUES LESS THAN ('2024-01-01'),
    PARTITION p1 VALUES LESS THAN ('2025-01-01'),
    ...
);
  • Секционирование по хэшу распределяет данные по указанному количеству партиций, используя хеш-функцию для достижения равномерности, что может улучшить производительность при вставке и чтении:
        CREATE TABLE users (
    user_id INT,
    username VARCHAR(255),
    ...
) PARTITION BY HASH(user_id) PARTITIONS 4;
  • Секционирование по списку применяется, когда значения столбца принадлежат определенным категориям или группам:
        CREATE TABLE sales (
    sale_id INT,
    region VARCHAR(255),
    ...
) PARTITION BY LIST (region) (
    PARTITION p0 VALUES IN ('North', 'South'),
    PARTITION p1 VALUES IN ('East', 'West')
);

5. Материализованные представления

Материализованные представления физически хранят результат выполнения запроса и могут периодически обновляться. Это улучшает производительность для сложных запросов, которые выполняются часто.

  • Создание материализованного представления:
        CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;
  • Обновление материализованного представления:
        REFRESH MATERIALIZED VIEW sales_summary;

Материализованные представления в MySQL

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

С использованием таблиц и запланированных обновлений

Этот подход заключается в создании таблицы, хранящей результаты запроса, и ее периодическом обновлении с помощью запланированных событий (cron-заданий).

Сначала создайте таблицу для хранения результатов:

        CREATE TABLE MaterializedActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';

Используйте запланированное событие для периодического обновления таблицы. В этом примере используется событие MySQL для обновления таблицы каждый час:

        CREATE EVENT UpdateMaterializedActiveCustomers
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM MaterializedActiveCustomers;
    INSERT INTO MaterializedActiveCustomers
    SELECT CustomerID, CustomerName, ContactName, Country
    FROM Customers
    WHERE Status = 'Active';
END;

Это событие каждый час очищает и заново заполняет таблицу MaterializedActiveCustomers последними активными клиентами.

С использованием триггеров

При этом подходе для синхронизации таблицы-представления с основными таблицами используются триггеры. По сравнению с первым методом этот способ сложнее, и к тому же менее эффективен для больших наборов данных.

Сначала создайте таблицу:

        CREATE TABLE MaterializedActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';

Создайте триггеры для обновления материализованной таблицы:

        DELIMITER //

CREATE TRIGGER after_customer_insert
AFTER INSERT ON Customers
FOR EACH ROW
BEGIN
    IF NEW.Status = 'Active' THEN
        INSERT INTO MaterializedActiveCustomers (CustomerID, CustomerName, ContactName, Country)
        VALUES (NEW.CustomerID, NEW.CustomerName, NEW.ContactName, NEW.Country);
    END IF;
END //

CREATE TRIGGER after_customer_update
AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN
    IF OLD.Status = 'Active' AND NEW.Status != 'Active' THEN
        DELETE FROM MaterializedActiveCustomers WHERE CustomerID = OLD.CustomerID;
    ELSEIF NEW.Status = 'Active' THEN
        REPLACE INTO MaterializedActiveCustomers (CustomerID, CustomerName, ContactName, Country)
        VALUES (NEW.CustomerID, NEW.CustomerName, NEW.ContactName, NEW.Country);
    END IF;
END //

CREATE TRIGGER after_customer_delete
AFTER DELETE ON Customers
FOR EACH ROW
BEGIN
    DELETE FROM MaterializedActiveCustomers WHERE CustomerID = OLD.CustomerID;
END //

DELIMITER ;

Эти триггеры обеспечат актуальность таблицы MaterializedActiveCustomers при изменениях в таблице Customers.

Продвинутые техники использования соединений

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

1. Самосоединения

Такие соединения позволяют объединить строки из одной и той же таблицы, используя разные псевдонимы. В этом примере извлекаются сотрудники и имена их менеджеров:

        SELECT a.employee_id, a.name, b.name AS manager_name
FROM employees a
INNER JOIN employees b ON a.manager_id = b.employee_id;

2. Латеральные соединения

Ключевое слово LATERAL позволяет подзапросам ссылаться на столбцы из предыдущих таблиц, указанных в FROM. В этом примере для каждой строки из table1 выполняется подзапрос, который извлекает одну строку из table2, соответствующую условию b.column1 = a.column1, отсортированную по b.column2 в порядке убывания:

        SELECT a.*, b.*
FROM table1 a
LEFT JOIN LATERAL (
    SELECT *
    FROM table2 b
    WHERE b.column1 = a.column1
    ORDER BY b.column2 DESC
    LIMIT 1
) b ON TRUE;

3. Полные внешние соединения с COALESCE

Эта техника позволяет избежать получения NULL значений в результате полного внешнего соединения. Функция COALESCE возвращает первое ненулевое значение из списка. В данном случае, если a.column1 не является NULL, возвращается его значение, иначе возвращается b.column1:

        SELECT COALESCE(a.column1, b.column1) AS column1, a.column2, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.column1 = b.column1;

4. Продвинутые фильтры соединений

Для более точной фильтрации результатов в соединениях можно применять сложные условия. В этом примере соединение производится с дополнительным условием на a.date_column, что позволяет отфильтровать результаты по диапазону дат:

        SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.column1 = b.column1 AND a.date_column BETWEEN '2023-01-01' AND '2023-12-31';

5. Антисоединения и полусоединения

Эти типы соединений полезны для запросов исключения и включения соответственно.

В этом примере антисоединение извлекает строки из левой таблицы, которые не имеют соответствующих строк в правой таблице:

        SELECT a.*
FROM table1 a
LEFT JOIN table2 b ON a.column1 = b.column1
WHERE b.column1 IS NULL;

А здесь полусоединение извлекает строки из левой таблицы, для которых существует одна или более соответствующих строк в правой таблице:

        SELECT a.*
FROM table1 a
WHERE EXISTS (SELECT 1 FROM table2 b WHERE a.column1 = b.column1);

Продвинутые техники использования SELECT

Возможности оператора SELECT легко расширить дополнительными функциями — это помогает решать самые сложные задачи по извлечению данных.

1. Оконные функции

Оконные функции выполняют вычисления по набору строк таблицы, связанных с текущей строкой, и предоставляют мощные аналитические возможности.

  • Нумерация строк:
        SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table_name;
  • Накопительный итог:
        SELECT column1, column2, SUM(column2) OVER (ORDER BY column1) AS running_total
FROM table_name;
  • Ранжирование:
        SELECT column1, column2, RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank
FROM table_name;
  • Скользящее среднее:
        SELECT column1, column2, AVG(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM table_name;

2. Рекурсивные обобщенные табличные выражения

Такие ОТВ позволяют выполнять рекурсивные запросы, что полезно при работе с иерархическими данными:

        WITH RECURSIVE cte AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
    UNION ALL
    SELECT t.column1, t.column2
    FROM table_name t
    INNER JOIN cte ON t.column1 = cte.column1
)
SELECT * FROM cte;

3. JSON функции

Современные СУБД часто включают функции, позволяющие хранить и запрашивать JSON документы.

  • Извлечение значений из JSON:
        SELECT json_column->>'key' AS value
FROM table_name;
  • Агрегация в JSON:
        SELECT json_agg(row_to_json(t))
FROM (SELECT column1, column2 FROM table_name) t;
  • Обновление JSON данных:
        UPDATE table_name
SET json_column = jsonb_set(json_column, '{key}', '"new_value"', true)
WHERE condition;

4. Пивотирование данных

Пивотирование преобразует строки в столбцы, пoзволяя реорганизовать и суммировать данные для составления отчетов. Для пивотирования используют CASE выражения:

        SELECT
    category,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN year = 2022 THEN sales ELSE 0 END) AS sales_2022
FROM sales_data
GROUP BY category;

5. Динамический SQL

Динамический SQL позволяет на лету конструировать SQL выражения для выполнения сложных запросов.

  • Выполнение динамического SQL:
        EXECUTE 'SELECT * FROM ' || table_name || ' WHERE ' || condi
  • Использование подготовленных выражений:
        PREPARE stmt AS SELECT * FROM table_name WHERE column1 = $1;
EXECUTE stmt('value

Подведем итоги

Продвинутые SQL-техники позволяют:

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

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

***

При подготовке статьи использовалась публикация Advanced SQL: Mastering Query Optimization and Complex Joins.

МЕРОПРИЯТИЯ

ЕВРАЗ 3.0
27 ноября Москва Онлайн Бесплатно

Совкомбанк SecureHack
06 декабря Онлайн Бесплатно

Хакатон Норникеля:Интеллектуальные горизонты
02 декабря Онлайн Бесплатно

Angular meetup #23
28 ноября Москва Онлайн Бесплатно

Комментарии

ВАКАНСИИ

Добавить вакансию
Go-разработчик
по итогам собеседования

ЛУЧШИЕ СТАТЬИ ПО ТЕМЕ