Índices en Bases de Datos MySQL

1. Introducción

En una base de datos, los índices son estructuras auxiliares que mejoran la velocidad de recuperación de datos. La comparación clásica es con el índice de un libro: en vez de leer página por página, buscamos una entrada y vamos directo a la parte que nos interesa.

Cuando una tabla no tiene un índice útil para una consulta, MySQL puede verse obligado a recorrer muchas filas, o incluso toda la tabla. Ese recorrido completo suele asociarse con un full scan. En tablas pequeñas esto puede pasar desapercibido, pero en tablas grandes el costo crece mucho.

Un índice se crea sobre una o más columnas y mantiene una estructura organizada con los valores indexados y referencias que permiten llegar más rápido a las filas reales.

2. Concepto básico

Un índice en MySQL es un objeto de base de datos que puede crearse sobre una o más columnas de una tabla. Su objetivo es permitir un acceso más rápido a los datos en las consultas que utilizan esas columnas.

Por ejemplo, si tenemos miles o millones de clientes y queremos buscar a uno por apellido, sin índice MySQL tendría que revisar fila por fila. Con un índice sobre apellido, puede ubicar mucho más rápido las filas candidatas.

3. ¿Por qué usar índices?

Ventajas principales

• Velocidad en consultas de búsqueda, especialmente con WHERE, JOIN, ORDER BY y GROUP BY.
• Eficiencia en búsquedas por rango cuando el tipo de índice lo permite, por ejemplo con BETWEEN, >, <, >= o <=.
• Ordenamiento más eficiente, ya que ciertos índices ya mantienen datos organizados de una forma útil para ordenar resultados.
• Optimización de búsquedas sobre columnas consultadas con frecuencia.
• Unicidad garantizada cuando se utilizan índices UNIQUE.

Desventajas y costos

• Ocupan espacio adicional en disco o memoria.
• Ralentizan operaciones de INSERT, UPDATE y DELETE, porque al cambiar los datos también debe mantenerse el índice.
• Un exceso de índices puede degradar el rendimiento general.
• Un índice mal elegido puede existir y aun así no ser realmente útil para ciertas consultas.

4. Tipos de índices según su estructura interna

En MySQL, los tipos concretos dependen también del motor de almacenamiento. En la práctica, para InnoDB, la enorme mayoría de los índices que veremos se basan en B-Tree o, más precisamente, en B+Tree.

En la práctica, más del 95% de los índices que se crean en un curso inicial de MySQL serán índices tipo B-Tree.

5. Índices B-Tree

Los índices B-Tree son árboles balanceados. Su objetivo es organizar los valores de forma jerárquica para poder buscar muy rápido sin recorrer toda la tabla.

¿Cómo funcionan?

El árbol está organizado en niveles. Hay una raíz, puede haber nodos intermedios y finalmente nodos hoja. En cada nivel, el índice descarta grandes porciones de datos hasta llegar a la ubicación probable del valor buscado.

La idea se parece a una búsqueda binaria, pero adaptada a estructuras de almacenamiento más complejas y eficientes para bases de datos.

Características

• Soportan búsquedas exactas con =.
• Soportan búsquedas por rango con <, <=, >, >=, BETWEEN.
• Son útiles para ORDER BY.
• Sirven como base de muchísimos índices en MySQL.

Limitaciones

• Su mantenimiento tiene costo cuando insertamos, actualizamos o eliminamos registros.
• No reemplazan a Full-Text cuando lo que buscamos son palabras dentro de textos largos.

6. Índice Clustered (Agrupado) vs Non-Clustered (No Agrupado)

Índice Clustered (Agrupado)

• Determina el orden físico de almacenamiento de las filas.
• Solo puede haber uno por tabla.
• En InnoDB, la clave primaria normalmente se usa como índice agrupado.
• Los nodos hoja contienen todos los datos de la fila completa.

¿Dónde se guarda?

En InnoDB, el índice agrupado forma parte de la organización principal de la tabla. Eso significa que los datos de las filas y el índice agrupado están estrechamente ligados: las hojas del árbol son, en la práctica, las filas completas. Por eso suele explicarse que “la tabla está organizada por el índice agrupado”.

Índice Non-Clustered (No Agrupado)

