Indexação e otimização de consultas

1) Metas de indexação e otimização

Latência: P50/P95/P99.
Largura de banda: crescimento QPS sem escala horizontal.
Previsibilidade: planos estáveis e falta de «saltos» do tempo de resposta.
Poupança: menos IO/CPU, menos conta de nuvem.
Confiabilidade: redução de bloqueios e dedos por meio de acessos corretos.

• Qualquer otimização deve manter a correta e coerente.
• Monitorar efeitos em métricas e logs de planos.

2) Estruturas básicas de índice e quando aplicá-los

2. 1 B-Tree (default)

Igual a/intervalo, triagem, 'ORDER BY'.
Bom para a maioria dos filtros de tempo/ID/status.

2. 2 Hash

Igualdade pura ('='), mais barata em memória, mas sem ordem (PG: limitações removidas, mas ainda escolha de nicho).

2. 3 GIN / GiST (PostgreSQL)

GIN: matrizes/chaves JSONB, zvector, containment ('@>').
Geo, faixa, .

2. 4 BRIN (PostgreSQL)

Índice super barato para mesas «naturalmente ordenadas» (append-only horário). Bom para time-series com grandes tabelas.

2. 5 Bitmap (MySQL/InnoDB: sem natividade; DW-SUBD/OLAP)

Eficientes para a baixa cardealidade e facetas, mais frequentemente em armazéns de coluna.

2. 6 Índices de coluna (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
Consultas OLAP com agregações e faixas.

2. 7 Índices invertidos (Elasticsearch/OpenSearch)

Cheio, facetas, busca híbrida. Para filtros precisos, use os campos keyward e doc values.

2. 8 MongoDB

Single, compound, multikey (matrizes), partial, TTL, text, hased (charding por chave uniforme).

3) Design de chaves e índices compostos

3. 1 Regra de prefixo esquerdo

A ordem de campo no índice determina a utilização.
Solicitação 'WHERE tenant _ id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Adicione uma cauda única (normalmente 'id') para uma triagem estável e paginação seek.

3. 3 Índices parciais/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 Funcionais/Computáveis

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

4) Particionamento e charding

4. 1 Particionamento (PG nativo/herança de tabela; MySQL RANGE/LIST)

A rotação do tempo ('daily/weekly') simplifica 'VACUUM/DELETE'.
Os índices de partituras locais são menores do que B-Tree, mais rápido que o plano.

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

4. 2 Chave de partilha

No OLTP, por 'tenant _ id' (localização de carga).
Em time-series/OLAP, por 'ts' (solicitações de intervalo).
Híbrido: '(tenant _ id, ts)' + subparticipações.

4. 3 Charding

Consent hasing/range-shard por 'tenant _ id' ou hora.
Pedido de cross-shard → scatter-gather e k-way merge; mantenha o per-shard cursor.

5) Estatísticas, cardealidade e planos

5. 1 Estatísticas atuais

Ative a análise automática ('autoaquum/autoanalyze') e aumente 'default _ estatísticos _ target' para as distribuições 'sujas'.

5. 2 Estatísticas Avançadas (PG)

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

5. 3 Plano de execução

