válido - ¿Mejores prácticas y anti patrones en la creación de índices en SQL Server?
restricciones de nombres que puede tener una base de datos (8)
¿Cuáles son las cosas que consideraría al definir índices, agrupados y no agrupados, para SQL Server? ¿Hay algún antipatrón que los principiantes DB deberían conocer? Por favor, explique el "Por qué" o proporcione referencias si es posible.
Aquí hay un par de anti-patrones de indexación que he visto o de los que he sido culpable:
Cobertura general - Colocación de índices en tablas con poco o ningún crecimiento y un conteo de filas (muy) bajo. Esto es contraproducente ya que la búsqueda de índice puede llevar más tiempo que una exploración de tabla.
Indice de fuerza industrial : colocación de un índice en una columna de clave principal. Me han pedido que haga esto para "acelerar" una consulta.
Considere leer el Diseño de índice de base de datos relacional y los optimizadores . Le dará muchas ideas y las razones por las que son buenas.
No pruebe índices u optimice consultas sin una base de datos llena de datos representativos.
La base de datos generalmente ignorará cualquier índice en un campo booleano. Lo ignorará como parte de un índice compuesto. (Sin embargo, consulte "índice filtrado" en SQL Server 2008.)
Para índices compuestos, donde se proporcionarán todos los valores, enumere en orden inverso por cardinalidad (o aridad, o cuántos valores distintos hay en los datos).
No asumas nada. Prueba todo
Solo tiene un índice agrupado. No lo desperdicie en un índice único a menos que esté seguro de que realmente necesitará tirar filas secuenciadas en esa columna. Desea usarlo para los casos en que a menudo se obtienen múltiples filas adyacentes.
Un índice de "cobertura" es uno que, en sí mismo, contiene todos los campos necesarios para resolver una selección. Recuerde que un índice que "casi" cubre no es lo suficientemente bueno en la mayoría de los casos críticos.
La mayoría de lo que lee en los blogs en línea sobre el diseño de índices es incorrecto o altamente calificado y no se aplica en su caso, o está mal calibrado en cuanto a los beneficios y el costo.
Un índice es básicamente una "hoja de trampa". Permite al DBMS encontrar un valor (o rango de valores) particular en el disco sin tener que escanear toda la tabla. Generalmente, usted paga un poco de penalización en INSERT / UPDATE / DELETE al tener un índice, pero rara vez tanto que es un cuello de botella en sí mismo. Un buen DBMS solo usará índices cuando ayuden a realizar consultas de rendimiento, por lo que no hay muchos antipatrones muy negativos aquí; por lo general, no te lastima mucho si tienes índices adicionales (a menos que estés hablando de tablas muy altamente transaccionales). Dicho esto, una indexación cuidadosa en todos los ámbitos le ayudará a asegurarse de que los realmente importantes estén allí, y la mejor forma de descubrirlo es mediante el perfil de su aplicación.
La clave para entender cuándo y cuándo no usar los índices es comprender lo que realmente están haciendo bajo las sábanas. En pocas palabras, los quiere cuando la selectividad del índice es alta (es decir, el número de valores posibles diferentes es alto en comparación con el tamaño de la relación). Entonces, por ejemplo, si tiene una tabla con 10,000 filas, y tiene una columna llamada "color" en esa tabla que es "roja" o "azul", no ayuda mucho tener un índice, porque el DBMS probablemente tenga que cargar la mayoría de las páginas en la memoria de todos modos (suponiendo una distribución aleatoria). Por el contrario, un índice en el ID de clave principal de una tabla (que casi siempre se agrega automáticamente) hará que las búsquedas en esa tabla se aclaren rápidamente, en el orden de log (n), porque una cantidad muy pequeña de nodos en el árbol debe ser examinado para encontrar la página en el disco donde reside el registro.
Los índices en la mayoría de los sistemas de bases de datos modernos se implementan con un árbol B +, que es una variante muy buena de B-Trees que está optimizada para almacenamiento secundario lento (discos en lugar de memoria). Puede obtener una buena introducción a su uso y funcionalidad de los sistemas de bases de datos: el libro completo .
Una cosa que descubrí que las personas olvidan hacer al indexar es indexar la clave externa. Los índices de claves principales se crean automáticamente (estoy hablando de SQL Server, otras bases de datos pueden variar) pero las claves externas no. Pero muchas personas asumen que lo son (presumiblemente las mismas personas que presumen que los disparadores actuarán solo en un registro a la vez). Dado que casi siempre participan en las uniones (¿por qué si no tendrías uno?), Necesitan indexarse la mayor parte del tiempo (la excepción sería una tabla muy pequeña).
Definiría mi anti-Patrón de indexación favorito como: ¿Por qué mis consultas son tan lentas? -la condición que ocurre cuando las personas que no usan bases de datos diseñan grandes bases de datos y ni siquiera saben lo suficiente como para poner ningún índice en ellas. Un síntoma típico se encuentra en un tablero de mensajes donde la persona pregunta por qué lleva 40 minutos realizar una consulta simple en comparación con su tabla de registro de 50 millones. Es probable que este antipatrón ocurra con muchos otros antipatrones de diseño de bases de datos, ya que es probable que alguien que no esté familiarizado con la indexación haya diseñado una estructura de base de datos eficiente o efectiva.
Veo un patrón de gente que solo usa el Asesor de ajuste de motor de base de datos y piensa que de alguna manera es lo suficientemente inteligente como para sugerir los índices y estadísticas más óptimos. Ese es un patrón que debes evitar.
En su lugar, observe los resultados de su plan de consulta antes de decidir cómo acercarse a la optimización. Pueden decirle mucha información útil acerca de cómo abordar mejor una consulta. Muchas veces las personas lanzan índices en todo tipo de campos porque piensan que es lo correcto, ignorando que en algunos casos cualquier ganancia que puedan lograr podría ser negada en general por el impacto de tener el índice mismo (muchos índices en una tabla). puede ralentizar inserciones y actualizaciones).
Busque un libro sobre consultas en T-SQL por Itzik Ben-Gan (MS Press) la próxima vez que esté en una librería (Lo tendrán). Lea los primeros 3 capítulos y se explicará cómo funciona el proceso de consulta dentro de SQL Server: en lo que respecta a su trabajo con esta tecnología en particular, pueden ser los 3 capítulos más importantes que jamás haya leído.
El Blunderbus : un anti-patrón de indexación del que he sido culpable en el pasado. Poner un índice o variaciones del mismo índice en columnas en una tabla sin haber analizado un plan de explicación o realmente haber entendido cómo funciona el optimizador.
Poner un índice agrupado en una columna GUID no es una buena idea. Un índice agrupado define el orden físico de cómo se almacenan los datos. Por lo tanto, es mejor colocar un índice agrupado en una columna que incremente o disminuya, y que sea único.
(Si el índice agrupado no es único, SQL Server agregará el PK internamente al índice agrupado). Un Guid es un valor aleatorio (a menos que se asegure de usar guid secuencial), por lo que cada vez que inserte o actualice un guid en una columna que forma parte de un índice agrupado, SQL Server deberá mover los registros en las páginas de datos.
Además, intente colocar un índice agrupado en las columnas que usa con frecuencia para realizar búsquedas de "rango".