Índices de MySQL y Optimización del Rendimiento

Introducción

En este laboratorio, aprenderá sobre los índices de MySQL y las técnicas de optimización del rendimiento. El laboratorio se centra en la creación y gestión de índices para mejorar el rendimiento de las consultas a la base de datos.

Comenzará creando una tabla users e insertando datos de ejemplo. Luego, creará un índice de una sola columna en la columna username y aprenderá a verificar su creación. El laboratorio también cubrirá el análisis de planes de consulta utilizando EXPLAIN, la adición de índices compuestos para consultas multicolumna y la eliminación de índices no utilizados para mantener la eficiencia de la base de datos.

Crear un índice de una sola columna en una tabla

En este paso, aprenderá cómo crear un índice de columna única en MySQL. Los índices son cruciales para mejorar el rendimiento de las consultas a la base de datos, especialmente cuando se trabaja con tablas grandes. Un índice en una columna permite a la base de datos localizar rápidamente filas que coinciden con un valor específico en esa columna, sin tener que escanear toda la tabla.

Comprendiendo los Índices

Piense en un índice como el índice de un libro. En lugar de leer el libro completo para encontrar un tema específico, puede usar el índice para localizar rápidamente las páginas relevantes. De manera similar, un índice de base de datos ayuda al motor de la base de datos a encontrar filas específicas rápidamente.

Creación de una Tabla

Primero, creemos una tabla simple llamada users para demostrar la creación de un índice. Abra una terminal en la VM de LabEx. Puede usar el acceso directo Xfce Terminal en el escritorio.

Conéctese al servidor MySQL como usuario root:

sudo mysql -u root

Primero, cree una base de datos para este laboratorio y selecciónela:

CREATE DATABASE lab_db;
USE lab_db;

Ahora, cree la tabla users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Esta declaración SQL crea una tabla llamada users con columnas para id, username, email y created_at. La columna id se establece como clave primaria y se auto-incrementa.

Insertemos algunos datos de ejemplo en la tabla users:

INSERT INTO users (username, email) VALUES
('john_doe', 'john.doe@example.com'),
('jane_smith', 'jane.smith@example.com'),
('peter_jones', 'peter.jones@example.com');

Creación de un Índice de Columna Única

Ahora, creemos un índice en la columna username. Esto ayudará a acelerar las consultas que buscan usuarios por su nombre de usuario.

CREATE INDEX idx_username ON users (username);

Esta declaración crea un índice llamado idx_username en la columna username de la tabla users.

Verificación del Índice

Puede verificar que el índice se ha creado utilizando el comando SHOW INDEXES:

SHOW INDEXES FROM users;

La salida mostrará los detalles de los índices en la tabla users, incluido el índice idx_username que acaba de crear. Debería ver una fila donde Key_name es idx_username y Column_name es username.

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY      |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username |            1 | username    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Ahora ha creado con éxito un índice de columna única en una tabla. Esto mejorará el rendimiento de las consultas que filtran datos basándose en la columna indexada.

Analizar un plan de consulta con EXPLAIN

En este paso, aprenderá a utilizar la declaración EXPLAIN en MySQL para analizar el plan de ejecución de consultas. Comprender el plan de consulta es esencial para identificar cuellos de botella en el rendimiento y optimizar sus consultas.

¿Qué es un Plan de Consulta?

Un plan de consulta es un mapa que el motor de la base de datos utiliza para ejecutar una consulta. Describe el orden en que se accede a las tablas, los índices que se utilizan y los algoritmos que se aplican para recuperar los datos. Al analizar el plan de consulta, puede comprender cómo la base de datos está ejecutando su consulta e identificar áreas de mejora.

Uso de la Declaración EXPLAIN

La declaración EXPLAIN proporciona información sobre cómo MySQL ejecuta una consulta. Muestra las tablas involucradas, los índices utilizados, el orden de unión (join) y otros detalles que pueden ayudarle a comprender el rendimiento de la consulta.

Ahora, analicemos una consulta simple usando EXPLAIN.

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

La salida de la declaración EXPLAIN será una tabla con varias columnas. Aquí hay un desglose de algunas de las columnas más importantes:

