IEC Banco de Dados I Aula 16 O5mização de Índices

IEC  

Banco  de  Dados  I  

Aula  16  –  O5mização  de  Índices  

Turmas:  Sistemas  de  Informação  

Professora:    André  Luiz  da  Costa  Carvalho   E-­‐mail:  andre@icomp.ufam.edu.br  

Site:  hLp://bdufam.wordpress.com    

2

Índice

Estrutura de dados para acesso eficiente

Set of Records index

Condição Sobre Atributo

Matching records

(search key)

3

Search Keys

¨ 

Uma chave de pesquisa é uma sequência de atributos

¤  create index i1 on accounts(branchnum, balance);

¨ 

Tipos de chaves

¤ 

Sequenciais

¤ 

Não sequenciais

4

Estrutura de Dados

¨ 

Maior parte dos índices podem ser vistos como árvores

¨ 

Em geral: raiz na memória, folhas no disco

¤ Performance depende do número de nós no caminho entre a raiz e a folha.

¤ Fan-out maior é preferível.

5

B+-Tree

¨ 

Árvore-B com dados apenas nas folhas.

96

75 83 107

96 98 103 107 110 120 83 92 95

75 80 81 33 48 69

6

B+-Performance

¨ 

Altura da árvore

¤ Fanout

n  Tamanho da chave

n  % de Utilização da árvore

¨ 

Manutenção

¤ Online

n  Toda inserção

n  Toda deleção

¤ Offline

¨ 

Travamento da árvore

¨ 

Raiz na memória

7

B+-Performance

¨ 

Tamanho da chave afeta o fanout

¤ Escolha uma chave pequena para aumentar a performance

¤ Compressão de chave

n  Compressão de prefixo: só guardar a parte da chave necessária para distinguir dos vizinhos Man, Marc, Mari para guardar Manuela, Marcia e Maria

8

Hash

¨ 

Usa função hash para guardar valores.

Hashed key values

0 1

n

R1 R5

R3 R6 R9 R14 R17 R21 R25 Hash

function key

2341

Tamanho das Listas encadeadas Afeta a compressão

Índices no Postgres

Índices no Postgres

¨  Os índices são recursos do SGBD para melhorar o

desempenho de consultas. Mas como o uso de índices também tem um preço é importante planejar bem e conhecer as

particularidades antes de adicionar um índice.

¨  Cada vez que um registro é inserido ou atualizado a tabela de índices também é atualizada.

¨  Quando criamos consultas SQL, que pesquisam tabelas com muitos registros e esta consulta usa a cláusula WHERE, então os campos que fazem parte da cláusula WHERE são bastante indicados para índice, para que melhore o desempenho da consulta.

Índices no Postgres

¨  Os índices são uma forma de melhorar o desempenho de

bancos de dados. Ao invés de procurar de forma sequencial, o servidor procura pelo índice, como se faz uma busca em

índices de livros e vai-se diretamente à página procurada.

¨  O índice é atualizado para cada registro adicionado ou removido.

¨  É difícil criar regras genéricas para determinar que índices devem ser definidos. Muita experiência por parte do

administrador e muita verificação experimental é necessária na maioria dos casos.

Criando Índices

¨ 

CREATE INDEX nomeindice ON tabela (campo);

¤ 

Regra geral para nome de índice:

idx_nometabela_nomecampo

¤ 

Obs.: índices não importantes ou não utilizados devem ser removidos.

¨ 

Criar um índice Único:

¤ 

CREATE UNIQUE INDEX nomeindice ON tabela (campo);

¤ 

Obs.: Somente os índices tipo B-tree podem ser do tipo

Unique.

Criar um índice com várias colunas

¨ 

CREATE INDEX idx_clientes_ps ON clientes (codigo, nome);

¨ 

Boa indicação para consultas com WHERE...AND.

Ao usar OR o índice não será utilizado pelo PostgreSQL:

¤ 

SELECT nome FROM clientes WHERE codigo = 12 AND nome = 'João’;

¨ 

Usar índices com várias colunas com moderação.

Índices com mais de 3 colunas tem grandes possibilidades de não serem utilizados

internamente.

Tipos de Índices

¨  O PostgreSQL suporta atualmente quatro tipos de índices: B- tree (árvore B), R-tree (árvore R), Hash e GiST.

¤  B-tree -> é o tipo padrão (assume quando não indicamos). São índices que podem tratar consultas de igualdade e de faixa, em dados que podem ser classificados.

¤  GiST: Indicado para consultas com os operadores: <, <=, =, >=, >.

Também pode ser utilizado com LIKE, ILIKE, ~ e ~*.

¤  R-tree -> tipo mais adequado a dados espaciais. Adequado para consultas com os operadores: <<, &<, &>, >>, @, ~=, &&.

¤  Hash ->indicados para consultas com comparações de igualdade

simples. É desencorajado seu uso. Em seu lugar recomenda-se o B-tree.

GiST ->

Criando índices de tipos diferentes:

¨ 

CREATE INDEX nome ON tabela USING tipo (campo);

¤ 

tipo: BTREE, RTREE, HASH, GIST

