Cómo crear índices en MySQL

Los índices son una herramienta muy útil para acelerar las consultas en las bases de datos relacionales como MySQL. Un índice es una estructura de datos que almacena los valores de una o más columnas de una tabla, ordenados de forma que faciliten la búsqueda de registros. Los índices se pueden comparar con el índice de un libro, que permite encontrar rápidamente una página con un determinado tema.

En este artículo, vamos a explicar cómo crear índices en MySQL, qué tipos de índices existen, qué ventajas e inconvenientes tienen y cómo usarlos correctamente para optimizar el rendimiento de nuestras consultas.

¿Qué es un índice y para qué sirve?

Un índice es una estructura de datos que almacena los valores de una o más columnas de una tabla, ordenados de forma que faciliten la búsqueda de registros. Los índices se pueden generar sobre cualquier tipo de columna, ya sea numérica, alfanumérica, binaria o espacial.

Los índices sirven para mejorar la velocidad de las consultas que implican condiciones sobre las columnas indexadas. Por ejemplo, si tenemos una tabla con los datos de los clientes de una empresa y queremos buscar los clientes que viven en una determinada ciudad, podemos originar un índice sobre la columna ciudad y así reducir el número de registros que hay que examinar para encontrar los que cumplen la condición.

Los índices también sirven para ordenar los resultados de una consulta según el criterio que especifiquemos. Por ejemplo, si queremos mostrar los clientes ordenados por su apellido, podemos crear un índice sobre la columna apellido y así evitar tener que ordenarlos manualmente después de obtenerlos.

Los índices también se utilizan para garantizar la integridad y la unicidad de los datos. Por ejemplo, si queremos asegurarnos de que no hay dos clientes con el mismo número de identificación, podemos producir un índice único sobre la columna id y así evitar la inserción o actualización de registros duplicados.

¿Qué tipos de índices existen en MySQL?

En MySQL existen varios tipos de índices, según el algoritmo que emplean para almacenar y buscar los valores. Los principales tipos son:

Índice B-tree: Es el tipo más común y versátil. Usa una estructura en forma de árbol binario balanceado, donde cada nodo tiene un número variable de hijos y almacena un rango de valores. Este tipo de índice es adecuado para consultas que implican condiciones de igualdad, desigualdad o rango sobre las columnas indexadas. También permite ordenar los resultados según el orden del índice.
Índice hash: Es un tipo especializado que utiliza una función hash para asignar cada valor a una posición en una tabla. Este tipo de índice es muy rápido para consultas que implican condiciones de igualdad exacta sobre las columnas indexadas, pero no sirve para condiciones de desigualdad o rango ni para ordenar los resultados.
Índice full-text: Es un tipo específico para columnas de texto, que permite buscar palabras o frases dentro del contenido. Este tipo de índice emplea un algoritmo basado en el análisis morfológico y semántico del lenguaje, que tiene en cuenta las variantes gramaticales y los sinónimos. Este tipo de índice es útil para implementar sistemas de búsqueda avanzada o motores de recomendación.
Índice espacial: Es un tipo diseñado para columnas que almacenan datos geográficos o geométricos, como puntos, líneas o polígonos. Este tipo de índice usa un algoritmo basado en el particionamiento del espacio en regiones jerárquicas, que permite realizar operaciones espaciales como calcular distancias, intersecciones o contenciones.

¿Cómo crear un índice en MySQL?

Para crear un índice en MySQL se utiliza la sentencia CREATE INDEX, con la siguiente sintaxis:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX nombre_indice [index_type]
ON nombre_tabla (columna1, columna2, ...)
[index_option]
[algorithm_option | lock_option];

En esta sentencia, se pueden especificar los siguientes elementos:

