Lesión 10.5 · Tiempo de lectura: ~11 min

Esta lección explica en detalle cómo funcionan los índices B-tree en SQL a nivel físico y lógico. Aprenderás qué nodos componen la estructura, cómo la base de datos recorre el árbol y por qué este enfoque acelera el filtrado y el ordenamiento. Veremos ejemplos prácticos en las tablas de Sakila y reforzaremos las reglas clave de uso. Al final de la lección, comprenderás mejor cuándo un índice B-tree realmente acelera las consultas.

Cómo Funcionan los Índices B-tree

En la lección anterior, aprendiste cómo crear índices. Ahora entendamos cómo se organiza un índice internamente y por qué acelera la búsqueda.

Entender B-tree te ayudará a ver cuándo un índice realmente funciona y cuándo no puede ser utilizado. Este conocimiento será útil al optimizar consultas lentas.

¿Qué es un Índice B-tree?

Un índice B-tree es como un índice en un libro. En lugar de leer todas las páginas en orden, abres el índice, encuentras el capítulo que necesitas y vas directamente allí.

Un B-tree tiene tres niveles:

• nodo raíz - el punto de partida de la búsqueda, como la portada de un índice;
• nodos intermedios - sugieren qué dirección tomar a continuación;
• nodos hoja - contienen los valores que necesitas y enlaces a las filas de la tabla.

Toda la estructura está ordenada, por lo que la base de datos puede elegir rápidamente la dirección correcta en cada nivel.

Así es como se ve:

                    [ RAÍZ ]
                   /   |   \
                  /    |    \
            [NODO A] [NODO B] [NODO C]
            / |  \    / | \    / | \
           /  |   \  /  |  \  /  |  \
         [L1][L2][L3][L4][L5][L6][L7][L8]

Cada nodo contiene valores que ayudan a seleccionar el siguiente nodo. Los nodos hoja (L1–L8) contienen los datos que necesitas.

Cómo Funciona la Búsqueda por B-tree

Cuando buscas WHERE last_name = 'SMITH', la base de datos:

1. comienza desde el nodo raíz;
2. selecciona la rama donde podrían estar los nombres que comienzan con 'S';
3. desciende, refinando la búsqueda en cada nivel;
4. encuentra el nombre que necesitas en un nodo hoja.

Gracias a este algoritmo, la búsqueda es muy rápida: incluso en una tabla con millones de filas, solo necesitas verificar unos pocos niveles.

¿Qué Operaciones Acelera Mejor B-tree?

Igualdad (=)

B-tree es adecuado para búsquedas de valores exactos.

SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

Rangos (>, <, BETWEEN)

Debido a que las claves están ordenadas, B-tree es eficiente para condiciones de rango.

SELECT
   payment_id,
   amount,
   payment_date
FROM payment
WHERE payment_date >= '2005-07-01'
  AND payment_date < '2005-08-01';

Ordenamiento (ORDER BY)

Si el orden de clasificación coincide con el índice, la base de datos a menudo puede evitar un costoso ordenamiento separado.

SELECT
   payment_id,
   customer_id,
   payment_date
FROM payment
WHERE customer_id = 10
ORDER BY payment_date;

Ejemplo de un Índice B-tree Compuesto

Creamos un índice para un patrón común de filtrado y ordenamiento:

CREATE INDEX idx_payment_customer_date
ON payment (customer_id, payment_date);

Verifiquemos el plan:

EXPLAIN
SELECT
   payment_id,
   customer_id,
   payment_date,
   amount
FROM payment
WHERE customer_id = 10
  AND payment_date >= '2005-07-01'
ORDER BY payment_date;

Resultado: típicamente la base de datos utiliza el índice para filtrar por customer_id y el rango payment_date, así como para la lectura ordenada.

Regla del Prefijo Izquierdo para Índices Compuestos

Si se crea un índice como (customer_id, payment_date), la base de datos lo utiliza mejor si la condición primero filtra por customer_id.

Funciona bien:

WHERE customer_id = 10

Funciona bien:

WHERE customer_id = 10 AND payment_date >= '2005-01-01'

Funciona mal:

WHERE payment_date >= '2005-01-01'

Esta regla se llama "prefijo izquierdo": el índice funciona mejor cuando usas condiciones de izquierda a derecha.

Cuándo un Índice No Ayuda

Un índice no se utiliza si:

• aplicas una función a una columna: WHERE YEAR(payment_date) = 2005 — el índice no funciona;
• usas una máscara al principio: WHERE name LIKE '%SMITH' — el índice no ayudará;
• la condición es demasiado general y devuelve muchas filas — el índice puede ser más lento que leer toda la tabla.

Malo (la función impide el uso del índice):

SELECT payment_id, payment_date
FROM payment
WHERE YEAR(payment_date) = 2005;

Bueno (el índice puede funcionar):

SELECT payment_id, payment_date
FROM payment
WHERE payment_date >= '2005-01-01'
  AND payment_date < '2006-01-01';

Recomendaciones Prácticas

• Indexa campos que aparecen frecuentemente en WHERE, JOIN, ORDER BY.
• Para índices compuestos, coloca la columna más importante para el filtrado primero.
• Verifica el uso real del índice a través de EXPLAIN.
• No crees índices redundantes: aumentan el costo de las escrituras.

Puntos clave de esta lección:

• B-tree es una estructura equilibrada que acelera la búsqueda de claves.
• La principal fortaleza de B-tree: igualdad, rangos y ordenamiento por orden de índice.
• Los índices compuestos siguen la regla del prefijo izquierdo.
• Una forma de condición inapropiada puede privar a una consulta de los beneficios del índice.
• EXPLAIN te ayuda a entender si B-tree se utiliza en el plan de ejecución real.

Preguntas de Entrevista

¿Por qué un índice B-tree suele ser más rápido que un escaneo completo?

Porque la base de datos recorre el árbol a lo largo de las ramas y encuentra el rango necesario en un número logarítmico de pasos, en lugar de escanear todas las filas de la tabla.

¿Cuál es la regla del prefijo izquierdo para un índice compuesto?

Esta regla significa que el optimizador utiliza mejor el índice cuando comienza desde la primera columna de la clave y avanza en orden.

¿Cómo verificas en la práctica que se está utilizando un índice B-tree?

Ejecutas EXPLAIN y observas el tipo de acceso, la clave elegida y el número esperado de filas en cada etapa de ejecución.

En la próxima lección, pasaremos al manejo de errores y técnicas de depuración en SQL.