On the differences between two data engines in MySQL database

InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons, depending on the application.

The basic differences are:

MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support.

The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

Myiasm is a new version of the Iasm table, with the following extensions:

1. Portability at the binary level.
2. NULL column index.
3. There is less fragmentation than the ISAM table for variable-length rows.
4. Support for large files.
5. Better index compression.
6. A better key? Statistical distribution.
7. Better and faster auto_increment processing.

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

1.InnoDB does not support indexes of type Fulltext.

The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.

4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.

The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the 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, to maximize the performance advantage of MySQL.

On the differences between two data engines in MySQL database