Various storage engines in MySQL database

Source: Various storage engines in MySQL database

MySQL has a variety of storage engines, each storage engine has its own advantages and disadvantages, you can choose the preferred use :

MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole.

MySQL supports several storage engines as a processor for different types of tables. The MySQL storage engine includes the engine that handles the transaction security table and the engine that handles the non-transactional security tables:

· MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations, it is the default storage engine, unless you configure MySQL by default using a different engine.

· The memory storage Engine provides an "in-store" table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, the memory and merge storage engines handle non-transactional tables, both of which are included by default in MySQL.

Note: The memory storage engine is formally identified as the heap engine.

· The InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in the Mysql-max binary distribution released for the operating system that supports it. InnoDB is also included by default in all MySQL 5.1 binary distributions, and you can configure MySQL to allow or disallow any engine as you prefer.
· The example storage engine is a "stub" engine that does nothing. You can use this engine to create a table, but no data is stored in it or retrieved from it. The purpose of this engine is to serve as an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Similarly, its main interest is to developers.

· NDB cluster is a storage engine that is used by MySQL cluster to implement tables that are partitioned into multiple computers. It is available in the Mysql-max 5.1 binary distribution. This storage engine is currently supported only by Linux, Solaris, and Mac OS X. In a future MySQL distribution, we want to add support for this engine from other platforms, including Windows.

· The archive storage engine is used to easily overwrite a large amount of data that is stored in a non-indexed manner.

· The CSV storage engine stores data in a comma-delimited format in a text file.

· The Blackhole storage engine accepts but does not store data, and the retrieval always returns an empty set.

· The federated storage Engine has data in the remote database. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In future distributions, we want to have it connect to another data source using a different drive or client connection method.

When you create a new table, you can tell MySQL what type of table you want to create by adding an engine or type option to the CREATE TABLE statement:

CREATE TABLE T (i INT) ENGINE = INNODB;

CREATE TABLE T (i INT) TYPE = MEMORY;

Although the type is still supported in MySQL 5.1, the engine is now the preferred term.

How do you choose the storage engine that works best for you?

The following storage engines are most commonly used:

· MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.

· InnoDB: For transactional applications, with many features, including acid transaction support. (Row-level lock available)

· BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

· Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

· Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

· Archive: Provides the perfect solution for storing and retrieving large amounts of historically, archived, or security audit information that is rarely referenced.

· Federated: The ability to link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

· Cluster/ndb:mysql's clustered database engine, especially suitable for applications with high performance lookup requirements, also requires the highest uptime and availability.

· Other: The other storage engines include CSV (referencing a comma-delimited file used as a database table), blackhole (for temporary suppression of application input to the database), and the example engine, which can help with the quick creation of a custom plug-in storage engine.

The following is from: MySQL Database engine detailed

The difference between each storage engine

To decide which storage engine to choose, we first need to consider which of the different core capabilities each storage engine provides. This feature allows us to differentiate between different storage engines. We generally divide these core functions into four categories: supported fields and data types, lock types, indexes, and processing. Some engines have unique features that can lead you to make decisions, and we'll look at these specific issues in a moment.

　　fields and data types

While all of these engines support common data types such as Integer, Real, and character, not all engines support other field types, especially blogs (binary large objects) or text text types. Other engines may only support a limited number of character widths and data sizes.

These limitations can directly affect the data you can store, and may have an indirect effect on the type of search you implement or the indexes you create for that information. These differences can affect the performance and functionality of your application because you have to choose the capabilities of the storage engine that you want to store based on the type of data you are storing.

　　Lock

The locking feature in the database engine determines how information access and updates are managed. When an object in the database is locked for information updates, other processing cannot modify the data (which in some cases is not allowed to read) until the update is complete.

Locking affects not only how many different applications update the information in the database, but also the query for that data. This is because the query may be accessing data that is being modified or updated. In general, this delay is very small. Most locking mechanisms are primarily designed to prevent multiple processes from updating the same data. Because of the need to lock in both information and update information, you can imagine that multiple applications can have a significant impact on the same database.

　　Different storage engines support locking at different object levels, and these levels affect information that can be accessed at the same time. There are three levels of support: Table lock, Block lock, and row lock. The most supported are table locks, which are provided in MyISAM. When the data is updated, it locks the entire table. This prevents many applications from updating a specific table at the same time. This has a big impact on applying many multiuser databases because it delays the process of updating.

Page-level locking uses the Berkeley DB Engine and locks data based on the uploaded information page (8KB). There is no problem with this lock when updates are made in many parts of the database. However, with the addition of a few lines of information to lock the last 8KB of the data structure, when the need to add a large number of rows, especially a large number of small data, will cause problems.

Row-level locking provides the best parallel access capability, with only one row of data locked in a table. This means that many applications can update data from different rows in the same table without causing locking problems. Only the InnoDB storage engine supports row-level locking.

　　Build an index

Indexing can significantly improve performance when searching and recovering data in a database. Different storage engines provide different techniques for indexing. Some techniques may be better suited to the type of data you store.

Some storage engines do not support indexing at all, either because they use a base table index (such as the merge engine) or because the data is stored in a way that does not allow indexing (such as federated or Blackhole engines).

　　Transaction processing

Transactional functionality provides reliability by providing the ability to update and insert information into a table. This reliability is achieved by allowing you to update the data in the table, but only accept your changes to the table when all relevant operations for the application are complete. For example, in accounting processing, each accounting entry processing will include changes to the debit and credit account data, and you will need to use the transactional functionality to ensure that the data changes to the debit and credit accounts are completed successfully before you accept the modifications. If any of the operations fail, you can cancel the transaction and the modifications will not exist. If the transaction process is complete, we can confirm the operation by allowing this modification.

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.
1. MyISAM
MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, but its access (read) speed is fast and there is no requirement for transactional integrity.
MyISAM In addition to providing a large number of functions for index and field management not available in ISAM, MyISAM also uses a table locking mechanism to optimize multiple concurrent read and write operations, at the cost of running the Optimize Table command frequently to restore space wasted by the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most web hosting providers and internet platform providers only allow the use of the MyISAM format. An important flaw in the MyISAM format is the inability to recover data after a table is corrupted.
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.

2.memory/heap
The memory (also called heap) storage engine creates a table using the contents of the existing content. 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 heap allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutting down, all the data will be lost. The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression.
Memory is primarily used for code tables where the content changes infrequently, or as an intermediate result table for statistical operations, which facilitates efficient analysis of the heap intermediate results and final statistical results.

3. MERGE
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 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.


The difference between MyISAM and InnoDB
InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

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.

Two types of InnoDB storage: 1 Use shared tablespace storage 2 using multi-table spaces

Various storage engines in MySQL database