En este paso, aprenderás cómo comparar el rendimiento de diferentes tipos de subconsultas en MySQL. Comprender las características de rendimiento de las subconsultas es crucial para escribir consultas SQL eficientes, especialmente cuando se trabaja con grandes conjuntos de datos.
El rendimiento de una subconsulta puede verse afectado por varios factores, entre ellos:
-
Conectarse al servidor MySQL:
Conéctate a tu servidor MySQL utilizando el cliente mysql.
mysql -u root
-
Seleccionar la base de datos:
Selecciona la base de datos labdb.
USE labdb;
-
Agregar más datos a la tabla orders:
Para que la comparación de rendimiento sea más realista, agreguemos una cantidad significativa de datos a la tabla orders. Insertaremos 1000 pedidos para cada cliente. Crea un archivo llamado ~/project/insert_orders.sql con el siguiente contenido:
USE labdb;
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;
Explicación:
- Este script SQL crea un procedimiento almacenado llamado
insert_many_orders.
- El procedimiento inserta 1000 pedidos para cada uno de los cuatro clientes en la tabla
orders.
- Después de insertar los datos, se elimina el procedimiento.
-
Ejecutar el script SQL:
Ejecuta el script SQL utilizando el siguiente comando:
mysql -u root < ~/project/insert_orders.sql
-
Consulta utilizando IN:
Ejecuta la siguiente consulta utilizando IN para encontrar a todos los clientes que hayan realizado al menos un pedido.
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-
Analizar el plan de ejecución de la consulta utilizando EXPLAIN:
Antes de ejecutar la consulta, utiliza el comando EXPLAIN para analizar el plan de ejecución de la consulta. Esto te dará información sobre cómo MySQL planea ejecutar la consulta e identificará posibles cuellos de botella de rendimiento.
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
La salida de EXPLAIN mostrará las tablas que se están accediendo, los índices que se están utilizando (si los hay) y el orden en el que se realizan las operaciones. Presta atención a la columna type, que indica el tipo de join o método de acceso utilizado.
-
Ejecutar la consulta y medir el tiempo de ejecución:
Para medir el tiempo de ejecución, utiliza la función BENCHMARK(). Esta función ejecuta una expresión un número especificado de veces y devuelve el tiempo que tarda en ejecutarse. Dado que queremos medir el tiempo de una sola ejecución, utilizaremos BENCHMARK(1, ...)
SELECT BENCHMARK(1, (SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders)));
La salida será una sola fila con el resultado del benchmark. Anota el tiempo que tarda en ejecutarse.
-
Consulta utilizando EXISTS:
Ejecuta la siguiente consulta utilizando EXISTS para encontrar a todos los clientes que hayan realizado al menos un pedido.
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
-
Analizar el plan de ejecución de la consulta utilizando EXPLAIN:
Utiliza el comando EXPLAIN para analizar el plan de ejecución de la consulta para la consulta EXISTS.
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Compara el plan de ejecución con el de la consulta IN. ¿Hay alguna diferencia en las tablas accedidas, los índices utilizados o los métodos de acceso?
-
Ejecutar la consulta y medir el tiempo de ejecución:
Mide el tiempo de ejecución de la consulta EXISTS utilizando la función BENCHMARK().
SELECT BENCHMARK(1, (SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
)));
Compara el tiempo de ejecución con el de la consulta IN. ¿Qué consulta fue más rápida?
-
Observaciones:
En general, EXISTS tiende a tener un mejor rendimiento que IN cuando la subconsulta devuelve un gran número de filas. Esto se debe a que IN necesita comparar los valores de la consulta externa con todos los valores devueltos por la subconsulta, mientras que EXISTS se detiene tan pronto como encuentra una coincidencia. Sin embargo, el rendimiento real puede variar dependiendo de la consulta específica, los datos y el sistema de base de datos.
-
Limpiar (opcional):
Si quieres limpiar la base de datos y las tablas, puedes ejecutar los siguientes comandos:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
Recuerda salir del cliente MySQL escribiendo exit y presionando Enter.