• No cambia el orden físico de la tabla.
• Puede haber varios por tabla.
• Sus hojas guardan el valor indexado y una referencia lógica para encontrar la fila real.
• En InnoDB, esa referencia suele ser la clave primaria de la fila.

¿Dónde se guarda?

El índice no agrupado se guarda en una estructura separada de la organización principal de la tabla. No contiene la fila completa, sino la clave indexada y la referencia necesaria para llegar al dato real. Por eso, cuando una consulta necesita columnas no presentes en ese índice, MySQL suele hacer una segunda navegación hacia el índice agrupado.

Importante: ¿qué pasa si no hay clave primaria?

Este punto es clave y conviene entenderlo bien. En InnoDB, el motor necesita una forma de identificar de manera única cada fila para organizar el índice agrupado.

• Si la tabla tiene PRIMARY KEY, esa PK será el índice agrupado.
• Si no hay PK, InnoDB puede aprovechar una columna o conjunto de columnas UNIQUE y NOT NULL como identificador agrupado.
• Si tampoco existe una opción adecuada de ese tipo, InnoDB crea una clave oculta interna, a menudo explicada didácticamente como un rowid oculto, para poder identificar las filas.

¿Qué diferencia hay en la lectura?

Cuando una consulta usa el índice agrupado y necesita columnas de la propia fila, muchas veces llega directamente al dato completo en una sola navegación del árbol. En cambio, cuando usa un índice no agrupado, primero encuentra la clave indexada y luego necesita una segunda búsqueda para ir al índice agrupado y obtener el resto de la fila. A esto suele explicárselo como una especie de doble salto o lookup.

Resumen ilustrativo y comparativo

Índice agrupado

• Nodos intermedios: contienen claves que dividen rangos y punteros a nodos hijos.
• Nodos hoja: contienen los datos completos de las filas.
• Las hojas suelen estar enlazadas secuencialmente para facilitar búsquedas por rango.

Índice no agrupado

• Nodos intermedios: contienen los valores de la columna indexada que dividen el árbol.
• Nodos hoja: contienen la clave del índice y la clave primaria correspondiente.
• La clave primaria actúa como referencia para llegar a la fila completa en el índice agrupado.

Comparación de punteros

• Agrupado: hojas con acceso directo a los datos de la fila.
• No agrupado: hojas con referencia a la clave primaria del índice agrupado.

Comparación de contenido de nodos

• Agrupado: hojas con datos completos; intermedios con claves ordenadoras.
• No agrupado: hojas con valor indexado + PK; intermedios con valores del índice.

7. Índices compuestos o multicolumna

Un índice compuesto es un índice definido sobre dos o más columnas.

CREATE INDEX idx_apellido_nombre ON clientes (apellido, nombre);

El orden de las columnas es crítico. Si el índice está definido como (apellido, nombre), puede servir para:

• búsquedas por apellido;
• búsquedas por apellido y nombre;
• ciertos casos donde se filtra por apellido y además se ordena por nombre.

Pero no sirve igual de bien para búsquedas solo por nombre, porque el árbol está ordenado primero por apellido.

¿Cómo impacta una PK compuesta en el índice agrupado?

Si definimos una clave primaria compuesta, por ejemplo (sucursal_id, cliente_id), esa combinación pasa a ser la clave que identifica físicamente las filas en el índice agrupado.

Además, los índices secundarios deben guardar esa PK compuesta en sus hojas. Eso significa que:

• los índices secundarios pueden ocupar más espacio;
• el orden físico de la tabla queda condicionado por esa PK compuesta;
• conviene que la PK compuesta esté bien pensada y no sea innecesariamente ancha.

8. Índices únicos (UNIQUE)

Un índice único garantiza que no existan dos filas con el mismo valor en la columna o conjunto de columnas indexadas.

CREATE UNIQUE INDEX idx_email_unico ON clientes (email);

También puede aparecer al definir una restricción UNIQUE dentro de la tabla.

Además de acelerar búsquedas, los índices únicos cumplen una función de integridad: impiden duplicados no permitidos.

9. ¿Cómo utilizan los índices las consultas?

Consulta sin índice

SELECT * FROM clientes WHERE apellido = 'González';

Si no existe un índice útil sobre apellido, MySQL deberá revisar muchas filas de la tabla para encontrar coincidencias.

Consulta con índice

CREATE INDEX idx_apellido ON clientes (apellido);

