Cómo comprobar y optimizar índices MySQL: Guía completa con SHOW INDEX, EXPLAIN y ajuste de rendimiento

1. Introducción

MySQL es una base de datos relacional ampliamente utilizada en muchas aplicaciones web y sistemas de gestión de datos. Para mejorar la velocidad de recuperación de información, existe un mecanismo llamado “índice”. Sin embargo, si no se gestiona adecuadamente, los índices pueden reducir el rendimiento.

¿Por qué es importante revisar los índices?

Un índice en una base de datos es similar a la sección de índice de un libro. Los índices diseñados correctamente mejoran la velocidad de ejecución de las consultas de búsqueda. No obstante, pueden presentarse los siguientes problemas:

• Los índices no se crean correctamente → Puede provocar búsquedas lentas
• Existen índices innecesarios → Generan actualizaciones e inserciones más lentas
• Incertidumbre sobre qué índices se están utilizando → Dificulta decidir si se deben eliminar los índices no usados

Qué aprenderás en este artículo

• El mecanismo básico de los índices en MySQL
• Cómo revisar los índices actuales (usando comandos SQL)
• Cómo gestionar y optimizar los índices
• Técnicas para analizar el uso de los índices

Ahora, aprendamos de forma sistemática sobre los índices de MySQL y usemos este conocimiento para mejorar el rendimiento de la base de datos.

2. ¿Qué es un índice en MySQL?

Un índice es una característica esencial para mejorar el rendimiento de la base de datos. En esta sección explicamos el concepto básico, los tipos, ventajas y desventajas de los índices.

Concepto básico de los índices

Un índice de base de datos es un mecanismo que permite buscar rápidamente valores en columnas específicas. Por ejemplo, al buscar un registro concreto en una tabla que contiene una gran cantidad de datos, sin un índice la base de datos debe escanear todos los registros (full table scan). Aplicar un índice hace que la recuperación de datos sea eficiente y mejora significativamente la velocidad de procesamiento.

Tipos de índices en MySQL

MySQL admite varios tipos de índices, cada uno adecuado para casos de uso específicos.

1. PRIMARY KEY (Índice de clave primaria)

• Solo se puede establecer una vez por tabla
• Garantiza la unicidad de la tabla
• Funciona como un índice agrupado

1. UNIQUE Index

• Asegura que los valores en la columna especificada no se dupliquen
• Se permiten valores NULL (se permiten múltiples NULL)

1. INDEX (Índice general)

• Se utiliza para acelerar las búsquedas
• Se permite datos duplicados

1. FULLTEXT Index (para búsqueda de texto)

• Optimiza la búsqueda de texto
• Se usa en combinación con la sintaxis MATCH ... AGAINST

1. SPATIAL Index (para datos geográficos)

• Diseñado para datos espaciales (GIS)

Ventajas y desventajas de los índices

Ventajas

• Mejora la velocidad de búsqueda en las consultas
• Optimiza el rendimiento de operaciones JOIN y cláusulas WHERE
• Hace que la recuperación de datos específicos sea más eficiente

Desventajas

• Un mayor número de índices ralentiza las operaciones INSERT, UPDATE y DELETE
• Consume espacio en disco
• Los índices mal diseñados pueden degradar el rendimiento

3. Cómo revisar los índices en MySQL

Para gestionar adecuadamente los índices de MySQL, es importante verificar qué índices están definidos actualmente en una tabla. En esta sección explicamos cómo revisar los índices usando el comando SHOW INDEX, INFORMATION_SCHEMA.STATISTICS y el comando mysqlshow.

Comando SHOW INDEX (Método básico)

En MySQL, puedes usar el comando SHOW INDEX para obtener una lista de los índices definidos en una tabla específica. Este comando permite revisar detalles como el nombre del índice, las columnas que cubre y si existe una restricción UNIQUE.

Sintaxis básica

SHOW INDEX FROM table_name;

Ejemplo

Por ejemplo, para revisar los índices definidos en la tabla users, ejecuta el siguiente SQL:

SHOW INDEX FROM users;

Salida de ejemplo

Uso de INFORMATION_SCHEMA.STATISTICS (Obtener información detallada)

Al utilizar la tabla del sistema de MySQL INFORMATION_SCHEMA.STATISTICS, puedes obtener la misma información que SHOW INDEX con mayor flexibilidad.

Revisar índices de una tabla específica

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Obtener índices en toda la base de datos

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

Comando mysqlshow (Verificar desde CLI)

También puedes obtener información de índices usando las herramientas de línea de comandos de MySQL. Esto es especialmente útil cuando trabajas vía SSH en un servidor MySQL.

Cómo ejecutar el comando

mysqlshow -u username -p password database_name table_name

Ejemplo

mysqlshow -u root -p my_database users

Qué hacer si no existen índices

Si no se muestran índices después de ejecutar SHOW INDEX o consultar INFORMATION_SCHEMA.STATISTICS, la tabla puede no tener índices apropiados. En ese caso, puedes mejorar el rendimiento de búsqueda creando índices según sea necesario.

