ÍNDICES EN MYSQL: QUÉ SON Y CÓMO OPTIMIZAR SU USO

Un índice es un puntero a una fila de una determinada tabla de nuestra base de datos, es decir, asocia el valor de una determinada columna (o el conjunto de valores de una serie de columnas) con las filas que contienen ese valor (o valores) en las columnas que componen el puntero.
Los índices mejoran el tiempo de recuperación de los datos en las consultas realizadas contra nuestra base de datos.
Pero ojo que los índices tiene sus desventajas:
  • La creación de índices implica un aumento en el tiempo de ejecución sobre aquellas consultas de inserción, actualización y eliminación realizadas sobre los datos que afectan a el índice (ya que se tiene que actualizar).
  • Los índices ocupan espacio del almacenamiento.

Gracias al uso de índices, se reduce, de forma considerable, el tiempo de ejecución de las consultas de tipo SELECT. La mejora de dicha ejecución será mayor cuanto mayor cantidad de datos tengan las tablas de la base de datos con la que estemos trabajando. MySQL emplea los índices para encontrar las filas que contienen los valores específicos de las columnas empleadas en la consulta de una forma más rápida.MySQL usa los índices para:
  • En las condiciones WHERE de la consulta y las columnas estén indexadas.
  • Cuando se realizan consultas usando JOIN. Pero es importante que los índices sean del mismo tipo y tamaño. Por ejemplo: una operación de tipo JOIN sobre dos columnas que tengan un índice del tipo INT(10).
  • Reducir el tiempo de ejecución de las consultas con ordenación (ORDER BY) o agrupamiento (GROUP BY) si todas las columnas presentes en los criterios forman parte de un índice.
Hay diferentes tipos de índices en MySQL:
  • INDEX (NON-UNIQUE): El valor de la columna no tiene porque ser único. Este índice se usa para mejora de rendimiento.
  • UNIQUE: El valor d ela columna tiene que ser único.
  • PRIMARY: Este tipo de índice se refiere a un índice en el que todas las columnas deben tener un valor único (al igual que en el caso del índice UNIQUE) pero con la limitación de que sólo puede existir un índice PRIMARY en cada una de las tablas.
  • FULLTEXT: Este índice se utiliza para búsquedas de texto. Este tipo de índices sólo están soportados por InnoDB y MyISAM. Puedes leer sobre este indice en este post.
  • SPATIAL: estos índices se emplean para realizar búsquedas sobre datos que componen formas geométricas representadas en el espacio. Este tipo de índices sólo están soportados por InnoDB y MyISAM.
  • COMPUESTOS: mejoran drásticamente el rendimiento de consultas que filtran, ordenan o agrupan por múltiples columnas simultáneamente, al incluir dos o más columnas en un solo índice. Siguen la regla del prefijo izquierdo (order matters), siendo ideales para búsquedas que utilizan las primeras columnas.
  • PREFIX: son índices creados sobre los primeros caracteres de una columna de cadena larga (VARCHAR, TEXT, BLOB), en lugar de indexar el valor completo. Optimizan el rendimiento y ahorran espacio en disco cuando las columnas son extensas y no requieren la cadena entera para distinguir registros.
  • DESCENDING INDEXES: estructuras de datos que almacenan los valores de las claves en orden descendente (DESC) en lugar del orden ascendente predeterminado. Optimizan consultas que utilizan ORDER BY con orden inverso, evitando el costoso ordenamiento en memoria.
  • FUNCTIONAL / EXPRESSION INDEXES: permiten indexar el resultado de una expresión o función en lugar de valores de columna directos. Aceleran las búsquedas WHERE, ORDER BY o GROUP BY que aplican funciones (ej. LOWER(col), JSON_EXTRACT) a los datos, permitiendo al optimizador usar índices cuando normalmente no podría.
  • INVISIBLE INDEXES: son índices que existen y se mantienen actualizados, pero que el optimizador de consultas ignora. Sirven para probar el impacto de eliminar un índice sin borrarlo definitivamente, permitiendo revertir el cambio rápidamente si el rendimiento.
Los índices suponen una mejora en el rendimiento a la hora de obtener datos reduciendo el tiempo de ejecución de la consulta. Esta reducción de tiempo es visible sobre tablas de gran tamaño. Sobre tablas pequeñas, el uso de índices no aporta una mejora considerable de rendimiento. Así que si la tabla donde se van a crear los índices no va a crecer demasiado no es muy aconsejable crear índices sobre ella.
Muchos pensareis que porque no crear índices sobre todos los campos de todas las tablas. Pero eso supondría un problema ya que disminuiría el rendimiento en la inserción, actualización y eliminación ya que después de realizar una de estás transacciones en la base de datos se actualizan los índices, otra desventaja es que dichos índices ocupan espacio físico en disco.

Tipos según la estructura de datos

  • B-TREE Es el más importante y es el predeterminado. Es la estructura por defecto en InnoDB y MyISAM.
    • Permite búsquedas por igualdad (=), rangos (>, <, BETWEEN, LIKE 'abc%'), ordenación (ORDER BY) y combinaciones.
    • Soporta índices compuestos (múltiples columnas).
    • Muy bueno para la mayoría de consultas.
  • HASHSolo Está disponible de forma explícita en el motor MEMORY (y HEAP).
    • Extremadamente rápido para búsquedas de igualdad exacta (= o IN).
    • No soporta rangos (>, <, BETWEEN, LIKE con comodín al inicio) ni ordenación.
    • En InnoDB existe el Adaptive Hash Index (interno y automático), pero no puedes crearlo manualmente.
  • R-TREE Se usa automáticamente cuando creas un SPATIAL INDEX. Optimizado para consultas geoespaciales.
  • Inverted Index (para FULLTEXT)InnoDB usa listas invertidas (no es un B-Tree clásico).

Ejemplo completo de creación de tabla con varios índices

CREATE TABLE productos (
id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- Clustered Index (InnoDB)
skuVARCHAR(50) UNIQUE NOT NULL,
nombre VARCHAR(255),
descripcion TEXT,
precio DECIMAL(10,2),
categoria_id INT,
tagsJSON,
coordenadas POINT,-- Para GIS

INDEX idx_categoria_precio (categoria_id, precio), -- Compuesto
FULLTEXT INDEX ft_nombre_desc (nombre, descripcion),-- Búsqueda texto
SPATIAL INDEX spx_coordenadas (coordenadas),  -- Geoespacial
INDEX idx_lower_nombre ((LOWER(nombre))) -- Functional
) ENGINE=InnoDB;