MATA60 – BANCO DE DADOS Aula 10- Indexação

Prof. Daniela Barreiro Claro

Indexação em SQL;

Vantagens e Custo dos Índices;

Indexação no PostgreSQL;

Indexação

2 de X; X=23 FORMAS - UFBA

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>

Indexação

3 de X FORMAS - UFBA

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)

) ;

Indices únicos

4 de X FORMAS - UFBA

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ícios

5 de X FORMAS - UFBA

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

INNER JOIN t2 ON t1.c1 = t2.c2

INNER JOIN t3 ON t2.c2 = t3.c3;

Benefícios

6 de X FORMAS - UFBA

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. Consequentemente, 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ícios

7 de X FORMAS - UFBA

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.

Benefícios

8 de X

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.

Custo dos indices

9 de X

Í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.

Uso dos índices

10 de X

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.

Uso dos índices

11 de X

Í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.

Dicas

12 de X

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.

Dicas

13 de X

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 frequentes.

Dicas

14 de X

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 ele escolha outro tipo,

dependendo dos operadores de comparação envolvidos:

Indexação no PostgreSQL

15 de X

Para escolher o tipo de índice deve-se acrescentar ao final o comando a

palavra USING, especificando o tipo desejado.

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);

Indexação no PostgreSQL

16 de X

Estruturas de índices no PG

17 de X

Indexação no PG

18 de X

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 no PG

19 de X

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 no PG

20 de X

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;

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 no PG

21 de X

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;

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 no PG

22 de X

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.

Indexação no PG

23 de X

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

Livro: Korth

Notas de aula do Prof. Clodis Boscarioli

Facebook: /formasresearchgroup Twitter: /formasresearch

Semantic Formalisms and Applications Research Group

FORMAS - UFBA

www.dcc.ufba.br/~dclaro

Disciplina: MATA60 (2016.1)