В этом шаге вы научитесь сравнивать производительность различных типов подзапросов в MySQL. Понимание характеристик производительности подзапросов имеет решающее значение для написания эффективных SQL-запросов, особенно при работе с большими наборами данных.
-
Добавление большего количества данных в таблицу orders:
Чтобы сделать сравнение производительности более реалистичным, добавим значительный объем данных в таблицу orders. Мы вставим 1000 заказов для каждого клиента с помощью хранимой процедуры. Выполните следующие SQL-команды в приглашении MySQL:
DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000 DO
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;
Объяснение:
- Этот SQL-скрипт создает хранимую процедуру с именем
insert_many_orders.
- Процедура вставляет 1000 заказов для каждого из четырех клиентов в таблицу
orders.
- После вставки данных процедура удаляется.
-
Запрос с использованием IN:
Выполните следующий запрос с использованием IN для поиска всех клиентов, которые разместили хотя бы один заказ:
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-
Анализ плана выполнения запроса с помощью EXPLAIN:
Перед выполнением запроса используйте команду EXPLAIN для анализа плана выполнения запроса. Это даст вам представление о том, как MySQL планирует выполнить запрос, и поможет выявить потенциальные узкие места в производительности. Выполните следующую SQL-команду в приглашении MySQL:
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
Вывод EXPLAIN покажет вам доступные таблицы, используемые индексы (если таковые имеются) и порядок выполнения операций. Обратите внимание на столбец type, который указывает тип используемого соединения или метода доступа.
-
Запрос с использованием EXISTS:
Выполните следующий запрос с использованием EXISTS для поиска всех клиентов, которые разместили хотя бы один заказ:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
-
Анализ плана выполнения запроса с помощью EXPLAIN:
Используйте команду EXPLAIN для анализа плана выполнения запроса EXISTS. Выполните следующую SQL-команду в приглашении MySQL:
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Сравните план выполнения с планом запроса IN. Есть ли какие-либо различия в доступных таблицах, используемых индексах или методах доступа?
-
Наблюдения:
В целом, EXISTS обычно работает лучше, чем IN, когда подзапрос возвращает большое количество строк. Это связано с тем, что IN должен сравнивать значения внешнего запроса со всеми значениями, возвращаемыми подзапросом, в то время как EXISTS останавливается, как только находит совпадение. Однако фактическая производительность может варьироваться в зависимости от конкретного запроса, данных и системы управления базами данных. Вы можете использовать функцию BENCHMARK() (как показано в исходном документе), чтобы получить более точное измерение времени выполнения, но для этой лабораторной работы анализ вывода EXPLAIN дает достаточное представление о планах запросов.
-
Очистка (необязательно):
Если вы хотите очистить базу данных и таблицы, вы можете выполнить следующие команды в приглашении MySQL:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
Когда вы закончите все шаги, вы можете выйти из клиента MySQL, набрав:
exit