• id: El ID de la declaración SELECT.
• select_type: El tipo de consulta SELECT (por ejemplo, SIMPLE, PRIMARY, SUBQUERY).
• table: La tabla a la que se accede.
• type: El tipo de unión (join type). Esta es una de las columnas más importantes. Los valores comunes incluyen:
  • system: La tabla tiene solo una fila.
  • const: La tabla tiene como máximo una fila coincidente, que se lee al inicio de la consulta.
  • eq_ref: Se lee una fila de esta tabla por cada combinación de filas de las tablas anteriores. Se utiliza al unir por una columna indexada.
  • ref: Se leen todas las filas coincidentes de esta tabla por cada combinación de filas de las tablas anteriores. Se utiliza al unir por una columna indexada.
  • range: Solo se recuperan filas dentro de un rango dado, utilizando un índice.
  • index: Se realiza un escaneo completo del índice.
  • ALL: Se realiza un escaneo completo de la tabla. Este es el tipo menos eficiente.
• possible_keys: Los índices que MySQL podría usar para encontrar las filas en la tabla.
• key: El índice que MySQL realmente utilizó.
• key_len: La longitud de la clave que utilizó MySQL.
• ref: Las columnas o constantes que se comparan con el índice.
• rows: El número de filas que MySQL estima que tendrá que examinar para ejecutar la consulta.
• Extra: Información adicional sobre cómo MySQL está ejecutando la consulta. Los valores comunes incluyen:
  • Using index: La consulta se puede satisfacer utilizando solo el índice.
  • Using where: MySQL necesita filtrar las filas después de acceder a la tabla.
  • Using temporary: MySQL necesita crear una tabla temporal para ejecutar la consulta.
  • Using filesort: MySQL necesita ordenar las filas después de acceder a la tabla.

Interpretación de la Salida de EXPLAIN

Para la consulta SELECT * FROM users WHERE username = 'john_doe', la salida de EXPLAIN debería verse algo así:

+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | idx_username  | idx_username | 767     | const | 1    | Using index condition |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+

En este ejemplo:

• type es ref, lo que significa que MySQL está utilizando un índice para encontrar la fila coincidente.
• possible_keys y key muestran ambos idx_username, lo que significa que MySQL está utilizando el índice idx_username que creamos en el paso anterior.
• rows es 1, lo que significa que MySQL estima que solo tendrá que examinar una fila para ejecutar la consulta.

Análisis de una Consulta sin Índice

Ahora, analicemos una consulta que no utiliza un índice. Primero, agreguemos una nueva columna a la tabla users llamada city:

ALTER TABLE users ADD COLUMN city VARCHAR(255);

Ahora, ejecutemos un EXPLAIN en una consulta que busca por city. Dado que aún no hemos agregado datos a la columna city, actualicemos una de las filas:

UPDATE users SET city = 'New York' WHERE username = 'john_doe';

Ahora, ejecute la declaración EXPLAIN nuevamente:

EXPLAIN SELECT * FROM users WHERE city = 'New York';

La salida podría verse así:

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

En este ejemplo:

• type es ALL, lo que significa que MySQL está realizando un escaneo completo de la tabla.
• possible_keys y key son ambos NULL, lo que significa que MySQL no está utilizando ningún índice.
• rows es 3, lo que significa que MySQL estima que tendrá que examinar las 3 filas de la tabla para ejecutar la consulta.
• Extra muestra Using where, lo que significa que MySQL necesita filtrar las filas después de acceder a la tabla.

Esto indica que la consulta no está optimizada y podría beneficiarse de un índice en la columna city.

Agregar un índice compuesto para consultas multicolumna

En este paso, aprenderá cómo crear un índice compuesto en MySQL. Un índice compuesto es un índice en dos o más columnas de una tabla. Puede mejorar significativamente el rendimiento de las consultas que filtran datos basándose en múltiples columnas.

¿Qué es un Índice Compuesto?

Un índice compuesto es un índice que cubre múltiples columnas. Es útil cuando las consultas utilizan frecuentemente múltiples columnas en la cláusula WHERE. El orden de las columnas en el índice compuesto es importante. El índice es más efectivo cuando las columnas se especifican en el mismo orden en la cláusula WHERE de la consulta.

Agreguemos más datos a la tabla users, incluyendo diferentes ciudades:

INSERT INTO users (username, email, city) VALUES
('alice_brown', 'alice.brown@example.com', 'Los Angeles'),
('bob_davis', 'bob.davis@example.com', 'Chicago'),
('charlie_wilson', 'charlie.wilson@example.com', 'New York'),
('david_garcia', 'david.garcia@example.com', 'Los Angeles');

Creación de un Índice Compuesto

Supongamos que a menudo ejecuta consultas que filtran usuarios tanto por city como por username. En este caso, puede crear un índice compuesto en las columnas city y username.

