Optimizar Queries con Indexación de Bases de Datos

Habilita slow query logging: en PostgreSQL setea log_min_duration_statement = 100 para loguear queries más lentas que 100ms. En MySQL setea long_query_time = 0.1 y habilita slow_query_log. Usa pg_stat_statements en PostgreSQL para trackear frecuencia de queries y tiempo promedio de ejecución: SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20. Busca queries con seq_scan alto en tablas grandes en pg_stat_user_tables. Ejecuta EXPLAIN (ANALYZE, BUFFERS) en queries lentas para confirmar sequential scans. Herramientas como pgBadger analizan logs de PostgreSQL y generan reportes con las queries más lentas y sus planes de ejecución.

B-tree es el default y maneja igualdad (=), rango (<, >, BETWEEN) y sorting (ORDER BY). Usa B-tree para la mayoría de columnas. Hash indexes solo soportan igualdad y son más rápidos que B-tree para = pero no pueden manejar range queries o sorting — úsalos para lookup tables. GIN (Generalized Inverted Index) es para valores compuestos como arrays, JSONB y full-text search — usa GIN para queries @> (contains) en JSONB. BRIN (Block Range Index) es para datos naturalmente ordenados como time-series — almacena min/max por block range, haciéndolo tiny (kilobytes vs gigabytes para B-tree). Usa BRIN en columnas timestamp en tablas append-only. GiST es para datos geométricos y nearest-neighbor searches.

Los partial indexes indexan solo rows que matchean un WHERE clause: CREATE INDEX idx_active_users ON users(email) WHERE active = true. Son más pequeños y más rápidos de mantener que full indexes. Úsalos cuando las queries siempre filtran por una condición específica: registros soft-deleted (WHERE deleted_at IS NULL), suscripciones activas (WHERE status = 'active'), o datos recientes (WHERE created_at > '2025-01-01'). El query planner usa un partial index solo cuando el WHERE clause de la query matchea el predicado del índice. Si la query filtra por active = true pero el predicado del índice es active = true AND deleted_at IS NULL, el planner puede no usarlo — los predicados deben matchear exactamente o ser implícitos.

Query pg_stat_user_indexes en PostgreSQL: SELECT schemaname, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY indexname. Un idx_scan de 0 significa que el índice nunca se usó desde el último reset de estadísticas. Compara el tamaño del índice con pg_size_pretty(pg_relation_size('index_name')). Elimina índices no usados: DROP INDEX CONCURRENTLY idx_name. Usa CONCURRENTLY para evitar lockear la tabla durante el drop. En MySQL, query sys.schema_unused_indexes o revisa INFORMATION_SCHEMA.STATISTICS combinado con data del performance schema. Programa auditorías mensuales de índices — índices no usados por 30+ días son candidatos para eliminación. Ten cuidado con índices usados por batch jobs infrecuentes o reportes trimestrales.

Los índices de PostgreSQL acumulan dead tuples después de operaciones UPDATE y DELETE. Verifica bloat con la extensión pgstattuple: SELECT * FROM pgstattuple('index_name'). Rebuilda índices con bloat con REINDEX INDEX CONCURRENTLY idx_name — la opción CONCURRENTLY evita bloquear writes. Para índices B-tree, considera pg_repack o pg_squeeze para rebuilds online sin locks exclusivos. En MySQL, ejecuta ANALYZE TABLE para actualizar estadísticas y OPTIMIZE TABLE para rebuildar la tabla e índices. Programa reindexing durante ventanas de bajo tráfico. Monitorea la columna n_dead_tup en pg_stat_user_tables para determinar cuándo se necesita reindexing — un ratio dead-to-live arriba del 20% es un buen threshold.

