Народ, который поет и пляшет, зла не думает (Екатерина II).

Как использовать заявление Explain в MySQL

2 мин для чтения

Главное меню » Базы данных » MySQL » Как использовать заявление Explain в MySQL

(1 оценок, среднее: 5,00 из 5)

Загрузка...

16.02.2021

Новые функции регулярных выражений в MySQL 8.0

Пользователь базы данных должен запускать разные типы запросов для разных целей. Но иногда многие запросы не возвращают ожидаемых результатов, и администратору базы данных необходимо выяснить причину. MySQL содержит полезный инструмент EXPLAIN для диагностики ошибки, при которой оператор запроса не работает должным образом. Он используется в начале любого оператора запроса, чтобы предоставить информацию о выполнении запроса. Ключевое слово EXPLAIN может использоваться с операторами SELECT, INSERT, UPDATE, DELETE и REPLACE. В этой статье показано, как ключевое слово EXPLAIN работает с операторами SELECT для диагностики ошибок запроса или оптимизации запроса.

Вывод ключевого слова EXPLAIN для запроса SELECT:

Когда ключевое слово EXPLAIN выполняется с помощью оператора SELECT, вывод EXPLAIN будет возвращать следующие столбцы.

Столбец	Описание
id	В нем указывается идентификатор запроса. Он представляет собой порядковый номер запросов SELECT.
select_type	Он указывает тип запроса SELECT. Тип может быть SIMPLE, PRIMARY, SUBQUERY, UNION и т. д.
table	Он указывает имя таблицы, используемое в запросе.
partitions	Он указывает разделы исследуемой многораздельной таблицы.
type	Он указывает тип JOIN или тип доступа к таблицам.
possible_keys	Он указывает ключи, которые MySQL может использовать для поиска строк в таблице.
key	Он указывает индекс, используемый MySQL.
key_len	Он указывает длину индекса, который будет использовать оптимизатор запросов.
ref	Он указывает столбцы или константы, которые сравниваются с индексом, указанным в ключевом столбце.
rows	В нем указаны списки проверенных записей.
filtered	Он указывает предполагаемый процент строк таблицы, которые будут отфильтрованы по условию.
extra	Он указывает дополнительную информацию о плане выполнения запроса.

Предположим, у вас есть две связанные таблицы с именами клиенты и заказы в базе данных с именем company. Ниже приведены необходимые операторы SQL для создания базы данных и таблиц с данными.

CREATE DATABASE company;

USE company;

CREATE TABLE customers (
id INT(5) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
mobile_no VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL) ENGINE = INNODB;

CREATE TABLE orders (
id VARCHAR(20) PRIMARY KEY,
order_date date,
customer_id INT(5) NOT NULL,
delivery_address VARCHAR(50) NOT NULL,
amount INT(11),
FOREIGN KEY (customer_id) REFERENCES customers(id))
ENGINE = INNODB;

INSERT INTO customers values
(NULL,'AndreyEx', '18477366643' , 'andreyex@andreyex.ru'),
(NULL,'Destroyer', '17839394985','destroyer@andreyex.ru'),
(NULL,'Alex','14993774655' , 'alex@andreyex.ru');

INSERT INTO orders value
('3456', '2021-01-03', 1, 'Moskow', 30000),
('3457', '2021-01-13', 3, 'Krasnodar', 25000),
('2314', '2021-02-01', 1, 'Samara', 18000),
('8563', '2021-02-11' , 2 , 'Sankt Petergburg', 19000),

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

SELECT * FROM customers;

Выполните следующую инструкцию, чтобы увидеть текущий список записей таблицы заказов.

SELECT * FROM orders;

Использование простого оператора EXPLAIN:

Следующий оператор SQL вернет ключевое слово оператора EXPLAIN простого запроса SELECT, который извлекает все записи из таблицы клиентов.

EXPLAIN SELECT * FROM customers \G;

Читать Запрос «IS NULL» в MySQL

Следующий вывод появится после выполнения оператора. Это запрос с одной таблицей, и в нем не используются специальные предложения, такие как JOIN, UNION и т. д. Для этого значение select_type равно SIMPLE. Таблица клиентов содержит всего три записи, поэтому значение строк равно 3. Значение отфильтрованного равно 100%, поскольку извлекаются все записи таблицы.

Использование EXPLAIN в запросе SELECT с JOIN:

Следующая инструкция EXPLAIN применяется в запросе SELECT для двух таблиц с предложением JOIN и условием WHERE.

EXPLAIN SELECT customers.name, orders.order_date, orders.amount
FROM customers
JOIN orders ON (customers.id = orders.customer_id)
WHERE customers.name = 'AndreyEx' \G

Следующий вывод появится после выполнения оператора. Здесь select_type равно SIMPLE для обеих таблиц. Две таблицы связаны отношением «один ко многим». Первичный ключ из клиентов таблицы используется в качестве внешнего ключа из заказов таблицы. Для этого значение possible_keys для второй строки — customer_id. Отфильтрованное значение для таблицы клиентов составляет 33%, потому что «AndreyEx» является первой записью в этой таблице и больше искать не нужно. Отфильтрованное значение таблицы заказов равно 100% из-за всех значений заказов в таблице, необходимая для проверки для получения данных.

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

SHOW WARNINGS \G

В запросе нет ошибки. В выходных данных отображается измененный выполненный запрос.

Использование EXPLAIN для обнаружения ошибки запроса SELECT:

Запрос SELECT, используемый в следующем операторе EXPLAIN, содержит ошибку. MySQL поддерживает формат даты «YYYY-MM-DD». Но в условии WHERE этого запроса значение даты указано как ‘DD-MM-YYYY’, что неверно.

EXPLAIN SELECT customers.name, orders.order_date, orders.amount
FROM customers
JOIN orders ON (customers.id = orders.customer_id)
WHERE orders.order_date = '10-10-2020' \G

После выполнения оператора появится следующий вывод. Будет показано два предупреждения. Одно значение по умолчанию, которое объясняется в предыдущем примере, а другое — для ошибки даты, упомянутой ранее.

Читать Разница между VARCHAR и TEXT в MySQL

Запустите инструкцию, чтобы увидеть ошибку.

SHOW WARNINGS \G

Выходные данные ясно показывают ошибку с сообщением об ошибке и именем столбца.

Использование EXPLAIN в запросе SELECT с оператором UNION ALL:

Оператор UNION ALL используется в запросе SELECT для извлечения всех совпадающих значений столбцов с дубликатами из связанных таблиц. Следующий оператор отобразит выходные данные EXPLAIN применения оператора UNION ALL между таблицами клиентов и заказов.

EXPLAIN SELECT id as ID
FROM customers
UNION ALL
SELECT customer_id as ID
FROM orders \G

В выводе значение select_type — UNION для второй строки вывода, а значение Extra — это индекс.

Вывод:

В этой статье показано очень простое использование оператора EXPLAIN. Но этот оператор можно использовать для решения различных сложных проблем с базой данных и оптимизации базы данных для повышения производительности.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.