Cómo usar índices en MySQL

El autor seleccionó el programa Write for DOnations.

Introducción

Las bases de datos relacionales se pueden utilizar para trabajar con datos de cualquier tamaño, incluidas las grandes bases de datos que contienen millones de filas. El lenguaje de consulta estructurado (SQL) brinda una forma concisa y directa de encontrar filas particulares en las tablas de la base de datos según criterios específicos. A medida que las bases de datos se hacen más grandes, localizar filas específicas dentro de ellas se vuelve cada vez más difícil, similar a buscar una aguja en un pajar.

La capacidad de las bases de datos para aceptar una amplia gama de condiciones de consulta dificulta que el motor de la base de datos anticipe qué consultas serán las más comunes. El motor debe estar preparado para ubicar filas de manera eficiente en las tablas de la base de datos, independientemente de su tamaño. Sin embargo, a medida que aumentan los datos, el rendimiento de la búsqueda puede verse afectado. Cuanto más grande sea el conjunto de datos, más difícil será para el motor de la base de datos encontrar rápidamente los documentos que coincidan con la consulta.

Los administradores de bases de datos pueden usar índices para ayudar al motor de la base de datos y mejorar su rendimiento.

En este tutorial, aprenderá qué son los índices, cómo crearlos y si se utilizan para consultar la base de datos.

requisitos previos

Para seguir esta guía, necesitará una computadora que ejecute un sistema de administración de bases de datos relacionales (RDBMS) basado en SQL. Las instrucciones y los ejemplos de esta guía se validaron utilizando el siguiente entorno:

• Un servidor que ejecuta Ubuntu 20.04, con un usuario no raíz con privilegios administrativos y un firewall configurado con UFW, como se describe en nuestra guía de configuración inicial del servidor para Ubuntu 20.04.
• MySQL instalado y protegido en el servidor, como se describe en el Paso 3.
• Familiaridad básica con la ejecución de consultas SELECT para recuperar datos de la base de datos como se describe en nuestra guía Cómo SELECCIONAR filas DE tablas en SQL.

Nota: tenga en cuenta que muchos RDBMS utilizan sus propias implementaciones únicas de SQL. Aunque los comandos descritos en este tutorial funcionarán en la mayoría de los RDBMS, los índices no forman parte de la sintaxis SQL estándar y, por lo tanto, la sintaxis o el resultado exactos pueden diferir si los prueba en un sistema que no sea MySQL.

También necesitará una base de datos con algunas tablas cargadas con datos de muestra para que pueda practicar el uso de índices. Le recomendamos que consulte la siguiente sección Conexión a MySQL y configuración de una base de datos de muestra para obtener detalles sobre cómo conectarse a un servidor MySQL y crear la base de datos de prueba utilizada en los ejemplos de esta guía.

Conexión a MySQL y configuración de una base de datos de muestra

En esta sección, se conectará a un servidor MySQL y creará una base de datos de muestra para que pueda seguir los ejemplos de esta guía.

Si su sistema de base de datos SQL se ejecuta en un servidor remoto, SSH en su servidor desde su máquina local:

ssh sammy@your_server_ip

Luego abra el indicador del servidor MySQL, reemplazando sammy con el nombre de su cuenta de usuario de MySQL:

mysql -u sammy -p

Cree una base de datos llamada indexes:

CREATE DATABASE indexes;

Si la base de datos se creó correctamente, recibirá un resultado como este:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos indexes, ejecute la siguiente instrucción USE:

USE indexes;

Recibirá el siguiente resultado:

Output
Database changed

Después de seleccionar la base de datos, puede crear una tabla de muestra dentro de ella. Para esta guía, utilizará una base de datos de empleados imaginaria para almacenar detalles sobre los empleados actuales y sus dispositivos de trabajo.

La tabla empleados contendrá datos simplificados sobre los empleados en la base de datos. Contendrá las siguientes columnas:

• employee_id: esta columna contiene el identificador del empleado, representado por el tipo de datos int. Esta columna se convertirá en la clave principal de la tabla y cada valor se convertirá en un identificador único para su fila respectiva.
• first_name: esta columna contiene el nombre de cada empleado, expresado con el tipo de datos varchar con un máximo de 50 caracteres.< /li>
• last_name: esta columna contiene el apellido de cada empleado, expresado con el tipo de datos varchar con un máximo de 50 caracteres.< /li>
• device_serial: Esta columna contiene el número de serie de la computadora asignada al empleado, expresado usando el tipo de datos varchar con un máximo de 15 personajes.
• salario: esta columna contiene el salario de cada empleado, expresado mediante el tipo de datos int que almacena datos numéricos.

