ИТ Блог. Работа с базой данных (MySQL, CouchDB, MariaDB, MongoDB)
(1 оценок, среднее: 5,00 из 5)
Загрузка...
Пользователь базы данных должен запускать разные типы запросов для разных целей. Но иногда многие запросы не возвращают ожидаемых результатов, и администратору базы данных необходимо выяснить причину. MySQL содержит полезный инструмент EXPLAIN для диагностики ошибки, при которой оператор запроса не работает должным образом. Он используется в начале любого оператора запроса, чтобы предоставить информацию о выполнении запроса. Ключевое слово EXPLAIN может использоваться с операторами SELECT, INSERT, UPDATE, DELETE и REPLACE. В этой статье показано, как ключевое слово 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;
Следующий оператор SQL вернет ключевое слово оператора EXPLAIN простого запроса SELECT, который извлекает все записи из таблицы клиентов.
EXPLAIN SELECT * FROM customers \G;
Следующий вывод появится после выполнения оператора. Это запрос с одной таблицей, и в нем не используются специальные предложения, такие как JOIN, UNION и т. д. Для этого значение select_type равно SIMPLE. Таблица клиентов содержит всего три записи, поэтому значение строк равно 3. Значение отфильтрованного равно 100%, поскольку извлекаются все записи таблицы.
Следующая инструкция 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
В запросе нет ошибки. В выходных данных отображается измененный выполненный запрос.
Запрос 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
Выходные данные ясно показывают ошибку с сообщением об ошибке и именем столбца.
Оператор 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. Но этот оператор можно использовать для решения различных сложных проблем с базой данных и оптимизации базы данных для повышения производительности.
