Índices (InterBase y Firebird)

Alexey Kovyazin, última actualización 07-Sep-2005

El concepto que se asume como base de los índices es simple y visual, y es una de las bases más importantes del diseño de bases de datos. Sobre la base de los índices se fundamentan muchos objetos básicos de la base de datos, y además, el uso correcto de los índices es clave para mejorar la productividad de las aplicaciones de bases de datos. Sin embargo, ¿qué es un índice? Un índice es un puntero ordenado de los registros en la tabla. Puntero significa que el índice contiene valores de uno o varios campos de la tabla y las direcciones de las páginas de datos donde se encuentran estos valores (para más detalles sobre las páginas de datos, consulte el capítulo “Estructura de la base de datos InterBase”) (parte 4). En otras palabras, el índice consiste en pares de valores “valor del campo” - “ubicación física de este campo”.

Así, por el valor del campo (o campos), incluido en el índice, usando el índice podemos encontrar rápidamente el lugar en la tabla donde se asigna el registro que contiene este valor. Ordenado significa que los valores de los campos almacenados en el índice están ordenados. Muy a menudo el índice se compara con un catálogo de biblioteca, en el que todos los libros se registran en tarjetas y se ordenan de alguna manera: según el alfabeto o los temas, y en cada tarjeta contiene la información de dónde exactamente se asigna el libro dado en el almacenamiento.

¿Por qué necesitamos índices?

Lo único que promueven los índices es acelerar la recuperación de registros por su campo indexado (indexado - significa incluido en el índice). La función principal de los índices es proporcionar una recuperación rápida de registros en la tabla. Cualquier uso de índices se reduce a esto.

¿Cómo se realiza esta función de recuperación? A la entrada de esta función tenemos el valor del campo indexado (o varios campos). Como resultado de la recuperación debemos recibir el registro completo, en el que el campo indexado tiene un valor preestablecido. Primero en el índice (para ser más precisos, en la matriz ordenada de valores del campo indexado) se busca el valor requerido, luego se toma la dirección de la página de datos donde se encuentra el registro requerido, el servidor va a esta página y lee el registro encontrado. Parece bastante inconveniente, sin embargo, la búsqueda, usando el índice, es muchas veces más rápida que la enumeración secuencial de todos los valores de la tabla.

Si continuamos haciendo la analogía entre el índice y el catálogo de la biblioteca, veremos que la recuperación de registros, usando el índice, es muy similar a la búsqueda de libros, usando la tarjeta. Cuando encontramos un libro en un catálogo bastante pequeño (en comparación con todo el almacenamiento de la biblioteca), recibimos inmediatamente la información sobre dónde exactamente se almacena el libro y podemos ir directamente allí. ¡La búsqueda sin usar el índice se puede comparar con la enumeración secuencial de todos los libros de la biblioteca!

La enumeración de todos los registros en la tabla se llama directa o natural. Debemos decir que a pesar de los poderes de las computadoras modernas, la enumeración natural puede ser muy larga si la tabla contiene un gran número de registros.

¿Cómo están organizados?

El índice no es una parte de la tabla, es un objeto separado conectado con la tabla y otros objetos de la base de datos. Este es un punto muy importante de la implementación del DBMS que permite separar el almacenamiento de información de su representación.

InterBase como cualquier otra base de datos relacional almacena registros en tablas de manera desordenada, es decir, no le importa en absoluto cómo se asignan físicamente los registros en la tabla. El almacenamiento desordenado significa que dos registros agregados a la tabla uno tras otro pueden no estar uno al lado del otro. Además, los datos extraídos de la tabla tampoco tienen orden aparte del que debería ser especificado explícitamente por el usuario que hace una consulta de recuperación.

Sin embargo, no podemos prescindir de ordenar los datos almacenados: los usuarios finales de las aplicaciones quieren ver los datos en el orden definido - por ejemplo, los apellidos de las personas según el alfabeto. Los índices resuelven el problema de la representación de datos de manera ordenada. Los valores de los campos incluidos en el índice están ordenados y se representan en la vista especial, optimizada para buscar los valores requeridos (es decir, esto es esencial para crear secuencias ordenadas).

