Quando temos a necessidade de criar índices compostos (mais de uma coluna em um índice), uma boa prática é identificarmos a coluna mais SELETIVA (coluna com menor repetição de valores) e fazer desta coluna a primeira coluna do índice. Ou seja, a coluna que possuir menor repetição de seus valores, deve ser declarada como a primeira coluna em um índice composto.
Exemplo:
Temos uma tabela com 37.000 linhas que possui várias colunas e entre elas temos: ORDERID, SHIPCOUNTRY e FIRSTNAME. As pesquisas sempre serão realizadas por SHIPCOUNTRY e FIRSTNAME.
Bom, de início é possível notar que precisaremos então de um índice composto pelas colunas SHIPCOUNTRY e FIRSTNAME, mas a pergunta aqui é: Em qual ordem de pesquisa teremos melhores ganhos de performance, (SHIPCOUNTRY, FIRSTNAME) ou (FIRSTNAME, SHIPCOUNTRY)?
Executando a query abaixo para calcular a seletividade das colunas, poderemos obter a resposta a esta pergunta:
-- Verifica a seletividade da coluna ShipCountry'
SELECT [Qtde. Registros] = COUNT(*),
[Reg. Distintos] = COUNT(DISTINCT ShipCountry),
-- Quanto mais próximo de 1, melhor
[Seletividade] = COUNT(DISTINCT ShipCountry)/CAST( COUNT(*) AS DEC(10,2))
FROM Orders
--Resultado
Qtde. Registros Reg. Distintos Seletividade
--------------- -------------- -------------
830 21 0.02530120481
-- Verifica a seletividade da coluna FirstName'
SELECT [Qtde. Registros] = COUNT(*),
[Reg. Distintos] = COUNT(distinct FirstName),
-- Quanto mais próximo de 1, melhor
[Seletividade] = COUNT(distinct FirstName)/CAST( count(*) AS DEC(10,2))
FROM Orders
--Resultado
Qtde. Registros Reg. Distintos Seletividade
--------------- -------------- -------------
830 90 0.10843373493
O objetivo da análise é verificar quais das colunas de uma tabela terá maior seletividade (quanto mais próximo de 1, maior será a seletividade da coluna) e tornar esta coluna a primeira coluna do índice composto.
Seguindo nosso exemplo temos a seguinte:
Coluna - ShipCountry:
SELETIVIDADE = 0.02530120481
Coluna – FirstName:
SELETIVIDADE = 0.10843373493
Resultado final:
Comparando o valor retornado para a seletividade das duas colunas, constatamos que a coluna FirstName é a mais SELETIVA (possui um menor número de valores repetidos) e portanto deve ser considerada como a primeira coluna na composição de um índice composto.
Exemplo:
CREATE INDEX IxOrders_00 On Orders (FirstName, ShipCountry)
Sendo assim, ao invés de realizarmos nossa pesquisa como: WHERE SHIPCOUNTRY='MÉXICO' AND FIRSTNAME= 'ANTONIO'
Devemos realizar como: WHERE FIRSTNAME= 'ANTONIO' AND SHIPCOUNTRY='MÉXICO'
É claro que esta regra pode não atender a todos os sistemas, tudo dependerá muito da linha de pesquisa de cadaaplicação. Porém, esta dica server para a maioria dos sistemas OLTP com grandes volumes de transação e com muitas pesquisa em tabelas.
Uma boa prática também é independente do índice ser composto ou não, procurar sempre verificar a seletividade da coluna e na medida do possível, fazer desta coluna a chave do seu índice. Sempre que possível, verifique também o plano de execução de suas queries, ele lhe dirá o quanto seus índices estão sendo eficiêntes ou não.
Obviamente que colunas que sofrem atualizações (UPDATE) não devem ser consideradas como candidatas a compor o índice pois isto criará um ambiente proprício a page split [1], degradando assim a performance da sua aplicação.
[1] Page split é o termo utilizado para explicar a divisão de páginas de índices para acomodar novas inserções.
Nota: Review
|
Comunidade:
Membros:
