Índices SQL — Por qué su consulta es lenta y qué hacer realmente para solucionarlo

Tu consulta tarda 2ms en localhost con 500 filas. En producción con 3 millones de filas, tarda 9 segundos y tus usuarios están mirando un indicador de carga. La consulta en sí probablemente está bien — el esquema no. Necesitas un índice y necesitas entenderlo. ¿por qué así que dejas de adivinar la próxima vez que esto ocurra.

Lo que hace la base de datos sin un índice

Un índice B-tree es una copia ordenada de una columna (o columnas), almacenada en un árbol equilibrado que permite una búsqueda binaria en tiempo O(log n). Sin él, la base de datos realiza escaneo completo de la tabla — lee cada fila para encontrar coincidencias. Con 3 millones de filas, eso significa 3 millones de lecturas de filas. Con un índice en la columna correcta, podría ser solo 20 a 30 recorridos de nodos del árbol para encontrar las filas coincidentes.

Piensa en ello como en un libro telefónico. Encontrar “Smith, John” en orden alfabético tarda segundos. Encontrar a todas las personas llamadas John leyendo cada entrada desde la página 1 tarda para siempre. Un índice es la ordenación alfabética — la base de datos lo utiliza para saltar directamente a las filas relevantes en lugar de escanear todo.

Una cosa importante: los índices existen en disco junto a tu tabla. La base de datos los mantiene automáticamente en las operaciones de escritura. Es gratis para lecturas, pero cuesta algo en cada INSERT, UPDATE y DELETE — por eso no puedes indexar todo.

Detectar el problema con EXPLAIN

Antes de tocar nada, ejecuta EXPLAIN en la consulta lenta. Aquí está lo que estás buscando en MySQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Dos campos importantes:

• type: ALL — escaneo completo de la tabla. Casi siempre malo una vez que tu tabla tenga más de ~100k filas.
• rows: 2847391 — el optimizador estima que examinará 2.8 millones de filas para devolver tus resultados.

Ahora añade el índice y ejecútalo de nuevo:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type: ref y rows: 12. Eso es la solución. 2.8 millones de filas examinadas → 12.

En PostgreSQL, usa EXPLAIN ANALYZE (que realmente ejecuta la consulta) para obtener números reales de tiempo:

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934ms → 0.3ms. Eso no es un ejemplo seleccionado — este es el impacto realista de un único índice faltante en una tabla con unos millones de filas.

Índices compuestos y la regla del prefijo izquierdo

Una vez que tengas múltiples columnas en tu cláusula WHERE, necesitas índices compuestos — y el orden de las columnas importa.

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

El regla del prefijo izquierdo: la base de datos utiliza un índice compuesto comenzando desde la columna más a la izquierda y avanza hacia la derecha, pero deja de ser útil una vez que encuentra una brecha. Dado un índice en (a, b, c):

• WHERE a = 1 — utiliza el índice
• WHERE a = 1 AND b = 2 — utiliza ambas columnas del índice
• WHERE a = 1 AND b = 2 AND c = 3 — utiliza el índice completo
• WHERE b = 2 — no utiliza el índice (columna omitida)
• WHERE a = 1 AND c = 3 — utiliza solo la parte a, ignora c

La regla práctica de ordenación: condiciones de igualdad primero, condiciones de rango al final. Las condiciones de rango (>, <, BETWEEN, LIKE 'prefix%') rompen el prefijo utilizable para las columnas siguientes. Así:

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

También piensa en la selectividad: coloca la columna más selectiva (menos valores duplicados) al principio del índice. Si customer_id tiene 50.000 valores distintos y status tiene 3, customer_id debería ir primero — reduce el conjunto de resultados más rápido.

Cuando los índices empeoran las cosas

Los índices no son gratis. Cada índice cuesta rendimiento de escritura y espacio en disco. La base de datos actualiza cada índice en cada INSERT, UPDATE y DELETE en esa tabla. En tablas con alta carga de escritura, demasiados índices realmente afectan el rendimiento. Aquí es donde los índices fallan o se vuelven contraproducentes:

