Table of Contents [+/-]
InnoDBStorage Engine [+/-]
InnoDBConcepts and Architecture
InnoDBPerformance Tuning and Troubleshooting
InnoDBFeatures for Flexibility, Ease of Use and Reliability
InnoDBStartup Options and System Variables
MyISAMStorage Engine [+/-]
CSVStorage Engine [+/-]
MERGEStorage Engine [+/-]
FEDERATEDStorage Engine [+/-]
Storage engines are MySQL components that handle the SQL operations
for different table types.
the most general-purpose storage engine, and Oracle recommends using
it for tables except for specialized use cases. (The
CREATE TABLE statement in MySQL
InnoDB tables by
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
Support column indicates whether an engine
can be used. A value of
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
InnoDB tables. It also covers
the use cases for the special-purpose MySQL storage engines, except
NDB which is covered in
Chapter 17, MySQL Cluster NDB 7.3. 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.6 FAQ: Storage Engines”.
A transaction-safe (ACID compliant) storage engine for MySQL
that has commit, rollback, and crash-recovery capabilities to
protect user data.
InnoDB row-level locking
(without escalation to coarser granularity locks) and
Oracle-style consistent nonlocking reads increase multi-user
concurrency and performance.
user data in clustered indexes to reduce I/O for common queries
based on primary keys. To maintain data integrity,
InnoDB also supports
KEY referential-integrity constraints.
InnoDB is the default storage engine in MySQL
These tables have a small footprint.
limits the performance in read/write workloads, so it is often
used in read-only or read-mostly workloads in Web and data
Stores all data in RAM, for fast access in environments that
require quick lookups of non-critical data. This engine was
formerly known as the
HEAP engine. Its use
cases are decreasing;
InnoDB with its buffer
pool memory area provides a general-purpose and durable way to
keep most or all data in memory, and
NDBCLUSTER provides fast key-value lookups
for huge distributed data sets.
Its tables are really text files with comma-separated values.
CSV tables let you import or dump data in CSV format, to
exchange data with scripts and applications that read and write
that same format. Because CSV tables are not indexed, you
typically keep the data in
during normal operation, and only use CSV tables during the
import or export stage.
These compact, unindexed tables are intended for storing and
retrieving large amounts of seldom-referenced historical,
archived, or security audit information.
The Blackhole storage engine accepts but does not store data,
similar to the Unix
/dev/null device. Queries
always return an empty set. These tables can be used in
replication configurations where DML statements are sent to
slave servers, but the master server does not keep its own copy
of the data.
NDB storage engine is not
supported in standard MySQL 5.6 releases.
Currently supported MySQL Cluster releases include MySQL
Cluster NDB 7.0 and MySQL Cluster NDB 7.1, which are based on
MySQL 5.1; MySQL Cluster NDB 7.2, which is based on MySQL 5.5;
and MySQL Cluster NDB 7.3, which is based on MySQL 5.5. While
based on MySQL Server, these releases also contain support for
Enables a MySQL DBA or developer to logically group a series of
MyISAM tables and reference them as
one object. Good for VLDB environments such as data warehousing.
Offers the ability to link separate MySQL servers to create one
logical database from many physical servers. Very good for
distributed or data mart environments.
This engine serves as an example in the MySQL source code that
illustrates how to begin writing new storage engines. It is
primarily of interest to developers. The storage engine is a
“stub” that does nothing. You can create tables
with this engine, but no data can be stored in them or retrieved
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
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
|Geospatial data type support||Yes||No||Yes||Yes||Yes|
|Geospatial indexing support||Yes||No||No||No||No|
|Full-text search indexes||Yes||No||Yes[b]||No||No|
|Cluster database support||No||No||No||No||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.