Enseñar índices de base de datos a un desarrollador backend junior

Comprensión de los índices de bases de datos: una guía práctica

Qué es un índice

Un índice de base de datos es una estructura de datos separada que la base de datos mantiene junto a una tabla. Almacena los valores de una o más columnas en un formato ordenado y buscable, junto con punteros a las filas reales. La tabla en sí almacena sus datos; el índice es una estructura adicional cuyo único propósito es ayudar a la base de datos a encontrar filas más rápido.

Aquí hay una analogía, y es la única que necesitará: un índice es como el índice al final de un libro de texto. Si desea todas las páginas que mencionan "transacciones", no lee el libro completo página por página; busca "transacciones" en el índice ordenado alfabéticamente, obtiene una lista corta de números de página y salta directamente a ellas. Sin ese índice, su única opción es escanear cada página. Una base de datos se enfrenta exactamente a la misma elección: usar un índice para saltar a las filas coincidentes o escanear toda la tabla.

Ahora el comportamiento real, sin la analogía. Cuando ejecuta una consulta como SELECT * FROM orders WHERE customer_id = 42, la base de datos tiene dos estrategias básicas. Un escaneo completo de la tabla lee cada fila y verifica la condición, lo que cuesta tiempo proporcional al tamaño de la tabla. Una búsqueda de índice, en cambio, busca en la estructura de índice ordenada customer_id = 42, encuentra rápidamente las entradas coincidentes y sigue los punteros almacenados para recuperar solo esas filas. Para una tabla grande donde solo unas pocas filas coinciden, la ruta del índice puede ser miles de veces más barata.

Cómo funciona un índice B-tree, a alto nivel

El tipo de índice más común es un B-tree. Es una estructura de árbol equilibrado donde las claves se mantienen en orden ordenado. El nodo superior divide el espacio de claves en rangos, cada nodo hijo subdivide aún más, y el nivel inferior (las hojas) contiene los valores indexados reales con punteros a las filas de la tabla. Debido a que el árbol está equilibrado y cada nodo contiene muchas claves, incluso una tabla con cientos de millones de filas generalmente necesita solo de tres a cinco lecturas de nodos para encontrar cualquier valor específico.

Debido a que un B-tree mantiene los valores en orden ordenado, admite más que coincidencias exactas. Maneja eficientemente condiciones de rango (WHERE created_at >= '2024-01-01'), coincidencias de prefijo en cadenas (WHERE email LIKE 'anna%'), y puede devolver filas ya ordenadas, lo que permite a la base de datos omitir un paso de ordenación separado para las cláusulas ORDER BY coincidentes.

Por qué los índices cuestan algo

Los índices no son gratuitos, y este es el compromiso que debe internalizar.

Las escrituras se vuelven más lentas. Cada INSERT debe agregar una entrada a cada índice de la tabla. Cada DELETE debe eliminar entradas. Cada UPDATE que cambia una columna indexada debe actualizar las entradas de índice correspondientes. Una tabla con seis índices efectivamente realiza hasta siete escrituras por cada inserción de fila lógica. En tablas con muchas escrituras, la indexación descuidada perjudica mediblemente el rendimiento.

El almacenamiento crece. Cada índice es una copia completa de los valores de las columnas indexadas más punteros y estructura de árbol. Los índices en tablas grandes pueden rivalizar o superar el tamaño de la tabla en sí, lo que también afecta las copias de seguridad y el almacenamiento en caché de memoria.

Por lo tanto, el principio rector es: los índices intercambian costo de escritura y almacenamiento por velocidad de lectura. Los agrega donde las lecturas se benefician claramente, no en todas partes.

Selectividad: el concepto clave para decidir el valor