nombre_indice: Es el nombre que le damos al índice. Debe ser único dentro de la tabla y seguir las reglas de identificadores de MySQL.
UNIQUE | FULLTEXT | SPATIAL: Son modificadores opcionales que indican el tipo de índice que queremos crear. Si no se especifica ninguno, se genera un índice B-tree por defecto. El modificador UNIQUE indica que el índice no permite valores duplicados, lo que implica una restricción de unicidad sobre las columnas indexadas. El modificador FULLTEXT indica que el índice es de tipo full-text, lo que implica que las columnas indexadas deben ser de tipo CHAR, VARCHAR o TEXT. El modificador SPATIAL indica que el índice es de tipo espacial, lo que implica que las columnas indexadas deben ser de tipo GEOMETRY o alguno de sus subtipos.
index_type: Es una opción opcional que permite especificar el algoritmo que se utiliza para crear el índice. Se puede elegir entre USING BTREE o USING HASH. Si no se especifica, se usa el algoritmo por defecto según el tipo de índice. Los índices B-tree y full-text usan el algoritmo B-tree, mientras que los índices hash y espaciales usan el algoritmo hash.
nombre_tabla: Es el nombre de la tabla sobre la que se crea el índice.
columna1, columna2, …: Son las columnas que forman parte del índice. Se pueden especificar una o más columnas, separadas por comas. El orden en que se especifican las columnas determina el orden en que se almacenan y buscan los valores en el índice. Para cada columna, se puede indicar opcionalmente un prefijo de longitud entre paréntesis, que indica el número de caracteres o bytes que se usan para el índice. Esto es útil para reducir el tamaño del índice cuando se trata de columnas de texto muy largas. También se puede indicar opcionalmente una dirección de ordenación, ASC o DESC, que indica si los valores se almacenan en orden ascendente o descendente. Esto es útil para optimizar consultas que requieren un orden específico. Si no se indica ninguna dirección, se usa el orden ascendente por defecto.
index_option: Son opciones adicionales que se pueden especificar para configurar el comportamiento del índice. Se pueden usar las siguientes opciones:
- KEY_BLOCK_SIZE = valor: Indica el tamaño en kilobytes de los bloques de datos del índice. Esto afecta al rendimiento y al espacio ocupado por el índice. El valor debe ser una potencia de 2 entre 1 y 16384. Si no se especifica, se usa el valor por defecto según el tipo de almacenamiento de la tabla.
- WITH PARSER nombre_parser: Indica el nombre del analizador (parser) que se utiliza para crear el índice full-text. Un analizador es un componente que procesa el texto y extrae las palabras o términos que forman parte del índice. MySQL proporciona un analizador por defecto llamado ngram, que divide el texto en fragmentos de longitud fija. También se pueden usar analizadores personalizados instalados como plugins.
- COMMENT ‘texto’: Indica un comentario sobre el índice, que se almacena junto con la definición del mismo. El comentario puede tener hasta 1024 caracteres y debe estar entre comillas simples.
- VISIBLE | INVISIBLE: Indica si el índice es visible o invisible para el optimizador de consultas. Un índice visible es un índice normal, que se usa para acelerar las consultas. Un índice invisible es un índice que se ignora para las consultas, pero se mantiene actualizado con los cambios en la tabla. Esto es útil para probar el impacto de un índice sin eliminarlo o crearlo. Por defecto, los índices son visibles.
- ENGINE_ATTRIBUTE = ‘texto’: Indica un atributo específico del motor de almacenamiento que se aplica al índice. El atributo debe estar entrecomillas simples. El valor del atributo depende del motor de almacenamiento que se use para la tabla. Por ejemplo, para el motor InnoDB se puede usar el atributo COMPRESSION para indicar el algoritmo de compresión que se usa para el índice.
- SECONDARY_ENGINE_ATTRIBUTE = ‘texto’: Indica un atributo específico del motor secundario que se aplica al índice. Un motor secundario es un componente que replica los datos de una tabla en otro formato, para mejorar el rendimiento o la disponibilidad. El atributo debe estar entre comillas simples. El valor del atributo depende del motor secundario que se use para la tabla. Por ejemplo, para el motor secundario NDBCLUSTER se puede usar el atributo HASH para indicar que el índice se crea como un índice hash.
  - algorithm_option | lock_option: Son opciones opcionales que permiten especificar el algoritmo y el nivel de bloqueo que se usan para crear el índice. Se pueden usar las siguientes opciones:ALGORITHM = DEFAULT | INPLACE | COPY: Indica el algoritmo que se usa para crear el índice. El valor DEFAULT indica que se usa el algoritmo por defecto según el tipo de índice y el motor de almacenamiento. El valor INPLACE indica que se crea el índice sin copiar la tabla, lo que reduce el tiempo y el espacio necesarios. El valor COPY indica que se crea una copia de la tabla con el nuevo índice, lo que implica una mayor sobrecarga. No todos los tipos de índices y motores de almacenamiento admiten todos los algoritmos.
  - LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE: Indica el nivel de bloqueo que se aplica a la tabla mientras se crea el índice. El valor DEFAULT indica que se usa el nivel de bloqueo por defecto según el tipo de índice y el motor de almacenamiento. El valor NONE indica que no se bloquea la tabla, lo que permite realizar operaciones de lectura y escritura sobre la misma. El valor SHARED indica que se bloquea la tabla en modo compartido, lo que permite realizar operaciones de lectura pero no de escritura sobre la misma. El valor EXCLUSIVE indica que se bloquea la tabla en modo exclusivo, lo que impide realizar cualquier operación sobre la misma.

