• Author / Uploaded
• Ale Pin

Citation preview

17

BASES DE DATOS

INDICES Un índice es una estructura almacenada asociada con una tabla o una vista que acelera la recuperación de filas de la tabla o de la vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista. Dichas claves están almacenadas en una estructura (árbol B) que permite que SQL Server busque de forma rápida y eficiente la fila o filas asociadas a los valores de cada clave. Una tabla o una vista puede contener como los más importantes, los siguientes tipos de índices: Los Clustered Indexes son índices que controlan el orden físico de las filas en la tabla, por lo cual solo puede existir uno para cada tabla. Los NonClustered indexes son índices que mantienen un sub conjunto de las columnas de la tabla en orden. Estos índices no modifican el orden de las filas de la tabla, en lugar de esto mantienen una lista ordenada de referencias a filas de la tabla original. Para ilustrar la diferencia entre estos 2 tipos de índices podemos decir que las páginas blancas de la guía telefónica tienen un clustered index por Apellido y Nombre, con lo cual puedo buscar de forma muy eficiente el número de teléfono de una persona si conozco sus apellidos y su nombre, una vez que lo encuentro obtendré su número de teléfono en forma inmediata pues el numero está al lado del nombre. En el caso de las páginas amarillas de la guía telefónica la forma de buscar es un poco distinta, en este caso busco por categoría. Primero busco en un índice, el cual me indica en qué página se encuentra la lista de empresas que satisfacen la condición que busco. Esto mismo es lo que pasa cuando utilizo un índice NonClustered; una vez que encuentro lo que quiero en el índice debo ir a leer la fila específica para obtener el resto de los datos.

INDICE AGRUPADO (CLUSTERED) Se refiere a una relación de la posición física de la fila de datos de la tabla en las páginas de almacenamiento. Sólo puede haber un índice agrupado por cada tabla, porque las filas de datos sólo pueden estar ordenadas de una forma (desde el punto de vista físico). o Por ejemplo: los libros en una estantería solo pueden ser físicamente dispuestos en un orden: por tamaño, por autor, por tema… o

Bases de Datos

Ing. Rosa Navarrete

17 Los índices agrupados ordenan y almacenan las filas de los datos de la tabla o vista de acuerdo con los valores de la clave del índice. Son columnas incluidas en la definición del índice. o La única ocasión en la que las filas de datos de una tabla están ordenadas es cuando la tabla contiene un índice agrupado (tabla agrupada). Si una tabla no tiene un índice agrupado, sus filas de datos están almacenadas en una estructura sin ordenar denominada montón. o

INDICES NO AGRUPADOS (NONCLUSTERED) Los índices no agrupados tienen una estructura separada de las filas de datos. Un índice no agrupado contiene los valores de clave de índice no agrupado y cada entrada de valor de clave tiene un puntero a la fila de datos que contiene el valor clave. Por ejemplo: imagine que usted escribe distintas formas de ordenamiento de los libros ubicados en la estantería; por autor, por título, etc. Cada forma de ordenamiento sería un índice no agrupado. o El puntero de una fila de índice no agrupado hacia 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. Si están en un montón, el localizador de filas es un puntero hacia la fila. Si están en una tabla agrupada, el localizador de fila es la clave de índice agrupada. o

Tanto los índices agrupados como los no agrupados pueden ser únicos. Esto significa que (dos filas no pueden tener el mismo valor para la clave de índice; de lo contrario, el índice no es único y varias filas pueden compartir el mismo valor de clave). Los índices se crean automáticamente cuando las restricciones PRIMARY KEY y UNIQUE se definen en las columnas de tabla.