La selectividad describe qué tan bien una condición reduce las filas. Una columna altamente selectiva tiene muchos valores distintos en relación con el recuento de filas. Un correo electrónico o un ID de pedido son altamente selectivos: filtrarlos devuelve una o un puñado de filas de millones, y un índice brilla. Una columna como status con tres valores ('pending', 'shipped', 'cancelled') o un indicador booleano is_active tiene baja selectividad: el filtrado aún puede coincidir con el 40% de la tabla.

¿Por qué esto importa? Si una condición coincide con una gran fracción de la tabla, saltar de un lado a otro entre el índice y la tabla para millones de filas a menudo es más lento que simplemente escanear la tabla secuencialmente. Los planificadores de consultas lo saben e ignorarán un índice cuando la fracción de coincidencia estimada sea demasiado alta. Como intuición aproximada, si una consulta típica que usa el índice devolvería más de un pequeño porcentaje de las filas, el índice podría no usarse en absoluto, y es una sobrecarga pura.

Índices compuestos y la regla del prefijo izquierdo

Un índice puede cubrir múltiples columnas, en un orden específico. Por ejemplo:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Piense en esto como ordenar las entradas primero por customer_id, luego por created_at dentro de cada cliente, como un directorio telefónico ordenado por apellido, luego por nombre.

La idea del prefijo izquierdo se deriva directamente de ese orden de clasificación. Este índice puede servir eficientemente:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND created_at >= '2024-01-01'

Pero no puede servir eficientemente WHERE created_at >= '2024-01-01' solo, porque las entradas para un rango de fechas determinado están dispersas en todos los clientes; no puede usar un directorio telefónico ordenado por apellido para encontrar a todos los llamados "Anna". El índice solo es utilizable cuando sus condiciones restringen un prefijo de su lista de columnas, comenzando desde la columna más a la izquierda. Esto significa que (customer_id, created_at) y (created_at, customer_id) son índices diferentes que sirven a consultas diferentes, y el orden de las columnas debe seguir sus patrones de consulta más importantes. Una regla general común: coloque las columnas filtradas por igualdad primero, luego la columna de rango o de ordenación.

Cuándo un índice no ayuda

• Baja selectividad: filtrar WHERE is_active = true en una tabla donde el 90% de las filas están activas. El planificador escaneará en su lugar.
• Funciones o expresiones en la columna: WHERE LOWER(email) = 'x@y.com' no puede usar un índice simple en email, porque el índice almacena valores sin procesar, no transformados. (Algunas bases de datos admiten índices de expresiones, pero el índice simple no se usará).
• Comodines iniciales: WHERE name LIKE '%son' no puede usar un B-tree, porque el orden ordenado solo ayuda cuando se conoce el prefijo.
• Omitir la columna más a la izquierda de un índice compuesto, como se describió anteriormente.
• Tablas muy pequeñas: para unos pocos cientos de filas, un escaneo ya es rápido; el índice agrega costo de escritura sin beneficio.
• Las discrepancias de tipo o las conversiones implícitas en la columna indexada también pueden evitar el uso del índice.

Dos pequeños ejemplos

Un índice útil. Suponga que su aplicación ejecuta constantemente:

SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Entonces este índice encaja perfectamente:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

La base de datos salta a las entradas del cliente 42, que ya están ordenadas por created_at, lee las 20 más recientes y se detiene. Es rápido en una tabla de cualquier tamaño, y también sirve para búsquedas simples por customer_id gracias al prefijo izquierdo.

Un índice problemático. Suponga que en su lugar crea:

CREATE INDEX idx_orders_status ON orders (status);

donde status tiene tres valores posibles y la mayoría de las filas son 'shipped'. Una consulta como SELECT * FROM orders WHERE status = 'shipped' coincide con la mayor parte de la tabla, por lo que el planificador hará un escaneo de tabla de todos modos. El índice rara vez o nunca se usa, sin embargo, cada inserción y cada actualización de estado paga para mantenerlo. Esto es una pérdida neta. (Una excepción que vale la pena conocer: indexar una columna de baja cardinalidad puede valer la pena cuando un valor es raro y se consulta mucho, como el pequeño conjunto de pedidos 'pending', pero la versión genérica anterior es un error clásico).

