Conceitos Fundamentais em Sistemas de Bancos de Dados e suas

Administração de Banco de Dados – Aula 2
Agora vamos falar de índices .
O que são índices ???
Imagine você procurar algum conhecido num condomio com diversos apartamentos
por andar e sem poder consultar uma lista , em ordem alfabética, pelo nome de cada
morador. Você teria que procurar o seu conhecido , andar por andar , apartamento
por apartamento.
Pois é o SQL permite criar lista indexadas , isto é em ordem alfabética ou numérica
para um ou mais campos de uma tabela.
Para cada índice criado , são também criados arquivos de índices com ponteiros para
o arquivo principal.
Alguns softwares de banco de dados , pemitem que estes arquivos de índices sejam
armazenados num outro disco , diferente de onde foram armazenadas as tabelas
principais . Isto possibilita deixar um disco exclusivo para índices , com o objetivo de
poder obter mais performance.
Matricula
5555
1111
6666
2222
Nome
Jose Pereira
Ana Lima
Maria Santos
Jorge Gomes
Estado
SP
RJ
SP
SP
Cidade
SÃO PAULO
RIO DE JANEIRO
CAMPINAS
SANTOS
Se for criado um índice em matricula , o arquivo de índice ficara com esta imagem
Matricula
1111
2222
5555
6666
Numero do
Registro da tabela
principal
2
4
1
3
O Numero do Registro da tabela principal é o ponteiro para a tabela principal.
Este arquivo de índices só contem as colunas do índice , que podem ser composta por
mais de uma coluna e a coluna do ponteiro e é este arquivo de índice que sempre é
reorganizado e não mais a tebela principal
Quando usamos a chave primária , a tabela fica sempre reorganizada pelos campos
que compõe o índice e portanto tem a vantagem de num acesso SELECT ser mais
rápido por não ter que percorrer o arquivo de índices , porem o processo de inserção ,
exclusão e alteração que envolve um campo chave , acaba sendo mais lento , pelo fato
de ter que ser feita a reorganização.
Cada vez que for inserido , alterado ou excluído um registro que contem índices , o
SQL vai reorganizar a tabela de índices e portanto pode gastar mais tempo em cada
atualização , fora o espaço em disco que é consumido para cada arquivo. Os arquivos
de índices não ficam visíveis para os usuários e são gerenciados pelo SGBD e são
criados um arquivo para cada índice , associado a tabela origem.
Para que sejam minimizadas as reorganizações nos arquivos índices , o software de
banco de dados (SGBD) permite que o DBA interfira dizendo o quanto de registros
vazios deseja que a tabela de índices fique.
Matricula
Numero do
Registro da tabela
principal
1111
2
2222
4
5555
1
6666
3
Estes buracos , no arquivo de índice são criados através da opção que existe no
comando de criação de índices . No caso do SQL server , ele tem o nome de FILL
FACTOR , onde é definido um percentual de buracos.
Quanto menos buracos , FILL FACTOR com zero , a consulta fica mais rápida , com
um Fill Factor maor , serão favorecidos os módulos de a inserção , exclusão e
alteração.
Acesso aos Dados
O SQL Server acessa os dados de dois métodos.
- Examinando todas as páginas de dados das tabelas – chamado de exame de tabela
ou TABLE SCAN (varredura da tabela inteira)
Ao executar um exame de tabela o SQL Server:
• Começa no inicio da tabela.
• Examina todos os registros da tabela, página a página.
• Extrai os registros que satisfazem os critérios da consulta.
- Usando índices. Ao usar um índice, o SQL Server:
• Percorre a estrutura da árvore do índice para localizar os registros solicitados pela
consulta.
• Extrai apenas os registros necessários que satisfazem os critérios da consulta
Como Criar Índices?
Ao criar índices, leve em conta dois fatores: a natureza dos dados e a natureza das consultas
realizada nas tabelas.
Natureza dos dados – Quando me refiro a natureza de dados, sempre levo em conta o tipo
da coluna em que colocarei um índice, é recomendável que índices estejam em colunas do
tipo: int, char, bigint, tinyint, smallint e datetime.
Natureza das consultas – As naturezas das consultas se referem em qual campo da minha
tabela devo criar um índice, exemplo: Imagine um cenário que tenho que criar uma
consulta que traga os pedidos emitidos por data de emissão e que a situação seja somente
os pedidos liberados. Naturalmente na minha tabela de pedidos eu teria o campo data de
emissão e situação do pedido. Essas são colunas aconselháveis para a criação de índices.
Os índices são úteis, porém consomem espaço em disco e acarretam custos de manutenção
e sobrecarga. Não crie um índice que não será usado com freqüência.
Índice Clustered ou Agrupamento
Esse tipo de índice é útil para as colunas pesquisadas com freqüência ou em busca de chave
de valores. Ao criar um índice desse tipo, considere as seguintes diretrizes:
• Cada tabela só pode ter um índice clustered.
• A ordem física dos registros da tabela e a ordem dos registros do índice são iguais.
Primeiramente é aconselhável que seja criado o indice clustered antes do índice não
clusterizado.
• Quando um registro é excluído, o espaço é restaurado e torna-se disponível para outro
registro.
• Quando se cria uma coluna como PRIMARY KEY, automaticamente essa coluna torna-se
um índice clustered.
• O tamanho médio de um índice clustered é aproximadamente 5% do tamanho da tabela.
No entanto, esse tamanho varia dependendo do tamanho da coluna indexada.
Índice No-Clustered ou Sem Agrupamento
Um índice não clusterizado é eficiente quando os usuários precisam de vários critérios de
pesquisa. Por exemplo, um vendedor pode pesquisar na tabela de pedidos pelo número do
pedido, data de emissão, cliente e o representante deste cliente. Ao criar um índice não
clusterizado, considere os seguintes fatos:
• A ordem das páginas no nível folha se um índice não clusterizado é diferente da ordem
• Pode existir até 249 índices não clusterizado por tabela.
• O SQL Server recria automaticamente os índices não clusterizados existentes quando
ocorre uma das situações a seguir:
• Um índice clustered é criado.
• Um índice clustered é descartado.
• A opção DROP_EXISTING é usada para alterar as colunas que definem o índice de
agrupamento.
Colunas que devem ser indexadas.
Crie índices em colunas pesquisadas com freqüências como:
• Chaves primárias (PK).
• Chaves externas (FK) ou colunas usadas frequentemente para unir tabelas.
• Colunas pesquisadas para a localização de faixa de valores de chave. (quando usamos a
clausula IN, NOT IN, BETWEEN).
• Colunas acessadas na ordem de classificação (ORDER BY)
• Colunas usadas durante a agregação. (GROUP BY)
Colunas que NÃO devem ser indexadas.
Não referencie colunas que:
• Raramente são usadas em um consulta.
• Colunas que retornam uma grande quantidades de dados , por exemplo um SELECT com
Where de intervalo entre datas muito espaças.
• Contenham poucos valores únicos. Por exemplo, um índice em uma coluna com dois
valores, Masculino e feminino, retorna uma alta porcentagem de registros.
• Sejam definidas com os tipos de dados text, ntext, varchar e image. Não é possível
indexar colunas com esses tipos de dados.
Criando índices.
Ao criar índice em uma tabela considere os fatos:
• Você deve ser proprietário da tabela.
• Quando se cria uma constraint do tipo PRIMARY KEY e UNIQUE, automaticamente o
SQL Server cria índices clustered para elas.
• Você pode criar índices em VIEWS
• Você não pode criar índice em colunas que já existam os mesmos.
• Você pode criar índice na hora de criação de sua tabela.
Sintaxe:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX nome_indice ON tabela (nome_coluna1 [, nome_coluna2, ...n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fator_preenchimento]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
on
CREATE NONCLUSTERED INDEX nomeDoIndice ON Tabela(CampoIndexado)
Em um de meus artigos anteriores eu mostrei a criação de tabelas para um e-commerce, e é
exatamente em uma dessas tabelas que criaremos o índice.
Na prática:
CREATE NONCLUSTERED INDEX IdxEmpresaID ON DptoEmpresa(EmpresaId)
Apagando índice
Ao apagar um índice você deve levar em conta as seguintes diretrizes:
• Você não pode apagar os índices criados pelas restrições PRIMARY KEY e UNIQUE.
• Você não pode apagar os índices das tabelas do sistema.
• Você deve está no banco que reside o índice para apagá-lo.
Sintaxe:
DROP INDEX Tabela.NomeDoIndice
Na prática:
DROP INDEX DptoEmpresa.IdxEmpresaID
Até a próxima e espero que todos tenham gostado.