Engines InnoDB e MyISAM

Uma mesma manutenção pode servir para uma tabela e não fazer sentido em outra, e um ROLLBACK pode terminar sem desfazer nada. O engine, o mecanismo de armazenamento de cada tabela, define como os dados são guardados e quais recursos existem. Por isso, conferir o engine é o primeiro passo antes de escolher qualquer manutenção.

O engine de cada tabela

O engine é escolhido tabela por tabela, e um mesmo banco pode ter tabelas em engines diferentes. Alguns comandos mostram essa informação:

• SHOW TABLE STATUS LIKE 'vendas';: mostra o engine da tabela na coluna Engine;
• SHOW CREATE TABLE vendas;: mostra o engine no fim da definição, depois da palavra ENGINE;
• SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'loja';: lista o engine de todas as tabelas do banco loja;
• SHOW ENGINES;: lista os engines do servidor, marca o padrão com a palavra DEFAULT e os indisponíveis com a palavra NO.

Uma tabela criada sem a indicação do engine usa o padrão do servidor. O InnoDB é o padrão a partir do MySQL 5.5, e o MyISAM era o padrão nas versões anteriores, embora o instalador do Windows dessas versões costume definir o InnoDB no arquivo de configuração. Como o padrão pode ter sido alterado, a versão do servidor não prova o engine de uma tabela. Uma tabela criada depois de uma conversão para o InnoDB também precisa ser conferida.

Quando o comando de criação ou de conversão indica um engine desligado ou inexistente, o resultado depende do modo do servidor. Com a opção NO_ENGINE_SUBSTITUTION, o comando falha com o erro 1286, de engine desconhecido. Sem ela, a criação usa o engine padrão, a conversão deixa a tabela no engine em que estava, e o MySQL registra apenas um aviso.

O InnoDB

• Transações: as alterações podem ser confirmadas com COMMIT ou desfeitas com ROLLBACK.
• Bloqueio por linha: uma gravação bloqueia as linhas envolvidas, e as demais linhas da tabela continuam livres para outras gravações.
• Recuperação depois de uma queda: o InnoDB registra as alterações em arquivos próprios antes de gravá-las nas tabelas. Depois de uma queda de energia ou de um encerramento abrupto do serviço, ele usa esses registros para refazer o que estava confirmado e desfazer o que não estava.
• Organização pela chave primária: as linhas ficam guardadas na ordem da chave primária.
• Chaves estrangeiras: entre os engines comuns, o InnoDB é o que aplica essas regras.

A recuperação automática trata a interrupção do serviço. Ela não corrige um disco com defeito, nem um arquivo danificado por uma cópia malfeita.

O MyISAM

• Sem transações: cada alteração vale no momento em que é feita. Um ROLLBACK não desfaz as alterações em tabelas MyISAM, e o MySQL avisa, com a mensagem 1196, que algumas tabelas não transacionais não puderam ser desfeitas.
• Bloqueio por tabela: uma gravação bloqueia a tabela inteira, e uma leitura longa faz as gravações esperarem.
• Marca de tabela danificada: depois de uma queda, a tabela pode ficar marcada como danificada. Nesse caso, o MySQL recusa o acesso com a mensagem de tabela marcada como danificada, de código 145 ou 1194, até que a tabela seja verificada e reparada.
• Sem chaves estrangeiras: a declaração é aceita, mas não tem efeito.

O nome MyISAM vem do modelo ISAM, de acesso sequencial indexado. O MyISAM foi o engine padrão por muitos anos e ainda aparece em instalações antigas.

Os arquivos de cada engine

O MySQL guarda os arquivos na pasta de dados, com uma subpasta para cada banco. O comando SELECT @@datadir; mostra o caminho da pasta de dados. Dentro da subpasta loja, os arquivos de cada tabela dependem do engine e da versão:

• InnoDB com um arquivo por tabela: vendas.ibd, com os dados e os índices da tabela. Esse arranjo é o padrão a partir do MySQL 5.6.
• InnoDB no espaço compartilhado: em instalações antigas, várias tabelas ficam juntas no arquivo ibdata1, na raiz da pasta de dados, e não existe um arquivo próprio para cada tabela.
• MyISAM: vendas.MYD, com os dados, e vendas.MYI, com os índices e o estado da tabela.
• Definição da tabela: até o MySQL 5.7, cada tabela tem também um arquivo vendas.frm. No MySQL 8, as definições ficam no dicionário de dados do servidor, e as tabelas MyISAM ganham um arquivo com a extensão sdi.

O InnoDB também mantém, na raiz da pasta de dados, os arquivos de registro das alterações, usados na recuperação depois de uma queda.

Arquivos não são cópia de segurança

Copiar apenas o arquivo vendas.ibd não produz uma cópia restaurável. O arquivo depende do dicionário de dados e dos registros de alterações do InnoDB, e uma cópia feita com o serviço em execução pode ficar inconsistente. A cópia de segurança de um banco usa uma exportação lógica, como a feita pelo mysqldump, ou um procedimento físico próprio, com o serviço parado ou com uma ferramenta feita para isso.

Trocar o engine de uma tabela

• ALTER TABLE vendas ENGINE = InnoDB;: converte a tabela para o InnoDB.

A conversão reconstrói a tabela inteira, ocupa espaço em disco durante a cópia e pode bloquear as gravações enquanto dura. É uma mudança de estrutura, que não se desfaz com ROLLBACK, e por isso exige uma cópia de segurança antes e uma janela de manutenção combinada.