Banco de DadosMySQL

MySQL: Mecanismos de Armazenamento - MyISAM, InnoDB e Memory (Parte 2)

·15 min de leitura

Este é o segundo artigo da série sobre administração de MySQL. Aqui vamos explorar os diferentes mecanismos de armazenamento disponíveis e como escolher o mais adequado para cada situação.

O que são Mecanismos de Armazenamento?

Vamos falar um pouquinho sobre mecanismos de armazenamento. Esses mecanismos de armazenamento que existem no MySQL, são um dos recursos mais exclusivos do banco de dados. Ele tem a ver com a forma com que o dado é guardado dentro das tabelas e o MySQL disponibiliza cerca de 20 diferentes tipos de mecanismos de armazenamento.

Então, como eu falei, ele é um mecanismo que gerencia a forma com que o dado é gravado em tabelas e a gente pode ter num mesmo banco de dados, diferentes tipos de mecanismos diferentes, como ele é aplicado a nível de tabela.

As vezes a gente confunde muito o mecanismo de armazenamento com um tipo de tabela. A gente pode chamar assim mesmo, quando a gente se refere a um mecanismo de armazenamento, na verdade, nós estamos falando de um tipo de tabela diferente do meu banco de dados.

Como Configurar Mecanismos de Armazenamento

Nas instruções de criação de tabela e de alteração de tabela, respectivamente os CREATE TABLE e os ALTER TABLE, nós temos uma opção chamada ENGINE, onde eu posso definir ou alterar o mecanismo de armazenamento que aquela tabela respectiva vai ter.

Uma outra característica interessante é que o mecanismo de armazenamento da tabela, ela está... não está associada com a arquitetura interna do MySQL, separando, justamente a forma com que eu tenho o core do banco de dados, com a forma com que eu vou armazenar a informação dentro da tabela.

Apesar de a gente ter esses 20 diferentes tipos de mecanismos de armazenamento, normalmente a gente trabalha com três principais: o MyISAM, o InnoDB e o MEMORY.

MyISAM - O Mecanismo Tradicional

Características do MyISAM

O MyISAM, ele é, na verdade, o mecanismo padrão do MySQL, inclusive, as tabelas internas do MySQL são armazenadas usando MyISAM. Ele é um mecanismo bem confiável e ele herdou do mecanismo original, que foi implementado nas primeiras versões do MySQL, que era o mecanismo chamado de ISAM.

E a partir da versão, creio que 3.2 do MySQL, eles substituíram o padrão original ISAM, para o MyISAM.

Funcionalidades e Limitações

Característica principal do mecanismo MyISAM: Ele não é um mecanismo puramente transacional, ele não implementa mecanismos de bloqueio dos registros dentro das tabelas.

O tipo de bloqueio que o MyISAM faz quando uma tabela está sendo atualizada, é um lock na tabela como um todo. Isso permite com que a tabela seja muito mais rápida, se eu quiser, por exemplo, só usá-la para efetuar somente leituras, mas aí, você tem um problema.

Se você tiver muita gravação simultânea, por diferentes usuários, diferentes sessões dentro do banco, como esse controle não é tudo específico, a gente pode ter problemas usando tabelas MyISAM, por isso nós temos que tomar cuidado.

Quando Usar MyISAM

Então, algumas características específicas do MyISAM, para a gente decidir que tipo de forma de tabela a gente vai utilizar:

  • Tabelas com poucas transações: Se eu tiver uma tabela que não vai ter muitas transações, eu posso usar MyISAM.
  • Não suporta chaves estrangeiras: O MyISAM tem uma característica que a chave estrangeira não suporta.
  • Suporte a FULLTEXT: Suporta o tipo FULLTEXT, que é um tipo de dado específico da tabela.
  • Tipos de índice: Quando a gente cria um hash de dados ou um hash de índice, a gente nunca pode se referenciar a ele através do nome.
  • Implementa dois tipos de índice: HASH e BTREE, a gente vai falar um pouquinho sobre eles mais a frente.

Configurações Específicas do MyISAM

Como eu já falei, o MyISAM, ele implementa bloqueio a nível de tabela, isso faz com que a atividade de leitura seja muito rápida, quando você usa o MyISAM.

Então ela é muito específica para bancos de dados, que nós chamamos de Data Warehouse, ou seja, bancos de dados gerenciais de consulta e internamente, os dados que são armazenados dentro das tabelas do MyISAM, já são automaticamente armazenados de forma compacta, melhorando o tamanho do banco de dados, quando eu tenho muita informação.

