Cómo optimizar tablas MySQL

Introducción

La optimización de las tablas de MySQL ayuda a reordenar la información en un servidor de almacenamiento dedicado para mejorar las velocidades de entrada y salida de datos. Sin embargo, saber cuándo usar cada función de optimización y cómo aplicarlas a su situación es clave para un mantenimiento viable de la tabla.

Este artículo proporciona consejos prácticos y funciones para la optimización de tablas MySQL.

Requisitos previos

• MySQL versión 8.0 instalado y configurado
• Acceso al símbolo del sistema o línea de comandos
• Tablas de bases de datos fragmentadas

¿Por qué debería optimizar las tablas de MySQL?

La razón principal de una tabla MySQL no optimizada es que se realizan con frecuencia consultas de actualización y eliminación. A su vez, esto provoca fragmentación y tiene algunas consecuencias:

1. La tabla de la base de datos ocupa más espacio de lo que necesita.

2. Consultar datos lleva más tiempo de lo que debería.

Las técnicas de optimización de tablas de MySQL abordan la disposición de los datos dentro de una base de datos. El resultado es un almacenamiento limpio sin espacio redundante sin utilizar, lo que ayuda a acelerar las consultas.

¿Cuándo debería optimizar las tablas de MySQL?

Las tablas en las que la información de una base de datos se actualiza continuamente, como las bases de datos transaccionales, son las candidatas más probables para la optimización.

Sin embargo, según el tamaño de la base de datos, la consulta de optimización tarda mucho en terminar con tablas más grandes. Por lo tanto, bloquear una tabla durante muchas horas no es beneficioso para los sistemas transaccionales.

En lugar de optimizar una tabla de inmediato, considere probar algunos de estos trucos para las tablas del motor INNODB:

• Eliminar índice, optimizar y volver a agregar índice . Si una tabla funciona sin índices durante algún tiempo, eliminar, optimizar y volver a agregar los índices funciona más rápido en algunos casos.
• Analizar qué valor se beneficia más al compactar . A veces, el índice primario no es útil o problemático. La optimización para el valor incorrecto provoca la fragmentación con índices secundarios.

Buscar tablas para optimización

Hay varias formas de mostrar tablas y analizarlas para su optimización. Comience conectándose a su base de datos MySQL:

use <database name>

Dependiendo de su caso de uso, filtre la información necesaria siguiendo uno de los consejos a continuación.

Sugerencia 1:muestra el espacio no utilizado en una tabla

Consulta el estado de la tabla deseada con:

show table status like "<table name>" \G

El resultado muestra información general sobre la tabla. Los siguientes dos números son importantes:

• Data_length representa la cantidad de espacio que ocupa la base de datos en total.
• Data_free muestra los bytes no utilizados asignados dentro de la tabla de la base de datos. Esta información ayuda a identificar qué tablas necesitan optimización y cuánto espacio se liberará después.

Sugerencia 2:muestra el espacio no utilizado para todas las tablas

El esquema de información almacena metadatos sobre un esquema de base de datos. Para verificar los datos no utilizados asignados para todas las tablas en un esquema seleccionado, ejecute:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

La consulta muestra el nombre de la tabla, el espacio total y el espacio asignado no utilizado. Por defecto, la memoria se imprime en bytes.

Sugerencia 3:Mostrar datos en megabytes

Imprime los datos en megabytes con:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Las tablas de ejemplo no están muy fragmentadas. Sin embargo, usando optimize table ayuda a liberar algo de espacio de la test_table .

Optimizar tablas

Hay varias formas de optimizar las tablas mediante la desfragmentación. Sin embargo, los pasos generales que realiza son similares.

Primero, el proceso hace una copia temporal de la tabla donde ocurre la optimización. Luego, cuando finaliza la operación, la función reemplaza la tabla original con la optimizada, renombrándola después del original.

Sugerencia 1:optimizar una tabla con MySQL

MySQL proporciona una función para optimizar una tabla. Para optimizar una tabla, use:

OPTIMIZE TABLE <table name>;

La salida muestra un mensaje de estado informativo sobre las acciones y los resultados de la optimización en un formato tabular.

Consejo 2:Optimice varias tablas a la vez

Para optimizar varias tablas a la vez, use:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

El resultado muestra el estado de la optimización para cada tabla optimizada.

Consejo 3:Optimice las tablas usando la terminal

Realice la optimización de la tabla a través de la terminal de Linux con:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Por ejemplo, para realizar la verificación en un esquema llamado prueba y tabla llamada test_table usando la raíz credenciales de usuario, ejecute:

sudo mysqlcheck -o test test_table -u root

Para realizar la optimización en varias tablas, separe cada nombre de tabla con un espacio:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Después de la optimización

Los resultados de la optimización cambian los valores de data_length y data_free de la tabla optimizada. Ambos valores se reducen, indicando:

1. La optimización liberó la memoria asignada no utilizada.

2. La memoria total de la base de datos es menor debido al espacio liberado.