DIRECTRICES PARA DISEÑAR INDICES CLUSTERED Por regla general, debe definir la clave de índice clúster con el menor número de columnas posible. Considere columnas que cuentan con uno o varios de los siguientes atributos: Son únicas o contienen muchos valores distintos Por ejemplo, un Id. de empleado identifica de forma exclusiva a los empleados. Un índice clúster o una restricción PRIMARY KEY en la columna EmployeeID mejoraría el rendimiento de las consultas que buscan información del empleado basándose en el número de Id. del empleado. También se podría crear un índice clúster en las columnas LastName y FirstName, ya que los registros de empleados se agrupan y se consultan con frecuencia de esta forma y la

Bases de Datos

Ing. Rosa Navarrete

17 combinación de estas columnas seguiría proporcionando un alto grado de diferencia. Se tiene acceso a ellas de forma secuencial Por ejemplo, un Id. de producto identifica de forma exclusiva los productos; un índice clúster en ProductID mejoraría las consultas donde se especifica una búsqueda secuencial, como WHERE ProductID BETWEEN 980 and 999. Esto se debe a que las filas se almacenan de forma ordenada en esta columna de clave. Se definen como IDENTITY, ya que la columna va a ser única en la tabla Se utilizan con frecuencia para ordenar los datos recuperados de una tabla Puede resultar conveniente agrupar, es decir, ordenar físicamente la tabla de dicha columna para evitar una operación de ordenación cada vez que se consulta la columna. Los índices agrupados no se recomiendan en: Columnas sometidas a cambios frecuentes Esto provoca que se mueva toda la fila, ya que el motor de base de datos debe mantener los valores de los datos de la fila ordenados físicamente. Esta consideración es importante en sistemas de procesamiento de transacciones de gran volumen en los que los datos tienden a ser volátiles. Claves amplias Las claves amplias se componen de varias columnas o varias columnas de gran tamaño. Los valores clave del índice clúster se utilizan en todos los índices no agrupados como claves de búsqueda. Los índices no agrupados definidos en la misma tabla serán bastante más grandes, ya que sus entradas contienen la clave de agrupación en clústeres y las columnas de clave definidas para dicho índice no agrupado

DIRECTRICES PARA DISEÑAR INDICES NONCLUSTERED Un índice no agrupado contiene los valores de clave del índice y localizadores de fila que apuntan a la ubicación de almacenamiento de los datos de tabla. Tenga en cuenta las columnas que tengan uno o varios de estos atributos: • Cubren la consulta.

Se obtienen mejoras de rendimiento cuando el índice contiene todas las columnas de la consulta. El optimizador de consultas puede buscar todos los valores de columna del índice; no se tiene acceso a los datos de tabla o

Bases de Datos

Ing. Rosa Navarrete

17 de índice clúster, lo que se traduce en menos operaciones de E/S de disco. Utilice un índice con columnas incluidas para agregar columnas de cobertura en lugar de crear una clave de índice ancho. • Gran número de valores distintos, como combinaciones de nombres y

apellidos, si se utiliza un índice clúster para otras columnas. • Se utilizan en cláusulas JOIN o GROUP BY.

Crean varios índices no agrupados para las columnas que intervienen en operaciones de combinación y de agrupación, y un índice clúster para las columnas de clave externa. • No devuelven conjuntos de resultados de gran tamaño.

Cree índices filtrados para atender consultas que subconjunto bien definido de filas en una tabla grande.

devuelven

un

• Contienen columnas que suelen incluirse en las condiciones de búsqueda

de una consulta, como la cláusula WHERE, que devuelven coincidencias exactas.

USO DE ÍNDICES Sin lugar a dudas, el desempeño de una aplicación está directamente relacionado al buen o mal diseño de los índices de la base de datos. Para demostrar esto tomemos como ejemplo la tabla de usuarios de un sistema cualquiera, la cual tiene la siguiente estructura:

Esta tabla no tiene ningún índice creado, por lo cual SQL Server tratará la tabla como un HEAP (una estructura de datos que almacena la posición física en la que se almacenó cada nueva fila dentro de las páginas asignadas a la tabla). Puesto que esta tabla no tiene ningún tipo de índice, es bastante eficiente para agregar nuevas filas a la tabla pero muy ineficiente para encontrar una fila específica, esto se debe a que es necesario leer toda la tabla para obtener el resultado deseado. Para ilustrar esto, realicemos lo siguiente:

Bases de Datos

Ing. Rosa Navarrete

17 Creamos una base de datos de trabajo, llamada bdusuarios: use master go SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create database bdusuarios go use bdusuarios go /* ============================================= Store Procedure para crear registros en la tabla Usuarios para pruebas Utiliza 'SHA1' como algoritmo hash para realizar el hash de la entrada Se probará para insertar 10.000 registros utilizando el procedimiento almacenado CrearUsuarios, los usuarios no están activos y su fecha de creación está en los últimos 600 días. ============================================= */ drop procedure [dbo].[CrearUsuarios] go CREATE PROCEDURE [dbo].[CrearUsuarios] @n int, @nInactivos int, @nDias int AS BEGIN SET NOCOUNT ON; DECLARE @x INT = 0 DECLARE @name varchar(20) DECLARE @pass varchar(30) DECLARE @fecha datetime DECLARE @activo bit DELETE FROM Usuarios WHILE (@x < @n) BEGIN SET @name = 'USUARIO' + CAST(@x as varchar) SET @pass = 'PASS' + CAST(@x as varchar)

Bases de Datos

Ing. Rosa Navarrete

17 SET @activo = (@x % (@n/@nInactivos)) SET @fecha = DATEADD(DAY,(@n-@X)/@nDias,GETDATE()) INSERT

INTO

Usuarios

(Username,

Password,

FechaCreacion,

Activo) VALUES (@name, HASHBYTES('SHA1', 'RN123' + @pass), @fecha, @activo) SET @x = @x + 1 END END GO exec sp_helptext CrearUsuarios exec CrearUsuarios 100000, 10000, 600

Ahora realicemos una consulta para validar el usuario al inicio de sesión del sistema. SELECT Password, Activo FROM Usuarios WHERE Username = 'Usuario123' La respuesta probablemente funcione bastante rápido, ya que al recién haber creado los datos, todas estas filas están en memoria, pero veamos el plan de ejecución de esta consulta presionando el botón (Incluir plan de ejecución real) de la barra de herramientas del SQL Server Management Studio y luego ejecutando la misma consulta.

Al pasar el mouse sobre el primer elemento de la izquierda se puede observar el costo de la consulta.

Bases de Datos

Ing. Rosa Navarrete

17

El costo estimado de la consulta es de 0.648838 y la forma de resolverlo fue un Table Scan, lo que implica leer toda la tabla. Para obtener más información sobre el acceso a los datos requeridos para resolver la consulta ejecutamos lo siguiente, antes de la consulta: CHECKPOINT DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS SET STATISTICS IO ON SELECT Password, Activo FROM Usuarios WHERE Username = 'Usuario123' SET STATISTICS IO OFF Lo primero que pasa al ejecutar esta consulta es que SQL Server escribe todos los cambios pendientes al disco, la segunda instrucción elimina todos los datos que tiene en memoria. La combinación de estas dos instrucciones obliga a SQL Server a leer todo desde disco nuevamente, es importante tener en cuenta que al ejecutar DBCC DropCleanBuffers se produce un fuerte impacto en el desempeño de todos los usuarios, por lo que no se debe utilizar en servidores de producción. La tercera instrucción nos permite obtener información sobre las lecturas de datos requeridas para contestar una consulta. El resultado que se obtiene en el área de mensajes es:

Bases de Datos

Ing. Rosa Navarrete

