Manutenção em tabelas – MySQL

Ola pessoal,

Manter a integridade dos dados e efetuar uma manutenção das tabelas em sua base de forma períodica, é importante para sempre manter um resultado eficaz em produção.

As tabelas em sua base podem ficar corrompidas devido a uma falha no servidor, principalmente quando ele é reiniciado de forma incorreta. Com isso, você pode perceber lentidões em consultas que geralmente são rapidas, porém podemos ter certeza ao consultar os logs de erro do MySQL.

Segue abaixo a relação de instruções SQL que podemos utilizar no dia a dia:

Analyze table:

Analisa e armazena a distribuição de chave de uma tabela, para uma tomada de decisão referente a junção das tabelas ou na utilização dos indices. Durante esta analise, a tabela ganha um nível de bloqueio para os engines InnoDB e MyISAM.

Esta instrução esta disponível para os Engines: InnoDB e MyISAM.

Ex feito em tabelas MyISAM e InnoDB:

Caso a tabela não tenha sido alterada desde o ultimo analyze, a mesma não será analisada novamente: ‘Table is already up to date’.

Check table:

Verifica a integridade da tabela a fim de identificar algum erro ou inconsistência.

É valida para os Engines: InnoDB, MyISAM, Archive. Caso seja executada em uma tabela MyISAM, as estatisticas dos indices também são atualizadas. Abaixo segue demais opções que podem ser incluidas apenas ao MyISAM:

– Quick: Não verifica os registros buscando as ligações incorretas. Validas para o Engine InnoDB e MyISAM e views.
– Fast: Verifica apenas as tabelas que não foram fechadas de forma correta. Valido apenas para tabelas MyISAM e views, e inválido para o InnoDB.
– Changed: Verifica somente as tabelas que foram alteradas desde a ultima analise ou que não foram fechadas de forma correta. Aplica-se para tabelas MyISAM e views, e inválido ao InnoDB.
– Medium: Efetua uma varredura para verificar se os registros/ligações removidas são válidas. Calcula também uma chave de conferência para os registros com base no check sum calculado para as chaves. Aplica-se para tabelas MyISAM e views, e inválido ao InnoDB.
– Extended: Faz uma consulta completa para todas as chaves de cada registro. Aplica-se para tabelas MyISAM e views, e inválido ao InnoDB.

Segue um exemplo de execução em tabelas MyISAM e InnoDB:

Checsum table:

Relata o checksum da tabela. Esta disponível para tabelas InnoDB e MyISAM, e atribuiu um nível de bloqueio enquanto o checksum é calculado.

Dispõe de duas opções:

– Quick: E feito a soma do checksum no mesmo instante e apresentado de forma rápida, porém é valido a tabelas MyISAM desde que você a tenha criado a tabela especificando o checksum = 1.

– Extended: É feito uma varredura na tabela de forma completa e o check sum é calculado, porém pode ser um processo demorado se tratando de tabelas grandes.

Segue alguns exemplos:

Optimeze Table

Como o próprio nome introduz, o objetivo deste é otimizar a tabela defragmentando o espaço não utilizado e reordenando as páginas de indices da tabela.

Uma tabela pode ficar fragmentada quando sofre vários inserts e updates, pois isso modifica a paginação do dado no disco, logo a referencia do indice junto a esse dado também é alterado, formando vários blocos inutilizados.

Esta opção é valida para os engines InnoDB, MyISAM e Archive. Com o engine InnoDB, o Optime Table é espelhado para o Alter table que ‘recria’ a tabela novamente para atualizar as estatísticas do indice e liberar espaço não utilizado.

Obs: Antes da versão 5.6, este espelhamento não é feito devido a uma concorrencia DML enquanto o Optimize é executado, logo você pode optar por rodar o alter table diretamente:

mysql> alter table table_name Engine = ‘InnoDB’;

Exemplo do Optimize:

Repair table

Esta instrução repara alguma tabela que esteja danificada ou propriamente corrompida. Este funciona apenas para os engines MyISAM e Archive.

Dispõe das seguintes opções:

– Quick: Repara somente o arquivo de indice .MYI e não o arquivo de dados.
– Extended O MySQL recria o indice linha por linha ao invés de criar um indice de uma vez com ordenação.
– use_frm O MySQL ira recriar o arquivo de indices .MYI com base no arquivo .FRM (Não recomendável)

Dificilmente será necessário efetuar um repair, já que o MySQL trabalha com este módulo de forma automática, da mesma forma estas são as opçoes disponíveis. Caso se trate de uma tabela muito grande, veremos ainda neste post uma melhor forma de resolver o problema.

Recomendo a leitura da Doc do MySQL sobre este assunto, principalmente para o uso do .FRM que deve ser utilizado em ultima instância.

Clientes sem conexão remota

Até o momento, vimos as opções de manutenção através de comandos SQL, porém também podemos efetuar este tipo de trabalho através da própria Shell do servidor, sem a necessidade de uma conexão remota.

Cliente mysqlcheck

Este cliente consegue verificar, reparar , otimizar e analisar as tabelas. Uma das principais opções é:

mysqlcheck –repair –quick: Será feito um reparo rapido.
mysqlcheck –repair: Opera com reparo normalmente se o rápido falhar
mysqlcheck –repair –force: A operação ira forçar um repair.

Obs: Ao iniciar o processo, a tabela ficara completamente lockada.

Segue alguns exemplos de utilização do mysqlcheck:

É possível passar também o nome da base e a tabela em especifico:

Você pode passar também o parametro –all-databases para que o check seja feito em todas as bases do servidor. Para consultar as demais opções deste cliente, recomendo a leitura da Documentação do MySQL.

Myisamchk

Este utilitário obtem as informações das suas tabelas MyISAM, quase igual ao mysqlcheck porém vem com maiores opções de manutenção aos arquivos de dados (MYD) e indices (MYI) do MyISAM.

Muitas vezes conseguimos resolver os problemas com estas tabelas com as instruções SQL a partir de uma conexão com o banco, porém caso seja necessário recorrer a esta ferramenta, certifique-se que você tenha o backup da base por precaução.

Particularmente, utilizo esta opção para reparar tabelas grandes do MyISAM, das quais demoram ou dão erro na conexão com o banco.

Para o primeiro exemplo, segue como verificar a tabela e repara-la em seguida:

Após o comando ‘myisamchk’, você deve passar o datadir da sua instancia MySQL e o nome da base, para a verificação em todas as tabelas MyISAM, ou passando o nomedatabase.MYI.

Você também pode optar por reparar todas as tabelas, conforme o exemplo:

Em casos de tabelas muito grandes, você consegue passar parametros de buffer e key jogando para uma partição exclusiva do seu servidor, que contenha mais espaço para concluir o reparo:

Os valores de buffer e key podem ser aumentados de acordo a disponibilidade do servidor.

Obs: Evite qualquer conexão a esta base neste momento para evitar qualquer concorrencia desnecessária.

Bem pessoal é isso, espero que essas dicas possam te ajudar no dia a dia.

Referencias utilizas:

https://dev.mysql.com/doc/refman/5.5/en/table-maintenance-sql.html
https://dev.mysql.com/doc/refman/5.6/en/mysqlcheck.html
https://dev.mysql.com/doc/refman/5.6/en/myisamchk.html

É isso ai pessoal.

Dúvidas, criticas ou sugestões? Fiquem a vontade, todo retorno é construtivo 🙂

Jose Wilson

Deixe uma resposta