Gustavo Maia Aguiar

Boa Noite Pessoal,

Hoje vi uma dúvida interessante no fórum de SQL Server (MSDN / TechNET). A dúvida em si não era complicada. Bastava apenas identificar quais foreign keys estavam com índices criados ou não. Postarei um script para fazer isso logo mais, mas antes de propriamente apresentá-lo parei um pouco para pensar a respeito dessa necessidade. Se alguém deseja identificar quais FKs não tem índice é porque certamente pretenderá criar índices sobre elas, o que me remete a pensar se indexar todas as FKs é de fato um bom negócio. Sim, vejo muitas recomendações para fazê-lo em vários livros de performance e modelagem de dados. De fato, costuma ser uma boa coisa em muitas situações, mas se fosse bom sempre, então porque o SQL Server, o ORACLE, o DB2 e todos os outros não criam a FKs indexadas por padrão ?

Por que criar índices sobre Foreign Keys ?

A partir do momento em que uma FK é criada sobre uma coluna ou um grupo de colunas, é impositivo que essa coluna ou esse grupo possui um relacionamento com uma coluna ou grupo de colunas de outra tabela que possui essa coluna ou grupo compondo a chave primária. Uma vez que esse relacionamento é construído por essas constraints, em algum momento será necessário identificá-lo para montar as consultas e as junções (JOINs), para recuperar um determinado resultado que combine dados de duas ou mais tabelas.

As operações de JOIN de um ponto de vista sintático são bem conhecidas (INNER, OUTER, FULL, NATURAL, etc), mas fisicamente falando, elas costumam obedecer três algoritmos básicos (Nested Loops, Merge Join e Hash Join). Não vou explicar os algoritmos hoje, mas limito a dizer que não existe um melhor ou pior que o outro (embora alguns jurem que HASH JOIN não é uma boa coisa), apenas que o otimizador normalmente sabe quando é melhor utilizar um ou outro algoritmo em uma determinada situação. O fato é que a presença de índices pode auxiliar o otimizador na escolha desse algoritmo e por consequência retornar os dados mais rapidamente. Vejamos um rápido exemplo:

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

— Cria as tabelas
CREATE TABLE Pessoas (ID INT, Nome VARCHAR(50))
CREATE TABLE Saldos (DataSaldo DATE, IDPessoa INT, Valor SMALLMONEY, HashRegistro UNIQUEIDENTIFIER)

— Cria as constraints
ALTER TABLE Pessoas ADD PRIMARY KEY (ID)
ALTER TABLE Saldos ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE Saldos ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)
ALTER TABLE Saldos ADD DEFAULT NewID() FOR HashRegistro

— Insere alguns registros de pessoas
INSERT INTO Pessoas VALUES
(1,‘Vinicius’),(2,‘Wagner’),(3,‘André’),(4,‘Gilson’),(5,‘Emerson’)

— Insere vários registros fictícios de saldos
;WITH Nums (Num) As (
    SELECT 0 As Num
    UNION ALL SELECT Num + 1 FROM Nums
    WHERE Num < 4500)

INSERT INTO Saldos (DataSaldo, IDPessoa, Valor)
SELECT DATEADD(d,N1.Num,‘2000-01-01’) As Data, N2.Num + 1 As IDPessoa, 1000 As Valor
FROM Nums As N1
CROSS JOIN Nums As N2
WHERE N2.Num <= 2
ORDER BY Data, IDPessoa
OPTION (MAXRECURSION 0)

— Insere um registro de saldo
INSERT INTO Saldos VALUES (‘2012-08-15’,4,5000, NEWID())

— Cria um índice sobre a coluna IDPessoa na tabela Saldos
CREATE INDEX IX_Pessoa ON Saldos (IDPessoa)

Agora que a tabela está criada, vejamos uma consulta que traga o nome e os registros de saldo do primeiro cliente:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 4

O plano de execução é exibido logo abaixo:

Podemos ver que primeiro o índice IX_Pessoa foi utilizado para pesquisar os registros na tabela de Saldo com IDPessoa = 4 retornando um ponteiro para o único registro existente na tabela de Saldos com essa condição. Posteriormente, após achar os ponteiros no índice, foi feita uma operação de Lookup contra a tabela Saldos e a junção do ponteiro do índice IX_Pessoa com os registros foram consolidadas em uma operação de junção através do algoritmo Nested Loop. Para juntar os registros da tabela de pessoas com a tabela de Saldos, foi feita uma outra operação de JOIN através do algoritmo Nested Loops e por fim o resultado foi devolvido (muito rapidamente por sinal). Podemos ver que nesse caso, o uso do índice foi muito benéfico para acelerar a pesquisa e o resultado corrobora com a tese de que indexar FKs é uma boa idéia.