Cree la tabla de muestra con el siguiente comando:

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

Si se imprime el siguiente resultado, la tabla se ha creado:

Output
Query OK, 0 rows affected (0.00 sec)

A continuación, cargue la tabla employees con algunos datos de muestra ejecutando la siguiente operación INSERT INTO:

INSERT INTO employees VALUES
    (1, 'John', 'Smith', 'ABC123', 60000),
    (2, 'Jane', 'Doe', 'DEF456', 65000),
    (3, 'Bob', 'Johnson', 'GHI789', 70000),
    (4, 'Sally', 'Fields', 'JKL012', 75000),
    (5, 'Michael', 'Smith', 'MNO345', 80000),
    (6, 'Emily', 'Jones', 'PQR678', 85000),
    (7, 'David', 'Williams', 'STU901', 90000),
    (8, 'Sarah', 'Johnson', 'VWX234', 95000),
    (9, 'James', 'Brown', 'YZA567', 100000),
    (10, 'Emma', 'Miller', 'BCD890', 105000),
    (11, 'William', 'Davis', 'EFG123', 110000),
    (12, 'Olivia', 'Garcia', 'HIJ456', 115000),
    (13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
    (14, 'Isabella', 'Wilson', 'NOP012', 125000),
    (15, 'Matthew', 'Martinez', 'QRS345', 130000),
    (16, 'Sophia', 'Anderson', 'TUV678', 135000),
    (17, 'Daniel', 'Smith', 'WXY901', 140000),
    (18, 'Mia', 'Thomas', 'ZAB234', 145000),
    (19, 'Joseph', 'Hernandez', 'CDE567', 150000),
    (20, 'Abigail', 'Smith', 'FGH890', 155000);

La base de datos responderá con el mensaje de éxito:

Output
Query OK, 20 rows affected (0.010 sec)
Records: 20  Duplicates: 0  Warnings: 0

Nota: El conjunto de datos no es lo suficientemente grande como para ilustrar directamente el impacto en el rendimiento de los índices. Sin embargo, este conjunto de datos demostrará cómo MySQL usa los índices para limitar la cantidad de filas recorridas para realizar consultas y obtener resultados.

Con eso, está listo para seguir el resto de la guía y comenzar a usar índices en MySQL.

Introducción a los índices

Por lo general, cuando ejecuta una consulta en la base de datos MySQL, la base de datos debe pasar por todas las filas de la tabla una por una. Por ejemplo, es posible que desee buscar apellidos de empleados que coincidan con Smith o todos los empleados con un salario superior a $100000. Cada fila de la tabla se examinará una por una para verificar si coincide con la condición. Si lo hace, se agregará a la lista de filas devueltas. Si no es así, MySQL escaneará las filas subsiguientes hasta que navegue por toda la tabla.

Aunque este método de encontrar filas coincidentes es efectivo, puede volverse lento y consumir muchos recursos a medida que aumenta el tamaño de la tabla. Como resultado, este enfoque puede no ser adecuado para tablas grandes o consultas que requieran un acceso rápido o frecuente a los datos.

Para resolver los problemas de rendimiento con tablas y consultas grandes, puede usar índices. Los índices son estructuras de datos únicas que almacenan solo un subconjunto ordenado de los datos por separado de las filas de la tabla. Permiten que el motor de la base de datos funcione de forma más rápida y eficaz al buscar valores o realizar pedidos en función de un campo o conjunto de campos específicos.

Usando la tabla employees como ejemplo, una de las consultas típicas que podría ejecutar es encontrar empleados por su apellido. Sin ningún índice, MySQL recuperaría a todos los empleados de la tabla y verificaría si el apellido coincide con la consulta. Pero al usar un índice, MySQL mantendrá una lista separada de apellidos, que solo contiene punteros a las filas de los empleados dados en la tabla principal. Luego usará ese índice para recuperar los resultados sin escanear toda la tabla.

Puede pensar en los índices como una analogía de una guía telefónica. Para ubicar a una persona llamada John Smith en el libro, primero pasa a la página derecha donde se enumeran las personas cuyos nombres comienzan con S, y luego busca personas en las páginas. con nombres que comienzan con Sm. Siguiendo esa lógica, puede eliminar muchas entradas rápidamente, sabiendo que no coinciden con la persona que está buscando. El proceso funciona solo porque los datos de la guía telefónica están ordenados alfabéticamente, lo que rara vez ocurre con los datos almacenados directamente en la base de datos. Un índice en el motor de la base de datos tiene un propósito similar al de una guía telefónica, ya que mantiene las referencias a los datos ordenadas alfabéticamente y, por lo tanto, ayuda a la base de datos a encontrar las filas requeridas rápidamente.

Usar índices en MySQL tiene múltiples beneficios. Los más comunes son acelerar las cláusulas ORDER BY más rápidamente y hacer cumplir la exclusividad del valor.

Sin embargo, el uso de índices puede degradar el rendimiento máximo de la base de datos en algunas circunstancias. Los índices están diseñados para acelerar la recuperación de datos y se implementan utilizando estructuras de datos adicionales que se almacenan junto con los datos de la tabla. Esas estructuras deben mantenerse actualizadas con cada cambio en la base de datos, lo que puede ralentizar el rendimiento de las consultas SELECT y a veces puede verse compensado por el rendimiento notablemente más lento de las consultas que escriben datos en la base de datos.

Se recomienda crear índices solo cuando exista una clara necesidad de ellos, como cuando el rendimiento de una aplicación comienza a disminuir. Al elegir qué índices crear, considere las consultas que se ejecutan con más frecuencia y toman más tiempo, y cree índices basados en las condiciones de consulta que más se beneficiarán de ellos.

Nota: Esta guía tiene como objetivo introducir el tema de los índices de bases de datos en MySQL, ilustrando las aplicaciones comunes y los tipos de índices. El motor de la base de datos admite muchos escenarios más complejos para usar índices a fin de aumentar el rendimiento de la base de datos, lo cual está fuera del alcance de esta guía. Le animamos a consultar la documentación oficial de MySQL sobre índices para obtener una descripción más completa de las características de la base de datos.

En los siguientes pasos, creará índices de diferentes tipos para una variedad de escenarios. Aprenderá cómo verificar si los índices se usan en una consulta. Finalmente, aprenderá a eliminar índices si es necesario.

Uso de índices de una sola columna

Un índice de una sola columna es el tipo de índice más común y directo que puede usar para optimizar el rendimiento de las consultas. Este tipo de índice ayuda a la base de datos a acelerar las consultas que filtran el conjunto de datos en función de los valores de una sola columna. Los índices creados en una sola columna pueden acelerar muchas consultas condicionales, incluidas las coincidencias exactas con el operador = y las comparaciones con los operadores > o <.

En la base de datos de ejemplo que creó en un paso anterior, no hay índices. Antes de crear un índice, primero probará cómo la base de datos maneja las consultas SELECT en la tabla employees cuando la cláusula WHERE se usa solo para solicitar un subconjunto de datos de la tabla.

Suponga que desea encontrar empleados con un salario de exactamente $100000. Ejecute la siguiente consulta:

SELECT * FROM employees WHERE salary = 100000;

La cláusula WHERE solicita una coincidencia exacta de empleados con el salario que coincida con el valor solicitado. En este ejemplo, la base de datos responderá de la siguiente manera:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Nota: Como se muestra en el resultado anterior, la base de datos respondió casi instantáneamente a la consulta emitida. Con solo un puñado de filas de muestra en la base de datos, el uso de índices no afectará visiblemente el rendimiento de la consulta. Sin embargo, con grandes conjuntos de datos, observará cambios significativos en el tiempo de ejecución de la consulta informado por la base de datos después de ejecutar la consulta.

A juzgar por el resultado de la consulta, no puede saber cómo el motor de la base de datos abordó el problema de encontrar las filas coincidentes en la tabla. Sin embargo, MySQL proporciona una forma de obtener información sobre el plan de consulta, que es cómo el motor ejecuta la consulta: declaraciones EXPLAIN.

Para acceder al plan de consulta para la consulta SELECT, ejecute lo siguiente:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

El comando EXPLAIN le dice a MySQL que ejecute la consulta SELECT, pero en lugar de devolver los resultados, mostrará información sobre cómo el motor de la base de datos realizó la consulta internamente.

El plan de ejecución será similar al siguiente (su tabla puede diferir ligeramente):

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

En la salida de esta tabla, las columnas describen muchos aspectos de la ejecución de la consulta. Dependiendo de su versión de MySQL, su salida puede contener columnas adicionales, pero para este tutorial, esta es la información más importante:

• possible_keys enumera los índices que MySQL consideró para su uso. En este caso, no hay ninguno (NULL).
• key describe el índice que MySQL decidió usar al realizar la consulta. En este caso, no se utilizó ningún índice (NULL).
• rows muestra el número de filas que MySQL tuvo que analizar individualmente antes de devolver los resultados. Aquí, es 20, que corresponde al número de todas las filas posibles en la tabla. Esto significa que MySQL tuvo que escanear cada fila en la tabla employees para encontrar la única devuelta.
• Extra muestra información descriptiva adicional sobre el plan de consulta. En este ejemplo, la anotación Using where significa que la base de datos filtró los resultados directamente desde dentro de la tabla usando la instrucción WHERE.

Sin índices establecidos, la base de datos tuvo que escanear 20 filas para recuperar una sola. Si la tabla contuviera millones de filas, MySQL tendría que revisarlas una por una, lo que daría como resultado un rendimiento deficiente de las consultas.

Nota: Las versiones más recientes de MySQL, cuando se usa EXPLAIN, muestran 1 fila en el conjunto, 1 advertencia en la salida, mientras que las versiones anteriores de MySQL y MySQL- las bases de datos compatibles a menudo simplemente mostrarán 1 fila en el conjunto en su lugar. La advertencia no es una señal de un problema. MySQL utiliza su mecanismo de advertencias para proporcionar más información ampliada sobre el plan de consulta. El uso de esta información adicional está fuera del alcance de este tutorial. Puede obtener más información sobre ese comportamiento en la página Formato de salida extendido EXPLAIN en la documentación de MySQL.

La consulta SELECT que acaba de ejecutar usó la condición de consulta exacta, WHERE salario=100000. A continuación, verifiquemos si la base de datos se comportará de manera similar con una condición de comparación. Intenta recuperar empleados con un salario inferior a 70000:

SELECT * FROM employees WHERE salary < 70000;

Esta vez, la base de datos devolvió dos filas para John Smith y Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

Sin embargo, cuando usa EXPLAIN para comprender la ejecución de la consulta de la siguiente manera:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

Notará que la tabla es casi idéntica a la consulta anterior:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Al igual que con la consulta anterior, MySQL analizó todas las filas 20 de la tabla para encontrar las que solicitó a través de la cláusula WHERE en la consulta. Aunque la cantidad de filas devueltas es pequeña en comparación con la cantidad de todas las filas de la tabla, el motor de la base de datos tiene que realizar mucho trabajo para encontrarlas.

Para remediarlo, puede crear un índice para la columna salary, que le indicará a MySQL que mantenga una estructura de datos adicional altamente optimizada, especialmente para los datos salary de < tabla código>empleados. Para ello, ejecute la siguiente consulta:

CREATE INDEX salary ON employees(salary);

La sintaxis de la instrucción CREATE INDEX requiere:

• El nombre del índice, que en este caso es salary. El nombre debe ser único dentro de una sola tabla, pero puede repetirse en diferentes tablas en la misma base de datos.
• El nombre de la tabla para la que se crea el índice. En este caso, se trata de empleados.
• La lista de columnas para las que se crea el índice. Aquí, está utilizando una sola columna llamada salary para construir el índice.

Nota: Dependiendo de sus permisos de usuario de MySQL, puede recibir un error al ejecutar el comando CREATE INDEX: ERROR 1142 (42000): comando INDEX denegado al usuario usuario@host para empleados de mesa. Para otorgar permisos de INDEX a su usuario, inicie sesión en MySQL como root y ejecute los siguientes comandos, reemplazando el nombre de usuario y host de MySQL según sea necesario:

GRANT INDEX on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

Después de actualizar los permisos de usuario, cierre sesión como root y vuelva a iniciar sesión como usuario, y vuelva a ejecutar la instrucción CREATE INDEX.

La base de datos confirmará que el índice se creó correctamente:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Con el índice en su lugar, intente repetir las consultas anteriores para verificar si algo ha cambiado. Comience recuperando al empleado único con el salario de exactamente 100000:

SELECT * FROM employees WHERE salary = 100000;

El resultado será el mismo que antes, con solo James Brown devuelto:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Sin embargo, pedirle a MySQL que explique cómo abordó la consulta mostrará algunas diferencias con respecto a antes. Ejecute la consulta EXPLAIN de la siguiente manera:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

Esta vez, la salida se imprimirá así:

Output
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | salary        | salary | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL declara que a partir de la única clave posible que se muestra en possible_keys, decidió usar la clave denominada salary, que es el índice que creó. La columna rows ahora muestra 1 en lugar de 20. Debido a que usó el índice, la base de datos evitó escanear todas las filas de la base de datos y pudo devolver la única fila solicitada de inmediato. La columna Extra ahora no menciona Using WHERE, porque no fue necesario iterar a través de la tabla principal y verificar cada fila con la condición de consulta para realizar la consulta.

Con un pequeño conjunto de datos de muestra, el impacto de usar el índice no es muy perceptible. Pero a la base de datos le tomó mucho menos trabajo recuperar el resultado y el efecto de este cambio sería significativo en un conjunto de datos más grande.

Intente volver a ejecutar la segunda consulta, recuperando a los empleados con un salario inferior a 70000, para verificar si el índice también se usará allí.

Ejecute la siguiente consulta:

SELECT * FROM employees WHERE salary < 70000;

Se devolverán las mismas dos filas para John Smith y Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

Sin embargo, cuando usa EXPLAIN de la siguiente manera:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

La tabla será diferente de la ejecución anterior de la misma consulta:

Output
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | salary        | salary | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

La columna key le dice que MySQL usó el índice para realizar la consulta. En rows, solo se analizaron dos filas para devolver el resultado. Esta vez, la columna Extra dice Using index condition, lo que significa que en este caso particular, MySQL filtró usando el índice y luego usó la tabla principal solo para recuperar el ya filas emparejadas.

Nota: A veces, aunque un índice esté presente y se pueda usar, MySQL decidirá no hacerlo. Por ejemplo, si ejecuta:

EXPLAIN SELECT * FROM employees WHERE salary < 140000;

El plan de ejecución será el siguiente:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | salary        | NULL | NULL    | NULL |   20 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Aunque salary aparece en possible_keys, la columna key vacía (que lee NULL) significa que MySQL decidió no utilice el índice, que se puede confirmar mediante las filas escaneadas 20. El planificador de consultas de la base de datos analiza cada consulta contra posibles índices para determinar la ruta de ejecución más rápida. Si el costo de acceder al índice supera el beneficio de usarlo (por ejemplo, si la consulta devuelve una parte significativa de los datos de la tabla original), la base de datos puede decidir que en realidad es más rápido realizar una exploración completa de la tabla.

De manera similar, las anotaciones en la columna Extra, como Using index condition o Using where, describen cómo el motor de la base de datos ejecutó la consulta con más detalle. . Dependiendo del contexto, la base de datos puede elegir otra forma de ejecutar la consulta y es posible que tenga salidas con la anotación Using index condition faltante, o presente otra anotación. Esto no significa que el índice no se use correctamente, sino que la base de datos decidió que una forma diferente de acceder a las filas sería más eficaz.

En esta sección, creó y usó índices de una sola columna para mejorar el rendimiento de las consultas SELECT que se basan en el filtrado en una sola columna. En la siguiente sección, explorará cómo se pueden usar los índices para garantizar la exclusividad de los valores en una columna determinada.

Uso de índices únicos para evitar la duplicación de datos

Como exploró en la última sección, un uso común de los índices es recuperar datos de manera más eficiente al ayudar al motor de la base de datos a trabajar menos para lograr el mismo resultado. Otro propósito es garantizar que los datos en la parte de la tabla en la que se define el índice no se repitan. Esto es lo que hace un índice único.

Evitar valores duplicados suele ser necesario para garantizar la integridad de los datos, ya sea desde un punto de vista lógico o técnico. Por ejemplo, no debe haber dos personas diferentes que usen el mismo número de seguro social, o un sistema en línea no debe permitir que se registren varios usuarios con el mismo nombre de usuario o dirección de correo electrónico.

En el caso del ejemplo de la tabla empleados de esta guía, el número de serie del dispositivo asignado es un campo que no debe contener duplicados. Si lo hiciera, significaría que dos empleados recibieron la misma computadora. En este punto, sin embargo, podría insertar fácilmente nuevos empleados con números de serie repetidos.

Intente insertar otro empleado con un número de serie de dispositivo que ya esté en uso:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

La base de datos complacerá e insertará la fila, notificándole el éxito:

Output
Query OK, 1 row affected (0.009 sec)

Sin embargo, si ahora consulta la base de datos de empleados que usan la computadora ABCD123 de esta manera:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Obtendrá dos personas diferentes como resultado:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|          21 | Sammy      | Smith     | ABC123        |  65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)

