MySQL - Conversão de tabelas MYISAM para INNODB

Olá,

Diferente da engine INNODB que armazena em buffer de memória, tanto blocos de dados de tabelas, quanto blocos de dados de índices, a engine MYISAM armazena em buffer de memória apenas dados de blocos de índices. Com a evolução da engine INNODB ao longo do tempo, a mesma vem oferecendo muitos recursos das quais a engine MYISAM não é capaz de oferecer. Em relação aos níveis de locks, a engine MYISAM possui tratamento de lock apenas no nível de tabela:

• SELECTs travam UPDATEs, DELETEs mas não INSERTs

• INSERTs, UPDATEs, DELETEs travam todas as instruções SQL

A engine INNODB oferece tratamento de locks no nível de linhas de tabelas, além de prover recursos como Foreign Keys que ajudam o otimizador do banco de dados a encontrar melhores planos de execução para os SQLs que são executados. A engine INNODB é compatível com as propriedades ACID oferecendo recursos transacionais, além de ser mais seguro que a engine MYISAM no que se refere a corrupção de dados em caso de falhas. Em relação ao parâmetros de configuração, somente a engine INNODB oferece parâmetros de ajustes finos. Segue abaixo uma tabela comparativa.

 
No mais, irei selecionar todas as tabelas MYISAM do banco de dados bd01.

mysql> SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=InnoDB;')
    ->   FROM information_schema.tables
    ->  WHERE table_schema='bd01'
    ->    AND engine='MyISAM';
+--------------------------------------------------------------------------+
| CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=InnoDB;') |
+--------------------------------------------------------------------------+
| ALTER TABLE `bd01`.`tbl01` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl02` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl03` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl04` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl05` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl06` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl07` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl08` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl09` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl10` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl11` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl12` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl13` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl14` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl15` ENGINE=InnoDB;                                |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Como exemplo irei converter a tabela tbl01 para a engine INNODB conforme o comando a seguir.

mysql>  ALTER TABLE `bd01`.`tbl01` ENGINE=InnoDB;
Query OK, 61 rows affected (0.23 sec)
Records: 61  Duplicates: 0  Warnings: 0

Dependendo do tamanho da tabela a ser convertida, o processo pode demorar um pouco. Neste caso, é possível verificar a sessão que está realizando a conversão conforme demonstrado abaixo.

 

mysql> show full processlist;
+----+------+---------------------+------+---------+------+-------------------+----------------------------------+
| Id | User | Host                | db   | Command | Time | State             | Info                             |
+----+------+---------------------+------+---------+------+-------------------+----------------------------------+
|  5 | root | localhost           | bd01 | Sleep   |  804 |                   | NULL                             |
|  6 | root | localhost           | NULL | Query   |    0 | starting          | show full processlist            |
|  7 | root | 192.168.56.51:49970 | bd01 | Sleep   |  103 |                   | NULL                             |
|  8 | root | 192.168.56.51:49971 | NULL | Sleep   | 9838 |                   | NULL                             |
| 14 | root | localhost           | bd01 | Query   |   15 | copy to tmp table | alter table tbl01 engine innodb  |
+----+------+---------------------+------+---------+------+-------------------+----------------------------------+

Apenas como informação adicional e em relação à comparação entre as engines MYISAM e INNODB, segue abaixo um estudo feito e publicado pela Oracle que demonstra algumas diferenças de performance entre as duas engines.

 

O gráfico acima demonstra que a engine INNODB oferece maior escalabilidade ao se utilizar de múltiplos núcleos de CPU . Este teste demonstra que o nível de lock de tabela implementada na engine MYISAM reduz o throughput em ambientes que possuem workloads de leitura/escrita.

 

O gráfico acima demonstra que a performance de leituras utilizando a engine INNODB oferece um throughput e escalabilidades superiores se comparados com a engine MYISAM.