Chapter 14. Storage Engines

Table of Contents

14.1. Setting the Storage Engine
14.2. The InnoDB Storage Engine
14.2.1. Getting Started with InnoDB Tables
14.2.2. Administering InnoDB
14.2.3. InnoDB Concepts and Architecture
14.2.4. InnoDB Performance Tuning and Troubleshooting
14.2.5. InnoDB Features for Flexibility, Ease of Use andReliability
14.2.6. InnoDB Startup Options and System Variables
14.2.7. Limits on InnoDB Tables
14.2.8. MySQL and the ACID Model
14.2.9. InnoDB Integration with memcached
14.3. The MyISAM Storage Engine
14.3.1. MyISAM Startup Options
14.3.2. Space Needed for Keys
14.3.3. MyISAM Table Storage Formats
14.3.4. MyISAM Table Problems
14.4. The MEMORY Storage Engine
14.5. The CSV Storage Engine
14.5.1. Repairing and Checking CSV Tables
14.5.2. CSV Limitations
14.6. The ARCHIVE Storage Engine
14.7. The BLACKHOLE Storage Engine
14.8. The MERGE Storage Engine
14.8.1. MERGE Table Advantages and Disadvantages
14.8.2. MERGE Table Problems
14.9. The FEDERATED Storage Engine
14.9.1. FEDERATED Storage Engine Overview
14.9.2. How to Create FEDERATED Tables
14.9.3. FEDERATED Storage Engine Notes and Tips
14.9.4. FEDERATED Storage Engine Resources
14.10. The EXAMPLE Storage Engine
14.11. Other Storage Engines
14.12. Overview of MySQL Storage Engine Architecture
14.12.1. Pluggable Storage Engine Architecture
14.12.2. The Common Database Server Layer

Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The CREATE TABLE statement in MySQL 5.7 creates InnoDB tables by default.)

MySQL Server uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.

To determine which storage engines your server supports, use the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine.

This chapter primarily describes the features and performance characteristics of InnoDB tables. It also covers the use cases for the special-purpose MySQL storage engines, except for NDBCLUSTER which is covered in MySQL Cluster NDB 7.2. For advanced users, it also contains a description of the pluggable storage engine architecture (see Section 14.12, "Overview of MySQL Storage Engine Architecture").

For information about storage engine support offered in commercial MySQL Server binaries, see MySQL Enterprise Server 5.6, on the MySQL Web site. The storage engines available might depend on which edition of Enterprise Server you are using.

For answers to some commonly asked questions about MySQL storage engines, see Section B.2, "MySQL 5.7 FAQ: Storage Engines".

MySQL 5.7 Supported storage Engines

You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.

Choosing a Storage Engine

The various storage engines provided with MySQL are designed with different use cases in mind. The following table provides an overview of some storage engines provided with MySQL:

Table 14.1. Storage Engines Feature Summary

Feature MyISAM Memory InnoDB Archive NDB
Storage limits 256TB RAM 64TB None 384EB
Transactions No No Yes No Yes
Locking granularity Table Table Row Table Row
MVCC No No Yes No No
Geospatial data type support Yes No Yes Yes Yes
Geospatial indexing support Yes No No No No
B-tree indexes Yes Yes Yes No No
T-tree indexes No No No No Yes
Hash indexes No Yes No[a] No Yes
Full-text search indexes Yes No Yes[b] No No
Clustered indexes No No Yes No No
Data caches No N/A Yes No Yes
Index caches Yes N/A Yes No Yes
Compressed data Yes[c] No Yes[d] Yes No
Encrypted data[e] Yes Yes Yes Yes Yes
Cluster database support No No No No Yes
Replication support[f] Yes Yes Yes Yes Yes
Foreign key support No No Yes No No
Backup / point-in-time recovery[g] Yes Yes Yes Yes Yes
Query cache support Yes Yes Yes Yes Yes
Update statistics for data dictionary Yes Yes Yes Yes Yes

[a] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

[b] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

[c] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

[d] Compressed InnoDB tables require the InnoDB Barracuda file format.

[e] Implemented in the server (via encryption functions), rather than in the storage engine.

[f] Implemented in the server, rather than in the storage engine.

[g] Implemented in the server, rather than in the storage engine. - all specs in one place