MySQL Storage engine

InnoDB:

(1) Transaction security (Transaction-safe (ACID compliant)) Table with transaction (commit), rollback (rollback), and crash-repair capability (crash recovery capabilities).

(2) Support foreign key.

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

(4) For a field of type auto_increment, the InnoDB must contain only the index of that field.

(5) When you delete from table, InnoDB does not reestablish the table, but deletes one row at a time.

MyISAM:

(1) Transaction operations are not supported.

(2) Foreign keys are not supported.

(3) MyISAM Save the specific number of rows in the table, execute SELECT COUNT (*) from table as long as you simply read out the number of rows saved.

(4) For fields of type auto_increment, in the MyISAM table, you can establish a federated index with other fields.

(5) The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So, one process requests a read lock on a MyISAM table, and another process requests a write lock on the same table, how does mysql handle it? The answer is that the write process gets the lock first. Not only that, even if the read request goes to the lock waiting queue, and the write request is reached, the write lock is inserted before the read lock request! This is because MySQL considers writing requests to be generally more important than reading requests. This is why the MyISAM table is not well suited for applications with a large number of update operations and query operations, because a large number of update operations can cause query operations to be difficult to obtain read locks, which can be blocked forever. This situation can sometimes get very bad!

MyISAM is a read lock and a write lock (2 locks are table-level locks).

There are two modes for MySQL table-level locks: table-Shared read lock and table write lock. What do you mean, it means that when you read a MyISAM table, it does not block other users from reading requests for the same table, but it blocks writes to the same table, and writes to the MyISAM table blocks other users from reading and writing to the same table.

Reference Link: 78030110

Reference Link: 51217952

MySQL Storage engine