Consejos para usar índices en SQL Server

En SQL Server, para optimizar el rendimiento de las consultas, a veces necesitamos crear índices en la tabla de datos para limitar rápidamente el alcance de la consulta de acuerdo con los requisitos de la consulta y evitar escaneos completos de la tabla.

Hay dos índices Hay dos tipos, a saber, índice agrupado e índice no agrupado

Solo puede haber un índice agrupado en una tabla.

Índice no agrupado

Solo puede haber un índice agrupado en la tabla y se crea de forma predeterminada cuando se crea la clave principal. Especifica el orden en el que los datos se almacenan físicamente en la tabla. También podemos cancelar el índice agrupado en la clave principal. Por lo tanto, es importante considerar los tipos de consultas que probablemente utilice su base de datos y cuáles se usan con más frecuencia, y crear un índice agrupado o una combinación de índices agrupados en uno o más de los campos más utilizados.

Un índice no agrupado es un índice en el que los datos se almacenan en un lugar y el índice se almacena en otro lugar. Los elementos de un índice se almacenan en el orden de las claves de índice, mientras que la información de una tabla se almacena en un orden diferente. Puede utilizar cualquier número de índices no agregados en una tabla. La consulta busca el índice no clasificado para encontrar la ubicación del valor de los datos en la tabla y luego recupera los datos directamente desde esa ubicación. Esto hace que los índices no agregados sean la mejor manera de realizar consultas de coincidencia exacta porque el índice contiene entradas que describen la ubicación precisa del valor de datos que busca la consulta.

Por lo tanto, al elegir crear un índice agrupado, debe prestar atención a los siguientes puntos

) Cuando crea una clave principal en una tabla , agregará automáticamente un índice agrupado a la clave principal, por ejemplo, un campo de numeración automática, no tiene sentido desperdiciar un índice agrupado en la clave principal a menos que solo esté consultando la clave principal, por lo que consultará el campo o campos más frecuentes en consultas condicionales. Establezca un índice agrupado en la combinación. (campo o combinación de campos

) Los índices se crean en función de las necesidades de la aplicación, en lugar de indexar en campos arbitrarios para aumentar la velocidad de consulta. Los índices agregados se crean de acuerdo con los siguientes principios

Se accede a las columnas con una gran cantidad de valores distintos

de forma secuencial utilizando los siguientes operadores BEEEN > >= < y <

Columnas

Consultas que devuelven conjuntos de resultados grandes

Columnas a las que acceden con frecuencia consultas que utilizan cláusulas de unión o GROUP BY, en términos generales, estas columnas son columnas de clave externa en ORDER; BY o los índices en las columnas especificadas en la cláusula GROUP BY eliminan la necesidad de que SQL Server ordene los datos porque las filas ya están ordenadas. Las filas ya están ordenadas, lo que puede mejorar el rendimiento de la consulta.

Las aplicaciones de tipo OLTP que requieren búsquedas muy rápidas de una sola fila (normalmente en la clave principal) deben crear índices agregados en la clave principal.

Por ejemplo, el registro de transacciones bancarias tiene un índice agregado en la fecha de la transacción y los datos se almacenan físicamente de forma secuencial en la página de datos, por lo que los valores duplicados se organizan en un rango para que se encuentren el inicio y el final del rango. primero y el final del rango se encuentra primero. (Si queremos configurar índices agregados en diferentes columnas de campo en la tabla de información básica del empleado, no es necesario configurar índices agregados, porque el contenido solo involucra dos valores diferentes: masculino y femenino

) En el índice agrupado, establecer un índice en una combinación de campos comunes crea un índice compuesto. Si los datos de la tabla de consulta tienen varios campos que siempre aparecen juntos, estos campos se pueden utilizar como índices compuestos. Esto creará una superposición de índice, lo que hará que la instrucción Where sea más eficiente.

) Los índices pueden optimizar las consultas, pero cambiar el contenido de la tabla hará que el índice cambie. Las operaciones frecuentes con datos (como insertar, actualizar, eliminar declaraciones) harán que el sistema gaste mucho dinero en actualizar el índice, lo que provocará una degradación general del rendimiento. En términos generales, cuando los requisitos de rendimiento de las consultas son mayores que los requisitos de rendimiento del mantenimiento de datos, se deben utilizar índices tanto como sea posible. A veces, en casos extremos donde las operaciones en la base de datos son frecuentes, puede eliminar el índice, luego actualizar la tabla de la base de datos con una gran cantidad de datos y finalmente reconstruir el índice.

Cuando un índice se utiliza durante mucho tiempo, generará mucha fragmentación, lo que afecta el rendimiento de la consulta.

Hay dos formas de resolver este problema: una es desfragmentar el índice usando DBCC INDEXDEFRAG y la otra es reconstruir el índice usando DBCC DBREINDEX.

El comando DBCC INDEXDEFRAG es una operación en línea. La desventaja de este método es que no es tan eficiente como una operación de colocar/crear índice agrupado para reorganizar los datos.

La recreación del índice agrupado reorganiza los datos y llena las páginas de datos a un nivel que se puede configurar. usando la opción FILLFACTOR

p>

La desventaja de este enfoque es que no es tan eficiente como eliminar/crear un índice agrupado para reorganizar los datos. La desventaja de este enfoque es que durante el ciclo de eliminación/creación, el índice está fuera de línea y la operación es atómica. Si se interrumpe la creación del índice, el índice no se recreará

Veamos cómo reconstruir el índice

Sintaxis DBCC DBREINDEX ( [ NombreTabla [ nombre_índice [ factor de relleno ] ] )