MySQL Storage engine comparison

The common storage engine for MySQL is MyISAM, InnoDB, memory, MERGE, where InnoDB provides transaction security tables, and other storage engines are non-transactional security tables.

MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, but it has fast access and no requirement for transactional integrity.

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

The memory storage engine creates a table using the contents of the existing memories. Each memory meter only actually corresponds to one disk file. Memory Type table access is very fast because its data is placed in memory, and the hash index is used by default. But once the service is closed, the data in the table is lost.

The merge storage engine is a combination of a set of MyISAM tables that must be structured exactly the same as the MyISAM tables. The merge table itself has no data, and the query, update, and delete operations on the merge type table are performed on the internal MyISAM table.

The MyISAM table also supports different storage formats in 3:
1 Static tables
2 Dynamic Tables
3 Compression table
Static tables are the default storage format, and fields in static tables are non-variable-length fields, with the advantage that storage is very fast, easy to cache, failures are easy to recover, and the disadvantage is that it takes up more space than a dynamic table. (Note: In the case of storage, when the width of the column is not enough, a space is used to make up, when the access time does not get these spaces)
The field of a dynamic table is longer, and the advantage is that it takes up relatively little space, but frequent updates to delete records are fragmented, require regular performance improvements, and are relatively difficult to recover in the event of a failure.
Compact tables take up less disk space and each record is individually compressed, so there is very little access expense.

MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires that the schedule must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.
Two types of InnoDB storage: 1 Use shared tablespace storage 2 using multi-table spaces

Memory type of storage engine is mainly used in those with less frequent changes in the Code table, or as a statistical operation of the intermediate results table, easy to efficiently analyze the heap intermediate results and get the final statistical results. It is prudent to update the tables of the memory storage engine because the data is not actually written to disk, so be sure to consider how to obtain the modified data after the next service restart.

The merge is used to logically group together a series of equivalent MyISAM tables and reference it as an object. The advantage of the merge table is that it can break the limit on the size of a single MyISAM table, and it can effectively improve the access efficiency of the merge table by distributing different tables on multiple disks.

Another article:-)

This is the time to see the high performanceMySQL, see the storage engine this place feels a lot of detail is unfamiliar, so summarize small remember some

In order to adapt to a variety of different operating environments,MySQL offers a variety of different storage engines (Storage Engine), at the level of application development, developers can choose the right one based on different needsStorage Engine Program, more flexible is that you can according to each table will be stored data characteristics, choose a differentMySQL architecture, at the highest level of abstraction, can be represented by the hierarchical architecture of Garlan & Shaw (left)

The application layer provides the user interface for all RDBMS users, and the logic layer includes the implementation of all core functions, and the physical layer is responsible for storing the data on the hardware device.

The right side of the graph describes the composition of the logical layer, which consists of the query processing subsystem, the transaction management subsystem, the recovery management subsystem, and the storage management subsystem.
The three images above are from an unofficial (not guaranteed to be correct)MySQL, given in the book "MySQL general structure (, basically corresponds to the logic Layer, from the right side of the first picture can be seenMySQL logic layer also conforms to the hierarchical architecture) or is quite consistent.

The interface between the second and third tiers of the connection is not a single API for any storage engine. Probably consists of 20 basic functions like "Start transaction, return result set". The storage engine does not handle SQL and does not communicate with each other, their task is simply to respond to requests from the top.

some features of the storage engine

The four storage engines mentioned above have their respective environments, depending on their unique characteristics. Mainly embodied in performance, transaction, concurrency control, referential integrity, caching, failure recovery, backup and back-up, and several other aspects


At present, the more popular storage engine is MyISAM and InnoDB. And MyISAM is the first choice for most Web applications. The main difference between MyISAM and InnoDB is in performance and transaction control.

MyISAM is an early ISAM (Indexed sequential Access Method, I'm using an extended implementation of MySQL5.0 that doesn't support ISAM anymore, and ISAM is designed to handle a situation where read frequency is much larger than write frequency, so ISAM and later MyISAM do not consider support for things, eliminate the TPM, do not require transaction logging, the ISAM query efficiency is considerable, and the memory accounts for With little. MyISAM, while inheriting this kind of advantage, has provided a lot of practical new features and related tools with the times. For example, given concurrency control, table-level locks are provided, although MyISAM itself does not support fault tolerance, but can be myisamchk for failback. And because MyISAM is each table using its own separate storage files (myd data files and myi index files), it makes backup and recovery very convenient (copy overwrite), but also supports online recovery.

So if your application does not require transactions and is only dealing with basic crud operations, then MyISAM is the choice

The InnoDB is designed for high concurrency and read-write scenarios. Use MVCC (multi-version Concurrency Control) and row-level locks to provide acid-compliant transactional support. INNODB supports foreign key referential integrity and is capable of failure recovery. In addition InnoDB performance is actually good, especially in the case of large data processing, in the official words: InnoDB CPU efficiency is other disk-based relational database storage engine can not be compared. But InnoDB backup recovery is a bit cumbersome, unless you use the Mulit-tablespace support provided by version 4.1, because InnoDB and MyISAM are different, his data files do not correspond to each table independently. Instead of using a shared table space, a simple copy overwrite method does not apply to him and must be stopped 

Attach a copy of the high performance Attribute

MyISAM

Heap

BDB

InnoDB

Transactions	No	No	Yes	Yes
Lock Granularity	Table	Table	Page (8 KB)	Row
Split files	In-memory	Single File per table	Tablespace (s)
Isolation Levels	None	None	Read committed	All
Portable format	Yes	N/A	No	Yes
Referential integrity	No	No	No	Yes
Primary Key with Data	No	No	Yes	Yes
MySQL caches data records	No	Yes	Yes	Yes
Availability	All versions	All versions	All Versions

MySQL Storage engine comparison