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.

Nota: Mesmo num banco de dados contendo somente tabelas transacionais o MySQL continua apresentando performance bem superior a diversos outros servidores.

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.

Nota: Para mudar o tipo padr�o de tabela no MySQL, modifique a linha default-table-type no arquivo mysql.cnf (ou mysql.ini no Windows).

Uma tabela MyISAM � armazenada em tr�s arquivos:

.MYD � Arquivo de dados;
.MYI � Arquivo de �ndice;
.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.

Figura 1. Conjunto de tabelas MyISAM

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.

Figura 2. Distribuiç�o em Hds diferentes ou armazenadas em um dispositivo RAID

Contudo, tabelas MERGE possuem algumas limitaç�es:

As tabelas unidas devem ter exatamente a mesma estrutura;
A aplicaç�o perde portabilidade, pois esse recurso � espec�fico do MySQL;
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;

Nota: Apesar de ser virtual uma tabela MERGE precisa ser criada apenas uma vez, j� que o MySQL gera arquivos (.FRM e .MRG) contendo sua estrutura e o nome das tabelas de origem.

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;

Listagem 1. Exemplo de criaç�o de uma tabela InnoDB

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

Ambas as tabelas devem ser do tipo InnoDB;
O campo que faz parte da chave estrangeira deve ser o primeiro de algum �ndice;
O campo referenciado na tabela pai deve ser o primeiro de algum �ndice;
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;

Listagem 2. Criaç�o de uma constraint

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.

Figura 3. Aplicativo SQLYog exibindo os tipos de tabela do MySQL