• `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 Estabilidade de planos

A configuração (prepared statents) pode ser um plano ruim. Use o plano cachê (PG: 'plano _ cachê _ modo = force _ custom _ plan' para consultas problemáticas) ou o 'espaço' das constantes.

6) Otimizar join-ov e triagens

6. 1 Estratégias

Nested Loop: Pequenos externos, índice rápido no interior.
Hash John: kits grandes, memória suficiente para baixo da tabela hash.
Merge Join: Entradas ordenadas, beneficiadas na ordem existente.

6. 2 Índices sob join

Para 'A JOIN B ON B.a _ id = A.id' → o índice em 'B (a _ id)'.
Para o filtro pós-join, é o índice nas colunas de filtro da tabela interna.

6. 3 Triagens

Evite 'ORDER BY' sem o índice adequado; triagem em grandes conjuntos de caminho por memória/disco.

7) Reescrever solicitações (query rewrite)

Livrem-se dos flocos de neve dos subprodutos; desenrolar para o JOIN.
Use o CTE-inline (PG in CTE padrão, mas 'MATERIALIZED' pode fixar o meio-termo, se necessário).
Limpar 'SELECT' → listar os campos (economizar IO/rede).
Transfira os cálculos de 'WHERE' para a forma indexada (colunas pré-fixadas).
Agregações: tabelas totais prévias/visualizações materializadas com atualização aumentada.

8) Batching, limitação e paginação

Batch-insert/update: maços 500-5000 em vez de uma coisa.
Seek-paginação por '(port _ key, id)' em vez de 'OFFSET' profundo.
Limite o conjunto antes da triagem/joyn (push-down 'LIMIT').

9) Cachagem e desnormalização

Query-cache nível de aplicação (chave = SQL + bind-vars + versão de direitos).
Materializador views para unidades pesadas; plano de rotação/refresco.
Denormalização: guarde campos computáveis frequentemente lidos (preço com desconto), mas com tarefas de desenho/fundo para consistência.
Redis como L2 para chaves «quentes» (com TTL e deficiência por evento).

10) Especificidades de 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

Estatísticas sobre histogramas ('ANALYZE PLACE... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Chave primária = triagem; 'ORDER BY (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 } } });

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

10. 5 Elasticsearch/OpenSearch

Keyword vs text campos; doc _ values para ordenar/agregar.
Segmentação heap: agregações - heavy; limite «size» e use «composite» de agregação (amostra de páginas).
Não inclua analisadores onde você deseja comparar com precisão.

11) Competição, bloqueio e MVCC

Transações curtas; evite leituras «longas» sob 'REPEATABLE READ' sem necessidade.
Transações de índice também tomam bloqueios (redução do write throughput).
Planeje uma indenização online: 'CREATE INDEX CONCURRENTLY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Inserções na cauda por hora/ID → «páginas quentes» do índice; distribuir a chave (UUIDv7/sal).

12) Observabilidade e SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) pelo nome da solicitação.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• Parte dos planos com 'Seq Scan' onde era esperado 'Index Scan'.
• Alertas de regresso quando a versão/parâmetros do SUBD é alterada.

• Inclua slow query jobs com limiar (por exemplo, 200 ms).
• Correlação de consultas com spans (trace _ id).
• Retire os planos de solicitação problemáticos e mantenha o armazenamento de objetos para retrospectiva.

• P95 leituras '<= 150 ms' com 'LIMIT <= 50' e tenante 'quente'.
• P95 gravações '<= 200 ms' com batches de até 1.000 linhas.

13) Segurança e multi-tenência

Os índices dos campos de controle de acesso ('tenant _ id', 'owner _ id') são obrigatórios.
As políticas (RLS/ABAC) devem ser pré-filtro; senão o otimista está a planear mal.
Não indexe campos sensíveis em forma aberta; use hashi/tokens.

14) Anti-pattern

'OFFSET' profundo sem alternativa de cursor seek.
«Um índice para tudo» - sobrecarga de memória e write-path.
'SELECT' em caminhos críticos.
Funções acima da coluna em 'WHERE' sem índice funcional.
Planos erráticos por causa de estatísticas antigas.
Falta de 'ORDER BY' enquanto espera ordem estável.
Para os índices: ROY <0 devido à gravação/suporte caro.

15) Folha de cheque de implementação

1. Top N solicitações QPS e horário → escolher 3-5 candidatos.
2. Retirar os planos de 'EXPLAIN ANALYZE', verificar a vitalidade vs real.
3. Projetar índices: ordem de campos, INCLUÍDO/partial/functional.
4. Implantar a partilha para tabelas maiores (chaves temporárias/tenantes).
5. Reescrever as solicitações: remover 'SELECT', ignorar CTE simples, limitar o conjunto.
6. Activar batching e paginação seek.
7. Configure L1/L2, deficiente por evento.
8. Introduza monitoramento de planos e slow-logs, alertas para regravações.
9. Fazer testes de carga com distribuição real de dados.
10. Atualizar Heidline para desenvolvimento (Hunts ORMs, indexação, limites).

16) Exemplos de antes/depois

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) ÓRM e protocolos API

Evite N + 1: amostras avessas ('incluses', 'JOHN FETCH', 'pretoad').
Projeções de campo explícitas, paginate com o cursor.
gRPC/REST: Limite «page _ size», fixe «sort», use tocadores opacos.
Plano-dinheiro: use a configuração; não gere um SQL «único» para cada chamada.

18) Migração e exploração

Adicione os índices online e pontue como INVISÍVEL/CONCURRENTLY, teste os planos e altere.
As revisões dos índices são de saneamento regular, duplicados, não usados, «mortos» para os antigos fichas.
Plano de rotação de partições (drop antigas) e 'VACUUM/OPTIMIZE' programação.

19) Resumos

A otimização das solicitações é uma engenharia de sistema: chaves e índices corretos, planos cuidadosos, particionização elaborada e charding, disciplina nas solicitações e RM, cachê e observabilidade. Respeitando os patterns descritos, você terá um sistema rápido, previsível e econômico, resistente ao aumento dos dados e da carga de trabalho.