Estrategias de Indexación — Desde B-Trees hasta BRIN

Overview

Los índices son el mecanismo principal para acelerar consultas de bases de datos. Son estructuras de datos que permiten a la base de datos localizar filas sin escanear cada registro. Pero los índices no son gratis — consumen almacenamiento, ralentizan escrituras y pueden perjudicar el rendimiento si se usan incorrectamente. Entender los diferentes tipos de índice y cuándo aplicarlos es una de las habilidades de mayor leverage en bases de datos.

When to Use

• Consultas filtrando en columnas específicas (WHERE, JOIN)
• Ordenar grandes conjuntos de resultados (ORDER BY)
• Aplicar restricciones de unicidad
• Acelerar búsqueda full-text y consultas geoespaciales

Índices B-Tree

El tipo de índice por defecto en la mayoría de bases de datos relacionales. Los B-Trees mantienen datos ordenados que permiten búsquedas O(log n), scans de rango y recorrido ordenado.

CREATE INDEX idx_users_email ON users(email);

-- Usa índice: coincidencia exacta
SELECT * FROM users WHERE email = 'alice@example.com';

-- Usa índice: scan de rango
SELECT * FROM users WHERE email BETWEEN 'a' AND 'c';

-- Usa índice: ORDER BY
SELECT * FROM users ORDER BY email LIMIT 10;

Índices B-Tree Compuestos

El orden de columnas importa. Un índice compuesto en (a, b, c) soporta consultas en a, (a, b) y (a, b, c).

CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

-- Usa índice: coincide columna inicial
SELECT * FROM orders WHERE customer_id = 42;

-- Usa índice: coincide columnas iniciales
SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2024-01-01';

-- NO usa índice: salta columna inicial
SELECT * FROM orders WHERE created_at > '2024-01-01';

Índices Hash

Optimizados para comparaciones de igualdad únicamente. Más pequeños y rápidos que B-Trees para coincidencias exactas, pero no soportan consultas de rango ni ordenamiento.

CREATE INDEX idx_sessions_token ON sessions USING HASH(token);

-- Rápido: igualdad
SELECT * FROM sessions WHERE token = 'abc123';

-- No puede usar índice hash: rango
SELECT * FROM sessions WHERE token > 'abc';

Índices GIN (Generalized Inverted Index)

Diseñados para columnas multi-valor y búsqueda full-text. Eficientes para arrays, JSONB y tsvector.

-- Contención de arrays
CREATE INDEX idx_products_tags ON products USING GIN(tags);
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'wireless'];

-- Búsqueda JSONB
CREATE INDEX idx_events_data ON events USING GIN(data jsonb_path_ops);
SELECT * FROM events WHERE data @> '{"status": "error"}';

-- Full-text search (PostgreSQL)
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('database & indexing');

Índices GiST (Generalized Search Tree)

Un framework para construir índices sobre tipos de datos complejos: geométricos, rangos y consultas de vecinos más cercanos.

-- Geoespacial (PostGIS)
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);
SELECT * FROM locations WHERE ST_DWithin(geom, ST_Point(0,0)::geography, 1000);

-- Consultas de rango
CREATE INDEX idx_reservations_period ON reservations USING GIST(period);
SELECT * FROM reservations WHERE period && daterange('2024-01-01', '2024-01-10');

Índices BRIN (Block Range Index)

Índices compactos para tablas muy grandes y naturalmente ordenadas. Almacenan min/max por bloque en lugar de por fila.

-- Datos de series temporales: logs, eventos, métricas
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- Tamaño: ~1% de B-Tree, pero solo útil para datos ordenados
-- Mejor para: miles de millones de filas, series temporales, workloads append-only

Índices Parciales

Indexa solo un subconjunto de filas, reduciendo tamaño y mejorando rendimiento de escritura.

-- Solo indexa usuarios activos (80% de consultas filtran por activo)
CREATE INDEX idx_users_active_email ON users(email) WHERE active = true;

-- Solo indexa órdenes impagas para reportes de aging
CREATE INDEX idx_orders_unpaid ON orders(created_at) WHERE status = 'unpaid';

Índices de Cobertura (Index-Only Scans)

Incluye columnas adicionales para que la base de datos pueda responder consultas sin tocar el heap.

-- PostgreSQL: INCLUDE agrega columnas a la hoja del índice
CREATE INDEX idx_orders_customer_total ON orders(customer_id) INCLUDE(total, status);

-- La consulta usa solo el índice — sin acceso al heap
SELECT total, status FROM orders WHERE customer_id = 42;

-- MySQL: índice compuesto cubre naturalmente
CREATE INDEX idx_orders_customer_total ON orders(customer_id, total, status);

Matriz de Selección de Índices

Tipo de Índice	Mejor Para	Evitar Cuando
B-Tree	Igualdad, rango, ordenamiento	Búsqueda de texto de alta cardinalidad
Hash	Coincidencia exacta en texto largo	Consultas de rango necesarias
GIN	Arrays, JSONB, full-text	Columnas escalares simples
GiST	Geoespacial, rangos	Lookups escalares estándar
BRIN	Grandes datasets ordenados	Patrones de acceso aleatorio
Parcial	Subconjuntos frecuentemente filtrados	Consultas escanean todas las filas

Errores Comunes

• Indexar cada columna — ralentiza escrituras drásticamente; los índices tienen costo de mantenimiento
• Orden incorrecto de columnas en compuestos — la columna inicial debe ser la más selectiva
• Indexar columnas de baja cardinalidad — género, flags booleanos; los índices bitmap manejan estos mejor
• Ignorar índices parciales — indexar 100% de filas cuando las consultas siempre filtran
• No actualizar estadísticas — estadísticas obsoletas llevan a malas elecciones de índice por el query planner

Monitoreo de Uso de Índices

-- PostgreSQL: encuentra índices no usados
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_toast%'
ORDER BY schemaname, tablename, indexname;

-- MySQL: uso de índices vía performance_schema
SELECT object_schema, object_name, index_name, count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_read DESC;

FAQ

¿Cuántos índices es demasiado? No hay un número fijo, pero cada índice añade overhead de escritura. Audita índices no usados trimestralmente.

¿Debería indexar foreign keys? Sí. El lado que referencia (lado “muchos”) de una foreign key debería casi siempre estar indexado para rendimiento de JOIN.

¿Los índices ralentizan INSERT? Sí. Cada índice en una tabla añade amplificación de escritura. Considera eliminar índices durante cargas masivas.