[MySQL] Mecanismos de Armazenamento e Tipos de Tabelas

Bom pessoal neste post vamos abordar a padronização dos tipos de tabelas (table types) suportadas pelo MySQL e os mecanismos de armazenamento (Storage Engine) por ele suportado. O MySQL possui uma característica um pouco diferente dos outros SGBD’s, uma vez que no MySQL é possível escolher o tipo da tabela no momento da criação da mesma. O formato de armazenamento dos dados, bem como alguns recursos do banco de dados são dependentes do tipo de tabela escolhido.

De acordo com a documentação oficial do MySQL 5.5, vou falar um pouco sobre os tipos de mecanismos de armazenamento por ele suportado, estamos abordando este assunto para termos uma melhor compreensão na criação de nossas tabelas no MySQL, pois precisamos entender qual mecanismo de armazenamento melhor atende nosso projeto de banco de dados, quando efetuarmos a normalização do deste banco de dados.

Veja abaixo os Mecanismos de Armazenamento suportados pelo MySQL:

InnoDB: é um mecanismo de armazenamento transacional seguro (compatível com ACID) que possuí rollback e commit de transações, como também recuperação de desastres para proteger os dados do usuário. O InnoDB efetua bloqueio em nível de linha (sem escalação de bloqueios de granularidades maiores), bem no estilo Oracle non-locking  com leitura consistente, controle de concorrência multiusuário e desempenho satisfatório. O InnoDB armazena dados do usuário em índices agrupados para reduzir I/O para consultas comuns com base em chaves primárias. Para manter a integridade referencial dos dados, o InnoDB também suporta chaves estrangeiras (Foreign Key) mantendo as restrições referenciadas nos dados armazenados. O InnoBD é o motor de armazenamento padrão do MySQL a partir da versão “5.5.5” descrita na documentação oficial.

MyISAM: é um dos mecanismos de armazenamento do MySQL mais utilizado na Web e para armazenamento de dados históricos (Data Warehousing). O MyISAM é suportado em todas as versões do MySQL sendo ele o motor de armazenamento padrão do MySQL anterior a versão “5.5.5” descrita na documentação oficial.

Merge: é um mecanismo de armazenamento que permite que um DBA MySQL ou Desenvolvedor possa agrupar logicamente uma série de tabelas MyISAM idênticas e referenciá-las como um objeto. Sendo isso bom bancos de dados com grandes volumes de dados.

Memory: é um mecanismo de armazenamento que armazena todos os dados em memória RAM com acesso extremamente rápido em ambientes que exigem pesquisas rápidas de referência a outros dados. este motor era conhecido nas versões anteriores do MySQL como HEAP.

Archive: é um mecanismo de armazenamento que fornece a solução ideal para armazenar e recuperar grandes quantidades de dados historicamente referenciados, arquivando informações de auditoria e de segurança.

Federated: é um mecanismo de armazenamento que oferece a capacidade de vincular servidores MySQL separados para criar um banco de dados lógico em vários servidores físicos. Sendo uma opção boa para implementar em ambientes distribuídos ou data marts.

CSV: é um mecanismo de armazenamento de dados em arquivos texto utilizando um separador comum para formatar os valores nele armazenado. Você pode utilizar o motor CSV facilmente para efetuar troca de dados entre aplicações que exportam e importam dados no formato CSV.

É importante lembrar que você não está restrito a utilizar apenas um mecanismo de armazenamento, muito pelo contrário, você terá a liberdade de utilizar um mecanismo por tabela, esquema ou banco de dados. Veja a imagem abaixo com um exemplo:

Como vocês podem ter percebido, o tipo de tabela utilizada no MySQL está inteiramente ligada nos mecanismos de armazenamento suportado pelo MySQL, no exemplo da imagem acima. Dentro do banco de dados teste, foram criar duas tabelas uma utilizando a Engine InnoDB e outra utilizando a Engine MyISAM.

Comando tabela teste1:

Comando tabela teste2:

Como complemento deste post, vou utilizar um texto que encontrei no Blog do Alexandre M Almeida no post “O que é um Storage Engine?“.
—————————————————————————————————————————