Separar el almacenamiento de datos de su representación da beneficios adicionales en comparación con la clasificación directa - quizás necesite ordenar la tabla inicial de diferentes maneras. Entonces los índices le ayudarán - ¡puede haber hasta 64 índices para cada tabla!

Si hablamos de la implementación de los índices a nivel físico, representan un árbol binario cuyos nodos representan pares “valor del campo en el índice” - “asignación de datos en la tabla”. La recuperación del registro requerido en el índice se realiza mediante el mecanismo de búsqueda hash - uno de los algoritmos de búsqueda más rápidos.

Aplicación de índices

Ahora, cuando está claro lo que podemos exigir de los índices, es hora de conocer su función en una base de datos. Los índices se usan en tres casos principales:

Aceleración de la ejecución de consultas. Los índices se crean para los campos utilizados bajo las condiciones de búsqueda de consultas SQL.
Soporte de unicidad de valores en campos; una restricción de clave primaria (sobre la cual se habló en el capítulo “Tablas. Claves primarias”) exige que en la tabla no haya dos valores idénticos de los campos incluidos en una clave primaria. Para cumplir esta condición, al insertar un nuevo registro debe buscar el mismo valor que se insertará. Para la recuperación de registros se usa la variedad especial de índice - un índice único (ver más abajo).
Soporte de integridad referencial. Las restricciones de claves foráneas (que se consideran en el capítulo “Restricciones de la base de datos”) se usan para verificar que los valores insertados en la tabla existan necesariamente en otra tabla. Al crear una clave foránea, el índice se crea automáticamente. Este índice se aplica para acelerar las consultas que usan la unión de tablas, así como para verificar las condiciones de la clave foránea. Hemos cubierto brevemente todas las aplicaciones posibles de los índices. Ahora consideraremos las peculiaridades de cada caso con más detalle y responderemos las preguntas más frecuentes sobre la aplicación de índices.

Aceleración de la ejecución de consultas usando índices

Se describió anteriormente que la aplicación de índices puede acelerar enormemente la ejecución de consultas. Es realmente así en la mayoría de los casos, pero hay ciertas estipulaciones. Primero, responderemos la pregunta frecuente entre aquellos que se han familiarizado con los índices. Si los índices aceleran la recuperación de una base de datos, ¿por qué no indexar todos los campos de la tabla? Hay dos momentos que bloquean la indexación general: el espacio en disco y los costos al modificar los datos en la tabla. Cada índice creado tiene un tamaño igual al tamaño de los datos en el campo indexado, más el tamaño de los datos de asignación de registros. Si creamos índices para cada campo de la tabla, su tamaño total será mayor que el tamaño de los datos en la tabla! Por lo tanto, la creación de un gran número de índices conduce a un gran gasto de espacio en disco.

El segundo momento es más importante. Estos son los gastos al modificar los datos en la tabla. En un DBMS relacional, como sabe, los registros en las tablas no están ordenados y, en consecuencia, agregar/eliminar registros se realiza sin gastos significativos de recursos del servidor. Incluso si se elimina un registro de la mitad de una base de datos, no hay movimiento de tamaños de datos para llenar este vacío - no es necesario: el servidor simplemente marcará el lugar vacío y escribirá algo allí cuando sea necesario. En cuanto a la adición, en la mayoría de los casos se ejecuta al final de la tabla. Sin embargo, aunque el servidor no mueve los datos principales en la tabla al modificar, los datos almacenados en los índices se reordenan cada vez que se agregan/eliminan los registros! En otras palabras, el servidor tiene que reconstruir el índice al agregar un registro al medio de la tabla. Ciertamente, la implementación del índice está de alguna manera destinada a reorganizaciones frecuentes, pero estas operaciones toman tiempo y recursos del procesador y cuando hay un gran número de índices en la tabla, la modificación de datos dentro de ella puede ser mucho más lenta que en la misma tabla sin índices!

