MySQL two big storage engine InnoDB and MyISAM

1.InnoDB Storage Engine

InnoDB becomes the default storage engine in MySQL5.5 . InnoDB is a transactional storage engine designed to handle a large number of short-term transactions. Its performance and automatic crash recovery feature make it popular in non-transactional scenarios as well.

So you can say, " unless you need to use features that some innodb don't have, and there's no other way to replace them, you should choose them first ."

innodb data is stored in a table space, which stores the data and indexes of each table in a separate file.

The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels. The default level is REPEATABLE READ (repeatable read), and the presence of Phantom reads is prevented by the gap lock policy.

InnoDB is based on clustered indexes, and clustered indexes have high performance in querying primary keys.

InnoDB supports true hot backup, which can be achieved through the Xtrabackup tool.

Many optimizations are made within the InnoDB, including the predictable pre-reading of data from disk, the ability to automatically create a hash index in memory, and the ability to speed up insert buffers for insert operations.

Application Scenarios :

Requires online hot backup

Large data volumes require a quick recovery after a system crash. such as order Processing.

2.MyISAM Storage Engine

Prior to MySQL5.1, MyISAM was the default storage engine. MyISAM does not support transaction and row-level locks and the biggest flaw is the inability to recover safely after a crash.

. MyISAM stores a table in two files: Data files and index files.

MyISAM Lock the entire table, read all the tables needed to share the lock, write and lock it. However, when a table has a read query, you can also insert new records into the table (concurrent insertions)

MyISAM can perform check repair operations manually or automatically, but may result in data loss and very slow repair operations.

MyISAM fields such as BLOBs and text in a table can also be indexed based on the first 500 characters. MyISAM also supports full-text indexing, which is a segmentation-based index that can support complex queries.

MyISAM If you specify Delay_key_write (defer updating the index key) when you create the table, it can greatly improve write performance by writing the index to the in-memory key buffer each time the modification is completed, blocking the key buffer or closing the table when the corresponding index block is written to disk. However, the index is corrupted when the database or host crashes.

Application Scenarios :

It is mainly the scene of select and insert operation, such as the application of general log type.

MySQL two big storage engine InnoDB and MyISAM