Comparison of various storage engines of mysql Databases

Comparison of various storage engines of mysql Databases
One of the most important features of a mysql database that distinguishes it from other databases is its plug-in Table storage engine, which is table-based rather than the InnoDB Storage engine. It supports transactions, it is designed mainly for online transaction processing (OLTP) applications. It features row lock design, support for foreign keys, and support for non-locked reads similar to oracle, that is, by default, read operations do not produce locks, and data is stored in a logical tablespace. In addition, the InnoDB Storage engine supports creating tablespaces using bare devices, the so-called bare device is a special block device file that is not formatted and read by Unix through the file system. It is not directly managed by the operating system. This type of device is less than the operating system layer, and the I/O efficiency is higher. InnoDB also provides high-performance and high-availability functions such as insert buffer, double write, adaptive hash index, and read ahead.
InnoDB uses multi-version concurrency control to achieve high concurrency, and implements four isolation levels of the SQL standard, namely:

• Uncommitted Read (Read uncommitted): Modifications in a transaction at the uncommitted Read level are visible to other transactions even if they are not committed. Transactions can Read uncommitted data, which is also known as Dirty Read ). This level will cause many problems. In terms of performance, uncommitted read is not much better than other levels, but it lacks many advantages of other levels and is rarely used in practical applications.
• Read committed: the default isolation level of most database systems is committed Read (but Mysql is not ). Commit read meets the simple definition of isolation mentioned above: at the beginning of a transaction, you can only "see" the modifications made by the committed transaction. In other words, any modifications made to a transaction from the beginning to before commit are invisible to other transactions. This level is sometimes called nonrepeatable read. Because the same query is executed twice, different results may be obtained.
• Repeatable read: it solves the dirty read problem. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. However, theoretically, the level of Repeatable read isolation still cannot solve another Phantom read problem. The so-called phantom read refers to when a transaction reads records within a certain range, another transaction inserts a new record in this range, A Phantom row is generated when the previous transaction reads records in this range again ). Deduplication is the default transaction isolation level of MySQL.
• Serializable: Serializable is the highest isolation level. It forces transaction serial execution to avoid the phantom read problem mentioned above. In short, serializable locks are applied to each row of data read, which may cause a large number of timeout and lock contention problems. This isolation level is rarely used in practical applications. This level can be considered only when data consistency is required and no concurrency is acceptable.
  The InnoDB Storage engine aggregates data in tables. Therefore, each table is stored in the primary key sequence. If no primary key is explicitly specified during table definition, the InnoDB Storage engine generates a 6-byte ROWID for each row and uses it as the primary key,
  MyISAM storage engine: The MyISAM storage engine does not support transactions and table lock design. It supports full-text indexing and is mainly applicable to some OLAP (online analysis and processing) database applications, another difference of the MyISAM storage engine is that its buffer pool only caches index files rather than data files. MyISAM storage engine tables are composed of MYD and MYI, and MYD is used to store data files, MYI is used to store index files. Before MySQL 5.0, MyISAM supports a table of 4 GB by default and starts from mysql. MyISAM supports TB of single table data by default,
  NDB storage engine: A Cluster Storage engine with a share nothing cluster architecture,

  Shared Everthting:Generally, it is designed for a single host and fully transparent shared CPU, MEMORY, and IO. The parallel processing capability is the worst. A typical example is SQLServer.

  Shared Disk:Each processing unit uses its own private CPU and Memory to share the disk system. A typical example is Oracle Rac, Which is data sharing. It can improve the parallel processing capability by adding nodes and better scalability. It is similar to SMP (Symmetric Multi-processing) mode, but when the memory interface reaches saturation, adding nodes does not achieve higher performance.

  Shared Nothing:Each processing unit has its own private CPU/memory/hard disk, and does not share resources. Similar to MPP (large-scale parallel processing) mode, each processing unit uses protocol communication, parallel processing and scalability are better. Typical examples are DB2 DPF and hadoop. Each node is independent of each other and processes its own data. The processed results may be summarized at the upper layer or transferred between nodes.

  We often say that Sharding is actually a Share Nothing architecture. It splits a table horizontally from physical storage and distributes it to multiple servers (or instances ), each server can work independently and have a common schema, such as MySQL Proxy and Google architectures. by increasing the number of servers, you can increase the processing capability and capacity.

  Therefore, it provides higher availability. NDB features that all data is stored in the memory. Therefore, the primary key search speed is extremely fast, and the database performance can be linearly improved by adding NDB data storage nodes, is a highly available cluster system. However, the link operation of the NDB storage engine is completed at the mysql database layer, rather than at the storage engine layer. This means that complicated connection operations require a huge amount of network overhead, so the query speed is very slow.

  Memory storage engine (formerly known as heap Storage engine ):

  Store data in the table in the memory. If the database restarts or crashes, the data in the table will disappear. This is very suitable for temporary tables that store temporary data. By default, hash indexes are used, instead of B + tree indexes. Only table locks are supported, and the concurrency performance is poor. The storage of variable-length fields (varchar) is based on the constant field (char), which wastes memory.

  Archive storage engine:

  Only insert and select operations are supported, and indexes are supported since mysql5.1. It is very suitable for storing archived data, such as log information. Its design goal is to provide high-speed insertion and compression functions.

  Federated storage engine:

  The storage engine table does not store data. It only points to a table on a remote mysql database server and only supports mysql database tables, heterogeneous database tables are not supported (the heterogeneous database system is a collection of multiple related database systems, allowing for data sharing and transparent access, each database system exists before it is added to a heterogeneous database system and has its own DBMS .)

  Maria storage engine:

  The design goal is to replace the original mysql storage engine. It supports caching data and index files, applies the row lock design, and provides the mvcc (Multi-version Concurrency Control) function, supports Security Options for transactions and non-transactions, as well as better processing performance for blob character types,

  There are two Field Types in MySQL that are confusing, namely TEXT and BLOB. In particular, the bloggers who write their own blog programs do not know whether to change their blog body fields to TEXT or BLOB.

  The following are some differences:

  I. Main differences

  The main difference between TEXT and BLOB is that BLOB stores binary data and TEXT stores character data. Currently, images in almost all Blog content are not stored in the database in binary format. Instead, they are uploaded to the server and then referenced using tags in the body, so that the blog can use the TEXT type. BLOB can convert the image into binary and save it to the data library.

  Ii. Differences of Types

  BLOB has four types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They only have different maximum lengths that can hold values.

  There are also four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These types have the same maximum length and storage requirements as BLOB types.

  Iii. Character Set

  BLOB columns do not have character sets, and sort and compare value values based on column value bytes. The TEXT column has a character set and sorts and compares values according to the character set's checking rules.

  Iv. Case sensitivity

  There is no case conversion during TEXT or BLOB column storage or retrieval. They are all the same!

  V. Strict Mode

  When running in non-strict mode, if you assign a value that exceeds the maximum length of the column type to the BLOB or TEXT column, the value is truncated to ensure the fit. If the truncated characters are not spaces, a warning is generated. When strict SQL mode is used, errors are generated, and the value is denied, rather than truncated, and a warning is given.

  Vi. Others

  When you save or retrieve the values of BLOB and TEXT columns, trailing spaces are not deleted.

  The index prefix length must be specified for index of BLOB and TEXT columns.

  BLOB and TEXT Columns cannot have default values.

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

  When you want to make the byte that exceeds max_sort_length meaningful, another way to use group by or order by for long BLOB or TEXT columns is to convert column values to fixed-length objects. 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 actual maximum value that can be passed between the client and the server is determined by the amount of available memory and the size of the Communication cache. You can change the size of the message cache by changing the value of the max_allowed_packet variable, but you must modify both the server and client programs.

  In addition to the above storage engines, mysql also has many other storage engines, including merge, csv, sphashes, and infobright. They all have their own places of use,