Ben Richardson

¿Cuál es la diferencia entre Índices Agrupados y No Agrupados en SQL Server?

May 28, 2018 by

Los índices son usados para acelerar el proceso de consultas en SQL Server, resultando en alto desempeño. Son similares a índices de libros de texto. En los libros de texto, si usted necesita ir a un capítulo en particular, usted va al índice, encuentra el número de página del capítulo y va directamente a esa página. Sin índices, el proceso de encontrar su capítulo deseado habría sido muy lento.

Lo mismo aplica a los índices en bases de datos. Sin índices, un Sistema de Gestión de Bases de Datos (DBMS, por sus siglas en inglés) tiene que recorrer todos los registros en la tabla para recuperar los resultados deseados. Este proceso es llamado escaneo de tablas, y es extremadamente lento. Por otra parte, si usted crea índices, la base de datos va a ese índice primero y luego recupera los correspondientes registros de tabla directamente.

Hay dos tipos de Índices en SQL Server:

  1. Índices Agrupados
  2. Índices No Agrupados

Índices Agrupados

Un índice agrupado define el orden en el cual los datos son físicamente almacenados en una tabla. Los datos de las tablas pueden ser ordenados sólo en una forma, por lo tanto, sólo puede haber un índice agrupado por tabla. En SQL Server, la restricción de llave primaria crea automáticamente un índice agrupado en esa columna en particular.

Demos un vistazo. Primero, cree una tabla “student” dentro de “schooldb” ejecutando el siguiente script:

Note aquí que, en la tabla “student”, hemos establecido la restricción de llave primaria en la columna “id”. Esto crea automáticamente un índice agrupado en la columna “id”. Esto crea automáticamente un índice agrupado en la columna “id”. Para ver todos los índices en una tabla particular, ejecute el procedimiento agregado “sp_helpindex”. Este procedimiento almacenado acepta el nombre de la tabla como parámetro y recupera todos los índices de la tabla. La siguiente consulta recupera los índices creados en la tabla “student”.

La consulta de arriba retornará este resultado:

index_name index_description index_keys
PK__student__3213E83F7F60ED59 clustered, unique, primary key located on PRIMARY id

En la salida usted puede ver el único índice. Este es el índice que fue automáticamente creado debido a la restricción de llave primaria en la columna “id”.

Otra manera de ver índices de tablas es yendo a “Object Explorer->Databases->Database_Name->Tables->Table_Name -> Indexes”. Vea la siguiente captura de pantalla para referencia.

Este índice agrupado almacena el registro en la tabla “student” en orden ascendente de “id”. Por lo tanto, si el registro insertado tiene el id de 5, el registro será insertado en la 5ª fila de la tabla en lugar de la primera fila. De forma similar, si el cuarto registro tiene un id de 3, será insertado en la tercera fila en lugar de la cuarta fila. Esto se debe a que el índice agrupado tiene que mantener el orden físico de los registros almacenados de acuerdo a la columna indexada, por ejemplo, id. Para ver esta acción de orden, ejecute el siguiente script:

El script de arriba inserta diez registros en la tabla student. Note aquí que los registros son insertados en el orden al azar de los valores en la columna “id”. Pero, debido al índice agrupado por defecto en la columna id, los registros son almacenados físicamente en orden ascendiente de los valores en la columna “id”. Ejecute la siguiente sentencia SELECT para recuperar los registros desde la tabla student.

Los registros serán recuperados en el siguiente orden:

id name gender DOB total_score city
1 Jolly Female 1989-06-12 00:00:00.000 500 London
2 Jon Male 1974-02-02 00:00:00.000 545 Manchester
3 Sara Female 1988-03-07 00:00:00.000 600 Leeds
4 Laura Female 1981-12-22 00:00:00.000 400 Liverpool
5 Alan Male 1993-07-29 00:00:00.000 500 London
6 Kate Female 1985-01-03 00:00:00.000 500 Liverpool
7 Joseph Male 1982-04-09 00:00:00.000 643 London
8 Mice Male 1974-08-16 00:00:00.000 543 Liverpool
9 Wise Male 1987-11-11 00:00:00.000 499 Manchester
10 Elis Female 1990-10-28 00:00:00.000 400 Leeds

Creando índices Agrupados Personalizados

Usted puede crear su propio índice personalizado, así como el índice agrupado por defecto. Para crear un nuevo índice agrupado en una tabla, usted debe primero eliminar el índice previo

Para eliminar un índice vaya a “Object Explorer->Databases->Database_Name->Tables->Table_Name -> Indexes”. Haga clic derecho en el índice que desea eliminar y seleccione DELETE. Vea la siguiente captura de pantalla.

Ahora, para crear un nuevo índice agrupado, ejecute el siguiente script:

El proceso de crear un índice agrupado es similar a un índice normal con una excepción. Con un índice agrupado, usted tiene que usar la palabra reservada “CLUSTERED” antes de “INDEX”.

El script de arriba crea un índice llamado “IX_tblStudent_Gender_Score” en la tabla student. Este índice es creado en las columnas “gender” y “total_score”. Un índice que es creado en más que una columna es llamado “índice compuesto”.

El índice de arriba primero ordena todos los registros en orden ascendente de gender. Si gender es lo mismo para dos o más registros, los registros son ordenados en orden descendiente de los valores en su columna “total_score”. Usted puede crear un índice agrupado en una sola columna también. Ahora, si usted selecciona todos los registros desde la tabla student, ellos serán recuperados en el siguiente orden:

id name gender DOB total_score city
3 Sara Female 1988-03-07 00:00:00.000 600 Leeds
1 Jolly Female 1989-06-12 00:00:00.000 500 London
6 Kate Female 1985-01-03 00:00:00.000 500 Liverpool
4 Laura Female 1981-12-22 00:00:00.000 400 Liverpool
10 Elis Female 1990-10-28 00:00:00.000 400 Leeds
7 Joseph Male 1982-04-09 00:00:00.000 643 London
2 Jon Male 1974-02-02 00:00:00.000 545 Manchester
8 Mice Male 1974-08-16 00:00:00.000 543 Liverpool
5 Alan Male 1993-07-29 00:00:00.000 500 London
9 Wise Male 1987-11-11 00:00:00.000 499 Manchester

Índices no Agrupados

Un índice no agrupado no ordena los datos físicos dentro de la tabla. De hecho, un índice no agrupado es agrupado en un solo lugar y los datos de la tabla son almacenados en otro lugar. Esto es similar a un libro de texto donde el contenido del libro está localizado en un lugar y el índice está localizado en otro. Esto permite tener más de un índice no agrupado por tabla.

Es importante mencionar que dentro de la tabla los datos serán ordenados por un índice agrupado. De todos modos, por dentro los datos del índice no agrupado son almacenados en un orden específico. El índice contiene valores de columna en los cuales el índice es creado y la dirección del registro a la que el valor de la columna pertenece.

Cuando una consulta es lanzada contra una columna en la cual el índice es creado, la base de datos primero irá al índice y buscará la dirección de la fila correspondiente en la tabla. Luego, irá a esa dirección de fila y obtendrá otros valores de columna. Es debido a este paso adicional que los índices no agrupados son más lentos que los índices agrupados.

Creando un Índice No Agrupado

La sintaxis para crear un Índice no agrupado es similar a la del índice agrupado. De todos modos, en el caso de los índices no agrupados, la palabra reservada “NONCLUSTERED” es usada en lugar de “CLUSTERED”. Revise el siguiente script.

El siguiente script crea un índice no agrupado en la columna “name” de la tabla student. El índice ordena por name en orden ascendente. Como dijimos anteriormente, los datos de la tabla e índice serán almacenados en lugares diferentes. Los registros de la tabla serán ordenados por un índice agrupado si hay uno. El índice será ordenado de acuerdo a su definición y será almacenado separadamente desde la tabla.

Datos de la Tabla Student:

id name gender DOB total_score City
1 Jolly Female 1989-06-12 00:00:00.000 500 London
2 Jon Male 1974-02-02 00:00:00.000 545 Manchester
3 Sara Female 1988-03-07 00:00:00.000 600 Leeds
4 Laura Female 1981-12-22 00:00:00.000 400 Liverpool
5 Alan Male 1993-07-29 00:00:00.000 500 London
6 Kate Female 1985-01-03 00:00:00.000 500 Liverpool
7 Joseph Male 1982-04-09 00:00:00.000 643 London
8 Mice Male 1974-08-16 00:00:00.000 543 Liverpool
9 Wise Male 1987-11-11 00:00:00.000 499 Manchester
10 Elis Female 1990-10-28 00:00:00.000 400 Leeds

Datos del ÍndiceIX_tblStudent_Name

name Row Address
Alan Row Address
Elis Row Address
Jolly Row Address
Jon Row Address
Joseph Row Address
Kate Row Address
Laura Row Address
Mice Row Address
Sara Row Address
Wise Row Address

Note, aquí en el índice cada fila tiene una columna que almacena la dirección de la fila a la cual el nombre pertenece. Así que, si una consulta es lanzada para recuperar gender y DOB de un estudiante llamado “Jon”, la base de datos primero buscará el nombre “Jon” dentro del índice. Luego leerá la dirección de la fila de “Jon” e irá directamente a esa fila en la tabla “student” para recuperar gender y DOB de Jon.

Conclusión

De la discusión encontramos las siguientes diferencias entre índices agrupados y no agrupados.

  1. Puede haber sólo un índice agrupado por tabla. De todos modos, usted puede crear múltiples índices no agrupados en una sola tabla.
  2. Los índices agrupados sólo ordenan tablas. Por lo tanto, no consumen almacenaje extra. Los índices no agrupados son almacenados en un lugar separado de la tabla real. Reclamando más espacio de almacenamiento.
  3. Los índices agrupados son más rápidos que los índices no agrupados, ya que no involucran ningún paso extra de búsqueda.

Ver más

Para arreglar la fragmentación de índices SQL, considere ApexSQL Defrag – una herramienta de desfragmentación, mantenimiento, análisis y monitoreo de índices de SQL Server.

Enlaces Útiles


Ben Richardson

Ben Richardson

Ben Richardson dirige Acuity Training, un proveedor líder de entrenamiento SQL en el Reino Unido. Ofrece un amplio rango de entrenamiento SQL, desde cursos de introducción hasta entrenamiento acerca de administración avanzada y almacén de datos –vea aquí para más detalles. Acuity tiene oficinas en Londres y Guildford, Surrey. Él también escribe ocasionalmente el blog de Acuity.

Ver todas las entradas de Ben Richardson
Ben Richardson
11,888 Views