SELECT * FROM clientes WHERE apellido = 'González';

Ahora MySQL puede usar el índice para localizar mucho más rápido las filas donde apellido = 'González'.

10. EXPLAIN: cómo verificar si una consulta usa índices

EXPLAIN es una herramienta fundamental para analizar cómo MySQL planea ejecutar una consulta. Antes de un SELECT, se puede anteponer EXPLAIN para observar si MySQL va a usar un índice, qué tabla lee, qué tipo de acceso utilizará y cuántas filas estima revisar.

EXPLAIN
SELECT *
FROM clientes
WHERE apellido = 'González';

¿Para qué sirve?

• Para ver si una consulta está usando un índice o no.
• Para detectar recorridos completos de tabla cuando no serían deseables.
• Para comparar el efecto de crear, eliminar o modificar un índice.
• Para pensar el rendimiento, no solo la sintaxis.

¿Qué campos conviene mirar?

• id: identifica la etapa o bloque de ejecución. En consultas simples suele aparecer 1.
• select_type: indica qué tipo de SELECT es. En una consulta simple suele aparecer SIMPLE.
• table: señala qué tabla está leyendo MySQL en ese paso.
• partitions: muestra particiones usadas si la tabla está particionada. Si no lo está, suele verse NULL.
• type: es uno de los campos más importantes. Describe el tipo de acceso. En general, cuanto más selectivo y menos filas necesite revisar, mejor.
• possible_keys: muestra qué índices podrían servir para esa consulta.
• key: muestra qué índice eligió realmente MySQL.
• key_len: indica cuántos bytes del índice está usando. Ayuda mucho a analizar índices compuestos.
• ref: muestra con qué valor o columna se compara el índice.
• rows: estima cuántas filas deberá revisar MySQL.
• filtered: porcentaje estimado de filas que pasarán el filtro después del acceso.
• Extra: ofrece información adicional, por ejemplo Using where, uso de temporales o de ordenamientos adicionales.

Ejemplo práctico completo: antes y después de crear un índice

Este script sirve para laboratorio o práctica en clase. La idea es crear una tabla sencilla, cargar registros, ejecutar una consulta sin índice, observar EXPLAIN, luego crear el índice y volver a ejecutar exactamente la misma consulta.

Paso 1: crear una base de prueba y una tabla

CREATE DATABASE IF NOT EXISTS prueba_indices;
USE prueba_indices;

DROP TABLE IF EXISTS clientes;

CREATE TABLE clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    apellido VARCHAR(50) NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    ciudad VARCHAR(50),
    edad INT
);

Paso 2: cargar registros de ejemplo

INSERT INTO clientes (apellido, nombre, ciudad, edad) VALUES
('González', 'Ana', 'Mar del Plata', 21),
('Pérez', 'Juan', 'Batán', 19),
('Gómez', 'Lucía', 'Mar del Plata', 25),
('Rodríguez', 'Carlos', 'Miramar', 31),
('Fernández', 'Marta', 'Mar del Plata', 28),
('López', 'Sofía', 'Necochea', 24),
('González', 'Pedro', 'Balcarce', 36),
('Martínez', 'Laura', 'Mar del Plata', 22),
('González', 'Diego', 'Batán', 27),
('Sánchez', 'Paula', 'Miramar', 20),
('Díaz', 'Marina', 'Mar del Plata', 26),
('González', 'Nora', 'Necochea', 33);

Paso 3: ejecutar EXPLAIN antes de crear el índice

EXPLAIN
SELECT *
FROM clientes
WHERE apellido = 'González';

Antes de crear el índice

En esta primera imagen se ve un escenario sin un índice útil sobre apellido. La consulta puede ejecutarse, pero MySQL no dispone todavía de una estructura específica para llegar rápido a las filas buscadas.

Paso 4: crear el índice B-Tree sobre la columna buscada

CREATE INDEX idx_clientes_apellido
ON clientes (apellido);

En MySQL, salvo indicación contraria y según el motor, este índice será de tipo B-Tree, adecuado para búsquedas exactas como apellido = 'González'.

Paso 5: repetir exactamente la misma consulta con EXPLAIN

EXPLAIN
SELECT *
FROM clientes
WHERE apellido = 'González';

Después de crear el índice

