Detailed analysis of the differences between MySQL InnoDB and MyISAM data engines

InnoDB and MyISAM are the two most commonly used table types inusing MySQL. Each has its own advantages and disadvantages, depending on the specific application. The basic difference is: the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type supports

The MyISAM type table emphasizes performance, and its execution times are faster than the InnoDB type, but does not provide transaction support. InnoDB provides transaction support and advanced database features such as foreign keys.

MyIASM is a new version of the IASM table, with the following extensions:
Binary level portability.
NULL columnIndex.
Has less fragmentation than ISAM tables for variable-length rows.
Support large files.
Better index compression.
Better key statistical distribution.
Better and faster auto_increment handling.

The following are some details and specific implementation differences:

1.InnoDB does not support FULLTEXT type indexes.
2. InnoDB does not save the specific number of rows in the table. That is to say, when executing selectcount(*) from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM only needs Simply read out the number of saved lines. Note that when the count(*) statement contains a where condition, the operations of the two tables are the same.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index with only this field, but in the MyISAM table, a joint index can be established with other fields.
4.DELETEWhen FROM table, InnoDB will not re-create the table, butdeleterow by row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, and then change it to an InnoDB table after importing the data. However, for the additional InnoDB features used (such as external Key) tables are not applicable.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, for example, update table set num=1 where name like “%aaa%”

No table is omnipotent. Only by selecting the appropriate table type appropriately for the business type can the performance advantages of MySQL be maximized.

The above is the detailed content of Detailed analysis of the differences between MySQL InnoDB and MyISAM data engines. For more information, please follow other related articles on the PHP Chinese website!