Manutenção em Tabelas no MySQL - Parte II

Continuando o post anterior hoje vou falar das ferramentas utilizadas para a manutenção de tabelas do MySQL.

CHECK TABLE

A instrução CHECK TABLE executa uma verificação de integridade de conteúdo e estrutura da tabela. Ele funciona para tabelas MyISAM e InnoDB,ARCHIVE, and CSV. Para tabelas MyISAM, ele também atualiza as estatísticas de índice. Se a tabela é uma view, CHECK TABLE verifica a definição da view. Se a saída de CHECK TABLE indica que uma tabela possui problemas, a tabela deve ser reparada.

mysql> check tables action;

+---------------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------------------+-------+----------+----------+

| qb3_migracao.action | check | status | OK |

+---------------------+-------+----------+----------+

1 row in set (15.93 sec)

Para maiores informações consulte o link :

http://dev.mysql.com/doc/refman/5.5/en/check-table.html

REPAIR TABLE

O REPAIR TABLE corrige problemas em uma tabela que foi corrompida.

Ele só funciona para tabelas MyISAM,ARCHIVE, and CSV.

Para executa-lo é necessário ter privilégios de Select e insert sobre a tabela.

È extremamente importante antes de executar um repair table, que seja feito um backup dos dados.

Se o servidor falhar durante a execução do REPAIR TABLE, é essencial depois de reiniciá-lo de que você execute imediatamente outro REPAIR TABLE na tabela antes de executar quaisquer outras operações sobre ela. Na pior das hipóteses, você pode ter um novo arquivo de índice limpo sem informações sobre o arquivo de dados e, em seguida, a próxima operação de executar poderia substituir o arquivo de dados. Este é um cenário improvável, mas possível que ressalta o valor de fazer um backup primeiro.

Obs: O Comando Repair table por default escreve no log binario e se for um servidor master de uma replicação, vai replicar a instrução para todos os slaves, então nesse caso é necessario que se rode a opção NO_WRITE_TO_BINLOG.

Maiores informações acesse :

http://dev.mysql.com/doc/refman/5.5/en/repair-table.html

ANALYZE TABLE

O ANALYZE TABLE atualiza a tabela com as informações sobre a distribuição das chaves da tabela. Essas informações são usadas pelo optmizador para fazer melhores escolhas sobre os planos de execução das consultas SQL.

Para executa-lo é necessário ter privilégios de Select e insert sobre a tabela.

Obs: O Analyze table por default escreve no log binario e se for um servidor master de uma replicação, vai replicar a instrução para todos os slaves, então nesse caso é necessario que se rode a opção NO_WRITE_TO_BINLOG.

O MySQL utiliza a distribuição de chaves para decidir a ordem em que as tabelas devem ser unidas quando você executar um join em alguma coisa que não seja uma constante. Além disso, a distribuição das chaves podem ser usadas para decidir quais os índices de uma tabela será utilizado dentro de uma consulta.

mysql> analyze table action;

+---------------------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------------------+---------+----------+----------+

| qb3_migracao.action | analyze | status | OK |

+---------------------+---------+----------+----------+

1 row in set (0.00 sec) 

Maiores informações acesse :

http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html

OPTIMIZE TABLE

A instrução optimize table efetua a desfragmentação de uma tabela MyISAM. Ela recupera o espaço não utilizado, causado pela fragmentação das operações delete e update. Optimize table table classifica as paginas e as estatisticas do indices.

Optimize table também funciona em tabelas innodb, porém não efetua a desfragmentação das tabelas, para isso é necessario que se execute um ALTER TABLE nome_da_tabela engine=innodb;

È interessante ressaltar que essa operação é extremamente custosa, bloqueia as tabelas, e se for feita em produção, é necessario que seja realizado em um horário que que não seja o de pico.

Obs: O optimize Table por default escreve no log binario e se for um servidor master de uma replicação, vai replicar a instrução para todos os slaves, então nesse caso é necessario que se rode a opção NO_WRITE_TO_BINLOG.

mysql> OPTIMIZE TABLE action;

+---------------------+----------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------------------+----------+----------+----------+

| qb3_migracao.action | optimize | status | OK |

+---------------------+----------+----------+----------+

1 row in set (0.04 sec)

Maiores informações acesse :

http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

Agora que falei das ferramentas vou mostrar uma procedure para automatizar esses recursos:

A procedure abaixo realiza um optimize em todas as tabelas do banco que você especificar, e dependendo da sua demanda você pode transforma-la em um evento do mysql, ou ainda criar um script para chama-la de tempo em tempo.

DELIMITER &&

CREATE PROCEDURE OPTIMIZE_TABLES()

BEGIN

DECLARE v_nome_table varchar(60);

DECLARE done INT DEFAULT 0;

DECLARE cur1 CURSOR FOR select table_name from information_schema.columns where table_schema='NOME_DO_BANCO' ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT

FETCH cur1 INTO v_nome_table;

IF NOT done THEN

SET @v_comando := concat("OPTIMIZE TABLES nome_do_banco.", v_nome_table, ";");

PREPARE stmt FROM @v_comando;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

end if;

UNTIL done END REPEAT;

CLOSE cur1;

END

&&

DELIMITER ;