Nem toda chave estrangeira deve ser indexada

Ylanite Koppens (Pexels)

A chave estrangeira é composta de uma ou mais colunas e permite o relacionamento entre tabelas, além de garantir a integridade referencial.
Dizem que é necessário criar índice para toda chave estrangeira…

1. Compilação de argumentos

Há alguns anos eu acompanhava uma apresentação online sobre sql server, tão comuns antes da pandemia de covid. Não me recordo do assunto mas me lembro vagamente do grupo de  palestrantes. Transmitida pelo youtube, no chat eram postadas perguntas por quem também assistia. Em uma delas alguém perguntou para um dos palestrantes se era necessário criar índices para todas as chaves estrangeiras. Eu. abelhudo, respondi que não. Pouco depois o palestrante responde no chat que “sim, para todas elas”. Ops! Fiquei calado sobre o assunto e não dei mais nenhum pitaco na apresentação, pois o palestrante é um dos gurus de sql server no Brasil. Mas não mudei de opinião…

Sou contra ficar criando índices nonclustered para tudo. É óbvio que índices nonclustered, quando criados com análise técnica do contexto, melhoram o desempenho de consultas sql. Entretanto, todo índice nonclustered tem seus pontos negativos: aumento no espaço físico ocupado pelo banco de dados; necessidade do database engine manter o índice atualizado a cada alteração no banco de dados que tenha relação direta com o índice; aumento no tamanho do arquivo (e tempo de duração) de backup do banco de dados; aumento no tempo das rotinas de manutenção de índices; etc.

Algum tempo após resolvi pesquisar sobre o assunto e percebi que eu não era o único que pensava assim (isto é, nem sempre se deve criar índices para todas as chaves estrangeiras). Quem? Ao longo do artigo o nome dele será mencionado, mas de imediato informo que ele é bem conceituado na comunidade sql server internacional.

Nota. Este primeiro capítulo contém vários links que levam ao respectivo artigo mencionado no texto do parágrafo ou frase. A sugestão é que primeiro este capítulo seja lido sem clicar em um único link mas, chegando ao final do capítulo, então relê-lo e ir clicando em cada artigo mencionado, aprofundando então no estudo.

Nas últimas semanas me deparei com dois artigos recentes publicados sobre o assunto. O primeiro, publicado no blog SQL Land por Etienne Lopes, The “hidden” benefits of Indexes in Foreign Key columns, em que o autor inicia com a seguinte pergunta: “Do you think that it can be beneficial to have a single column index for the foreign key column in the child table?” e considera 3 possíveis respostas: nunca; sempre; e depende.

Casos em que é inútil. Para os que acham que “nunca”, ele relaciona alguns fatos que podem favorecer essa resposta, tratando dos casos de:

• • join elimination (vide artigo Chave estrangeira untrusted X otimizador de consulta); e que
  • embora chaves estrangeiras sejam frequentemente utilizadas em junções, ter índice somente pela chave estrangeira não traz benefícios nas junções (vide artigo Índices de cobertura).

E ele faz uma detalhada explanação sobre os dois pontos acima, demonstrando que eles não são motivos para criar índices pelas chaves estrangeiras. Aliás, o que me parece é que Etienne Lopes escreveu um artigo sobre join elimination dentro do artigo sobre índices em chaves estrangeiras: And now Ladies and Gentlemen, time for the demo where Join Elimination will happen 🙂

A explanação sobre os 2 pontos acima termina no parágrafo “So, this proves that having an index on the foreign key column(s) (alone) by itself will not bring benefits when that column is simply used in the JOIN predicate in SELECT clauses”.

Índices não (ou raramente) utilizados. Antes de tratar do argumento que Etienne Lopes escreveu a favor da criação de índice nas chaves estrangeiras, vamos abrir um parênteses para o artigo “Which is Worse: an Unused Index, or an Un-Indexed Foreign Key?”, de Kendra Little. Nesse artigo ela trata de caso em que são citados dois relatórios: (1) tabelas com chaves estrangeiras mas sem os respectivos índices das chaves estrangeiras; e (2) índices que não são utilizados. Mas o principal é o que ocorre quando se depara com o fato de que no relatório de índices não utilizados aparecem vários índices de chaves estrangeiras que foram criados para atender a recomendação “indexar todas as chaves estrangeiras”! Temos aqui um cachorro correndo atrás do próprio rabo?

Problemas de desempenho. Outra situação em que não foram criados índices para todas as chaves estrangeiras é relatado no artigo “Unindexed Foreign Keys Can Make Performance Worse”, em que o autor comenta que “Em um compromisso recente, simplesmente não pudemos usar essa solução. Eles já tinham sérios problemas de desempenho durante as inserções: o hardware não suportava nenhuma lentidão adicional durante as inserções e atualizações, e eles já tinham problemas extensos de bloqueio e gravação”. Quem comentou isso foi Brent Ozar e ele terminou o artigo com o seguinte parágrafo: “Do I wish every database had perfect data, perfect foreign keys, perfect indexes, and perfect hardware to sustain all of the load? Absolutely. But in real life, constraints aren’t black and white: they’re fifty shades of … oh, you know”.

Casos em que índices fazem diferença. Retornando ao artigo de Etienne Lopes, a seguir ele nos traz exemplo em que é removida uma linha em duas tabelas, ambas com mesma estrutura e mesmo conjunto de dados. Na primeira tabela, T1, o apagamento foi imediato mas na outra, TB1, demorou 5 segundos; qual o motivo? Continuando a leitura, tomamos então conhecimento de que ambas tabelas possuem coluna que é referenciada como chave estrangeira em várias outras tabelas, sendo que no caso de T1 todas as demais tabelas que possuem chave estrangeira referenciando T1 estão com índice nas chaves estrangeiras mas no caso de TB1 as demais tabelas não possuem índice nas chaves estrangeiras. E ilustra o artigo com os planos de execução das duas situações: uma imagem vale mais do que mil palavras.

E não terminou aí. Para reforçar a necessidade de índice nas chaves estrangeiras, ele mostra o que ocorre se as colunas definidas como chave estrangeira estão também definidas com DELETE CASCADE: Well, as shown in the above execution plan (“Table Delete” operator), SQL Server will now automatically delete child rows and what does this imply?… Exactly, LOCKS!

Achei detalhado o artigo do Etienne Lopes, principalmente comparando com outros sobre o mesmo assunto. Recomendo a leitura, e com atenção especial nessa parte que trata do DELETE e UPDATE e a falta que faz ter chaves estrangeiras indexadas, nesses casos.

Jogando mais lenha na fogueira. E não podia deixar de consultar o blog do Gustavo Maia Aguiar, em um artigo dele de 2012. Vou somente pinçar um parágrafo do artigo Índices e Foreign Keys: “A conclusão que chego é provavelmente a que os fabricantes já chegaram, ou seja, como não sabemos o comportamento da seletividade dos valores nas colunas da chave estrangeira, não há como afirmar se a criação de índices em chaves estrangeiras é ou não um bom negócio”.

E lá também é assim! Enquanto pesquisava artigos sobre o assunto me deparei com um sobre o Oracle Database: “Devo criar índices nas colunas das FKs?”, publicado no que entendi ser a página oficial de recursos técnicos da Oracle. Entretanto, a formatação está estranha mas no sítio web do autor, Fábio Prado, a apresentação está legível: Devo criar índices nas colunas das FKs?

De imediato o autor daquele artigo opina que “Se índices nas FKs (foreign keys ou chaves estrangeiras) fossem úteis em 100% dos casos, certamente existiria um recurso no Oracle que faria a criação deles automaticamente, na respectiva coluna da FK, durante ou após o seu processo de criação”. Mas, ao longo do artigo, ele alerta que “a Oracle diz que as tabelas que contém colunas de FKs não indexadas estão mais propensas a ter uma maior quantidade de locks, e até mesmo deadlocks”.

ERP. Há pouco tempo tomei conhecimento da situação de um banco de dados de um ERP (ou seja, centenas de tabelas) em que haviam chaves estrangeiras não indexadas. Ao analisar as chaves estrangeiras, obteve-se a informação de que em um dos clientes do ERP esse banco de dados possuía centenas de chaves estrangeiras ativas, das quais mais de 80% não estavam indexadas; e boa parte definidas com DELETE CASCADE. Uau! Nesse caso, se fosse criar índices para todas as chaves estrangeiras, seriam centenas de índices nonclustered adicionais. Imagina o impacto nesse banco de dados, tanto na parte de espaço físico adicional quanto na de manter os índices atualizados.

Mas nesse cliente do ERP também foi observado que metade das chaves estrangeiras estava em tabelas que não possuíam nenhuma página alocada; em resumo, tabelas vazias. Isto é comum em ERP em que o cliente ativa somente os módulos que utilizará, fazendo com que parte do banco de dados não seja utilizado.

Além disso, no caso desse ERP o que se observou é que raramente são efetuados apagamentos físicos, por causa das informações históricas que devem ser mantidas por determinado período para atender a legislação fiscal. Por exemplo, um produto que não é mais vendido não é apagado da tabela de produtos mas somente marcado como inativo. Assim, nesse caso percebeu-se que não seria necessário criar índice para todas as chaves estrangeiras, pois no dia a dia não há DELETE nas tabelas principais. Exceto nas rotinas periódicas de limpeza.

Erik Darling. Quando já havia encerrado a parte de compilação de artigos resolvi ler artigo de Erik Darling, da época em que ele trabalhava como consultor junto com Brent Ozar (e outros). Percebi então que não era somente um mas sim uma série de 5 artigos sobre chaves estrangeiras.

