Курс по SQL

Урок 42: Анализ и оптимизация запросов: Использование EXPLAIN для анализа запросов

В этом уроке мы рассмотрим использование команды EXPLAIN для анализа и оптимизации SQL-запросов. Команда EXPLAIN помогает понять, как СУБД выполняет запрос, и выявить возможные узкие места в производительности.

Что такое EXPLAIN?

Команда EXPLAIN используется для получения информации о плане выполнения SQL-запроса. Она показывает, как СУБД планирует выполнить запрос, какие индексы будут использованы, и оценку количества строк, которые будут обработаны. Эта информация помогает оптимизировать запросы для повышения их производительности.

Использование EXPLAIN

Для использования команды EXPLAIN перед SQL-запросом необходимо добавить ключевое слово EXPLAIN. Рассмотрим пример:

EXPLAIN SELECT * FROM Клиенты WHERE Имя = 'Иван';

Результат выполнения

Поля результата EXPLAIN

Рассмотрим основные поля результата команды EXPLAIN:

• id: Идентификатор запроса.
• select_type: Тип операции SELECT.
• table: Имя таблицы.
• type: Тип соединения (например, ALL, index, range, ref, eq_ref, const, system, NULL).
• possible_keys: Возможные индексы, которые могут быть использованы для выполнения запроса.
• key: Индекс, который фактически используется для выполнения запроса.
• key_len: Длина используемого ключа.
• ref: Столбцы или константы, которые сравниваются с индексом.
• rows: Оценочное количество строк, которые будут обработаны.
• Extra: Дополнительная информация о выполнении запроса (например, Using index, Using where, Using temporary, Using filesort).

Оптимизация запросов с использованием EXPLAIN

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

Пример 1: Добавление индекса

Рассмотрим запрос без индекса:

EXPLAIN SELECT * FROM Заказы WHERE Дата = '2023-01-01';

Результат выполнения

В данном случае СУБД выполняет полное сканирование таблицы (type = ALL). Это может быть неэффективно для больших таблиц. Добавим индекс на столбец "Дата":

CREATE INDEX idx_Дата ON Заказы (Дата);

EXPLAIN SELECT * FROM Заказы WHERE Дата = '2023-01-01';

Результат выполнения

Теперь запрос использует индекс (type = ref), что значительно ускоряет его выполнение.

Пример 2: Использование составного индекса

Рассмотрим запрос без составного индекса:

EXPLAIN SELECT * FROM Продукты WHERE Категория = 'Электроника' AND Цена < 1000;

Результат выполнения

В данном случае СУБД выполняет полное сканирование таблицы (type = ALL). Добавим составной индекс на столбцы "Категория" и "Цена":

CREATE INDEX idx_КатегорияЦена ON Продукты (Категория, Цена);

EXPLAIN SELECT * FROM Продукты WHERE Категория = 'Электроника' AND Цена < 1000;

Результат выполнения

Теперь запрос использует составной индекс (type = range), что ускоряет его выполнение.

Упражнения

EXPLAIN SELECT * FROM Заказы WHERE КлиентID = 1 AND Дата BETWEEN '2023-01-01' AND '2023-12-31';

CREATE INDEX idx_КлиентИДДата ON Заказы (КлиентID, Дата);

EXPLAIN SELECT * FROM Заказы WHERE КлиентID = 1 AND Дата BETWEEN '2023-01-01' AND '2023-12-31';

EXPLAIN SELECT * FROM Сотрудники WHERE Отдел = 'Маркетинг' AND Зарплата > 50000;

CREATE INDEX idx_ОтделЗарплата ON Сотрудники (Отдел, Зарплата);

EXPLAIN SELECT * FROM Сотрудники WHERE Отдел = 'Маркетинг' AND Зарплата > 50000;

EXPLAIN SELECT * FROM Продукты WHERE Категория = 'Компьютеры' AND Цена < 2000;

CREATE INDEX idx_КатегорияЦена ON Продукты (Категория, Цена);

EXPLAIN SELECT * FROM Продукты WHERE Категория = 'Компьютеры' AND Цена < 2000;

EXPLAIN SELECT * FROM Продажи WHERE Дата BETWEEN '2023-01-01' AND '2023-12-31' AND ПродуктID = 10;

CREATE INDEX idx_ДатаПродуктID ON Продажи (Дата, ПродуктID);

EXPLAIN SELECT * FROM Продажи WHERE Дата BETWEEN '2023-01-01' AND '2023-12-31' AND ПродуктID = 10;