El mundo de MySQL & MariaDB

Cómo ver índices en MySQL | Comandos SQL y gestión

Optimización del rendimiento
目次

1. Qué es el índice en MySQL: la clave para mejorar el rendimiento de la base de datos

Al usar índices de manera eficaz en una base de datos MySQL, se puede mejorar considerablemente el rendimiento de las consultas. Un índice es una estructura de datos generada para una columna específica (campo) de la base de datos, que tiene la función de acelerar la velocidad de búsqueda y filtrado. Por ejemplo, al extraer información específica de un gran volumen de datos, el uso de índices permite omitir el escaneo de todos los datos y buscar solo la columna especificada dentro del índice.

Funciones y tipos de índices

En MySQL existen los siguientes tipos de índices:
  • PRIMARY (clave primaria): Es una clave única permitida solo una vez por tabla y se utiliza como el principal identificador de la tabla.
  • Índice UNIQUE: un índice que mantiene la unicidad y evita que se inserten valores duplicados en la columna especificada.
  • Índice general: un índice sin restricción de unicidad, utilizado para mejorar la eficiencia de búsqueda de una columna específica.
De esta manera, los índices aumentan la eficiencia de búsqueda y manipulación de datos en una tabla, y se convierten en un elemento indispensable, especialmente en conjuntos de datos a gran escala. Sin embargo, si hay demasiados índices, las operaciones INSERT y UPDATE se vuelven más lentas, por lo que es importante gestionar los índices según sea necesario.

2. Método básico para verificar índices en MySQL

En MySQL, se utiliza el comando SHOW INDEX para verificar los índices creados. Es un comando SQL sencillo que muestra la información de los índices en la tabla especificada. A continuación se presentan los pasos concretos.

Sintaxis básica y contenido de salida de SHOW INDEX

SHOW INDEX FROM nombre_de_tabla;

Explicación del contenido de salida

Al ejecutar este comando, se muestra la siguiente información.
  • Table: nombre de la tabla donde existe el índice
  • Non_unique: indica si el índice es único (0) o permite duplicados (1)
  • Key_name: nombre del índice
  • Column_name: nombre de la columna a la que se aplica el índice
  • Cardinality: estimación de los valores únicos registrados en el índice. Se utiliza como una métrica que indica la eficiencia de la búsqueda.
Al aprovechar esta información, se puede comprender visualmente el estado de los índices en la tabla y la distribución de índices en cada columna. Además, es posible filtrar la visualización usando la cláusula WHERE.

3. Verificación de índices en la tabla INFORMATION_SCHEMA.STATISTICS

Además del comando SHOW INDEX, MySQL permite verificar los índices consultando la tabla INFORMATION_SCHEMA.STATISTICS. Esto es útil para listar los índices de toda la base de datos y permite obtener información más detallada.

Consulta básica de INFORMATION_SCHEMA.STATISTICS

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

Detalles del resultado de la consulta

  • TABLE_SCHEMA: nombre de la base de datos a la que pertenece el índice
  • TABLE_NAME: nombre de la tabla donde existe el índice
  • COLUMN_NAME: nombre de la columna a la que se aplica el índice
  • INDEX_NAME: nombre del índice
Al usar este método, se puede consultar una lista de información de índices que abarcan varias tablas o bases de datos específicas. Es especialmente útil al gestionar los índices de toda la base de datos.

4. Métodos para agregar y eliminar índices y su impacto

Método para agregar índices

Los índices pueden añadirse posteriormente según sea necesario. Utilice el siguiente comando para crear un índice en la columna especificada.
CREATE INDEX nombre_del_índice ON nombre_de_la_tabla(nombre_de_la_columna);
Por ejemplo, si desea agregar un índice a la columna email de la tabla users, ejecútelo de la siguiente manera:
CREATE INDEX idx_email ON users(email);

Método para eliminar índices

Puede eliminar índices innecesarios para optimizar el rendimiento de las operaciones INSERT y UPDATE. Para eliminar, utilice el comando DROP INDEX.
DROP INDEX nombre_del_índice ON nombre_de_la_tabla;
Ejemplos de índices innecesarios incluyen aquellos creados en columnas que no se utilizan en condiciones de búsqueda (cláusula WHERE). Eliminar los índices puede mejorar la velocidad de inserción y actualización de datos.

5. Verificar el rendimiento del índice con la sentencia EXPLAIN

La sentencia EXPLAIN de MySQL es útil para revisar el plan de ejecución de consultas y examinar qué índices se están aplicando. Con ello, se puede evaluar la efectividad de los índices y, si es necesario, optimizarlos.

Uso básico de la sentencia EXPLAIN

EXPLAIN SELECT * FROM tabla WHERE columna = 'condicion';
Al usar este comando, se puede comprobar si se está utilizando un índice o si se está realizando un escaneo completo de la tabla. Los resultados incluyen los siguientes elementos：
  • type: tipo de consulta (ALL indica escaneo completo, INDEX indica que se está usando un índice)
  • possible_keys: lista de índices que pueden usarse para la consulta
  • key: nombre del índice realmente utilizado
  • rows: número estimado de filas que se escanearán
Con base en esta información, se puede analizar la efectividad de los índices y determinar si es necesario mejorar el rendimiento de las búsquedas。

6. Resumen

La gestión adecuada de índices es indispensable para la optimización del rendimiento de bases de datos MySQL. En particular, en tablas que manejan grandes volúmenes de datos, establecer índices en las columnas utilizadas en cláusulas WHERE y JOIN mejora drásticamente la eficiencia de búsqueda. Sin embargo, si hay demasiados índices, las operaciones de inserción y actualización se vuelven más lentas, por lo que es importante mantener un equilibrio adecuado. Comprender los pasos para agregar, verificar, eliminar índices y comprobar el rendimiento facilita la optimización de la base de datos y mejora la eficiencia del sistema en su conjunto.