Variáveis de Configuração

Lembra que a gente falou sobre variáveis de ambiente? Existe algumas variáveis de ambiente que são diretamente ligadas ao MyISAM:

key_buffer_size: Determina o tamanho de cache que a gente vai usar para armazenar os índices do MyISAM. Dependendo do sistema operacional, se eu estiver usando 32 bits ou 64 bits, esse padrão pode ir desde 8MB, até 4GB.

concurrent_insert: Determina o comportamento das inserções concorrentes dentro de uma tabela MyISAM. Existe uma variável chamada: intervalo de dados, que é uma espera que MySQL faz entre a instrução de um dado e de outro dado.

  • Se essa variável concurrent_insert for igual a 1, você consegue fazer inserções simultâneas, sem intervalo de dados.
  • Se a configuração for igual a 0, a gente desativa as inserções simultâneas, ou seja, uma instrução sempre vai esperar a tabela ser liberada para funcionar.
  • Quando tiver a configuração numero 2, eu permito a instrução simultâneas com um intervalo de dados ativado.

delay_key_write: A gente usa essa variável para criar um atraso entre a atualização dos índices e o momento que a tabela é fechada. O padrão do MyISAM é essa variável delay_key_write, com o valor igual a 1.

max_write_lock_count: Determina quantas gravações em uma tabela vão ter precedências às leituras, ou seja, quando tiver gravações e leituras ao mesmo tempo, qual vai ser a prioridade da inclusão de dados na tabela, em relação às leituras.

preload_buffer_size: Determina o tamanho do buffer que vai ser usado no pré carregamento do índice de caches de chaves da tabela. O padrão dessa variável, normalmente, é KB.

Utilitários do MyISAM

O que nós estamos vendo aqui, são três utilitários que existem dentro do MySQL, para a gente poder manipular tabelas do tipo MyISAM:

myisamchk: Ele é usado para a gente poder analisar, otimizar e reparar tabelas MyISAM. Pode ser que as tabelas estejam mal construídas, algum problema no seu armazenamento interno. Então o myisamchk, reconstrói essas tabelas.

myisampack: Ele é usado para a gente poder criar tabelas MyISAM compactadas, que vão ser só usadas para leitura, nada mais. São tabelas que a gente, durante o uso do aplicativo, a gente cria elas, coloca informação e elas não vão poder sofrer nenhum tipo de insert.

myisam_ftdump: Que é usado para a gente poder exibir informações sobre os campos do tipo texto, que eu tenho dentro do MyISAM, ele fornece uma informação mais completa sobre esses campos.

InnoDB - O Mecanismo Transacional

Características do InnoDB

Agora vamos falar do InnoDB. O InnoDB é um mecanismo de armazenamento usado quando eu vou realmente ter um banco de dados transacional.

Quando eu falo banco de dados transacional, eu estou imaginando um banco de dados onde eu tenho uma aplicação, onde eu tenho dezenas, centenas ou milhares de usuários fazendo inclusões, alterações, exclusões e consulta de dados naquele banco ao mesmo tempo.

É uma forma diferente, quando eu falo de bancos de dados gerenciais, onde durante um período, eu faço uma carga grande desse banco e depois os usuários apenas consultam as informações. Nesse caso, o MyISAM, ele é mais direcionado.

Já para bancos de dados com várias transações, a gente aconselha usar o mecanismo de armazenamento InnoDB e foi o InnoDB que trouxe para o MySQL o suporte a transações relacionais, até versões anteriores, quando não utilizavam esse tipo de mecanismo, o MySQL não era full transacional.

Funcionalidades do InnoDB

Características do mecanismo de armazenamento InnoDB:

  • Suporte completo ao banco de dados transacional
  • Bloqueio a nível de linha: O bloqueio da tabela durante uma atualização, ele é feito a nível de linha. Ou seja, quando eu atualizo uma informação, aquela linha está bloqueada, mas a tabela toda está liberada para sofrer outras alterações.
  • Suporte completo a chaves estrangeiras
  • Em termos de índice: O InnoDB só utiliza índice do tipo BTREE
  • Backup sem bloqueio: E eu consigo, através de bancos InnoDB, fazer um backup do banco sem bloqueá-lo, sem precisar tirar ele do ar para fazer isso.

Variáveis de Configuração do InnoDB

Nós temos algumas variáveis de ambiente, as três primeiras, elas estão relacionadas com as tabelas:

