Ticout Outsourcing Center

Explotemos la Tecnología

Ticout Outsourcing Center - Explotemos la Tecnología

Sql Server: Diferencias entre Clustered Index y Non-Clustered Index

Cuando empecé a usar Sql Server, estaba al principio un poco confuso con las diferencias entre Clustered Index y Non-Clustered Index. El objetivo de este post es clarificar un poco  las diferencias existentes entre ellos.

Non-Clustered Index

Para entender mejor como funcionan, crearemos una tabla e insertaremos registros, para ver como Sql Server los almacena.

CREATE TABLE empTest1 ( 
empId INT,
empNombre VARCHAR(1000))
GO

En la creación de la tabla anterior, no se crea ningún índice. En términos técnicos, una tabla sin un índice se llama heap. Para ver como la nueva tabla no tiene ningún índice se puede ver mediante la tabla de sistema sysindexes, esta tabla tiene una entrada para cada una de las tablas que forma la base de datos, si la columna indid vale 0, quiere decir, que la tabla no tiene ningún índice.

SELECT OBJECT_NAME(i.id) AS Table, i.name AS iName, i.indid AS iId
FROM sysindexes AS i
GO

Una vez creada la tabla, pasamos a insertar registros:

INSERT INTO empTest1 VALUES(4, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(6, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(1, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(3, REPLICATE ('a', 1000))
GO

Si recuperamos los registros de la tabla mediante una SELECT, Sql Server nos los devuelve en el mismo orden en que fueron insertados:

SELECT empID FROM empTest1
GO

Cada tabla tiene asociada unas páginas determinadas para almacenar los datos, para saber, que páginas tiene asociadas a una tabla, tenemos el siguiente script:

DBCC TRACEON(3604)
GO

DECLARE @DBID Int, @TableID Int
SELECT @DBID = DB_ID(), @TableID = OBJECT_ID('empTest1')

DBCC ind(@DBID, @TableID, -1)
GO

De la mucha información que devuelve este script, nos interesa sólo tres columnas:

PagePID IndexID PageTypeD
9654 0 10
9653 0 1

 

  • PagePID: Identificador de página.
  • IndexID: Tipo de índice.
    • 0: Página de datos
    • 1: Clustered Index
    • 2: >= 2 es página de índice (Non-clustered y Ordinary Index)
  • PageType: Tipo de datos almacenados.
    • 10: Index Allocation MAP
    • 1: Página de datos
    • 2: Página de Indice

Según la información devuelta, no tenemos ninguna página que guarde información de índices. Con el PagePID podemos ver que información tiene almacenada la página. Para esto ejecutamos el script:

DBCC TRACEON (3604)
GO

Declare @DBID Int
Select @DBID = db_id()

DBCC page(@DBID, 1, 9653, 3)
GO

Donde 9653 es el ID de una de mis páginas asociadas a la tabla, para ver la información de otra página, sólo tenemos que cambiar en DBCC page el ID de página.

La información que nos muestra la última página son los datos almacenados,  estos se encuentran guardados en el mismo orden en que fueron insertados. Recordemos que se trata de una página de tipo 1 (información devuelta por DBCC ind).

Creación de un Índice Non-Clustered

Para entender como funciona un índice Non-Clustered, lo mejor es que lo creemos, de esta forma se verá como afecta a los datos y a la forma en que se almacenan en Sql Server.

CREATE UNIQUE NONCLUSTERED INDEX empTest1_empId
ON empTest1(empId)
GO

Volvamos a listar las páginas asociadas, mediante DBCC ind, para ver si ha habido alguna modificación en las páginas asociadas a la tabla:

DBCC TRACEON (3604) 
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('empTest1')
DBCC ind(@DBID, @TableID, -1)
GO

La lista de páginas que obtenemos son:

PagePID IndexID PageType
9654 0 10
9653 0 1
235512 2 10
9655 2 2

Aparecen dos registros nuevos. Las dos primeras páginas son las mismas que cuando no teníamos índices asociados, las dos últimas contienen toda la información del índice. Mediante el IndexID = 2, sabemos que se trata de un índice Non-Clustered. Podemos ver el contenido de cada página, como hemos anteriormente, con el comando DBCC page y el PagePID.

DBCC TRACEON (3604) 
GO

Declare @DBID Int
Select @DBID = db_id()

DBCC page(@DBID, 1, 9655, 3)
GO

En la página 9653, no hemos sufrido ninguna modificación, es decir el orden de los datos viene dado por su inserción. En la página 9655, encontramos la información ordenada por el índice (empId).

En un índice Non-Clustered, la clave por la que buscamos tiene un puntero a la página de datos donde se encuentra el registro. Este tipo de índices son particularmente útiles cuando queremos devolver un único registro de la tabla, por ejemplo para buscar un empleado con un ID determinado (empId = 3), donde el filtro se aplica sobre la columna que tiene el índice Non-Clustered.

Insertaremos unos registros más, para poder estudiar el comportamiento de los datos y el índice.

INSERT INTO empTest1 VALUES(8, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(7, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(2, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(10, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(5, REPLICATE ('a', 1000))
GO
INSERT INTO empTest1 VALUES(9, REPLICATE ('a', 1000))
GO

Al recuperar las páginas asociadas a la tabla, aparece una página nueva de tipo datos. Esta página nueva de datos, contiene los registros nuevos que no cabían en la página anterior. En la página de índice se han actualizado los punteros con los nuevos registros, pero como todavía hay espacio no se ha creado una nueva página. Recalcar que en la página de datos del índice, sólo aparecen los datos que han sido indexados, no todo el registro. Además, si recuperamos los datos de la tabla mediante una SELECT, estos continúan saliendo sin ordenar.

Añadiremos una nueva columna, sobre la que crearemos un CLUSTERED Index. En una misma tabla tenemos un índice Clustered y Non-Clustered.

ALTER TABLE empTest1 ADD EmpIndex Int IDENTITY(1,1)
GO
CREATE UNIQUE CLUSTERED INDEX clust_emp ON empTest1 (EmpIndex)
GO

Al crear un CLUSTERED Index, la estructura de páginas asociada a la tabla ha sido modificada, para verlo ejecutamos:

DBCC TRACEON (3604) 
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('empTest1')
DBCC ind(@DBID, @TableID, -1)
GO

El resultado es:

PagePID IndexID PageType
235516 1 10
235515 1 1
235517 1 1
235518 1 2
235520 2 10
235519 2 2

Las páginas que no tenían ningún índice asociado (IndexId = 0), ahora tienen un índice Clustered asociado (IndexId = 1, de hecho se trata de páginas nuevas). En las páginas de datos, encontramos la información ordenada por el campo donde se ha aplicado el Clustered Index, además cualquier inserción será ordenada según el índice.

Si miramos que contiene cada una de sus páginas o hacemos un SELECT sobre la tabla veremos como contiene todos los valores ordenados. En un CLUSTERED Index sus leaf son las páginas de datos.

 

Clustered Index

Para ver mejor los Clustered Index, crearemos otra tabla e insertaremos registros en en ella.

CREATE TABLE empTest2 (
    EmpId INT,
    EmpName VARCHAR(1000)
)
GO
INSERT INTO empTest2 VALUES (4, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (6, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (1, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (3, REPLICATE('a', 1000))
GO

Al no tener ningún indice, SQL Server almacenará los registros en el mismo orden en que han sido insertado. Si mostramos las páginas asociadas a esta tabla:

DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('empTest2')
DBCC ind(@DBID, @TableID, -1)
GO

Las páginas asociadas a esta tabla son (sólo mostramos las columnas que nos interesa)

PagePID IndexID PageType
9654 0 10
9653 0 1

En la información mostrada vemos que no tenemos ningún índice asociado a la tabla que acabamos de crear. Si miramos la información almacenada en la última página, veremos que los datos están almacenados en el orden en que fueron insertados.

DBCC TRACEON (3604)
GO
Declare @DBID Int
Select @DBID = db_id()
DBCC page(@DBID, 1, 9653, 3)
GO

Vamos a crear un Unique Clustered Index sobre empID, de esta forma podremos ver como afecta a los datos almacenados en la tabla.

CREATE UNIQUE CLUSTERED INDEX empTest2_EmpIndex
ON empTest2 (EmpId)
GO

Si ahora, hacemos una SELECT sobre esta tabla, veremos que todos los registros nos aparecen ordenados mediante el índice que hemos creado. Recuperemos una vez más, las páginas asociadas a la tabla, para ver si ha habido  cambios en las páginas.

DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('empTest2')
DBCC ind(@DBID, @TableID, -1)
GO

La dos páginas que tenemos, tienen asociado un índice Clustered,. En la página de datos encontraremos la información ordenada por ese índice.

DBCC TRACEON (3604)
GO
DECLARE @DBID Int
SELECT @DBID = db_id()
DBCC page(@DBID, 1, 9655, 3)
GO

Insertaremos más registros, para estudiar como se ven afectados estos por el Clustered Index.

INSERT INTO empTest2 VALUES (10, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (2, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (5, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (8, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (9, REPLICATE('a', 1000))
GO
INSERT INTO empTest2 VALUES (7, REPLICATE('a', 1000))
GO

Si volvemos a listar las páginas de la tabla, tendremos más páginas debido a que una de las páginas de datos se ha rellenado. En este caso, nos interesan más columnas que en los casos anteriores:

PagePID IndexID PageTypeD NextPagePID PrevPagePID
235512 1 10 0 0
9655 1 1 235521 0
235514 1 2 0 0
235521 1 1 0 9655

Las columnas NextPagePID y PrevPagePID, apuntan a la siguiente y anterior página respectivamente, de esta forma el sistema sabe como recorrer los datos. El contenido de los datos en estas páginas se encuentra ordenado como indica el Clustered Index.

Los Clustered Index son muy utiles cuando queremos recuperar conjuntos de registros, que estan comprendidos entre rangos de datos, por ejemplo para buscar un conjunto de empleados que su ID está entre 3 y 9.

Al tener los datos en los leaf del árbol, se ahorra mucha E/S. Sólo es posible tener un Clustered Index por tabla.