17 (1 filas afectadas) Tabla 'Usuarios'. Recuento de exámenes 1, lecturas lógicas 724, lecturas físicas 8, lecturas anticipadas 728, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. (1 filas afectadas) Esto nos dice que se accedió a la tabla “Usuarios”, se hizo una lectura completa de la tabla 1 vez, lo que equivale a 724 páginas leídas, como cada página pesa 8Kb, 724 * 8kb es igual a 5792 Kb, casi 6 Mb, precisamente lo mismo que pesa la tabla completa. Las lecturas físicas son las páginas que no se encontraban en memoria cuando SQL Server las necesitaba, en este caso fueron 8, algo extraño ya que vaciamos el cache antes de ejecutar la consulta por lo cual no debería existir ninguna página en memoria, pero SQL Server es más inteligente y cada vez que tiene que leer páginas desde el disco, lo cual es una operación muy costosa, lee también otras páginas cercanas que puedan ser útiles para responder la consulta que solicitó la lectura. En este caso SQL Server fue capaz de Leer 728 páginas utilizando este mecanismo de lecturas anticipadas (read-ahead). Las “lob” o Large Object Block, son operaciones relacionadas a datos que no se guardan junto con la fila como los campos de tipo TEXT, IMAGE y los tipos de datos que sobrepasen los 8000 bytes de la página. En este caso no hay operaciones de este tipo. UTILIZANDO INDICES Para mejorar el desempeño de las consultas se utilizan índices. Veamos qué pasa cuando agregamos un índice a la columna Username de la tabla Usuarios que creamos. El índice será Non-Clustered y Único puesto que no podemos tener más de un usuario con el mismo nombre. create unique nonclustered index index_username on Usuarios (Username) Volvemos a ejecutar la misma consulta: SELECT Password, Activo FROM Usuarios WHERE Username = 'Usuario123'

Bases de Datos

Ing. Rosa Navarrete

17

El plan de ejecución es un poco más complicado pero esta consulta es 100 veces menos costosa que la anterior. (anterior: 0.648838, actual: 0,0065704). Ahora la consulta utiliza el índice para encontrar el RID, que es el identificador de la fila, con este RID hace una búsqueda en la tabla de tipo heap (RID Lookup). Volviendo a utilizar: CHECKPOINT DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS SET STATISTICS IO ON SELECT Password, Activo FROM Usuarios WHERE Username = 'Usuario123' SET STATISTICS IO OFF Se obtiene: (1 filas afectadas) Tabla 'Usuarios'. Recuento de exámenes 0, lecturas lógicas 4, lecturas físicas 4, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. (1 filas afectadas)

Bases de Datos

Ing. Rosa Navarrete

17 Esta reducción de costo se explica por la cantidad de accesos a datos que esta consulta requiere, puesto que estos bajan desde 724 a solo 4 páginas lo que equivale 32 kb de datos contra 5792 kb, que eran necesarios antes de la creación del índice. Veamos otra alternativa, si utilizamos un clustered index en lugar de un noncluster index no será necesario el lookup, por lo que el acceso será más eficiente. -- Borramos el índice nonclustered drop index index_username on Usuarios -- Creamos el índice clustered create unique clustered index index_username on Usuarios (Username)

Y el acceso se ve así: CHECKPOINT DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS SET STATISTICS IO ON SELECT Password, Activo FROM Usuarios WHERE Username = 'Usuario123' SET STATISTICS IO OFF

Bases de Datos

Ing. Rosa Navarrete

17 (1 filas afectadas) Tabla 'Usuarios'. Recuento de exámenes 0, lecturas lógicas 3, lecturas físicas 2, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. (1 filas afectadas) Como se puede ver el costo de esta consulta ahora es menor, casi a la mitad que con el índice non-clustered y en lugar de 4 lecturas lógicas tenemos sólo 3. Es importante destacar que según Comparing Tables Organized with Clustered Indexes versus Heaps, es siempre recomendable utilizar tablas que utilicen un Cluster index en lugar de tablas que solo utilicen índices non-cluster.

