1Copyright © Computer Aided Education, S.A.

Índices

1. INTRODUCCIÓN

Los índices son uno de los objetos de una base de datos SQL Server que menos frecuentemente se utilizan, y que, sin

embargo, pueden determinar en gran medida las prestaciones de la base de datos.

Los índices son estructuras que se utilizan para acceder rápidamente a la información almacenada en las tablas. La idea principal es evitar recorrer la tabla para lo que, en lugar de ello, se accede al índice.

Debido a esto, durante el diseño inicial de las tablas podemos anticipar la utilización de alguno de los índices pero no de todos.

Normalmente, el diseñador de bases de datos debe estar “atento” al uso que se está haciendo de la base de datos; es decir, a las consultas más frecuentes, y generar nuevos índices si ello contribuye a mejorar su rendimiento.

Sin embargo, aunque pueda parecer que la creación de índices es algo que mejora las prestaciones de la base de datos, no tiene por qué ser siempre así.

Si pensamos que normalmente será más rápido consultar un índice que recorrer toda una tabla, entendemos que los índices son útiles cuando queremos consultar la información de una tabla.

Sin embargo, si pensamos en lo que ocurre cada vez que insertamos nuevos registros o que modificamos/eliminamos los existentes en una tabla, veremos que la presencia de índices puede ralentizar estos procesos ya que ellos mismos también tienen que actualizarse.

Esto nos debe hacer pensar que un buen diseño de índices es crucial en nuestra base de datos y que según las características de ésta, será adecuado utilizarlos en mayor o menor medida.

En esta lección veremos qué considerar durante el diseño de los índices y ofreceremos toda una serie de directrices que nos pueden ayudar en este proceso, pero teniendo en cuenta que muchas veces es necesario realizar pruebas del rendimiento de la base de datos para encontrar el mejor conjunto de índices.

2

Índices

Lo primero que tenemos que conocer es que podemos crear dos tipos de índices en una tabla de SQL Server: agrupados y no

agrupados.

Sólo puede haber un índice agrupado por tabla ya que los registros de ésta se ordenan y almacenan físicamente según ese índice.

Teniendo en cuenta este detalle, SQL Server genera, de forma automática, un índice agrupado al definir la clave principal de una tabla, como puede ver en la carpeta Índices de esta figura.

La tabla Clientes tiene el índice PK_Clientes. Este índice se creó automáticamente al definir la clave principal en el campo IdCliente. Fíjese que se trata de un índice agrupado.

También tiene otro índice, que se generó automáticamente al crear la restricción UNIQUE. En este caso, no es un índice agrupado.

Las restricciones de clave principal y UNIQUE generan automáticamente índices en la definición de la tabla.

¿Qué debemos entender entonces? Pues que SQL Server supone que la mejor forma de mantener ordenada y almacenada la información de esta tabla es según su campo IdCliente, por lo que ha creado un índice agrupado con dicho campo.

2. ÍNDICES AGRUPADOS

Los índices agrupados también se

conocen como índices

CLUSTERED.

3Copyright © Computer Aided Education, S.A.

Índices

Aquí podemos ver la estructura de un índice agrupado. Se trata del índice según el campo IdCliente de la tabla Clientes.

En un índice encontramos tres tipos de registros o nodos: el nodo raíz, los nodos intermedios y los nodos hoja.

El nodo raíz es desde donde parte toda la estructura, que se amplía con los nodos intermedios y que llega a las hojas de datos o donde realmente está almacenada la información de la tabla Clientes.

Por ejemplo, imagine la consulta “todos aquellos clientes cuyo IdCliente esté contenido entre el 158 y el 271”.

Cuando SQL Server recibe esta consulta, comprueba qué valor almacenado en el nodo raíz del índice es el más apropiado para empezar a buscar y elige el 157.

Fíjese que en el índice sólo se almacena el valor de IdCliente (columna clave del índice) y no el resto de detalles que tenemos en la tabla Clientes.

Este valor apunta a un nodo intermedio que finalmente da paso a las hojas de datos de la propia tabla Clientes.

Como se trata de un índice agrupado, los clientes se guardan físicamente uno detrás del otro según el campo IdCliente, por lo que una vez encontrado el primero, es muy rápido acceder al resto.

Podemos acceder a las propiedades de un índice mediante su menú contextual, con lo que obtendremos un cuadro de diálogo como el de la página siguiente.

4

Índices

En la lista Tipo de índice podemos ver que se trata de un índice agrupado. Mientras que en la sección Columnas de clave de índice quedan reflejadas aquellas columnas que se almacenan en el índice.