Ejemplos de cómo crear índices en MySQL

A continuación, vamos a ver algunos ejemplos prácticos de cómo crear índices en MySQL, usando una tabla llamada clientes con los siguientes campos:

id: Es el identificador único del cliente, de tipo INT y clave primaria.
nombre: Es el nombre del cliente, de tipo VARCHAR(50).
apellido: Es el apellido del cliente, de tipo VARCHAR(50).
email: Es el correo electrónico del cliente, de tipo VARCHAR(100).
ciudad: Es la ciudad donde vive el cliente, de tipo VARCHAR(50).
descripcion: Es una descripción breve del cliente, de tipo TEXT.

Crear un índice B-tree sobre la columna ciudad

Para crear un índice B-tree sobre la columna ciudad, podemos usar la siguiente sentencia:

CREATE INDEX idx_ciudad ON clientes (ciudad);

Este índice nos permitirá acelerar las consultas que busquen clientes por su ciudad, como por ejemplo:

SELECT * FROM clientes WHERE ciudad = 'Madrid';

También nos permitirá ordenar los resultados por la ciudad, como por ejemplo:

SELECT * FROM clientes ORDER BY ciudad;

Crear un índice hash sobre la columna email

Para crear un índice hash sobre la columna email, podemos usar la siguiente sentencia:

CREATE INDEX idx_email USING HASH ON clientes (email);

Este índice nos permitirá acelerar las consultas que busquen clientes por su correo electrónico exacto, como por ejemplo:

SELECT * FROM clientes WHERE email = '[email protected]';

Sin embargo, este índice no nos servirá para consultas que usen condiciones de desigualdad o rango, como por ejemplo:

SELECT * FROM clientes WHERE email LIKE '%gmail.com';

Tampoco nos servirá para ordenar los resultados por el correo electrónico.

Crear un índice full-text sobre la columna descripcion

Para crear un índice full-text sobre la columna descripcion, podemos usar la siguiente sentencia:

CREATE FULLTEXT INDEX idx_descripcion ON clientes (descripcion);

Este índice nos permitirá realizar búsquedas de texto dentro de la descripción del cliente, usando la sintaxis de la cláusula MATCH ... AGAINST, como por ejemplo:

SELECT * FROM clientes WHERE MATCH (descripcion) AGAINST ('amable y profesional');

Esta consulta nos devolverá los clientes cuya descripción contenga las palabras ‘amable’ y ‘profesional’, teniendo en cuenta las variantes gramaticales y los sinónimos.