Este no es un comportamiento esperado para mantener válida la base de datos employees. Reviertamos este cambio eliminando la fila recién creada:

DELETE FROM employees WHERE employee_id = 21;

Puede confirmarlo volviendo a ejecutar la consulta anterior SELECT:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Una vez más, solo John Smith utiliza el dispositivo con el número de serie ABC123:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Para proteger la base de datos contra tales errores, puede crear un índice único en la columna device_serial.

Para hacerlo, ejecute:

CREATE UNIQUE INDEX device_serial ON employees(device_serial);

Agregar la palabra clave UNIQUE al crear el índice le indica a la base de datos que se asegure de que los valores en la columna device_serial no se puedan repetir. Con índices únicos, todas las filas nuevas agregadas a la tabla se compararán con el índice para determinar si el valor de la columna satisface la restricción.

La base de datos confirmará la creación del índice:

Output
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ahora, verifique si aún es posible agregar una entrada duplicada a la tabla. Intente ejecutar de nuevo la consulta INSERT anteriormente exitosa:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

Esta vez, se mostrará el mensaje de error:

Output
ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'

Puede verificar que la nueva fila no se agregó a la tabla usando la consulta SELECT nuevamente:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Ahora se devuelve una sola fila:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Los índices únicos, además de proteger contra entradas duplicadas, también son índices completamente funcionales para acelerar las consultas. El motor de base de datos utilizará índices únicos de la misma manera que en el paso anterior. Puede verificar esto ejecutando:

EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';

El plan de ejecución será similar al siguiente (su tabla puede diferir ligeramente):

Output
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

El índice device_serial se muestra tanto en possible_keys como en la columna key, lo que confirma que se utilizó el índice al realizar la consulta.

Ha utilizado índices únicos para protegerse contra datos duplicados en la base de datos. En la siguiente sección, utilizará índices que abarcan más de una columna.

Uso de índices en varias columnas

Hasta ahora, todos los índices que creó en las secciones anteriores se definieron usando un solo nombre de columna, correspondiente a los valores de la columna elegida. La mayoría de los sistemas de bases de datos admiten índices que abarcan más de una columna. Dichos índices, denominados índices de varias columnas, proporcionan una forma de almacenar valores para varias columnas en un solo índice, lo que permite que el motor de la base de datos ejecute consultas de manera más rápida y eficiente utilizando el conjunto de columnas juntas.

Las consultas de uso frecuente que deben optimizarse para el rendimiento suelen utilizar varias condiciones en la cláusula de filtrado WHERE. Un ejemplo de este tipo de consulta sería pedirle a la base de datos que busque a una persona por su nombre y apellido:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

El primer pensamiento para optimizar esta consulta con índices podría ser crear dos índices individuales, uno en la columna last_name y otro en la columna first_name. Sin embargo, esta no es la mejor opción para esta situación.

Si creó dos índices separados de esta manera, MySQL sabría cómo encontrar a todos los empleados llamados Smith. También sabría cómo encontrar a todos los empleados llamados John. Sin embargo, no sabría cómo encontrar personas llamadas John Smith.

Para ilustrar el problema de tener dos índices individuales, imagine tener dos guías telefónicas separadas, una ordenada por apellidos y otra por nombres. Ambos directorios telefónicos se asemejan a índices creados en las columnas last_name y first_name respectivamente. Como usuario de una guía telefónica, podría abordar el problema de encontrar a John Smith de tres maneras posibles:

• Use la guía telefónica ordenada por apellidos para buscar a todas las personas llamadas Smith, ignore la segunda guía telefónica y recorra manualmente todas las personas Smith una por una hasta que encuentre Juan Smith.
• Haga lo contrario: use la guía telefónica ordenada por nombre para buscar a todas las personas llamadas John, ignore la segunda guía telefónica y recorra manualmente todas las personas de John una por uno hasta que encuentres a John Smith.
• Intente usar ambas guías telefónicas juntas: encuentre a todas las personas llamadas John y por separado a todas las personas llamadas Smith, anote los resultados provisionales e intente intersectar manualmente los dos subconjuntos de datos que buscan personas que están en ambas listas individuales.

