Índices y rendimiento

Con 8 productos cualquier consulta es instantánea. Con 5 millones de pedidos, una consulta sin índice puede tardar minutos y tumbar el sitio de tu cliente en pleno Buen Fin. En esta lección entenderás cómo funcionan los índices, cuándo crearlos (y cuándo no), cómo leer EXPLAIN y cuáles son las causas más comunes de consultas lentas.

Concepto

Un índice es como el índice alfabético al final de un libro. Para encontrar "normalización" puedes:

Leer el libro completo página por página (escaneo completo / full table scan).
Ir al índice, buscar la "N", ver "página 312" e ir directo (búsqueda por índice).

El índice es una estructura aparte que guarda los valores de una o varias columnas ordenados, con un apuntador a la fila correspondiente.

Cómo es por dentro: el árbol B (B-tree)

El tipo de índice por defecto en MySQL, PostgreSQL y SQLite es el B-tree, un árbol balanceado:

txt

                       [ M ]
                  /            \
           [ D   H ]          [ R   W ]
          /   |    \         /   |    \
      [A-C] [E-G] [I-L]  [N-Q] [S-V] [X-Z]   ← hojas: valor + apuntador a la fila

Para encontrar un valor entre millones solo se recorren 3 o 4 niveles. Pasar de leer 5,000,000 filas a leer 4 nodos es la diferencia entre 3 segundos y 1 milisegundo.

Un B-tree sirve para:

Igualdad: WHERE email = 'ana@correo.com'
Rangos: WHERE fecha BETWEEN ... AND ..., precio > 500
Prefijos: WHERE nombre LIKE 'Tec%'
Ordenar: ORDER BY fecha (ya están ordenados)
MIN / MAX

No sirve para: LIKE '%texto%' (comodín al inicio) ni funciones aplicadas a la columna (salvo índices de expresión).

El costo de los índices

Los índices no son gratis:

Ventaja	Desventaja
`SELECT` mucho más rápidos	Ocupan espacio en disco y memoria
`ORDER BY` y `JOIN` más rápidos	Cada `INSERT`/`UPDATE`/`DELETE` debe actualizar todos los índices

Una tabla con 15 índices escribe mucho más lento. La meta es tener los índices justos.

Ejemplo: crear índices

sql

-- Índice simple
CREATE INDEX idx_pedidos_fecha ON pedidos (fecha);

-- Índice único (además de acelerar, impide duplicados)
CREATE UNIQUE INDEX idx_clientes_email ON clientes (email);

-- Índice compuesto (varias columnas)
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, fecha);

-- Ver índices
-- MySQL:      SHOW INDEX FROM pedidos;
-- PostgreSQL: \di   o   \d pedidos

-- Eliminar
DROP INDEX idx_pedidos_fecha;              -- PostgreSQL / SQLite
DROP INDEX idx_pedidos_fecha ON pedidos;   -- MySQL

Índices que ya existen automáticamente:

La PRIMARY KEY siempre tiene índice.
Cada restricción UNIQUE crea un índice.
En MySQL/InnoDB, cada FOREIGN KEY crea un índice si no existe.
En PostgreSQL, las FK no se indexan solas: créalos tú.

Buena práctica (PostgreSQL)

Indexa siempre las columnas de clave foránea (pedidos.cliente_id, detalle_pedido.producto_id, productos.categoria_id). Se usan en cada JOIN y al borrar un registro padre la BD las revisa.

sql

-- Índices recomendados para el esquema de la tienda (PostgreSQL)
CREATE INDEX idx_productos_categoria ON productos (categoria_id);
CREATE INDEX idx_pedidos_cliente     ON pedidos (cliente_id);
CREATE INDEX idx_detalle_producto    ON detalle_pedido (producto_id);
-- detalle_pedido(pedido_id) ya está cubierto: es la primera columna de la PK compuesta

Índices compuestos y el orden de las columnas

Un índice sobre (cliente_id, fecha) es como una guía telefónica ordenada por apellido y luego nombre:

txt

(cliente_id, fecha)
1, 2024-05-02
1, 2024-06-01
2, 2024-05-10
3, 2024-06-15
4, 2024-06-20

Consulta	¿Usa el índice?
`WHERE cliente_id = 1`	Sí (columna izquierda)
`WHERE cliente_id = 1 AND fecha >= '2024-06-01'`	Sí, perfecto
`WHERE cliente_id = 1 ORDER BY fecha`	Sí, y evita ordenar
`WHERE fecha >= '2024-06-01'`	No eficientemente (falta la primera columna)

Es la regla del prefijo izquierdo: un índice compuesto sirve para consultas que usan sus columnas de izquierda a derecha. Igual que una guía telefónica no te ayuda a buscar a todos los "Juan" sin saber el apellido.

Criterio para ordenar las columnas:

Primero las de igualdad (cliente_id = ?).
Después las de rango u orden (fecha >= ?, ORDER BY fecha).

Índice cubriente

Si el índice contiene todas las columnas que pide la consulta, la BD responde solo con el índice, sin tocar la tabla:

sql

-- El índice (cliente_id, fecha) cubre esta consulta por completo
SELECT fecha FROM pedidos WHERE cliente_id = 1;

-- PostgreSQL: agregar columnas "de pasajero" al índice
CREATE INDEX idx_pedidos_cliente_inc ON pedidos (cliente_id) INCLUDE (fecha, estado);

EXPLAIN: ver el plan de ejecución

EXPLAIN muestra cómo piensa ejecutar la BD tu consulta, sin adivinar.

En MySQL

sql

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 1;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ref	cliente_id	cliente_id	2	NULL

Columnas clave:

Columna	Qué mirar
`type`	De mejor a peor: `const` → `eq_ref` → `ref` → `range` → `index` → `ALL` (escaneo completo)
`key`	Índice que realmente usa (`NULL` = ninguno)
`rows`	Filas que estima revisar
`Extra`	`Using index` (cubriente, ¡bien!), `Using filesort` o `Using temporary` (ojo)

sql

-- MySQL 8.0.18+: ejecuta la consulta y muestra tiempos reales
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE fecha >= '2024-06-01';

En PostgreSQL

sql

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 1;

Sin índice:

txt

Seq Scan on pedidos  (cost=0.00..18.50 rows=4 width=52) (actual time=0.012..0.015 rows=2 loops=1)
  Filter: (cliente_id = 1)
  Rows Removed by Filter: 3
Planning Time: 0.080 ms
Execution Time: 0.030 ms

Con índice (en una tabla grande):

txt

Index Scan using idx_pedidos_cliente on pedidos  (cost=0.29..8.31 rows=2 width=52) (actual time=0.010..0.011 rows=2 loops=1)
  Index Cond: (cliente_id = 1)
Execution Time: 0.025 ms

Nodo	Significa
`Seq Scan`	Lee toda la tabla
`Index Scan`	Usa el índice y luego va a la tabla
`Index Only Scan`	Responde solo con el índice (cubriente)
`Bitmap Heap Scan`	Usa índice para muchas filas dispersas
`Nested Loop` / `Hash Join` / `Merge Join`	Estrategias para JOINs
`Sort`	Tiene que ordenar (¿falta un índice para el ORDER BY?)

¿Por qué no usa mi índice?

En tablas pequeñas (como las de este curso), la BD prefiere leer toda la tabla: es más rápido que consultar el índice. Para practicar de verdad, genera muchas filas (abajo tienes un script). También ignora el índice si la consulta devuelve gran parte de la tabla (por ejemplo, WHERE estado <> 'cancelado').

EXPLAIN ANALYZE ejecuta la consulta

EXPLAIN ANALYZE de un DELETE o UPDATE realmente borra o modifica. Envuélvelo en una transacción: BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;

Paso a paso: practicar con un millón de filas (PostgreSQL)

sql

-- 1. Tabla de prueba
CREATE TABLE ventas_prueba (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id INT NOT NULL,
  fecha DATE NOT NULL,
  total NUMERIC(10,2) NOT NULL
);

-- 2. Un millón de filas aleatorias
INSERT INTO ventas_prueba (cliente_id, fecha, total)
SELECT
  (random() * 50000)::int + 1,
  DATE '2023-01-01' + (random() * 700)::int,
  round((random() * 5000)::numeric, 2)
FROM generate_series(1, 1000000);

ANALYZE ventas_prueba;   -- actualiza estadísticas

-- 3. Medir sin índice
EXPLAIN ANALYZE SELECT * FROM ventas_prueba WHERE cliente_id = 1234;
-- Seq Scan ... Execution Time: ~60 ms

-- 4. Crear índice y volver a medir
CREATE INDEX idx_vp_cliente ON ventas_prueba (cliente_id);
EXPLAIN ANALYZE SELECT * FROM ventas_prueba WHERE cliente_id = 1234;
-- Bitmap Index Scan ... Execution Time: ~0.1 ms

En MySQL puedes generar los datos con una CTE recursiva (subiendo cte_max_recursion_depth) o con un script en PHP/Node.

Las causas más comunes de consultas lentas

1. Funciones sobre la columna indexada

sql

-- MAL: el índice en fecha no se usa
SELECT * FROM pedidos WHERE YEAR(fecha) = 2024;

-- BIEN: rango sobre la columna "desnuda"
SELECT * FROM pedidos WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01';

Lo mismo con LOWER(email) = .... Solución: normalizar el dato al guardar, o crear un índice de expresión (PostgreSQL: CREATE INDEX ON clientes (LOWER(email));; MySQL 8: CREATE INDEX idx ON clientes ((LOWER(email)));).

2. LIKE con comodín al inicio

LIKE '%sql%' recorre todo. Usa búsqueda de texto completo para buscadores.

3. Tipos que no coinciden

Si telefono es VARCHAR y buscas WHERE telefono = 5512345678 (número), MySQL convierte cada fila y no usa el índice. Compara con el mismo tipo: '5512345678'.

