Indexación y optimización de consultas

1) Objetivos de indexación y optimización

Latencia: reducción de P50/P95/P99.
Ancho de banda: crecimiento de QPS sin escala horizontal.
Previsibilidad: planes estables y sin «saltos» de tiempo de respuesta.
Ahorro: menos IO/CPU, menos factura por nube.
Fiabilidad: reduzca los bloqueos y los desbloqueos a través de los accesos correctos.

• Cualquier optimización debe mantener la corrección y la consistencia.
• Realizar un seguimiento del efecto en métricas y logotipos de planes.

2) Estructuras básicas de los índices y cuándo aplicarlos

2. 1 B-Tree (default)

Igual a/rangos, ordenaciones, 'ORDER BY'.
Es bueno para la mayoría de los filtros de tiempo/ID/estado.

2. 2 Hash

Pure Equality ('='), más barato de memoria, pero sin orden (PG: se eliminan las restricciones, pero sigue siendo una opción de nicho).

2. 3 GIN / GiST (PostgreSQL)

GIN: matrices/claves JSONB, texto completo (tsvector), contenido ('@>').
GiST: geo, rangos, kNN.

2. 4 BRIN (PostgreSQL)

Índice súper barato por mesas «naturalmente ordenadas» (append-only por tiempo). Bueno para series de tiempo con tablas grandes.

2. 5 Bitmap (MySQL/InnoDB: no nativo; DW-SUBD/OLAP)

Son eficaces para la baja cardinalidad y facetas, más a menudo en bóvedas de columnas.

2. 6 Índices de columna (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
Solicitudes OLAP con agregaciones y rangos.

2. 7 Índices invertidos (Elasticsearch/OpenSearch)

Texto completo, facetas, búsqueda híbrida. Para filtros precisos, utilice los campos clave y doc values.

2. 8 MongoDB

Single, compound, multikey (matrices), partial, TTL, text, hashed (para charding en clave uniforme).

3) Diseño de llaves e índices compuestos

3. 1 Regla «prefijo izquierdo»

El orden de los campos del índice determina la usabilidad.
La consulta 'WHERE tenant_id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Añade una cola única (normalmente 'id') para ordenar y ver la paginación de forma estable.

3. 3 Índices parciales/filtrados

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 Índices de cobertura

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 Funcional/Computable

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) Partición y chardeo

4. 1 Partición (herencia nativa de PG/tabla; MySQL RANGE/LIST)

La rotación de lotes por tiempo ('daily/weekly') simplifica 'VACUUM/DELETE'.
Los índices son lotes locales → menos B-Tree, plan más rápido.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 Clave de partición

En OLTP, por 'tenant _ id' (localización de carga).
En series de tiempo/OLAP - por 'ts' (consultas de rango).
Híbrido: '(tenant_id, ts)' + subparticiones.

4. 3 Charding

Consistent hashing/range-shard por 'tenant _ id' o por tiempo.
Consulta cruzada → scatter-gather y k-way merge; Mantenga el cursor per-shard.

5) Estadísticas, cardinalidad y planes

5. 1 Estadísticas actualizadas

Habilite el análisis automático ('autovacuum/autoanalyze'), aumente 'default _ statistics _ target' para las distribuciones 'sucias'.

5. 2 Estadísticas avanzadas (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 Plan de ejecución

• `Rows`, `Loops`, `Actual time`, `Shared Read/Hit`, `Recheck Cond`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Estabilidad de planes

La parametrización (estados preestablecidos) puede «verter» en un mal plan. Utilice el plan cache guardrails (PG: 'plan _ cache _ mode = force_custom_plan' para consultas problemáticas) o el «probador» de constantes.

6) Optimización de join-in y ordenaciones

6. 1 Estrategias

Nested Loop: pequeño externo, índice rápido en el interior.
Hash Join: conjuntos grandes, agarra la memoria debajo de la mesa de hash.
Merge Join: entradas ordenadas, ventajosas con el orden ya disponible.

6. 2 Índices bajo join

Para 'A JOIN B ON B.a_id = A.id' → el índice en 'B (a_id)'.
Para el filtro post-join: índice en las columnas del filtro de la tabla interna.

6. 3 Ordenaciones

Evite 'ORDER BY' sin el índice correspondiente; ordenar en conjuntos grandes el camino de memoria/disco.

7) Reescritura de solicitudes (query rewrite)

Deshacerse de los «copos de nieve» de las sub-estaciones; desplegar en JOIN.
Utilice CTE-inline (PG ≥12 inlines CTE por defecto, pero 'MATERIALIZED' puede confirmar el resultado intermedio si es necesario).
Retire 'SELECT' → enumere los campos (ahorro de IO/red).
Transfiera los cálculos de 'WHERE' a la forma indexada (columnas anticipadas).
Agregaciones: tablas resumidas preliminares/representaciones materializadas con actualización incremental.

8) Batcheo, limitación y paginación