Debido a que sólo podemos crear un índice agrupado y de que éste facilita la forma más rápida de acceder a la información de una tabla, debemos tener en cuenta algunas indicaciones para elegir la mejor opción:

En general, utilice índices en aquellas tablas en las que no sea muy frecuente la inserción o modificación de datos, sino más bien su consulta.

Tener en cuenta aquellos campos que se utilizan en consultas frecuentes. Si la consulta hace referencia exclusivamente a los campos almacenados en el índice, no será necesario acceder a la tabla para extraer el resultado de la misma.

Tener en cuenta campos en los que sus valores tengan un alto grado de unicidad. Por ejemplo, no es adecuado crear un índice agrupado en un campo donde se almacene el sexo de una persona, ya que sólo puede haber dos posibles valores: hombre o mujer.

5Copyright © Computer Aided Education, S.A.

Índices

Por eso los campos con la característica Identidad suelen ser buenos candidatos para formar un índice agrupado.

Tener en cuenta campos en los que se realicen consultas de intervalos: “IdProducto entre el 10 y el 35”, “Ordenes enviadas durante el mes de agosto”, etc. Se refieren a consultas en las que se utilizan la cláusula BETWEEN o los operadores <, >, <= y >= del lenguaje SQL.

Campos que aparecen en cláusulas ORDER BY o GROUP BY del lenguaje SQL. Ya que estos comandos ordenan el resultado de la consulta, es útil tener un índice agrupado en dichos campos porque los registros ya estarán ordenados.

Si el campo o conjunto de campos que usted ha elegido para crear un índice agrupado tiene la característica de ser único para los registros de la tabla, debe especificarlo aquí con la propiedad Único; si no, no lo haga y SQL Server se encargará de almacenar físicamente los registros según dicho índice, pero sin limitar los valores que puede tomar el campo elegido.

Aquí vemos un ejemplo más de un índice agrupado creado automáticamente por SQL Server. En este caso, como la clave principal de la tabla está constituida por dos campos, estos dos campos aparecen en el índice.

6

Índices

Recuerde que la característica fundamental de un índice agrupado es que los registros de la tabla se ordenan y almacenan según dicho índice.

Por lo tanto, piense en qué orden es el más adecuado para cada tabla y establezca el índice agrupado para conseguirlo.

A diferencia de los índices agrupados, puede crear tantos índices no agrupados como necesite para una tabla.

En este caso, el último enlace de un índice no agrupado no apunta directamente a los datos sino a dónde están esos datos.

Esto hace que sea necesario un acceso más para llegar a los datos deseados pero, incluso así, la creación de un índice de este tipo puede acelerar considerablemente el acceso a los datos.

Aquí puede ver representado cómo se accede a la información si utilizamos un índice no agrupado.

3. ÍNDICES NO AGRUPADOS

7Copyright © Computer Aided Education, S.A.

Índices

Fíjese que los nodos hoja del índice no se corresponden con la información en sí, como ocurre con los índices agrupados, sino con enlaces a dicha información, almacenada en otro lugar.

Sin embargo, esta situación se da cuando no existe un índice agrupado en la tabla. Si la tabla tiene definido un índice agrupado, entonces los nodos hoja del índice no agrupado no apuntan a los datos en sí sino a las entradas del índice agrupado, lo que representa un paso más para acceder a los datos.

Aún en estos casos, si la tabla se consulta frecuentemente por el campo del índice no agrupado, el rendimiento seguramente será mejor que si no dispusiéramos de él.

Recorrer una tabla con muchos registros es un proceso que, si se repite con mucha frecuencia, debemos evitar utilizando índices agrupados y no agrupados.

Un ejemplo de consulta que puede beneficiarse por un índice no agrupado lo tenemos en la tabla Productos.

Imagine que es frecuente consultar la base de datos para conocer los productos de una determinada categoría. Por ejemplo, podríamos incluir un filtro en la aplicación web que accede a la base de datos para que el usuario elija el tipo o categoría de productos que quiere comprar.

En este caso, disponer de un índice no agrupado según el identificador de la categoría en la tabla Productos podría acelerar el acceso a dicha información, ya que en el índice no agrupado dispondríamos de la ubicación exacta de cada producto de dicha categoría.

Seguramente utilizar este índice sea más efectivo que recorrer toda la tabla, sobre todo si se tienen muchos productos.

Para crear un índice, elija Nuevo índice en el menú contextual de la carpeta Índices correspondiente a la tabla y establézcalo adecuadamente en el cuadro de diálogo que aparece.

