Indexação em Banco de Dados

Banco de Dados I
2007
Módulo V: Indexação em Banco de Dados
(Aulas 4)
Clodis Boscarioli
Agenda:
Indexação em SQL;
Vantagens e Custo dos Índices;
Indexação no PostgreSQL;
Dicas Práticas.
Índice em SQL
Sintaxe:
create [unique] index <nome_indice> on
<nome_relação> (<lista atributos>)
Exemplo:
create index meu_indice on agencia(nome_agencia)
O atributo unique exige que a chave de procura seja
uma chave candidata (não duplicada).
Para remover um índice faz-se:
drop index <nome_índice>
Índices Únicos
CREATE UNIQUE INDEX nome_indice ON tabela (coluna)
Entretanto, é mais aconselhável utilizar CONSTRAINTs
para impor esse tipo de restrição de unicidade.
Exemplo:
CREATE TABLE usuario
(
cpf int,
nome varchar,
CONSTRAINT constraint_exemplo UNIQUE (cpf)
);
Benefício dos Índices
Quando uma tabela não tem índices, os seus registros
são desordenados e uma consulta terá que percorrer
todos os registros;
O uso de índices pode ainda ser mais valioso em
consultas envolvendo joins ou múltiplas tabelas:
Consultas em uma tabela o número de valores
que precisam ser examinados por coluna é o número
de registros da tabela.
Em consultas em múltiplas tabelas, o número de
possíveis combinações aumenta.
Benefício dos Índices
Exemplo: Suponha que existam três tabelas
sem índices, t1, t2 e t3, cada uma contendo as
colunas c1, c2 e c3, respectivamente, e cada
coluna contenha 1.000 registros com dados de
1 a 1.000. Suponha ainda a consulta abaixo:
SELECT t1.c1, t2.c2, t3.c3
FROM t1, t2, t3
WHERE t1.c1 = t2.c2 AND t2.c2 = t3.c3;
Benefício dos Índices
O resultado desta consulta deve ser uma tabela de
1.000 registros, cada um contendo três valores iguais.
Se a consulta for executada sem o uso de índices, todas
tuplas têm que ser percorridas. Conseqüentemente, o
banco tenta todas as combinações possíveis para
encontrar os registros que combinam com a condição da
cláusula WHERE. O número de possíveis combinações
é 1.000 x 1.000 x 1.000 = 1.000.000.000.
Benefício dos Índices
O uso de índices nas tabelas melhora o tempo de resposta da
consulta, pois permitem que consultas sejam assim
processadas:
1. Selecione a primeira tupla da tabela t1 e veja o seu valor;
2. Usando o índice da tabela t2, vá diretamente à tupla que
combine com o valor de t1. Ainda, use o índice da tabela t3 para
ir diretamente à tupla que combine com o valor de t2;
3. Vá para o próximo registro da tabela t1 e repita o
procedimento anterior. Faça isto até que todas as linhas em t1
tenham sido examinadas.
Ainda há uma varredura completa da tabela t1, mas índices são
usados nas tabelas t2 e t3 para encontrar as tuplas diretamente.
Custo dos Índices
Há custos de espaço e tempo. Na prática, as
desvantagens tendem a serem minimizadas pelo valor
das vantagens.
Índices aumentam a velocidade de consultas, mas
tornam mais lentas as operações de escrita (inserções,
atualizações e remoções), pois gravar uma tupla requer
não apenas escrevê-la, mas também mudar a
ordenação dos índices.
Quanto mais índices houver em uma tabela, mais
mudanças de ordenação necessitam ser feitas, o que
pode comprometer o desempenho.
A Escolha dos Índices
Índices devem ser criados em colunas usadas para
pesquisa, ordenação ou agrupamento, ou seja, nas que
aparecem na cláusula WHERE, em joins, ORDER BY ou
GROUP BY.
Colunas que aparecem em cláusulas JOIN ou em
expressões como col1 = col2 na cláusula WHERE são
fortes candidatas à criação de um índice.
É importante utilizar índices em chaves estrangeiras, já
que estas são muito utilizados em joins.
A Escolha dos Índices
Em determinados casos, em que haja várias consultas
que utilizem os comandos ORDER BY, GROUP BY e
DISTINCT é aconselhável criar um índice para a coluna
que está sendo utilizada nestas consultas. Isso se deve
ao fato de que cada vez que ocorre isto, o SGBD
dispara um SORT para ordenação dos dados, o que
pode comprometer o desempenho. Tendo índices para
este caso, os dados já poderão estar ordenados,
implicando em economias no processamento.
Índices – Dicas Práticas
Índices funcionam melhor em colunas que contenham
um alto número de valores distintos.
Mantenha, sempre que possível, chaves primárias
pequenas (os SGBDs criam um índice para cada chave
primária).
Não crie índices em excesso. Índices devem ser
atualizados e reorganizados quando o conteúdo de uma
tabela é modificado. Logo, quanto mais índices, mais
demorada será a atualização e a ordenação.
Índices – Dicas Práticas
Crie índices com valores pequenos. Use tipos de dados
o menor possível. Por exemplo, não use uma coluna
BIGINT se MEDIUMINT suporta os dados que serão
armazenados. Não use CHAR(100) se nenhum dos
valores armazenados ultrapassa 25 caracteres.
Valores pequenos melhoram o processamento de
índices de muitas maneiras:
Podem ser comparados mais rapidamente;
Ocupam menos espaço de disco nos arquivos de
índices;
É possível a permanência de mais registros em
cache, fazendo com que o servidor tenha menos
acessos a disco.
Índices – Dicas Práticas
Se um índice é raramente ou nunca utilizado,
este diminui, desnecessariamente, o
desempenho da tabela. Além disso, índices
desnecessários podem fazer com que o
otimizador de consultas não escolha o melhor
índice a ser usado.
As expressões de índice devem ser utilizadas
somente quando as consultas que usam o
índice forem muito freqüentes.
Indexação em PostgreSQL
CREATE INDEX nome_indice ON tabela_nome
(coluna_nome);
Por padrão, ao criar um índice em PostgreSQL,
se não for especificado seu tipo, ele utiliza a
estrutura B-Tree. Contudo, pode ser que o ele
escolha outro tipo, dependendo dos operadores
de comparação envolvidos:
Indexação em PostgreSQL
Tipo
Operadores
B-Tree
<, <=, =, >=, >
R-Tree
<<, &<, &>, >>, @, ~=, &&
Hash
=
Para escolher o tipo de índice deve-se acrescentar ao
final o comando a palavra USING, especificando o tipo
desejado.
Indexação em PostgreSQL
CREATE INDEX nome ON tabela USING HASH (coluna);
CREATE INDEX nome ON tabela USING BTREE (coluna);
CREATE INDEX nome ON tabela USING RTREE (coluna);
CREATE INDEX nome ON tabela USING GIST (coluna);
Estruturas de Índices no PostgreSQL
Índice
B-Tree
R-Tree
Hash
GiST
Definição
São árvores de busca balanceadas desenvolvidas para trabalharem em dispositivos de
armazenamento de acesso direto em memória secundária. O índice B-tree é uma
implementação das árvores B de alta concorrência propostas por Lehman e Yao.
Utiliza o algoritmo de partição quadrática de Guttman, sendo utilizada para indexar
estrutura de dados multidimensionais, cuja implementação está limitada a dados com até
8Kbytes, sendo bastante limitada para dados geográficos reais. Utilizado normalmente
com dados do tipo box, circle, point e outros.
O índice hash é uma implementação das dispersões lineares de Litwin. Na própria
documentação do PostgreSQL está presente a nota: "Os testes mostram que os índices
hash do PostgreSQL têm desempenho semelhante ou mais lento que os índices B-tree, e
que o tamanho e o tempo de construção dos índices hash são muito piores. Os índices
hash também possuem um fraco desempenho sob alta concorrência. Por estas razões, a
utilização dos índices hash é desestimulada".
Generalized Index Search Trees (Árvores de Busca de Índice Generalizadas), para mais
informações visite http://www.sai.msu.su/~megera/postgres/gist/doc/intro.shtml
Indexação em PostgreSQL
Quando se utiliza mais de uma coluna, o índice é
organizado de acordo com a primeira coluna
especificada na declaração, sendo a segunda utilizada
apenas quando a primeira coluna possuir vários valores
iguais. Portanto, a segunda coluna é usada como uma
segunda opção de classificação.
CREATE INDEX coluna1_coluna2_tabela_idx ON tabela
(coluna1,coluna2);
Indexação em PostgreSQL
Quando utilizadas várias colunas em um índice, o
otimizador de consultas pode utilizar todas as colunas
especificadas ou apenas uma ou algumas, de acordo
com sua decisão. Isto vai depender se as colunas são
consecutivas.
Por exemplo, um índice incluindo (col_1, col_2, col_3)
pode ser utilizado em consultas envolvendo col_1, col_2
e col_3, ou em consultas envolvendo col_1 e col_2, ou
em consultas envolvendo apenas col_1, mas não em
outras combinações. (Em uma consulta envolvendo
col_1 e col_3, o otimizador pode decidir utilizar um
índice para col_1 apenas, tratando col_3 como uma
coluna comum, não indexada).
Indexação em PostgreSQL
Exemplo: Dada a criação do índice:
CREATE INDEX ano_valor_cliente_idx ON clientes USING
BTREE (ano_nasc, valor_devido);
Considere as consultas abaixo:
SELECT nome FROM clientes WHERE ano_nasc > 1973
AND valor_devido < 1750;
SELECT nome FROM clientes WHERE ano_nasc > 1973
OR valor_devido < 1750;
Indexação em PostgreSQL
No exemplo, apenas a primeira consulta usa o
índice. Por definição, o PostgreSQL utiliza
apenas o índice com mais de uma coluna
quando as colunas estão unidas em uma
cláusula WHERE por AND, em outros casos o
índice vai ser utilizado apenas na coluna que foi
definida por primeiro na criação do índice.
Indexação em PostgreSQL
Na segunda consulta o índice
ano_valor_cliente_idx será usado apenas na
busca dos clientes nascidos após 1973, pela
ano_nasc ser sua coluna principal.
Indexação em PostgreSQL
O comando:
SELECT * FROM pg_stat_all_indexes
traz informações sobre os índices contidos no
banco de dados, como o número total de
varreduras que utilizaram um determinado
índice e o número de linhas lidas pelo índice.
Índices – Considerações Finais
Indexação é uma ferramenta importante para o aumento de
desempenho das consultas.
Use indexação como a primeira alternativa para obter um ganho de
desempenho e depois avalie que outras técnicas podem ser úteis.
Quando uma consulta demora a ser concluída, normalmente as
tabelas envolvidas não possuem índices ou estes foram mal
criados. A adequação ou criação dos índices necessários resolve o
problema na grande maioria das vezes.
Referências Bibliográficas
Sistemas de Banco de Dados. (Cap. 11) Abraham Silberchatz, Henry
F. Korth e S. Sudarshan. 3ª Edição. Makron Books, 1999.
Sistemas de Banco de Dados. (Cap. 14) Ramez Elsmari, 4ª Edição.
Shamkant B. Navathe. Pearson Addison Wesley, 2005.
Manuais do PostgreSQL.