MySQL database each storage engine comparison

one of the most important features of MySQL database that differs from other databases is its plug-in table storage engine , which is table-based. Rather than the 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. It puts the data in a logical table space. In addition InnoDB Storage Engine SupportBare EquipmentUsed to build its tablespace, the so-called bare device is a special block device file that is not formatted and is not read by UNIX through the file system, and is a device that is not managed directly by the operating system.

This device is less of a layer of the operating system. I/O is more efficient. 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 multi-version concurrency control to achieve high concurrency. And the 4 isolation levels of the SQL standard are implemented, each of which is:?

• UNCOMMITTED read (READ UNCOMMITTED) : In the uncommitted read level, changes in the transaction are visible to other transactions, even if they are not committed. Transactions are able to read uncommitted data, which is also known as Dirty reads (Dirty read). This level can cause very many problems. On the performance level, uncommitted reads are not much better than others, but there is a lack of many advantages at other levels. It is generally very seldom 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, a transaction starts from the beginning until it is committed. Whatever changes you make are not visible to other transactions.

  This level is sometimes called non-repeatable (nonrepeatable Read), because it runs the same query two times. May get a different result.

• REPEATABLE READ (repeatable Read): can read and conquer the problem of dirty reading repeatedly. This level guarantees that the result of reading the same record multiple times in the same transaction is consistent.

  But theoretically. The isolation level can be read over and over again cannot resolve another question. The so-called When a transaction reads a range of records, another transaction inserts a new record in that range when the previous transaction reads the record for that scope again. Generates Magic Line (Phantom row) can be read repeatedly mysql The default transaction isolation level

• Serializable (Serializable) : Serializable is the highest isolation level.

  It avoids the above-mentioned phantom-reading problem by forcing the transaction to run 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. This level is only considered when there is a need to ensure that the data is consistent and acceptable without concurrency.

?? for data storage in a table. The InnoDB storage engine usesGathering, so the storage for each table is stored in the order of the primary key. Assuming that a primary key is not 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:? ?? The MyISAM storage engine does not support transaction, table lock design, and supports full-text indexing. Mainly for some OLAP (on-Line Analytical Processing) database applications , the MyISAM storage engine and a different place is that its buffer pool just cache index files, not data files , MyISAM Storage engine table consists of myd and myi, MyD used to store data files, Myi used to hold the index file, before the mysql5.0 version number, MyISAM the default supported table size is 4gb,5.0 version number, myisam default support 256TB of single table data,
NDB Storage Engine:??? is a clustered storage engine. Its structure is share nothing of the cluster architecture.

Shared everthting: typically for a single host. Completely transparent sharing of cpu/memory/io, parallel processing power is the worst, typically representing SQL Server

Shared disk : Each processing unit uses its own private CPU and memory to share the system. Typically represents an Oracle RAC. It is a data sharing, can be added by adding nodes to improve the ability of parallel processing, the ability to expand better. It is similar to SMP (symmetric multi-processing) mode. However, when the memory interface is saturated, the addition of nodes will not achieve higher performance.

shared nothing: each processing unit has its own private cpu/memory/hard disk, etc., and no shared resources exist. Similar to the MPP (Massively parallel processing) pattern, the processing units communicate through protocol, parallel processing and expansion capabilities are better. Typical representative DB2 DPF and Hadoop, each node independent. Each process their own data. The processed results may be aggregated upward or flowed between nodes.

What do we always say? sharding is actually the Share Nothing architecture, which is to cut a table horizontally from the physical storage and assign it to multiple servers (or multiple instances), each of which can work independently, with a common Schema. For example, MySQL Proxy and Google 's various architectures, only need to add the number of servers to add 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 completed at the MySQL database layer, not at the storage engine level, which means that complex connection operations require significant network overhead. Therefore, the query is very slow.

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

??? to store the data in the table in memory, assuming the database restarts or crashes, the data in the table disappears and is ideal for temporary tables that store temporary data, by default using a hash index instead of a B + Tree index. Only table locks are supported. Concurrency is poor, and storing a variable-length field (varchar) is done in the same way as the constant field (char). Therefore, memory is wasted.

Archive Storage Engine:

? ? ? Only insert and select operations are supported. Supporting indexing from mysql5.1 is ideal for storing archived data, such as log information, designed to provide fast insertion and compression capabilities.

Federated Storage Engine:

The Storage engine table does not hold data. He is simply pointing to a table on a remote MySQL database server. Only support MySQL database table, not support heterogeneous database tables (heterogeneous database system is a collection of related multiple database systems, can realize the sharing of data and transparent access, each database system before joining the heterogeneous database system itself already exists, 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 field types in MySQL easy confusing, that is text and blob. In particular, 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 text and Blob is that the BLOB holds the binary data. Text saves character data. At the moment almost all the images in the blog content are not stored in binary database, but upload the image to the server and then use the tag reference in the text, this kind of blog can use the text type.

BLOBs are then able to convert the image into binary and save it to the database.

Ii. Types of differences

There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They are just the maximum lengths of the values that can be accommodated are different.

There are also 4 types of text: Tinytext, text, Mediumtext, and Longtext. These types are the same as BLOB types. Have the same maximum length and storage requirements.

Third, Character set

Blob columns do not have a character set, and are sorted and compared based on numeric values of 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, uppercase and lowercase

During the storage or retrieval of a text or BLOB column. There are no uppercase and lowercase conversions, all the same!

Five, Strict mode

When running in non-strict mode, assume that you assign a BLOB or text column A value that exceeds the maximum length of the column type. Values are intercepted to ensure suitability.

Suppose the truncated character is not a space. A warning will be generated. Using strict SQL mode generates an error. And the value will be 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, using a group by or order by for a BLOB or text column with a Long value is another way 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 is actually passed between the client and 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 the server and client programs must be changed at the same time.

In addition to the above storage engine, MySQL also has a lot of other storage engines. Contains merge, CSV, Sphinx, Infobright. They all have their own place of use,

MySQL database each storage engine comparison