jump to navigation

BD2 – Introducción a los Índices 2 junio, 2012

Posted by AS in Apuntes bd2, Bases de Datos 2, General.
trackback

Fuente: http://www.sqlservercentral.com/articles/Indexing/68439/

Introducción a los Índices

Por Gail Shaw, 2011/01/07 (publicado:26/10/2009)

Unos buenos índices son la clave para un buen rendimiento en SQL Server y la clave para crearlos es entender lo que los índices son y cómo SQL Server los utiliza para evaluar consultas.

En esta primera parte de una serie de tres, vamos a ver los conceptos básicos de lo que los índices son, qué tipos existen en SQL y cómo se utilizan.

Que es un Índice?

Un índice es una estructura dentro de SQL Server que se utiliza para localizar rápidamente filas específicas de una tabla. Puede ser útil imaginar un índice en un libro  cuando se piensa acerca de los índices de SQL. Ambos tienen el mismo propósito – encontrar información específica rápidamente.

Estructura general

Un índice se define en una o más columnas, denominadas columnas de clave. Las columnas de clave (también conocida como la clave del índice) se puede comparar con los temas que figuran en el índice de un libro. Son los valores que el índice utilizará para buscar. Al igual que con el índice de encontrado en la parte posterior de un libro de texto (ver figura 1), el índice se ordenan por las columnas de clave.

Figura 1: Índice de un libro.

Si se crea un índice con más de una columna de clave, se lo conoce como un índice compuesto.
La estructura general de un índice es la de un árbol equilibrado. El índice tendrá una página sola raíz, cero o más niveles intermedios y un nivel de hoja. Una página es un trozo de 8 kilobytes del archivo de datos, con un encabezado y pie de página y se identifica por una combinación de ID de Archivo y el número de página.

Figura 2: Estructura de un Indice

Nota: Comúnmente la página raíz se muestra en la parte superior del diagrama de árbol y las páginas de hoja en la parte inferior. Piense en ello como un árbol invertido.
En el nivel de hoja, no hay una entrada para cada fila del índice(*). Las entradas en el índice están ordenados lógicamente(**) en el orden de la clave de índice.
Los niveles que no son hojas contiene una fila por cada página del nivel inferior, que hace referencia al valor del índice más bajo en cada página. Si todas las filas caben en una sola página, la pagina se considera considera la raíz y el índice es de sólo dos niveles de profundidad. Si todas esas filas no caben en una sola página, a continuación, uno (o más) niveles intermedios se agregan al índice.
El número de niveles en un índice se refiere como la profundidad del índice. Esta es una consideración importante para evaluar la eficiencia del índice. El índice se ilustra en la figura 2 tiene una profundidad de 3.
(*) Con la excepción de los índices filtrados SQL Server 2008, un índice tendrá el mismo número de filas en el nivel de hoja como la tabla.
(**) Estoy usando la frase “ordenado de lógicamente ” porque el índice no define necesariamente el almacenamiento físico de las filas. Las filas se almacenan en una forma que SQL puede recuperarlas ordenadas.

Cluster  y no-clúster

Hay dos tipos principales de índices en SQL Server, el clúster y el índice no-clúster.
Los índices clúster definen el orden lógico de la tabla. El nivel hoja del índice agrupado tiene las páginas de datos reales de la tabla. Debido a esto, sólo puede haber un índice agrupado por tabla. Una tabla que no tiene un índice agrupado se la llama heap.
Los índices no-clúster son independientes de la tabla. El nivel de la hoja de un índice no-clúster tiene un puntero como parte de cada fila de índice. Ese puntero es o bien la clave del índice agrupado en los casos en que la tabla base tiene un índice agrupado o el RID (identificador de fila) en los casos en que la tabla es un heap. El RID es una estructura de 8 bytes que contiene  la identificación del archivo, número de página y número de slot y identificará unívocamente una fila en el heap subyacente. De cualquier manera, cada fila de  índice no-clúster  tiene una referencia a la fila de datos completa.

Limites de los Índices

Hay un número de limitaciones inherentes en los índices:

Tamaño de la clave

El tamaño de una clave de índice se limita a un máximo de 900 bytes y un máximo de 16 columnas. Esto es definitivamente un límite, no una virtud, ya que cuanto mayor sea la clave de índice ,más páginas hay en el índice y más profundo es el árbol del índice. Como el número de páginas y la profundidad del árbol aumenta por lo que el índice se vuelve menos eficiente de usar. Mayores índices también utilizan más espacio de almacenamiento y el resultado en un uso menos eficiente de la caché de SQL de datos.

Número de índices

En SQL Server 2005 y versiones anteriores había una limitación de 250 índices por tabla, uno clúster y 249 no clúster. En SQL Server 2008, con la adición de los índices filtrados,  la limitación se incrementó a 1000, uno de clúster y 999 índices no clúster.
Ambos límites son muy altos y hay pocas circunstancias en que un sistema bien diseñado debe acercarse a ese límite.

La razón de esto es:
• A medida que el número de índices aumenta por lo que el tamaño total ocupado por la tabla (con todos sus índices) aumenta. Claro, los discos duros son baratos y el almacenamiento es abundante, pero el aumento del tamaño de una base de datos tiene otros efectos. Operaciones de mantenimiento (copias de seguridad, controles de consistencia y las reconstrucciones del índice), todos toman más tiempo que el tamaño de una base de datos aumenta.
• Los índices tienen que actualizarse en cada cambios de datos y cuanto más índices hay en la tabla, más información tiene que ser cambiada. Si hay 10 índices no-cluster  en una tabla, una inserción debe hacerse en 11 lugares (la tabla y cada uno de esos índices no-cluster). En bases de datos que en su mayoría son de sólo lectura  (apoyo a las decisiones, almacenes de datos) que puede ser aceptable. En las bases de datos que tienen inserciones, actualizaciones frecuentes y eliminaciones (sistemas OLTP), la sobrecarga de imponer por varios índices, no puede ser aceptable

Como utiliza SQL los índices.

Si una tabla no tiene índice, la única manera de encontrar todas las ocurrencias de un valor dentro de una tabla es leer toda la tabla. Si una tabla tiene un índice, que acelera la localización de los valores dentro de dicho índice de dos maneras.
1. El índice se ordenan en el orden de las columnas de clave. Esto significa que una vez que todos los valores para los productos se han encontrado, la porción restante de la tabla puede ser ignorada. Esto es lo mismo que un directorio telefónico, al encontrar un apellido determinado , el resto del libro se puede ignorar, ya que no son posibles más coincidencias.
2. La estructura de árbol del índice permite que un divide y triunfarás a las filas de búsqueda, donde una gran parte de la tabla puede ser rápidamente excluida de la búsqueda.

Hay cuatro operaciones básicas que SQL puede hacer en un índice. Es capaz de analizar el índice, puede buscar en el índice, se puede realizar búsquedas en el índice y se puede actualizar el índice.

Analizar un índice (Scan)

Una exploración de índice es una lectura completa de todas las páginas hoja del índice. Cuando un índice de exploración se lleva a cabo en el índice clúster, es un recorrido de toda la tabla menos los nombres,  pero
Cuando una exploración de índice de se lleva a cabo por el procesador de consultas, es siempre una lectura completa de todas las páginas de hoja en el índice, independientemente de si todas las filas se devuelven. Nunca es un análisis parcial.
Un examen no sólo implica la lectura de los niveles hoja del índice, las páginas de nivel superior también se leen como parte de la exploración de índice.

Buscar un índice (Seek)

Una búsqueda de índice es una operación en la que SQL utiliza la estructura de árbol para localizar un valor específico o el comienzo de un intervalo de valor. Para que se pueda buscar un índice, debe haber un predicado SARGable(***) especificado en la consulta y un índice coincidente (o parcialmente coincidente). Un índice coincidente es una donde el predicado de la consulta utiliza un subconjunto de la izquierda basada en las columnas de índice.
La operación de búsqueda se evalúa a partir de la página raíz. Usando las filas de la página raíz, el procesador de consultas encontrará la página en el siguiente nivel inferior del índice contiene la 1 ª fila que está siendo buscada.

A continuación, lee la página. Si ese es el nivel hoja del índice, la búsqueda termina aquí. Si no es la hoja a continuación, el procesador de consulta de nuevo verifica en la página en el siguiente nivel inferior contiene el valor especificado. Este proceso continúa hasta que el nivel de hoja se alcanza.
Una vez que el procesador de consultas ha localizado la página de la hoja que contiene el valor especificado en el rango de valores clave, entonces lee a lo largo de las páginas de la hoja hasta que todas las filas que coinciden con el predicado han sido devueltas. La figura 2 muestra cómo se llevaría a cabo de búsqueda en un índice cuando se busca el valor de 4.

Si el índice contiene todas las columnas que necesita de consulta, entonces el índice se dice que está cubriendo la consulta. Si el índice no contiene todas las columnas a continuación, SQL va a hacer una búsqueda en la tabla de base en busca de las otras columnas con el fin de procesar la consulta.

(***) SARGable es una palabra inventada, construida a partir del argumento de (Search ARGument). Se refiere a un predicado que es de una forma que SQL Server puede utilizar para una búsqueda de índice. Para más detalles vea: http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx

Búsquedas en el índice (Lookups)

Las búsquedas se producen cuando SQL utiliza un índice para localizar filas afectadas por una consulta, pero que el índice no contiene todas las columnas necesarias para satisfacer la consulta, que es el índice no está cubriendo de esa consulta. Para obtener las columnas restantes, SQL Server realiza una búsqueda ya sea el índice agrupado o montón.
Una búsqueda de un índice agrupado es esencialmente un índice clúster y es siempre una búsqueda de una fila.  Así que si una búsqueda  se necesita para 500 filas, esta involucra 500 búsquedas individuales del índice clúster.

Actualizar el índice (Updates)

Siempre que se cambia una fila, los cambios se deben realizar no sólo en la tabla base(índice clúster o heap), sino también en cualquier otro índice que contiene las columnas que fueron afectados por el cambio. Esto se aplica para operaciones  insertar, actualizar y eliminar.

Consideraciones para crear índices

•Los  índices clúster debe ser reducidos, porque la clave de agrupación es parte de todos los índices no clúster.
• Los índices  compuestos no-clúster  son generalmente más útiles que los índices de una sola columna, a menos que todas las consultas a la tabla filtren una columna a la vez.
• Los índices no debe ser mayor de lo que tiene que ser. Demasiadas columnas desperdicia espacio y aumenta la cantidad de lugares que los datos deben ser cambiados cuando una inserción / actualización / eliminación se produce.
• Si hay un índice único, especificar que es único. El optimizador a veces puede usar esa información para generar planes de ejecución más óptimos.
• Tenga cuidado de la creación de un montón de índices en tablas modificadas con frecuencia, ya que puede frenar las modificaciones de datos.

Nota Importante

Se recomienda leer tambien los articulos referidos a INDICES de nuestra Base de Conocimientos

A %d blogueros les gusta esto: