MySQL Storage engine features comparison

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

Two of the most common storage engines are MyISAM and InnoDB

When I first approached MySQL, it might be a little surprised to have a storage engine that doesn't support transactions, and everyone who learns relational database theory knows that transactions are the core of relational databases. But in real-world applications (especially the Internet), in order to improve performance, the transaction can be discarded in some scenarios. The following describes the various storage engines:

MyISAM Storage Engine MyISAM is the default storage engine provided by MySQL, which is characterized by not supporting transactions, table locks, and full-text indexing, and for some OLAP systems, the operation is fast. Each myisam is stored as three files on disk. The file name is the same as the table name, and the extension is. frm (store table definition), respectively. MYD (MYData, storing data),. MYI (myindex, storage index). In particular, it is important to note that MyISAM does not cache data files and caches only index files. Back to top 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 non-locking reads similar to that of Oracle, where the read does not generate locks by default. InnoDB puts the data in a logical tablespace (similar to Oracle). InnoDB achieves high concurrency with multiple versions of concurrency control, implements 4 isolation levels for ANSI standards, defaults to repeatable, and avoids phantom reads with a strategy called Next-key locking.

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

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and cable
Cited.

Back to top NDB storage engine

The NDB storage engine is a clustered storage engine similar to Oracle's RAC, but it is the share nothing architecture and therefore provides a higher level of high availability and scalability. NDB is characterized by the fact that the data is all in memory, so it is very fast to find it through the primary key.

With regard to NDB, there is a problem to note that its connection (join) operation is done at the MySQL database layer, not at the storage engine level, which means that complex join operations require significant network overhead and query speed is slow.

Back to the top memory (HEAP) storage engine

The Memory storage engine (formerly called the heap) stores data in the table and, if the database restarts or crashes, data is lost, making it ideal for storing temporary data.

Back to top archive storage engine

As its name implies, archive is ideal for storing archived data, such as log information. It supports only insert and select operations and is designed primarily to provide high-speed insertion and compression capabilities.

Back to top federated storage engine

The federated storage Engine does not hold data, it points to at least one table on a remote MySQL database server, very similar to the transparent gateway of Oracle.

Back to the top Maria storage engine

The Maria storage Engine is a newly developed engine designed to replace the original MyISAM storage engine and become the default storage engine for MySQL.

Last but not least, although MySQL has a lot of storage engines, there is no good or bad relationship between them, but rather a choice of storage engines for their business based on different applications. If you are strong enough, you can also modify or develop the storage engine, this is not the charm of open source?

This article transferred from: http://www.open-open.com/lib/view/open1370959041320.html

MySQL Storage engine features comparison