4. SELECT * innecesario

Trae columnas que no usas (incluso TEXT enormes) e impide índices cubrientes.

5. OFFSET gigante

LIMIT 20 OFFSET 200000 lee y descarta 200,000 filas. Usa paginación por cursor: WHERE id > :ultimo_id ORDER BY id LIMIT 20.

6. El problema N+1

Ocurre en el código de la aplicación, sobre todo con ORMs:

txt

1 consulta:  SELECT * FROM pedidos LIMIT 50;
50 consultas: SELECT * FROM clientes WHERE id = ?;   ← una por cada pedido
= 51 consultas a la BD para una sola página

Solución: un JOIN o una segunda consulta con IN:

sql

SELECT pe.*, cl.nombre
FROM pedidos pe
JOIN clientes cl ON cl.id = pe.cliente_id
LIMIT 50;
-- o: SELECT * FROM clientes WHERE id IN (1, 2, 3, ...);

En Laravel se resuelve con with('cliente') (eager loading), en Prisma con include.

7. Falta de índice en columnas de JOIN, WHERE y ORDER BY frecuentes

Encuentra las consultas lentas reales:

MySQL: activa el slow query log (ver Optimización de MySQL).
PostgreSQL: extensión pg_stat_statements (ver Índices avanzados).

Checklist: ¿qué indexar?

Indexa	No indexes
Columnas en `WHERE` frecuentes y selectivas (email, sku)	Columnas con pocos valores distintos (`activo` sí/no) por sí solas
Claves foráneas	Tablas diminutas
Columnas de `ORDER BY` en listados paginados	Columnas que casi nunca filtras
Combinaciones frecuentes → índice compuesto	Columnas que se actualizan constantemente sin consultarse
Columnas que deben ser únicas (`UNIQUE`)	Duplicados: `(a)` sobra si ya tienes `(a, b)`

Buena práctica

No indexes "por si acaso". Mide primero: identifica la consulta lenta, mira su EXPLAIN, crea el índice, vuelve a medir. Y revisa periódicamente los índices que nunca se usan (PostgreSQL: vista pg_stat_user_indexes).

Ejercicios

¿Qué índice crearías para acelerar SELECT * FROM pedidos WHERE estado = 'pendiente' ORDER BY fecha;?
Tienes el índice (categoria_id, precio). ¿Cuáles de estas consultas lo aprovechan? a) WHERE categoria_id = 1, b) WHERE precio < 500, c) WHERE categoria_id = 1 AND precio < 500, d) WHERE categoria_id = 1 ORDER BY precio.
Reescribe para que use el índice de fecha_registro: SELECT * FROM clientes WHERE EXTRACT(MONTH FROM fecha_registro) = 2 AND EXTRACT(YEAR FROM fecha_registro) = 2024;
En EXPLAIN de MySQL ves type: ALL y rows: 2000000. ¿Qué significa y qué harías?

Ver solución

Un índice compuesto con la igualdad primero y el orden después:

sql

CREATE INDEX idx_pedidos_estado_fecha ON pedidos (estado, fecha);

a) Sí. b) No (falta la primera columna). c) Sí, ideal. d) Sí, y además evita ordenar.

sql

SELECT * FROM clientes
WHERE fecha_registro >= '2024-02-01' AND fecha_registro < '2024-03-01';

Que hace un escaneo completo de 2 millones de filas. Revisa qué columnas usa el WHERE/JOIN, verifica que no tengan funciones encima y crea el índice adecuado; luego confirma con EXPLAIN que type cambie a ref o range.

Resumen

Un índice (B-tree) es una estructura ordenada que evita leer toda la tabla.
Aceleran lecturas pero cuestan espacio y hacen más lentas las escrituras: crea los justos.
PK y UNIQUE se indexan solos; en PostgreSQL indexa tú las FK.
En índices compuestos importa el orden: igualdades primero, rangos después (prefijo izquierdo).
EXPLAIN / EXPLAIN ANALYZE muestran el plan: evita ALL / Seq Scan en tablas grandes.
Enemigos del rendimiento: funciones sobre columnas, LIKE '%x', SELECT *, OFFSET grande y N+1.

Índices y rendimiento ​

Concepto ​

Cómo es por dentro: el árbol B (B-tree) ​

El costo de los índices ​

Ejemplo: crear índices ​

Índices compuestos y el orden de las columnas ​

Índice cubriente ​

EXPLAIN: ver el plan de ejecución ​

En MySQL ​

En PostgreSQL ​

Paso a paso: practicar con un millón de filas (PostgreSQL) ​

Las causas más comunes de consultas lentas ​

1. Funciones sobre la columna indexada ​

2. LIKE con comodín al inicio ​

3. Tipos que no coinciden ​

4. SELECT * innecesario ​

5. OFFSET gigante ​

6. El problema N+1 ​

7. Falta de índice en columnas de JOIN, WHERE y ORDER BY frecuentes ​

Checklist: ¿qué indexar? ​

Ejercicios ​

Resumen ​