Respuesta

Índices — estructuras de datos especiales (normalmente basadas en árboles B) que sirven para la búsqueda rápida de datos en una determinada columna o conjunto de columnas de una tabla. Los índices aceleran la selección, ordenación y filtrado, reduciendo el número de filas examinadas.

Tipos de índices:

• Comunes (B-tree, hash)
• Compuestos (multicolumna)
• Únicos (aseguran la unicidad de los valores)
• Cubrientes (covering)
• De texto completo (full-text)

Los índices aceleran:

• WHERE ... = ...
• JOIN por columna indexada
• ORDER BY y GROUP BY por columna indexada

Pueden ralentizar:

• Inserción, actualización, eliminación — los índices requieren operaciones adicionales para mantener su estructura.
• Si las consultas usan a menudo columnas que no tienen índices, los índices serán inútiles, y en caso de inserciones masivas, incluso perjudiciales.

Ejemplo de creación de un índice:

CREATE INDEX idx_user_email ON users (email);

Ejemplo donde el índice no ayuda:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- si el índice se construyó en email, pero la consulta usa la función lower(email), ¡el índice no se utiliza!

Pregunta capciosa

Si se agrega un índice a todas las columnas de la tabla, ¿siempre se acelerará la ejecución de todas las consultas SELECT?

Respuesta:
No. Los índices solo aceleran aquellas consultas donde el filtrado o la ordenación se realizan estrictamente por la columna indexada sin aplicar funciones u operaciones que impidan el uso del índice. Un número excesivo de índices no solo ralentiza INSERT/UPDATE/DELETE, sino que también ocupa mucho espacio, y algunas consultas complejas pueden incluso obviar los índices (por ejemplo, al escanear por rango con una expresión).

Ejemplo:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- si el índice está solo en order_date, el índice no funciona debido a la función year()

---

Historia

En el sistema de correo electrónico se creó un trigger que generaba un índice para cada campo de uso frecuente. Después de seis meses, el rendimiento del sistema disminuyó — cada inserción o modificación de fila tardaba 4-5 veces más tiempo. Tras una auditoría, se redujo el número de índices, y el sistema se aceleró.

---

Historia

En la plataforma publicitaria se encontraron frecuentemente consultas SELECT con un filtro por substring(url, 1, 10). A pesar de tener un índice en url, SQL no utilizó el índice debido a la función substring. La solución fue introducir un campo separado para tal selección y un índice sobre él.

---

Historia

En el programa de lealtad se creó un índice compuesto en los campos (customer_id, shop_id). Al hacer consultas solo por shop_id, el índice no se aplicó, y se realizó un escaneo completo de la tabla (full scan). Esto llevó a la pérdida de los beneficios del índice en los cálculos de bonificaciones. La optimización ayudó: un índice separado por shop_id.