Os índices são, inquestionavelmente, fundamentais para o desempenho do acesso às tabelas de um banco de dados. Porém, o que pouca gente leva em consideração são os custos inerentes aos índices.
Veja o exemplo real da tabela abaixo no SQL Server:
Em um determinado sistema financeiro, toda transação gera uma guia de pagamento e, na maioria absoluta das vezes em que ela é acessada para consulta o que interessa é somente as guias abertas (Situação ativa ou parcialmente paga).
A Query abaixo demonstra como os dados estão alocados na tabela.
with RegPorSituacao as (select a.SituacaoID
, b.nome SituacaoDS
, count(*) TotalID
from Guia a join Situacao b on A.SituacaoID = b.SituacaoID
group by a.SituaçãoID, b.nome)
, RegTotal as (select Count(*) TotalGeral
from Guia a)
select x.SituacaoID SitID
, x.SituacaoDS SitDs
, x.TotalID Total
, ROUND((CAST(x.TotalID as decimal)/
CAST(y.TotalGeral as decimal))*100,2) QtdPerc
from RegPorSituacao x
, RegTotal y
Esta Query gera o seguinte resultado:
SitID SitDs Total QtdPerc ----- ----------------- ------ ------- 15 BAIXADO 202624 88.6700 3 CANCELADO 14967 6.5500 1 ATIVO 10860 4.7500 25 PARCIALMENTE PAGO 58 0.0300
Espaço Ocupado
Índices ocupam bastante espaço e quanto mais complexo for o índice mais espaço ele ocupa.
Observe que, pela regra do sistema acima, mais de 94% dos registros não interessam ao sistema. Portanto, a criação de um índice único ocuparia espaço com registros que nunca seriam utilizado (ou pesquisados) no sistema
Além do desperdício de espaço, ele gera problema de I/O na busca, pois o SQL Server terá que buscar um grande volume de dados no índice.
Inserção e Updates
Toda vez que um registro é inserido ou atualizado, é necessário a atualização do índice.
Neste caso, quando a guia é cancelada ou paga, existe um desperdício de recurso atualizando índices que não serão atualizados.
Otimização do Índice no SQL Server
Uma forma muito inteligente e simples criada pelo SQL Server são os “Filtered Indexes” ou índices com filtros.
Nada mais é do que a possibilidade de adicionar um filtro ao índice, ditando desta forma a condição de existência do registro no arquivo de índices da tabela
Como o sistema não usa as situações de BAIXADO e CANCELADO para absolutamente nada, os índices possuem mais de 96% dos registros ocupando espaço desnecessariamente.
Observe o espaço utilizado pelo índice criado normalmente (sem a cláusula where):
Para testarmos o desempenho vamos rodar uma Query simples, conforme abaixo:
select count(*) from guia where situacaoid = 3
Veja o desempenho na tabela abaixo:
Agora vamos substituir o índice atual por um mais efetivo, conforme criado abaixo:
create index guia_indiceSituacaoAtivo on guia (situacaoid) where situacaoid in (1,25)
Observe o espaço utilizado pelo índice criado com a cláusula where:
Agora vamos testar o desempenho do novo índice, conforme tabela abaixo:
Resultados comparativos
Espaço Utilizado
Observe que com a cláusula where o índice representa 5,5% do índice geral, o que já demonstra uma significativa redução de espaço armazenado.
Desempenho do Acesso
Observe que no índice filtrado, o custo de I/O caiu de 0,027 para 0,004, ou seja, o índice filtrado gerou um custo de I/o 6,75 vezes menor
Outros indicadores a serem considerados são os indicadores “Operator Cost” e “Subtree Cost” que apresentaram redução de custo de 50%
Considerações
O índice filtrado é uma excelente opção para economia de espaço quando as tabelas apresentam situações que não seriam úteis para o sistema mas que o índice tradicional é obrigado a considerar, especialmente quando estas situações ocupam a maior parte dos registros.
Por outro lado, mesmo que todas as situações da tabela sejam úteis, ainda assim o índice filtrado se mostra útil, pois este reduz o custo de I/O do acesso ao índice quando acessado pelo campo do filtro.
Além da situação outras condições pode ser adotadas para auxiliar o desempenho geral do índice. Uma situação que está sendo aplicada é o particionamento das chaves de acesso.
Por exemplo, temos um cadastro de pessoas com 250 mil pessoas cadastradas que é constantemente acessado pelo campo PessoaID. Criamos o índice filtrado com a seguinte condição:
Create index Pessoa_01 on pessoa (PessoaID) where (PessoaID <= 50000);
Create index Pessoa_02 on pessoa (PessoaID) where (PessoaID > 50000 and PessoaID <= 100000);
Create index Pessoa_03 on pessoa (PessoaID) where (PessoaID > 100000 and PessoaID <= 150000);
Create index Pessoa_04 on pessoa (PessoaID) where (PessoaID > 150000 and PessoaID <= 200000);
Create index Pessoa_05 on pessoa (PessoaID) where (PessoaID > 200000 and PessoaID <= 250000);
Create index Pessoa_06 on pessoa (PessoaID) where (PessoaID > 250000);
Com isso além de reduzir o I/O de acesso ao índice, reduz-se também o tempo de espera por acesso concorrente.
Essa mesma técnica pode ser aplicada no Oracle de duas maneiras diferentes: Filtered Index e particionamento. Posteriormente colocarei um artigo sobre este recurso aplicado ao Oracle