¿Cuáles son las diferencias entre un índice agrupado y un índice no agrupado?

¿Cuáles son las diferencias entre un non-clustered index clustered y un non-clustered index ?

Índice agrupado

  • Solo uno por mesa
  • Más rápido para leer que no agrupado ya que los datos se almacenan físicamente en orden de índice

Índice no agrupado

  • Se puede usar muchas veces por mesa
  • Más rápido para las operaciones de inserción y actualización que un índice agrupado

Ambos tipos de índice mejorarán el rendimiento cuando seleccionen datos con campos que usan el índice, pero ralentizarán la actualización e insertarán operaciones.

Debido a la inserción y actualización más lenta, los índices agrupados deben establecerse en un campo que normalmente es incremental, es decir Id o Timestamp.

SQL Server normalmente solo usará un índice si su selectividad es superior al 95%.

Los índices agrupados físicamente ordenan los datos en el disco. Esto significa que no se necesitan datos adicionales para el índice, pero solo puede haber un índice agrupado (obviamente). El acceso a los datos mediante un índice agrupado es más rápido.

Todos los demás índices deben ser no agrupados. Un índice no agrupado tiene un duplicado de los datos de las columnas indexadas ordenadas junto con punteros a las filas de datos reales (punteros al índice agrupado, si hay alguno). Esto significa que el acceso a los datos a través de un índice no agrupado debe pasar por una capa adicional de indirección. Sin embargo, si selecciona solo los datos disponibles en las columnas indexadas, puede recuperar los datos directamente desde los datos del índice duplicado (por eso es una buena idea SELECCIONAR solo las columnas que necesita y no usar *)

Los índices agrupados se almacenan físicamente en la tabla. Esto significa que son los más rápidos y solo puede tener un índice agrupado por tabla.

Los índices no agrupados se almacenan por separado, y puede tener tantos como desee.

La mejor opción es establecer su índice agrupado en la columna única más utilizada, generalmente PK. Siempre debe tener un índice agrupado bien seleccionado en sus tablas, a menos que sea una razón muy convincente, no se puede pensar en una sola, pero bueno, puede estar ahí, porque no aparece.

Índice agrupado

  1. Solo puede haber un índice agrupado para una tabla.
  2. Por lo general, hecho en la clave principal.
  3. Los nodos hoja de un índice agrupado contienen las páginas de datos.

Índice no agrupado

  1. Solo puede haber 249 índices no agrupados para una tabla (hasta que las versiones posteriores de sql 2005 admitan hasta 999 índices no agrupados).
  2. Usualmente hecho en cualquier tecla.
  3. El nodo hoja de un índice no agrupado no consta de las páginas de datos. En cambio, los nodos hoja contienen filas de índice.

Índice agrupado

  • Solo un índice agrupado puede estar allí en una tabla
  • Ordene los registros y guárdelos físicamente según el orden
  • La recuperación de datos es más rápida que los índices no agrupados
  • No necesita espacio adicional para almacenar la estructura lógica

Índice no agrupado

  • Puede haber cualquier número de índices no agrupados en una tabla
  • No afecta el orden físico. Cree un orden lógico para las filas de datos y use los punteros a los archivos de datos físicos
  • La inserción / actualización de datos es más rápida que el índice agrupado
  • Use espacio extra para almacenar estructura lógica

Además de estas diferencias, debe saber que cuando la tabla no está agrupada (cuando la tabla no tiene un índice agrupado) los archivos de datos están desordenados y utiliza la estructura de datos de Heap como estructura de datos.

Agrupado básicamente significa que los datos están en ese orden físico en la tabla. Es por eso que puedes tener solo uno por mesa.

Unclustered significa que es “solo” un orden lógico.

Pros:

Los índices agrupados funcionan muy bien para los rangos (por ejemplo, seleccione * de my_table donde my_key entre @min y @max)

En algunas condiciones, el DBMS no tendrá que trabajar para ordenar si usa una instrucción orderby.

Contras:

Los índices agrupados pueden ralentizar las inserciones porque los diseños físicos de los registros deben modificarse a medida que se colocan los registros si las nuevas claves no están en orden secuencial.

Un índice agrupado realmente describe el orden en que los registros se almacenan físicamente en el disco, de ahí la razón por la que solo puede tener uno.

Un índice no agrupado define un orden lógico que no coincide con el orden físico en el disco.

Un índice agrupado es esencialmente una copia ordenada de los datos en las columnas indexadas.

La principal ventaja de un índice agrupado es que cuando su consulta (búsqueda) localiza los datos en el índice, entonces no se necesita IO adicional para recuperar esos datos.

La sobrecarga de mantener un índice agrupado, especialmente en una tabla actualizada con frecuencia, puede generar un rendimiento deficiente y, por esa razón, puede ser preferible crear un índice no agrupado.

Una base de datos indexada tiene dos partes: un conjunto de registros físicos, que están dispuestos en un orden arbitrario, y un conjunto de índices que identifica la secuencia en la que los registros deben leerse para producir un resultado ordenado por algún criterio. Si no existe una correlación entre la disposición física y el índice, entonces leer todos los registros en orden puede requerir la realización de muchas operaciones de lectura independientes de un solo registro. Debido a que una base de datos puede leer docenas de registros consecutivos en menos tiempo de lo que llevaría leer dos registros no consecutivos, el rendimiento puede mejorarse si los registros que son consecutivos en el índice también se almacenan consecutivamente en el disco. Especificar que un índice está agrupado hará que la base de datos haga algún esfuerzo (diferentes bases de datos difieren en cuanto a la cantidad) para organizar las cosas de modo que los grupos de registros que son consecutivos en el índice sean consecutivos en el disco.

Por ejemplo, si uno comenzara con una base de datos vacía no agrupada y agregara 10.000 registros en secuencia aleatoria, los registros probablemente se agregarían al final en el orden en que se agregaron. Leer la base de datos en orden por el índice requeriría 10,000 lecturas de un registro. Sin embargo, si se utilizara una base de datos agrupada, el sistema podría verificar al agregar cada registro si el registro anterior se almacenó por sí mismo; si este fuera el caso, podría escribir ese registro con el nuevo al final de la base de datos. Luego podría mirar el registro físico antes de las ranuras donde solían residir los registros movidos y ver si el registro que siguió se almacenó por sí mismo. Si descubrió que ese es el caso, podría mover ese registro a ese punto. El uso de este tipo de enfoque podría hacer que muchos registros se agrupen en pares, lo que podría duplicar la velocidad de lectura secuencial.

En realidad, las bases de datos agrupadas utilizan algoritmos más sofisticados que este. Sin embargo, una cosa clave a tener en cuenta es que hay una compensación entre el tiempo requerido para actualizar la base de datos y el tiempo requerido para leerla secuencialmente. El mantenimiento de una base de datos agrupada boostá significativamente la cantidad de trabajo requerido para agregar, eliminar o actualizar registros de cualquier manera que afecte la secuencia de clasificación. Si la base de datos se leerá secuencialmente con mucha más frecuencia de lo que se actualizará, la agrupación en clústeres puede ser una gran ganancia. Si se actualiza con frecuencia, pero rara vez se lee en secuencia, la agrupación puede ser una gran pérdida de rendimiento, especialmente si la secuencia en la que los elementos se agregan a la base de datos es independiente de su orden de clasificación con respecto al índice agrupado.