The storage engine is the MYSQL component that handles SQL operations for different table types. InnoDB is the default, most versatile storage engine, and is also the official recommended storage engine, unless some specific cases are used. The storage engine for the table created by the CREATE TABLE statement in MYSQL 5.6 defaults to InnoDB. MYSQL server uses a pluggable storage engine architecture. It is capable of dynamically loading or unloading these storage engines at execution time. The ability to use the show ENGINES statement to view the storage engines supported by your MYSQL server.
The value of the support joins indicates whether an engine can be used by you: Yes, no, and default values, respectively, indicating that a storage engine is available, unavailable, available, and is the current default storage engine.
The storage engine supported by MYSQL 5.6
- InnoDB: MYSQL 5.6 Version number is the default storage engine. InnoDB is a transaction-safe storage engine that provides the ability to commit, rollback, and crash recovery to protect user data. InnoDB's row-level locking and Oracle-style consistency lock-free reads increase its number of multiuser concurrency and performance. InnoDB stores user data in a clustered index to reduce the I/O overhead of normal queries based on primary keys. To ensure the integrity of the data, InnoDB also supports foreign key constraints.
- MyISAM: Table-level locking limits its performance on read-write loads, so it is often applied to data scenarios that are read-only or read-only.
- Memory: Stores all of the data in RAM and applies to scenarios where non-critical data is found by high speed. The Memory engine was once called the HEAP engine.
Its use case is decreasing: the InnoDB memory buffer provides a common and durable way to keep most or all of the data in memory. Ndbcluster provides high-speed key-value access to large distributed datasets.
- CSV: Its table is really a comma-delimited text file. The CSV table agrees that you import the exported data in CSV format, with the same read and write format and script and application interaction data. Since the CSV table does not have an index, it is best to put the data in the InnoDB table in normal operation. Just use the CSV table in the import or export phase.
- Archive: Black hole storage engine. Unix-like/dev/null,archive only receive but do not save data. Queries on tables of such engines often return an empty set.
Such tables can be applied to DML statements that need to be sent from the server, but the primary server does not retain such data in the master-slave configuration of the backup.
- NDB: (aka Ndbcluster)--Such a cluster data engine is particularly suitable for applications that require the highest level of uptime and availability. Note: The NDB storage engine is not supported in the standard MYSQL 5.6 version number. The version numbers that can support MySQL cluster at the moment are: MySQL Cluster NDB 7.1 based on MySQL 5.1 and MySQL Cluster NDB 7.2 based on MySQL 5.5. MySQL Cluster NDB 7.3 based on MySQL 5.6. MySQL Cluster NDB 7.4, also based on MySQL 5.6, is now in the development phase.
- Merge: Agree with MySql DBA or developer to group a series of identical MyISAM tables and reference them as an object.
Ideal for hyper-scale data scenarios. such as data warehouses.
- Federated: provides the ability to join different MYSQL servers from multiple physical machines to create a logical database. Suitable for distributed or data-market scenarios.
- Example: This storage engine holds a sample of the MYSQL source code that illustrates how to start writing a new storage engine. It is intended for developers who are interested.
Such a storage engine is a "stub" that does nothing. You can use such an engine to create a table, but you can't save whatever data to it. It is also not possible to retrieve whatever index from them.
You are not constrained to use the same storage engine for the entire server or schema. You can define the storage engine for all tables. For example, an application might be dominated by a InnoDB table. A CSV table is used to export the data to the spreadsheet. A little MEMORY table is used for temporary work space.
Storage Engine selection These storage engines provided by MySQL are designed for different application cases. The following table provides an overview of the storage engine provided by MYSQL:
Storage Engine Feature summary
| features |
MyISAM |
Memory |
InnoDB |
Archive |
NDB |
| Storage caps |
NDB |
Ram |
64TB |
No |
384EB |
| Transaction support |
Whether |
Whether |
Is |
Whether |
Is |
| Lock particle size |
Table |
Table |
Yes |
Table |
Yes |
| MVCC |
Whether |
Whether |
Is |
Whether |
Whether |
| Geo-spatial data type support |
Is |
Whether |
Is |
Is |
Is |
| Geo-Spatial Index support |
Is |
Whether |
Is |
Whether |
Whether |
| B-tree Index |
Is |
Is |
Is |
Whether |
Whether |
| T-tree Index |
Whether |
Whether |
Whether |
Whether |
Is |
| Hash Index |
Whether |
Is |
Whether |
Whether |
Is |
| Full-Text Search index |
Is |
Whether |
Is |
Whether |
Whether |
| Clustered index |
Whether |
Whether |
Is |
Whether |
Whether |
| Data caching |
Whether |
N/A |
Is |
Whether |
Is |
| Index cache |
Is |
N/A |
Is |
Whether |
Is |
| Data compression |
Is |
Whether |
Is |
Is |
Whether |
| Data encryption |
Is |
Is |
Is |
Is |
Is |
| Support DB Cluster |
Whether |
Whether |
Whether |
Whether |
Is |
| Support Master-Slave |
Is |
Is |
Is |
Is |
Is |
| Support for foreign keys |
Whether |
Whether |
Is |
Whether |
Whether |
| Backup/Point-in-time recovery |
Is |
Is |
Is |
Is |
Is |
| Support for query caching |
Is |
Is |
Is |
Is |
Is |
| Update statistics for a data dictionary |
Is |
Is |
Is |
Is |
Is |
Attention
Original link: http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html.
Selection of MYSQL storage engine
