4.3 Índices, reorganización y reconstrucción.

4.3 Índices, reorganización y reconstrucción

En MySQL se tienen dos tipos de índices, los cuales son:

Índices agrupados.

Los índices agrupados, definen el orden en que almacenan las filas de la tabla (nodos hoja/página de datos de la imagen anterior). La clave del índice agrupado es el elemento clave para esta ordenación; el índice agrupado se implementa como una estructura de árbol b que ayuda a que la recuperación de las filas a partir de los valores de las claves del índice agrupado sea más rápida. Las páginas de cada nivel del índice, incluidas las páginas de datos del nivel hoja, se vinculan en una lista con vínculos dobles. Además, el desplazamiento de un nivel a otro se produce recorriendo los valores de claves.

Consideraciones para usar índices agrupados:

Ø  Columnas selectivas.

Ø  Columnas afectadas en consultas.

Ø  Columnas accedidas "secuencialmente".

Ø  Columnas implicadas en JOIN, GROUP BY.

Ø  Acceso muy rápido a filas: lookups.

 

Índices No Agrupados.

Los índices no agrupados tienen la misma estructura de árbol b que los índices agrupados, con algunos matices; como hemos visto antes, en los índices agrupados, en el último nivel del índice (nivel de hoja) están los datos; en los índices no-agrupados, en el nivel de hoja del índice, hay un puntero a la localización física de la fila correspondiente en el índice agrupado. Además, la ordenación de las filas del índice está construida en base a la(s) columna(s) indexadas, lo cual no quiere decir (a diferencia de los índices agrupados), que la organización física de las páginas de datos corresponda con el índice.

 

Reorganización de índices.

Un paquete puede usar la tarea Reorganizar índice para reorganizar los índices de una base de datos individual o de varias bases de datos. Si la tarea solo reorganiza los índices de una base de datos individual, puede elegir las vistas o las tablas cuyos índices reorganiza la tarea. La tarea Reorganizar índice también incluye la opción de compactar datos de objetos grandes. Los datos de objetos grandes son datos de tipo image, text, ntext, varchar(max), nvarchar(max), varbinary(max) o xml.

La tarea Reorganizar índice encapsula la instrucción ALTER INDEX de Transact-SQL. Si elige compactar datos de objetos grandes, la instrucción utiliza la cláusula REORGANIZE WITH (LOB_COMPACTION = ON); en caso contrario, se establece LOB_COMPACTION en OFF

Dentro de las tareas habituales de Mantenimiento de las Bases de Datos se encuentran aquellas destinadas al control y respaldo de las mismas como ser: Control de Integridad, Chequeo de Consistencia, Copias de Seguridad o Compactación de las bases.

Pero también es necesario ejecutar trabajos de mantenimiento cuyos objetivos sean el de mantener la performance de las bases de datos y evitar su degradación.

Esos trabajos son la Reorganización de Índices y la Actualización de Estadísticas.

Estos trabajos son independientes del estado de la base de datos. Puede ocurrir que a la base le falten estudios de optimización, pero, al menos, mantendremos la performance actual.

Si la base se encuentra optimizada, entonces más aún, son necesarios para evitar la degradación producto del uso continuo.

Cualquiera de estos trabajos debe realizarse fuera de línea por motivos de: alto consumo de recurso y bloqueo de las tablas en el momento de ejecución.

Las tablas que contienen índices al ser actualizadas o por inserción de nuevos datos, generan fragmentación de estos índices. Estas fragmentaciones conllevan a la pérdida de performance al acceder a ellas.

La instrucción DBCC DBREINDEX reorganiza el índice de una tabla o todos los índices definidos para una tabla.

La sintaxis de esta instrucción es:

DBCC DBREINDEX

   (   ’basededatos.dueño.nombre_de_tabla‘

           [ , índice

               [ , fillfactor ]

           ]

   )   [ WITH NO_INFOMSGS ]

 

Reconstrucción de índices.

Es importante periódicamente examinar y determinar qué índices son susceptibles de ser reconstruidos. Cuando un Índice está descompensado puede ser porque algunas partes de Éste han sido accedidas con mayor frecuencia que otras. Como resultado de este suceso podemos obtener problemas de contención de disco o cuellos de botella en el sistema. Normalmente reconstruimos un Índice con el comando ALTER INDEX.

Es importante tener actualizadas las estadísticas de la base de datos. Para saber si las estadísticas se están lanzando correctamente podemos hacer una consulta sobre la tabla dba_indexes y ver el campo last_analyzed para observar cuando se ejecutaron sobre ese Índice las estadísticas.

SELECT index_name, last_analyzed

FROM dba_indexed

WHERE table_owner=’nb_usuario’

Nota: Siendo nb_usuario el nombre del esquema del usuario para el que queramos validar las estadísticas. (Lanzar con usuario SYS)

Para actualizar las estadísticas utilizamos el paquete DBM_STATS. Podemos actualizar las estadísticas de todos los objetos de un esquema de la siguiente forma:

Execute DBMS_STATS.gather_schema_stats(‘Esquema’);

Nota: Sustituimos esquema por el nombre de nuestro esquema a actualizar (lanzar con usuario SYS)

 

Los Índices que deberíamos de reconstruir son los que en la columna ok aparecen como BLEVEL HIGH.

Blevel (branch level) es parte del formato del B-tree del Índice e indica el número de veces que ORACLE ha tenido que reducir la búsqueda en ese Índice. Si este valor está por encima de 4 el Índice deberá de ser reconstruido.

Comando ALTER INDEX.

Como hemos comentado esta sentencia se utiliza para cambiar o reconstruir un Índice existente en la base de datos.

Para poder ejecutar este comando el Índice debe de estar en el propio esquema donde intentes ejecutarlo o deberías de tener el privilegio alter any index. También tenemos que tener en cuenta que para realizar la reconstrucción de un Índice deberíamos de tener cuota suficiente sobre el tablespace que lo lanzamos.

Para reconstruir un Índice bastaría con lazar la siguiente sentencia:

ALTER INDEX REBUILD;

Para reconstruir una partición de un Índice podríamos hacer lo siguiente

ALTER INDEX REBUILD PARTITION NOLOGGING;

Nota: En algunos casos cuando alguno de los Índices tiene algún tipo de corrupción no es posible reconstruirlo. La solución en este caso es borrar el Índice y recrearlo.

 

PARA MAS ENTRETENIMIENTO CHECA EL VIDEO:

 

Referencia Bibliográficas:

UNIDAD 4 :: Administracion Bases de Datos. (2014). Webnode.mx. https://proyecto359.webnode.mx/unidad4/

‌