Optimizar tablas y bases de datos en MySQL

1. Cuándo y por qué optimizar tablas en MySQL
2. Optimizar tablas en MySQL
3. Use MySQL Shell en el sistema operativo Windows/Ubuntu 20.04 para optimizar tablas únicas o múltiples
4. Use Windows Terminal para optimizar la tabla
5. Utilice la terminal de Ubuntu 20.04 para optimizar la tabla
6. Optimice todas las tablas en una base de datos MySQL
7. Optimizar todas las bases de datos en MySQL

Este tutorial es una guía completa sobre cómo podemos optimizar tablas y bases de datos en MySQL. Usaremos dos sistemas operativos, Windows y Linux (Ubuntu 20.04).

También educa sobre la importancia de la optimización en MySQL.

Cuándo y por qué optimizar tablas en MySQL

Existe una mayor posibilidad de que los archivos de datos en MySQL estén fragmentados si nuestra aplicación está realizando muchas operaciones de ELIMINAR y ACTUALIZAR en una base de datos. Da como resultado un espacio no utilizado que también puede afectar el rendimiento.

Es muy necesario que desfragmentemos nuestras tablas MySQL de forma continua y recuperemos el espacio no utilizado. Aquí es donde necesitamos la optimización de tablas en MySQL, que admite el reordenamiento de los datos en un servidor almacenamiento dedicado que, en última instancia, mejora el rendimiento y la velocidad de entrada y salida de datos.

Ahora, ¿cómo sabemos qué tabla debemos optimizar? Debemos optimizar las tablas donde la información (datos) se actualiza continuamente; por ejemplo, las bases de datos transaccionales son los candidatos perfectos para la optimización de tablas.

Sin embargo, las consultas de optimización pueden consumir más tiempo según el tamaño de las tablas y la base de datos. Por lo tanto, no es bueno que un sistema de transacciones bloquee una tabla durante muchas horas.

En su lugar, podemos probar algunos trucos en la tabla del motor INNODB. Algunos de los trucos se enumeran a continuación:

1. A veces, optimizar el valor incorrecto puede resultar en fragmentación con índices secundarios, por lo que es importante analizar cómo obtener más beneficios al compactar un valor en particular. Significa que la identificación del valor correcto para la optimización es muy importante.
2. Otra forma es eliminar el índice, optimizar las tablas y volver a agregar los índices. Esta forma solo es aplicable si la tabla puede funcionar sin índices por un corto tiempo.

Optimizar tablas en MySQL

Primero, debemos analizar las tablas que queremos optimizar. Debemos estar conectados a nuestra base de datos usando el siguiente comando.

Código de ejemplo:

-- Syntax: Use your_database_name;
mysql> USE test;

Una vez conectado con la base de datos deseada, utilice la siguiente consulta para obtener el estado de la tabla.

Código de ejemplo:

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

PRODUCCIÓN:

Tenemos dos propiedades importantes para saber si debemos optimizar esta tabla o no.

1. El Data_length habla de cuánto espacio ocupa una base de datos.
2. El Data_free indica los bytes asignados pero no utilizados dentro de una tabla de base de datos.

Esta información nos guía para identificar qué tabla necesita optimización y qué cantidad de espacio recuperaremos después.

Podemos obtener estos dos números (Data_length y Data_free) para todas las tablas en una base de datos particular usando la siguiente consulta. Actualmente, solo tenemos una tabla llamada test_table en la base de datos test.

Código de ejemplo:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

PRODUCCIÓN:

La consulta anterior imprime los nombres de la tabla, el espacio total en bytes y el espacio no utilizado asignado en bytes. Si se siente cómodo trabajando en Megabytes, puede usar la siguiente consulta para obtener resultados en Megabytes.

Código de ejemplo:

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

Aunque la tabla de ejemplo dada no está muy fragmentada, podemos recuperar el espacio usando el comando OPTIMIZE TABLE.

Aprenderemos a optimizar tablas o bases de datos simples/múltiples utilizando MySQL shell y terminal.

Use MySQL Shell en el sistema operativo Windows/Ubuntu 20.04 para optimizar tablas únicas o múltiples

Código de ejemplo:

mysql> OPTIMIZE TABLE test_table;

PRODUCCIÓN:

Para optimizar varias tablas, use la siguiente consulta.

Código de ejemplo:

mysql> OPTIMIZE TABLE tableName1, tableName2;

Ahora, use el siguiente comando para confirmar que la tabla deseada está optimizada.

Código de ejemplo:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

PRODUCCIÓN:

Las mismas consultas funcionarán en MySQL shell en el sistema operativo Linux (Ubuntu 20.04).

Use Windows Terminal para optimizar la tabla

Código de ejemplo:

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

Una vez que escribimos el comando anterior y presionamos Enter, se nos pedirá que ingresemos la contraseña root de MySQL. Solo ingresa eso.

Debemos estar en la carpeta bin para ejecutar la consulta (ver el siguiente ejemplo).

Código de ejemplo:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

Utilice la terminal de Ubuntu 20.04 para optimizar la tabla

Si iniciamos sesión como superusuario usando el comando sudo su, ejecute la consulta como se indica a continuación. Solo pedirá una contraseña root de MySQL.

Código de ejemplo:

mysqlcheck -o test test_table -u root -p

Si no estamos registrados como superusuario, ejecutamos el comando mysqlcheck. Aquí, se nos pedirá que ingresemos la contraseña root del sistema y la contraseña root de MySQL.

Código de ejemplo:

sudo mysqlcheck -o test test_table -u root -p

Optimice todas las tablas en una base de datos MySQL

Optimice todas las tablas usando la línea de comandos de Windows:

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

Optimice todas las tablas usando la terminal de Ubuntu:

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

Optimizar todas las bases de datos en MySQL

Optimice todas las bases de datos mediante la línea de comandos de Windows:

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

Optimice todas las bases de datos usando la terminal de Ubuntu:

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p