Ahora sí existe un índice sobre apellido y MySQL puede aprovecharlo. Se ve claramente que el plan de ejecución cambió.

Comparación directa: antes vs después

Variación útil: consulta con ORDER BY

EXPLAIN
SELECT id, apellido, nombre
FROM clientes
WHERE apellido = 'González'
ORDER BY nombre;

En este caso, el índice sobre apellido puede ayudar al filtrado, pero no necesariamente resolver por sí solo el ordenamiento por nombre. Aquí aparece la utilidad de los índices compuestos.

Variación útil: índice compuesto

CREATE INDEX idx_clientes_apellido_nombre
ON clientes (apellido, nombre);

EXPLAIN
SELECT id, apellido, nombre
FROM clientes
WHERE apellido = 'González'
ORDER BY nombre;

Esta prueba permite ver por qué el orden de las columnas dentro del índice compuesto importa tanto. Si primero filtramos por apellido y después ordenamos por nombre, un índice (apellido, nombre) puede resultar muy conveniente.

11. Índices Full-Text (texto completo)

Los índices Full-Text están diseñados para búsquedas en grandes bloques de texto. No son simplemente una versión rápida de LIKE '%palabra%'; trabajan de otra manera.

¿Cómo funcionan?

En lugar de buscar coincidencias carácter por carácter en todo el texto, el motor tokeniza el contenido, es decir, lo divide en palabras o unidades llamadas tokens. Luego las almacena en una estructura que permite búsquedas por relevancia.

¿Qué es un token?

Un token es una unidad elemental de información. En un índice Full-Text, normalmente un token es una palabra relevante extraída del texto.

Proceso de tokenización

1. Extracción de palabras: se toma el contenido de una columna de texto y se lo separa en palabras o términos.
2. Eliminación de palabras irrelevantes: se descartan muchas palabras comunes o stop words, como “y”, “de”, “la”, “el”, según configuración y motor.
3. Indexación de tokens: se guardan los tokens junto con información que permite saber en qué filas aparecen.

¿Dónde se guarda?

Didácticamente puede explicarse como una estructura interna asociada a la tabla que relaciona tokens con filas. No es la misma organización que un B-Tree clásico de igualdad o rango. Lo importante es entender que el motor mantiene un índice especializado para palabras y referencias a los registros donde aparecen.

Ejemplo de idea conceptual

Si una fila contiene el texto “Los avances en la inteligencia artificial son sorprendentes”, el sistema puede quedarse con tokens como avances, inteligencia, artificial y sorprendentes, descartando otras palabras comunes.

Ejemplo de creación y uso

CREATE TABLE articulos (
    id INT PRIMARY KEY,
    titulo VARCHAR(200),
    contenido TEXT,
    FULLTEXT(titulo, contenido)
);

SELECT *
FROM articulos
WHERE MATCH(titulo, contenido) AGAINST('base de datos');

También puede crearse con una instrucción separada:

CREATE FULLTEXT INDEX idx_contenido ON articulos (contenido);

Ventajas

• Búsquedas rápidas y precisas en textos largos.
• Permiten trabajar con relevancia.
• Son muy superiores a un LIKE '%texto%' cuando el objetivo es buscar contenido textual grande.

Limitaciones

• Solo funcionan en motores y versiones que lo soporten.
• No son ideales para igualdad estricta de valores pequeños.
• Las stop words y la configuración pueden afectar resultados.

Optimización de búsquedas Full-Text

• Definirlos sobre columnas con mucho texto y búsqueda frecuente.
• Tener en cuenta las stop words.
• Usar consultas lo bastante específicas.
• Aplicar mantenimiento si la tabla cambia mucho.

12. Índices Hash

¿Qué son?

Un índice Hash utiliza una función hash para convertir un valor en un identificador y localizar el dato asociado de forma directa. No organiza los datos por orden, sino por el resultado del hash.

¿Cómo funcionan?

• Cuando se indexa una columna, el motor calcula un hash de sus valores.
• Cuando buscamos un valor, calcula el hash del valor buscado.
• Con ese hash va directamente a la ubicación asociada.

¿Dónde se guardan?

En el caso típico de tablas MEMORY, los índices y los datos se mantienen en memoria RAM mientras la tabla exista y el servidor permanezca activo. Esto explica su gran velocidad y también una de sus limitaciones más importantes: su comportamiento está pensado para datos temporales o altamente volátiles, no para persistencia clásica.

