Indexação SQLExplained

31 of 31 rows

Conceito↕	Explicação↕	Dica↕
Tipos de índice
⤢B-tree	Árvore equilibrada para igualdade, intervalos e varreduras ordenadas. O predefinido na maioria das bases de dados.	Use para =, <, >, BETWEEN, ORDER BY. Não serve para pesquisa de texto integral.
⤢Clustered	Índice cujo nível de folhas é a própria tabela. As linhas ficam armazenadas na ordem da chave. Um por tabela.	O InnoDB agrupa pela chave primária. Tabelas SQL Server sem uma são heaps.
⤢Nonclustered	Estrutura separada com chaves mais um apontador de volta à tabela base. Muitos por tabela.	Num heap guarda um row ID; numa tabela clustered guarda a chave de agrupamento.
⤢Hash	Tabela de hash apenas para procuras por igualdade. Correspondência exata em tempo constante.	Use só para operações =; intervalos e ordenação precisam de B-tree. O hash memory-optimized do SQL Server precisa de uma contagem de buckets próxima da de linhas.
⤢GIN	Generalized Inverted Index. Mapeia valores para linhas. Feito para arrays e texto integral.	Use para jsonb, arrays, tsvector, pesquisa trigram. Mais lento a construir do que B-tree.
⤢GiST	Generalized Search Tree. Extensível para tipos não escalares e consultas de sobreposição.	Use para intervalos geométricos, kNN, restrições de exclusão. O pg_trgm acelera o LIKE.
⤢SP-GiST	GiST de partição espacial. Divide os dados em regiões não sobrepostas via árvores radix ou quad.	Use para pontos, prefixos IP e correspondência por prefixo. Não para sobreposições; use GiST.
⤢BRIN	Block Range Index. Guarda mínimo/máximo por intervalo de blocos. Pegada minúscula.	Use em tabelas grandes fisicamente ordenadas. Barato de construir, seletividade fraca.
⤢Columnstore	Índice armazenado como segmentos de coluna em vez de linhas. Compressão alta, execução em lote.	Use para varreduras analíticas de muitas linhas e poucas colunas. Procuras de linha única são lentas.
⤢Full-text	Índice invertido dedicado a palavras no MySQL e SQL Server. Construído por coluna de texto.	Use MATCH AGAINST ou CONTAINS. O texto integral do Postgres usa GIN sobre tsvector.
⤢Bitmap	Índice Oracle que guarda um bitmap por valor distinto. Cada bit marca uma linha.	Use em colunas de baixa cardinalidade em armazéns de leitura intensiva. Escritas concorrentes serializam por bitmap.
⤢Descending	Coluna de índice declarada DESC. As chaves dessa coluna ficam em ordem inversa.	Necessário para ordenações mistas como (a ASC, b DESC). DESC simples também pode ler um índice normal ao contrário.
⤢Invisible	Índice MySQL 8 que o otimizador ignora. Ainda é mantido em cada escrita.	Esconda um índice antes de o apagar para testar o impacto. Torne-o visível de novo sem reconstruir.
Desenho
⤢Composite	Índice de várias colunas. As colunas iniciais têm de seguir a ordem do filtro da consulta.	Ordene primeiro igualdades, depois intervalos. Dois índices simples não substituem um composto.
⤢Partial	Índice sobre um predicado WHERE. O SQL Server chama-lhe filtered index. Cobre só as linhas correspondentes.	Indexe apenas as linhas active='t'. Mete o tamanho e o custo de escrita.
⤢Covering (INCLUDE)	B-tree com colunas extra não-chave anexadas. Varreduras só-índice evitam idas ao heap.	Junte colunas frequentemente procuradas via INCLUDE. Mantém o índice ordenável.
⤢Unique	Índice que impede valores duplicados. Rejeita inserções que colidem.	Use para chaves naturais e restrições um-para-um. UNIQUE NULL permite muitos NULLs.
⤢Expression	Índice construído sobre uma função das colunas em vez das colunas em si.	Indexe lower(email) ou date_trunc('day', created_at). A consulta tem de repetir a expressão exata.
⤢Opclass	Classe de operadores. Define como um índice compara e armazena o tipo de uma coluna.	Defina varchar_pattern_ops para LIKE 'abc%' sem dobragem de caixa. Os predefinidos servem igualdade e intervalos.
⤢Index-only scan	Plano que responde a uma consulta apenas com páginas do índice. O heap nunca é lido.	Exige toda a coluna procurada no índice ou na lista INCLUDE. O Vacuum mantém o mapa de visibilidade atual.
⤢Foreign keys	Colunas de chave estrangeira não recebem índice automático na tabela que referencia.	Indexe toda a coluna FK por onde junta ou apaga. Deletes em cascata varrem a tabela filha sem um.
Manutenção
⤢EXPLAIN	Inspetor de planos. Mostra varreduras sequenciais, escolhas de índice, estimativas de custo.	Use EXPLAIN (ANALYZE, BUFFERS). Um Seq Scan numa tabela de 10M de linhas significa índice em falta.
⤢ANALYZE	Amostra colunas da tabela para construir estatísticas do planeador. Os histogramas dirigem a escolha de índice.	Corra após cargas massivas. Estatísticas velhas causam seq scans em colunas indexadas.
⤢Over-indexing	Cada índice acrescenta sobrecarga de escrita. Inserts, updates e deletes pagam por índice.	Apague índices não usados. Acompanhe o uso em pg_stat_user_indexes.
⤢pg_stat_user_indexes	Vista Postgres do uso por índice. Conta varreduras e tuplos lidos desde o reinício das estatísticas.	Filtre por idx_scan = 0 para achar índices não usados. Reinicie contadores com pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Constrói um índice sem bloquear leituras nem escritas. Precisa de duas varreduras da tabela.	Não pode correr dentro de uma transação. Uma construção falhada deixa um índice INVALID a apagar.
⤢DROP INDEX	Remove um índice e liberta o seu armazenamento. Toma um lock exclusivo por predefinição.	Use DROP INDEX CONCURRENTLY em tabelas movimentadas. Verifique as estatísticas de uso antes de remover.
⤢REINDEX	Reconstrói um índice inchado. Recupera o espaço de tuplos mortos.	Use REINDEX CONCURRENTLY em produção. Sem ele, os locks bloqueiam escritas.
⤢Bloat	Tuplos mortos e lacunas de página que se acumulam em tabelas MVCC e nos seus índices.	Meça com pgstattuple. Faça Vacuum com frequência; REINDEX quando o espaço morto domina.
⤢Fillfactor	Percentagem de página preenchida na escrita. O espaço livre absorve updates futuros.	Baixe para 70-90 em tabelas de update intenso. Páginas cheias forçam divisões de página.
⤢CLUSTER	Reescreve a tabela fisicamente ordenada por um índice. Operação única.	Parece bem com BRIN para blocos ordenados. A ordem não se mantém em escritas futuras.