Índices redundantes y el impacto en la performance


En muchos lugares se habla de los índices redundantes o similares, estos básicamente son índices que están cubiertos por otros dentro de la misma tabla.

Supongamos que tenemos una tabla con 5 campos (C1,C2,C3,C4,C5) y luego creamos índices con este estilo:

Índice 1 = C1

Índice 2 = C1,C2

Índice 3 = C1,C2,C3,C4

El índice 3 cubre a los otros 2 (recordar que se leen y son útiles de izquierda a derecha) ya que índice 3 va a servir para las consultas de C1=Valor y C1 and C2 = Valor.

Que sucede si tenemos estos 3 índices en una tabla? bueno estaríamos castigando las operaciones de modificación (INSERT , UPDATE, DELETE)  ya que tiene que actualizar 3 índices cuando si borro Indice1 e Indice2 seria menor el costo en estas operaciones.

En principio entonces estaríamos diciendo que deberíamos eliminar el índice 1 y 2 para solo quedarnos con el 3, pero en este post veremos que no es tan así la cosa y antes de hacer esto hay que analizar otras cuestiones Sonrisa

Vamos a ir por partes entonces, en primer lugar crearemos una tabla que luego la llenaremos con una cantidad de registros.

 

Ahora que tenemos la tabla creada y sus datos en ella haremos algunas pruebas.

Update sin índices:

Haremos un UPDATE sobre la tabla sin índices creados para medir el consumo de Reads, para ello usaremos el siguiente código:

Como resultado obtenemos las siguientes estadísticas

Table ‘TR’. Scan count 1, logical reads 246045, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Update con el primer índice:

Lo que haremos ahora es crear un primer índice para el campo C2 y volver a correr el UPDATE anterior para poder así obtener las estadísticas.

 

Como resultado obtenemos las siguientes estadísticas

Table ‘TR’. Scan count 1, logical reads 1002733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como se puede observar hemos pasado de 246045 lecturas a 1002733 debido a que ahora la operación es mas costosa por el índice.

Update con dos índices:

Lo que haremos ahora es crear un segundo índice por varios campos pero el primero será para C2 y volveremos a correr el UPDATE

 

 

Como resultado obtenemos las siguientes estadísticas

Table ‘TR’. Scan count 1, logical reads 1135426, physical reads 0, read-ahead reads 411, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como se puede observar también ahora es mas caro hacer el UPDATE  porque tenemos mas índices.

Índices redundantes:

Observando hasta acá podríamos decir que el primer índice I1 por la columna C2 es redundante ya que el I2 lo contiene en su primera columna, por lo cual podría ser una buena idea eliminar el índice 1 I1 para que las operaciones de modificación sean mas eficientes.

Vamos a hacer algunas pruebas primero con consultas y observar que pasa.

Si vemos el query plan del siguiente código observaremos que usa el índice 1 (I1)

 

Redundante1

Con un costo de QP de 146

Rediundante2

Como el primer índice es redundante con I2 vamos a eliminarlo y volver a correr la misma consulta.

 

Vamos a observar el Query plan ahora

Redundante3

Redundante4

 

Como podemos observar se sigue haciendo un Index Seek (ahora por I2) pero el costo es mayor, pasamos de 146 a 151, y esto se debe a que I2 al ser mas pesado que I1 cuesta mas, recordar que el Costo de un Query Plan es = Costo CPU + Costo I/O de una query, como aquí el I/o es superior la consulta pesa mas y tarda mas.

 

Conclusiones:

No es para nada bueno tener índices redundantes, muchas veces (por no decir la mayoría) son por errores en el diseño, pero hay que tener cuidado con consultas especificas como las que vimos,en nuestro ejemplo si borramos el índice redundante las operaciones de UPDATE serán mas rápidas pero en la del Select será mas lenta, dependiendo de cuan critica sea una y otra es como deberíamos actuar.