Índices, Clustered, NonClustered que diabos é isso?

Fala aee pessoal, espero que estejam todos ótimos !!

No post passado, explicamos o que é um índice, caso não tenha lido o link está aqui http://ithalobruno.tech/?p=43 .

Entendemos que quando uma tabela não possui um índice, ela é denominada tabela HEAP.

Vamos analisar como fica a estrutura de uma tabela Heap??

Tabela Heap

Podemos perceber que neste cenário, uma pesquisa por qualquer valor, obrigatoriamente teremos que fazer um “scan” ou seja, percorrer por todos os registros.


Vamos praticar!

Criaremos uma tabela para emular a tabela HEAP.

use Estudos

CREATE TABLE Codigo ( 
	Cod int
)

Agora vamos “popular” nossa tabela:

DECLARE @CONTADOR int = 1

BEGIN
    WHILE @CONTADOR <= 10000
    BEGIN
        INSERT INTO Codigo(Cod)
		VALUES(
			@CONTADOR
		)
       SET @CONTADOR = @CONTADOR + 1;
     
    END;
END;

Agora vamos para o teste!

Inserimos 10 mil registros na nossa tabela Codigo, e agora vamos procurar pelo código 45.

SELECT * FROM CODIGO
WHERE COD = 45

Ao analisar essa query podemos perceber que ela fez 17 logical reads, lembrem-se que esta é uma tabela pequena, e que em um ambiente de produção o mundo é completamente diferente.


Agora a brincadeira fica séria!

Indices Clustered.

  • Bom agora que entendemos o que é, e como servem os índices vamos entender o que é cada um, segundo a documentação da Microsoft, “pode haver apenas um índice clusterizado por tabela, pois as linhas de dados podem ser classificadas somente em uma única ordem.
  • O único momento em que as linhas de dados de uma tabela são armazenadas na ordem de classificação é quando a tabela contém um índice clusterizado.”

https://docs.microsoft.com/pt-br/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15.

Vamos entender, antes da criação do índice clustered, nossa tabela era toda desorganizada, (pra não falar zoneada mesmo, em alguns casos isso é até legal).

Quando criamos um índice clustered, nos organizamos nossa tabela da seguinte maneira:

Indice Clustered

E aí meus amigos, é ai que a mágica acontece, nós criamos uma estrutura de árvore, ordenada pelo campo que definimos na criação do índice (no caso a coluna código), e o que acontece quando filtramos por um código qualquer? O SQL, sabe apontar exatamente onde está o registro.

CREATE CLUSTERED INDEX IX_Codigo
ON Codigo (Cod);   
GO 

Nossa consulta que anteriormente precisou fazer 17 logical reads, fez apenas 2 logical reads agora. Waw!! ^^.

Bom, vamos agora então conhecer as características principais de um índice clustered.

Um índice clustered é exatamente sua tabela, ordenada. (Importantissimo!)

  1. Uma tabela, só pode ter um índice clustered
  2. Um índice clustered pode ser composto, ou seja, ter mais de um campo.

________________________________________________________________________

Indice NonClustered.

Belezinha acho que entendemos o significado de índice clustered, para que ele serve, como criar. Tudo joia, mas eu tenho alguns probleminhas no meu ambiente.

– “Meu banco de dados já tem um índice clustered, e aprendemos que a tabela só pode ter 1 índice clustered ( e eu não sou louco de dropar esse índice)”

-“Meu fornecedor de software já criou o índice e dropar ele, para criar outro está fora de cogitação”. Etc etc.

Todos esses problemas são bem pertinentes e ainda bem que você percebeu isso ^^.

Percebem que nossa grande necessidade neste caso é justamente a criação de um novo índice? E é aí que entra nosso índice nonClustered, podemos criar N índices nonclustered na nossa tabela (olha, me imagine com uma cara de desespero implorando NÃO FAÇA ISSO!!!!)

Diferente do índice clustered, o índice non clustered, não cria uma cópia da sua tabela organizada, ele cria um objeto ordenado pelo(s) campo(s), que foi determinado na sua criação.


Vamos praticar²!

Vamos criar uma tabela no nosso banco Estudos para armazenar dados de Pessoa.

CREATE TABLE Pessoa(
CODIGO INT IDENTITY(1,1) NOT NULL,
NOME VARCHAR(50) NOT NULL,
DATA_NASCIMENTO DATE NOT NULL,
SEXO CHAR NOT NULL,
ALTURA INT 
)

Em seguida vamos criar o índice clustered, no código.

CREATE CLUSTERED INDEX IX_Codigo
ON Pessoa (Codigo);   
GO 

Agora vamos inserir alguns registros nesta tabela.

INSERT INTO [dbo].[Pessoa]
           ([NOME]
           ,[DATA_NASCIMENTO]
           ,[SEXO]
           ,[ALTURA])
     VALUES
           ('JOAO',
           '1980-01-01',
           'M',
           180)
GO



INSERT INTO [dbo].[Pessoa]
           ([NOME]
           ,[DATA_NASCIMENTO]
           ,[SEXO]
           ,[ALTURA])
     VALUES
           ('MARIA',
           '1986-02-15',
           'F',
           170)
GO


INSERT INTO [dbo].[Pessoa]
           ([NOME]
           ,[DATA_NASCIMENTO]
           ,[SEXO]
           ,[ALTURA])
     VALUES
           ('CARLOS',
           '1990-03-31',
           'M',
           165)
GO


INSERT INTO [dbo].[Pessoa]
           ([NOME]
           ,[DATA_NASCIMENTO]
           ,[SEXO]
           ,[ALTURA])
     VALUES
           ('ROBERTA',
           '1986-08-25',
           'F',
           181)
GO

Já aprendemos que quando buscamos pelo código, a pesquisa será executada rapidamente, pois é o nosso índice clustered, mas o que acontecerá se nossa pesquisa for pelo nome?

SELECT * FROM PESSOA
WHERE NOME = 'Joao' 

Bom, não temos nada na nossa tabela indicando uma “ajudinha” para o SQL, nesse caso ele terá que fazer o danadinho do Scan :’(  (Pesquisar em todos os registros), e pesquisar em todos os registros (no caso 4) é moleza neh!! Suavão.. Mas e quando sua tabela tem trocentos milhões de registros.. ai complica um poquinho.

Vamos ajudar o SQL, nesta situação? Bom, já entendemos que não podemos criar outro índice clustered, então vamos lá criar o índice NonClustered, na coluna nome.


CREATE NONCLUSTERED INDEX IX_Nome
ON Pessoa (Nome);   
GO

AEEEEEE, Ithalo, agora sim posso buscar por nome que minha pesquisa vai “vuá”. Bora testar?

SELECT * FROM PESSOA
WHERE NOME = 'JOAO'

Vamos dar uma olhadinha no plano de execução desta query (antes de executar a query, pressione CTRL+M, que irá exibir uma nova aba “Execution Plan” logo após a execução da query).

Ops :’( Continuou fazendo o scan !!!! E ele nem usou nosso índice, que acabamos de criar. Por quê???????

Lembram do nosso alerta do primeiro post, sobre o “*”? Pois é…

Vamos explicar, no próximo post, até porque este post aqui ficou bem extenso.

Espero que tenham gostado.

Um abraço!!

E até a próxima!!!

1 comentário

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *