MySQL database each storage engine comparison

one of the most important features of the MySQL database that distinguishes it from other databases is its plug-in table storage engine , which is table-based rather than databaseInnoDB Storage Engine: support transactions, which are designed primarily for online transaction processing (OLTP) applications, feature row lock design, support for foreign keys, and support for non-locking reads similar to Oracle, where default read operations do not generate locks, which place data in a logical table space, in addition, The InnoDB storage engine supports the use of bare devices to establish its tablespace, which is a special block device file that is not formatted and not read by UNIX through a file system, and is a device that is not managed directly by the operating system. This device is less than the operating system layer, and I/O efficiency is higher. InnoDB also provides high performance and high-availability features such as insert buffer, two write (double write), Adaptive hash Indexing (Adaptive hash Index), pre-read (read ahead), and more.
The InnoDB uses multiple versions of concurrency control to achieve high concurrency and4 isolation levels for SQL standard, respectively, are:

• UNCOMMITTED read (READ UNCOMMITTED) : In the uncommitted read level, changes in the transaction are visible even if they are not committed. Transactions can read uncommitted data, which is also known as Dirty reads (Dirty read). This level can cause a lot of problems, and in performance, uncommitted reads are not much better than the others, but there is a lack of many benefits from other levels that are rarely used in practical applications.
• Submit Read (committed) : The default isolation level for most database systems is read-committed (but not MySQL). The commit read satisfies the simple definition of the previously mentioned isolation: When a transaction starts, it can only "see" the changes that have been made to the firm that has been submitted. In other words, any changes you make to a transaction from the beginning until it is committed are not visible to other transactions. This level is sometimes called non-repeatable read (nonrepeatable read), because two times the same query is executed, you may get a different result.
• REPEATABLE READ (repeatable Read): Repeatable read solves the problem of dirty reading. This level guarantees that the result of reading the same record multiple times in the same transaction is consistent. In theory, however, it is not possible to re-read the isolation level to resolve anotherPhantom Read (Phantom Read)problem. So-calledPhantom Readingrefers to when a transaction reads a range of records, another transaction inserts a new record in that range, and when the previous transaction reads the record of that range again, it producesPhantom Line (Phantom row). REPEATABLE Read is the default transaction isolation level for MySQL .
• Serializable (Serializable) : Serializable is the highest isolation level. It avoids the above-mentioned phantom-reading problem by forcing the transaction to execute serially. In simple terms, serializable can add locks to every row of data that is read, so it can cause a lot of timeouts and lock contention issues. This isolation level is rarely used in real-world applications, and is only considered when there is a need to ensure that data is consistent and acceptable without concurrency.

for data storage in tables, the INNODB storage engine uses aGathering, so that each table's storage is stored in the order of the primary key, and if no primary key is explicitly specified when the table is defined, the InnoDB storage engine generates a 6-byte rowid for each row, which is used as the primary key.
MyISAM Storage Engine:MyISAM Storage Engine does not support transaction, table lock design, support full-text indexing, mainly for some OLAP (online analytical Processing) database applications, Another differentiator for the MyISAM storage engine is that its buffer pool caches only the index files, not the data files , and the MyISAM Storage engine table consists of myd and myi, MyD used to hold data files, myi to hold index files, Before the mysql5.0 version, the MyISAM default supported table size is 4gb,5.0 version, myisam default support for 256TB of single-table data,
NDB Storage Engine:is a clustered storage engine whose structure is the share-nothing cluster architecture,

Shared everthting: generally for a single host, fully transparent shared cpu/memory/io, parallel processing power is the worst, typically representative of SQL Server

Shared disk : Each processing unit uses its own private CPU and memory to share the system. The typical representative of Oracle RAC, which is data sharing, can increase the ability of parallel processing by adding nodes, which is better to expand. It is similar to SMP (symmetric multi-processing) mode, but when the memory interface is saturated, adding nodes does not achieve higher performance.

shared nothing: each processing unit has its own private cpu/memory/hard disk, etc., there is no shared resources, similar to the MPP (massively parallel processing) mode, the processing units through protocol communication, parallel processing and extension capabilities better. Typical representative DB2 DPF and Hadoop, each node independent, each processing their own data, the processed results may be aggregated up or between the nodes flow.

What we often call sharding is the Share Nothing architecture, which is to divide a table horizontally from the physical storage and assign it to multiple servers (or instances), each of which can work independently, with a common schema, such as MySQL Proxy and Google 's various architectures, simply increase the number of servers to increase processing capacity and capacity.

Thus providing higher availability, NDB is characterized by the fact that the data is all stored in memory, so the primary key lookup is extremely fast, and by adding the NDB data storage node can improve the database performance linearly, is a highly available cluster system. However, the link operation of the NDB storage engine is done at the MySQL database layer, not at the storage engine level, meaning that complex connection operations require significant network overhead, so queries are slow.

Memory storage Engine (formerly known as the heap storage engine):

The data in the table is stored in memory, and if the database restarts or crashes, the data in the table disappears, which is ideal for temporary tables that store temporary data, using a hash index instead of a B + Tree index. Table locks are supported only, concurrency is poor, and storage of variable-length fields (varchar) is performed in the form of a constant field (char), thus wasting memory.

Archive Storage Engine:

only insert and select operations are supported, and indexing is supported starting from mysql5.1, ideal for storing archived data, such as log information, designed to provide high-speed insertion and compression capabilities.

Federated Storage Engine:

Storage Engine table does not hold data, he just point to a remote MySQL database server table, only support MySQL database table, not support heterogeneous database tables (heterogeneous database system is a collection of related multiple database systems, can achieve data sharing and transparent access, Each database system already exists before it joins the heterogeneous database system, and has its own DBMS. ）

Maria Storage Engine:

The design goal is to replace the original MySQL storage engine, which is characterized by supporting the cache data and index files, applying the row lock design, providing the MVCC (multi-version concurrency control) feature, supporting transactional and non-transactional security options, and better processing performance of BLOB character types,

There are two types of fields in MySQL that are confusing, that is, text and blobs, especially bloggers who write their own blogs do not know whether to change their own blog text segment select text or blob type.

Here are a few differences:

First, the main difference

The main difference between the text and the Blob is that the BLOB holds the binary data, and the text holds the character data. At present, almost all the images in the blog content are not stored in the binary database, but the image is uploaded to the server and then the body using the tag reference, such a blog can use the text type. The blob can then be converted into binary to the database.

Ii. Types of differences

There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.

There are also 4 types of text: Tinytext, text, Mediumtext, and Longtext. These types, like blob types, have the same maximum length and storage requirements.

Third, Character set

Blob columns do not have a character set, and sort and compare numeric values based on column-valued bytes. The text column has a character set, and the values are sorted and compared according to the collation rules of the character set

Four, case

There is no case conversion during the storage or retrieval of a text or BLOB column, all the same!

Five, Strict mode

When running in non-strict mode, if you assign a BLOB or text column A value that exceeds the maximum length of the column type, the value is truncated to ensure that it is appropriate. If the truncated character is not a space, a warning will be generated. With strict SQL mode, errors are generated and the values are rejected rather than intercepted and given a warning.

Vi. Other

Trailing spaces are not deleted when you save or retrieve the values of the Blob and text columns.

For the index of the Blob and text columns, you must specify the length of the index prefix.

Blob and text columns cannot have default values.

Only the first max_sort_length bytes of the column are used when sorting. The default value for Max_sort_length is 1024.

When you want to make sense for more than max_sort_length bytes, another way to use a group by or order by for a BLOB or text column with a Long value is to convert the column value to a fixed-length object. The standard method is to use the SUBSTRING function.

The maximum size of a blob or text object is determined by its type, but the maximum value that can actually be passed between the client and the server is determined by the amount of available memory and the size of the communication buffer. You can change the size of the message buffer by changing the value of the Max_allowed_packet variable, but you must modify both the server and the client program.

In addition to the above storage engine, MySQL also has many other storage engines, including merge, CSV, Sphinx, Infobright, and they all have their own place of use,

MySQL database each storage engine comparison