También podemos usar modos de búsqueda más avanzados, como el modo booleano o el modo de relevancia, que nos permiten usar operadores lógicos, comodines, frases entre comillas o modificar el peso de las palabras.

Crear un índice espacial sobre una columna geométrica

Para crear un índice espacial sobre una columna geométrica, primero tenemos que crear la columna con el tipo adecuado. Por ejemplo, si queremos almacenar la ubicación del cliente como un punto con coordenadas X e Y, podemos usar el tipo POINT y crear la columna ubicacion de la siguiente forma:

ALTER TABLE clientes ADD COLUMN ubicacion POINT;

Luego, podemos crear el índice espacial sobre la columna ubicacion, usando la siguiente sentencia:

CREATE SPATIAL INDEX idx_ubicacion ON clientes (ubicacion);

Este índice nos permitirá realizar operaciones espaciales sobre la ubicación del cliente, usando las funciones geométricas que proporciona MySQL, como por ejemplo:

SELECT * FROM clientes WHERE ST_Distance(ubicacion, ST_GeomFromText('POINT(40.416775 -3.703790)')) < 10000;

Esta consulta nos devolverá los clientes cuya ubicación esté a menos de 10 kilómetros del punto con coordenadas (40.416775, -3.703790), que corresponde al centro de Madrid.

Ventajas e inconvenientes de usar índices en MySQL

Los índices son una herramienta muy poderosa para mejorar el rendimiento de las consultas en MySQL, pero también tienen sus ventajas e inconvenientes que hay que tener en cuenta a la hora de usarlos. Algunas de las ventajas son:

Aceleran las consultas: Los índices permiten encontrar los registros que cumplen las condiciones de forma más rápida y eficiente, evitando tener que examinar toda la tabla. También permiten ordenar los resultados según el criterio deseado sin tener que hacer una operación adicional de ordenación.
Garantizan la integridad y la unicidad de los datos: Los índices únicos impiden que se inserten o actualicen registros con valores duplicados en las columnas indexadas, lo que asegura que no haya inconsistencias o anomalías en los datos.
Facilitan la implementación de funcionalidades avanzadas: Los índices full-text y espaciales permiten realizar búsquedas de texto o operaciones geométricas sobre los datos, lo que facilita la implementación de funcionalidades avanzadas como sistemas de búsqueda, motores de recomendación o aplicaciones geográficas.

Algunos de los inconvenientes son:

Ocupan espacio: Los índices ocupan espacio adicional en el disco, lo que puede afectar al almacenamiento disponible y al rendimiento general del sistema. El espacio ocupado depende del tipo y el tamaño del índice, así como del número y la longitud de las columnas indexadas.
Ralentizan las operaciones de escritura: Los índices se tienen que mantener actualizados con los cambios que se producen en la tabla, lo que implica un coste adicional cada vez que se inserta, actualiza o elimina un registro. Este coste depende del tipo y el tamaño del índice, así como del número y la frecuencia de las operaciones de escritura.
Requieren un diseño adecuado: Los índices requieren un diseño adecuado para aprovechar sus beneficios y evitar sus inconvenientes. No todos los tipos de índices son adecuados para todas las situaciones, ni todas las columnas son candidatas a ser indexadas. Hay que analizar las características de los datos y las consultas para elegir el tipo y el número de índices más apropiados.

Conclusiones

Los índices son una herramienta muy útil para mejorar el rendimiento de las consultas en MySQL, pero también tienen sus ventajas e inconvenientes que hay que tener en cuenta a la hora de usarlos. Para crear un índice en MySQL se utiliza la sentencia CREATE INDEX, con la que se pueden especificar diferentes opciones según el tipo, el algoritmo, el nivel de bloqueo y otros atributos del índice. Los índices se pueden crear sobre cualquier tipo de columna, ya sea numérica, alfanumérica, binaria o espacial.

Artículos relacionados con MySQL: