<h1>Comparación de rendimiento de MySQL: trabajar con y sin índices</h1>
<p>En este artículo, examinaremos cómo los índices afectan el rendimiento de las consultas en MySQL, comparando la ejecución de operaciones de filtrado y JOIN con y sin índices.</p>
<h2>¿Qué son los índices y por qué son necesarios?</h2>
<p>Los índices en MySQL son estructuras de datos especiales que aceleran la búsqueda y recuperación de datos de las tablas. Sin índices, MySQL debe realizar un escaneo completo de la tabla, lo que es similar a leer un libro entero para encontrar una sola palabra. Con índices, la búsqueda se vuelve similar a usar el índice de un libro.</p>
<h3>Ejemplo 1: Filtrado de datos por un solo campo</h3>
<p><strong>Configuración del entorno de prueba</strong></p>
<p>Creemos una tabla de prueba y poblémosla con datos:</p>
<div class="code">-- Crear base de datos de prueba
CREATE DATABASE test_indexes;
USE test_indexes;
-- Crear tabla sin índices
CREATE TABLE users_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Crear tabla similar con índice
CREATE TABLE users_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_age (age)
);
-- Poblar tablas con datos de prueba (100.000 registros cada una)
DELIMITER $$
CREATE PROCEDURE GenerateTestData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO users_no_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
INSERT INTO users_with_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateTestData();</div>
<h3>Comparación del rendimiento de filtrado</h3>
<p><strong>Consulta sin índice:</strong></p>
<div class="code">-- Analizar consulta sin índice
EXPLAIN SELECT * FROM users_no_index WHERE age = 25;</div>
<p><strong>Resultados de EXPLAIN:</strong></p>
<ul>
<li>type: ALL (escaneo completo de la tabla)</li>
<li>rows: 100000 (100.000 filas verificadas)</li>
<li>Extra: Using where</li>
</ul>
<p><strong>Tiempo de ejecución:</strong> ~120 ms</p>
<p><strong>Consulta con índice:</strong></p>
<div class="code">-- Analizar consulta con índice
EXPLAIN SELECT * FROM users_with_index WHERE age = 25;</div>
<p><strong>Resultados de EXPLAIN:</strong></p>
<ul>
<li>type: ref (búsqueda por índice)</li>
<li>rows: ~1000 (solo ~1000 filas verificadas)</li>
<li>key: idx_age (índice utilizado)</li>
<li>Extra: Using index condition</li>
</ul>
<p><strong>Tiempo de ejecución:</strong> ~5 ms</p>
<p><strong>Conclusiones sobre el filtrado</strong></p>
<ul>
<li><strong>Sin índice:</strong> MySQL realiza un escaneo completo de la tabla, verificando cada fila</li>
<li><strong>Con índice:</strong> MySQL utiliza el índice B-tree para encontrar rápidamente las filas requeridas</li>
<li><strong>Diferencia de rendimiento:</strong> de 20 a 30 veces más rápido</li>
</ul>
<h3>Ejemplo 2: Operaciones JOIN con y sin índices</h3>
<p><strong>Preparación de tablas relacionadas</strong></p>
<div class="code">-- Tabla de pedidos sin índices
CREATE TABLE orders_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);
-- Tabla de pedidos con índice
CREATE TABLE orders_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20),
INDEX idx_user_id (user_id)
);
-- Poblar tablas de pedidos
DELIMITER $$
CREATE PROCEDURE GenerateOrderData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 50000 DO
INSERT INTO orders_no_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
INSERT INTO orders_with_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateOrderData();</div>
<h3>Comparación de operaciones JOIN</h3>
<p><strong>JOIN sin índices:</strong></p>
<div class="code">-- JOIN sin índices
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_no_index u
JOIN orders_no_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Resultados de EXPLAIN:</strong></p>
<ul>
<li>Para ambas tablas: type: ALL</li>
<li>rows: 100000 * 50000 = 5.000.000.000 comparaciones potenciales</li>
<li>Extra: Using where; Using temporary; Using filesort</li>
</ul>
<p><strong>Tiempo de ejecución:</strong> ~4500 ms</p>
<p><strong>JOIN con índices:</strong></p>
<div class="code">-- JOIN con índices
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_with_index u
JOIN orders_with_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Resultados de EXPLAIN:</strong></p>
<ul>
<li>Para users: type: range (utiliza el índice age)</li>
<li>Para orders: type: ref (utiliza el índice user_id)</li>
<li>rows: significativamente menos</li>
<li>Uso más eficiente de tablas temporales</li>
</ul>
<p><strong>Tiempo de ejecución:</strong> ~150 ms</p>
<p><strong>Conclusiones sobre operaciones JOIN</strong></p>
<ul>
<li><strong>Sin índices:</strong> MySQL se ve obligado a realizar bucles anidados con escaneos completos de tablas</li>
<li><strong>Con índices:</strong> MySQL utiliza eficientemente los índices para encontrar rápidamente los registros relacionados</li>
<li><strong>Diferencia de rendimiento:</strong> 30 veces o más</li>
</ul>
<h3>¿Cuándo utilizar índices?</h3>
<p><strong>Se recomienda crear índices para:</strong></p>
<ul>
<li>Campos utilizados frecuentemente en condiciones WHERE</li>
<li>Campos involucrados en operaciones JOIN</li>
<li>Campos utilizados para ordenamiento (ORDER BY)</li>
<li>Campos utilizados en GROUP BY</li>
<li>Campos únicos o claves primarias</li>
</ul>
<p><strong>Cuándo los índices pueden ser ineficientes</strong>:</p>
<ul>
<li>Tablas con operaciones INSERT/UPDATE/DELETE frecuentes</li>
<li>Tablas pequeñas (menos de 1000 filas)</li>
<li>Columnas con baja selectividad (pocos valores únicos)</li>
</ul>
<h3>Mejores prácticas para trabajar con índices</h3>
<ul>
<li><strong>Indexar conscientemente</strong> — cada índice ralentiza las operaciones de escritura</li>
<li><strong>Utilizar índices compuestos</strong> para combinaciones de campos utilizadas frecuentemente</li>
<li><strong>Monitorear la selectividad</strong> — los índices en campos con pocos valores únicos son menos efectivos</li>
<li><strong>Analizar regularmente el uso de índices</strong>:</li>
</ul>
<div class="code">-- Analizar el uso de índices
SELECT * FROM sys.schema_unused_indexes;</div>
<p>5. <strong>Optimizar índices existentes</strong>:</p>
<div class="code">-- Analizar el rendimiento de consultas
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;</div>
<div class="code">-- Verificar la fragmentación de índices
ANALYZE TABLE users_with_index;</div>
<h2>Conclusión</h2>
<p>Los índices son una herramienta poderosa para optimizar el rendimiento de MySQL. Un uso adecuado puede acelerar la ejecución de consultas decenas de veces, especialmente para operaciones de filtrado y JOIN. Sin embargo, es importante recordar el equilibrio — una indexación excesiva puede ralentizar las operaciones de escritura. El monitoreo regular y el análisis de rendimiento ayudarán a encontrar la configuración óptima de índices para su aplicación.</p>
<p>Pruebe con sus propios datos, ya que la efectividad de los índices depende en gran medida de las características específicas de sus datos y de los patrones de consulta en su aplicación.</p>