Хочешь уверенно проходить IT-интервью?

Мы понимаем, как сложно подготовиться: стресс, алгоритмы, вопросы, от которых голова идёт кругом. Но с AI тренажёром всё гораздо проще.
💡 Почему Т1 тренажёр — это мастхэв?
- Получишь настоящую обратную связь: где затык, что подтянуть и как стать лучше
- Научишься не только решать задачи, но и объяснять своё решение так, чтобы интервьюер сказал: "Вау!".
- Освоишь все этапы собеседования, от вопросов по алгоритмам до диалога о твоих целях.
Зачем листать миллион туториалов? Просто зайди в Т1 тренажёр, потренируйся и уверенно удиви интервьюеров. Мы не обещаем лёгкой прогулки, но обещаем, что будешь готов!
Реклама. ООО «Смарт Гико», ИНН 7743264341. Erid 2VtzqwP8vqy
Знание изложенных ниже методов работы позволит вам:
- Составлять сложные и эффективные запросы.
- Оптимизировать производительность базы данных.
Продвинутые техники оптимизации запросов
Оптимизация 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.
Комментарии