Detailed details of the Mysql storage engine comparison _mysql

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

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

The memory storage engine uses content that exists in memory to create a table. Each memory table actually corresponds to only one disk file. Table access of the memory type is very fast because its data is 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 structurally identical to the MyISAM tables. The merge table itself does not have the data, the merge type table carries on the query, the update, the deletion operation, is carries on the internal MyISAM table.

The MyISAM table also supports different storage formats in 3:
1 Static tables
2 Dynamic Table
3 Compression table
Static table is the default storage format, the fields in the static table are not variable long fields, the advantage is: storage is very fast, easy to cache, failure easy to recover; The disadvantage is that the space used is usually more than the dynamic table. (Note: When storing, the columns are not wide enough to be filled with spaces, and they are not available when they are accessed.)
Dynamic table fields are longer, with the advantage that there is a relatively small footprint, but frequent updates to deleted records can produce fragmentation, requiring periodic performance improvements, and relatively difficult recoveries when they fail.
Compressed tables take up less disk space and each record is compressed individually, so only very small access expenses are available.

MySQL supports the foreign key storage engine only InnoDB, when creating a foreign key, requires that the schedule must have a corresponding index, and the child table automatically creates the corresponding index when the foreign key is created.
InnoDB storage mode is two kinds: 1 use shared tablespace Storage 2 use multiple-table space

The memory type of storage engine is mainly used for those code tables with infrequently varying content, or as an intermediate result table for statistical operations, which facilitates efficient heap of intermediate results for analysis and 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 these modified data the next time the service is restarted.

The merge is used to logically combine a series of equivalent MyISAM tables and refer to it as an object. The advantage of the merge table is that it can break the limit of 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.

In order to adapt to the various operating environments,MysqlProvides a variety of different storage engines (StorageEngine), at the level of application development, developers can choose the right one based on different requirementsStorageEngine scheme, the more flexible is that you can according to each table will be stored data characteristics, choose a differentStorageEngine, that is to say, in aMysqlDatabase, you can mix and use a variety of differentStorageEngine

First, a little glimpse.The MySQL architecture, at the highest level of abstraction, can be represented by Garlan & Shaw's layered architecture (left)

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

The right side of the diagram describes the composition of the logical layer more concretely, the query processing subsystem, the transaction management subsystem, the recovery management subsystem and the storage management subsystem together compose the MySQL logic layer. It is believed that the position of Storage engine is in the Storage management, Storage engine is part of Storage management subsystem.

To get a clearer idea, here's a comprehensive architecture diagram (or, more specifically, a flowchart, just ignoring feedback).

The above three images come from an unofficial (not guaranteed percent correct) MySQL architecture report, and "High performance MySQL" book in the MySQL general structure (the following figure, basically corresponds to logic Layer, From the right side of the first picture you can see that MySQL logic layer also comply with the layered architecture.

The interface between the second and third layers of the diagram above is a single API that is not intended for any storage engine. Approximately 20 basic functions such as "Start transaction, return result set" are composed. The storage engine does not handle SQL and does not communicate with each other, and their task is simply to respond to requests from the top.

some features of the storage engine

The four storage engines mentioned above all have their own applicable environments, depending on some of their unique characteristics. Mainly embodied in performance, transactions, concurrency control, referential integrity, caching, failure recovery, backup and restore and so on several aspects


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

MyISAM is the early ISAM (Indexed sequential Access method, The MySQL5.0 I'm using now has no support for ISAM extension implementations, ISAM is designed to handle a situation where the frequency of reading is much greater than the frequency of writing, so ISAM and later MyISAM do not consider support for things, exclude the TPM, do not need transaction records, ISAM query efficiency is considerable, and memory accounted for With little. While inheriting this kind of advantage, MyISAM has provided a lot of practical new features and related tools with the times. For example, given the concurrency control, table-level locks are provided, although MyISAM itself does not support fault-tolerant, but can be myisamchk for recovery. And since MyISAM uses separate storage files (myd data files and myi index files) for each table, backup and recovery is convenient (copies are overwritten) and online restores are supported.

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

InnoDB is designed to be suitable for high concurrent read and write situations. Provides acid-compliant transaction support using MVCC (multi-version concurrency control) and row-level locks. INNODB supports foreign key referential integrity and is capable of failure recovery. In addition InnoDB performance is good, especially in the case of large data processing, in the official words is: InnoDB CPU efficiency is other disk-based relational database storage engine can not be compared. However, InnoDB backup recovery is a bit tricky, unless you use the Mulit-tablespace support provided in version 4.1, because InnoDB and MyISAM differ, his data files are not independent of each table. Instead of using a shared table space, a simple copy-covering method does not apply to him, and must be stoppeddata recovery after MySQL. It is much simpler to use the per-table tablespacesd to make each table correspond to a separate table space file.

In general, InnoDB is a good choice if you need transactional support and have a higher frequency of concurrent read and write. If the concurrent read and write frequency is not high, can actually consider BDB, but because in MySQL5.1 and later versions, will no longer provide BDB support. There's no such option.

As for Heap and BDB (Berkeley DB), the penetration rate is not as good as the first two, but in some cases it's quite applicable.

Heap storage engine is to store data in memory, because there is no disk i./o waiting, very fast. However, because it is a memory storage engine, any modifications you make will disappear after the server restarts.

Heap is a good fit to use when doing tests.

BDB is MySQL's first transaction-safe storage engine. Built on the Berkeley DB Database library, it is also transaction-safe, but BDB's penetration is obviously less than InnoDB, since most of the storage engines looking for support transactions in MySQL are also looking for support MVCC or row-level lock storage engines , and BDB only supports Page-level Lock.

Attached is a list of the characteristics of each storage engine in the high performance MySQL

Attribute

MyISAM

Heap

BDB

InnoDB

Transactions

No

No

Yes

Yes

Lock Granularity

Table

Table

Page (8 KB)

Row

Storage

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

Mysql-max

All versions