innodb_data_file_path: Determina o caminho dentro do sistema operacional, onde as informações serão armazenadas e o tamanho desses arquivos máximos. O InnoDB, ele armazena as informações através de um arquivo que vai crescendo e depois quando ele acaba, ele vai criando um outro arquivo com sufixo 1, 2, 3 e assim por diante.

innodb_data_home_dir: Como o próprio nome diz, ele é feito para dizer qual é o caminho comum de diretório de todos os arquivos InnoDB. Se eu especificar esse cara, ele vai gravar tudo dentro desse diretório, diferente do default, o padrão, ele vai gravar tudo dentro de um diretório chamado MySQL Data, que é o padrão do armazenamento de dados de um banco de dados MySQL.

innodb_file_per_table: A gente pode especificar cada tabela de armazenamento InnoDB, os arquivos que armazenam as informações, eles tem uma extensão ".ibd". E aí, quando a gente usa esse parâmetro innodb_file_per_table, a gente consegue separar o armazenamento dos dados, com os índices.

Variáveis de Performance

Já as três últimas variáveis que estão aqui nesse slide, ele diz respeito a variáveis que estão relacionadas com performance:

innodb_buffer_pool_size: Ela determina o tamanho de buffer que o mecanismo de armazenamento InnoDB, vai estar usando para armazenar dados e índices em cache. Quando a gente utiliza cache, a gente está falando de coisas que ficam em memória que melhoram a performance.

innodb_flush_log_at_trx_commit: Nome grande, ela vai configurar a frequência com que o buffer de log é liberado para o disco. Na medida em que a gente vai usando o banco, esse buffer de log vai crescendo e de tempos em tempos, ele é descarregado para o disco rígido.

innodb_log_file_size: Ele vai determinar o tamanho em bytes que cada um dos arquivos de log (InnoDB) vão ter. O padrão dessa variável, quando você não menciona nada, é ter um log de no máximo 5MB.

Memory - Tabelas em Memória

Características do Memory

Vamos agora falar de outro mecanismo de armazenamento, que é o Memory e como o próprio nome diz, o Memory, ele é um mecanismo de armazenamento que cria tabelas apenas na memória, quando eu falo memória, eu estou falando apenas na memória RAM.

E aí, claro, se a informação... se a tabela está lá na memória RAM, isso significa que o acesso a ela é super rápido, porém tem uma desvantagem, essa informação, ela não fica armazenada no disco. Os dados, eles precisam ser sempre reinicializados quando o servidor é inicializado.

Ou seja, eu inicializei um servidor, as tabelas de Memory estão vazias, se eu for criar tabelas e colocar dados em membro, eles vão ficar lá até o servidor ser reinicializado novamente. Se isso acontecer, se houver uma reinicialização, os dados são perdidos.

Funcionalidades e Limitações

Características dos bancos em membro, das tabelas em membro:

  • Não tem chave estrangeira
  • Leitura e escrita rápidas: É claro, o ato de ler e escrever dados nas tabelas membro são muito rápidos, claro, porque a informação está em memória
  • Bloqueio similar ao MyISAM: O bloqueio é muito parecido com o MyISAM, ou seja, quando eu vou bloquear algum registro, porque eu estou atualizando ele, eu bloqueio a tabela toda
  • Índices: Em termos de índices, o Memory, ele também utiliza os mecanismos de HASH e de BTREE, mas o padrão é o HASH

Características Específicas de Armazenamento

As tabelas de Memory, elas têm uma característica específica para armazenar o dado. Elas usam um formato que no MySQL, nós chamamos de formato de linha de comprimento fixo, então, por causa disso, eu não posso ter tipos de campos muito grandes, como por exemplo, tipos Blob ou tipos Texto, ou seja, campos que são muito... são campos que tem tamanho muito grande, que armazenam muitos caracteres.

Usando os Mecanismos de Armazenamento na Prática

Criação de Tabelas

Vamos ver na prática um pouquinho como é que funciona durante a manipulação de tabelas, os mecanismos de armazenamento. Então, eu vou criar aqui um script novo, eu entrei aqui no MySQL Workbench e eu vou usar esse banco aqui, o sakila, que é um banco de dados padrão que vem, quando a gente instala o MySQL.

Eu vou criar uma tabela: "CREATE TABLE", é o comando para criar tabela, "Default_Table", vai ser o nome da tabela. A gente quando viu o curso de manipulação de dados ou curso de consulta avançada e até o curso de introdução, a SQL, usando o MySQL, a gente já viu o comando CREATE TABLE.