Ninguno de estos enfoques es ideal, y MySQL tiene opciones similares disponibles cuando se trata de múltiples índices inconexos y una consulta que solicita más de una condición de filtrado.

Otro enfoque sería usar índices que no tengan en cuenta una sola columna, sino muchas columnas. Puede imaginar esto como una guía telefónica colocada dentro de otra guía telefónica: primero busca el apellido Smith, lo que lo lleva al segundo catálogo de todas las personas llamadas Smith organizadas alfabéticamente por nombre, que puede usar para encontrar rápidamente a John.

Nota: a menudo se dice que MySQL puede usar solo un índice para cada tabla utilizada en una consulta. No siempre es cierto, ya que MySQL admite optimizaciones de combinación de índices para usar varios índices de forma conjunta cuando se ejecuta una consulta. Sin embargo, esta limitación es una buena regla general al crear índices. MySQL puede decidir no usar múltiples índices; incluso si lo hace, en muchos escenarios, no cumplirán el propósito tan bien como un índice dedicado.

En MySQL, para crear un índice de varias columnas para apellidos y nombres en la tabla employees, ejecute:

CREATE INDEX names ON employees(last_name, first_name);

En este caso, la instrucción CREATE INDEX difiere ligeramente. Ahora, entre paréntesis después del nombre de la tabla (empleados), se enumeran dos columnas: last_name y luego first_name. Esto crea un índice de varias columnas en ambas columnas. El orden en que se enumeran las columnas en la definición del índice es importante, como verá en un momento.

