Rendimiento

Dic

2023

Introducción a los índices en SQL Server (Índices parte 1)

Vamos a empezar con una serie de varios artículos sobre los índices en SQL Server, un aspecto fundamental en nuestra base de datos que marcará todo su funcionamiento. A lo largo de los próximos días vamos a profundizar en este tema partiendo desde lo más básico. Empezaremos viendo las estructuras fundamentales de las tablas, lo que nos llevará a adentrarnos en los principales tipos de índices. Esos conocimientos nos ayudarán a entender el resto de índices y cómo afectan al rendimiento de nuestra base de datos. La mejor parte es que al final de la serie habremos aprendido cómo mejorar y optimizar nuestros índices y por tanto, las consultas que leen datos de esas tablas.

Estructura de una tabla en SQL Server

Como a estas alturas ya sabréis, en SQL Server los datos se almacenan en tablas con columnas y filas. Lo que ya no todo el mundo sabe es que estas tablas a su vez se almacenan en páginas de 8 kb. Cada una de estas páginas es una unidad indivisible, son los átomos de nuestra base de datos. Todas nuestras tablas tendrán al menos una página y será exclusiva. Esto es importante, cada página sólo contendrá datos de un solo objeto (tabla o índice). A medida que nuestras tablas crezcan irán ocupando más y más páginas pero siempre con estos requisitos que hemos comentado. Cuando se borren datos ese espacio se quedará libre pero no se liberará, estará reservado para almacenar futuros datos de esa tabla.

La tabla más básica que podemos crear se llama HEAP (montón traducido literalmente). Será una tabla donde los datos que escribimos se van añadiendo en el primer espacio libre que encuentre en las páginas sin ningún orden. Empezará llenando secuencialmente el espacio de las páginas pero cuando quede espacio libre por algún borrado se añadirá ahí.

Índices Clustered en SQL Server

Lo contrario a una tabla HEAP es una tabla con un índice clustered (agrupado). Los datos se escribirán en páginas, ordenados siempre por la columna (o las columnas) del índice clustered. Como si de un diccionario se tratase, SQL Server sabe cual es el primer y el último dato de cada página, lo que facilita en gran medida las búsquedas por estas columnas. Como se trata de la propia ordenación de los datos sólo puede haber un índice clustered por cada tabla.

Índices Nonclustered en SQL Server

Los índices nonclustered (no agrupados) son un objeto separado de la tabla, sólo incluyen algunas de las columnas y se almacenan en páginas separadas. Un índice nonclustered será por tanto una copia de la tabla original con solo parte de las columnas y ordenado de manera diferente. Tenga una tabla un índice clustered o sea un HEAP podremos crear tantos índices nonclustered como queramos.

¿Cómo lee los datos SQL Server?

Con lo que ya sabemos nos podemos hacer una idea de como SQL Server va a acceder a la información que tenemos almacenada y empezar a comprender la importancia de los índices. Vamos a empezar con una tabla HEAP como la que bajo estas líneas (podéis ampliar la imagen haciendo click en ella):

Como veis tenemos una tabla de personas con un ID, nombre, apellido y una fecha de modificación. Supongamos que tenemos que buscar la fecha de modificación del usuario número 2, ¿cómo lo haríais vosotros?. Pensadlo un momento. Si habéis pensado en empezar a leer la columna id hasta llegar al 2 y después recorrer horizontalmente la tabla hasta la columna fecha de modificación, felicidades habéis pensado como SQL Server. Ahora bien, esta no parece la forma más efectiva de consultar información, ¿verdad?. ¿Y si la tabla en vez de 20 tuviera 20.000 registros? ¿Y 20.000.000? Lo mejor será crear un índice clustered por el campo ID para así tenerlo ordenado, ¿no creéis?

Como hemos comentado el índice clustered es la propia tabla ordenada por lo que tendremos todos los datos igual que en nuestra tabla HEAP pero ordenados por la columna ID en este caso.

Ahora es mucho más fácil buscar los datos de una persona si nos dan su ID, ¿a que si?. Pero, ¿qué pasaría si la pregunta ahora fuese por la fecha de modificación de los usuarios que se llaman Gigi? Tendríamos el mismo problema que antes, deberíamos recorrer la columna nombre hasta encontrar el que buscamos. Es incluso peor cuando estamos hablando de datos que no son únicos, aunque hayamos encontrado un Gigi puede ser que haya más, no lo sabremos hasta que no terminemos con toda la tabla. Es entonces el momento de crear un índice nonclustered por el nombre y el id para ayudarnos en esta tarea. Veámoslo:

Ahora si, podemos consultar nuestro índice nonclustered hasta ver que Gigi es el ID 9 y que el siguiente registro ya tiene otro valor por lo que solo hay una persona que se llama así. Usaremos nuestro índice nonclustered para encontrar el ID y luego en nuestro índice clustered iremos directamente al registro que necesitamos.

Si en este punto pensáis que os lleva más trabajo mirar primero el índice nonclustered y después el clustered que directamente leer todo el índice clustered que solo tiene 20 registros no os preocupeis, SQL piensa igual. Los índices no se usan en tablas pequeñas, aunque existan. En esos casos siempre va a tardar menos en leer la tabla entera que en usar los índices. Pero esto lo veremos más adelante, queda mucha serie de índices, veremos cosas nuevas y profundizaremos en lo que hemos visto. Estad atentos.

Contenido de la serie

Nov

2023

VISTAS INDEXADAS DE SQL SERVER

