sql - primary - Agrupados vs no agrupados

tipos de indices sql server

Question

Mi conocimiento de nivel inferior de SQL (Server 2008) es limitado y nuestros DBA ahora lo están desafiando. Permítanme explicar (he mencionado declaraciones obvias con la esperanza de que estoy en lo correcto, pero si ve algo mal, por favor díganme) el escenario:

Tenemos una mesa que contiene ''Órdenes de la corte'' para las personas. Cuando creé la tabla, (Nombre: CourtOrder), la creé como:

CREATE TABLE dbo.CourtOrder
(
  CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
  PersonId INT NOT NULL,
  + around 20 other fields of different types.
)

Luego apliqué un índice no agrupado a la clave principal (por eficiencia). Mi razón es que es un campo único (clave principal) y debe indexarse, principalmente para fines de selección, ya que a menudo Select from table where primary key = ...

Luego apliqué un índice CLUSTERED en PersonId. La razón era agrupar físicamente las órdenes para una persona en particular, ya que la gran mayoría del trabajo consiste en obtener pedidos para una persona. Por lo tanto, select from mytable where personId = ...

Me he detenido en esto ahora. Me han dicho que debemos poner el índice agrupado en la clave principal y el índice normal en el ID de persona. Eso me parece muy extraño. En primer lugar, ¿por qué pondría un índice agrupado en una columna única? ¿qué está agrupando? Sin duda, eso es un desperdicio del índice agrupado? Creía que se usaría un índice normal en una columna única. Además, la agrupación del índice significa que no podemos agrupar una columna diferente (uno por mesa, ¿verdad?).

El razonamiento para decirme que cometí un error es que creen que poner un índice agrupado en PersonId haría que las inserciones fuesen lentas. Para la ganancia del 5% en la velocidad de una selección, obtendríamos una degradación del 95% en la velocidad en las inserciones y actualizaciones. ¿Es eso correcto y válido?

Dicen que debido a que agrupamos el personId, SQL Server tiene que reorganizar los datos cada vez que insertamos o hacemos un cambio en el PersonId.

Entonces, pregunté, ¿por qué SQL tiene el concepto de ÍNDICE CLUSTER, si es tan lento? ¿Es tan lento como dicen? ¿Cómo debería haber configurado mis índices para lograr un rendimiento óptimo? Pensé que SELECT se usa más que INSERT ... pero dicen que estamos teniendo problemas de bloqueo en INSERTS ...

Espero que alguien pueda ayudarme.

Answer 1

Algunos autores sugieren no "desperdiciar" el CI en una columna de identity si existe una alternativa que pueda beneficiar las consultas de rango.

Según las pautas de diseño de índice agrupado de MSDN, la clave debe elegirse de acuerdo con los siguientes criterios

1. Se puede usar para consultas frecuentes.
2. Proporcionar un alto grado de singularidad.
3. Puede ser utilizado en consultas de rango.

Su columna CourtOrderID cumple 2 . Su PersonId cumple 1 y 3 . Como la mayoría de las filas terminarán con el uniqueifier agregado de todos modos, también podría declararlo único y usar PersonId,CourtOrderID ya que tendrá el mismo ancho pero será más útil ya que la clave de índice agrupado se agrega a todos los NCI como el localizador de filas y esto les permitirá cubrir más consultas.

El principal problema con el uso de PersonId,CourtOrderID como CI es que probablemente se produzca una fragmentación lógica (y esto afecta especialmente las consultas de rango a las que intenta ayudar) por lo que necesitaría monitorear los factores de relleno y los niveles de fragmentación y realizar el mantenimiento de índices con mayor frecuencia. .

Answer 2

Algunos db con algunos desagradables selects, se une en un procedimiento almacenado, solo diffrence es el índice

ÍNDICES: agrupados frente a no agrupados

  891 rows
  10 sec
  NONCLUSTERED 

  OR

  891 rows
  14 sec
  CLUSTERED

Answer 3

De ninguna manera soy un experto en SQL ... así que tome esto como una vista de desarrollador en lugar de una vista de DBA.

Las inserciones en índices agrupados (ordenados físicamente) que no están en orden secuencial causan trabajo adicional para inserciones / actualizaciones. Además, si tiene muchas inserciones sucediendo a la vez y todas ocurren en la misma ubicación, terminará con contención. Su rendimiento específico varía según sus datos y cómo accede a ellos. La regla general es construir su índice agrupado en el valor estrecho más exclusivo de su tabla (generalmente el PK).

Supongo que su PersonId no cambiará, por lo que las actualizaciones no entran en juego aquí. Pero considere una instantánea de algunas filas con PersonId of 1 2 3 3 4 5 6 7 8 8

