Índices B-Tree y cómo ayudan

Cuando un entrevistador dice 'esta consulta es lenta, ¿qué hace?', casi siempre espera escuchar una respuesta que incluya un índice. Los índices son el mecanismo que más puede mejorar el rendimiento de lectura, por lo que permiten distinguir a quienes han memorizado la sintaxis de quienes entienden cómo encuentra realmente las filas una base de datos.

En esta lección construirá un modelo mental preciso del índice B-Tree: qué almacena, qué operaciones acelera y cómo hablar de él como lo haría un ingeniero sénior.

Sin un índice, encontrar las filas que cumplen una condición obliga a la base de datos a leer todas las filas de la tabla. Esto es un escaneo secuencial (o escaneo completo de la tabla). En una tabla de un millón de filas, implica comprobar un millón de filas aunque solo coincida una.

Un índice es una estructura de datos independiente y ordenada que permite al motor saltar directamente a las filas coincidentes, del mismo modo que el índice de un libro permite encontrar un tema sin leer todas las páginas.

El índice predeterminado en PostgreSQL, MySQL, SQL Server y la mayoría de los motores es un B-Tree (árbol equilibrado). Almacena los valores de la columna indexada en orden ascendente, organizados en un árbol poco profundo de páginas.

• Cada nodo hoja contiene las claves del índice y un puntero a la fila real de la tabla.
• El árbol se mantiene equilibrado, por lo que cualquier búsqueda solo toca unas pocas páginas, independientemente del tamaño de la tabla.

Una búsqueda baja desde la raíz hasta una hoja en aproximadamente log(N) pasos, en lugar de examinar las N filas.

Puede crear un índice B-Tree con CREATE INDEX. Asígnele un nombre claro para que quien revise el código identifique la tabla y las columnas de un vistazo.

Una vez creado este índice, una consulta que filtre por email puede utilizarlo para encontrar la fila coincidente leyendo unas pocas páginas, en lugar de realizar un escaneo completo.

Como un B-Tree mantiene los valores ordenados, acelera mucho más que las coincidencias exactas. A los entrevistadores les gusta que las enumere con precisión:

• Igualdad: WHERE email = ?
• Rango: WHERE age > 30, BETWEEN, <, >=
• Coincidencia por prefijo: WHERE name LIKE 'Ada%' (pero no '%da')
• ORDER BY sobre la columna indexada, evitando una ordenación
• MIN/MAX, ya que se encuentran en los extremos de la estructura ordenada

Considere una tabla orders con millones de filas. Una consulta de informes solicita los pedidos recientes. Con un índice sobre created_at, el motor busca el inicio del rango en el índice ordenado y avanza solo hasta donde es necesario.

El índice convierte un escaneo de toda la tabla en un escaneo de rango acotado que lee únicamente el segmento que cumple la condición.

Un aspecto que se pasa por alto con frecuencia: como el índice ya está ordenado, el motor puede devolver las filas en el orden del índice y omitir un paso de ordenación independiente. Esto es importante para ORDER BY y, especialmente, para la paginación de los primeros N resultados.

Si ordena por una columna que tiene un índice correspondiente, el optimizador puede leer el índice en orden y detenerse en cuanto haya obtenido suficientes filas.

Un índice B-Tree normal almacena únicamente la columna indexada y un puntero a la fila. Por eso, después de encontrar las entradas coincidentes, el motor todavía debe ir a la tabla (el heap) para leer las demás columnas seleccionadas.

Ese segundo salto es el heap fetch. Resulta barato para unas pocas filas, pero costoso cuando una consulta coincide con muchas filas; esta es una de las razones por las que a veces se ignora un índice con baja selectividad. (Más adelante verá cómo los índices de cobertura resuelven este problema.)

Nunca afirme que se usa un índice: demuéstrelo con EXPLAIN. En una entrevista, explicar el plan en voz alta demuestra que realmente entiende el tema.

• Seq Scan significa que el índice NO se utilizó.
• Index Scan o Index Seek significa que sí se utilizó.

Si ha añadido un índice pero sigue viendo un escaneo secuencial, el planificador ha considerado que el escaneo era más barato, a menudo porque la consulta coincide con una fracción demasiado grande de la tabla.

Una pregunta capciosa habitual en las entrevistas: declarar una restricción PRIMARY KEY o UNIQUE crea automáticamente un índice B-Tree de respaldo. No necesita, ni debería, añadir un segundo índice sobre la misma columna.

Por eso las uniones y búsquedas por claves primarias ya son rápidas, y por eso la pregunta «¿debería crear un índice para la columna id?» suele ser una trampa: ya está hecho.

Resúmalo con una frase clara que el entrevistador pueda seguir fácilmente:

'Un índice B-Tree es una estructura ordenada y equilibrada que permite al motor encontrar filas con log(N) lecturas de páginas, en lugar de escanear toda la tabla. Acelera las operaciones de igualdad, rango, prefijo y ORDER BY sobre las columnas indexadas, pero cada coincidencia sigue teniendo un coste de heap fetch para las columnas que no están indexadas.'

Después, respáldelo con EXPLAIN. Esa combinación de modelo y pruebas es la que permite obtener una buena valoración.

Ponga a prueba su modelo mental sobre las operaciones que acelera un índice B-Tree.

Ideas clave que debe llevarse a la siguiente lección:

• Un B-Tree almacena los valores indexados ordenados en un árbol equilibrado, lo que permite búsquedas en log(N).
• Acelera las operaciones de igualdad, rango, prefijo (LIKE inicial), ORDER BY y MIN/MAX.
• Cada coincidencia sigue necesitando un heap fetch para las columnas que no están en el índice.
• Aplicar una función a una columna o utilizar un comodín inicial desactiva el índice.
• Verifique siempre con EXPLAIN; las restricciones PRIMARY KEY y UNIQUE crean índices automáticamente.

A continuación: cómo ordenar las columnas cuando un índice cubre varias a la vez.