¨ 

Obs.: Somente os tipos B-tree e GiST suportam índices com várias colunas.

¨ 

Índices com mais de um campo somente serão

utilizado se as cláusulas com os campos indexados

forem ligados por AND.

Índice Parcial

¨  Criado apenas sobre um subconjunto dos registros de uma tabela, definido numa expressão durante a criação do índice parcial. É um recurso para melhorar o desempenho dos índices, já que atualiza somente parte dos registros.

¨  Exemplos:

¤  CREATE INDEX idx_registro_acesso_ip_cliente ON tbl_registro_acesso (ip_cliente) WHERE NOT (ip_cliente > inet '192.168.100.0' AND

ip_cliente < inet '192.168.100.255');

¤  CREATE INDEX idx_pedidos_nao_faturados ON pedidos (num_pedido) WHERE faturado is not true;

¤  CREATE UNIQUE INDEX unq_resposta_correta ON tbl_teste (pergunta, correto) WHERE correto;*

Examinando a Utilização dos Índices

¨ 

A verificação de uso de índices deve ser feita com os comandos EXPLAIN e ANALYZE.

¨ 

O comando ANALYZE coleta estatísticas sobre a distribuição dos valores na tabela.

¨ 

Devem ser utilizados dados reais e o conjunto de dados de teste nunca deve ser pequeno.

¨ 

EXPLAIN ANALYZE <SELECT>

¨ 

Atentar para usar índices nos campos das Cláusulas

¤ 

FOREIGN KEY

¤ 

ORDER BY

¤ 

WHERE

¤ 

ON

¤ 

GROUP BY

¤ 

HAVING

Exemplos

¨  CREATE INDEX idx_pessoas_nome ON pessoas USING hash (nome);

¨  Criando um índice que “incorpora” dos campos ao mesmo tempo:

¤  CREATE INDEX idx_usuario_login_senha ON usuario USING btree (login,senha);

¨  Criando um índice com a cláusula WHERE:

¤  CREATE UNIQUE INDEX ind_nome_tiago ON pessoa USING GIN (nome) WHERE nome iLIKE 'Tiago%';

¨  Renomeando um índice:

¤  CREATE INDEX idx_pessoas_endereco RENAME TO idx_pessoas_endereco_completo

¨  Excluindo (dropando) um índice:

¤  DROP INDEX idx_pessoas_endereco_completo

¨ 

UNIQUE: Declarado, o Postgres não irá indexar registros duplicados e retornará um erro. Veja o exemplo de uso:

¤ 

CREATE INDEX UNIQUE idx_pessoa_cpf ON pessoas USING btree (cpf);

¨ 

CONCURRENTLY: Caso seja declarado ele informa que não será realizado o bloqueio do índice

durante procedimentos de DML (insert, update, etc).

Veja o exemplo de uso:

¤ 

CREATE INDEX CONCURRENTLY idx_pessoa_rg ON

pessoas USING btree

Otimização de Índices

22

Otimização de índices

¨ 

Problemas de índices

¤ 

Índices podem ser melhores ou piores que simplesmente ler as tabelas.

¤ 

Joins Multi-Tabelas podem levar horas com os índices errados

¤ 

Índices que são mantidos mas nunca usados.

23

Índice de Cluster

¨ 

Cluster index (primário)

¤  Ponteiros são organizados em ordem

¤  Proximidade

¤  Muito menor e mais barato

¨ 

Non-clustered index (secundário)

¤  Ignora organização

¤  Vários por tabela

Records Records

24

Denso/Esparço

¨ 

Esparsos

¤  Ponteiros são associados à páginas/blocos

¨ 

Denso

¤  Ponteiros são assossiados a registros

¤  Não cluster são densos

P1 P2 Pi ^record

record record

25

Implementações em SGBDs famosos

¨  SQL Server

¤  B+-Tree

¤  Clusters são esparsos

¤  Índices são atualizados durante updates.

¨  DB2

¤  B+-Tree, R-tree

¤  Clusters são densos

¤  Comando explícito para reindexação

¨  Oracle

¤  B+-tree, hash, bitmap, R-Tree

¤  Clusters

n  Configurável

26

Tipos de consulta

¨  Point Query

SELECT balance FROM accounts

WHERE number = 1023;

¨  Multipoint Query

SELECT balance FROM accounts

WHERE branchnum = 100;

¨ 

Range Query

SELECT number FROM accounts

WHERE balance > 10000 and balance <= 20000;

¨ 

Prefix Match Query

SELECT *

FROM employees

WHERE name = ‘J*’ ;

27

Tipos de consulta

¨  Extremal Query

SELECT *

FROM accounts WHERE balance =

max(select balance from accounts)

¨  Ordering Query

SELECT *

FROM accounts

ORDER BY balance;

¨  Grouping Query

SELECT branchnum, avg(balance) FROM accounts

GROUP BY branchnum;

¨  Join Query

SELECT distinct branch.adresse FROM accounts, branch

WHERE

accounts.branchnum = branch.number

and accounts.balance > 10000;

28

Exemplo

Configuração:

employees(ssnum, name, lat, long, hundreds1, hundreds2);

clustered index c on employees(hundreds1) with fillfactor = 100;

nonclustered index nc on employees (hundreds2);

index nc3 on employees (ssnum, name, hundreds2);

index nc4 on employees (lat, ssnum, name);

¤  1000000 tuplas

29

Operações

Operações

¤  Update:

update employees set name = ‘XXX’ where ssnum = ?;

¤  Insert:

insert into employees values (1003505,'polo94064', 97.48,84.03,4700.55,3987.2);

¤  Multipoint query:

select * from employees where hundreds1= ?;

select * from employees where hundreds2= ?;

¤  Covered query:

select ssnum, name, lat from employees;

¤ 

Range Query:

select * from employees where long between ? and ?;

¤ 

Point Query:

select * from employees where ssnum = ?

30

Índice de Cluster

¨ 

Consulta Multiponto retorna 100 registros de 1000000.

¨ 

Índice de Cluster 2

vezes mais rápido que normal, 2 vezes mais rápido que não cluster.

0 0.2 0.4 0.6 0.8 1

SQLServer Oracle DB2

Throughput ratio

clustered nonclustered no index

31

Covering Index

¨ 

Select name from employee where department =

“ marketing ”

¨ 

Bom índice seria sobre (department, name)

¨ 

Índice sobre (name, department) menos útil

¨ 

Índice sobre departament moderadamente útil.

32

Covering Index - impacto

¨  Covering index melhor que cluster quando o primeiro atributo está na cláusula where.

¨  For a de ordem é muito pior

0 10 20 30 40 50 60 70

SQLServer

Throughput (queries/sec)

covering covering - not ordered

non clustering clustering

33

Ler Tabela pode ser melhor

¨  IBM DB2 v7.1 on Windows 2000

¨  Range Query

¨  Se uma consulta retornar mais de 10% dos registros, ler tudo pode ser melhor que usar um índice não cluster não-covering.

¤  Principalmente para dados contiguos

0 5 10 15 20 25

% of selected records

Throughput (queries/sec)

scan

non clustering

34

Índices em tabelas pequenas

¨  Tabela pequena: ex:100 tuplas.

¤  Valor que indica

“pequenitude”depende do tamanho do registro x

tamanho da chave.

¤  Registros grandes podem ocupar muitos blocos, fazendo o índice fazer sentido em uma tabela assim.

¤  Tabela com muitas

atualizações pode fazer sentido, mesmo sendo pequena

0 2 4 6 8 10 12 14 16 18

no index index

Throughput (updates/sec)

35

Hash vs. B+-Tree

Settings:

employees(ssnum, name, lat, long, hundreds1, hundreds2);

create cluster c_hundreds (hundreds2 number(8)) PCTFREE 0;

create cluster c_ssnum(ssnum integer) PCTFREE 0 size 60;

create cluster c_hundreds(hundreds2 number(8)) PCTFREE 0 HASHKEYS 1000 size 600;

create cluster c_ssnum(ssnum integer) PCTFREE 0 HASHKEYS 1000000 SIZE 60;

create bitmap index b on employees (hundreds2);

create bitmap index b2 on employees (ssnum);

¤  1000000 tuplas

36

Multipoint query: B-Tree, Hash Tree

¨  Hundreds2 tem poucos valores, então o

encadeamento do hash é longo.

¨  B-Tree+ é com cluster, então registros ficam em blocos contiguos.

Multipoint Queries

0 5 10 15 20 25

B-Tree Hash index Bitmap index

Throughput (queries/sec)

37

¨ 

Índices hash não são bons para intervalos

¨ 

Mas são melhores em consultas pontuais

(igualdade)

Range Queries

0 0.1 0.2 0.3 0.4 0.5

B-Tree Hash index Bitmap index

Throughput (queries/sec)

B-Tree, Hash Tree

Point Queries

0 10 20 30 40 50 60

B-Tree hash index

Throughput(queries/sec)

Índices: Sumário

¨ 

Hash: Igualdades

¤ 

B-Tree+ se houverem tanto igualdades quanto inequidades.

¨ 

Clustering

¨ 

Evite índices em tabelas pequenas.

¤ 

E quando existem mais inserções que consultas

Índices: Sumário

¨ 

Índices tem que ser cuidados.

¤ 

Hashs podem ter encadeamentos muito longos

¤ 

Hashs muito cheios, ou ocupando muito espaço

¤ 

Btree+ com ocupação muito baixa, ou nós vazios (em alguns sistemas)

¤ 

Drop + Rebuild

¨ 

Índices que atrapalham podem ser removidos.

¤ 

Ex: Muitas inserções durante o dia, muitas consultas complexas à noite.

n Drop durante o dia, rebuild antes das consultas

40

Manutenção de índices

¨  Inserções causam splits, e I/O extra

¨  Manutenção = Drop + Rebuild

¨  Coma manutenção a

performance é constante, enquanto a performace cai sem manutenção.

SQLServer

0 20 40 60 80 100

0 20 40 60 80 100

% Increase in Table Size Throughput (queries/sec)

No maintenance Maintenance