How to choose the appropriate engine for MySQL and perform engine conversion

How do we choose the right engine? Here is a simple summary: "Unless you need to use some features that InnoDB does not have, and there is no other way to replace it, you should give priority to the InnoDB engine."

Unless it is absolutely necessary, Otherwise, it is not recommended to mix multiple storage engines, otherwise it may cause a series of complex problems and some potential bugs.

Several major factors to consider when using different engines:

1. Transaction

If necessary Transaction support, then InnoDB or XtraDB are currently the most stable. If transactions are not required and the main operations are SELECT and INSERT, MyISAM is a good choice.

2. Backup

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

3. Crash recovery

When the amount of data is relatively large, how to quickly recover after a system crash is an issue that needs to be considered, and this is also The reason many people choose InnoDB even if they don't need transaction support.

Conversion table engine:

1.ALTER TABLE

The simplest The method is the ALTER TABLE statement:

 mysql> ALTER TABLE mytable ENGINE = InnoDB;

This syntax is suitable for 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 the data to a file, and then modify the CREATE TABLE statement in the file Storage engine, please pay attention to modify the table name.

At the same time, please note that the mysqldump tool will automatically add the DROP TABLE statement before the CREATE TABLE statement by default to be careful of data loss.

3. Creation and query

To combine the first and second methods, first create a new storage engine table, and then use INSTER ...SELECT syntax to import 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 amount of data is large, it can be processed in batches, and transaction submission operations are performed for each piece of data to avoid undo caused by large transactions. Percona Toolkit provides a pt-online-schema-change tool, which can be executed simply and conveniently to avoid errors caused by manual operations.

The above is the detailed content of How to choose the appropriate engine for MySQL and perform engine conversion. For more information, please follow other related articles on the PHP Chinese website!