Quais são as principais características, recursos ou funcionalidades inerentes aos storage engines/motores:

a) Capacidade Transacional: É a capacidade da tabela aceitar múltiplos acessos (de múltiplos usuários/aplicações), com colisão e travamento mínimos, sem que um usuário interfira com a operação do outro. É poder executar comandos em blocos (transação), ao invés de executar um comando SQL por vez. É estar de acordo com o modelo ACID (Atômico, Consistente, Isolado e Durável).

b) Meio de armazenamento: Isto é fantástico. Nos outros bancos de dados, toda tabela grava e lê os dados de uma única forma padrão. No MySQL/MariaDB, dependendo do motor escolhido, pode-se gravar a tabela 100% em memória, isto mesmo, nada no disco. Podem-se gravar dados em uma TABLESPACE, como no Oracle, por exemplo. Pode-se se utilizar uma tecnologia dos anos 70, extremante rápida, conhecida como ISAM, para gravar dados e recuperá-los (leitura) de forma muito rápida. Outro tipo de motor pode gravar os dados de forma compactada, como em um arquivo ZIP, economizando muito espaço em disco. Ainda, pode-se gravar em formato CSV que facilita muito a integração com equipamentos de rede e telefonia, por exemplo. E mais, ao invés de ler e gravar os dados no servidor onde o MySQL/MariaDB está instalado, pode-se espalhar os dados por vários computadores para se criar um cluster de alta disponibilidade e alta performance.

c) Índices: Dependendo do motor, temos índices do tipo B-TREE, B+TREE, RTREE (spatial), ou FULL TEXT (que indexa palavras ao invés da coluna ou campo inteiro, e permite buscas como fazemos no GOOGLE, digitando palavras fora de ordem).

d) Integridade Referencial: Estou falando de FK (foreign key). Há motores que usam, e, motores que não usam. Como todos os recursos que estamos discutindo, dependendo da aplicação ou finalidade da tabela isto não é necessário… e é uma funcionalidade que pesa para o banco de dados. Às vezes, não ter este recurso pode ser uma vantagem em termos de velocidade.

e) Tipo de travamento: É a capacidade de poder travar um único registro (linha), vários registros ou a tabela inteira. Cada motor tem um ou mais tipo de travamentos à disposição da tabela.

f) BOL – Backup On-Line: Que ótimo fazer backup online, com todo mundo trabalhando, sem precisar parar o banco. Lindo? Claro, mas tem um custo alto. Se a sua aplicação não roda H24 (24 horas por dia), significa que você tem janela de backup. Não seria mais lindo se você pudesse abrir mão deste recurso para ter mais agilidade nas leituras e escritas? Bem vindo ao mundo do storage engine.

g) Auto Recovery: Há motores que caso haja uma corrupção de índice, por exemplo, você se verá obrigado a indisponibilizar o banco para rodar um comandinho básico de REPAIR TABLE. Há outros, no entanto, que no máximo você será avisado, através do log de erro, que o MySQL/MariaDB server encontrou uma inconsistência e, já consertou.

Em resumo, storage engine, é muito mais do que, simplesmente, tipo de tabela. É uma característica única e exclusiva do MySQL/MariaDB que permite escolher um conjunto de recursos pré-definidos que melhor atenda aos requisitos e às necessidades de sua tabela, respeitando, o hardware disponível.

—————————————————————————————————————————
Para entender melhor sobre o que é o modelo ACID, veja o post abaixo:
http://certificacaobd.com.br/2012/05/09/transacao/

Reforço a todos que estão lendo este post que para que possa ter uma compreensão maior leia as fontes originais utilizadas. segue abaixo links:
http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html
http://www.devshed.com/c/a/MySQL/MySQL-Data-and-Table-Types-62908/
http://www.alexandremalmeida.com.br/2011/06/17/o-que-e-storage-engine-parte-1/

Pessoal a idéia deste post era de trazer o conhecimento das possibilidades de armazenamento que o MySQL possui assim se adequando as necessidades de nossas implementações e projetos com o MySQL. Qualquer dúvida comente no post sobre o assunto terei o maior prazer de ajudá-los. Que a Graça e a Paz estejam com Vocês.