Tipos de tabelas do MySQL

O fato de o MySQL possuir diferentes “tipos” de tabela pode causar estranheza aos novos usuários deste servidor. Atualmente, são suportados seis formatos de tabela: ISAM, MyISAM, MERGE, HEAP, InnoDB e BDB (até a versão 3.22 havia também o tipo Gemini).

Apesar de cada formato oferecer características específicas o trabalho do desenvolvedor praticamente não muda, pois os diferentes comportamentos são implementados, na maioria dos casos, de forma totalmente transparente para a aplicação. Além disso, os diferentes tipos de tabela podem ser misturados num mesmo banco de dados ou combinados numa mesma instrução SQL.

As tabelas podem ser divididas em dois grupos: não-transacionais (ISAM, MyISAM, MERGE e HEAP) e transacionais (InnoDB e BDB). As diferenças são claras: tabelas do primeiro grupo são mais rápidas, mas não podem ser manipuladas sob transações (cada operação é atômica). As tabelas do segundo grupo são menos velozes, mas oferecem as propriedades ACID (Atomicidade, Consistência, Isolação e Durabilidade) de um ambiente transacional.

ISAM

Foi o tipo padrão de tabela até a versão 3.22. As tabelas ISAM foram evoluídas para as atuais MyISAM e, embora ainda estejam incluídas nos fontes da versão 4.1, não estarão mais presentes a partir da versão 5.0 do MySQL.

Uma tabela ISAM pode ser atualizada para MyISAM com o comando abaixo:

ALTER TABLE nome_tabela TYPE=MyISAM

MyISAM

A partir da versão 3.23, toda tabela criada sem indicação explícita de tipo é definida como MyISAM. Esse tipo foi implementado a partir do código da tabela ISAM, introduzindo diversas melhorias. Entre as principais, podemos citar:

• Os arquivos de tabela são transportáveis entre diferentes sistemas operacionais;
• Campos BLOB e TEXT podem ser indexados;
• Os arquivos de índice e de dados podem ser armazenados em dispositivos diferentes (causando aumento de performance);
• Os algoritmos de distribuição dos dados e gerenciamento de índices foram melhorados.

Uma tabela MyISAM é armazenada em três arquivos:

1. .MYD – Arquivo de dados;
2. .MYI – Arquivo de índice;
3. .FRM – Estrutura da tabela (esse arquivo é utilizado em todos os tipos de tabela do MySQL).

As tabelas MyISAM se subdividem em três tipos:

• Estática ou tamanho fixo – Selecionado automaticamente quando a tabela não contém colunas VARCHAR, BLOB ou TEXT. Como as colunas possuem tamanho fixo, esse é o formato mais simples e que apresenta maior performance.
• Dinâmica – Selecionado automaticamente quando a tabela possui campos de tamanho variável (VARCHAR, BLOB ou TEXT).
• Comprimida – São tabelas read-only, que podem ocupar até 80% menos de espaço. Para aplicações que trabalham apenas com leitura de dados, tais como enciclopédias virtuais, bancos de dados em CD-ROM etc., a compressão de tabelas é a melhor opção.

Para comprimir ou descomprimir uma tabela é necessário utilizar os aplicativos myisampack e myisamchk, respectivamente. Nos dois casos é necessário passar o nome do arquivo de índice como parâmetro (a extensão pode ser omitida). Veja os exemplos:

Comprimindo

>myisampack nome_da_tabela.myi ou      
 >myisampack nome_da_tabela

Descomprimindo

>myisamchk -u nome_da_tabela.myi

Após a compressão é necessário recriar os índices. Isso pode ser feito com o comando a seguir:

>myisamchk –rq –a –-sort-index nome_da_tabela.myi

Onde:

• -rq: Recria o arquivo de índice;
• -a Recria as estatísticas do índice, que são utilizadas pelo otimizador de consultas do MySQL para acelerar a busca;
• --sort-index Ordena os blocos de dados do índice, tornando a consulta ainda mais veloz.

MERGE

A partir da versão 3.23.25 um conjunto de tabelas MyISAM pode ser unido sob uma única tabela virtual. Esse recurso, chamado de tabela MERGE, permite que uma aplicação manipule diversas tabelas como se fossem uma só, de forma totalmente transparente. Veja a figura 1.

O interessante é que os comandos SELECT, INSERT, UPDATE e DELETE podem ser utilizados normalmente com uma tabela MERGE, independente da quantidade de tabelas unidas. Veja como cada comando se comporta:

• SELECT – Os registros podem ser buscados de diferentes fontes com pouco custo de performance, pois o MySQL não necessita de um índice exclusivo para a tabela MERGE - ele utiliza os próprios índices das tabelas originais.
• INSERT – Os dados são inseridos de acordo com o parâmetro INSERT_METHOD, especificado na criação da tabela MERGE. As opções são:
• LAST – os dados são inseridos na última tabela da junção (na figura 1, a última tabela é Log_fevereiro);
• FIRST – os dados são inseridos na primeira tabela da junção (na figura 1, a primeira tabela é Log_janeiro);
• NO – a tabela não receberá inserções.
• UPDATE/DELETE – A alteração/exclusão é executada sem restrições, inclusive em tabelas diferentes ao mesmo tempo – nesse caso, o MySQL utiliza os índices das tabelas originais para buscar os registros.

O uso prático mais comum desse recurso é permitir que tabelas grandes (como tabelas de histórico) sejam ‘quebradas’ em tabelas menores sem criar alterações na aplicação. Dividir uma tabela pode aumentar a performance, principalmente se as partes menores forem distribuídas em HDs diferentes ou armazenadas em um dispositivo RAID. Veja o gráfico da figura 2.

Contudo, tabelas MERGE possuem algumas limitações:

1. As tabelas unidas devem ter exatamente a mesma estrutura;
2. A aplicação perde portabilidade, pois esse recurso é específico do MySQL;
3. A tabela MERGE não tem a maturidade de desenvolvimento dos outros formatos - a lista de inconsistências e comportamentos inesperados ainda é grande.

Veja um exemplo de criação de uma tabela MERGE:

CREATE TABLE MERGE_log  
 (
 ID NOT NULL,
 data DATE, 
 mensagem VARCHAR(100),
 KEY(ID) )             
 TYPE=MERGE 
 UNION = (log_janeiro, log_fevereiro,log_marco) 
 INSERT_METHOD=LAST;

HEAP

São tabelas que permanecem em memória. Aceitam comandos de inserção e alteração, mas seus dados não se tornam persistentes (apenas a estrutura da tabela é criada fisicamente em disco).

A implementação de tabelas temporárias é a aplicação mais comum desse recurso. Após o uso é recomendável remover a tabela da memória com o comando DROP TABLE.

Veja alguns exemplos de criação de tabelas HEAP:

CREATE TABLE temp_log  (
 ID INTEGER NOT NULL,
 data DATE, 
 mensagem VARCHAR(100))             
 TYPE=HEAP;

O exemplo a seguir cria e inicializa a tabela HEAP com dados da tabela NotasTB:

CREATE TABLE temp_notas
 TYPE=HEAP 
 SELECT nome,SUM(nota) AS nota 
 FROM NotasTB 
 group by nome;

InnoDB

As tabelas InnoDB rodam sob um engine, desenvolvido pela Innobase, que acrescenta ao MySQL o suporte a transações. Esse engine é uma camada adicional ao kernel do MySQL, possuindo recursos e algoritmos de manipulação dos dados proprietários.

Um exemplo de criação de uma tabela InnoDB e do uso de transações está disponível na listagem 1.

CREATE TABLE usuarioTB  (
 Login varchar(20),
 Senha varchar(20) )             
 TYPE=InnoDB;
  
  
 START TRANSACTION;
  
 INSERT INTO usuarioTB (login,senha) VALUES  ('eduardo','123456');
 UPDATE EstatisticaTB SET qtdeUsuarios = qtdeUsuarios +1; 
  
 COMMIT;

Outra característica dessas tabelas é o suporte a constraints de chave estrangeira. Para utilizar esse recurso algumas regras devem ser seguidas:

1. Ambas as tabelas devem ser do tipo InnoDB;
2. O campo que faz parte da chave estrangeira deve ser o primeiro de algum índice;
3. O campo referenciado na tabela pai deve ser o primeiro de algum índice;
4. Os campos devem ter o mesmo tipo de dados. A listagem 2 mostra a criação de uma constraint.

