MySql storage engine Selection
The storage engine is a MySql component that processes SQL operations for different table types. InnoDB is the default and most common storage engine and is also officially recommended unless in some specific cases. The storage engine of the TABLE created by the create table statement in MySql 5.6 is InnoDB by default. The MySql server uses a pluggable storage engine architecture that can dynamically load or uninstall these storage engines during running. You can use the show engines statement to view the storage ENGINES supported by your MySql server. The value in the Support column indicates whether an engine can be used by you: YES, NO, and DEFAULT indicate that a storage engine is available, unavailable, available, and the current DEFAULT storage engine.
Storage engine supported by MySql 5.6
- InnoDB: Default storage engine of MySql 5.6. InnoDB is a transaction-safe storage engine that provides the capabilities of commit, rollback, and crash recovery to protect user data. Row-level locking and Oracle-style consistency of InnoDB without locks improve the concurrency and performance of multiple users. InnoDB stores user data in clustered indexes to reduce the I/O overhead of common queries based on primary keys. To ensure data integrity, InnoDB also supports foreign key constraints.
- MyISAMRow-level locking limits its reading and writing load performance. Therefore, it is often used in read-only or read-based data scenarios.
- Memory: Stores all data in the memory and applies it to scenarios where non-critical data is quickly searched. The Memory engine is called the HEAP engine. Its use cases are decreasing: InnoDB's memory buffer provides a universal and durable way to keep most or all of the data in the memory, NDBCLUSTER provides quick key-value access for large distributed datasets.
- CSV: Its table is really a text file separated by commas. CSV tables allow you to import and export data in CSV format, and use the same read and write formats and interactive data between scripts and applications. Because the CSV table does not have an index, you 'd better place the data in the InnoDB table in normal operations. You can only use the CSV table during the import or export phase.
- Archive: Black Hole storage engine, similar to Unix/dev/null, Archive only receives but does not save data. A blank set is often returned for queries on tables of this engine. This type of table can be applied to DML statements that need to be sent to the slave server, but the master server does not keep the master-slave configuration of the backup of this data.
- NDB(Also known as NDBCLUSTER)-This cluster Data Engine is especially suitable for applications that require the highest degree of normal running time and availability. Note: The NDB storage engine is not supported in standard MySql 5.6. Currently, MySql clusters are supported in the following versions: MySql Cluster NDB 5.1 Based on MySQL 7.1, MySql Cluster NDB 5.5 Based on MySQL 7.2, and MySql Cluster NDB 5.6 Based on MySQL 7.3. MySql Cluster NDB 5.6, which is also based on MySQL 7.4, is currently under development.
- Merge: Allows MySql DBAs or developers to group A series of identical MyISAM tables and reference them as an object. Suitable for ultra-large-scale data scenarios, such as data warehouses.
- Federated: Provides the ability to connect different MySql servers from multiple physical machines to create a logical database. Applicable to distributed or data market scenarios.
- Example: This storage engine is used to save an example of how to start writing MySql source code for the new storage engine. It is mainly intended for interested developers. Such a storage engine is nothing but a "stub ". You can use this engine to create tables, but you cannot store any data or retrieve any indexes from them.
For the entire server or the entire schema, you are not limited to using the same storage engine. You can define storage engines for all tables. For example, an application may use an InnoDB table and a CSV table to export data to a workbook. A few MEMORY tables are used for temporary workspace.
Storage engine selection MySql provides these storage engines for different application cases. The following table provides an overview of the storage engine provided by MySql:
Summary of storage Engine Features
| Features |
MyISAM |
Memory |
InnoDB |
Archive |
NDB |
| Storage Limit |
NDB |
RAM |
64 TB |
None |
384EB |
| Transaction support |
No |
No |
Yes |
No |
Yes |
| Lock Granularity |
Table |
Table |
Line |
Table |
Line |
| MVCC |
No |
No |
Yes |
No |
No |
| Supported Geospatial Data Types |
Yes |
No |
Yes |
Yes |
Yes |
| Geospatial index support |
Yes |
No |
Yes |
No |
No |
| B-tree Index |
Yes |
Yes |
Yes |
No |
No |
| T-tree Index |
No |
No |
No |
No |
Yes |
| Hash Index |
No |
Yes |
No |
No |
Yes |
| Full-text search index |
Yes |
No |
Yes |
No |
No |
| Clustered Index |
No |
No |
Yes |
No |
No |
| Data Cache |
No |
N/ |
Yes |
No |
Yes |
| Index Cache |
Yes |
N/ |
Yes |
No |
Yes |
| Data Compression |
Yes |
No |
Yes |
Yes |
No |
| Data Encryption |
Yes |
Yes |
Yes |
Yes |
Yes |
| Supports database Clusters |
No |
No |
No |
No |
Yes |
| Support Master/Slave |
Yes |
Yes |
Yes |
Yes |
Yes |
| Supports Foreign keys |
No |
No |
Yes |
No |
No |
| Backup/time point recovery |
Yes |
Yes |
Yes |
Yes |
Yes |
| Query cache supported |
Yes |
Yes |
Yes |
Yes |
Yes |
| Update Data Dictionary statistics |
Yes |
Yes |
Yes |
Yes |
Yes |
Note:
- InnoDB supports geospatial Indexing in MySQL 5.7.5 and later versions.
- InnoDB provides built-in hash index Optimization Based on its adaptive hash index feature.
- InnoDB supports FULLTEXT indexes in MySql 5.6.4 and later versions.
- MyISAM only supports data compression in row compression format. MyISAM table read-only with row Compression
- InnoDB table compression requires the InnoDB Barracuda File Format
- MySql Data Encryption is provided by server encryption functions, not a storage engine
- Master/Slave support, backup/time point recovery, and other functions are also provided by the server, rather than the storage engine.
Original article: http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html.
