An explanation of the six major differences between Mysql storage engines InnoDB and Myisam

MyISAM
InnoDB
Differences in composition:

Each MyISAM is stored as three files on the disk. The name of the first file begins with the name of the table and the extension indicates the file type.

.frm file stores table definitions.

The extension of the data file is .MYD (MYData).

The extension of the index file is .MYI (MYIndex).

Disk-based resources are the InnoDB table space data file and its log file. The size of the InnoDB table is only limited by the size of the operating system file, generally 2GB


Transaction processing aspect:

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, foreign keys and other advanced database functions

SELECT UPDATE,INSERT,DeleteOperation

If you perform a large number of SELECTs, MyISAM is a better choice


1.If your data performs a large number of INSERT or UPDATE, for performance reasons, InnoDB table


2.DELETE FROM table, InnoDB will not Re-create the table and delete it row by row.

3.LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to an InnoDB table, but for Tables that use additional InnoDB features (such as foreign keys) are not applicable

Operations on AUTO_INCREMENT

Per Internal handling of an AUTO_INCREMEN column in a table.


MyISAM automatically updates for INSERT and UPDATE operations This column . This makes AUTO_INCREMENT columns faster (at least 10%). After the value at the top of the sequence is deleted, it cannot be used again. (When the AUTO_INCREMENT column is defined as the last column of a multi-column index, it is possible to reuse values ​​removed from the top of the sequence).

AUTO_INCREMENT value can be reset with ALTER TABLE or myisamch

For AUTO_INCREMENT type fields, InnoDB must contain an index with only this field, but in the MyISAM table, it can be created together with other fields Union index

Better and faster auto_increment handling

If you specify the AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary contains a counter called the autoincrement counter, which Used to assign a new value to the column.


Automatic growth counter is only stored in the main memory, instead of existing on the disk

The algorithm implementation of the calculator, please refer to

The above is the detailed content of An explanation of the six major differences between Mysql storage engines InnoDB and Myisam. For more information, please follow other related articles on the PHP Chinese website!