MySQL InnoDB and MyISAM Data Engine differential analysis _mysql

Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys.

Myiasm is a new version of the Iasm table, with the following extensions:
Portability of the binary hierarchy.
The null column index.
Fewer fragments are available for variable-length rows than for ISAM tables.
Support for large files.
Better index compression.
A better key? Statistical distribution.
Better and faster auto_increment processing.

The following are some details and specific implementations of the differences:

The fulltext type index is not supported for 1.InnoDB.
The number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.
4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time.
5.LOAD table from Master does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import the data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign key) of the table does not apply.

In addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as Update table set num=1 where name like "%aaa%"

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, in order to maximize the performance of MySQL advantage.