Estas son dos razones principales que interfieren con la indexación general. Además de ellas, hay algunas observaciones más que restringen la aplicación de índices. La primera es la regla del 20 %. Dice que si la consulta de recuperación devuelve más del 20 % de los registros de la tabla, el uso del índice puede ralentizar la recuperación de datos! Ciertamente, la situación depende de una consulta concreta y las condiciones establecidas para la recuperación, pero debemos recordar que el 20 % de los registros es un umbral cuando la eficiencia del uso de índices se vuelve dudosa. La segunda observación no está formulada tan claramente. Está conectada con el trabajo del optimizador de InterBase.

El optimizador es una colección de mecanismos que desarrollan el programa de ejecución de la consulta. Cuando el usuario da cualquier consulta SQL a InterBase, especifica qué debe devolver el servidor después de ejecutar la consulta, pero no define CÓMO el servidor debe cumplir la consulta. El optimizador sobre la base de la consulta dada crea el programa de su ejecución, es decir, de dónde y en qué orden se tomarán los datos para ejecutar la consulta, qué índices se usarán en eso. Cuando el servidor analiza las condiciones de recuperación (estas son principalmente partes de la expresión WHERE, ORDER BY, etc.) para cada campo incluido en la condición, el servidor intenta usar el índice. Desafortunadamente, el algoritmo de crear el programa es incompleto y el optimizador frecuentemente usa índices que no son demasiado efectivos para la consulta concreta debido a lo cual el tiempo de ejecución puede ralentizarse esencialmente. Por lo tanto, la creación de índices innecesarios puede llevar a la creación de programas no óptimos.

Debe marcarse que en el clon Yaffil este problema se resuelve usando algoritmos modernos de hacer programas. El tercer caso cuando el índice no es necesario son los campos con un conjunto limitado de valores - por ejemplo, el campo que almacena la información sobre el sexo de la persona y contiene solo dos valores posibles - “F” y “M”; no tiene sentido indexar este campo. Entonces, hemos considerado las principales restricciones para crear índices. Ahora debemos cubrir el problema de cuándo es necesario usar índices para lograr una mejora de la productividad. Hay 3 casos principales cuando un campo tiene que ser indexado:

Cuando este campo se usa bajo las condiciones de recuperación en consultas
Cuando las uniones de tablas usan este campo
Cuando este campo se usa en la declaración de ordenamiento ORDER BY Si el campo se aplica de la manera mencionada anteriormente, crear el índice para él puede llevar a una mejora de la productividad de la consulta.

Consideremos una sintaxis de creación de índices. Aquí está el formato completo del comando DDL que permite crear índices:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

La expresión mínima que crea el índice es la siguiente:

CREATE INDEX my_index ON Table_example(ID)

En este ejemplo, el índice con nombre my_index se crea para la tabla Table_example, y el campo ID es el campo indexado. El índice es ascendente, es decir, los valores en él están ordenados por ascenso, así como no único, y significa que el campo ID puede tener varios valores idénticos. Ciertamente es el ejemplo más simple de índice - el más común. Como podemos ver en la descripción de la sintaxis, el índice puede contener no uno, sino varios campos. Tal índice se usa cuando las consultas se cumplen frecuentemente y contienen una combinación de campos indexados bajo las condiciones de búsqueda o ordenamiento. Por ejemplo, si tenemos una tabla que contiene campos Apellido, Nombre, Patronímico, tal índice se aplicará al hacer la consulta que usa el ordenamiento por Apellido, Nombre y Patronímico. En general, no es necesario especificar las condiciones para los 3 campos aplicados en el índice para usar sus ventajas. Si queremos ordenar el resultado de la consulta, el índice se usará en caso de que el primer campo en una condición de ordenamiento coincida con el primer campo en el índice. Por ejemplo, nuestro índice se aplicará en caso de ordenamiento por Apellido y Nombre.

Según la documentación para la optimización de la ejecución de consultas que contienen en la declaración WHERE una unión de campos con la condición OR, debemos usar no el índice agregado, sino varios índices individuales para todos los campos incluidos en la condición OR.