• Columnas de baja cardinalidad. Un índice en un campo booleano o en un status campo con 3 valores distintos a menudo es ignorado por el optimizador. Si una consulta devolvería más de ~10–15% de filas mediante el índice, la base de datos normalmente prefiere un escaneo completo de la tabla — el costo de búsqueda en el índice no vale la pena. Verifica con EXPLAIN si el índice realmente se está utilizando.
• Tablas con alta carga de escritura. Tablas de log, tablas de eventos, tablas de auditoría — sé conservador. Una tabla con 10 índices y millones de escrituras por hora paga 10 veces el costo de escritura en esos índices.
• Índices inutilizados. En PostgreSQL, pg_stat_user_indexes muestra idx_scan — cuántas veces se ha usado cada índice desde la última actualización de estadísticas. Un índice con cero escaneos es un gasto puro. MySQL lo rastrea mediante el Esquema de Rendimiento (sys.schema_unused_indexes en MySQL 5.7+).
• El optimizador adivina mal. Los planificadores de consultas no siempre están correctos. A veces eligen un índice más lento que un escaneo completo. EXPLAIN ANALYZE capturará esto — compara filas estimadas con filas reales. En MySQL puedes indicar con USE INDEX (idx_name); en PostgreSQL puedes probar con SET enable_seqscan = off (no dejes esto en producción).

Índice o reescritura? La verdadera compensación

A veces el índice es la solución. A veces la consulta es el problema y el índice solo lo oculta. Algunos patrones en los que deberías reescribir la consulta en lugar de (o además de) añadir un índice:

Funciones en columnas indexadas

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

Envolver una columna en cualquier función impide el uso estándar del índice. El índice almacena valores de created_at, no valores de YEAR(created_at) — son cosas diferentes. PostgreSQL soporta índices de expresión (CREATE INDEX ON orders (YEAR(created_at))) como solución alternativa, pero la reescritura de la consulta es más limpia y funciona en todos los entornos.

Índices cubriendo para rutas de lectura intensas

A índice cubriendo incluye todas las columnas que necesita una consulta, así que la base de datos nunca tiene que leer las filas reales — responde completamente desde el índice. Esto puede ser significativamente más rápido que un índice normal en tablas de alta lectura:

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

Patrones de LIKE

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

Los prefijos iniciales no se pueden corregir con índices B-tree. Si necesitas búsqueda de sufijo o subcadena, necesitas un índice de búsqueda completa (FULLTEXT en MySQL, GIN con pg_trgm en PostgreSQL) o un sistema externo de búsqueda como Elasticsearch.

N+1 consultas

Un índice no corrige un problema de N+1. Si tu ORM realiza una consulta para obtener 200 órdenes, seguido de una consulta separada por cada orden para obtener el nombre del cliente, tienes 201 consultas. Cada una podría ser rápida. El problema es el volumen. Solúcelo con un JOIN o una consulta en lote — ninguna cantidad de índices cambia el número de consultas.

Una cosa más: limpia la consulta antes de analizarla

Antes de ejecutar EXPLAIN en una consulta lenta, ayuda tener la consulta formateada claramente. Una línea de 200 caracteres sin indentación es más difícil de razonar que una consulta estructurada. El Formateador de SQL en IO Tools maneja MySQL, PostgreSQL, SQL Server y otros dialectos — útil cuando pegas una consulta de logs o de salida de ORM y necesitas ver su estructura de forma rápida.

La lista práctica

• Ejecuta EXPLAIN antes de añadir cualquier índice. Confirma el problema primero.
• Busca type: ALL (MySQL) o Seq Scan (PostgreSQL) — normalmente donde está el problema.
• Revisa la rows estimación en EXPLAIN. Millones de filas escaneadas para devolver decenas = índice faltante.
• En índices compuestos: condiciones de igualdad primero, condiciones de rango al final.
• Las columnas de mayor selectividad van antes en un índice compuesto.
• No indexa columnas de baja cardinalidad sin verificar que el optimizador realmente use el índice.
• Revisa regularmente índices inutilizados: pg_stat_user_indexes (PostgreSQL), sys.schema_unused_indexes (MySQL).
• Si una función envuelve tu columna en WHERE, reescribe la consulta en lugar de indexar la función.
• Para rutas de lectura intensas con un pequeño conjunto de columnas, considera índices cubriendo.
• N+1 es un problema de arquitectura de consultas, no de índices.

También te puede interesar