How does MySQL select an appropriate engine and engine conversion ., Mysql engine Conversion

How does MySQL select an appropriate engine and engine conversion ., Mysql engine Conversion

How can we choose a suitable engine? Here is a simple sentence:"Unless some features not available in InnoDB are used and there is no other way to replace them, the InnoDB engine should be preferred."

Unless you have to, we do not recommend that you use multiple storage engines together. Otherwise, a series of complex problems and potential bugs may occur.

Consider the following factors when using different engines:

1. Transactions

If transaction support is required, InnoDB or XtraDB is currently the most stable. MyISAM is a good choice if you do not need a transaction and mainly perform SELECT and INSERT operations.

2. Backup

If you need online hot backup, InnoDB is the basic choice.

3. Crash recovery

When the data volume is large, how to quickly recover after the system crashes is a problem that needs to be considered. This is why many people choose InnoDB even if they do not need transaction support.

 

 Conversion table engine:

1. ALTER TABLE

The simplest method is the alter table statement: mysql> alter table mytable ENGINE = InnoDB;

This syntax applies to any storage engine, but it takes a long time to execute.

The storage engine of the conversion table will lose all features related to the original engine.

2. Export and Import

You can use the mysqldump tool to export data to a file and modify the storage engine of the create table statement in the file. Note that you must modify the TABLE name.

Note that the mysqldump tool will automatically add the drop table statement before the create table statement by default, so be careful about data loss.

3. Create and query

First, create a new storage engine table, and then use the INSTER... SELECT syntax to export data.

Mysql> create table innodb_table LIKE myisam_table;

Mysql> alter table innodb_table ENGINE = InnoDB;

Mysql> inster into innodb_table SELECT * FROM myisam_table;

If the data volume is large, it can be processed in batches to execute transaction commit operations for each piece of data to avoid undo operations produced by large transactions. Percona Toolkit provides a pt-online-schema-change tool, which is simple and convenient for execution to avoid mistakes caused by manual operations.