Selection of MYSQL storage engine

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. The MYSQL server uses a pluggable storage engine architecture that dynamically loads or uninstalls these storage engines while it is running. You can 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: The default storage engine for MYSQL version 5.6. 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: Row-level locking limits its performance on read-write loads, so it is often applied to read-only or read-only data scenarios.
• Memory: Store all of the data in the RAM, applied to the scene of the fast lookup of non-critical data. The Memory engine was once called the HEAP engine. Its use case is decreasing: InnoDB's memory buffers provide a common and durable way to keep most or all of the data in memory, Ndbcluster provides fast key-value access to large distributed datasets.
• CSV: Its table is really a comma-delimited text file. The CSV table allows you to import exported data in CSV format, with the same read and write format and script and app interaction data. Since the CSV table does not have an index, it is best to place the data in the InnoDB table in normal operations, using the CSV table only during the import or export phase.
• Archive: Black hole storage engine, Unix-like/dev/null,archive only receives but does not save data. Queries for tables of this engine often return an empty set. This table can be applied to DML statements that need to be sent to the slave server, but the master server does not retain the backup of such data in the master and subordinate configuration.
• NDB: (aka Ndbcluster)-this cluster data engine is especially 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 release. Currently capable of supporting MySQL cluster version is: MySQL 5.1 based MySQL Cluster NDB 7.1; MySQL 5.5-based MySQL Cluster NDB 7.2; MySQL 5.6-based MySQL Cluster NDB 7.3. MySQL Cluster NDB 7.4, also based on MySQL 5.6, is currently in the research and development phase.
• Merge: Allows a 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 is used to save examples of MYSQL source code that illustrates how to start writing a new storage engine. It is intended for developers who are interested. This storage engine is a "stub" that does nothing. You can use this engine to create tables, but you can't save any data to them, and you can't retrieve any indexes from them.

You are not limited to using 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 based on a InnoDB table, supplemented by a CSV table for exporting data to spreadsheets, and a little MEMORY table for temporary workspaces.

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

• InnoDB geo-spatial indexes are supported in MySQL 5.7.5 and later versions
• InnoDB Hash index optimizations built into the properties of its adaptive hash index
• InnoDB in MYSQL 5.6.4 and later versions to support Fulltext indexing
• MyISAM supports data compression only when the format is compressed in rows. MyISAM table read-only with row compression
• InnoDB table compression requires InnoDB Barracuda file format
• MYSQL data encryption is provided by the server's cryptographic function, not the storage engine
• Features such as master-slave support, backup/point-in-time recovery, etc. are also provided by the server, not the storage engine

Original link: http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html.

Selection of MYSQL storage engine