Índices de bases de datos: La clave para la optimización del rendimiento

Las bases de datos son el pilar de las aplicaciones modernas, pero a medida que aumenta la cantidad de datos, surgen desafíos, especialmente en términos de velocidad de consulta. Los índices de bases de datos, en particular el índice B-tree, son el foco de esta revisión para comprender cómo ayudan a acelerar las consultas de bases de datos.

Los índices de bases de datos juegan un papel crucial en el mundo de las bases de datos de Oracle. A medida que aumentan los volúmenes de datos, los usuarios de bases de datos a menudo luchan con velocidades de consulta lentas. Para resolver este problema, se indexan las tablas. Sin embargo, no es suficiente simplemente crear nuevos índices para acelerar las consultas. Para aprovechar al máximo el potencial de los índices, se requiere un profundo entendimiento de cómo funcionan. En este artículo, analizamos más de cerca cómo funcionan los índices utilizando el ejemplo de las bases de datos de Oracle.

Objetivo de las consultas de bases de datos

Cuando se aplica una consulta SELECT a una o más tablas, uno de los principales objetivos es minimizar el tiempo de consulta. Los datos se almacenan en bloques, conocidos como páginas de BD, en el disco duro. Leer y escribir desde el disco duro es un proceso que consume tiempo. Los datos que se consultan con frecuencia se almacenan en la memoria caché del búfer limitada. Cuanto más frecuentemente se tenga que leer datos del disco duro, más tiempo lleva una consulta.

Si los datos están restringidos en la instrucción SELECT por criterios de filtro (condición WHERE) y no hay índices disponibles, todos los registros de datos en esta tabla deben leerse desde el disco duro y verificarse con el criterio de filtro dado.

Tal escaneo completo de la tabla puede no ser un problema para tablas pequeñas. Con miles o millones de registros de datos, sin embargo, este procedimiento conduce a tiempos de espera más largos. Especialmente si varias tablas están vinculadas, como es el caso de las bases de datos normalizadas en el esquema Star.

Los índices se utilizan para reducir los tiempos de espera y minimizar el número de operaciones de lectura en el disco duro.

El índice B-tree

Uno de los tipos de índices más utilizados es el índice B-tree. Esta es una estructura de árbol jerárquica basada en un conjunto específico de reglas para permitir búsquedas, inserciones y eliminaciones de datos más rápidas y eficientes en memoria. El nombre "B-Tree" se refiere a la estructura equilibrada del árbol ("árbol equilibrado"). Esto significa que todos los nodos hoja están en el mismo nivel y el grado de ramificación se mantiene relativamente bajo para garantizar un acceso rápido a los nodos hoja. El índice B-tree almacena los datos en orden ordenado, lo que lo hace muy eficiente para consultas de rango (WHERE X BETWEEN Y AND Z) y verificaciones de igualdad (WHERE X = Y). B-Tree es el tipo de índice estándar para varios sistemas de bases de datos como Oracle y MySQL. El siguiente ejemplo muestra un simple índice B-tree basado en una lista de números.

Estructura de un índice B-tree

En este ejemplo, tenemos una lista ordenada de valores numéricos [0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28], que representan el índice. El árbol está equilibrado porque todas las hojas más bajas, mostradas aquí en verde, están en el mismo nivel (3). Las hojas, también conocidas como nodos hoja, contienen los datos reales o referencias a registros de datos en la tabla de la base de datos. En el ejemplo de Oracle, estas hojas contienen el ROWID, que permite identificar rápidamente el registro de datos deseado en el disco duro.

Por encima de las hojas están los nodos (ramas), mostrados aquí en amarillo. En este ejemplo, cada nodo solo puede contener tres valores y, por lo tanto, solo tiene tres nodos hijos/hojas. Los nodos siempre representan solo un área para los nodos o hojas almacenados en ellos. El nodo superior se llama raíz, se muestra aquí en azul y representa el inicio del índice B-tree.