COLUMNAS INCLUIDAS A partir del SQL Server 2005 se agregó una nueva funcionalidad a los índices non-clustered llamada columnas incluidas; estas columnas no son parte de la llave índice, por lo que no se mantienen ordenadas dentro del índice y como consecuencia de esto solo es necesario almacenar su valor en el nodo hoja del índice. Las columnas incluidas tienen varias ventajas: • •

•

Al utilizar columnas incluidas en el índice es posible superar la limitación de los 900 bytes para la llave del índice, manteniendo un índice eficiente. Las modificaciones al valor de una columna incluida es más eficiente que la modificación de una columna que es parte de la llave del índice pues estas no requieren ser mantenidas en orden. Es posible crear Covering Indexes, que son índices que incluyen todas las columnas requeridas para contestar una consulta específica, que no requieren la búsqueda sobre el clustered index. Este tipo de índice es igual o más eficiente que u índice cluster.

ÍNDICES FILTRADOS El SQL Server 2008 agregó otra mejora a los índices non-clustered llamada “Índices Filtrados”. Estos índices mantienen ordenados un sub conjunto de las filas de la tabla. El uso más común de este tipo de índices se da cuando queremos crear un índice sobre una columna que permite valores nulos. Al crear un índice normal podemos desperdiciar una gran parte del espacio de índice ordenando filas que tienen un valor nulo. A continuación se describen algunos ejemplos de casos de uso para estos índices filtrados:

Bases de Datos

Ing. Rosa Navarrete

17 • •

La columna “fecha de término de contrato” de la tabla empleado contiene una gran cantidad de filas con el valor nulo. Los estados intermedios son buenos candidatos para ser índices filtrados. Es posible crear un índice que solo contenga las ordenes de compra recibidas y en proceso, pero no las despachadas.

EJEMPLO

DE USO DE ÍNDICES

La forma más sencilla de ver la diferencia que puede provocar el uso de índices es crear una tabla simple y ver el plan de ejecución de consultas, en ambos casos (con y sin el índice). Comencemos creando la tabla y agregando algunos valores: CREATE TABLE [dbo].[Datos1]( [ID] [int] NOT NULL, [Numero] [int]NOT NULL, [Descripcion] [nvarchar](50) NOT NULL, ) INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) INSERT INTO Datos1 ([ID],[Numero],[Descripcion])

VALUES VALUES VALUES VALUES

(1,1,'D1') (2,2,'D2') (3,3,'D3') (4,4,'D4')

Luego iniciaremos una búsqueda y veremos el plan de ejecución. El plan de ejecución mostrará de qué manera el query optimizer intentará acceder a los datos durante una consulta, (El query optimizer es el encargado de diseñar la estrategia del acceso a los datos). Existen varias maneras de ver el plan de ejecución, utilizaremos en estos ejemplos la forma grafica. Luego de haber ejecutado el script previo deberemos escribir lo siguiente en un query analizer: SELECT [ID], [Numero], [Descripcion] FROM Datos1 WHERE ID=1

Y luego presionar CTRL+L. Se obtendrá un resultado similar a lo siguiente:

Bases de Datos

Ing. Rosa Navarrete

17

Los planes de ejecución en formato gráfico deben leerse de izquierda a derecha y de arriba hacia abajo, y aunque pueden ser extremadamente largos y complejos de leer, en este caso podemos ver el mismo está compuesto por solamente dos iconos y una flecha que los une a ambos. Cada icono representará una operación y la flecha simbolizará el movimiento de datos entre las dos operaciones, indicándonos que la operación “Table Scan” ha tomado los datos que la operación SELECT procesará, en realidad la operación SELECT no ha hecho nada en este caso. Este diagrama nos indica que está haciendo internamente el motor de base de datos. Una operación “Table Scan” nos está indicando que el motor ha necesitado recorrer secuencialmente la tabla Datos1 para poder encontrar los registros que cumplan con la condición pedida. La operación “Table Scan” es equivalente a tener un diccionario desordenado donde es necesario recorrerlo secuencialmente hasta encontrar la palabra que deseamos buscar, pero además la palabra puede existir más de una vez, así que siempre deberemos recorrerlo hasta la última palabra para asegurarnos que hemos encontrado todas las definiciones. Cuando no hay índices creados la performance de las búsquedas quedan gravemente comprometidas. En contraposición crearemos un índice y veremos que cambios se producen en el plan de ejecución, ejecutaremos la siguiente línea de código: CREATE CLUSTERED INDEX IX_1

