MySQL supports multiple storage engines

The strength of MySQL is its plug-in storage engine, which allows us to use different storage engines based on table features to achieve the best performance.

MySQL has a variety of storage engines: 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. Just like MyISAM, the memory and merge storage engines process non-transactional tables,
Both of these engines are also 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,
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 an example in the MySQL source code,
It shows 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 a future distribution edition,
We want to connect it 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?

InnoDB Storage Engine
Characteristics:
1, supporting transactions, primarily for online transaction processing (OLTP) applications.
2, row lock design, support foreign key, and support orcle non-lock read, that is, by default, read operation is not locked.
3, third-party storage engine, acquired by Orcle.
4,windows version default storage engine, other system MySQL default storage engine is MyISAM.
Design concept:
1,innodb By default puts the data into a logical tablespace, which is managed by the InnoDB itself like a black box. Starting with mysql4.1, it can store the tables of each INNODB storage engine in a separate IBD file.
Similar to orcle, the InnoDB storage engine can also use a bare device (row disk) to establish its tablespace.
2,innodb obtains high concurrency by using multi-version concurrency control (MVCC) and implements 4 isolation levels for the SQL standard, which defaults to the repeatable level.
Also use a next-key-locking strategy to avoid Phantom reads (Phantom). In addition to this, the InnoDB storage engine also provides insert buffer,
Two writes (double write), Adaptive Hash Index (Adaptive hash indexes), pre-read (read ahead), and other high performance and highly available features.
MyISAM Storage Engine
Characteristics:
1, does not support transaction, table lock (table-level lock, lock will lock the entire table), support full-text indexing, for some OLAP (online analytical processing, on-line analysis processing) Operation Fast
2, the official storage engine, in addition to the Windows version, is the default storage engine for all MySQL versions.
Design concept:
The 1,myisam Storage engine table consists of myd and myi. MyD is used to store data files, Myi is used to store index files, you can further compress the data file through the Myisampack tool, because the tool uses Huffman coded static algorithm to compress data.
As a result, tables that are compressed with the tool are read-only and, of course, can be decompressed by Myisampack.
Before 2,mysql5.0, the MyISAM default supported table size is 4g, and if you need to support MyISAM tables larger than 4g, you need to develop max_rows and Avg_row_length properties.
Starting with the mysql5.0 version, MyISAM supports 256T of single-table data by default, enough to meet the needs of general applications.
3, for the MyISAM Storage engine table, the MySQL database only caches its index files, and the cache of the data files is done by the operating system itself. This is very different from most of the other databases that use the LRU algorithm to cache data.
In addition, prior to the mysql5.1.23 version, the cache index can only be set to 4g in a 32b or 64b operating system environment, and in later versions, the 64-bit system could support an index buffer greater than 4g.
NDB Storage Engine
The NDB storage engine is a clustered storage engine, similar to a orcle RAC cluster, but unlike the RAC share everything structure, its structure is the share nothing cluster structure, thus providing a higher level of high availability.
Characteristics:
1, the data is all in memory, starting with the 5.1 version, the non-indexed data can be placed on disk, so the primary key lookup is very fast, and by adding the NDB data storage engine node can be linearly improved database performance,
is a high-availability, high-performance cluster system.
The connection operation (join) of the 2,NDB storage engine is done at the MySQL database layer, not at the storage engine level, which means that complex connection operations require significant network overhead, so queries are slow, which is a NDB bottleneck.
Memory Storage Engine
The Memory storage Engine (formerly known as the heap storage engine) stores the data in the table and, if the database restarts or crashes, the data in the table disappears. It is ideal for staging tables that hold temporary data, and for the latitude table of the Data Warehouse, by default using a hash index instead of a B + Tree index.
Usage restrictions: The memory storage engine supports only table locks, poor concurrency performance, and does not support text and BLOB column types. Most importantly, storing the variable length field (varchar) is stored in a constant field, resulting in memory wastage.
In addition, the MySQL database uses a staging table to hold the query's intermediate result set (intermediate result). If the intermediate result set is larger than the capacity setting for the Memory storage engine table, or if the intermediate result set contains text or BLOB fields, the MySQL database converts it to the MyISAM storage engine table and onto the disk.
Because MyISAM does not cache data files, the performance of the temporary tables that are generated at this time is a loss for the query.
Archive Storage Engine
Characteristics:
The 1,archive storage engine supports only insert and select operations, and mysql5.1 begins to support indexing. Use the zlib algorithm to compress data rows and store them, with compression ratios typically up to 1:10
The 2,archive storage engine is ideal for storing archived data, such as log information.
The 3,archive storage engine uses row locks for high-concurrency inserts. Because it is not a transaction-safe storage engine, it is designed primarily to provide high-speed insertion and compression capabilities.
Maria Storage Engine
The Maria storage Engine is the newly developed engine, designed primarily to replace the original MyISAM storage engine, and thus become the default storage engine for MySQL.
Characteristics:
1, cache data and index files, row lock design, provide MVCC functionality, support for transactional and non-transactional security options, and better processing performance for BLOB field types.

MySQL supports multiple storage engines