4.4.1 Tipos de índices y 4.4.2 Reorganización de índices
4.4.1 Tipos de índices
En MySQL se tienen dos tipos de índices, los cuales son:
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.
Í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.
4.4.2 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 deben 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 reorganización de realiza dinámicamente sin necesidad de conocer la estructura de la misma o las restricciones que ella tenga. Por lo tanto no es necesario conocer si una tabla tiene clave primaria o si esta clave es única y además pertenece a algún índice, ya que la reorganización no necesita eliminar y recrear éstas restricciones para realizar su trabajo.
La sintaxis de esta instrucción es:
DBCC DBREINDEX
( ’basededatos.dueño.nombre_de_tabla‘
[ , índice
[ , fillfactor ]
]
) [ WITH NO_INFOMSGS ]
Fillfactor es el porcentaje de espacio de página destinado a ser ocupado. El valor definido reemplaza al que fue generado en el momento de la creación del índice. Si se quiere mantener el valor original, entonces se utiliza el valor 0.
WITH NO_INFOMSGS se suprimen los mensajes generados en la ejecución.
No es necesario conocer los nombres de todos los índices de todas las tablas, ya que si utilizamos la instrucción de la siguiente forma:
DBCC RBINDEX (Movimientos, ‘’, 0)
Se reorganizaran todos los índices que contengan la tabla Movimientos, conservandose el fillfactor original de cada índice en particular.
Una de las formas de utilizarlo es, escribir un script con una sentencia DBCC RBINDEX por cada tabla que necesitamos reorganizar y agendarlas en forma periódica mediante un trabajo de mantenimiento dentro de algún horario disponible.
Por lo tanto, la recomendación será: elegir las tablas más accedidas y/o actualizadas, y reorganizarse una vez entre semana. Para reorganizar todas las tablas que contengan índices se utiliza el mismo concepto, pero dentro de un procedimiento que recorra todas la tablas de la base y las reorganice, sin necesidad que escribamos todas la tablas que contiene la base de datos. Estos procedimientos se pueden encontrar en el Forum bajo el nombre de Tips, y la idea es generar un trabajo de mantenimiento que se ejecute, por ejemplo, en el fin de semana
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.
Í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.
4.4.2 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 deben 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 reorganización de realiza dinámicamente sin necesidad de conocer la estructura de la misma o las restricciones que ella tenga. Por lo tanto no es necesario conocer si una tabla tiene clave primaria o si esta clave es única y además pertenece a algún índice, ya que la reorganización no necesita eliminar y recrear éstas restricciones para realizar su trabajo.
La sintaxis de esta instrucción es:
DBCC DBREINDEX
( ’basededatos.dueño.nombre_de_tabla‘
[ , índice
[ , fillfactor ]
]
) [ WITH NO_INFOMSGS ]
Fillfactor es el porcentaje de espacio de página destinado a ser ocupado. El valor definido reemplaza al que fue generado en el momento de la creación del índice. Si se quiere mantener el valor original, entonces se utiliza el valor 0.
WITH NO_INFOMSGS se suprimen los mensajes generados en la ejecución.
No es necesario conocer los nombres de todos los índices de todas las tablas, ya que si utilizamos la instrucción de la siguiente forma:
DBCC RBINDEX (Movimientos, ‘’, 0)
Se reorganizaran todos los índices que contengan la tabla Movimientos, conservandose el fillfactor original de cada índice en particular.
Una de las formas de utilizarlo es, escribir un script con una sentencia DBCC RBINDEX por cada tabla que necesitamos reorganizar y agendarlas en forma periódica mediante un trabajo de mantenimiento dentro de algún horario disponible.
Por lo tanto, la recomendación será: elegir las tablas más accedidas y/o actualizadas, y reorganizarse una vez entre semana. Para reorganizar todas las tablas que contengan índices se utiliza el mismo concepto, pero dentro de un procedimiento que recorra todas la tablas de la base y las reorganice, sin necesidad que escribamos todas la tablas que contiene la base de datos. Estos procedimientos se pueden encontrar en el Forum bajo el nombre de Tips, y la idea es generar un trabajo de mantenimiento que se ejecute, por ejemplo, en el fin de semana
Comentarios
Publicar un comentario