Índices no SQL Server – parte 01

• Tweet
• Sharebar

• Tweet

Fala, gente… beleza? Agora estou escrevendo sobre o SQL Server para o MXStudio. É o banco de dados da Microsoft e (na minha opinião) o melhor que existe hoje no mercado.

Com que dados eu fundamento isso? Experiência, fiote… experiência… Trabalhei alguns anos com Oracle, já desenvolvi em/para Access e MySQL, além do DB2 e Sybase. Mas eu gostei mesmo é do SQL Server. 

Vamos ao assunto do post:

Um recurso que melhora muito a velocidade de acesso aos dados armazenados é o tal de índice. Já tinha publicado esse post no meu site, mas vale a pena rever pois é muito atual.

Primeiro: qual a função de um índice? Melhorar o desempenho em consultas.

Segundo: quais os tipos de índice que temos no SQL Server? Dois: CLUSTERED e NON-CLUSTERED.

Terceiro: o que é um índice? É uma estrutura de árvore (balanceada – um tipo de árvore binária).

Quarto: é mais rápido que pesquisar na tabela, mesmo? Agora sim apareceu A pergunta… vamos começar por ela.

Inicialmente, precisamos definir árvore binária: uma estrutura formada por nós (vértices) e por folhas. Os nós são para determinar a localização de uma informação qualquer (na figura - uma representação simplificada - abaixo, 1-4, 1-2 e 3-4 são nós). As folhas (1, 2, 3 e 4, na figura abaixo) são as informações armazenadas.

Imagine, agora, que minha tabela tenha somente 2 registros:  olhando para um deles saberei se é aquele que quero, ou não.

Se tiver 4 registros, devo procurar um máximo de 2 vezes. Veja a figura acima:

• 1a. pesquisa: o número  está entre 1 e 2 (na verdade, é a leitura do nó que contém 1-2)?
• 2a. pesquisa: se o número está entre 1 e 2: o número é 1 (leitura da folha que tem o conteúdo 1)? Se for, ok. Se não for, é o 2. Ou:
• 2a. pesquisa: se o número não está entre 1 e 2: o número é 3 (leitura da folha que tem o conteúdo 3)? Se for, ok. Se não for, é o 4.

Extrapolando, podemos montar uma tabela como abaixo, já que sabemos ser o número de pesquisas necessárias igual ao expoente a que 2 deve ser elevado para “dar” a quantidade de registros:

Mas, onde esses índices são gravados? Ops… Onde qualquer dado (tabela ou índice) é gravado? A resposta é: numa estrutura chamada PAGE. Uma page (página de dados) tem 8kB (8.192 bytes). Desse valor, tiramos 96 bytes, que são usados para endereçamento interno. O que sobra (8.096 bytes) está disponível para armazenamento de dados.

Uma page pode conter dados de tabelas ou de índices. Outra coisa: uma page somente pode conter dados de uma ÚNICA tabela ou de um ÚNICO índice.

Agora, sim. Podemos dizer que:

• ÍNDICE NON-CLUSTERED – tem os nós e folhas gravados em páginas de dados independentes da tabela;
• ÍNDICE CLUSTERED – os nós são uma estrutura separada da tabela, mas as folhas são os próprios registros. As duas estruturas (índice e tabela) são mescladas.

Para os exemplos seguintes, usaremos a tabela criada com o bloco de comandos abaixo:

CREATE DATABASE EXEMPLO_INDICE
GO
USE EXEMPLO_INDICE

CREATE TABLE PESSOA
(
      COD INT NOT NULL,
      NOME VARCHAR(50),
      SEXO CHAR(1)
)

Inserimos alguns registros:

INSERT INTO PESSOA VALUES(1, ‘BRUNO’, ‘M’)
INSERT INTO PESSOA VALUES(2, ‘NETINHO’, ‘M’)
INSERT INTO PESSOA VALUES(3, ‘PEDRO’, ‘M’)
INSERT INTO PESSOA VALUES(4, ‘AGNALDO’, ‘M’)
INSERT INTO PESSOA VALUES(5, ‘MARIA’, ‘F’)
INSERT INTO PESSOA VALUES(6, ‘ANA MARIA’, ‘F’)
INSERT INTO PESSOA VALUES(7, ‘MARIANA’, ‘F’)
INSERT INTO PESSOA VALUES(8, ‘MARINA’, ‘F’)

Agora, vamos fazer alguns selects e ver qual é o plano de execução gerado. Eita nóis… O que é um plano de execução? Plano de execução é um “mapa” gerado pelo mecanismo relacional que informa ao mecanismo de armazenamento onde ler ou gravar no disco, se vai acessar a tabela diretamente ou se vai usar um índice, se a pesquisa é feita por seek ou por scan.

De novo: eita ferro!!! O que são esses mecanismos? Quando instalamos o SQL Server e iniciamos o serviço MSSQLSERVER, temos na memória três elementos:

• Mecanismo relacional - responsável por gerar um plano de execução baseado nas estatísticas associadas aos dados das tabelas/índices;
• Mecanismo de armazenamento - responsável por acessar uma tabela ou índice, acompanhando o definido no plano de execução;
• ODS – open data services – fica “vigiando” uma porta do micro, esperando conexões de clientes. A porta padrão para o SQL Server é a 1433. Esse processo se chama listening.

Como vejo um plano de execução? Basta habilitar sua apresentação através das teclas de atalho [CTRL] + [M] ou acessar a opção [Include Actual Execution Plan] do menu [Query]. Para testar, podemos selecionar alguns dados da tabela, usando:

SELECT * FROM PESSOA

O plano de execução apresentado é:

Onde Table Scan é o operador que significa que foi feito um acesso sequencial a todos os elementos da tabela Pessoa.

Para qualquer um dos comandos o plano de execução gerado é o mesmo:

SELECT * FROM PESSOA WHERE COD = 1
SELECT NOME FROM PESSOA
SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’
SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’
SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’

Agora, vamos criar um índice do tipo non-clustered para o campo NOME da tabela, usando o comando:

CREATE NONCLUSTERED INDEX INDEX_PESSOA_NOME ON PESSOA(NOME)

Executando os comandos abaixo, temos planos de execução (uso de operadores) diversos:

SELECT * FROM PESSOA
–-TABLE SCAN
SELECT * FROM PESSOA WHERE COD = 1
–-TABLE SCAN
SELECT NOME FROM PESSOA
–-INDEX SCAN
SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’
–-INDEX SEEK
SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’
–-TABLE SCAN
SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’
–-TABLE SCAN

Criamos agora, um índice clustered, para o campo COD da tabela:

CREATE CLUSTERED INDEX INDEX_PESSOA_COD ON PESSOA(COD)

E executamos os mesmos comandos:

SELECT * FROM PESSOA
–-CLUSTERED INDEX SCAN
SELECT * FROM PESSOA WHERE COD = 1
–-CLUSTERED INDEX SEEK
SELECT NOME FROM PESSOA
–-INDEX SCAN
SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’
–-INDEX SEEK
SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’
–-INDEX SEEK
SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’
–-CLUSTERED INDEX SCAN

… continua …

Escrito por Agnaldo Diogo dos Santos on maio 28, 2008. Arquivado em SQL. Você pode seguir as respostas a esse artigo pelo RSS 2.0. Você pode deixar respostas para esse artigo