Mas a gente não viu esse mecanismo de armazenamento, ou seja, especificar qual é o mecanismo de armazenamento que uma tabela vai usar. Estou criando a tabela, eu vou criar um campo ID que inteiro, "INTEGER" e um campo, nome, que eu vou colocar como "VARCHAR(100)".

Não estou especificando nada, não estou falando qual é o mecanismo de armazenamento que essa tabela vai usar, eu simplesmente vou rodar o comando.

Verificando o Mecanismo Padrão

Se eu vier aqui no meu banco sakila, botão direito do mouse dou um Refresh e vou ver aqui que eu tenho a minha default_table e aqui do lado, eu tenho um caminho que me dá a informação sobre essa tabela. Eu vejo os campos, os índices, as chaves, tudo mais.

Bem, eu vou clicar aqui no "i" e aí note que ele tem esse parâmetro aqui: Engine InnoDB, o que significa? Significa que se eu não falar nada, automaticamente o MySQL vai criar uma tabela do tipo InnoDB.

Ou seja, vai preparar a minha tabela para um banco de dados relacional, que vai suportar muitas transações, que vai fazer o controle da transação por linha, banco que está sendo preparado para ser usado numa aplicação que vai ter muitos usuários acessando ao mesmo tempo, incluindo, alterando, excluindo ou consultando informação.

Alterando o Mecanismo de Armazenamento

Mas eu posso, se quiser, alterar esse mecanismo da tabela, mesmo com a tabela criada a inclusive, mesmo com a tabela com dados. Se eu pegar esse comando aqui, vou colar aqui, só que... Não, não vou fazer isso, desculpe, vou alterar o mecanismo.

Então, eu uso o "ALTER TABLE", coloco aqui o nome da tabela e aí, o parâmetro é engine, se eu botar aqui "engine = MyISAM", vamos olhar aqui, eu coloquei aqui "Engine = MyISAM", essa tabela default_table que originalmente é InnoDB, se eu rodar esse comando, vier aqui e olhar a informação da tabela, note que ela agora ficou MyISAM.

Ou seja, agora é uma tabela que vai estar locando a tabela toda quando eu for alterar uma coisa nela, é uma tabela que fica mais rápido para leitura, mas que pode não se dar bem, quando a gente tiver muitas transações sobre ela, alterando ou incluindo coisas novas.

Especificando o Mecanismo na Criação

Mas durante a criação da tabela, eu posso estar especificando o mecanismo de armazenamento, então agora sim, eu vou copiar aqui o comando de cima, vou criar uma tabela dois e aqui, depois da criação da tabela, eu posso colocar o engine, por exemplo, memory.

Ou seja, eu estou forçando e especificando que durante a criação da tabela, essa tabela vai ser do tipo de memória, memory. Eu vou selecionar a linha, rodei, atualizo aqui o meu banco, as tabelas do meu banco, então eu tenho aqui o meu default_table 2, botão direito do mouse.

Não, na verdade, não, clicando no table 2, eu vou clicar no "i" de informação e eu tenho aqui o meu padrão memory. A gente viu e eu falei sobre o InnoDB, o MyISAM e o Memory, que são os mais utilizados.

Interface Gráfica para Criação

Mas por exemplo, se eu clicar aqui em tables, botão direito do mouse e usar a opção create tables, ou seja, eu vou ver a caixa de diálogo, de criação de uma tabela, para eu poder criar tabela, não necessariamente por comando.

Note que ao selecionar essa caixa de diálogo, a opção engine aqui em cima, ela já vem InnoDB selecionada, porque é o padrão, é o default do MySQL criar tabelas do tipo InnoDB, mas note, eu tenho uma gama de outras formas de... outros mecanismos de armazenamento que a gente pode estar utilizando.

A gente viu o InnoDB, o MyISAM e o Memory, eu dei ênfase a esses três tipos, desse treinamento porque são os mais usados, mas eu tenho aqui uma série de outros tipos de mecanismos, que tem propriedades específicas.

Conclusão

Os mecanismos de armazenamento do MySQL oferecem flexibilidade para diferentes cenários de uso. O InnoDB é ideal para aplicações transacionais com muitos usuários simultâneos, o MyISAM é excelente para Data Warehouses e consultas de leitura, enquanto o Memory oferece performance extrema para dados temporários.

A escolha do mecanismo correto depende do seu caso de uso específico, considerando fatores como volume de transações, necessidade de consistência, performance de leitura e persistência dos dados.

Na próxima parte desta série, exploraremos backup e recuperação de dados, incluindo o uso do mysqldump e diferentes estratégias de backup.

Referências