MySQL Database engine

Mysql provides us with 4 of data engines:

MYISAM, Heap and InnoDB and Berkley (BDB)

This highlights the common MyISAM and InnoDB

Myiasm engine

1.MyIASM is the default engine for MySQL , but it does not provide support for database transactions , row-level locks and foreign keys ,

so when Insert (insert) or update (update) data when the write operation needs to lock the entire table, the efficiency is lower .

2.MYISAM emphasis on fast read operations

An important flaw in the 3.MyISAM format is the inability to recover data after a table is corrupted.

InnoDB engine

The InnoDB engine provides support for database acid transactions and implements four isolation levels for SQL standards

Supports row locks and foreign keys.

The MySQL runtime InnoDB creates a buffer pool in memory for buffering data and indexes.

1. When overriding index queries, you can use their default clustered index columns In addition to the included columns of the index itself

2, Nnodb does not save the specific number of rows in the table, that is, to execute SELECT COUNT (*) fromtable, InnoDB to scan through the entire table to calculate how many rows, but MyISAM simply read out the number of rows saved.

3, for the secondary index of InnoDB, its leaf node stores the index value and the location to the primary key index, and then needs to query the table's field values through the primary key, so the secondary index stores the value of the primary key

4, the overlay index can also be used on the INNODB default clustered index

5, the InnoDB engine has all stored the primary key ID, transaction ID, rollback pointer, non-primary key ID, his query will be non-primary key ID can also overwrite to obtain the primary key ID

The difference between the two engines:

1. InnoDB provides support for database transactions, supports row and foreign keys, and MyISAM does not

The primary key binding is expected in 2.InnoDB, and all indexes in MyISAM are nonclustered indexes

3.InnoDB uses the B-tree, while Mysiam is using the B+tree

　　

MySQL Database engine