sql server - nonclustered - ¿Debería cada User Table tener un índice agrupado?
primary key clustered sql server (6)
Considere la posibilidad de utilizar un clustered index cuando las columnas contengan una gran cantidad de valores distintos para evitar la necesidad de que SQL Server agregue un "elemento único" para duplicar los valores clave.
Desventaja : lleva más tiempo actualizar los registros si solo cambian los campos en el índice de clúster.
Evite agrupar construcciones de índices donde exista el riesgo de que ocurran muchas inserciones simultáneas en casi el mismo valor de índice de clúster
Las búsquedas en un índice no agrupado aparecerán más lentamente si el índice agrupado no se compila correctamente o si no incluye todas las columnas necesarias para devolver los datos a la aplicación que realiza la llamada. En el caso de que el índice no agrupado no contenga todos los datos necesarios, SQL Server irá al índice agrupado para obtener los datos faltantes (a través de una búsqueda) lo que hará que la consulta se ejecute más lentamente a medida que se realiza la búsqueda. por fila
Recientemente encontré un par de tablas en una base de datos sin índices agrupados definidos. Pero hay índices no agrupados definidos, por lo que están en HEAP.
En el análisis, encontré que las sentencias select usaban filtro en las columnas definidas en índices no agrupados.
¿No tener un índice agrupado en estas tablas afecta el rendimiento?
El rendimiento es un gran problema peludo. Asegúrese de estar optimizando para lo correcto.
El asesoramiento gratuito siempre vale la pena su precio, y no hay sustituto para la experimentación real.
El propósito de un índice es encontrar filas coincidentes y ayudar a recuperar los datos cuando se encuentran.
Un índice no agrupado en sus criterios de búsqueda ayudará a encontrar filas, pero es necesario que haya una operación adicional para obtener los datos de la fila.
Si no hay un índice agrupado, SQL usa un rowId interno para señalar la ubicación de los datos.
Sin embargo, si hay un índice agrupado en la tabla, rowId se reemplaza por los valores de datos en el índice agrupado.
Entonces, el paso de leer los datos de las filas no sería necesario y estaría cubierto por los valores en el índice.
Incluso si un índice agrupado no es muy bueno para ser selectivo, si esas claves son con frecuencia la mayoría o la totalidad de los resultados solicitados, puede ser útil tenerlos como la hoja del índice no agrupado.
Es difícil decirlo de forma más sucinta que el MVP de SQL Server, Brad McGehee :
Como regla general, cada tabla debe tener un índice agrupado. En general, pero no siempre, el índice agrupado debe estar en una columna que aumente monótonamente, como una columna de identidad o alguna otra columna donde el valor aumenta, y es único. En muchos casos, la clave principal es la columna ideal para un índice agrupado.
BOL hace eco de este sentimiento:
Con pocas excepciones, cada tabla debe tener un índice agrupado.
Las razones para hacer esto son muchas y se basan principalmente en el hecho de que un índice agrupado físicamente ordena sus datos en el almacenamiento .
Si su índice agrupado está en una sola columna aumenta monótonamente, las inserciones se producen en orden en su dispositivo de almacenamiento y no se producirán divisiones de página.
Los índices agrupados son eficientes para encontrar una fila específica cuando el valor indexado es único, como el patrón común de seleccionar una fila en función de la clave principal.
Un índice agrupado a often permite consultas eficientes en columnas que a menudo se buscan rangos de valores (
between,>, etc.).La agrupación en clúster puede acelerar las consultas donde los datos se ordenan comúnmente por columnas o columnas específicas.
Un índice agrupado puede reconstruirse o reorganizarse según demanda para controlar la fragmentación de la tabla.
Estos beneficios pueden incluso aplicarse a las vistas .
Es posible que no desee tener un índice agrupado en:
Columnas que tienen cambios de datos frecuentes, ya que SQL Server debe reordenar físicamente los datos en el almacenamiento.
Columnas que ya están cubiertas por otros índices.
Claves amplias, ya que el índice agrupado también se usa en búsquedas de índices no agrupados.
Las columnas GUID, que son más grandes que las identidades y también los valores aleatorios (que probablemente no se
newsequentialid()), aunquenewsequentialid()podría usarse para ayudar a mitigar el reordenamiento físico durante las inserciones.Una razón poco común para usar un heap (tabla sin un índice agrupado) es si siempre se accede a los datos a través de índices no agrupados y se sabe que el RID (identificador de fila interno de SQL Server) es más pequeño que una clave de índice agrupado.
Debido a estas y otras consideraciones, como las cargas de trabajo de su aplicación en particular, debe seleccionar cuidadosamente sus índices agrupados para obtener el máximo beneficio para sus consultas.
También tenga en cuenta que cuando crea una clave principal en una tabla en SQL Server, creará de manera predeterminada un índice agrupado único (si aún no tiene uno). Esto significa que si encuentra una tabla que no tiene un índice agrupado, pero tiene una clave principal (como deberían hacerlo todas las tablas), un desarrollador había tomado la decisión de crearla de esa manera. Es posible que desee tener una razón convincente para cambiar eso (de los cuales hay muchos, como hemos visto). Agregar, cambiar o descartar el índice agrupado requiere volver a escribir la tabla completa y cualquier índice no agrupado, por lo que puede tomar algo de tiempo en una tabla grande.
No diría que "Todas las tablas deben tener un índice agrupado", yo diría "Mire cuidadosamente cada tabla y cómo se accede y trate de definir un índice agrupado si tiene sentido ". Es una ventaja, como un Joker, tienes solo un Joker por mesa, pero no tienes que usarlo. Otros sistemas de bases de datos no tienen esto, al menos en esta forma, por cierto.
Poner índices agrupados en todas partes sin entender lo que está haciendo también puede matar su rendimiento (en general, el rendimiento de INSERT porque un índice agrupado significa reordenamiento físico en el disco, o al menos es una buena forma de entenderlo), por ejemplo con claves primarias GUID como vemos más y más.
Entonces, lee las excepciones y la razón de Tim Lehner.
Sí, cada tabla debe tener un índice agrupado. El índice agrupado establece el orden físico de los datos en una tabla. Puede comparar esto con el orden de la música en una tienda, por nombre de grupo y / o páginas amarillas ordenadas por un apellido. Como se trata del orden físico, puede tener solo uno, puede estar compuesto por muchas columnas, pero solo puede tener uno.
Lo mejor es colocar el índice agrupado en las columnas a menudo buscadas para un rango de valores. Ejemplo sería un rango de fechas. Los índices agrupados también son eficientes para encontrar una fila específica cuando el valor indexado es único. Microsoft SQL colocará índices agrupados en una restricción PRIMARY KEY automáticamente si no se definen índices agrupados.
Los índices agrupados no son una buena opción para:
Columnas que sufren cambios frecuentes
- Esto hace que toda la fila se mueva (porque SQL Server debe mantener los valores de datos de una fila en orden físico). Esta es una consideración importante en sistemas de procesamiento de transacciones de gran volumen donde los datos tienden a ser volátiles.
Teclas anchas
- Los valores clave del índice agrupado son utilizados por todos los índices no agrupados como claves de búsqueda y, por lo tanto, se almacenan en cada entrada de hoja de índice no agrupado.
Sí, debería tener el índice agrupado en una tabla. Para que todos los índices no agrupados funcionen mejor.