La base de datos mostrará el siguiente mensaje confirmando que creó el índice con éxito:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ahora, intente emitir la consulta SELECT para encontrar filas con el primer nombre que coincida con John y el apellido que coincida con Smith:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

El resultado es una sola fila con un empleado llamado John Smith:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Ahora use la consulta EXPLAIN para verificar si se usó el índice:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

El plan de ejecución será similar al siguiente (su tabla puede diferir ligeramente):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

La base de datos utilizó el índice names. Se escaneó una sola fila, por lo que la tabla no se recorrió más de lo necesario. La columna Extra dice Using index condition, lo que significa que MySQL podría completar el filtrado únicamente usando el índice.

Filtrar por nombres y apellidos utilizando el índice de varias columnas que abarca esas dos columnas proporciona a la base de datos una forma directa y rápida de encontrar los resultados deseados.

Con el índice definido en ambas columnas, ¿qué sucederá si intenta buscar a todos los empleados con el nombre Smith pero no filtra por el nombre? Ejecute la consulta modificada:

SELECT * FROM employees WHERE last_name = 'Smith';

La salida devolverá lo siguiente:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|          20 | Abigail    | Smith     | FGH890        | 155000 |
|          17 | Daniel     | Smith     | WXY901        | 140000 |
|           1 | John       | Smith     | ABC123        |  60000 |
|           5 | Michael    | Smith     | MNO345        |  80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)

Cuatro empleados tienen el apellido Smith.

Una vez más, acceda al plan de ejecución de la consulta:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

El plan de ejecución será similar al siguiente (su tabla puede diferir ligeramente):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 203     | const |    4 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Esta vez se devolvieron cuatro filas, ya que hay más de un empleado con ese apellido. Sin embargo, la tabla del plan de ejecución muestra que la base de datos usó el índice de varias columnas names para realizar esta consulta, escaneando solo 4 filas: el número exacto devuelto.

En las consultas anteriores, la columna utilizada para filtrar los resultados (last_name) se pasó primero en la instrucción CREATE INDEX. Ahora filtrará la tabla employees por first_name, que era la segunda columna en la lista de columnas para este índice de varias columnas. Ejecute la siguiente consulta:

SELECT * FROM employees WHERE first_name = 'John';

La salida regresará de la siguiente manera:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Accede al plan de ejecución de la consulta:

EXPLAIN SELECT * FROM employees WHERE first_name = 'John';