Batch-insert/update: paquetes 500-5000 en lugar de piezas.
Seek-paginación por '(sort_key, id)' en lugar de 'OFFSET' profundo.
Limite el conjunto antes de ordenar/joyne (push-down 'LIMIT').

9) Almacenamiento en caché y denormalización

Query-cache de nivel de aplicación (clave = SQL + bind-vars + versión de derechos).
Vistas materializadas para unidades pesadas; plan de rotación/refresco.
Denormalización: almacena campos computables de lectura frecuente (precio con descuento), pero con un disparador/tarea de fondo para consistencia.
Redis como L2 para llaves «hot» (con TTL y discapacidad por eventos).

10) Especificidad de los motores populares

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, INCLUDE.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

Estadísticas por histogramas ('ANALYZE TABLE... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Clave principal = ordenar; 'ORDENAR POR (tenant_id, ts, id)'.

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

Utilice 'hint ()' para diagnosticar, siga 'covered query'.

10. 5 Elasticsearch/OpenSearch

Keyword vs text campos; doc_values para ordenar/agregados.
Segmentación heap: agregaciones - heavy; limite el 'tamaño' y utilice la agregación 'composición' (muestra por página).
No incluya analizadores donde se requiera una comparación precisa.

11) Competencia, bloqueo y MVCC

Transacciones cortas; evitar lecturas «largas» bajo 'LECTURA REPEATABLE' sin necesidad.
Las operaciones de índice también toman bloqueos (reducción de la escritura a través de la entrada).
Planifique la indexación en línea: 'CREATE INDEX CONCURRENTLY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Insertar en la cola por hora/ID → «páginas calientes» del índice; distribuir la llave (UUIDv7/sal).

12) Observabilidad y SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) por el nombre de la consulta.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• Una fracción de los planes con 'Seq Scan' donde se esperaba 'Index Scan'.
• alertas de retroceso cuando se cambia la versión/los parámetros del DBM.

• Habilite el registro de consulta lenta con un umbral (por ejemplo, 200 ms).
• Correlación de solicitudes con spans (trace_id).
• Retire los planes de consultas problemáticas y guárdelos en el almacén de objetos para una retrospectiva.

• P95 de lecturas '<= 150 ms' en' LIMIT <= 50 'y tenante' caliente '.
• P95 entradas '<= 200 ms' en batches de hasta 1000 líneas.

13) Seguridad y multi-tenencia

Los índices por campos de control de acceso ('tenant _ id', 'owner _ id') son obligatorios.
Las directivas (RLS/ABAC) deben ser pre-filtros; de lo contrario, el optimizador planea incorrectamente.
No indexe campos sensibles en vista abierta; use hashes/tokens.

14) Anti-patrones

Un 'OFFSET' profundo sin una alternativa de seek-cursor.
«Un índice para todo» - sobrecarga de memoria y escritura-path.
'SELECT' en caminos críticos.
Funciones sobre la columna en 'WHERE' sin índice funcional.
Planes inestables debido a las estadísticas antiguas.
Ausencia de 'ORDER BY' en espera de un orden estable.
Índices en aras de los índices: ROI <0 debido a su costoso registro/soporte.

15) Lista de verificación de implementación

1. La N superior de las consultas por QPS y tiempo → seleccionar 3-5 candidatos.
2. Retire los planes de 'EXPLAIN ANALYZE', verifique la cardinalidad vs real.
3. Diseñar índices: orden de campos, INCLUDE/parcial/funcional.
4. Implementar partición para tablas grandes (claves de tiempo/sombra).
5. Reescribir consultas: quitar 'SELECT', insertar CTE simples, limitar el conjunto.
6. Habilitar batching y paginación seek.
7. Configurar caché: L1/L2, discapacidad por eventos.
8. Introducir el monitoreo de planes y slow-logs, alertas de retrocesos.
9. Realizar pruebas de carga con distribución de datos real.
10. Actualizar las líneas de datos para el desarrollo (hints ORM, indexación, límites).

16) Ejemplos «antes/después»

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) FORM y protocolos API

Evitar N + 1: muestras codiciosas ('includes', 'JOIN FETCH', 'preload').
Proyecciones explícitas de campos, paginate con el cursor.
gRPC/NAT: limite 'page _ size', fije 'sort', use tokens opacos.
Plan de caché: utilice la parametrización; no genere SQL 'únicos' por cada llamada.

18) Migración y explotación

Agregue índices en línea y etiquete como INVISIBLE/CONCURRENTLY, pruebe los planes, luego cambie.
Las revisiones de los índices son limpieza sanitaria regular: duplicados, sin usar, «muertos» para los viejos fiches.
Plan de rotación de lotes (drop de antaño) y horario 'VACUUM/OPTIMIZE'.

19) Resumen

La optimización de consultas es ingeniería de sistemas: claves e índices correctos, planes ordenados, lotes y charding bien pensados, disciplina en consultas y ORM, almacenamiento en caché y observabilidad. Al cumplir con los patrones descritos, obtendrá un sistema rápido, predecible y rentable, resistente al crecimiento de datos y carga.