Mysql is a must-use for PHP development. So do you know the difference between MyISAM and InnoDB in Mysql?

Differences in composition:

Each MyISAM is stored in 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 aspects:

The MyISAM type table emphasizes performance, and its execution times are faster than the InnoDB type, but it does not provide transaction support

InnoDB provides transaction support, foreign keys and other advanced database functions

SELECT UPDATE, INSERT, Delete operations
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, you should When using InnoDB table

2.DELETE FROM table, InnoDB will not re-create the table, but will 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, then import the data and then change it to an InnoDB table. However, for the additional InnoDB features used (For example, foreign key) tables are not applicable

Operations on AUTO_INCREMENT

Internal processing of one AUTO_INCREMEN column per table.

MyISAM automatically updates this column for INSERT and UPDATE operations. 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).

The 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 established with other fields Union indexes

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.

The automatic growth counter is only stored in the main memory, not on the disk.

For the algorithm implementation of this calculator, please refer to

How the AUTO_INCREMENT column is listed in InnoDB The specific number of rows in the working table

select count(*) from table, MyISAM simply reads the number of saved rows. Note that when the count(*) statement contains the where condition, The operations of the two tables are the same

InnoDB does not save the specific number of rows in the table, that is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are

Lock

Table lock

provides row locking (locking on row level) and non-locking read in

SELECTs consistent with Oracle type. 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%”