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

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

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

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

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

Оптимизация 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.