ON [dbo].[Datos1] (ID)

Donde hemos indicado la creación de un índice por la columna “ID”,(la palabra CLUSTERED indicará que la tabla se ordenará físicamente por el índice solicitado, luego veremos que existe otro tipo de índices que no impone tal condición.) Si volvemos a ejecutar la consulta anterior, el plan de ejecución tomará el siguiente formato:

Indicando que en este caso la búsqueda de datos está utilizando el índice IX_1, de manera que el motor ya no debe recorrer toda la tabla para encontrar los

Bases de Datos

Ing. Rosa Navarrete

17 registros pedidos. Podemos ahora preguntarnos que pasaría si además es necesario realizar búsquedas por otro campo, supongamos por el campo “Numero”, en este caso no podremos reordenar la tabla físicamente por “Numero”, ya que al hacer esto perderíamos el orden físico que ya habíamos establecido por el campo “ID”, es claro que el orden físico puede establecerse solo para una clave (ya sea compuesta por un solo o varios campos). Para estos casos existen otro tipo de índices conocidos como índices non-clustered, ya que no modifican el orden físico de los registros en la tabla original, estos índices guardarán en otra estructura una copia de los valores involucrados en la clave y un puntero al registro original de la tabla. Para probar lo antes comentado ejecutaremos el siguiente comando: CREATE INDEX IX_2

ON [dbo].[Datos1] (Numero)

Y luego veremos el plan de la siguiente búsqueda:

Donde puede verse que el query optimizer ha decidido utilizar el nuevo índice IX_2. En este último query solo estamos incluyendo a la columna “Numero”. Si realizara la misma búsqueda pero esta vez con todos los campos, comprobará que el query optimizer habrá decidió utilizar el índice IX_1, y no IX_2; esto debido a que como se estableció previamente, los índices non-clustered guardan una copia de las claves y un puntero al registro original, de esta manera cuando hemos buscado solamente por “Numero” el índice IX_2 es capaz de devolver la información solicitada ya que posee el valor de la columna “Numero”, pero cuando hemos pedido otros datos como “ID” y “Descripcion” que no existen en IX_2 el query optimizer ha decidido que es menos costoso recorrer la tabla por IX_1 para devolver los datos que IX_2 no posee. Cuando un índice non-clustered cubre todos los datos solicitados en la consulta se dice que es un covered-index, el caso contrario no será un covered-index y el query optimizer deberá buscar Bases de Datos

Ing. Rosa Navarrete

17

alguna estrategia para obtener los datos faltantes, obviamente los clustered index son siempre covered index, ya que poseen el registro completo. El query optimizer puede utilizar otras estrategias para obtener los datos faltantes como veremos a continuación. Si ejecutamos el siguiente código: DELETE FROM Datos1 DECLARE @C int =1 WHILE @C < 10000 BEGIN INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) VALUES (@C,@C + 1,'D1' + cast(@C as nvarchar(10))) SET @C+=1 END

Donde solamente hemos agregado más datos y volvemos a ejecutar la consulta anterior veremos lo siguiente:

Ahora el query optimizer ha utilizado nuestro índice IX_2 pero para recuperar los datos faltantes a requerido efectuar una operación de Key Lookup extra utilizando el índice IX_1, para finalmente unir los datos en la operación Nested Loops. Si creamos un nuevo índice que cubra todos los datos pedidos de la siguiente forma: CREATE INDEX IX_3