À medida que lia o artigo 4, cujo título é “How to Index Foreign Keys”, percebia semelhanças com as observações iniciais do artigo de Etienne Lopes. Para não estender este artigo, deixo no capítulo 4 o link para o primeiro artigo da série “Adventures In Foreign Keys”; nele constam os links para os demais artigos daquela série.

2. Quando, então?

Uma das definições de paradigma é a representação de um modelo a ser seguido. E, geralmente, paradigmas existem para serem quebrados.

3. Programa para gerar os índices ausentes

Enquanto estudava o caso do ERP mencionado anteriormente, desenvolvi programa para relacionar as chaves estrangeiras existentes, situação (ativa ou não, confiável ou não, etc), se há índice para a coluna que é chave estrangeira, etc.. E acrescentei nele a opção de gerar/executar os comandos sql de criação dos índices. Foi um bom exercício para aprofundar nos metadados de um banco de dados no sql server.

Testei, funcionou. Mas não gostei, pois era tudo ou nada: ou gerava os comandos de criação dos índices para todas as chaves estrangeiras não indexadas ou não gerava nada. Aprimorei então o programa de análise de índices em chaves estrangeiras, de modo que ele gerasse os comandos de criação de índices somente para determinadas situações, utilizando como critérios quantidade de páginas alocadas ou quantidade de linhas existentes em cada tabela com chave estrangeira não indexada. Melhorou, mas ainda não considero o suficiente.

O cabeçalho do programa (no momento em que este artigo foi escrito), onde constam alguns dos parâmetros de execução a serem definidos pelo usuário, é o seguinte:

-- Análise de chaves estrangeiras declaradas (e ativas) e respectivos índices na tabela

-- define o modo de execução
--    modo 1: lista chaves estrangeiras declaradas
--    modo 2: gera comandos sql de criação de índices

declare @modo tinyint;
set @modo= 1;

-- controla se vai executar comandos gerados ou somente imprimi-los (somente no modo 2)
declare @opc_exec char(1);
set @opc_exec= 'N';

-- quantidade mínima de páginas ou de linhas para criação de índice (somente no modo 2)
declare @min_paginas integer, @min_linhas integer;
set @min_paginas= NULL;
set @min_linhas= NULL;

O primeiro parâmetro, @modo, define o que o programa fará, dentre duas opções: (1) emite o relatório de chaves estrangeiras ativas, no banco de dados; ou (2) gera os comandos sql para a criação dos índices nas chaves estrangeiras.

O segundo parâmetro, @opc_exec, é válido somente para o modo 2 e permite definir se os comandos gerados devem ou não serem efetivados. É útil para quando estamos avaliando as possibilidades e queremos ter noção dos índices que serão gerados.

Os terceiro e quarto parâmetros, opcionais, permitem restringir para quais chaves estrangeiras serão gerados os índices, delimitando as tabelas de origem (isto é, naquelas em que as chaves estrangeiras foram utilizadas) que tenham ao menos x páginas alocadas ou ao menos y linhas existentes.

Como teste, eis trecho inicial do relatório gerado no modo 1 para o banco de dados Adventure Works:

Na coluna “Índice” está o nome do índice da chave estrangeira, caso exista. No relatório constam também a quantidade de páginas alocadas para a tabela e o número de linhas. Há ainda informações adicionais:

Pelo relatório acima, no banco de dados Adventure Works foram encontradas 90 chaves estrangeiras ativas, das quais 61 não estão indexadas.

E a seguir os comandos sql de criação dos índices são listados, ao rodar o programa no modo 2:

Em tempos de IA, o programa precisa de um pouco mais de inteligência natural. Você tem alguma sugestão de critério para decidir, de forma automática, se o índice deve ou não ser criado? Toda sugestão é bem vinda e por favor escreva-a abaixo, ao final deste artigo.

4. Artigos relacionados

4.1 Indexação de chave estrangeira

The “hidden” benefits of Indexes in Foreign Key columns  (Etienne Lopes), 2023

Which is Worse: an Unused Index, or an Un-Indexed Foreign Key?  (Kendra Little), 2016

Unindexed Foreign Keys Can Make Performance Worse  (Brent Ozar), 2020

Índices e Foreign Keys  (Gustavo Maia Aguiar), 2012

Adventures In Foreign Keys 4: How to Index Foreign Keys  (Erik Darling) , 2018

The Benefits of Indexing Foreign Keys  (Erin Stellato), 2012

Devo criar índices nas colunas das FKs?  (Fábio Prado), 2017

4.2. Chave estrangeira

Chave estrangeira “untrusted” X otimizador de consulta 

Adventures In Foreign Keys   (Erik Darling) , 2018
série de 5 artigos sobre chaves estrangeiras