En cuanto a la cuestión del orden de clasificación del índice, este puede ser ascendente o descendente. ¿Por qué necesitamos diferentes órdenes de clasificación? ¡Obviamente, para diferentes ordenamientos! Si deseamos ordenar personas por apellido en orden ascendente, creamos el índice ascendente (ASC), y si en orden descendente (de Z a A) - ¡entonces descendente! Si queremos ambos, tenemos que crear ambos índices.

Soporte de integridad referencial mediante índices

Hay una opción más en la definición de índices: UNIQUE. Si la especificamos, el índice permitirá insertar solo valores únicos en la tabla. En realidad, es la base para la implementación de claves únicas. Las claves únicas se utilizan ampliamente en las bases de datos. Es decir, la CP es una clave-índice única, pero no toda CU es una CP. Arriba hablamos solo de la CP. Una clave primaria es el tipo más común de clave única. Al crear una clave primaria para la tabla, se crea automáticamente un índice único. Se le da un nombre compuesto por RDB$PRIMARYNNN, donde NNN es un número único secuencial dentro de la base de datos. Así, dos restricciones principales de integridad referencial - una clave única y una clave primaria - se realizan mediante el uso de un índice único. Es obvio que la noción de unicidad es incompatible con la noción de valor indefinido. En otras palabras, no debe haber valores de tipo NULL en los campos contenidos en índices únicos. Antes de crear un índice único para un campo, es necesario establecer la restricción NOT NULL. Si el índice se crea para datos que ya existen, al crearlo se comprobará si el campo indexado contiene valores repetitivos. Si los contiene, se le prohibirá crear el índice.

Además de las restricciones de clave única y clave primaria, el mecanismo de índices subyace en la implementación de una restricción más de integridad referencial: una clave externa. La restricción de clave externa se establece para uno o varios campos de cualquier tabla y evita insertar en estos campos valores que no estén incluidos en la clave primaria de la otra tabla, la tabla padre. Para implementar la clave externa, es decir, para realizar la verificación de si existe un valor en la tabla padre, se crea automáticamente un índice especial. Su nombre es RDB$FOREIGNNN, donde NNN es un número único secuencial dentro de la base de datos.

¿Por qué se utiliza el mecanismo de índices para implementar las restricciones de integridad referencial? El asunto es que los índices en InterBase están en una posición especial y preferente - se dice que se ejecutan fuera del contexto de las transacciones. Esta es una propiedad muy importante. Hablaremos de las transacciones más adelante, en el capítulo dedicado a ellas. Ahora solo mencionaremos que cuando los índices están fuera de las transacciones, significa que todos los usuarios que trabajan simultáneamente con los datos en la misma tabla tienen que observar las restricciones de integridad referencial.

Optimización de la productividad de los índices

En el título de esta parte podemos encontrar una paradoja: los índices, como se dijo arriba, sirven para acelerar la ejecución de consultas, ¡y resulta que también deben optimizarse! Pero qué se le va a hacer (así es la vida) - alguien tiene que cuidar de los índices. ¿Qué les sucede a los índices? ¿Por qué “pierden forma”? Tendremos que decir una vez más que los índices se implementan como un árbol binario. Y cuando se añade un nuevo registro (se actualiza, se elimina - como se quiera) a la tabla, se añade una nueva rama al árbol. Estas ramas no se añaden al medio del árbol, sino a las puntas de otras ramas. Gradualmente el árbol se vuelve cada vez más ramificado (o desequilibrado), y la búsqueda - menos efectiva. La reconstrucción del árbol o (en algunos casos) el recálculo de estadísticas puede mejorar la situación.

Periódicamente se requiere recrear el índice para restaurar su productividad. La recreación del índice ocurre en los siguientes casos:

Al reconstruir el índice usando el comando ALTER INDEX.
Al eliminar y recrear el índice usando los comandos DROP INDEX y CREATE INDEX.
Al hacer una copia de seguridad y restaurar desde una copia de respaldo usando la herramienta gbak.