Crear un nuevo índice

CREATE INDEX idx_column ON users (email);

Establecer una clave primaria (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Eliminar índices innecesarios

ALTER TABLE users DROP INDEX idx_column;

4. Cómo verificar el uso de índices

Verificar si los índices funcionan correctamente es un paso crítico en la optimización del rendimiento de MySQL. En esta sección, explicamos cómo determinar qué índices está usando una consulta aprovechando el comando EXPLAIN y Performance Schema.

Análisis de consultas con EXPLAIN

El comando EXPLAIN se usa para visualizar cómo se ejecutará una consulta SQL dada. Te ayuda a analizar los índices usados, métodos de acceso y plan de ejecución, lo que resulta útil para verificar si los índices están funcionando como se espera.

Sintaxis básica

EXPLAIN SELECT * FROM table_name WHERE condition;

Ejemplo

Por ejemplo, para buscar en la tabla users usando la columna email como condición:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Salida de ejemplo

Puntos clave

• Si type = ALL, la consulta está realizando un escaneo completo de la tabla, por lo que probablemente necesites un índice.
• Si un nombre de índice aparece en key, ese índice está siendo usado.
• Si el valor de rows es demasiado grande, puede ser necesario optimizar la consulta.

Uso de Performance Schema

Con el performance_schema de MySQL, puedes analizar en detalle qué índices se están usando y con qué frecuencia durante la ejecución de consultas.

Obtener estadísticas de ejecución de consultas

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Verificar el uso de índices para una tabla específica

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

Qué hacer si un índice no se usa

1. Revisar la consulta

Si un índice no se está usando, la estructura de la consulta puede ser el problema. Por ejemplo, el siguiente patrón puede impedir el uso del índice.

❌ Ejemplo incorrecto (La función desactiva el uso del índice)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ Debido a LOWER(email), el índice sobre email puede ser ignorado.

✅ Ejemplo corregido (Evitar usar una función)

SELECT * FROM users WHERE email = 'test@example.com';

2. Recrear el índice

Si un índice existente no funciona correctamente, eliminarlo y recrearlo a veces puede mejorar el rendimiento.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Actualizar estadísticas

Si las estadísticas de la tabla se vuelven obsoletas, MySQL puede no usar los índices de forma óptima. Puedes actualizar las estadísticas con el siguiente comando:

ANALYZE TABLE users;

5. Gestión de índices

Los índices de MySQL son esenciales para mejorar el rendimiento de la recuperación de datos. Sin embargo, si no se gestionan adecuadamente, pueden degradar el rendimiento general de la base de datos. En esta sección, explicamos en detalle cómo crear, eliminar e identificar índices innecesarios.

Creación de índices

Al crear índices apropiados, puedes acelerar significativamente las búsquedas de datos. En MySQL, puedes agregar índices usando CREATE INDEX o ALTER TABLE.

Sintaxis básica

CREATE INDEX index_name ON table_name(column_name);

Ejemplo

Para agregar un índice a la columna email en la tabla users:

CREATE INDEX idx_email ON users(email);

Índice de varias columnas (índice compuesto)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Índice único

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Establecer una clave primaria (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Eliminación de índices

Eliminar índices innecesarios ayuda a reducir la sobrecarga de la base de datos.

Sintaxis básica

ALTER TABLE table_name DROP INDEX index_name;

Ejemplo

Por ejemplo, para eliminar un índice llamado idx_email:

ALTER TABLE users DROP INDEX idx_email;

Identificación y eliminación de índices innecesarios

Buscar índices no utilizados

SELECT * FROM sys.schema_unused_indexes;

Verificar el estado de la tabla (impacto de los índices)

SHOW TABLE STATUS LIKE 'users';

Eliminar índices innecesarios

ALTER TABLE users DROP INDEX idx_unused;

Después de la eliminación, se recomienda actualizar las estadísticas usando ANALYZE TABLE.

ANALYZE TABLE users;

6. Optimización de índices (Mejora del rendimiento)

Una gestión adecuada de los índices puede mejorar significativamente el rendimiento de las consultas en MySQL. Sin embargo, simplemente crear índices no es suficiente — diseño, gestión y monitoreo adecuados son necesarios para mantener un rendimiento óptimo.

Diseño adecuado de índices

Los índices bien diseñados pueden mejorar drásticamente la velocidad de búsqueda en MySQL.

Casos en los que se deben aplicar índices

Casos en los que no se deben aplicar índices

Uso del registro de consultas lentas

El registro de consultas lentas permite identificar consultas de larga duración y analizar qué índices no se están aplicando.

Activar el registro de consultas lentas

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Revisar el registro de consultas lentas

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Analizar una consulta lenta

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Ejemplo de aplicación de un índice

CREATE INDEX idx_last_login ON users(last_login);

Actualización de estadísticas (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Actualizar estadísticas)

ANALYZE TABLE users;

OPTIMIZE TABLE (Desfragmentación)

OPTIMIZE TABLE users;

7. Preguntas frecuentes sobre índices (FAQ)

Los índices de MySQL son un mecanismo esencial para mejorar el rendimiento de la base de datos. Sin embargo, si no se gestionan adecuadamente, pueden tener el efecto contrario. En esta sección, resumimos preguntas frecuentes (FAQ) y respuestas relacionadas con los índices de MySQL.

¿Mejorará la velocidad de búsqueda al agregar más índices?

R. No necesariamente.

Los índices están diseñados para mejorar el rendimiento de las consultas, pero agregar demasiados índices puede degradar realmente el rendimiento de la base de datos.

• Aumenta la sobrecarga de escritura (INSERT, UPDATE, DELETE)
• Si un índice se utiliza depende de la consulta
• Los índices innecesarios consumen memoria

¿Qué columnas deberían tener índices?

R. Es eficaz aplicar índices a los siguientes tipos de columnas:

¿Se crean los índices automáticamente?

R. Algunos índices se crean automáticamente, pero otros deben agregarse manualmente.

Índices creados automáticamente

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Índices creados manualmente

CREATE INDEX idx_email ON users(email);

¿Cómo debo elegir entre índices B‑Tree, Hash y FULLTEXT?

¿Cómo puedo comprobar el tamaño de los índices?

SHOW TABLE STATUS LIKE 'users';

¿Cómo puedo verificar si un índice está siendo utilizado?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

¿Cuándo debo eliminar un índice?

Eliminar un índice innecesario

ALTER TABLE users DROP INDEX idx_unused;

8. Resumen

En este artículo cubrimos de forma exhaustiva los índices de MySQL, desde los conceptos básicos hasta los métodos de verificación, gestión, optimización y preguntas frecuentes. Aquí repasamos los puntos clave de cada sección y resumimos las mejores prácticas para optimizar la gestión de índices en MySQL.

Conclusiones principales

¿Qué es un índice en MySQL?

• Un índice es un mecanismo que acelera la recuperación de datos.
• Existen varios tipos, incluidos los índices B‑Tree, Hash y FULLTEXT.
• Son más efectivos cuando se aplican a columnas usadas en cláusulas WHERE, JOIN y ORDER BY.

Cómo comprobar los índices de MySQL

• Utiliza el comando SHOW INDEX para ver la lista de índices.
• Utiliza INFORMATION_SCHEMA.STATISTICS para obtener información detallada.

Cómo comprobar el uso de índices

• Usa EXPLAIN para ver qué índices utiliza una consulta.
• Utiliza Performance Schema para analizar la frecuencia de uso de los índices.

Gestión de índices

• Usa CREATE INDEX para aplicar índices a las columnas apropiadas.
• Usa ALTER TABLE DROP INDEX para eliminar índices innecesarios.
• Verifica el tamaño de los índices con SHOW TABLE STATUS y optimiza según sea necesario.

Optimización de índices (Mejora del rendimiento)

• Aplica índices a las columnas de WHERE, JOIN y ORDER BY que se usan con frecuencia.
• Utiliza el Slow Query Log para identificar y optimizar consultas lentas.
• Actualiza las estadísticas con ANALYZE TABLE y OPTIMIZE TABLE.

Mejores prácticas para la gestión de índices en MySQL

1. Identifica los cuellos de botella de las consultas antes de crear índices.
2. Selecciona los índices adecuados.

• Utiliza correctamente índices de una sola columna y compuestos.
• Usa UNIQUE INDEX donde se requieran restricciones de unicidad.

1. Elimina regularmente los índices innecesarios.

• Identifica los índices no usados con SHOW INDEX y schema_unused_indexes.

1. Actualiza las estadísticas de forma periódica.

• Actualiza las estadísticas con ANALYZE TABLE.
• Ejecuta OPTIMIZE TABLE para resolver la fragmentación causada por borrados y actualizaciones.

Próximos pasos

✅ Lista de verificación práctica
✅ ¿Has revisado los índices actuales con SHOW INDEX?
✅ ¿Has habilitado el Slow Query Log e identificado consultas lentas?
✅ ¿Has analizado el plan de ejecución de consultas con EXPLAIN?
✅ ¿Has eliminado índices innecesarios y creado los apropiados?
✅ ¿Has actualizado las estadísticas con ANALYZE TABLE?

Resumen final

• Una gestión adecuada de los índices de MySQL mejora significativamente el rendimiento de búsqueda.
• Utiliza el Slow Query Log y EXPLAIN para analizar la efectividad de los índices y optimizarlos en consecuencia.
• Mantén el rendimiento de la base de datos actualizando regularmente las estadísticas y optimizando las tablas.

Esto concluye la guía completa sobre la gestión de índices en MySQL.

Utiliza este conocimiento para crear sistemas de bases de datos más rápidos y eficientes. 💡🚀