Two major MySQL storage engines: InnoDB and MyISAM and innodbmyisam

Two major MySQL storage engines: InnoDB and MyISAM and innodbmyisam

1. InnoDB Storage Engine

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

So we can say:"Unless you need some features not available in InnoDB and there is no other way to replace them, you should give priority to them.".

InnoDB data is stored in tablespaces, which can store the data and indexes of each table in a separate file.

  InnoDB adopts MVCC to support high concurrency and achieves four standard isolation levels. The default value is repeatable read (repeatable read), and the gap lock policy is used to prevent phantom READ.

InnoDB indicates that the cluster index is created based on the clustering index, and the clustering index has high performance in primary key query.

InnoDB supports real hot backup and can be implemented using XtraBackup.

InnoDB has made a lot of internal optimizations, including the predictable pre-read used to read data from the disk, the ability to automatically create hash indexes in the memory, and the ability to accelerate insert buffer operations.

Application scenarios:

Online hot backup is required

Large data volumes require quick recovery after a system crash. For example, order processing.

2. MyISAM storage engine

Before MySQL5.1, MyISAM was the default storage engine. MyISAM does not support transactions and row-level locks. The biggest defect is that it cannot be safely restored after a crash.

. MyISAM stores tables in two files: data files and index files.

MyISAM locks the entire table, adds a shared lock to all required tables during reading, and adds an exclusive lock to write data. However, when a table reads a query, you can insert a new record to the table (insert records concurrently)

MyISAM can manually or automatically perform repair check, but may cause data loss and the repair operation is very slow.

Long fields such as BLOB and TEXT in the MyISAM table can also be indexed based on the first 500 characters. MyISAM also supports full-text indexes, which are created based on Word Segmentation and support complex queries.

When MyISAM creates a table, if DELAY_KEY_WRITE is specified (the index key is updated with a delay), the index is written to the key buffer in the memory each time the modification is completed, when the key buffer is cleared or the table is closed, the corresponding index block is written to the disk. This method can greatly improve the write performance. However, the index is damaged when the database or host crashes.

 Application scenarios:

The main scenarios are SELECT and INSERT operations, such as general log applications.