Este artículo explica a detalle el funcionamiento de los índices clustered y nonclustered, las ventajas de uno sobre el otro y cuando deben de utilizarse.
Indexando
Existen muchas maneras de mejorar el performance de SQL. Algunas de ellas puedes ocasionar mejores resultados que otros, aunque la verdadera forma de mejorar el performance y con buenos resultados es que el diseñador de la base de datos, concentre sus esfuerzos en la generación de índices. La manera correcta de seleccionar un índice respecto a la cláusula WHERE, de tal manera que el optimizador de consultas selecciones la estrategia más eficiente, puede darnos excelentes resultados.
Entonces, que son los índices y porque son tan importantes.
Extrayendo datos sin el uso de índices.
Imaginemos que tenemos un libro que no contiene índices, y queremos encontrar cierta información sobre un tema. No vamos a tener otra opción que abrir el libro desde la página uno y revisar página por página, hasta que hayamos revisado todas las páginas del libro. Tendríamos que revisar todas las páginas para asegurarnos de revisar todo lo relacionado al tema en todo el libro.
SQL Server trabaja de una manera similar, cuando le preguntamos que extraiga los registros de una tabla que no contiene índices. Supongamos que queremos ejecutar la siguiente sentencia SQL sobre la tabla accounts asumiendo que no tenemos ningún índice sobre la tabla.
SELECT * FROM accounts WHERE branch_no = 1100
¿Como SQL Server encontraría los registros apropiados? Tendría que buscar sobre la tabla accounts desde el inicio de la tabla, hasta el final buscando registros que contengan el valor 1100 en el campo branch_no. Esto estaría bien para tablas pequeñas que solo tienen algunos registros, pero si la tabla tuviera millones de registros, el query tardaría mucho tiempo en completarse.
Que sería necesario en el caso de que quisiéremos una forma eficiente y rápida de encontrar los registros. En el caso de un libro, existe usualmente una sección llamada índice, donde el tema buscado puede ser encontrado en orden alfabético, en donde se muestra el número de página donde puede ser encontrado. De esta manera la página requerida puede ser directamente accesada en el libro.
La manera de encontrar la información requerida en SQL Server no es muy distinta a la utilizada en un libro. Estructuras llamadas índice pueden ser creadas en las tablas, que hacen que SQL Server puede buscar rápidamente sobre las páginas que contenga el valor de la llave, en nuestro ejemplo el valor 1100 para la columna branch_no.
A diferencia de un libro que solo contiene un índice, una tabla puede contener muchos índices. Estos índices son basados en una o más columnas de la tabla. En SQL Server existen dos tipos de índices clustered y nonclustered. La última decisión sobre si es usado un índice o no sobre una tabla, es llevada acabo por un componente de SQL Server conocido como optimizador de consultas.
Índices Clustered
Solo es posible crear un índice clustered en una tabla, por lo que debes de pensarlo cuidadosamente. ¿Y porque solo un índice clustered por tabla? Porque a diferencia de los nonclustered un índice clustered requiere de un ordenamiento físico de los datos.
Crear un índice clustered hace que los registros de la tabla se reordenen en el disco, por lo tanto quedan ordenados de la misma forma que la llave del índice clustered. Por ejemplo, si fuéramos a crear un índice clustered en la columna nombre de la tabla empleados, los registros estarían ordenados de tal manera que el orden físico en el disco estaría de manera ascendente del campo nombre, es decir Carlos estaría antes de Francisco.
Este orden se mantendrá mientras exista el índice clustered. En caso de que se insertara un registro nuevos, SQL Server se encargaría se colocarlo físicamente en el orden que le corresponda de acuerda a la llave.
La estructura de un índice clustered, en donde su llave es la columna nombre de la tabla empleados se muestra en la siguiente figura.
{mosimage}
{mospagebreak}
El nivel mas bajo de un índice clustered esta compuesto por páginas de datos y en un índice clustered las páginas de datos se conocen como leaf level. El resto del índice clustered esta compuesto de páginas de índice. La página de índice que se encuentra hasta la parte más alta de un índice se conoce como root. Los niveles entre la página root y el leaf-level son conocidos como nivel de páginas intermedias (intermediate-level pages). Otro nombre que se les da a una página de índice es index node. Por simplicidad mostramos la estructura de un índice clustered con la capacidad de almacenar dos registros por página y tres entradas por página de índice, aunque en la realidad se pueden encontrar más registros y entradas de índices por página.
En cualquier nivel de un índice clustered todas las páginas se encuentran ligadas y esto lo diferencia de un índice clustered con un nonclustered.
Las entradas en las páginas de índices contienen llaves que apuntan a otra página de índice contenida en el siguiente nivel inferior, que comienza con la llave, más alguna otra información de control. El apuntador en un índice clustered es un número de página. En la figura 1, por ejemplo, la página root tiene una entrada conteniendo una llave con valor ADAMS y un número de página 58 apuntando a un nivel intermedio del índice, donde su valor inferior es ADAMS.
El apuntador también contiene un identificador de fila (File ID) como prefijo. Este es necesario porque los números de página son únicos en un archivo de datos. Un files ID más un número de página también es conocida como Page ID.
La razón por la que solo puede haber un índice clustered en una tabla, es que el índice clustered gobierna el orden físico de los datos y los datos no pueden estar en dos lugares al mismo tiempo. Solo puede existir una sola secuencia en la que pueden estar ordenados los datos físicamente.
¿Entonces como es que un índice clustered puede ayudarnos a mejorar la eficiencia de nuestros queries? El índice clustered nos va a yudar a evitar que se realicen table scans, suponiendo que el optimizador de consultas probablemente utilice el índice clustered para extraer los datos directamente. Supongamos que utilicamos la siguiente consulta:
SELECT * FROM empleados WHERE nombre = ‘GREEN’
Supongamos que el optimizador de consultas decide que el índice clustered es la forma más eficiente de acceder a los datos. Estamos asumiendo algo muy real, ya que en la cláusula WHERE solo se especifica la columna nombre donde esta basado el índice.
SQL Server primero obtiene el número de página root de la tabla sysindexes, en nuestro ejemplo la 24. Esta página root contiene varias llaves, en este caso ADAM y JAMES. SQL Server buscara por el valor de llave más alto pero no mayor a GREEN, que sería ADAMS.
En un índice clustered, una entrada en el índice consiste en la llave del índice más un apuntador, que es un número de página. El apuntador reside junto con la llave de ADAM que apunta a la página 58, por lo que será leída la página 58.
Aunque la página 58 sigue siendo una página de índice, SQL Server buscara el valor de la llave más alto pero no mayor a GREEN, en este caso sería DATE. El apuntador de DATE sería la página de datos 337, la cual extraería. La página será escaneada para encontrar un registro que contenga GREEN en la columna nombre. El registro es encontrado y regresado como resultado. Note que SQL Server no conoce que existe el registro, hasta que la página de datos es obtenida.
Claramente podemos observar como el índice clustered nos da un rápido acceso a los datos, si consideramos el número de I/Os requeridos para pasar por el índice clustered podemos ver que se requiere un I/O para la página root, uno I/O para la página de índice intermedia y un I/O para extraer la página de datos, en total 3 I/Os. Un table scan seguramente hubiera requerido de más I/Os.
Ahora, los tres I/Os requeridos para atravesar el índice son lecturas físicas al disco? Probablemente no, ya que la página root de un índice es usada por cada query que necesita el índice y por lo tanto va a estar en cache si se usa frecuentemente. Las páginas de índice intermedias y las páginas de datos es menor probable que se encuentren en cache a menos que se tenga data cache muy grande es probable que si estén.
Hemos visto un SELECT que extraer un solo registro, que pasa con un SELECT que extrae un rango de registros.
SELECT * FROM clientes WHERE nombre BETWEEN ‘DATE’ AND ‘KIRK’
En el ejemplo anterior un rango de valores es especificado mediante la columna nombre. Como se ve en la figura 1 tenemos un índice clustered el cual se encuentra ordenado por su llave, por ser clustered los registros que cumplan con la sentencia WHERE se encuentran almacenados en las páginas juntos. En nuestro ejemplo, los seis registros que cumplen con la sentencia se encuentran en tres páginas, por lo que solo se requeriría de tres I/O’s para extraer los datos.
En el caso de que no fuera un índice clustered, los registros no estarían ordenados por lo que tendrían que realizarse en el peor de los casos seis I/Os para extraer los seis registros, pensando que cada uno de ellos se encuentra en una página de datos diferente.
Debemos de considerar que en este ejemplo solo serían seis I/Os sin un índice clustered, pero en la realidad podrían existir mas datos, por ejemplo que el resultado fuera de 120 registros, serían 120 I/Os en caso de estar en diferentes páginas y suponiendo 15 registros por página y un índice clustered, serían solo 8 I/Os una considerable diferencia.
Los índices clustered también soportan búsquedas usando el operador LIKE. Supongamos que ejecutamos el siguiente query:
SELECT * FROM clientes WHERE nombre LIKE ‘N%’
Todos los empleados donde su nombre inicie con N serán extraídos. Nuevamente nuestro índice clustered nos asegura que los registros se encuentran en orden por el campo nombre, dándonos el menor número de I/Os para extraerlos.
Finalmente, que pasa si extraemos los datos en orden. Supongamos que ejecutamos lo siguiente:
SELECT * FROM clientes ORDER BY nombre
El optimizador de consultas sabría que el índice clustered nos garantiza que los datos se encuentran en el orden del índice, por lo tanto no es necesario realizar un sort para satisfacer el ORDER BY, ahorrándonos I/O en el disco.