Ahora inserte 20 nuevas filas para PersonId of 3. En primer lugar, como esta no es una clave única, el servidor agrega algunos bytes adicionales a su valor (detrás de las escenas) para hacerlo único (lo que también agrega espacio adicional) y luego la ubicación donde estos residirán tienen que ser alterados. Compárelo con la inserción de un PK autoincrementado donde las inserciones suceden al final. La explicación no técnica probablemente se reducirá a esto: hay menos trabajo de ''barajado de hojas'' que hacer si está progresando de forma natural los valores más altos al final de la tabla en comparación con la ubicación de los elementos existentes en esa ubicación al insertar sus artículos.

Ahora, si tiene problemas con Inserts, es probable que inserte muchos de los mismos valores PersonId (o similares) a la vez, lo que está causando este trabajo adicional en varios lugares de la tabla y la fragmentación lo está matando. La desventaja de cambiar al PK agrupado en su caso es si hoy tiene problemas de inserción en las Personas que varían en el valor distribuido en toda la tabla, si cambia su índice agrupado al PK y todas las inserciones ahora suceden en una ubicación, entonces su problema en realidad puede empeorar debido a una mayor concentración de contención. (Por otro lado, si sus inserciones de hoy no están distribuidas por todos lados, pero todas están generalmente agrupadas en áreas similares, entonces su problema probablemente disminuirá al cambiar su índice agrupado de PersonId a su PK porque reducirá al mínimo el fragmentación.)

Sus problemas de rendimiento deben analizarse según su situación particular y tomar este tipo de respuestas solo como pautas generales. Su mejor opción es confiar en un DBA que pueda validar exactamente dónde se encuentran sus problemas. Parece que tienes problemas de contención de recursos que pueden estar más allá de un simple ajuste del índice. Esto podría ser un síntoma de un problema mucho más grande. (Posibles problemas de diseño ... de lo contrario, limitaciones de recursos).

en cualquier caso, buena suerte!

Answer 4

La distinción entre un índice agrupado vs. no agrupado es que el índice agrupado determina el orden físico de las filas en la base de datos . En otras palabras, aplicar el índice agrupado a PersonId significa que las filas se ordenarán físicamente por PersonId en la tabla, lo que permite que una búsqueda de índice vaya directamente a la fila (en lugar de un índice no agrupado, lo que lo dirigiría a la ubicación de la fila, agregando un paso adicional).

Dicho esto, es inusual que la clave principal no sea el índice agrupado, pero no es algo inaudito. El problema con su escenario es, en realidad, lo opuesto a lo que está asumiendo: desea valores únicos en un índice agrupado, no duplicados. Debido a que el índice agrupado determina el orden físico de la fila, si el índice está en una columna no única, el servidor tiene que agregar un valor de fondo a las filas que tienen un valor de clave duplicado (en su caso, cualquier fila con el mismo PersonId ) para que el valor combinado (clave + valor de fondo) sea único.

Lo único que sugeriría es que no use una clave sustituta (su CourtOrderId ) como la clave principal, sino que use una clave primaria compuesta de PersonId y otra columna o conjunto de columnas con identificación única. Si eso no es posible (o no práctico), sin embargo, coloque el índice agrupado en CourtOrderId .

Answer 5

Se explica en el siguiente enlace: https://msdn.microsoft.com/en-us/ms190457.aspx

Agrupado

• Los índices agrupados clasifican y almacenan las filas de datos en la tabla o vista en función de sus valores clave. Estas son las columnas incluidas en la definición del índice. Solo puede haber un índice agrupado por tabla, ya que las filas de datos pueden ordenarse en un solo pedido.

• La única vez que las filas de datos en una tabla se almacenan en orden ordenado es cuando la tabla contiene un índice agrupado. Cuando una tabla tiene un índice agrupado, la tabla se denomina tabla agrupada. Si una tabla no tiene un índice agrupado, sus filas de datos se almacenan en una estructura desordenada llamada montón.

No agrupado

• Los índices no agrupados tienen una estructura separada de las filas de datos. Un índice no agrupado c contiene los valores de clave de índice no agrupados y cada entrada de valor de clave tiene un puntero a la fila de datos que contiene el valor de clave .

• El puntero de una fila de índice en un índice no agrupado a una fila de datos se denomina localizador de fila. La estructura del localizador de filas depende de si las páginas de datos están almacenadas en un montón o en una tabla agrupada. Para un montón, un localizador de filas es un puntero a la fila. Para una tabla agrupada, el localizador de filas es la clave del índice agrupado.

• Puede agregar columnas no clave al nivel de hoja del índice no agrupado para eludir los límites de clave de índice existentes, 900 bytes y 16 columnas de clave, y ejecutar consultas completamente cubiertas e indexadas.