CREATE INDEX idx_city_username ON users (city, username);

Esta declaración crea un índice llamado idx_city_username en las columnas city y username de la tabla users.

Verificación del Índice

Puede verificar que el índice se ha creado utilizando el comando SHOW INDEXES:

SHOW INDEXES FROM users;

La salida mostrará los detalles de los índices en la tabla users, incluido el índice idx_city_username que acaba de crear. Debería ver dos filas para idx_city_username, una para la columna city y otra para la columna username.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username      |            1 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Uso del Índice Compuesto

Para ver el beneficio del índice compuesto, puede usar el comando EXPLAIN para analizar una consulta que utiliza tanto las columnas city como username en la cláusula WHERE.

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

La salida de EXPLAIN mostrará que la consulta está utilizando el índice idx_city_username, lo que significa que la base de datos puede encontrar rápidamente la fila coincidente sin escanear toda la tabla. Busque las columnas possible_keys y key en la salida. Si se está utilizando el índice, verá idx_city_username en estas columnas.

+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
| id   | select_type | table | type | possible_keys                  | key          | key_len | ref   | rows | Extra                              |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | users | ref  | idx_username,idx_city_username | idx_username | 767     | const | 1    | Using index condition; Using where |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+

Orden de las Columnas en el Índice

El orden de las columnas en el índice compuesto importa. Si crea un índice en (username, city) en lugar de (city, username), el índice será menos efectivo para consultas que filtran por city y luego por username.

Por ejemplo, si tuviéramos un índice en (username, city) y ejecutáramos la siguiente consulta:

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

MySQL podría no utilizar el índice, o podría utilizarlo solo parcialmente, porque la columna city no es la columna principal en el índice.

Eliminar un índice no utilizado

En este paso, aprenderá cómo eliminar un índice no utilizado en MySQL. Si bien los índices pueden mejorar significativamente el rendimiento de las consultas, también agregan sobrecarga a las operaciones de escritura (inserciones, actualizaciones y eliminaciones). Por lo tanto, es importante identificar y eliminar los índices que ya no se están utilizando.

¿Por Qué Eliminar Índices No Utilizados?

Los índices no utilizados ocupan espacio en disco y pueden ralentizar las operaciones de escritura. Cuando se modifican datos en una tabla, el motor de la base de datos también debe actualizar todos los índices de esa tabla. Si una consulta no está utilizando un índice, simplemente está agregando una sobrecarga innecesaria.

En los pasos anteriores, creamos un índice llamado idx_username en la columna username. Supongamos que, después de analizar sus patrones de consulta, determina que este índice ya no se está utilizando.

Eliminación del Índice

Para eliminar el índice idx_username, puede utilizar la declaración DROP INDEX:

DROP INDEX idx_username ON users;

Esta declaración elimina el índice idx_username de la tabla users.

Verificación de la Eliminación del Índice

Puede verificar que el índice se ha eliminado utilizando el comando SHOW INDEXES:

SHOW INDEXES FROM users;

La salida mostrará los detalles de los índices en la tabla users. Ya no debería ver el índice idx_username en la salida.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Identificación de Índices No Utilizados

En un escenario del mundo real, identificar índices no utilizados puede ser un desafío. MySQL proporciona varias herramientas y técnicas para ayudarle con esta tarea:

• MySQL Enterprise Audit: Esta característica le permite registrar todas las consultas ejecutadas en su servidor. Luego puede analizar los registros de consultas para identificar qué índices se están utilizando.
• Performance Schema: El Performance Schema proporciona información detallada sobre el rendimiento del servidor, incluido el uso de índices.
• Herramientas de Terceros: Varias herramientas de terceros pueden ayudarle a monitorear el uso de índices e identificar índices no utilizados.

Al monitorear regularmente el uso de sus índices y eliminar los índices no utilizados, puede mejorar el rendimiento general de su base de datos.

Ahora que hemos completado todos los pasos, salgamos de la consola de MySQL:

exit;

Resumen

En este laboratorio, aprendió a crear un índice de una sola columna en MySQL para mejorar el rendimiento de las consultas. También aprendió a utilizar la declaración EXPLAIN para analizar planes de ejecución de consultas e identificar cuellos de botella de rendimiento. Además, practicó la creación de índices compuestos para consultas multi-columna y la eliminación de índices no utilizados para mantener la eficiencia de la base de datos. Comprender y utilizar los índices de manera efectiva es una habilidad fundamental para optimizar el rendimiento de la base de datos.