Lo primero es indicar el nombre del nuevo índice. Seguiremos la nomenclatura de SQL Server y lo identificaremos con el prefijo IX. Por ejemplo, IX_Categoria para el caso que hemos comentado en la tabla Productos.

8

Índices

Vemos que el tipo predeterminado para este caso es no agrupado porque ya existe un índice agrupado en la tabla (coincidiendo con la clave principal).

Además, en este caso la información del índice no será única porque podemos tener varios productos de la misma categoría.

Finalmente indicaremos el conjunto de columnas que compondrán el índice en el apartado Columnas de clave de índice. Debe pulsar en el botón Agregar y seleccionar las columnas que se guardarán como clave del índice.

9Copyright © Computer Aided Education, S.A.

Índices

Podríamos elegir también el orden (ascendente o descendente) con el que queremos almacenar la información en el índice. Esto vendrá dado por las consultas que vayamos a realizar.

Tenga en cuenta, sin embargo, que como se trata de un índice no agrupado, no será tan efectivo como uno agrupado.

Así, el índice apuntará a cada uno de los productos de una categoría, pero como éstos no tienen por qué estar almacenados de forma continua, el acceso no será tan rápido.

Algunas directrices a la hora de crear índices no agrupados:

En general, utilice índices en aquellas tablas en las que no sea muy frecuente la inserción o modificación de datos, sino más bien su consulta.

Utilice índices no agrupados para mejorar el rendimiento de consultas con cláusulas JOIN o GROUP BY del lenguaje SQL.

Consultas en las que la cláusula WHERE devuelve coincidencias exactas (como en el ejemplo visto de productos de una determinada categoría).

Consultas en las que los datos deseados están incluidos en el propio índice no agrupado. De esta forma, no será necesario acceder a la tabla, ya que la información estará disponible en el propio índice.

Campos con varios valores distintos y en los que no se ha podido crear un índice agrupado.

Campos que actúen como clave externa en una relación con otra tabla.

Al diseñar, crear o modificar un índice, debe tener en cuenta varias opciones de índice. Algunas de ellas sólo se aplican

al crear el índice; mientras que otras, cuando se modifica.

Puede acceder a estas opciones tanto al crear el índice como mediante el comando Propiedades del menú contextual del índice.

4. OPCIONES Y COLUMNAS INCLUIDAS

10

Índices

En cualquier caso, deberá acceder al apartado Opciones, como muestra la figura.

A continuación se describen las opciones más interesantes (entre paréntesis puede encontrar sus nombres en inglés, que son los que se utilizan al crear los índices mediante comandos T-SQL):

Quitar el índice existente (DROP_EXISTING): si está creando un índice con el mismo nombre que uno existente, podrá indicar que previamente este último se elimine. Es mejor esta opción que eliminar el índice existente desde la carpeta Índices.

Volver a generar: indica si el índice se tiene que volver a generar (crear de nuevo su contenido) al modificar algunas de sus propiedades.

Pasar por alto valores duplicados (IGNORE_DUP_KEY): es otra de las propiedades sólo disponibles al crear un nuevo índice único. Indica si SQL Server debe pasar por alto valores duplicados en las claves del índice.

11Copyright © Computer Aided Education, S.A.

Índices

Si está activada esta opción, SQL Server emitirá una advertencia cuando encuentre una entrada duplicada en el índice, pero seguirá adelante omitiendo la fila duplicada (porque ya existe en el índice); si no está activada, producirá un error y no continuará creando el índice.

Volver a calcular estadísticas automáticamente (STATISTICS_NORECOMPUTE): SQL Server mantiene estadísticas del uso de los índices. Con esta opción indicamos si se tienen que volver a calcular al modificarlo.

Usar bloqueos de fila/página al tener acceso al índice (ALLOW ROW/PAGE LOCKS): indica si se deben permitir los bloqueos cuando se utiliza el índice para acceder a los datos de la tabla. Generalmente es mejor activar estas opciones para que sea SQL Server el que administre los bloqueos.

Almacenar resultados de orden intermedio en la base de datos tempdb (SORT_IN_TEMPDB): indica si las operaciones necesarias durante la creación del índice se realizan en la base de datos tempdb o utilizando la ubicación de la misma base de datos en la que se crea el índice.

Normalmente es mejor hacerlo en tempdb por motivos de rendimiento.

Permitir procesamiento en línea de instrucciones DML al crear el índice (ONLINE): sólo disponible durante la creación del índice, esta propiedad es una de las más importantes, ya que si la activamos, permitiremos que los usuarios sigan utilizando la tabla mientras se crea el índice; en caso contrario, la tabla se bloqueará hasta que el índice haya sido creado.