CREATE TABLE ClienteTB
 (idCliente INT NOT NULL, 
 PRIMARY KEY (idCliente)) 
 TYPE=INNODB;
  
 CREATE TABLE DependenteTB
 (cdo_dep INT, 
 idTitular INT, 
 INDEX Dep_index(idTitular),
              FOREIGN KEY (idTitular) 
 REFERENCES ClienteTB(idCliente)
 ON UPDATE CASCADE 
 ON DELETE RESTRICT
 ) TYPE=INNODB;

Algumas observações sobre as tabelas InnoDB:

• O tipo InnoDB já vem habilitado normalmente para uso nas opções Standard e Max do MySQL a partir da versão 4.0;
• Para converter uma tabela do tipo MyISAM para InnoDB basta usar o comando ALTER TABLE nome_tabela TYPE=InnoDB (o inverso pode ser feito com a opção TYPE=MyISAM);
• Enquanto no tipo MyISAM as tabelas são armazenadas em arquivos distintos, no InnoDB todas as tabelas e índices são incluídos no arquivo ibdata (os logs de transações são armazenados em arquivos iniciados por ib_logfile);
• Tabelas MyISAM podem ser utilizadas com tabelas InnoDB em uma transação. Nesse caso, as operações realizadas sobre a tabela não-transacional são automaticamente contabilizadas e ignoram o efeito do comando ROLLBACK;
• Para obter o máximo de performance com tabelas InnoDB é necessário configurar o arquivo my.cnf (ou my.ini no Windows) de acordo com as características de hardware do servidor onde o MySQL está rodando. Os detalhes dessa configuração podem ser obtidos no manual do InnoDB.

Berkeley DB

Desenvolvido pela Sleepycat, o Berkeley DB (BDB) é muito mais do que um tipo de tabela do MySQL; ele é um banco de dados open source completo e independente, que oferece todos os recursos de um ambiente transacional.

O produto é muito conhecido no mercado de embedded databases, onde um banco de dados deve rodar ‘dentro’ de um dispositivo de hardware. Nesses ambientes o banco deve ser o mais leve possível e atender aos requisitos de disponibilidade 24/7 e ausência de falhas. Entre os concorrentes do Berkeley DB, podemos citar: Velocis, Empress, Pervasive.SQL, Polyhedra e Solid FlowEngine.

O Berkeley DB foi inserido no kernel do MySQL, como um engine de tabela, a partir da versão 3.23.34. É utilizado como uma alternativa ao InnoDB pois oferece suporte a transações e crash recovery.

No entanto, a versão do Berkeley DB para o MySQL perde em recursos para o engine InnoDB. As tabelas BDB não oferecem, por exemplo, suporte a constraints de chave estrangeira.

Algumas observações sobre as tabelas BDB:

• As tabelas InnoDB e Berkeley DB podem ser misturadas numa mesma transação;
• No MySQL Max, a partir da versão 4, o suporte a BDB é automático e não necessita de nenhuma configuração adicional;
• Para obter o máximo de performance com tabelas BDB é recomendável armazenar os arquivos de log de transação em um dispositivo diferente dos arquivos de dados. Para isso, atualize a variável bdb_logdir no arquivo my.cnf/my.ini (o diretório dos arquivos de dados pode ser configurado na variável bdb_home).

O exemplo a seguir cria uma tabela do tipo BDB:

CREATE TABLE FuncionarioTB
 (cod_funcionario INT NOT NULL, 
  cod_empresa INT NOT NULL,
 salario DOUBLE,
 data_cadastro DATE
 PRIMARY KEY (cod_funcionario)) 
 TYPE=BDB;

Conclusão

O MySQL provavelmente é o único banco de dados com o conceito de table handler. A estratégia é interessante: o servidor passou a oferecer suporte a transações e controle de constraints sem abrir mão do seu modo de acesso ‘ultra-rápido’, proveniente das tabelas ISAM e MyISAM.

Maiores detalhes sobre cada tipo de tabela estão disponíveis na documentação do produto. Até a próxima!

BOX: Propriedades ACID

Um ambiente transacional robusto deve oferecer quatro propriedades:

• Atomicidade: Significa que as operações sob uma transação são indivisíveis – se uma falhar, todas devem ser ignoradas.
• Consistência: Determina que uma transação, mesmo que não seja completada, não pode violar as regras de integridade do banco de dados.
• Isolação: Uma transação não pode interferir nem enxergar os dados de outra. Isso garante uma visão consistente do banco de dados a todo momento.
• Durabilidade: Após uma transação ser efetivada ela deve ficar permanente, mesmo que o computador venha a ser desligado.