Para ilustrar cómo funciona esto, ahora vamos a ver un solo bloque de números. Por ejemplo, la segunda entrada en la raíz (18-28) se refiere a todos los valores entre los números 18 y 28. Como necesitamos almacenar un total de 6 números y la capacidad de almacenamiento máxima se ha establecido en tres valores por hoja, se debe crear un nuevo nodo. Este nuevo nodo contiene nuevamente dos entradas (18-23 | 24-28), que a su vez se refieren a un rango de números. En este ejemplo, solo dos de las tres posiciones están ocupadas. Los valores individuales ahora se guardan en el nivel más bajo (nivel 3). El límite de tres valores por nodo/hoja definido para todo el árbol también se aplica aquí.

En el ejemplo, todavía hay una posición libre en la raíz (azul) y en el segundo nodo (amarillo). Esto significa que aún no se ha alcanzado el número máximo de valores a guardar. Si ahora se agregan los valores 32 y 36, se crea una nueva hoja en el lado derecho. Esta hoja crea una nueva entrada en el nodo, que ahora tiene tres entradas. Este árbol, limitado a tres entradas por nodo, puede almacenar hasta 27 entradas en el tercer nivel (33). Si el número de valores a guardar supera los 27, se debe crear un nuevo nivel. El árbol ahora podría almacenar hasta 81 valores en el nivel 4 (34).

En una base de datos de Oracle, el tamaño de los nodos/bloques se puede definir al crear la base de datos. Tomemos un tamaño de bloque de 8 KB como ejemplo. Si se almacenan datos como cadenas, marcas de tiempo o enteros con un tamaño de 8 bytes, un bloque puede almacenar más de 400 entradas. Como se puede ver en la tabla, este tipo de árbol ya puede almacenar hasta 64 millones de entradas en el tercer nivel y hasta 25 mil millones de entradas en el cuarto nivel. En la práctica, los B-trees suelen tener solo hasta 4 niveles.

Cambio en el índice B-tree

Un aspecto clave del índice B-tree es su capacidad para autoequilibrarse. Cuando cambian los datos subyacentes, ya sea agregando nuevos registros o eliminando registros existentes, el árbol se reestructura automáticamente para mantener su equilibrio. Esto significa que los nodos hoja del B-tree siempre permanecen en el mismo nivel. Este equilibrio automático garantiza que el rendimiento de búsqueda permanezca en un nivel consistentemente alto incluso cuando los datos cambian dinámicamente. Sin embargo, debe tenerse en cuenta que los ajustes regulares al índice pueden ralentizar la inserción (INSERT), actualización (UPDATE) y eliminación (DELETE) de datos en comparación con las tablas sin índice.

Para ilustrar esto, volvamos a mirar los valores numéricos previamente indexados de 0 a 28 y agreguemos los valores 13, 15 y 17. Al insertar estos valores, el árbol debe reestructurarse hasta la raíz. Esto significa que los rangos de números deben ajustarse en cada nivel y se debe agregar una nueva hoja. La nueva estructura con los valores cambiados en negrita se muestra en la siguiente ilustración.

Índices B-tree en Oracle

A pesar de sus dificultades potenciales, el índice B-tree sigue siendo una herramienta indispensable para optimizar las consultas de bases de datos y aumentar el rendimiento. Para crear un índice B-tree en Oracle, se debe ejecutar el siguiente código: CREATE INDEX index_name ON table_name(attribute_name);

También es posible crear índices utilizando varios atributos de una tabla. Sin embargo, debe tenerse en cuenta que el orden de los atributos especificados es importante. La optimización del rendimiento solo se puede lograr a través del índice definido si los atributos especificados también se utilizan en las operaciones de filtro (WHERE) de la declaración SELECT. Si este no es el caso y solo algunos de los atributos se filtran en la declaración WHERE, el optimizador de consultas realiza un escaneo completo de la tabla o posiblemente utiliza otros índices más pequeños. Para crear un índice a partir de múltiples atributos en Oracle, se utiliza el siguiente código: CREATE INDEX index_name ON table_name(attribute_name_1, attribute_name_2);

