Bases de Dados

Propaganda
Bases de Dados
Índices
Discos
ƒ
ƒ
transferência lenta
e em blocos ((512B ~ 4KB))
ƒ
ƒ
transferência rápida
e aos bytes
y
memória
disco
aplicação
IST ▪ DEI ▪ Bases de Dados
2
1
Discos
7200rpm = 120 rot/s
1–5 platters
Ø ≈ 8.9 cm
50000 ~ 100000
tracks per platter
sector = 512 B
~ 500 sectors
per (inner) track
~ 1000 sectors
per (outer) track
IST ▪ DEI ▪ Bases de Dados
3
Acesso aos dados
ƒ Os dados são transferidos entre o disco e a memória
em blocos
• um bloco abrange vários sectores contíguos
ƒ É objectivo de qualquer SGBD
• minimizar o número de transferências de blocos
entre disco e memória
IST ▪ DEI ▪ Bases de Dados
4
2
Índices
ƒ Índices permitem acelerar o acesso aos dados
• p.ex. índice de um livro
▫ assunto é a chave de procura (search key)
▫ nº da página é o apontador
ƒ Um ficheiro de índices contém registos na forma
search key
apontador
• em geral é muito mais pequeno que o ficheiro de
dados
IST ▪ DEI ▪ Bases de Dados
5
Índices
ƒ Dois tipos básicos de índices
• índices ordenados (p.ex. árvore B+)
▫ valores da chave de procura armazenados por ordem
• índices do tipo "hash"
▫ valores da chave de procura distribuídos uniformemente
por vários "contentores" (buckets) de acordo com uma
função de "hash"
IST ▪ DEI ▪ Bases de Dados
6
3
Escolha de índices
ƒ Critérios para a escolha de índices
• possibilidade de acesso eficiente aos dados
▫ registos com o valor dado para o atributo
▫ registos com o valor do atributo numa gama de valores
• tempo de inserção
• tempo de remoção
• espaço ocupado
IST ▪ DEI ▪ Bases de Dados
7
Índices ordenados
ƒ Num ficheiro de dados sequencial podem haver 2 tipos
de índices
• índice primário
▫ é o índice cuja chave de procura especifica a ordem dos
dados no ficheiro
▫ normalmente a chave de procura é a chave primária
• índice secundário
▫ um índice que especifica uma ordem diferente da do
fi h i
ficheiro
ƒ Se houver índice primário, o ficheiro é "sequencial
indexado"
IST ▪ DEI ▪ Bases de Dados
8
4
Índices primários (clustered)
índice
índice
9
IST ▪ DEI ▪ Bases de Dados
Índices secundários (non-clustered)
índice
IST ▪ DEI ▪ Bases de Dados
índice
índice
10
5
Índices densos
ƒ Um índice é denso se refere todos os valores
possíveis da chave de procura
IST ▪ DEI ▪ Bases de Dados
11
Índices esparsos
ƒ Um índice é esparso se contém entradas para apenas
alguns dos valores da chave de procura
ƒ Aplicável quando os registos estão ordenados por
essa chave (índice primário)
• para localizar o registo com valor K
▫ encontrar a entrada no índice com o maior valor < K
▫ p
pesquisar
q
o ficheiro sequencialmente
q
ap
partir daí
IST ▪ DEI ▪ Bases de Dados
12
6
Índices esparsos
ƒ Menos espaço e manobras de inserção/remoção
ƒ Mais lento a localizar os registos pretendidos
ƒ Melhor compromisso é um índice esparso com uma
entrada para cada bloco no ficheiro, correspondente
ao menor valor da chave de procura nesse bloco
IST ▪ DEI ▪ Bases de Dados
13
Índices multi-nível
ƒ Se um índice primário não couber todo na memória, o
acesso complica-se
• para reduzir o número de acessos ao disco, trata-se
o índice como um ficheiro sequencial
• e cria-se um índice esparso para o índice primário
• se este for demasiado grande para caber em
memória, cria-se ainda outro nível
ƒ Os índices têm que ser mantidos actualizados em
todos os níveis
IST ▪ DEI ▪ Bases de Dados
14
7
Índices multi-nível
IST ▪ DEI ▪ Bases de Dados
15
Actualização de índices
ƒ Remoção
• ao remover o único registo com um dado valor,
remove-se a respectiva entrada do índice
ƒ Inserção
• pesquisar o valor do registo no índice
▫ índices densos – se o valor não estiver indexado, inserir
nova entrada no índice
▫ índices esparsos – só é necessário criar uma nova entrada
se a inserção der origem a um novo bloco
IST ▪ DEI ▪ Bases de Dados
16
8
Índices secundários
ƒ Um índice secundário pode ser necessário para fazer
pesquisas por outro critério
• se a relação account estiver armazenada por
account_number
▫ encontrar todas as contas de uma dada agência
▫ encontrar todas as contas de uma agência e com saldo
numa dada gama de valores
ƒ É possível ter um índice secundário para cada uma
destas chaves de procura
• o índice aponta para um contentor de apontadores
para registos
IST ▪ DEI ▪ Bases de Dados
17
Índices secundários
ƒ Índices secundários têm de ser densos (porquê?)
ƒ Também têm que ser mantidos actualizados
ƒ Menos eficiente que um índice primário
▫ cada registo pode implicar o acesso a um bloco diferente
IST ▪ DEI ▪ Bases de Dados
18
9
Bases de Dados
Índices em árvore B+
Árvore B+ – exemplo 1
árvore B+ com n=3
IST ▪ DEI ▪ Bases de Dados
20
10
Árvore B+ – exemplo 2
árvore B+ com n=5
IST ▪ DEI ▪ Bases de Dados
21
Árvore B+ – nós
ƒ Cada nó da árvore contém
• Ki são os valores da chave de procura
▫ ordenados K1 < K2 < K3 < . . . < Kn–1
• Pi são
▫ apontadores para outros nós (se o nó não é folha)
▫ ou apontadores para contentores de registos (se é folha)
IST ▪ DEI ▪ Bases de Dados
22
11
Árvore B+ – nós folha
ƒ Se o nó é folha
• Pi aponta para um registo com valor Ki
• ou Pi aponta para um contentor de registos com Ki
▫ necessário se a chave de procura não for chave primária
• Pn aponta para a folha seguinte (mesmo nível)
IST ▪ DEI ▪ Bases de Dados
23
Árvore B+ – nós não folha
ƒ Se o nó não é folha
• forma um índice esparso multi-nível para as folhas
• todas as chaves na sub-árvore de P1 são menores
que K1
• em geral, todas as chaves na sub-árvore de Pi são
≥ Ki–1 e < Ki
• as chaves ≥ Kn–1 estão na sub-árvore apontada por
Pn
IST ▪ DEI ▪ Bases de Dados
24
12
Árvore B+ – propriedades
ƒ Todos os caminhos da raiz a qualquer folha têm o
mesmo comprimento
ƒ Cada nó tem entre n/2 e n descendentes
• excepto a raiz, que pode ter menos
ƒ Cada folha tem n apontadores e (n-1) valores
• apenas alguns podem estar preenchidos
• mas tem que ter no mínimo (n-1)/2 valores
ƒ Casos especiais:
• se a raiz
i não
ã é uma ffolha,
lh ttem pelo
l menos 2
descendentes
• se a raiz é folha (e a árvore só tem esse nó) pode
ter entre 0 e (n–1) valores
IST ▪ DEI ▪ Bases de Dados
25
Árvore B+ – eficiência
ƒ Como as ligações são feitas por apontadores, não é
necessário que os blocos estejam fisicamente juntos
ƒ Uma árvore B+ contém relativamente poucos níveis
• as pesquisas são eficientes
ƒ Inserções e remoções também podem ser tratadas
eficientemente
• a árvore pode ser reestruturada em tempo
logarítmico
IST ▪ DEI ▪ Bases de Dados
26
13
Árvore B+ – vantagens
ƒ Em ficheiros sequenciais indexados, o desempenho
degrada-se à medida que o ficheiro cresce
• e é necessário reorganizar todo o ficheiro de vez
em quando
ƒ Em árvores B+ apenas pequenas alterações locais são
necessárias perante inserções e remoções
• à custa de mais algum processamento e espaço
• mas as vantagens compensam as desvantagens
IST ▪ DEI ▪ Bases de Dados
27
Procura em árvores B+
ƒ
Encontrar todos os registos com chave de procura k
1. começa pela raiz
a) procurar na raiz o menor valor Ki tal que Ki > k
b) se existir, seguir Pi para o nó descendente
c) senão, seguir Pn
2. repetir o passo anterior até chegar a uma folha
3. assim que chegarmos a uma folha
a)) procurar
p
na folha o valor Ki tal q
que Ki = k
b) se existir, seguir Pi para o registo ou contentor
c) senão, conclui-se que o registo não existe
IST ▪ DEI ▪ Bases de Dados
28
14
Procura em árvores B+
ƒ Se houver K valores da chave de procura na árvore,
nenhum caminho é mais comprido do que ⎡ log⎡n/2⎤(K)⎤
ƒ Geralmente um nó é do tamanho de um bloco,
tipicamente 4 KB
• para isso escolhe-se n≈100 com 40 bytes para
cada entrada
• com 1 milhão de valores de chave e n=100 acedese a apenas
p
log
g50((1 000 000)) = 4 nós
• se a árvore fosse binária seriam precisos 20 nós
• cada nó exige um acesso ao disco (20ms)
IST ▪ DEI ▪ Bases de Dados
29
Modificações em árvores B+
ƒ As modificações em árvores B+ seguem os seguintes
passos:
1. pesquisar o valor pretendido na árvore, desde a
raíz até chegar à folha respectiva (top-down)
2. fazer as alterações pretendidas ao nível das folhas
3. repercutir os efeitos de forma bottom-up, isto é,
depois de alterar nós a um nível, verificar se o nó
ascendente fica coerente,, senão propagar
p p g os
efeitos recursivamente (até à raiz, se necessário)
IST ▪ DEI ▪ Bases de Dados
30
15
Propriedades
ƒ Propriedades gerais que devem ser mantidas:
A. à esquerda de um valor está uma sub-árvore com
valores sempre menores
B. à direita de um valor está uma sub-árvore com
valores maiores ou iguais
C. cada nó deve ter sempre pelo menos (n-1)/2
valores (ou n/2 apontadores) preenchidos, caso
contrário é necessário redistribuir valores ((e
apontadores) pelos nós vizinhos
IST ▪ DEI ▪ Bases de Dados
31
Inserção em árvores B+
ƒ Inserção
• procurar a folha em que esteja presente a chave
dada
• se existir, adicionar registo ao ficheiro e inserir
apontador no contentor
• senão, adicionar registo e criar novo contentor
▫ inserir a entrada (valor, apontador) na folha
• se a folha ultrapassar o máximo de n
n–1
1 valores
▫ separar os n valores em 2 nós
IST ▪ DEI ▪ Bases de Dados
32
16
Inserção em árvores B+
ƒ Separação de n valores em 2 nós
• deixar os primeiros ⎡n/2⎤ valores no nó original, o
resto passa um segundo, novo nó
• inserir a entrada (k,p) no nó ascendente, em que
▫ k é a menor chave do novo nó
▫ p é um apontador para o novo nó
• se o nó ascendente excedeu o tamanho máximo,
parti-lo segundo
p
g
o mesmo p
procedimento
• se a divisão se propagar até à raiz
▫ a raiz é partida em 2 nós
▫ é criada uma nova raiz como ascendente destes 2 nós
IST ▪ DEI ▪ Bases de Dados
33
Inserção em árvores B+ – exemplo
IST ▪ DEI ▪ Bases de Dados
34
17
Bases de Dados
Remoções em árvores B+
Remoção em árvores B+
ƒ Remoção
• procurar o registo e removê-lo do ficheiro
• se o apontador ou contentor ficar vazio, remover a
entrada (valor, apontador) da folha
• se a folha ficou abaixo do mínimo de ⎡(n-1)/2⎤
valores
▫ se for possível, passar estes valores para o nó da
esquerda, remover a folha, e remover a entrada respectiva
no nó ascendente
▫ se isso não for possível, redistribuir os apontadores pelos
nós vizinhos e ajustar o valor da chave no nó ascendente
IST ▪ DEI ▪ Bases de Dados
36
18
Remoção em árvores B+
ƒ Se ao modificar o nó ascendente, este ficar com um
número de apontadores abaixo de ⎡n/2⎤
• repetir o procedimento recursivamente
ƒ Estes efeitos "sobem" pela árvore até chegar a um nó
que fique com pelo menos ⎡n/2⎤ apontadores
ƒ Se a raiz ficar só com um descendente depois da
remoção, então o descendente passa a ser a nova raiz
IST ▪ DEI ▪ Bases de Dados
37
Remoção em árvores B+ – exemplo 1
IST ▪ DEI ▪ Bases de Dados
38
19
Remoção em árvores B+ – exemplo 2
IST ▪ DEI ▪ Bases de Dados
39
Remoção em árvores B+ – exemplo 3
IST ▪ DEI ▪ Bases de Dados
40
20
Remoção em árvores B+ – exemplo 3
ƒ Neste exemplo a sequência da remoção é a seguinte:
1.
2.
3.
4.
5.
6.
7.
8.
9.
desaparece uma folha
no nó ascendente, desaparece o apontador para essa folha
pela p
p
propriedade
p
((C)) o nó ascendente fica abaixo do limite de n/2
apontadores, logo é necessário redistribuir
só há um nó vizinho à esquerda, redistribuir com esse
só são necessários 4 apontadores (porque só há 4 folhas), logo
ficam 2 apontadores em cada nó
se ficam 2 apontadores, fica apenas 1 valor em cada nó
pela propriedade (B) o valor que fica em cada um desses nós é o
menor valor da sub-árvore direita
f
a coerência do
depois de mexer no 2ºº nível é necessário verificar
nó ascendente (raiz)
pela propriedade (B) o valor que fica na raiz é o menor valor da
sub-árvore direita, ou seja, Mianus
IST ▪ DEI ▪ Bases de Dados
41
Exercício
ƒ Considere uma árvore B+ com n=3
a) A partir de uma árvore vazia, desenhe a árvore
após a inserção sucessiva dos seguintes valores:
1, 9, 5, 3, 6, 8, 4
b) Remova os mesmos elementos pela mesma
ordem até chegar a uma arvore vazia
IST ▪ DEI ▪ Bases de Dados
42
21
Exercício
a)
5
1
1
9
3
6
9
9
8
8
9
5
3
5
6
5
5
1
1
9
5
1
1
9
1
9
3
5
6
8
9
9
8
3
5
9
4
1
3
4
5
9
4
5
6
8
9
43
IST ▪ DEI ▪ Bases de Dados
Exercício
b)
8
1
4
3
5
9
4
5
6
8
6
5
9
4
4
8
3
4
5
6
8
4
6
8
6
8
4
4
8
4
8
IST ▪ DEI ▪ Bases de Dados
3
8
6
8
5
5
3
9
8
4
44
22
Bases de Dados
Índices do tipo hash
Hashing estático
ƒ Um contentor armazena um conjunto de registos
• tipicamente um contentor ocupa um bloco em disco
ƒ Ficheiros com organização do tipo hash
• função de hash determina contentor de um registo
▫ recebe como parâmetro o valor da chave de procura
▫ e devolve um apontador para o contentor
ƒ N
Num mesmo contentor
t t pode
d haver
h
registos
i t com
diferentes valores de chave de procura
• os contentores são pesquisados sequencialmente
IST ▪ DEI ▪ Bases de Dados
46
23
Hashing estático – exemplo
IST ▪ DEI ▪ Bases de Dados
47
Funções de hash
ƒ A função de hash ideal
• devia ser uniforme
▫ todos os contentores ficam com o mesmo número de
valores de chave de procura
• devia ser aleatória
▫ todos os contentores ficam com o mesmo número de
registos
ƒ Tipicamente, as funções de hash trabalham com a
representação
ã bi
binária
ái d
do valor
l d
da chave
h
d
de procura
• p.ex. somar todos os caracteres da string e fazer o
resto da divisão pelo número de contentores
IST ▪ DEI ▪ Bases de Dados
48
24
Overflow de contentores
ƒ Pode ocorrer overflow de contentores devido a
• número insuficiente de contentores
• desequilíbrio na distribuição dos registos
▫ múltiplos registos com a mesma chave de procura
▫ função de hash não uniforme
ƒ A probabilidade de overflow de um contentor não pode
ser eliminada
• é normalmente resolvida com contentores extra
(overflow buckets)
IST ▪ DEI ▪ Bases de Dados
49
Overflow de contentores
ƒ Encadeamento de contentores
IST ▪ DEI ▪ Bases de Dados
50
25
Índices do tipo hash
ƒ Pode ser usado hashing para organização de ficheiros
e para criação de índices
• os índices do tipo hash são normalmente usados
como índices secundários
IST ▪ DEI ▪ Bases de Dados
51
Índices do tipo hash – exemplo
IST ▪ DEI ▪ Bases de Dados
52
26
Problemas na utilização de hashing estático
ƒ A função de hash mapeia valores da chave de procura
para um número fixo de contentores
• se a BD crescer
crescer, desempenho sofre com excesso
de contentores extra
• mesmo que o tamanho possa ser previsto,
desperdício de espaço inicialmente
• se a BD diminuir, espaço desperdiçado
• reorganização periódica é dispendiosa
ƒ Solução: fazer variar o número de contentores
dinamicamente
IST ▪ DEI ▪ Bases de Dados
53
Hashing dinâmico
ƒ Permite modificar a função de hash dinamicamente
ƒ Hashing extensível
• a função de hash gera valores numa gama
alargada – tipicamente inteiros com 32 bits
• a ideia é usar em cada momento apenas um prefixo
para endereçar um conjunto de contentores
▫ seja i o comprimento do prefixo, com 0 ≤ i ≤ 32
▫ número máximo de contentores endereçáveis: 2i
▫ o valor de i varia conforme o tamanho da BD
IST ▪ DEI ▪ Bases de Dados
54
27
Hashing dinâmico – exemplo
comprimento do prefixo ( i )
comprimento do prefixo
para cada contentor ( ij ≤ i )
IST ▪ DEI ▪ Bases de Dados
55
Hashing dinâmico – procura
ƒ
O prefixo com i bits permite localizar a entrada
correcta no índice
• mas o mesmo contentor pode ser usado em várias
entradas
▫
ƒ
significa que o prefixo do contentor (ij) é ≤ i
Para encontrar o contentor com chave Kj
1. calcular funcao_hash(Kj) = X
2 usar os primeiros i bits de X para localizar a
2.
entrada no índice
3. seguir o apontador respectivo para o contentor
IST ▪ DEI ▪ Bases de Dados
56
28
Hashing dinâmico – inserção
ƒ Para inserir um registo com chave Kj
• encontrar o contentor para essa chave
• se houver espaço no contentor, inserir o registo
• senão, separar o contentor em 2 e redistribuir os
registos
▫ eventualmente será necessário aumentar i
IST ▪ DEI ▪ Bases de Dados
57
Hashing dinâmico – inserção (caso ij = i )
ƒ Como separar em 2 um contentor para a chave Kj
• se ij = i (só há um apontador para o contentor j )
▫ incrementar o valor de i , o que duplica o tamanho do
índice
▫ substituir cada entrada no índice por 2 entradas que
apontam para o mesmo contentor
▫ alocar novo contentor z e fazer ij = iz = i
▫ colocar o segundo apontador de j a apontar para z
▫ remover e re-inserir
re inserir cada registo em j (agora com novo ij )
▫ alguns registos vão parar a j , outros a z
▫ inserir o novo registo com chave Kj
IST ▪ DEI ▪ Bases de Dados
58
29
Hashing dinâmico – inserção (caso ij < i )
ƒ Como separar em 2 um contentor para a chave Kj
• se ij < i (mais do que um apontador para j )
▫ alocar novo contentor z e fazer ij = iz = ij + 1
▫ a segunda metade das entradas que apontam para j
passam a apontar para z
▫ remover e re-inserir cada registo em j (agora com novo ij )
▫ alguns registos vão parar a j , outros a z
▫ inserir o novo registo com chave Kj
▫ se o contentor
t t ainda
i d estiver
ti
cheio,
h i repetir
ti recursivamente
i
t
um dos casos ij = i ou ij < i , como apropriado
▫ se o contentor permanecer cheio, usar um contentor extra
(i atingiu o máximo e há overflow)
IST ▪ DEI ▪ Bases de Dados
59
Hashing dinâmico – inserção
IST ▪ DEI ▪ Bases de Dados
60
30
Hashing dinâmico – inserção
IST ▪ DEI ▪ Bases de Dados
61
Hashing dinâmico – inserção
IST ▪ DEI ▪ Bases de Dados
62
31
Hashing dinâmico – inserção
IST ▪ DEI ▪ Bases de Dados
63
Hashing dinâmico – inserção
IST ▪ DEI ▪ Bases de Dados
64
32
Hashing dinâmico – remoção
ƒ Para apagar um valor de chave
• localizar o registo dentro do seu contentor e
removê-lo
• se o contentor ficar vazio, removê-lo e actualizar o
índice
• é possível juntar contentores que tenham o mesmo
número ij e o mesmo prefixo nos primeiros ij -1 bits
• é possível diminuir o tamanho do índice
▫ operação dispendiosa, só feita quando o número de
contentores é muito menor que o número de entradas no
índice
IST ▪ DEI ▪ Bases de Dados
65
Bases de Dados
Índices e SQL
33
Definição de índices em SQL
ƒ Criação de um índice
create index index_name
on relation_name(attribute_list)
• exemplo
▫ create index branch_index on branch(branch_name)
• é possível escolher o tipo de índice com
▫ … using btree ou using hash
ƒ Para eliminar um índice: drop index index_name
IST ▪ DEI ▪ Bases de Dados
67
Índices com múltiplas chaves de procura
ƒ Certas perguntas exigem múltiplos índices
select account_number
from account
where branch_name = ”Perryridge” and balance = 1000
• estratégias possíveis
▫ usar índice p
para branch_name e testar valor de balance
▫ usar índice para balance e testar valor de branch_name
▫ usar índice para branch_name e outro para balance e
intersectar os resultados
IST ▪ DEI ▪ Bases de Dados
68
34
Chaves de procura compostas
ƒ Chaves de procura compostas
• chaves de procura com mais de um atributo
• p.ex. (branch_name, balance)
ƒ Ordem lexicográfica
• (a1, a2) < (b1, b2) se
▫ (a1 < b1) ou
▫ (a1= b1) e (a2 < b2)
IST ▪ DEI ▪ Bases de Dados
69
Chaves de procura compostas
where branch_name = "Perryridge" and balance = 1000
ƒ O mesmo índice pode ser usado para obter os registos
que satisfazem as duas condições
• mais eficiente que usar índices separados
• também é eficiente noutros casos
▫ where branch_name = "Perryridge" and balance < 1000
• não é eficiente em
▫ where branch_name < "Perryridge" and balance = 1000
▫ obtém registos que satisfazem a primeira mas não a
segunda condição
IST ▪ DEI ▪ Bases de Dados
70
35
Download