Lista de verificación práctica antes de agregar un índice

1. Identifique primero la consulta lenta real; no indexe especulativamente. Observe los patrones de consulta reales y use EXPLAIN para ver el plan actual.
2. Verifique la selectividad: ¿las consultas típicas que usan este índice devolverán una pequeña fracción de la tabla? Si no, reconsidera.
3. Para filtros y ordenaciones multijugador, diseñe un índice compuesto con el orden de columnas correcto (columnas de igualdad primero, luego columnas de rango/ordenación) en lugar de varios índices de una sola columna.
4. Confirme la regla del prefijo izquierdo: ¿su consulta más común restringe la primera columna del índice?
5. Asegúrese de que la consulta no anule el índice con funciones, comodines iniciales o conversiones de tipo en la columna indexada.
6. Pondere el tráfico de escritura: en tablas con muchas escrituras, cada índice adicional tiene un costo real. Elimine los índices que se superponen o son prefijos de otros.
7. Verifique si un índice existente ya cubre la consulta antes de crear uno nuevo.
8. Después de crear el índice, verifique con EXPLAIN que el planificador realmente lo usa y mida el tiempo de consulta antes y después.
9. Revise y elimine periódicamente los índices no utilizados; cuestan escrituras y almacenamiento para siempre.

El modelo mental central a mantener: un índice es una estructura de búsqueda ordenada por la que paga en cada escritura para que las lecturas específicas y selectivas sean baratas. Agregue uno cuando pueda nombrar la consulta que sirve y demostrar que ayuda.

Hablemos de los índices de bases de datos. Probablemente has escrito consultas SQL que funcionan muy bien en tu máquina de desarrollo con unos pocos cientos de filas. Pero cuando una tabla crece hasta tener millones de filas, algunas de esas consultas pueden volverse increíblemente lentas. Un índice es la herramienta más común que usamos para resolver este problema.

¿Qué es un Índice de Base de Datos? Una Analogía

Piense en un índice como el índice al final de un libro de texto. Si quieres encontrar todas las páginas que mencionan "Árboles B", no lees el libro entero de principio a fin. En su lugar, vas al índice, buscas la entrada de "Árboles B" y ves una lista ordenada de números de página (por ejemplo, 112, 254, 256). Esto es mucho más rápido.

Un índice de base de datos funciona de la misma manera. Es una estructura de datos separada que la base de datos mantiene. Contiene una copia de los datos de una o más columnas en orden ordenado, junto con un puntero a la fila original en la tabla. Cuando consultas datos en esa columna, la base de datos puede usar este índice ordenado para encontrar rápidamente los punteros a las filas que necesitas, en lugar de escanear toda la tabla fila por fila (un "escaneo completo de tabla").

Cómo Funciona: El Árbol B

La mayoría de las bases de datos relacionales utilizan un Árbol B para sus índices estándar. No necesitas conocer los detalles profundos de la informática, pero a un alto nivel, un Árbol B es una estructura de árbol autoequilibrada. Imagina una jerarquía ramificada. Para encontrar un valor, la base de datos comienza en el nodo raíz y sigue un camino por las ramas. Cada paso reduce drásticamente la búsqueda. Esto permite a la base de datos encontrar cualquier valor en un conjunto de datos masivo con muy pocos pasos, haciendo que las búsquedas sean increíblemente eficientes (complejidad de tiempo logarítmica, o O(log n)) en comparación con un escaneo completo de tabla (complejidad de tiempo lineal, o O(n)).

Las Contrapartidas: Velocidad de Escritura y Almacenamiento

Los índices no son gratuitos. Vienen con dos costos principales:

1. Almacenamiento: Un índice es una estructura de datos física almacenada en disco. Duplica los datos de tu tabla, por lo que consume espacio de almacenamiento adicional.
2. Escrituras más lentas: Esta es la contrapartida más importante. Cuando INSERTAS, ACTUALIZAS o ELIMINAS una fila, la base de datos no solo cambia la tabla. También debe actualizar cada índice que contenga las columnas afectadas para mantenerlo sincronizado y ordenado. Si tienes una tabla con cinco índices, una instrucción INSERT en realidad resulta en seis escrituras: una para la tabla y una para cada índice. Esto puede ralentizar significativamente las aplicaciones con muchas escrituras.

Conceptos Clave para la Indexación Práctica

1. Selectividad
La selectividad se refiere a cuántos valores únicos hay en una columna en relación con el número total de filas.

• Alta Selectividad: Una columna donde la mayoría de los valores son únicos, como user_id o email_address. Los índices en estas columnas son muy efectivos porque pueden reducir una búsqueda a solo una o unas pocas filas muy rápidamente.
• Baja Selectividad: Una columna con muy pocos valores únicos, como un indicador booleano is_active (verdadero/falso) o una columna status ('pendiente', 'activo', 'inactivo'). Un índice en una columna de baja selectividad a menudo es inútil. Si buscas todos los usuarios activos, eso podría ser el 80% de tu tabla. Es más rápido para la base de datos leer toda la tabla que ir y venir entre un índice y los datos de la tabla.

2. Índices Compuestos y la Regla del Prefijo Más Izquierdo
Un índice compuesto es un índice en dos o más columnas, como CREATE INDEX idx_name ON users (last_name, first_name);.

El orden de las columnas es crítico. Esta es la regla del prefijo más izquierdo. Un índice en (A, B, C) puede ser utilizado por la base de datos para acelerar las consultas que filtran por:

• A
• A y B
• A, B y C

Sin embargo, no puede ser utilizado para consultas que solo filtran por B, C o B y C. La base de datos necesita las primeras columnas en la definición del índice para comenzar su búsqueda.

Ejemplos de SQL

Supongamos una tabla products con millones de filas.

Buen Ejemplo de Índice:
Muchos usuarios buscan productos por un código de producto único.

-- La Consulta
SELECT name, price FROM products WHERE product_code = 'ABC-12345';

-- La Solución: Un índice en una columna de alta selectividad
CREATE INDEX idx_products_product_code ON products (product_code);

Este índice es muy efectivo porque product_code es único (alta selectividad), y el índice permite a la base de datos encontrar la fila del producto exacto casi instantáneamente.

Ejemplo de Índice Menos Útil:
Queremos encontrar todos los productos que están actualmente disponibles para la venta.

-- La Consulta
SELECT * FROM products WHERE is_available = TRUE;

-- El Índice Problemático
CREATE INDEX idx_products_is_available ON products (is_available);

Este índice no es muy útil. La columna is_available tiene baja selectividad (solo dos valores: verdadero y falso). Si el 90% de los productos están disponibles, la base de datos probablemente ignorará este índice y realizará un escaneo completo de tabla, ya que es más eficiente.

Lista de Verificación: Cuándo Añadir un Índice

Antes de añadir un índice, hazte estas preguntas:

1. ¿Es la tabla grande? (Los índices en tablas pequeñas tienen poco o ningún beneficio).
2. ¿Está una consulta específica funcionando lentamente? (No añadas índices a ciegas; añádelos para solucionar un problema de rendimiento conocido).
3. ¿Filtro frecuentemente en esta columna en una cláusula WHERE o la uso en un JOIN? (Estos son los candidatos principales para indexar).
4. ¿Tiene la columna alta selectividad? (¿Reducirá el índice significativamente el número de filas a comprobar?).
5. Para un índice compuesto, ¿mi consulta utiliza las columnas más a la izquierda del índice?
6. ¿Es esta una tabla con muchas lecturas? (Si la tabla tiene escrituras muy frecuentes pero pocas lecturas, el costo de mantener el índice podría superar los beneficios de lectura).