Guía completa de índices y planes de ejecución en SQL para desarrolladores

Esta guía explica cómo funcionan los índices, cómo leer planes de ejecución y decisiones prácticas para diseñar índices eficientes. Incluye ejemplos ejecutables en Postgres y MySQL, errores comunes y cuándo los índices pueden empeorar el rendimiento.

1. ¿Por qué importar un índice?

• Reducen lecturas físicas: evitan escaneos completos de tablas.
• Permiten búsquedas y ordenaciones rápidas.
• Pueden habilitar index-only scans (si la columna está en el índice y las estadísticas lo permiten).

2. Tipos de índices (resumen)

• B-tree: predeterminado, soporta igualdad y rangos; ideal para la mayoría de casos.
• Hash: igualdad rápida (en ciertos motores); limitada para rangos.
• GiST / GIN: para búsquedas geoespaciales y texto (Postgres).
• Index expressions: índices sobre funciones/expresiones (Postgres, MySQL funcion-based en algunas versiones).
• Partial / filtered: indexar solo filas que cumplen una condición.
• Covering / INCLUDE: columnas adicionales en el índice para evitar lecturas de tabla (Postgres INCLUDE, SQL Server INCLUDE).

3. Reglas prácticas: cómo crear buenos índices

1. Indexa columnas usadas en WHERE, JOIN y ORDER BY.
2. En índices compuestos, pon primero la columna con mayor selectividad o la que se filtra primero.
3. No indexar columnas de baja cardinalidad (booleanas, banderas pequeñas) salvo que formen parte de un índice compuesto selectivo.
4. Evita demasiados índices en tablas con alta tasa de escritura.
5. Usa índices parciales cuando solo un subconjunto de filas necesita acelerar consultas.

4. Ejemplos prácticos

Tabla de ejemplo

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  total_amount NUMERIC(10,2)
);

Escenario: queremos acelerar consultas por customer_id y búsquedas por estado reciente.

-- Índice simple para JOIN/WHERE
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Índice compuesto: útil si filtramos por customer_id y luego por created_at range
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

-- Índice parcial para filas activas
CREATE INDEX idx_orders_status_active ON orders (created_at) WHERE status = 'active';

-- Postgres: incluir columnas para evitar lectura de heap
CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total_amount);

Por qué el orden importa

Un índice (a,b) puede usarse para consultas que filtren por 'a' o por 'a' y 'b', pero no por 'b' solo. Diseña el orden según los patrones de filtro más frecuentes.

5. Analizar planes de ejecución

Comando básico:

-- Postgres
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Puntos clave al leer un plan:

• Tipo de acceso: index scan, index seek, seq scan/table scan.
• Rows estimadas vs reales: grandes desviaciones indican estadísticas obsoletas.
• Costo estimado: útil para comparar planes, no es tiempo real en ms.
• Tiempo total (EXPLAIN ANALYZE): tiempo real incluyendo ejecución.

-- Ejemplo Postgres simplificado
EXPLAIN ANALYZE SELECT total_amount FROM orders WHERE customer_id = 123;

-- Salida relevante (resumida):
-- Index Scan using idx_orders_customer on orders (cost=0.29..12.50 rows=10 width=16) (actual time=0.10..0.35 rows=8 loops=1)

Interpretación: el optimizador eligió un Index Scan usando idx_orders_customer; las filas reales (8) están cerca de la estimación (10), buen indicador.

6. Index-only scans y estadísticas

Un index-only scan ocurre cuando todas columnas requeridas están en el índice y el motor puede satisfacer la consulta sin leer la tabla. En Postgres esto depende de la visibilidad de filas (MVCC) y de si la información de tupla es visible según las estadísticas de vacuum.

7. Mantenimiento y métricas

• Postgres: ejecutar ANALYZE para actualizar estadísticas; VACUUM/REINDEX para recuperar espacio y reconstruir índices tras mucha actividad de escritura.
• MySQL (InnoDB): OPTIMIZE TABLE para compactar; SHOW INDEX / INFORMATION_SCHEMA.STATISTICS para ver índices.
• Mide: índice tamaño (pg_relation_size), uso de índices (pg_stat_user_indexes), tasa de cache misses.

8. Cuando los índices empeoran el rendimiento

• Insert/update/delete se ralentizan porque cada índice debe actualizarse.
• Demasiados índices aumentan uso de espacio y fragmentación.
• Índices no selectivos consumen recursos sin beneficio real.

9. Casos avanzados y trucos

• Usa partial indexes para tablas con mucha inactividad: reduce tamaño y mejora selectividad.
• En Postgres, CREATE INDEX CONCURRENTLY evita bloquear escrituras al crear índice.
• Considera expression indexes para búsquedas normalizadas (por ejemplo LOWER(col)).
• Evalúa fillfactor para tablas con muchas actualizaciones en Postgres.
• Monitorea plan changes tras cambios de versión o parámetros del servidor.

10. Checklist rápido antes de crear un índice

1. ¿La columna aparece frecuentemente en WHERE/JOIN/ORDER BY? Si no, no indexar.
2. ¿La cardinalidad es suficiente para justificar un índice?
3. ¿El índice será usado por el optimizador (verificar con EXPLAIN)?
4. ¿Impacto en escrituras aceptable?
5. ¿Necesitas INCLUDE/covering para evitar lecturas de tabla?

11. Comandos útiles (resumen)

-- Postgres
ANALYZE orders;
VACUUM (VERBOSE, ANALYZE) orders;
REINDEX TABLE orders;
SELECT relname, pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_indexes JOIN pg_stat_all_tables ON pg_stat_user_indexes.relid = pg_stat_all_tables.relid;

-- MySQL
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;
SHOW INDEX FROM orders;

12. Errores comunes

• Crear índices "por si acaso" sin medir su uso.
• Ignorar orden de columnas en índices compuestos.
• No actualizar estadísticas tras migraciones o cargas masivas.
• Subestimar el coste en escrituras y espacio.

Si necesitas ejemplos concretos con datos reales, prepara una muestra de tus consultas y la definición de la tabla; con EXPLAIN ANALYZE puedo ayudarte a proponer índices y simular el impacto. Consejo avanzado: habilita y revisa métricas de uso de índices (pg_stat_user_indexes o performance_schema) antes y después de cambios para validar hipótesis.

Advertencia: añadir índices indiscriminadamente puede degradar el rendimiento de escritura y aumentar la complejidad operativa; automatiza tests de rendimiento y planifica mantenimiento (vacuum/reindex) como parte del ciclo de vida.

Siguiente paso: prueba las recomendaciones en un entorno de staging con datos representativos y recopila EXPLAIN ANALYZE antes y después para cuantificar mejoras.