Поиск по сайту:

Лучше изучить лишнее, чем ничего не изучить (Сенека Старший).

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

4 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
16 февраля 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;

 

Читать  DDL против DML: большая разница между DDL и DML

Следующий вывод появится после выполнения оператора. Это запрос с одной таблицей, и в нем не используются специальные предложения, такие как 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

 

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

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

SHOW WARNINGS \G

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

Читать  WITH в MySQL: общее табличное выражение (CTE)

 

Использование 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.

Если статья понравилась, то поделитесь ей в социальных сетях:

Читайте также

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest
0 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии
0
Оставьте комментарий! Напишите, что думаете по поводу статьи.x
()
x

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close