Resumo : neste tutorial você aprenderá como usar a OPTIMIZE TABLE
instrução MySQL para melhorar o desempenho do banco de dados.
Introdução à instrução MySQL OPTIMIZE TABLE
A OPTIMIZE
TABLE
instruçã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 TABLE
declaraçã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 TABLE
declaraçã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
, eBLOB
podem ficar fragmentadas com o tempo. Usando aOPTIMIZE TABLE
instruçã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 TABLE
periodicamente para manter a eficiência de armazenamento ideal.
No geral, o uso da OPTIMIZE TABLE
instrução ajuda a otimizar o espaço de armazenamento dos dados da tabela e a melhorar o desempenho da consulta.
A OPTIMIZE TABLE
instrução funciona com tabelas InnoDB, MyISAM e ARCHIVE.
Exemplos de instruções MySQL OPTIMIZE TABLE
Vejamos alguns exemplos de uso da OPTIMIZE TABLE
instrução MySQL.
1) Usando a instrução MySQL OPTIMIZE TABLE para tabelas MyISAM
Para tabelas MyISAM, a OPTIMIZE TABLE
instruçã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 status
instrução:
SHOW TABLE STATUS LIKE '<table_name>'\G
Linguagem 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_length
o 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 TABLE
declaração.
Segundo, otimize a tabela usando a OPTIMIZE TABLE
instruçã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
é OK
que indica que a otimização foi bem-sucedida.
Terceiro, verifique novamente o status da tabela:
SHOW TABLE STATUS LIKE '<table_name>'\G
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Se o espaço de tabela estiver fragmentado, você verá Data_free
que é 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 TABLE
instrução nas InnoDB
tabelas, 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 instead
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Isso significa que OPTIMIZE
TABLE
não otimiza as InnoDB
tabelas da mesma forma que otimiza as tabelas MyISAM. Em vez disso, a OPTIMIZE
TABLE
instruçã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
ANALYZE
instrução para coletar estatísticas da tabela.
Um cuidado é evitar executar a OPTIMIZE
TABLE
instrução em uma InnoDB
tabela 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
OPTIMIZE
TABLE
instruçã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.