MySQL's storage engine contrast

In a previous article on MySQL architecture, it was mentioned that MySQL's biggest feature is its pluggable plug-in storage engine, which contrasts several major storage engines. In particular, MySQL is open source, so if you're not satisfied with some of the storage engines, you can modify or write a storage engine to increase the features you want, which is one of the advantages of MySQL as an open source database.

The following table shows the characteristics of the various storage engines:

Two of the most common storage engines are MyISAM and InnoDB

When you first contact MySQL may be a little surprised that there is no support for transaction storage engine, the study of relational database theory, people know that transactions are the core of relational databases. But in real-world applications (especially the Internet), in order to improve performance, it is possible to discard transactions in certain scenarios. Here are a few of the storage engines:

MyISAM Storage Engine

MyISAM is the MySQL official provides the default storage engine, its characteristic is does not support the transaction, the table lock and the Full-text index, for some OLAP system, the operation speed is fast.

Each myisam is stored on disk as three files. The file name is the same as the table name, and the extension is. frm (storage table definition),. MyD (MYData, storing data),. Myi (myindex, storage index). The special note here is that MyISAM does not cache data files, only the index files.

InnoDB Storage Engine

The InnoDB storage engine supports transactions, primarily for OLTP applications, characterized by row-lock settings, support for foreign keys, and support for unlocked reads like Oracle, which means that no locks are generated by default. InnoDB places data in a logical tablespace (similar to Oracle). InnoDB through multiple versioning concurrency control to achieve high concurrency, the ANSI standard of 4 isolation levels, the default is repeatable, using a strategy called Next-key locking to avoid phantom reading.

For storage of data in a table, InnoDB is stored in a way that is similar to an Oracle Index organization table clustered.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared to the MyISAM storage engine, InnoDB writes less efficiently and consumes more disk space to preserve data and indexes.