La salida regresará de la siguiente manera:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Una vez más, los resultados devueltos contienen un solo empleado, pero esta vez no se utilizó ningún índice. La base de datos escaneó toda la tabla, ilustrada por la anotación Using where en la columna Extra, así como 20 filas escaneadas.

En este caso, la base de datos no usó el índice debido al orden de las columnas pasadas a la instrucción CREATE INDEX cuando se creó el índice por primera vez: last_name, first_name. La base de datos solo puede usar el índice si la consulta usa la primera columna o la primera y la segunda columna; no puede admitir consultas contra el índice donde no se usa la primera columna de la definición del índice.

Con un índice creado en varias columnas, la base de datos puede usar el índice para acelerar las consultas que involucran todas las columnas indexadas o un prefijo creciente a la izquierda de todas las columnas indexadas. Por ejemplo, un índice de varias columnas que incluye las columnas (a, b, c) se puede usar para acelerar las consultas que involucran las tres columnas y las consultas que solo involucran las dos primeras columnas, o incluso Consultas que solo involucran la primera columna. Por otro lado, el índice no ayudará con consultas que involucren solo la última columna, c, o las dos últimas columnas, b y c.

Al elegir cuidadosamente las columnas incluidas en el índice y su orden, se puede usar un solo índice de varias columnas para acelerar varias consultas en la misma tabla. En este ejemplo, si asumimos que la búsqueda de empleados se realiza por nombre y apellido o solo por apellido, el orden proporcionado de las columnas en el índice names garantiza que el índice acelerará todas las consultas relevantes.

En esta sección, usó índices de varias columnas y aprendió sobre el orden de las columnas al especificar dicho índice. En la siguiente sección, aprenderá a administrar los índices existentes.

Listado y eliminación de índices existentes

En las secciones anteriores, creó nuevos índices. Dado que los índices tienen nombres y se definen en tablas particulares, también puede enumerarlos y manipularlos cuando sea necesario.

Para enumerar todos los índices que ha creado en este tutorial para la tabla employees, ejecute la siguiente instrucción:

SHOW INDEXES FROM employees;

La salida será similar a la siguiente:

Output
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | device_serial |            1 | device_serial | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | salary        |            1 | salary        | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            1 | last_name     | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            2 | first_name    | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

Dependiendo de su versión de MySQL, su salida puede diferir ligeramente, pero incluirá todos los índices, incluidos sus nombres, columnas utilizadas para definir el índice, información sobre su singularidad y otros detalles extensos de la definición del índice.

Para eliminar índices existentes, puede usar la instrucción SQL DROP INDEX. Imagine que ya no desea aplicar la exclusividad en la columna device_serial. Por lo tanto, el índice device_serial ya no será necesario. Ejecute el siguiente comando:

DROP INDEX device_serial ON employees;

device_serial es el nombre del índice y employees es la tabla en la que se definió el índice. La base de datos confirmará la eliminación del índice:

Output
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

A veces, los patrones de las consultas típicas cambiarán con el tiempo, o se destacarán nuevos tipos de consultas. Luego, es posible que deba volver a evaluar los índices usados, crear nuevos o eliminar los que no se usan para evitar degradar el rendimiento de la base de datos manteniéndolos actualizados.

Usando los comandos DROP INDEX, puede administrar índices en una base de datos existente, siguiendo las mejores prácticas para crear índices cuando sean necesarios y beneficiosos.

Conclusión

Al seguir esta guía, aprendió qué son los índices y cómo usar los tipos más comunes en MySQL para acelerar la recuperación de datos a través de consultas condicionales SELECT. Usó índices para mantener la exclusividad de los datos de columna y aprendió cómo los índices pueden afectar las consultas donde se usa más de una columna en la condición de filtrado.

Puede usar índices para dar forma al rendimiento de la base de datos de acuerdo con el tipo de consultas que se ejecutan con mayor frecuencia, logrando el equilibrio adecuado entre el rendimiento de lectura y escritura para casos de uso comunes. Este tutorial cubrió solo los conceptos básicos del uso de índices para ese propósito. Puede admitir consultas más complejas a través de índices al comprender cómo MySQL elige qué índices usar y cuándo usarlos. Para obtener más información al respecto, consulte la documentación de MySQL sobre índices.

Si desea obtener más información sobre los diferentes conceptos relacionados con el lenguaje SQL y cómo trabajar con él, le recomendamos que consulte las otras guías de la serie Cómo usar SQL.