Directrices generales para diseñar índices
Los administradores de bases de datos más experimentados pueden diseñar un buen conjunto de índices, pero esta tarea es muy compleja, consume mucho tiempo y está sujeta a errores, incluso con cargas de trabajo y bases de datos con un grado de complejidad no excesivo. La comprensión de las características de la base de datos, las consultas y las columnas de datos facilita el diseño de los índices.
Consideraciones acerca de las bases de datos
Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de la base de datos:
Si se utiliza un gran número de índices en una tabla, el rendimiento de las instrucciones INSERT, UPDATE, DELETE y MERGE se verá afectado, ya que todos los índices deben ajustarse adecuadamente a medida que cambian los datos de la tabla.
Evite crear demasiados índices en tablas que se actualizan con mucha frecuencia y mantenga los índices estrechos, es decir, defínalos con el menor número de columnas posible.
Utilice un número mayor de índices para mejorar el rendimiento de consultas en tablas con pocas necesidades de actualización, pero con grandes volúmenes de datos. Un gran número de índices contribuye a mejorar el rendimiento de las consultas que no modifican datos, como las instrucciones SELECT, ya que el optimizador de consultas dispone de más índices entre los que elegir para determinar el método de acceso más rápido.
La indización de tablas pequeñas puede no ser una solución óptima, porque puede provocar que el optimizador de consultas tarde más tiempo en realizar la búsqueda de los datos a través del índice que en realizar un simple recorrido de la tabla. De este modo, es posible que los índices de tablas pequeñas no se utilicen nunca; sin embargo, sigue siendo necesario su mantenimiento a medida que cambian los datos de la tabla.
Los índices en vistas pueden mejorar de forma significativa el rendimiento si la vista contiene agregaciones, combinaciones de tabla o una mezcla de agregaciones y combinaciones. No es necesario hacer referencia de forma explícita a la vista en la consulta para que el optimizador de consultas la utilice. Para obtener más información, vea Diseñar vistas indizadas.
Utilice el Asistente para la optimización de motor de base de datos para analizar las bases de datos y crear recomendaciones de índices. Para obtener más información, vea Descripción del Asistente para la optimización de motor de base de datos.
Consideraciones sobre las consultas
Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las consultas:
Cree índices no agrupados en todas las columnas que se utilizan con frecuencia en predicados y condiciones de combinación de las consultas.
.gif "Nota importante")
ImportanteEvite agregar columnas innecesarias. Si agrega demasiadas columnas de índice, puede reducir el espacio en disco y el rendimiento del mantenimiento del índice.
La utilización de índices puede mejorar el rendimiento de las consultas, ya que los datos necesarios para satisfacer las necesidades de la consulta existen en el propio índice. Es decir, sólo se necesitan las páginas de índice y no las páginas de datos de la tabla o el índice agrupado para recuperar los datos solicitados; por tanto, se reduce la E/S global en el disco. Por ejemplo, una consulta de las columnas a y b de una tabla que dispone de un índice compuesto creado en las columnas a, b y c puede recuperar los datos especificados del propio índice.
Escriba consultas que inserten o modifiquen tantas filas como sea posible en una sola instrucción, en lugar de utilizar varias consultas para actualizar las mismas filas. Al utilizar sólo una instrucción, se puede aprovechar el mantenimiento de índices optimizados.
Analice el tipo de la consulta y cómo se utilizan las columnas en ella. Por ejemplo, una columna utilizada en una consulta de coincidencia exacta sería una buena candidata para un índice no agrupado o agrupado. Para obtener más información, vea Tipos de consultas e índices.
Consideraciones sobre las columnas
Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las columnas:
Utilice una longitud corta en la clave de los índices agrupados. Los índices agrupados también mejoran si se crean en columnas únicas o que no admitan valores NULL. Para obtener más información, vea Directrices para diseñar ndices clúster.
Las columnas con tipos de datos ntext, text, image, varchar(max), nvarchar(max) y varbinary(max) no se pueden especificar como columnas de clave de índice. Sin embargo, los tipos de datos varchar(max), nvarchar(max), varbinary(max) y xml pueden participar en un índice no agrupado como columnas de índice sin clave. Para obtener más información, vea Índice con columnas incluidas.
El tipo de datos xml sólo puede ser una columna de clave en un índice XML. Para obtener más información, vea Índices en columnas del tipo de datos XML.
Examine la unicidad de las columnas. Un índice único en lugar de un índice no único con la misma combinación de columnas proporciona información adicional al optimizador de consultas y, por tanto, resulta más útil. Para obtener más información, vea Directrices para diseñar índices únicos.
Examine la distribución de los datos en la columna. A menudo, se crean consultas cuya ejecución es muy larga al indizar una columna con pocos valores únicos, o bien al realizar una combinación en dicha columna. Se trata de un problema fundamental con los datos y la consulta, y normalmente no se puede resolver sin identificar esta situación. Por ejemplo, una agenda telefónica ordenada por apellidos no localizará rápidamente a una persona si todas las personas de la ciudad se llaman Smith o Jones. Para obtener más información acerca de la distribución de datos, vea Utilizar las estadísticas para mejorar el rendimiento de las consultas.
Considere la posibilidad de usar índices filtrados en columnas que tengan subconjuntos bien definidos, por ejemplo columnas dispersas, columnas con una mayoría de valores NULL, columnas con categorías de valores y columnas con intervalos de valores diferenciados. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, así como reducir los costos de almacenamiento y de mantenimiento de los índices. Para obtener más información, vea Directrices generales para diseñar índices filtrados.
Tenga en cuenta el orden de las columnas si el índice va a contener varias columnas. La columna que se utiliza en la cláusula WHERE en una condición de búsqueda igual a (=), mayor que (>), menor que (<) o BETWEEN, o que participa en una combinación, debe situarse en primer lugar. Las demás columnas deben ordenarse basándose en su nivel de diferenciación, es decir, de más distintas a menos distintas.
Por ejemplo, si el índice se define como LastName, FirstName, resultará útil si el criterio de búsqueda es WHERE LastName = 'Smith' o WHERE LastName = Smith AND FirstName LIKE 'J%'. Sin embargo, el optimizador de consultas no utilizará el índice en una consulta que sólo busque FirstName (WHERE FirstName = 'Jane').
Tenga en cuenta la indización de columnas calculadas. Para obtener más información, vea Crear índices en columnas calculadas.
Características de los índices
Después de determinar que un índice resulta adecuado para una consulta, puede seleccionar el tipo de índice que mejor se ajusta a la situación. Entre las características de los índices se incluyen:
Índices agrupados y no agrupados
Índices exclusivos y no exclusivos
Índices de una sola columna y de varias columnas
Orden ascendente o descendente en las columnas del índice
Índices de tabla completa y filtrados en índices no agrupados
También puede personalizar las características iniciales de almacenamiento del índice para optimizar su rendimiento o mantenimiento; por ejemplo, puede establecer una opción como FILLFACTOR. Para obtener más información, vea Establecer opciones de índice. Además, puede determinar la ubicación de almacenamiento del índice si utiliza grupos de archivos o esquemas de partición y mejorar así el rendimiento. Para obtener más información, vea Colocar índices en grupos de archivos.
Vea también