Piense que si se trata de una tabla con muchos registros, el proceso de crear un índice puede llegar a ser costoso.

Finalmente, veamos una característica interesante de los índices no agrupados: la posibilidad de incluir columnas, pero no como claves del índice.

Para ello, sitúese en el apartado Columnas incluidas y agregue las columnas deseadas exactamente igual que en el caso de las columnas de clave del índice.

12

Índices

Como se ha comentado en alguna ocasión, si la consulta que realizamos puede ser resuelta únicamente con la información del índice, SQL Server no accederá a la tabla, por lo que el procedimiento será mucho más rápido.

Esto nos puede hacer pensar que tal vez sea interesante incluir un mayor número de columnas en el índice para “cubrir” aquellas consultas más utilizadas.

El problema que tiene esto es que, como se trata de columnas clave del índice, sus valores se almacenarán en todos los nodos del índice y tendrán que actualizarse cada vez que insertemos, modifiquemos o eliminemos registros en la tabla.

Para evitar este problema y mantener los índices lo más pequeños posible, podemos incluir columnas que no sean columnas clave del índice.

Esto tiene la ventaja de que los valores de estas columnas “incluidas” sólo se almacenan en los nodos hoja del índice por lo que el mantenimiento de éste no es mayor.

13Copyright © Computer Aided Education, S.A.

Índices

Por ejemplo, hemos creado un índice con el campo IdCategoria porque frecuentemente se realiza la consulta de los productos de una determinada categoría de la tienda virtual.

Es fácil pensar que en dicha consulta se necesitará conocer también el nombre de cada uno de estos productos e incluso su precio.

Podríamos incluir estos dos campos en el índice, de forma que la consulta quedaría totalmente cubierta con él, sin necesidad de acceder a la tabla, pero tampoco cargando mucho el índice, ya que no serían columnas clave. Obsérvelo en la figura anterior.

SQL Server hace algo parecido a incluir columnas cuando la tabla tiene un índice agrupado.

En este caso, anexa el contenido de las columnas del índice agrupado al final de todos los índices no agrupados.

En el ejemplo que estamos viendo esto quiere decir que en el índice no agrupado IX_Categoria no sólo aparecería la columna IdCategoria sino que también el campo IdProducto, que es la columna clave del índice agrupado.

Hay que tener en cuenta este detalle porque a veces no es necesario incluir columnas, ya que SQL Server lo ha hecho por nosotros con las columnas del índice agrupado.

En resumen vemos que en el apartado Columnas incluidas introduciremos aquellas columnas que queremos que incluya el índice pero no como columnas clave; mientras que en la sección General, indicaremos las columnas clave del índice.

La confección del conjunto de índices apropiado no es sencilla por lo que sólo entender lo mejor posible cómo funcionan nos

puede ayudar a realizarla.

Debemos entender que los índices tienen el objetivo de acelerar el acceso a los datos, por lo que son de especial utilidad en aquellas tablas donde lo más frecuente es la selección o extracción de información y no su modificación.

5. ¿CUÁNDO SE UTILIZAN LOS ÍNDICES?

14

Índices

Los índices se utilizan cuando SQL Server recibe la solicitud de una consulta. En ese momento tiene dos opciones: bien recorrer la tabla o bien utilizar el índice.

Cuando la tabla tiene muchos registros, es lógico pensar que será mucho más rápido utilizar el índice, ya que normalmente será mucho más pequeño y, además, dispondrá de cierto orden.

SQL Server decide qué hacer consultando las estadísticas de uso de los distintos índices y en función del tamaño de la tabla. La mayoría de las veces elegirá utilizar el índice (sobre todo cuando en la consulta intervienen varias tablas en las que hemos definido índices para las claves externas).

Como contrapartida a esta situación en la que los índices pueden ser de gran utilidad, tenemos las operaciones de inserción (INSERT), modificación (UPDATE) y eliminación (DELETE) de registros.

En estos casos no sólo se ve afectada la tabla, sino también los índices. Además, en el caso del índice agregado esto puede ser incluso peor, ya que puede implicar modificar la ubicación de almacenamiento de los registros para seguir manteniéndolos ordenados.

Esta circunstancia, junto al hecho ineludible de que los índices también ocupan espacio ya que, al fin y al cabo, están almacenando información que ya disponemos en las tablas, hacen que veamos la necesidad de analizar con cuidado cada situación y experimentar hasta conseguir un buen diseño de índices.

Puede obtener la sintaxis correcta del comando CREATE INDEX en la colección de documentación “Libros de pantalla de SQL Server”.

Este comando T-SQL es el que se utiliza para crear índices sin una herramienta visual como SQL Server Management Studio.