Mejores Prácticas

Al utilizar índices B-tree, hay varios aspectos a considerar para evitar reducciones indeseables en la velocidad de consulta. Las reglas más importantes y las mejores prácticas para el uso de índices B-tree se resumen a continuación:

• Clave primaria y clave externa: Para mejorar el rendimiento de las consultas con uno o más joins sobre tablas, es recomendable crear índices para claves primarias y externas. Esto significa que los joins se pueden ejecutar más rápidamente y no es necesario realizar un escaneo completo de la tabla para tablas grandes.
• Alta cardinalidad: Los atributos seleccionados para el índice deben tener una alta cardinalidad, es decir, contener muchos valores únicos. Por ejemplo, el atributo de fecha de nacimiento tiene una alta cardinalidad y el atributo de género tiene una cardinalidad muy baja. Si crea un índice B-tree con baja cardinalidad, es probable que el Optimizador de Consultas no use este índice. Otros tipos de índices, como el índice de mapa de bits, son más adecuados aquí.
• Operadores correctos: Para poder utilizar un índice en una consulta, se deben usar los operadores de comparación correctos en la condición WHERE. Estos son, en particular, operadores de igualdad (mayor que, menor que, igual a) y operadores de rango (ENTRE). Los operadores de negación como NOT o "!=" impiden el uso de un índice.
• Pequeños conjuntos de consultas: Un índice solo se utiliza por el Optimizador de Consultas si el resultado representa un subconjunto pequeño del volumen total de datos. Es ideal si los datos seleccionados representan menos del 1% del volumen total de datos disponible. Entre el uno por ciento y el diez por ciento, el índice aún se puede usar; para más del diez por ciento, es posible que un escaneo completo de la tabla sea más rápido.
• Caso de uso: Para crear índices óptimos, es útil conocer las consultas SQL esperadas y adaptar los índices en consecuencia.
• Múltiples atributos: Para consultas y joins complejos, puede ser útil crear un índice a partir de múltiples atributos. Los atributos con alta cardinalidad deben colocarse primero para permitir una traversía más rápida del árbol de índice.
• Número de índices: Debe evitar crear demasiados índices, ya que esto consume memoria y puede ralentizar las operaciones SQL como INSERT, UPDATE y DELETE.
• Plan de consulta: El plan de consulta se puede utilizar para verificar si se utiliza un índice en las consultas SELECT. Esto muestra si se utiliza un escaneo completo de la tabla o un índice para los atributos a filtrar.

Conclusión

Para resumir, las razones para utilizar índices B-tree en sistemas de bases de datos se pueden derivar de los puntos discutidos anteriormente:

• Eficiencia: Los B-Trees proporcionan una operación de búsqueda eficiente con una complejidad logarítmica del peor caso de O(log n), donde n es el número de entradas en el árbol. Esto significa que el tiempo de búsqueda crece logarítmicamente con el número de entradas, lo que hace que B-Tree sea muy eficiente para grandes conjuntos de datos.
• Equilibrio: Los B-Trees son autoequilibrados, lo que significa que cuando se agrega o elimina datos, el árbol se reestructura automáticamente para mantener un equilibrio entre los subárboles izquierdo y derecho. Esto garantiza que las operaciones de búsqueda tengan aproximadamente el mismo tiempo para todos los nodos en el árbol y mejora el rendimiento general de la base de datos.
• Consultas de rango: B-Tree admite consultas de rango de manera eficiente, ya que los datos se almacenan en bloques consecutivos en el disco duro. Esto permite un acceso rápido a los datos dentro de un rango de valores en una columna.
• Espacio en disco duro: Los B-Trees están optimizados para los requisitos especiales del espacio en disco duro, que es crucial para las bases de datos. Los índices B-Tree minimizan el número de búsquedas de disco necesarias para encontrar un registro y mejoran el rendimiento general del sistema.