Engines MySQL – InnoDB

Ola pessoal,

Conforme divulgado no post anterior, vamos continuar a explicação sobre os engines do MySQL, falando hoje sobre o InnoDB.

O InnoDB foi implementado como Engine Default no MySQL a partir da versão 5.5, deixando de ser um plugin como nas versões anteriores.

Algumas das características cruciais:

– Prove suporte a transações baseadas em ACID.
– Prove suporte a chaves estrangeiras.
– Nível de bloqueio por campo e não por tabela.

Sim jovens, o InnoDB prove suporte a transações seguras, com a conformidade do ACID provendo todas as opções de commit, rollback e recuperação de falhas para proteger os dados envolvidos. Não sabe o que é o ACID? Recomendo a leitura deste artigo, pois é muito importante.

O seu nível de bloqueio é a partir do campo, logo se você esta efetuando uma operação de insert no campo da tabela, apenas aquele campo ficara em lock, enquanto a tabela estará disponível para novas instruções.

Para uma tabela com diversas instruções, esse recurso é uma mão na roda em questão de desempenho e concorrência. Seguem outras características interessantes sobre o InnoDB:

– Armazenamento: Limite de 64TB per table
– Suporte a Transações e Chave estrangeira;
– Suporte a MVCC;
– Bloqueio de linha ao invés de tabelas
– Backup e Recuperação: Sim
– Recuperação automática de falhas: Sim

Para utilizar uma tabela com o InnoDB, basta definir o engine no final da criação da sua tabela, exemplo:

Caso deseje alterar uma tabela existente, você pode executar:

É possível definir o InnoDB como padrão na criação de novas tabelas , incluindo a variável no arquivo de configuração do MySQL (my.cnf):

*Processo necessário em versões anteriores a 5.5.

Diferente do MyISAM o InnoDB trabalha diretamente na memória, onde utiliza o pool de buffers para manter os dados em cache, e os índices na memória. Ou seja, grandes operações sendo feitas diretamente na memória, traz um ganho muito maior ao invés de ter que ir sempre no disco.

Cada conjunto de dados na memória tem um hash único, logo se você alterar algum campo e realizar uma nova consulta neste, o MySQL irá buscar o novo dado no disco e mante-lo novamente na memória, com outro hash, evitando assim qualquer inconsistência.

Você pode definir o valor do buffer através da variável innodb_buffer_pool_size no arquivo de configuração do MySQL. Em um servidor dedicado somente ao MySQL, é recomendado reservar de 50% a 80% da memória total para o InnoDB, mas tome cuidado, caso o seu servidor opere outros serviços além do MySQL, não é interessante definir um valor tão alto.

Motivo? Simples, quando o MySQL for iniciado, ele automaticamente irá reservar o buffer na memória, mesmo estando em uso ou não. Caso alguma operação do S.O precise de memória e acabe tirando do que esta reservado ao buffer, o mysql será encerrado e só vai voltar ao normal quando conseguir reservar o valor x definido na memória.

Recomendo a leitura no artigo sobre Como o MySQL usa a memoria interna, caso queira outras informações sobre a operação do MySQL na Memória do servidor.

Quando você cria uma tabela InnoDB, por default ele cria um arquivo .frm com a estrutura da tabela no seu datadir, e mantem todos os dados da tabela e dos índices em um tablespace compartilhado no servidor.

Ou seja, todas as tabelas InnoDB utilizam um mesmo arquivo de controle de dados e de índices, diferente do MyISAM neste caso.

A variável innodb_data_file_path detalha o arquivo tablespace compartilhado e a sua configuração:

Com as informações acima, o arquivo ibdata1 é o tablespace (default) com o tamanho inicial de 10M, sem algum limite de crescimento. É possível estabelecer um limite ao arquivo retirando o autoextend, porém caso não seja mais possível armazenar dados, será retornado o erro de: The Table is full.

Este cenário era o padrão até a versão 5.5 do MySQL, onde o funcionamento do InnoDB era pleno, porém você não consegue ter um controle de quanto este arquivo pode crescer.

Então podemos trabalhar com a variável innodb_file_per_table. Com este parâmetro ativado, cada tabela InnoDB terá um tablespace único, armazenada no datadir do servidor correspondente a cada base, facilitando a administração e o crescimento.

Exemplo pratico:

Em uma instância sem o innodb_file_per_table ativado, vemos que ao criar uma tabela, no datadir referente a base foi gerado apenas o arquivo .frm:

post1

Ativando o innodb_file_per_table, vemos que as próximas tabelas são geradas com o .frm e o .ibd (tablespace único para aquela tabela).

post2

Recomendo que defina qual padrão vai utilizar antes de implementar o banco em operação, ou seja, com tabelas apenas com o tablespace individual ou não.

Conforme falamos no inicio, o InnoDB trabalha também com um auto recovery. Este recurso tem base na posição do log do InnoDB, onde caso o serviço seja encerrado ou sofra algum problema sem a gravação da posição no log, o mysql inicia o famoso crash recovery, verificando a integridade de todos os dados a cada tablespace.

Mas tragedias podem acontecer jovem, e esteja preparada para elas. Tenha uma rotina de backup diária e se preocupe com a geração dos bin logs, pois é difícil uma tabela InnoDB corromper, mas quando corrompe, se prepare para uma GRANDE DOR DE CABEÇA caso não tenha nenhum backup consistente.

O InnoDB tem uma alternativa caso a tabela seja corrompida, o force recovery, porém por se tratar de um assunto mais complexo, irei trata-lo em outro post.

A recomendação que não é novidade independente do Engine utilizado é, tenha o backup de todas as suas bases, e planeje uma rotina de testar este backup, afinal não é legal saber que o backup é inválido quando se precisa dele 🙂

Fontes utilizadas para este post:

Excelente leitura!! -> http://imasters.com.br/artigo/8065/banco-de-dados/mysql-innodb-introducao-e-principais-caracteristicas/

http://pt.wikipedia.org/wiki/InnoDB


É isso ai pessoal.

Dúvidas, criticas ou sugestões? Fiquem a vontade, todo retorno é construtivo 🙂

Jose Wilson

2 Replies to “Engines MySQL – InnoDB”

  1. Opa José, tudo bem? Primeiramente parabéns pelo artigo e obrigado pelas informações.

    Tenho um problema e queria saber se consegue me ajudar.

    Tenho um banco de e dados MySQL na versão 5.6.27, no qual atualmente todas as tabelas existentes, foram criadas com a Engine MyISAM. Necessito criar novas tabelas, porém todas com Engine InnoDB, porém o banco não possui suporte para essa engine. Já reinstalei o banco para ver se habilitada, porém sem sucesso. O banco fica instalado no Windows. (Tenho pouco conhecimento de banco de dados, o básico). Como posso fazer para que esse banco aceite novas tabelas com engine InnoDB? Você poderia me ajudar? Agradeço desde já.

    1. Ola Maurício, tudo bem e contigo? O banco deve ter este engine habilitado. Acesse o mysql e digite:

      show engines;

      Ele deve aparecer que o InnoDB esta ativado. Para criar as tabelas com este engine, no final da instrução SQL coloque:

      CREATE TABLE TESTE (ID INT) ENGINE = INNODB;

      Para alterar uma tabela MyISAM para INNODB, é quase a mesma coisa …

      ALTER TABLE TESTE ENGINE = INNODB;

      Você pode deixar configurado que qualquer tabela criada seja com o InnoDB, basta seguir esta doc:

      https://dev.mysql.com/doc/refman/5.7/en/storage-engine-setting.html

      Abraços

Deixe uma resposta