MySQL storage engine Mysiam and InnoDB feature comparison

Things: MyISAM does not support things, myisam types of tables emphasize performance, which is performed more quickly than InnoDB

types. If things are not considered, a large number of select and insert are suitable for MyISAM table

Locks: myisam supports table Locks
    InnoDB provides row-level locks, provides non-lock reads consistent with Oracle type ( Non-locking read in),
   InnoDB row locks are not absolute, and when executing an SQL statement, MySQL cannot determine the range to scan, and the InnoDB table also locks the full table, such as the Update table set num =1 where name like "%aaa%"

Multiple Versions high concurrency: Multiple versions of high concurrency is MVCC,INNODB only MVCC support high concurrency, achieve four standard
quasi-isolation level, by default is repeatable read, through the gap lock to prevent Phantom read. The gap lock makes the InnoDB not only
locks the rows involved in the query, but also locks the gaps in the index to prevent phantom rows from being written.

Transaction Repair and crash repair: MyISAM can check and repair operations manually or automatically, but not crash
recovery. InnoDB to some extent support for data loss recovery at crash time, but not absolute recovery, and long recovery time

Backup: If you need to shut down the server to perform a backup, the backup policy can be ignored. However, the need for online hot backup, InnoDB is better to choose the


Conversion engine, there are three ways
1:alert Table statement:  alert table mytable engine=innodb;
This method is the least efficient, need to copy the data to a new table, the system IO capability may be exhausted, while in the original table lock, need to avoid the table peak usage, and will cause the original engine features lost, index, foreign keys, such as

2: Import and Export
through the Mysqldump tool can be exported to a file. However, default to the CREATE table with the DROP TABLE statement, no attention will result in data loss

2: Create a query
Create a new storage engine table, insert---Select Import, This is a good choice when the data volume level is low, if the data volume is too large, it can be processed in batches. When finished, the new table is a copy of the old table, but the old table still exists. You can also lock the original table in the responsible process to ensure consistency with the original table data.

MySQL storage engine Mysiam and InnoDB feature comparison