Agregar un índice compuesto para consultas de múltiples columnas
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 en función de múltiples columnas.
¿Qué es un índice compuesto?
Un índice compuesto es un índice que abarca múltiples columnas. Es útil cuando las consultas utilizan con frecuencia 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.
Continuemos utilizando la tabla users que creamos en los pasos anteriores. Abra una terminal en la máquina virtual (VM) de LabEx (utilizando el acceso directo Xfce Terminal en el escritorio) y conéctese al servidor MySQL como usuario root:
mysql -u root -proot
Agreguemos más datos a la tabla users, incluyendo diferentes ciudades:
INSERT INTO users (username, email, city) VALUES
('alice_brown', '[email protected]', 'Los Angeles'),
('bob_davis', '[email protected]', 'Chicago'),
('charlie_wilson', '[email protected]', 'New York'),
('david_garcia', '[email protected]', 'Los Angeles');
Crear un índice compuesto
Supongamos que a menudo ejecuta consultas que filtran usuarios por city y 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.
Verificar el índice
Puede verificar que se haya creado el índice utilizando el comando SHOW INDEXES:
SHOW INDEXES FROM users;
La salida mostrará los detalles de los índices en la tabla users, incluyendo 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 | Visible | Expression |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_username | 1 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 1 | city | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 2 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Utilizar el índice compuesto
Para ver el beneficio del índice compuesto, puede utilizar el comando EXPLAIN para analizar una consulta que utiliza tanto la columna city como la columna 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 | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_city_username | idx_city_username | 770 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
Orden de las columnas en el índice
El orden de las columnas en el índice compuesto es importante. Si crea un índice en (username, city) en lugar de (city, username), el índice será menos efectivo para las 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.
exit;