Uma segunda opinião

E se mudássemos a consulta ? E se ao invés de escolher IDPessoa = 4 escolhéssemos IDPessoa = 1 ? Vejamos:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

O plano de execução é exibido logo abaixo:

Dessa vez os resultados foram um pouco diferentes da consulta anterior. Podemos ver que na tabela Pessoa, houve uma pesquisa (Seek) para recuperar a pessoa de ID = 1, mas na tabela Saldos, houve uma varredura (Scan) da tabela inteira (Clustered Index pq ela está clusterizada). O otimizador concluiu que era melhor ignorar o índice IX_Pessoa, pois, para favorecer a condição IDPessoa = 1, seria mais fácil ler a tabela inteira do que ir no índice e ir na tabela registro a registro. Se formos analisar por esse resultado, a criação de índices em FKs só tem a trazer desvantagens. O índice IX_Pessoa na tabela de saldos irá retardar as operações de escrita, irá ocupar mais espaço e não servirá para atender as consultas que utilizem a coluna IDPessoa. No entanto, será que o índice não serve mesmo para nada ?

SELECT P.Nome, COUNT(*) FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 1
GROUP BY P.Nome

O plano de execução é exibido logo abaixo:

Embora o índice IX_Pessoa não seja útil para filtrar registros na tabela Saldos onde IDPessoa = 1, o fato de existir um índice  e o objetivo ser apenas uma contagem e não uma exibição das colunas, faz com que a consulta tenha de ser feita apenas sobre o índice e nesse caso, foi possível até fazer um seek e posteriormente uma agregação para posteriormente efetuar um JOIN com a tabela Pessoas. Podemos ver que o índice IX_Pessoa não é tão ruim afinal.

O papel do índice em relacionamentos fortes ou identificados

Um relacionamento é caracterizado pela presença de uma PK em sua tabela Pai e uma FK referenciado as colunas da PK em uma tabela filha. Diz-se que o relacionamento é forte ou identificado quando as colunas da FK também fazem parte da PK na tabela filha. Vejamos o papel que o índice pode desempenhar nesse caso.

— Efetua duas cópias da tabela de Saldos
SELECT IDPessoa, DataSaldo, Valor, HashRegistro
INTO Saldos2 FROM Saldos
ORDER BY IDPessoa, DataSaldo

SELECT IDPessoa, DataSaldo, Valor, HashRegistro
INTO Saldos3 FROM Saldos
ORDER BY IDPessoa, DataSaldo

— Cria as constraints (PK apenas em Saldos2)
ALTER TABLE Saldos2 ADD PRIMARY KEY (IDPessoa, DataSaldo)
ALTER TABLE Saldos2 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)
ALTER TABLE Saldos3 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)

— Cria um índice em Saldos3
CREATE INDEX IX_Pessoa ON Saldos3 (IDPessoa)

Agora vejamos o impacto nas consultas que fizemos anteriormente:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos2 As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos3 As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

O plano de execução é exibido logo abaixo:

No primeiro caso, podemos ver que o fato da coluna IDPessoa ser a primeira coluna da chave primária (que por padrão é clusterizada) faz com que a leitura dos registros da tabela Saldos2 ainda seja uma pesquisa (Seek), pois, a chave primária composta por IDPessoa e DataSaldo impõe uma ordem dos registros por IDPessoa. Já no caso da tabela Saldos3, como não há nenhuma ordem e muitos registros para IDPessoa = 1, o índice é simplesmente ignorado e a tabela é completamente varrida (Table Scan). Proporcionalmente, podemos ver que a consulta em Saldos3 é duas vezes maior que em Saldos2 (67% vs 33% no mesmo Batch).

O papel do índice nas operações de exclusão

Até agora tivemos um foco muito grande no uso do índice nas FKs para consultas, mas ele tem papel nas operações de exclusão ou atualizações em cascata ?

— Efetua uma cópia da tabela de Saldos
SELECT * INTO Saldos4 FROM Saldos

— Cria as constraints
ALTER TABLE Saldos4 ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE Saldos4 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)

DELETE FROM Pessoas WHERE ID = 1

O plano de execução é exibido logo abaixo:

