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
