Indíces - Departamento de Ciência de Computadores

Organização Fı́sica dos Dados em BDs
Pesquisa binária em árvores-B+
5
K1= 5
K2=9
3
1
3
Bases de Dados
7
5
6
8
7
8
9
Fernando Silva / Departamento de Ciência de Computadores
12
23
Organização Fı́sica dos Dados em BDs
Índices em SQL
criar um ficheiro de ı́ndices sobre um atributo de uma dada tabela:
CREATE INDEX NomeIndex ON NomeRelaçãoR(Atributo)
para remover o ı́ndice:
DROP INDEX NomeIndex
A escolha de qual o ı́ndice a criar não é fácil. Não deve ser criado para relações onde ocorram muitas
alterações nos seus tuplos. Porquê?
Exemplo: indexar o atributo a relação empregado com base no departamento.
CREATE INDEX EmpDepIndex ON Empregado(Ndep)
Indices em SQL: não é standard (até SQL2).
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
24
Organização Fı́sica dos Dados em BDs
Definição de árvore-B+ de ordem p
nó-interno:
1. "!"!"!#%$'&()$+* , ,.-0/ e #1 apontador para uma sub-árvore B+.
2. Em cada nó-interno, têm-se que 23%45!"!"!3.$'& .
3. Para qualquer valor de pesquisa 6
na sub-árvore apontada por , têm-se que:
.17&2869-3.1:<;=?>@?, ou 6A-B.1CD>FEG; ou .1H3;I86D>FEB, .
4. Cada nó tem no máximo p-apontadores.
5. Cada nó, excepto a raı́z, tem pelo menos JDKL/NMPOPQSR (condição de overflow) apontadores para sub-árvores. A raı́z tem pelo
menos dois apontadores para sub-árvores, se for um nó interno.
6. Um nó interno com q apontadores, ,.-0/ , tem ,TH; valores de pesquisa.
nó-folha:
1. IU#V'I*I"W NVSX*I"!"!"!)YU.$'&NV:$'&=*ITZ)["\]=* , ,-?/ , NV^1 apontador para bloco em disco que contém
um registo cujo campo de pesquisa é .1 , e _Z
["\] apontador para nó-folha seguinte.
2. Em cada nó-folha têm-se que 2 45!"!"!%$'&(,.-0/ .
4. cada nó-folha tem pelo menos `DKL/NMPOPQSa valores (condição de underflow).
5. todos os nós-folha estão ao mesmo nı́vel.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
21
Organização Fı́sica dos Dados em BDs
Vantagens de uma árvore-B+
Uma das vantagens destas estruturas é que qualquer caminho da raı́z a uma qualquer das folhas tem
igual comprimento (árvores equilibradas).
Em média, uma árvore-B+quando estável, tem cada nó apenas 69% cheio. Sendo assim, quantos nós
e entradas tem, em média, uma árvore-B+de ordem p=34.
Cada nó tem em média apontadores e valores.
raı́z:
1 nó
22 entradas
23 apontadores
nı́vel 1:
23 nós
506 entradas
529 apontadores
nı́vel 2:
529 nós 11638 entradas 12167 apontadores
nı́vel-folhas: 12167 nós 255507 apontadores para registos
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
22
Organização Fı́sica dos Dados em BDs
Número de acessos a blocos (cont. slide 14)
b EBcededfded registos; g Eihjded bytes por registo; klEGhjdemPn bytes por bloco em disco; op b EGhqd registos por bloco.
indice-secundário denso: grsEGhqt bytes por registo; op b r2Evuxw registos por bloco;
b r2E5cdedfded registos (um apontador por cada registo do ficheiro a indexar).
o r EyJDK b r M"op b r QSR=EyJDK^cededfdedfMPufwQSR+E3nenm blocos no ficheiro de ı́ndices.
pesquisa binária: JDKCzx{P|erSo~}(QSR=E5 acessos. Procurar um registo envolve 10 acessos a blocos em disco.
ı́ndice-primário: op b€ E5ufw registos por bloco; b€ EBcdfded registos (um apontador para cada bloco do ficheiro a indexar).
o € EyJDK^cdedfdeMPuxwQSR4E3nt blocos no ficheiro de ı́ndices.
Pesquisa binária: Jƒ‚7„†… r o € R=E5u acessos.Procurar um registo envolve 7 acessos a blocos em disco.
indice nı́veis-múltiplos: 1o nı́vel igual a ı́ndice secundário b€ E5cededfd registos, op b€ E5ufw registos por bloco.
b € SQ R=E3nfnm blocos no 1o nı́vel.
o € y
E JDK b€ M"op 
¯
€
o r EyJDK‡o M"op b € SQ R=EyJDKƒnfnmfMPufweQSRˆEB‰ blocos no 2o¯ nı́vel.
oŠ2EyJDK‡orM"op b€ SQ R=EyJDK^‰eMPufweQSR_EGh bloco no 3o¯ nı́vel.
Procurar um registo envolve aceder um bloco em cada nı́vel e um bloco do ficheiro, neste caso 4 acessos a blocos.
Bases de Dados
19
Fernando Silva / Departamento de Ciência de Computadores
Organização Fı́sica dos Dados em BDs
Árvores de pesquisa: Árvores B+
Os ı́ndices de nı́veis-múltiplos são normalmente implementados como árvores B+, que deixam algum
espaço livre em cada bloco de forma a simplicar adição e remoção de valores.
Uma árvore-B+tem uma estrutura diferente para nós-internos e nós-folha:
no-interno:
P1
...
K1
Ki-1
Pi
X
Ki
...
Kq-1
X
X
X<=K1
Pq
Kq-1 < X
Ki-1 < X <= Ki
no-folha:
K1
Pr1
K2
Pr2
...
Ki
Pri
...
Kq-1 Prq-1
Pnext
no-folha
seguinte
Pri = apontador para o bloco de um ficheiro que contem o registo cujo campo depesquisa e’ Ki
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
20
Organização Fı́sica dos Dados em BDs
Índices-secundários (cont.)
Também podemos ter ı́ndices-secundários sobre campos não-chave do ficheiro de dados. Neste caso
teremos vários registos do ficheiro apontados com o mesmo valor de indexação.
campo-index.
ficheiro de indices-secundario
campo-index.
aptr-seq-aptrs
Sequencia de aptrs para blocos
1
2
2
5
1
3
4
1
6
2
3
4
5
...
6
...
1
3
4
5
...
ficheiro de dados
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
17
Organização Fı́sica dos Dados em BDs
Índices de nı́veis-múltiplos
No¯ de acessos em esquemas de indexação anteriores: K‹‚7„†… Œ 1‹Q blocos ( Œ 1 = no¯ de blocos do fich. de indexação).
Índices de nı́veis-múltiplos: no¯ de acessos a blocos = ‚7„†…fƒŽ V‹<Œ 1 , com Œ‘ 1)’5O (blocking-factor E“`DK:”.MP•=QSa ).
1o. nivel
2
8
15
24
2o. nivel
2
35
55
85
35
39
44
51
55
68
71
80
85
Bases de Dados
2
5
8
12
15
21
24
29
35
36
...
ficheiro-dados
Fernando Silva / Departamento de Ciência de Computadores
18
Organização Fı́sica dos Dados em BDs
Índices cluster
Se os registos de um ficheiro estiverem ordenados por um campo não-chave, então poderemos ter
registos com valores iguais para este campo e podemos pensar criar um ficheiro de ı́ndices-cluster
para indexar apenas os valores diferentes (i.e. o primeiro valor de cada cluster). Este campo designase por campo-cluster. Problemas com adição ou remoção de registos.
campo-cluster
aptr-bloco
1
Pnome
Ana
Catarina
EBI
DNasc
End
Sal
...
Ndep
1
1
Eduardo
1
Gabriel
Antonio
1
2
2
3
...
4
...
...
ficheiro de indices-cluster
Anabela
3
Carla
Carlos
3
3
...
4
Diana
ficheiro ordenado
Bases de Dados
15
Fernando Silva / Departamento de Ciência de Computadores
Organização Fı́sica dos Dados em BDs
Índices-secundários
Indexam ficheiros de registos não ordenados e em geral incluem um registo (valor e apontador) para
cada registo desse ficheiro. Se o campo de indexação for um campo-chave do ficheiro a indexar, então
o ficheiro de ı́ndices-secundário é denso. Requerem mais memória e maior tempo de procura do que
ı́ndices-primários.
ficheiro de indices-secundario
campo-index.
aptr-bloco
12345
12543
15682
Pnome
Ana
Catarina
EBI DNasc
12345
21346
Eduardo
12543
1
Gabriel
Antonio
32654
15682
1
2
Anabela
19065
3
Carla
Carlos
26540
50972
3
3
Diana
36458
...
19065
21346
26540
32654
...
...
End
...
...
Sal
Ndep
1
1
4
ficheiro de dados
campo chave
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
16
Organização Fı́sica dos Dados em BDs
Indice primário
Ê
o ı́ndice corresponde ao campo chave de ordenação de um ficheiro ordenado.
campo-index.
Pnome
Ana
Anabela
aptr-bloco
Ana
EBI
DNasc
End
Sal
Ndep
...
Antonio
Carlos
Carla
Catarina
Diana
...
...
Carlos
...
Diana
Eduardo
ficheiro de indices
...
Gabriel
ficheiro ordenado
Ê
o maior inconveniente nos indices primários é inserir e remover registos (obriga a
ordenação no ficheiro e possivelmente nos indices).
Bases de Dados
13
Fernando Silva / Departamento de Ciência de Computadores
Organização Fı́sica dos Dados em BDs
Número de acessos a blocos (ficheiro vs. indice primário).
Ficheiro ordenado com r= 30,000 registos. Tamanho de um registo, R=100 bytes. Tamanho dos
blocos em disco, B=1024 bytes.
–˜—™›šœYžCŸ¡ ¢¡£D¤¥š¦œYžY§¨©)ª« ¡§¨¨£D¤š¬§¨
registos por bloco.
š¦®"žC¯# Número de blocos necessário para o ficheiro ordenado: ­
­x°
¯N£D±¥š
blocos.
š³²x´
Pesquisa linear sobre o ficheiro: ­ acessos a blocos, em média.
®Sµ·¶¸#¹ ±š³²
­
acessos a blocos em média.
Pesquisa binária:
Construção do ficheiro de ı́ndices-primário (um ı́ndice para cada bloco): campo de indexação: º
š
š³²x´
bytes; apontador para blocos: »
bytes.
bytes. Registo do ficheiro de ı́ndices: ¼¾½
–˜—™F¿Àš¦œYžSŸ« ¢¡¿<£NœDšÁœYž"§¨©)ª¡ «§Â
£#œÃšÅÄÆ
¯
½
š
entradas e ­q½
š
registos por bloco, no ficheiro de ı́ndices.
š®YžC¯ ¯ £D±š®Yž
£(±Èš
½ ­x° ½
Ç
®Sµ·¶¸#¹
´
blocos no ficheiro de ı́ndices.
±Éš
Pesquisa binária no fich. de ı́ndices:
­q½
acessos. Portanto, em média são necessários 7
acessos para encontrar um registo de um ficheiro através de um fich. ı́ndices-primário.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
14
Organização Fı́sica dos Dados em BDs
Ficheiros de Hash (ou de acesso directo)
Ê
os registos estão organizados com base numa técnica de hashing
a procura tem por base uma condição de igualdade num único campo –campo de
hash.
Ê
acesso muito rápido com base no campo de hash.
Ê
Hashing: seja ËÌ<Í uma função de hash e Î um valor do campo de hash de um registo
Ï
, então ËÐÌÎXÍ determina o endereço do bloco em disco no qual está armazenado Ï .
campo de hash
registo r:
v
h( v ) = endereco do bloco em disco
Vantagem: normalmente só precisamos de aceder a um único bloco para determinar o registo.
Desvantagem: acesso por outros campos é dispendioso. Problemas de colisões.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
11
Organização Fı́sica dos Dados em BDs
Estruturas de Indices para Ficheiros
Índices ou estruturas de acesso são estruturas de dados que vizam acelerar o acesso
a registos em resposta a determinadas condições de procura.
Uma estrutura de ı́ndices é um ficheiro em que cada registo se compõe de:
Ê
um campo de indexação - um campo do ficheiro.
Ê
um apontador para um bloco em disco - que contém registos com valor igual ao
campo de indexação.
A ideia é que sendo o ficheiro de ı́ndices mais pequeno, podemos tê-lo em memória
e usá-lo para localizar mais depressa os registos nos blocos do disco.
Num ficheiro de ı́ndices, os registos estão ordenados pelo valor do campo de indexação.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
12
Organização Fı́sica dos Dados em BDs
Ficheiros Ordenados
Ê
ficheiros em que os registos estão fisicamente ordenados (nos blocos) com base nos
valores de um dos seus campos (campo de ordenação).
Vantagens:
acesso por ordem a registos é muito eficiente.
encontrar o registo seguinte (por ordem) é muito eficiente (normalmente está no mesmo bloco).
procura com base num valor do campo de ordenação é muito rápida (uando pesquisa binária).
se o acesso a registos não for com base no campo de ordenação não há qualquer vantagem sobre
ficheiros não-ordenados.
Desvantagens:
inserir e remover registos são operações dispendiosas, porque os registos têm de permanecer ordenados após estas operações.
A inserção obriga a determinar a posição no ficheiro e arranjar espaço para o registo, o que obriga ao deslocamento
de registos para outros blocos para libertar espaço. Melhora-se a eficiência, usando um ficheiro de transações e fazendo
periodicamente a junção desse ficheiro com o ficheiro-master para produzir um novo. Procura fica mais complexa.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
9
Organização Fı́sica dos Dados em BDs
Exemplo de ficheiro (de registos) ordenado
Bloco 1
Pnome
Ana
Anabela
EBI
DNasc
End
Sal
Ndep
...
Antonio
Bloco 2
Carla
Catarina
Carlos
Bloco n
Teresa
Tiago
...
...
...
Zacarias
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
10
Organização Fı́sica dos Dados em BDs
Operações sobre ficheiros
leitura: não alteram o ficheiro, apenas se localizam determinados registos para que os seus campos
possam ser lidos.
escrita: alteram o ficheiro, removendo ou adicionando registos ou actualizando campos de registos.
Algumas das operações mais habituais:
find - procura um registo que satisfaz a condição de procura. Transfere o bloco que contém esse registo para um buffer
em memória, se não estiver já lá. Passa a ser o registo corrente.
read - copia o registo corrente do buffer para uma variável do programa. Actualiza o apontador para o registo corrente.
insert - insere um novo registo no ficheiro. Localiza o bloco onde o registo deve ser inserido, transfere esse bloco para
memória (buffer), escreve o registo no bloco em buffer e escreve o bloco em buffer para o disco.
Outras operações: delete, findnext, modify, findall, open, close, etc.
Estas operações determinam métodos de acesso ao sistema de ficheiros. É também importante conhecer a organização do ficheiro, i.e. como é que os blocos e registos são armazenados em disco e
como são interligados.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
7
Organização Fı́sica dos Dados em BDs
Ficheiros Heap (registos não-ordenados)
Ê
tipo de organização mais simples e básica.
os registos são colocados no ficheiro pela ordem e que são inseridos; novos registos
são adicionados no fim do ficheiro.
Ê
Vantagens:
Ê
inserção é muito eficiente. O último bloco em disco é copiado para memória, o
novo registo é adicionado e o bloco é re-escrito para o disco.
Desvantagens:
Ê
procurar um registo não é eficiente, envolve pesquisa linear sobre os blocos em
disco.
Ê
remoção de registos conduz a blocos com buracos.
Ê
necessário re-organizar periodicamente o ficheiro para recuperar espaço.
Ê
leitura de todos os registos por ordem de um determinado campo, obriga a criação
de uma cópia (ordenação é aqui uma operação dispendiosa).
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
8
Organização Fı́sica dos Dados em BDs
Ficheiros e registos
Os dados de uma BDs são guardados em ficheiros de registos.
Cada registo é um conjunto de valores relacionados, onde cada valor representa um atributo de uma
relação. Um registo corresponde aos valores de um tuplo de uma relação.
O formato de um registo é definido pelo tipo de dados que a tabela, que o registo representa, pode
tomar (definido no CREATE TABLE).
BDs recentes precisam de guardar objectos não estruturados, como sejam imagens, video ou audio,
ou texto livre. Estes objectos são designados por BLOBs (Binary Large Objects).
Se uma tabela contém um atributo do tipo BLOB, o registo de tuplos dessa tabela terá na sua estrutura
apenas um apontador para um bloco em memória onde está guardado o BLOB.
Os registos podem ter comprimento fixo ou variável (depende do tipo de dados). Se tiverem comprimento variável existem marcas-especiais para indicar a separação entre campos.
Bases de Dados
5
Fernando Silva / Departamento de Ciência de Computadores
Organização Fı́sica dos Dados em BDs
Associação registos e blocos em disco
Dado que a unidade de transferência para o disco é um bloco, para guardarmos os
dados de uma BDs em disco temos de fazer corresponder os registos aos blocos em
disco.
Tamanho do bloco: B bytes; Tamanho do registo: R bytes; Supõe-se
Ñ
ÒÔÓ
, então:
bfr= Õ<ÑyÖ2ÓW× dá-nos o número de registos por bloco (blocking factor).
O objectivo de uma boa organização de ficheiros é o de permitir a localização do
bloco em disco que contém um determinado registo envolvendo um número mı́nimo
de transferências de blocos do disco para memória.
A verificação se um dado registo está entre os registos guardados num dado bloco só
pode ser feita com uma cópia do bloco em memória (depois de carregado do disco
para memória), e pode ter-se de carregar mais do que um bloco até se encontrar o
registo pretendido.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
6
Organização Fı́sica dos Dados em BDs
Armazenamento de BDs
Habitualmente, as BDs possuem grandes volumes de dados que têm de existir durante
grandes perı́odos de tempo.
Usam-se discos magnéticos para a BDs porque:
Ê
Em geral, a BDs é demasiado grande para ficar na memória central.
menor risco de perdas de informação, pois os discos são não-voláteis.
menor custo.
Ê
Ê
Assim, é importante conhecer técnicas de armazenamento de informação em discos.
O acesso a uma parte parte da BDs pelo DBMS envolve:
Ê
Ê
Ê
localizar em disco a parte da BDs que se pretende aceder;
copiar essa parte para memória para processamento,
e em caso de alteração, reescrer essa parte no disco.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
3
Organização Fı́sica dos Dados em BDs
Discos
Os discos magnéticos são habitualmente constituı́dos por várias placas circulares empilhadas com
cabeças de leitura/escrita independentes.
Cada placa contém um conjuntos pistas circulares e concêntricas (capacidade até 150 Kbytes).
O conjunto das várias pistas com o mesmo diâmetro nas várias placas, constitui um cilindro. A
informação guardada num mesmo cilindro pode ser acedida mais rápidamente.
Cada pista é normalmente dividida em sectores ou blocos (de tamanho fixo).
A divisão de um disco em blocos (ou páginas) de um determinado tamanho é feita pelo S.O. quando
da formatação do disco. O tamanho tı́pico de blocos em disco é de 512 bytes a 4Kbytes.
A leitura de um bloco do disco está condicionada por vários factores:
tempo-posicionamento da cabeça de leitura na pista correcta
tempo-de-rotação do disco até que o bloco pretendido fique em posição
tempo-de-transferência de um bloco do disco para memória.
O tempo total de leitura é a soma destes 3 tempos. Os mais significativos são os de posicionamento e
rotação. Tempos de acesso a disco: 6 a 10 ms.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
4
Organização Fı́sica dos Dados em BDs
Estruturas de Armazenamento de Informação
Onde e como guardar os dados que constituem a Base de Dados?
Ê
Estrutura lógica onde guardar: ficheiros de registos.
Ê
Estrutura fı́sica onde guardar: (tipicamente) discos magnéticos.
Hierarquia de Memória:
Memória primária - pode ser acedida directamente pelo CPU: memória central (RAM) e caches.
acesso rápido, mas é volátil e de capacidade limitada devido ao custo (actualmente grandes servidores de BDs têm Û 1GB de RAM).
Memória secundária - inclui periféricos de maior capacidade e de menor custo, e.g. discos
magnéticos ou ópticos, e tapes.
a informação neste tipo de memória não pode ser processada directamente pelo CPU, tem de ser
primeiro copiada para a memória central.
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
1
Organização Fı́sica dos Dados em BDs
Tipos de memória e periféricos
Caches: static RAM; memória mais cara; acelera execução de progamas.
Memória central: dynamic RAM (DRAM); volátil; menor custo que cache mas mais lenta.
Discos magnéticos: maior capacidade e menor custo que DRAM.
Juke-boxes de discos ópticos; Write-Once-Read-Many (WORM); usa discos CD-ROM; mais lentos
que discos magnéticos.
DVD (Digital Video Disk): mais recente e de maior capacidade (4 a 15 GB por deisco).
Fitas magnéticas (tape juke-boxes): o meio mais barato de armazenamento e normalmente usado para
backups.
Unidades de medida:
1 byte = 8 bits (bit toma valor 0 ou 1)
š²
1 kilobyte = ~ØÃÙ ¹
e bytes
š³²
²
e e bytes
1 Megabyte = Ù
š²
²
²
1 Gigabyte = ÚÙ
e eÐ e bytes
²
1 Terabyte = gigabytes
Bases de Dados
Fernando Silva / Departamento de Ciência de Computadores
2