ON [dbo].[Datos1] (Numero,ID,Descripcion)

No debería sorprendernos el siguiente resultado:

Bases de Datos

Ing. Rosa Navarrete

17

Otra opción para incluir las columnas restantes es utilizar la sentencia INCLUDE de la siguiente forma: CREATE INDEX IX_3 ON [dbo].[Datos1] (Numero)

INCLUDE (Descripcion, ID)

En el segundo caso, las columnas son agregadas al índice pero no forman parte del mismo. En ambos tipos de índices, clustered o non-clustered existe la posibilidad de definirlos como únicos (unique), un índice único no admite repetición de valores, y permite una mayor optimización en las búsquedas. Las claves primarias de las tablas están compuestas por índices “unique” que pueden ser o no clustered. En Sql Server 2008 existe además la posibilidad de crear índices filtrados, o sea índices que se aplican solo a un grupo de datos. Para probarlo podemos eliminar los índices IX_2 e IX_3 y crear un nuevo índice IX_4 filtrado, las siguientes líneas de código efectuan estas operaciones: DROP INDEX IX_2 ON [dbo].[Datos1] DROP INDEX IX_3 ON [dbo].[Datos1] CREATE INDEX IX_4 ON [dbo].[Datos1] (Numero,ID,Descripcion) WHERE Numero < 100

De esta forma el índice IX_4 será aplicable para algunas condiciones solamente, por ejemplo si ejecutamos el siguiente query:

Bases de Datos

Ing. Rosa Navarrete

17

El query optimizer ha decidido emplear IX_4 mientras que en el caso de:

Ha optado por IX_1.

Un índice es una estructura de la tabla utilizada por SQL SERVER para proveer un rápido acceso a los registros de la tabla, basado en valores de una o más columnas. Contiene valores de datos y punteros a los registros donde estos valores aparecen. VENTAJAS DE INDEXAR: • • • • •

Fuerza la unicidad de registros Acelera los join Asegura el chequeo de integridad referencial Acelera la recuperación de datos esparcidos Acelera las operaciones de GROUP BY y ORDER BY

DESVENTAJAS DE INDEXAR : •

Tiempo invertido en la creación de los índices

Bases de Datos

Ing. Rosa Navarrete

17 • •

Toma espacio en el disco, se necesita espacio para cada índice que se crea Las modificaciones de datos pueden tomar más tiempo; insert, update, delete alteran los índices.

CUANDO SE RECOMIENDA INDEXAR: • • • •

• •

Las claves primarias deben ser indexadas (por default) Crear un índice en cada columna sobre la que se necesite realizar búsquedas frecuentemente. Las claves foráneas también deben ser indexadas, para ayudar a encontrar la información más eficientemente. Una columna que se accesa frecuentemente para ordenamiento, debe tener un índice clustered para que SQLSERVER tome ventaja del ordenamiento del índice Las columnas que se utilizan regularmente en los JOIN, deben ser indexadas para acelerar la juntura. Una columna que a menudo es considerada para rangos de valores.

CUANDO NO ES RECOMENDABLE INDEXAR: • • • •

Cuando una columna se referencia muy poco en las consultas Cuando una columna contiene pocos valores únicos. Cuando una columna contiene texto, imágenes o campos bit. Cuando el rendimiento en UPDATE es más importante que el rendimiento en SELECT.

TIPOS DE INDICES: UNIQUE Crea un índice con entradas de valor único. No admite valores duplicados y los verifica en insert, update, delete. CLUSTERED Crea un índice agrupado en el mismo orden físico de las filas en el almacenamiento. Solo un índice por tabla puede declararse como CLUSTERED. NONCLUSTERED Crea un índice que representa un orden lógico de las tablas. Pueden existir hasta 249 índices NONCLUSTERED por tabla.

Bases de Datos

Ing. Rosa Navarrete