Fortaleza principal

Son extremadamente rápidos para búsquedas de igualdad, como = o IN.

Debilidad principal

No tienen orden lógico. Por eso no sirven para:

• búsquedas por rango;
• BETWEEN;
• <, >;
• ORDER BY.

Ejemplo

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255),
    precio DECIMAL(10, 2),
    tipo_memoria VARCHAR(50)
) ENGINE=MEMORY;

CREATE INDEX idx_tipo_hash ON productos (tipo_memoria) USING HASH;

SELECT * FROM productos WHERE tipo_memoria = 'SSD';

Ventajas

• Velocidad extrema para igualdad.
• Eficiencia de recursos en ciertos casos.

Limitaciones

• Se asocian principalmente al motor MEMORY.
• No sirven para rangos.
• No soportan ordenamiento por el propio índice.
• Pueden existir colisiones hash.

¿Qué es una colisión?

Una colisión ocurre cuando dos valores distintos producen el mismo hash. El motor puede resolverlo, pero si hay muchas colisiones el rendimiento puede deteriorarse.

¿Cuándo usar índices Hash?

• Si trabajamos con tablas MEMORY.
• Si las búsquedas son principalmente de igualdad.
• Si no necesitamos rangos ni ORDER BY.

13. El motor MEMORY: cuándo podría usarse

El motor MEMORY almacena sus datos en RAM. Eso lo hace muy rápido, pero también implica limitaciones importantes. Didácticamente conviene pensar que no es el motor común para una base transaccional persistente, sino una herramienta para casos muy puntuales.

Casos en los que podría resultar útil

• Tablas temporales de trabajo dentro de procesos intermedios.
• Datos de apoyo o caché que pueden reconstruirse fácilmente.
• Sesiones, resultados parciales o estructuras auxiliares donde prima la velocidad y no la persistencia a largo plazo.
• Pruebas de laboratorio para mostrar diferencias entre B-Tree y Hash.

Cuándo no conviene

• Cuando los datos deben sobrevivir normalmente a reinicios del servidor.
• Cuando hay grandes volúmenes de información que podrían consumir demasiada RAM.
• Cuando necesitamos soporte típico de motores como InnoDB para escenarios transaccionales más completos.

14. Mantenimiento de índices

Los índices también requieren mantenimiento. Con el tiempo, inserciones, actualizaciones y eliminaciones pueden afectar su organización y el rendimiento general.

Optimizar índices

OPTIMIZE TABLE nombre_tabla;

Esta operación puede ayudar a reorganizar y optimizar la estructura asociada a la tabla y sus índices, dependiendo del motor.

Modificar un índice

MySQL no permite, en general, cambiar directamente las columnas de un índice existente con una instrucción tipo “editar índice”. Lo habitual es:

1. Eliminar el índice viejo.
2. Crear uno nuevo con la estructura deseada.

DROP INDEX nombre_indice ON nombre_tabla;

CREATE INDEX nuevo_nombre_indice
ON nombre_tabla (nueva_columna1, nueva_columna2);

Eliminar un índice

DROP INDEX nombre_indice ON nombre_tabla;

15. Buenas prácticas

• No crear índices “por si acaso”. Primero analizar consultas reales.
• Evaluar si la columna tiene suficiente utilidad y selectividad.
• Usar índices compuestos cuando varias columnas se consultan juntas con frecuencia.
• Recordar que demasiados índices perjudican inserciones y actualizaciones.
• Aplicar EXPLAIN antes y después de cambios importantes.
• Si hay claves foráneas y JOIN frecuentes, pensar el indexado de manera explícita.

Sobre las FOREIGN KEY

Conviene recordar que una FK sin buen soporte de indexación puede volver lentos los JOIN y ciertas validaciones. En la práctica, siempre es recomendable revisar cómo quedaron indexadas las columnas implicadas en relaciones.

16. Preguntas guía con respuesta

17. Cierre

La idea central es simple: los índices aceleran lecturas, pero tienen costo de mantenimiento. Por eso deben diseñarse en función de consultas reales. Entender la diferencia entre buscar datos, ordenar datos y mantener datos ayuda a pensar la base de datos con criterio y no solo desde la sintaxis.