También se puede usar el recálculo de estadísticas. Pero hay que entender que esta operación no cambia el estado del índice, solo informa al optimizador sobre la información precisa de su estado, permitiendo usar este índice correctamente. En otras palabras, el recálculo de estadísticas no es una “cura” para el índice, sino solo un diagnóstico preciso de su estado. Consideremos todas estas formas de optimización de índices con más detalle. El uso del comando ALTER INDEX tiene el siguiente formato:

ALTER INDEX nombre {ACTIVE | INACTIVE};

Aquí nombre es el nombre del índice, y ACTIVE e INACTIVE - dos estados del índice a los que se puede convertir usando el comando ALTER INDEX. El parámetro ACTIVE significa que el índice está activo y puede aplicarse en todas las consultas y procedimientos. Si establece el índice en INACTIVE, resultará en la desconexión de su uso. Para reorganizar el árbol se deben ejecutar secuencialmente dos comandos:

ALTER INDEX nombre INACTIVE; ALTER INDEX nombre ACTIVE;

Así, el índice se reconstruirá. El uso de ALTER INDEX tiene una serie de restricciones: no se pueden reconstruir los índices utilizados en claves primarias, únicas y externas; no se puede reconstruir el índice si está siendo utilizado por alguna consulta en el momento actual; y también para alterar el índice es necesario tener los derechos de administrador (SYSDBA) o ser el creador del índice dado.

La recreación del índice usando los comandos DROP INDEX y CREATE INDEX conduce a la eliminación completa del índice de la base de datos, y luego a su creación desde cero. La sintaxis del comando DROP INDEX es obvia:

DROP INDEX nombre_del_índice;

Después de eliminarlo, es necesario crear el índice con el mismo nombre y parámetros usando el comando CREATE INDEX, cuya sintaxis ya hemos considerado. La forma de reconstruir el índice mediante su recreación completa tiene restricciones similares a las del uso de ALTER INDEX.

La tercera forma de reconstruir el índice se basa en la propiedad de las copias de seguridad de las bases de datos InterBase creadas por la utilidad gbak. El asunto es que, al hacer una copia de seguridad, los datos incluidos en el índice no se guardan en la copia de respaldo, solo se almacena la definición del índice. Al restaurar desde una copia de respaldo, el índice se recrea. Si desea saber más sobre la copia de seguridad, consulte el capítulo “Copia de seguridad y restauración desde una copia de respaldo” (parte 4).

La cuarta forma de mejorar la productividad de los índices es recopilar estadísticas sobre los índices usando el comando SET STATISTICS. La estadística de la tabla es un valor dentro del rango de 0 a 1, cuyo valor depende del número de registros diferentes en la tabla. El optimizador de InterBase utiliza las estadísticas para definir la eficiencia de la aplicación de tal o cual índice en una consulta. Cuando el número de registros en la tabla puede alterarse profundamente (por ejemplo, debido a un gran número de inserciones o eliminaciones), el recálculo de estadísticas puede mejorar considerablemente la productividad. El comando de recálculo de estadísticas es el siguiente:

SET STATISTICS INDEX nombre;

Aquí nombre es el nombre del índice para el cual se recalculan las estadísticas. El recálculo de estadísticas no reconstruye el índice y por eso está libre de la mayoría de las restricciones establecidas para las formas descritas anteriormente de mejorar la productividad, excepto que solo el creador del índice o el administrador del sistema (el usuario con nombre SYSDBA) puede recalcular las estadísticas. Las estadísticas correctas permiten al optimizador tomar una decisión verdadera sobre usar cualquier índice o no.

Hemos considerado algunas formas de mejorar la productividad de los índices. Usando los comandos ALTER INDEX y DROP/CREATE INDEX, podemos reconstruir cualquier índice excepto los índices del sistema creados automáticamente, destinados a garantizar la integridad referencial. Si desea reconstruir estos índices, debe usar los comandos de alteración y creación de tablas - ALTER TABLE y CREATE TABLE, ya que estos índices son una parte integral de las claves tabulares.