Las vistas son tablas virtuales que nos devuelven datos de una consulta sobre una o varias tablas. Igual que una tabla tiene sus columnas y sus filas pero al contrario que estas ese conjunto de datos como tal no está almacenado en ningún sitio. Cada tabla tiene sus datos y la vista los lee de ahí. Entonces, ¿los resultados de datos de las vistas no se guardan en ningún sitio? En realidad hay algunos tipos de vistas que sí guardan los datos finales como es el caso de las vistas materializadas que implementan algunos gestores de bases de datos (Oracle, por ejemplo). En el caso de SQL Server este tipo de vista no está disponible, aunque no podemos descartar que se implemente en un futuro (en Azure Synapse Analytics han existido desde siempre).

¿Qué alternativa tenemos en SQL Server?

Como hemos visto antes, las vistas las vemos como si fueran una tabla pero en realidad no tienen datos, los recuperan dinámicamente de las tablas a las que se referencia. Esto es muy útil en la mayoría de los casos ya sea por simplicidad de las consultas, para abstraernos de la estructura original y simplificar la estructura saliente o para gestionar permisos. Sin embargo el uso de una vista no afecta en nada al rendimiento pues ejecuta la consulta tal cual como si la escribiéramos a mano. Si una vista lee sobre varias tablas grandes generará mucho consumo de E/S y si la definición de la vista incluye procesamientos complejos y muchas uniones entre tablas tendremos una notoria degradación de rendimiento.

Como alternativa, en SQL Server podemos hacer uso de un tipo de vista especial que son las Vistas Indexadas (o indizadas), son vistas que tienen un índice clustered por lo que si van a almacenar el conjunto de resultados en la base de datos para poder hacer una lectura plana.

Dónde usar las vistas indexadas

Las vistas indexadas ofrecen un rendimiento de lecturas mejorado al hacer uso del índice para leer los datos y no tener que ejecutar la consulta subyacente. Sin embargo, nos pueden llegar a penalizar considerablemente las operaciones de escritura al añadir no sólo otro índice donde escribir los datos sino complejidad a ese índice. Adicionalmente podremos crear tantos índices nonclustered sobre la vista como necesitemos. Al igual que ocurre con los índices de las tablas en nuestra mano queda medir el coste/beneficio y valorar su idoneidad.

Con las vistas indexadas notaremos una mejora sustancial en el rendimiento de nuestras consultas si las aplicamos sobre entornos con una gran diferencia de lecturas frente a escrituras, se me viene a la cabeza sobre todo entornos Data Warehouse, bases de datos OLAP o entornos de minería de datos. Rara vez serán recomendables en entornos con alta carga de transacciones IUD (insert, update y delete) en bases de datos OLTP.

Limitaciones de las vistas indexadas

Como ya hemos visto uno de los inconvenientes de las vistas indexadas es que la degradación en rendimiento de las escrituras es mayor que la mejora en las lecturas, esto nos limita en gran medida su uso. Sin embargo no es su única limitación, la verdad es que tienen una amplia lista de incompatibilidades y requisitos. Uno de los principales requisitos de las vistas indexadas es que hay que crearlas con la opción SCHEMABINDING esto implica que no se podrán modificar las tablas referenciadas.

Consideraciones para crear vistas indexadas

Además de tener que crear la vista como SCHEMABINDING tenemos que tener en cuenta otros aspectos importantes:

Las vistas indexadas no admiten expresiones no deterministas. Es decir, las expresiones de la vista siempre deben devolver el mismo resultado no como GETDATE() que nos devolvería un valor distinto en cada ejecución.
Las tablas y funciones dentro de la vista deben declararse con el nombre completo (esquema.tabla).
No se admiten subconsultas.
No se admiten OUTER JOINS, esto deja fuera RIGHT JOIN y LEFT JOIN.
El índice clustered de nuestra vista ocupará espacio en disco.
Solo se puede hacer referencia a tablas de la misma base de datos.
Si tenemos GROUP BY, la definición de la vista debe contener COUNT_BIG(*), pero no HAVING.
No se puede usar EXISTS, NOT EXISTS, COUNT(*), MIN, MAX, hints de tablas, TOP ni UNION.
No puede utilizar los tipos de datos text, ntext , image o XML. El tipo de datos float se puede utilizar en la vista, pero no en el índice agrupado

Como crear vistas indexadas

Para crear una vista indexada lo primero que haremos será crear la vista con la opción SCHEMABINDING

CREATE VIEW VI_Facturas_Ventas
WITH SCHEMABINDING  AS
SELECT FC.cliente, FD.Articulo, COUNT_BIG(*)
FROM Ventas.FacturasCabeceras FC
INNER JOIN Ventas.FacturasDetalle FD
ON FC.NumFactura = FD.NumFactura
GROUP BY FC.cliente, FD.Articulo

Una vez que tenemos creada la vista tendremos que crear el índice clustered.

CREATE UNIQUE CLUSTERED INDEX CI_Facturas_Ventas ON VI_Facturas_Ventas (Cliente, Articulo);

En este punto el optimizador de consultas podrá usar nuestro índice para cualquier consulta sobre la tabla, incluso para consultas sobre las tablas sin que se use la vista aunque, esto último, sólo en ediciones Enterprise de SQL Server.

Conclusiones

Un gran poder conlleva una gran responsabilidad y las vistas indexadas son una increíble herramienta en entornos o tablas con gran cantidad de lectura y pocas modificaciones pero si no es así nos pueden hacer mucho daño al servidor. El uso de vistas indexadas de SQL Server puede ser una buena técnica para mejorar el rendimiento de las consultas al reducir el costo de E/S y la duración de las consultas, pero requiere pruebas, planificación y un estudio pormenorizado de donde usar vistas indexadas. Se debe realizar un análisis completo del impacto en el rendimiento, midiendo las mejoras en el rendimiento de lecturas frente al coste en las escrituras.