Podemos ver que a exclusão ocorre na tabela Pessoas representada pela operação Clustered Index Delete. Também podemos ver que é feita uma verificação da existência de registros filhos nas tabelas Saldos, Saldos2, Saldos3 e Saldos4. Podemos ver que em todas as tabelas filhas é feita uma pesquisa para checagem da existência (algo muito parecido com a consulta que fez a contagem de registros nos exemplos anteriores). Especificamente na Saldos4, podemos ver que é feita uma varredura.

A varredura na tabela Saldos4 é feita porque não há como saber se existem registros para IDPessoa = 5 senão lendo a tabela inteira uma vez que não exista nenhum índice capaz de auxiliar nessa pesquisa. Em virtude da tabela ser pequena, o custo no plano é de apenas 13% (1% a mais do que as demais tabelas de Saldo). Agora imagine se essa tabela fosse muito grande e muito larga. Uma varredura poderia tornar a exclusão absurdamente lenta, além de impor bloqueios por muito tempo e muitas vezes desnecessários.

Algumas conclusões

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. Então nesse caso, é melhor assumir a posição de não criar índices nas colunas das chaves estrangeiras e deixar a cargo do implementador efetuar essa avaliação. Excetuando as operações de exclusão e atualizações em cascata, a criação de índices está muito mais voltada para a consulta em si do que o fato de existir um relacionamento. Ainda assim algumas recomendações:

1. Se você for efetuar exclusões nas tabelas pai com frequência e as tabelas filhas forem muito volumosas, indexe as FKs
2. Se você for efetuar JOINs com o objetivo apenas de contar registros ou verificar existência (EXISTs), indexe as FKs
3. Se você utilizar relacionamentos fortes ou identificados, indexe as FKs
4. Se as ocorrência dos valores nas colunas das FKs tiverem muita repetição, ou seja, forem pouco seletivas e as condições acima não forem verdadeiras, avalie se criar índices é um bom negócio (possivelmente não será)
5. Se os três primeiros itens não forem verdadeiros, olhe a criação do índice muito mais como um índice sobre um coluna comum, pois, nesse caso, a coluna ser ou não FK não fará diferença

E como descobrir quais FKs não estão indexadas ?

Seria simples utilizar o exemplo anterior que possui colunas simples, mas vou criar mais três tabelas com chaves compostas e assim podermos avaliar a eficácia do meu script. Embora as tabelas induzam um relacionamento identificado, deixarei algumas colunas de fora propositalmente

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

— Cria mais três tabelas
CREATE TABLE SaldosSuspeitos (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)
CREATE TABLE SaldosLimite (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)
CREATE TABLE SaldosDevedores (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)

— Adiciona as Constraints
ALTER TABLE SaldosSuspeitos ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE SaldosSuspeitos ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)
ALTER TABLE SaldosLimite ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)
ALTER TABLE SaldosDevedores ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)

— Adiciona um índice na FK para a tabela SaldosDevedores
CREATE INDEX IX_Saldo ON SaldosDevedores (DataSaldo, IDPessoa)
CREATE INDEX IX_DataSaldo ON SaldosLimite (DataSaldo)

Visualmente sabemos que a tabela Saldo4 não tem índice na coluna IDPessoa e tem FK para essa coluna. Sabemos também que a tabela SaldosLimite tem a FK com as colunas DataSaldo e IDPessoa, mas possui um índice apenas sobre a coluna DataSaldo e portanto não tem sua FK indexada. Segue a consulta:

;WITH FKsNI (ObjectId, FKId) As (
SELECT DISTINCT
    parent_object_id, constraint_object_id
FROM sys.foreign_key_columns As FC
WHERE NOT EXISTS (
    SELECT * FROM sys.index_columns As IC
    WHERE
        FC.parent_object_id = IC.object_id AND
        FC.constraint_column_id = IC.index_column_id AND
        FC.parent_column_id = IC.column_id))

SELECT
    OBJECT_NAME(FKsNI.ObjectId) As Objeto,
    OBJECT_NAME(FKsNI.FKId) As FK,
    C.Name As Coluna,
    FC.constraint_column_id As Ordem
FROM FKsNI
INNER JOIN sys.foreign_key_columns As FC ON
    FKsNI.FKId = FC.constraint_object_id AND
    FKsNI.ObjectId = FC.parent_object_id
INNER JOIN sys.columns As C ON
    FC.parent_object_id = C.object_id AND
    FC.parent_column_id = C.column_id
ORDER BY Ordem

O resultado sai exatamente conforme o esperado:

Agora vem a pergunta ? Depois que você rodar a consulta e identificar as FKs que não estão indexadas, você vai criar os índices ? Depende de cada um.

[ ]s,

Gustavo