Base de datos

Cómo usar índices para mejorar el rendimiento de las consultas MySQL

Este artículo analiza los índices y cómo puede usarlos para mejorar el rendimiento de las consultas a la base de datos MySQL.

Tabla de contenido
Rendimiento de la base de datos e índices
Analizando consultas de bases de datos
Agregar, eliminar y ver índices en una tabla

RENDIMIENTO DE LA BASE DE DATOS E ÍNDICES

Los índices de base de datos en MySQL le permiten acelerar el rendimiento de las instrucciones de consulta SELECT. Para tablas pequeñas, un índice no ayuda mucho. Sin embargo, si tiene tablas con una gran cantidad de datos, los índices pueden mejorar drásticamente el rendimiento.

Los siguientes comportamientos son todos indicios de que las tablas pueden beneficiarse al agregar índices:

Su sitio se carga muy lentamente o no se carga en absoluto.
Una aplicación no puede conectarse a su base de datos.
Las consultas de la base de datos están respaldadas.
Si experimenta alguno de estos problemas, debe analizar las consultas de su base de datos y considerar agregar índices.

Si tiene un VPS administrado o un servidor dedicado Flex administrado, un mayor tamaño del búfer de clasificación también puede ayudar, además de agregar índices. Abra un ticket en el Portal del cliente en https://my.a2hosting.com para analizar esta opción con nuestro Guru Crew.

ANALIZANDO CONSULTAS DE BASE DE DATOS

Para determinar qué tablas pueden beneficiarse del uso de índices, debe analizar las consultas de su base de datos. La instrucción EXPLAIN SELECT te ayuda a hacer esto. Para analizar las consultas de la base de datos, siga estos pasos:

Inicie sesión en su cuenta con SSH.
En el símbolo del sistema, escriba el siguiente comando, reemplazando el nombre de usuario con el nombre de usuario de su cuenta de A2 Hosting y la base de datos con el nombre de la base de datos:
mysql -u nombre de usuario -p base de datos
En el mensaje Ingresar contraseña, escriba su contraseña. Aparece el indicador mysql>.
Escriba el siguiente comando SQL

EXPLAIN SELECT * FROM table_name WHERE conditions \G
Si escribe su propio código de aplicación, ya conoce las instrucciones SELECT que usa para recuperar datos. Si está utilizando una aplicación de terceros, como WordPress o PrestaShop, es posible que deba examinar el código fuente o los registros de la base de datos para determinar las declaraciones SELECT exactas que se utilizan para recuperar datos.
El resultado de la instrucción EXPLAIN SELECT muestra cómo el optimizador de consultas MySQL ejecutará la consulta. Por ejemplo, considere el siguiente resultado:

mysql> EXPLAIN SELECT title FROM employees WHERE lastname LIKE ‘T%’ \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 142
Extra: Using where
1 row in set (0.00 sec)

En este ejemplo, salida:

Posibles_keys y valores clave son NULL, lo que indica que MySQL no tiene un índice que pueda usar para esta consulta.
El valor de las filas indica que MySQL leerá 142 filas para esta consulta. Si hay 142 filas en total en la tabla, esto significa que MySQL debe examinar cada fila para generar el conjunto de resultados. Esto podría llevar bastante tiempo para una mesa grande.
Por otro lado, si creáramos un índice llamado index_name para la columna de apellido, MySQL podría generar el siguiente resultado para la misma consulta:

mysql> EXPLAIN SELECT title FROM employees WHERE lastname LIKE ‘T%’ \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: range
possible_keys: index_name
key: index_name
key_len: 22
ref: NULL
rows: 17
Extra: Using where; Using index
1 row in set (0.00 sec)

Como puede ver, los possible_keys y los valores clave indican que MySQL ha encontrado un índice que puede usar para optimizar la consulta. Además, MySQL leerá solo 17 filas para generar el conjunto de resultados, en lugar de las 142 filas. (Esto significa que la tabla tiene 17 filas donde el apellido comienza con “T”). Por último, el valor Extra también indica que MySQL usará un índice para la consulta.

AÑADIR, ELIMINAR Y VER ÍNDICES EN UNA TABLA

Después de analizar las consultas de la base de datos y determinar dónde están los cuellos de botella de rendimiento, está listo para agregar índices. Para hacer esto, siga estos pasos:

Inicie sesión en su cuenta con SSH.
En el símbolo del sistema, escriba el siguiente comando, reemplazando el nombre de usuario con el nombre de usuario de su cuenta de A2 Hosting y la base de datos con el nombre de la base de datos:
mysql -u nombre de usuario -p base de datos
En el mensaje Ingresar contraseña, escriba su contraseña. Aparece el indicador mysql>.
Para agregar un índice a una tabla, escriba el siguiente comando SQL. Reemplace table_name con el nombre de la tabla, index_name con el nombre del nuevo índice (que puede ser cualquier cosa que desee) y table_column con el nombre de la columna de la tabla para la que desea agregar el índice:

ALTER TABLE table_name ADD INDEX index_name (table_column);
Aunque los índices pueden mejorar el rendimiento, los índices también pueden afectar negativamente el rendimiento si hay demasiados. Esto se debe a que cuanto más índices tiene una tabla, más trabajo debe hacer MySQL para mantenerlos actualizados. El truco es encontrar el equilibrio adecuado entre suficientes índices para mejorar el rendimiento, pero no tantos que afecten negativamente el rendimiento.
Para eliminar un índice de una tabla, escriba el siguiente comando SQL. Reemplace table_name con el nombre de la tabla y reemplace index_name con el nombre del índice que desea eliminar:

ALTER TABLE table_name DROP INDEX index_name;
Para ver todos los índices de una tabla, escriba el siguiente comando SQL. Reemplace table_name con el nombre de la tabla:

MOSTRAR ÍNDICE DE nombre_tabla \ G
Después de crear un índice, debe usar la instrucción EXPLAIN SELECT nuevamente para ver cómo se ven afectadas las consultas. Además, debe continuar supervisando el rendimiento de la base de datos para ver si hay mejoras. Es posible que deba ejecutar una serie de pruebas para encontrar la cantidad óptima de índices para su base de datos.

MÁS INFORMACIÓN

Para obtener más información sobre la optimización e índices de MySQL.
Para obtener más información sobre la declaración EXPLAIN.

ARTÍCULOS RELACIONADOS

Conectando a MySQL desde la línea de comando
Aprenda cómo conectarse a MySQL desde la línea de comandos utilizando el programa mysql con esta guía que incluye instrucciones detalladas, fragmentos de código y enlaces a artículos relacionados.

Optimización y reparación de bases de datos MySQL con phpMyAdmin
Aprenda a usar phpMyAdmin para optimizar y reparar bases de datos MySQL.

Beto López
Ingeniero de Software y programador web "Full stack" que ha dejado una startup dónde los miércoles eran día de yoga, para construir un servicio de soporte técnico estandarizado low cost, más profundo que el que el servicio que ofrecen las compañias de hosting, para particulares, autónomos y empresas digitales. Web Personal, LinkedinTwitter.


Cómo usar índices para mejorar el rendimiento de las consultas MySQL
Te respondemos rápidamente
Contacta
Do NOT follow this link or you will be banned from the site!
Php Ninja
icono whatsapp programador web