En la versión SQL 2008 o superior se pueden crear índices que tengan filtros. Estos mimos que son de simple creación pueden tener un gran impacto en la mejora de performance.
En este post haremos un análisis del uso de estos índices y sus respectivos costos vs los índices comunes.
Esta más que claro que no se los puede aplicar en todos los casos, primero deberíamos determinar selectividad y espacio, un ejemplo de esto sería: Si tenemos una tabla de correos electrónicos y en la misma hay un estado donde indicamos si es leído o no leído, es muy probable que nuestros datos estén más o menos así: 90% Leídos 10% no leídos.
Si deseamos hacer una query que traiga los mails no leídos vamos a necesitar un índice por ese campo estado para que la consulta responda de forma eficiente. Esto se podría hacer con un índice común o bien uno con filtro, la diferencia va a ser importante ya que no va a ser lo mismo guardar todos los estados (Leídos y no Leídos) vs los no leídos solos.
Vayamos por partes, lo primero que vamos a hacer es crear una tabla.
CREATE
TABLE
MAILS
(ID
INT
IDENTITY
PRIMARY
KEY,
FECHA
DATETIME,
LEIDO
BIT,
MENSAJE
VARCHAR(MAX))
GO
Luego lo que haremos es insertar un par de millones de registros para que podamos luego ver los impactos más fáciles entre un tipo de índice y el otro, a mayor volumetría la diferencia será más notoria.
DECLARE
@N
INT
= 0
WHILE
@N
<= 1000000
BEGIN
INSERT
INTO
MAILS
(FECHA,
LEIDO,
MENSAJE)
VALUES (GETDATE(),
1,
'MENSAJE DE CORREO'
+
CONVERT(VARCHAR(36),NEWID()))
SET
@N
=
@N
+ 1
END
-- INSERTAMOS UN PAR DE MILLONES MAS
INSERT
INTO
MAILS
(FECHA,
LEIDO,
MENSAJE)
SELECT
FECHA,
1,
MENSAJE
FROM
MAILS
GO 4
Hacemos ahora un update de unos 5000 registros para poner su estado leído en 0 indicando con esto que esos registros están sin leer.
UPDATE
TOP (5000)
MAILS
SET
LEIDO
= 0
Si ejecutamos la siguiente query buscando los correos sin leer vamos a obtener un query plan que hace un Clustered index Scan ya que no tenemos índices por el campo leído.
SELECT
FECHA,
MENSAJE
FROM
MAILS
WHERE
LEIDO
= 0
Para poder mejorar nuestro proceso entonces le creamos un índice sobre la columna leído
SET
STATISTICS
TIME
ON
CREATE
INDEX
IX1
ON
MAILS
(LEIDO)
El proceso ha tenido una duración de 15 segundos aproximadamente. Ya con ese índice creado hemos podido mejorar la performance y nuestro query plan ha cambiado.
Con esto nos podríamos quedar contentos, pero no va a ser mi caso J, veamos qué pasa si creamos un índice con filtro, porque? Porque este índice que acabamos de crear anteriormente solo se podrá utilizar cuando le pidamos los no leídos, si cambio la query y solicito los leídos como son muchos datos va a ser más barato hacer un Scan que el Seek que vimos anteriormente.
SELECT
FECHA,
MENSAJE
FROM
MAILS
WHERE
LEIDO
= 1
Esto es totalmente lógico ya que es la selectividad de mi consulta. La pregunta es: si entonces solo este índice es útil cuando busco los no leídos por su selectividad cuando yo lo cree almaceno no solo estos sino que también los leídos ya que no hice ningún filtro, esto va a tener un impacto en el tamaño de dichos índices no.
Ahora lo que vamos a hacer es crear el índice con filtro de la siguiente manera
SET
STATISTICS
TIME
ON
CREATE
INDEX
IX2
ON
MAILS
(LEIDO)
WHERE
LEIDO
= 0
El tiempo de ejecución ha sido de 1 segundo contra los 14 del índice anterior, aquí ya tenemos una gran diferencia, obviamente que cuando hagamos tareas de mantenimiento sobre estos índices (defrag o Rebuild) va a ser mucho más eficiente en el índice con filtro que en el otro ya que pesa menos.
Ahora compararemos los query plan usando un índice o el otro, en mi caso dieron lo mismo.
SELECT
FECHA,
MENSAJE
FROM
MAILS
WITH (INDEX(IX1))
WHERE
LEIDO
= 0
SELECT
FECHA,
MENSAJE
FROM
MAILS
WITH (INDEX(IX2))
WHERE
LEIDO
= 0
Veamos ahora el espacio ocupado por cada uno de estos índices, para ello usaremos la siguiente query
SELECT
I.NAME
AS
INDEXNAME,
SUM(PAGE_COUNT * 8)
AS
INDEXSIZEKB
FROM
SYS.DM_DB_INDEX_PHYSICAL_STATS(
DB_ID(), OBJECT_ID('MAILS'),
NULL,
NULL, 'DETAILED')
AS
S
JOIN
SYS.INDEXES
AS
I
ON
S.[OBJECT_ID] = I.[OBJECT_ID] AND S.INDEX_ID = I.INDEX_ID
GROUP
BY
I.NAME
ORDER
BY
I.NAME
IndexName | IndexSizeKB |
IX1 | 76256 |
IX2 | 64 |
Ahora haremos una prueba más usando índices con filtro pero además que sean cover para así poder mejorar aún más nuestra query.
-- BORRAMOS LOS DOS INDICES
DROP
INDEX
IX1
ON
MAILS
DROP
INDEX
IX2
ON
MAILS
CREATE
INDEX
IX1
ON
MAILS
(LEIDO)
INCLUDE(FECHA,MENSAJE)
CREATE
INDEX
IX2
ON
MAILS
(LEIDO)
INCLUDE(FECHA,MENSAJE)
WHERE
LEIDO
= 0
El primer índice sin el filtro ha tardado unos 14 segundos en su creación vs 1 segundo contra el que tiene filtro.
Ahora observaremos además el espacio ocupado por cada uno de ellos
IndexName | IndexSizeKB |
IX1 | 151272 |
IX2 | 392 |
Volvemos ejecutar nuestra query original y comparar los query plan entre usar el índice con filtro o sin él.
SELECT
FECHA,
MENSAJE
FROM
MAILS
WITH (INDEX(IX1))
WHERE
LEIDO
= 0
SELECT
FECHA,
MENSAJE
FROM
MAILS
WITH (INDEX(IX2))
WHERE
LEIDO
= 0
Ahora se puede observar que las dos operaciones hacen el index seek ya que hemos creado un Cover Index, pero como los índices pesan mucho menos obtenemos una mejora importante en la performance de los índices con filtro.
Resumen:
Los índices con filtro son una herramienta muy potente para muchos casos ya que no tiene sentido almacenar en un índice toda la información si la misma no se usa.
Filter by APML
Suscribirse