Funcionamiento de los índices de bases de datos

¿Qué es un índice?

Un índice es una estructura de datos para acelerar las búsquedas en bases de datos. Como el índice de un libro, permite localizar rápidamente la ubicación de los datos objetivo.

¿Por qué acelera?: Sin un índice, es necesario escanear todas las filas (escaneo completo), pero con un índice solo se accede a las filas necesarias.

Índice B-Tree

Es la estructura de índice más común. Se utiliza en InnoDB de MySQL y PostgreSQL.

Características del B-Tree

• Estructura de árbol balanceado
• Todos los nodos hoja tienen la misma profundidad
• Soporta búsquedas por rango
• Mantiene los datos en estado ordenado

Operaciones en las que B-Tree es eficiente

• Búsqueda de coincidencia exacta: WHERE id = 100
• Búsqueda por rango: WHERE price BETWEEN 1000 AND 5000
• Búsqueda por prefijo: WHERE name LIKE 'John%'
• Ordenamiento: ORDER BY created_at

Índice Hash

Es un índice que calcula la posición de los datos usando una función hash.

Características del índice Hash

• Búsqueda de coincidencia exacta muy rápida (O(1))
• No se puede usar para búsquedas por rango
• No se puede usar para ordenamiento

-- Creación de índice Hash en MySQL (motor MEMORY)
CREATE TABLE sessions (
    id VARCHAR(64),
    data TEXT,
    INDEX USING HASH (id)
) ENGINE=MEMORY;

Creación de índices

-- Índice de una sola columna
CREATE INDEX idx_email ON users(email);

-- Índice compuesto
CREATE INDEX idx_name_created ON posts(user_id, created_at);

-- Índice único
CREATE UNIQUE INDEX idx_unique_email ON users(email);

Consideraciones sobre los índices

Desventajas

• Consume capacidad de almacenamiento
• INSERT/UPDATE/DELETE se vuelven más lentos
• Demasiados índices generan sobrecarga

Casos en que el índice no funciona

• Aplicar función a la columna: WHERE YEAR(created_at) = 2024
• Coincidencia por sufijo: WHERE name LIKE '%smith'
• Condiciones negativas: WHERE status != 'deleted'
• Búsqueda de NULL (depende del caso)

Verificar el plan de ejecución: Con el comando EXPLAIN puedes confirmar si la consulta está usando el índice.

Resumen

Los índices apropiados mejoran drásticamente el rendimiento de las consultas, pero crearlos indiscriminadamente puede ser contraproducente. Es importante verificar el plan de ejecución y determinar los índices necesarios.