Índice Não Agrupado

Encontro SQL BH
May 2016
Desmistificando Índices com SQL Server
Apoio
Local Chapters
5
Upcoming SQL Saturdays
Brazil
• Jun 18
Rio de Janeiro #512
Visit www.sqlsaturday.com to
register for an event near you!
Agenda
•
•
•
•
•
7
Conceitos Fundamentais
Estatísticas
Índice agrupado
Índice não agrupado
Índices Filtrados
Conceitos Fundamentais
8
Como o SQL acessa dados?
• Table Scan
• Índice
9
Table Scan
BOM PASSEIO!!!
1
0
Com Índice
1
1
Estrutura dos Índices
• Normalmente baseado em estruturas de árvore
• O nó mais alto é o raiz e o mais baixo os
chamados nós folha
Página Índice
Raiz
Nós
Nós Folhas
Tabela
1
2
Páginas Índices
Diferentes tipos de dados e índices
• Números
• Pequenos e por isso rápidos para navegar.
• Se incremental, melhor ainda.
• Textos
• Contrário dos números
• Datas
• Só perdem para os números. Ótimos para ordenar e obter
intervalos.
• GUID
• Alguns dizem ser grande. Mas não é! Somente 16 bytes e pode
ser comparado de maneira binária. Portanto muito rápido.
• BIT
• Equivocadamente dizem que não deve ser indexado
1
3
Seletividade, densidade
• Seletividade
• Quantas linhas serão retornadas em uma pesquisa,
comparado ao total de linhas que existem
• Alta seletividade significa retornar um pequeno
conjunto de linhas dentro do universo de dados
• Número de Valores Exclusivos / Número Total de
Registros
CPF X SEXO
1
4
Seletividade, densidade
• Densidade
• Na física – Massa / Volume
• Oposto a seletividade. Quando mais denso, menos
seletivo normalmente, devido ao grande número de
repetições.
“MAIS DO MESMO”
1
5
Porque falar sobre isso?
• Chave primária é normalmente o índice mais
seletivo
• Indexar colunas de baixa seletividade não vale a
pena
• Lembrar que índices secundários são um ponteiro
para o índice primário. Alta densidade significa
centenas ou milhares de ponteiros para o
primário.
• Escolha da ordem das colunas em um índice
composto
• Otimizador de consultas faz uso destas
estatísticas para escolher como criar os planos de
execução
1
6
Quiz
Você é DBA na sua empresa e o gerente de Recursos
Humanos pediu a você que otimizasse a consulta de
empregados para que ficasse o mais rápida possível.
Qual coluna da tabela você usaria para filtrar rapidamente
um empregado?
1. Sobrenome
2. O GUID
3. A data de admissão
4. A idade
5. O departamento
1
7
Primeiro Mito
Índice deixa minhas alterações lentas?
1
8
Estatísticas
19
Estatísticas
Criadas automaticamente com os índices
Composto internamente de 03 partes:
1. Cabeçalho
2. Vetor de Densidade
3. Histograma
DBCC show_statistics(‘nome da tabela ou view’, ‘nome da estatística’);
2
0
Estatísticas
2
1
Estatísticas - Histograma
•
•
•
•
2
2
Retrata a distribuição de valores da coluna
Mede a frequência de valores exclusivos
Em resumo: calcula a Densidade / Seletividade
Traz os passos usados para criar as estatísticas
Estatísticas - Cabeçalho
Traz data e hora da última atualização
Número total de linhas na ocasião
Número de Passos no histograma (Máximo 200)
Informa se é uma estatística agrupada para
consultas de caracteres (char, varchar, etc)
• Se for um índice filtrado, traz a expressão do
filtro
•
•
•
•
2
3
DEMO
2
4
Índice Simples
• Índice simples é aquele composto por uma única
coluna.
• Útil quase sempre em colunas de alta seletividade
2
5
Índice Composto
• Índice composto é aquele formado por várias
colunas
• Muito útil em consultas FREQUENTES de alta
seletividade alcançada apenas em múltiplas colunas
• Índice composto pelas colunas A, B não é igual ao
índice formado pelas colunas B,A
• Geralmente a boa prática nos diz para usar a coluna
mais seletiva primeiro
2
6
Segundo Mito
A ordem das colunas nos índices é
indiferente?
2
7
Índice Agrupado
28
Índice Agrupado
• Páginas de dados da tabela são armazenados
em uma sequência lógica
• As linhas são armazenados em uma sequência
lógica junto com as páginas de dados
• Possível apenas um índice agrupado por tabela
2
9
Índice Agrupado - Criação
• Automaticamente ao especificar uma chave
CREATE TABLE dbo.Article
primária
( ArticleID int IDENTITY(1,1) PRIMARY KEY,
• Manualmente, ArticleName nvarchar(50) NOT NULL,
PublicationDate date NOT NULL
caso não exista );
CREATE TABLE dbo.LogData
( LogID int IDENTITY(1,1),
LogData xml NOT NULL
);
ALTER TABLE dbo.LogData
ADD CONSTRAINT PK_LogData
PRIMARY KEY (LogId);
CREATE CLUSTERED INDEX CL_LogTime
ON dbo.LogTime(LogTimeID);
3
0
Terceiro Mito
Se eu usar a coluna CPF como índice
agrupado, terei lentidão na atualização de
dados?
3
1
Incorporando espaço livre nos índices
Pode resolver os problemas causados na alteração
de dados
• FILLFACTOR
• PAD_INDEX (Aplica FILLFACTOR nas páginas
intermediárias do índice
3
2
Índice agrupado eficiente
•
•
•
•
3
3
Pequeno
Estático
Crescente
Exclusivo
Índice agrupado – Tipos de Dados
Adequados
Tipo
Comentários
int
Bom candidato principalmente se IDENTITY
bigint
Bom candidato principalmente se IDENTITY
uniqueidentifier
Alguns possíveis problemas com tamanho
varchar
Problemas com tamanho, performance para
ordenar e com o fato de normalmente não ser
estático.
date
Problemas por não ser exclusive, mas excelente
para consultas de faixas
smalldatetime
Problemas com exclusividade
3
4
Índice Não Agrupado
35
Índice não agrupado
• A página folha aponta para um índice
agrupado normalmente
• Pode melhorar performance em colunas
adicionais e muito utilizadas em consultas
• O impacto causado pela modificação de
dados deve ser levado em conta
3
6
Índice não agrupado - Funcionamento
Página Raiz
Páginas do Ìndice
Nós Folha
Contém a chave
Chave agrupada
id
index_id=1
root_page
Páginas contém dados
Índice agrupado
3
7
Página Raiz
Cláusula Include
Colunas especificadas são adicionadas nos nós folha do índice não
agrupado
As colunas base, são ordenadas, as colunas incluídas não
Não entra no limite de tamanho dos índices (16 colunas / 900 bytes)
Evita a busca dos dados no índice agrupado
Inclua TODAS as colunas que vai usar na sua consulta, senão perde o
sentido
SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5
CREATE NONCLUSTERED INDEX NC_EmpDep
ON Employee(EmployeeID, DepartmentID)
INCLUDE (Lastname)
3
8
Índices Filtrados
39
Índices Filtrados
Melhor desempenho das consultas
Redução do custo de manutenção do índice
Redução do custo de armazenamento
Muito útil em colunas com alto índice de
nulabilidade
• Muito útil em colunas de alta densidade mas em
consultas a valores de alta seletividade
•
•
•
•
4
0
Volunteering Opportunities
PASS would not exist without its global network of
passionate, dedicated, and hardworking volunteers.
Volunteer today!
For local opportunities, please visit:
volunteer.sqlpass.org
For PASS HQ related activities, please update the
“MyVolunteering” section of your MyPASS profile.
4
1
Stay Involved!
•
Sign up for a free membership today at sqlpass.org.
•
•
•
•
Linked In:
Facebook:
Twitter:
PASS:
http://www.sqlpass.org/linkedin
http://www.sqlpass.org/facebook
@SQLPASS
http://www.sqlpass.org
Daniel Silveira
[email protected]
daniel.silveira.182
dansil2002
www.danielsilveira.com.br
dansil2002
43
OBRIGADO!!!!
44