Чтение плана запроса в MySQL
Чтение плана запроса (execution plan) в MySQL — это важный навык для оптимизации и понимания того, как СУБД выполняет запрос. План запроса показывает, какие индексы используются, как таблицы соединяются, и какие операции выполняются. Для анализа плана запроса используется команда EXPLAIN.
1. Как получить план запроса
Чтобы получить план запроса, добавьте ключевое слово EXPLAIN перед SQL-запросом. Например:
EXPLAIN SELECT * FROM users WHERE age > 30;
2. Основные столбцы в выводе EXPLAIN
Результат выполнения EXPLAIN содержит несколько столбцов. Вот основные из них:
| Столбец | Описание |
|--------------------|-----------------------------------------------------------------------------|
| id | Идентификатор шага выполнения. Если запрос содержит подзапросы, каждый подзапрос будет иметь свой id . |
| select_type | Тип операции SELECT . Например, SIMPLE (простой запрос), PRIMARY (основной запрос), SUBQUERY (подзапрос). |
| table | Имя таблицы, к которой относится строка. |
| partitions | Разделы таблицы, которые будут использоваться (если таблица разделена). |
| type | Тип соединения (access type). Показывает, как MySQL будет искать строки в таблице. Например, ALL (полное сканирование), index (сканирование по индексу), ref (поиск по ключу). |
| possible_keys | Возможные индексы, которые могут быть использованы для выполнения запроса. |
| key | Фактический индекс, который будет использован. |
| key_len | Длина используемого индекса (в байтах). |
| ref | Столбцы или константы, которые сравниваются с индексом. |
| rows | Оценочное количество строк, которые будут обработаны. |
| filtered | Процент строк, которые будут отфильтрованы после применения условий WHERE . |
| Extra | Дополнительная информация о выполнении запроса. Например, Using where , Using index , Using temporary , Using filesort . |
3. Расшифровка ключевых параметров
type
- ALL: Полное сканирование таблицы (самый медленный способ).
- index: Сканирование по индексу (быстрее, чем ALL, но всё ещё медленно).
- range: Поиск по диапазону значений (например, BETWEEN, IN, >).
- ref: Поиск по не уникальному индексу.
- eq_ref: Поиск по уникальному индексу (например, PRIMARY KEY).
- const: Поиск по константе (например, WHERE id = 1).
Extra
- Using where: Применяется фильтрация по условию WHERE.
- Using index: Запрос использует только индекс (покрывающий индекс).
- Using temporary: Создается временная таблица (может быть медленно).
- Using filesort: Сортировка выполняется на диске (медленно).
4. Пример анализа плана запроса
Рассмотрим пример:
EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY name;
Результат может выглядеть так:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------|------|---------------|------|---------|------|------|----------|----------------|
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 1000 | 50.00 | Using where; Using filesort |
Анализ:
- type: ALL: MySQL выполняет полное сканирование таблицы, что неэффективно.
- key: NULL: Индекс не используется.
- rows: 1000: MySQL оценивает, что нужно просканировать 1000 строк.
- Extra: Using where; Using filesort: Применяется фильтрация и сортировка на диске.
Рекомендации:
- Добавьте индекс на столбец age, чтобы ускорить фильтрацию.
- Добавьте индекс на столбец name, чтобы избежать сортировки на диске.
5. Использование EXPLAIN FORMAT=JSON**
Для более детального анализа можно использовать EXPLAIN FORMAT=JSON:
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;
Этот формат предоставляет расширенную информацию, включая стоимость выполнения запроса.
6. Практические советы
- Индексы: Убедитесь, что индексы используются для фильтрации и сортировки.
- Оптимизация WHERE: Упрощайте условия в WHERE, чтобы они могли использовать индексы.
- Избегайте filesort: Добавляйте индексы на столбцы, используемые в ORDER BY.
- Анализ rows: Если значение rows слишком велико, это может указывать на неэффективность запроса.
7. Пример оптимизации
Добавим индекс на столбец age:
CREATE INDEX idx_age ON users(age);
Теперь выполним EXPLAIN снова:
EXPLAIN SELECT * FROM users WHERE age > 30;
Результат:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------|-------|---------------|---------|---------|------|------|----------|-------------|
| 1 | SIMPLE | users | range | idx_age | idx_age | 4 | NULL | 500 | 100.00 | Using where |
Анализ:
- type: range: MySQL использует индекс для поиска по диапазону.
- key: idx_age: Используется индекс idx_age.
- rows: 500: Количество обрабатываемых строк уменьшилось.
-
Создано 03.02.2025 08:16:35
-
Михаил Русаков
Комментарии (0):
Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.