El orden de columnas en composite indexes importa: el índice es usable solo cuando la query filtra en las columnas más a la izquierda. CREATE INDEX idx_a_b_c ON orders(user_id, status, created_at) soporta WHERE user_id = 1, WHERE user_id = 1 AND status = 'paid', y WHERE user_id = 1 AND status = 'paid' ORDER BY created_at. NO soporta WHERE status = 'paid' solo — user_id debe ir primero. Pon columnas de igualdad antes de columnas de rango: (status, created_at) para WHERE status = 'active' AND created_at > '2025-01-01'. Para optimización de ORDER BY, matchea el orden del índice al orden del sort. Limita composite indexes a 3-4 columnas — índices más anchos consumen más disco y memoria con returns decrecientes.

Los B-tree indexes no soportan LIKE '%pattern%' (wildcard inicial). Para suffix matching, usa un trigram index en PostgreSQL: CREATE EXTENSION pg_trgm; CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops). Query con WHERE name % 'john' o WHERE name ILIKE '%john%'. Para full-text search, crea un GIN index en una columna tsvector: CREATE INDEX idx_search ON articles USING gin(to_tsvector('english', title || ' ' || body)). Query con WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'search term'). En MySQL, usa full-text indexes: CREATE FULLTEXT INDEX idx_search ON articles(title, body) y query con MATCH(title, body) AGAINST('search term' IN NATURAL LANGUAGE MODE).

Cada índice agrega overhead a operaciones INSERT, UPDATE y DELETE — la base de datos debe actualizar cada índice en la tabla. Una tabla con 10 índices hace writes ~10x más lentos que un write solo heap. Mide el impacto en writes con scripts de benchmark: inserta 10K rows con y sin índices y compara throughput. Para tablas write-heavy (logs, events, metrics), minimiza índices — considera escribir a una tabla staging sin índices y batch-mergear a la tabla indexada. Usa COPY en PostgreSQL en lugar de INSERTs individuales para bulk loads. Considera diferir la creación de índices hasta después de bulk data loads: drop índices, load data, recreate índices. Monitorea write latency con pg_stat_user_tables — compara n_tup_ins, n_tup_upd y n_tup_del contra el tamaño de la tabla.

Los covering indexes incluyen columnas adicionales en el índice sin hacerlas parte de la search key. En PostgreSQL: CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total_amount, created_at). La query SELECT total_amount, created_at FROM orders WHERE user_id = 1 AND status = 'paid' puede servirse enteramente desde el índice (Index-Only Scan) sin tocar el heap. Las columnas INCLUDE no son parte de la B-tree sort key, así que no afectan el ordenamiento del índice — solo almacenan valores en las leaf pages. Esto reduce I/O para queries que seleccionan pocas columnas. En MySQL, usa un composite index que incluya todas las columnas seleccionadas: CREATE INDEX idx_covering ON orders(user_id, status, total_amount, created_at). El InnoDB de MySQL almacena secondary indexes con el valor del primary key, así que si el PK es id, un covering index en (user_id, status) ya incluye id en las leaf pages. Monitorea el uso de Index-Only Scan en EXPLAIN (ANALYZE, BUFFERS) — busca Heap Fetches: 0 que significa que todos los datos vinieron del índice.

Para aislamiento schema-per-tenant, cada tenant tiene su propio schema con índices idénticos — no se necesita manejo especial. Para multi-tenancy shared-schema con una columna tenant_id, prefija todos los composite indexes con tenant_id: CREATE INDEX idx_orders_tenant ON orders(tenant_id, user_id, status). Esto asegura que el planner pueda filtrar por tenant primero, reduciendo el rango del index scan. Para RLS (Row-Level Security) en PostgreSQL, el planner agrega el filtro de tenant automáticamente — asegúrate de que tenant_id sea la columna inicial de todos los índices en tablas multi-tenant. Considera partial indexes por tenant para tenants de alto volumen: CREATE INDEX idx_tenant_a_orders ON orders(user_id) WHERE tenant_id = 'tenant_a'. Esto mantiene el índice pequeño para el tenant específico. Monitorea el tamaño del índice por tenant con pg_stat_user_indexes y considera particionar la tabla por tenant_id cuando los datos de un solo tenant excedan el 20% de la tabla.