Resumo : neste tutorial você aprenderá como usar a OPTIMIZE TABLEinstrução MySQL para melhorar o desempenho do banco de dados.
Introdução à instrução MySQL OPTIMIZE TABLE
A OPTIMIZE TABLEinstrução permite reorganizar o armazenamento físico dos dados da tabela para recuperar espaço de armazenamento não utilizado e melhorar o desempenho ao acessar a tabela.
Na prática, você achará a OPTIMIZE TABLEdeclaração útil nos seguintes casos:
- Exclusões/atualizações frequentes: se uma tabela tiver atualizações ou exclusões frequentes , seus dados poderão ser fragmentados. A
OPTIMIZE TABLEdeclaração pode ajudar a reorganizar a estrutura de armazenamento e eliminar o desperdício de espaço. - Tabela com linhas de comprimento variável: tabelas com dados de comprimento variável, como
VARCHAR,TEXT, eBLOBpodem ficar fragmentadas com o tempo. Usando aOPTIMIZE TABLEinstrução, você pode reduzir a sobrecarga de armazenamento. - Crescimento e redução significativos de dados: se seu banco de dados sofrer crescimento e redução significativos, você poderá executá-lo
OPTIMIZE TABLEperiodicamente para manter a eficiência de armazenamento ideal.
No geral, o uso da OPTIMIZE TABLEinstrução ajuda a otimizar o espaço de armazenamento dos dados da tabela e a melhorar o desempenho da consulta.
A OPTIMIZE TABLEinstrução funciona com tabelas InnoDB, MyISAM e ARCHIVE.
Exemplos de instruções MySQL OPTIMIZE TABLE
Vejamos alguns exemplos de uso da OPTIMIZE TABLEinstrução MySQL.
1) Usando a instrução MySQL OPTIMIZE TABLE para tabelas MyISAM
Para tabelas MyISAM, a OPTIMIZE TABLEinstrução funciona da seguinte forma:
- Repare a tabela se ela tiver excluído ou dividido linhas.
- Classifique as páginas de índice se elas não estiverem classificadas.
- Atualize as estatísticas da tabela se elas não estiverem atualizadas.
O exemplo a seguir ilustra as etapas para otimizar uma tabela MyISAM:
Primeiro, verifique o status da tabela usando a show table statusinstrução:
SHOW TABLE STATUS LIKE '<table_name>'\GLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ele retornará a saída assim:
*************************** 1. row ***************************
Name: <table_name>
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 5000
Avg_row_length: 44
Data_length: 440000
Max_data_length: 281474976710655
Index_length: 105472
Data_free: 220000
Auto_increment: 10001
Create_time: 2023-11-21 07:34:39
Update_time: 2023-11-21 07:38:43
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Existem duas colunas importantes na saída em relação à otimização da tabela:
- Representa
Data_lengtho espaço usado por todas as linhas da tabela, incluindo qualquer sobrecarga como cabeçalhos de linha. - A
Data_freeé a quantidade de espaço livre (em bytes) no arquivo de dados. Indica quanto espaço pode ser potencialmente recuperado pelaOPTIMIZE TABLEdeclaração.
Segundo, otimize a tabela usando a OPTIMIZE TABLEinstrução:
OPTIMIZE TABLE <table_name>;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.text_data | optimize | status | OK |
+----------------+----------+----------+----------+
1 row in set (0.05 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O Msg_texté OKque indica que a otimização foi bem-sucedida.
Terceiro, verifique novamente o status da tabela:
SHOW TABLE STATUS LIKE '<table_name>'\GLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Se o espaço de tabela estiver fragmentado, você verá Data_freeque é zero:
*************************** 1. row ***************************
Name: <table_name>
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 5000
Avg_row_length: 44
Data_length: 440000
Max_data_length: 281474976710655
Index_length: 105472
Data_free: 0
Auto_increment: 10001
Create_time: 2023-11-21 08:03:12
Update_time: 2023-11-21 08:03:41
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )2) Usando a instrução MySQL OPTIMIZE TABLE para tabelas InnoDB
Ao executar a OPTIMIZE TABLEinstrução nas InnoDBtabelas, você obterá a seguinte saída:
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A primeira mensagem:
Table does not support optimize, doing recreate + analyze insteadLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Isso significa que OPTIMIZE TABLEnão otimiza as InnoDBtabelas da mesma forma que otimiza as tabelas MyISAM. Em vez disso, a OPTIMIZE TABLEinstrução executa as seguintes ações:
- Primeiro, crie uma nova tabela vazia.
- Segundo, copie todas as linhas da tabela original para a nova tabela.
- Terceiro, exclua a tabela original e renomeie as novas tabelas.
- Finalmente, execute a
ANALYZEinstrução para coletar estatísticas da tabela.
Um cuidado é evitar executar a OPTIMIZE TABLEinstrução em uma InnoDBtabela grande quando o espaço em disco estiver baixo, pois isso provavelmente fará com que o servidor fique sem espaço ao tentar recriar a tabela grande.
Resumo
- Use a
OPTIMIZETABLEinstrução para reorganizar o armazenamento físico de tabelas para reduzir o uso do espaço em disco e melhorar o tempo de execução da consulta.
