Table of Contents
- 13.1. Comparing Transaction and Nontransaction Engines
- 13.2. Other Storage Engines
- 13.3. Setting the Storage Engine
- 13.4. Overview of MySQL Storage Engine Architecture
- 13.5. The
MyISAMStorage Engine - 13.6. The
InnoDBStorage Engine - 13.6.1. InnoDB as the Default MySQL Storage Engine
- 13.6.2. Configuring
InnoDB - 13.6.3. Using Per-Table Tablespaces
- 13.6.4.
InnoDBStartup Options and System Variables - 13.6.5. Creating and Using
InnoDBTables - 13.6.6. Adding, Removing, or Resizing
InnoDBData and Log Files - 13.6.7. Backing Up and Recovering an
InnoDBDatabase - 13.6.8. Moving an
InnoDBDatabase to Another Machine - 13.6.9. The
InnoDBTransaction Model and Locking - 13.6.10.
InnoDBMulti-Versioning - 13.6.11.
InnoDBTable and Index Structures - 13.6.12.
InnoDBDisk I/O and File Space Management - 13.6.13.
InnoDBError Handling - 13.6.14.
InnoDBPerformance Tuning and Troubleshooting - 13.6.15. Limits on
InnoDBTables
- 13.7. New Features of InnoDB 1.1
- 13.7.1. Introduction to InnoDB 1.1
- 13.7.2. Fast Index Creation in the InnoDB Storage Engine
- 13.7.3. InnoDB Data Compression
- 13.7.4. InnoDB File Format Management
- 13.7.5. Storage of Variable-Length Columns
- 13.7.6. InnoDB
INFORMATION_SCHEMAtables - 13.7.7. Performance and Scalability Enhancements
- 13.7.8. Changes for Flexibility, Ease of Use and Reliability
- 13.7.9. Installing the InnoDB Storage Engine
- 13.7.10. Upgrading the InnoDB Storage Engine
- 13.7.11. Downgrading the InnoDB Storage Engine
- 13.7.12. InnoDB Storage Engine Change History
- 13.7.13. Third-Party Software
- 13.7.14. List of Parameters Changed in InnoDB 1.1 and InnoDB Plugin 1.0
- 13.8. The
MERGEStorage Engine - 13.9. The
MEMORYStorage Engine - 13.10. The
EXAMPLEStorage Engine - 13.11. The
FEDERATEDStorage Engine - 13.12. The
ARCHIVEStorage Engine - 13.13. The
CSVStorage Engine - 13.14. The
BLACKHOLEStorage Engine
MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safe tables.
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 by using 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.
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
...
This chapter describes each of the MySQL storage engines except for
NDBCLUSTER, which is covered in
MySQL Cluster NDB 6.X/7.X. It also contains a
description of the pluggable storage engine architecture (see
Section 13.4, “Overview of MySQL Storage Engine Architecture”).
For information about storage engine support offered in commercial MySQL Server binaries, see MySQL Enterprise Server 5.1, 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.5 FAQ: Storage Engines”.
MySQL 5.5 supported storage engines
InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.InnoDBrow-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance.InnoDBstores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity,InnoDBalso supportsFOREIGN KEYreferential-integrity constraints.InnoDBis the default storage engine as of MySQL 5.5.5.MyISAM: The MySQL storage engine that is used the most in Web, data warehousing, and other application environments.MyISAMis supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.Memory: Stores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data. This engine was formerly known as theHEAPengine.Merge: Enables a MySQL DBA or developer to logically group a series of identicalMyISAMtables and reference them as one object. Good for VLDB environments such as data warehousing.Archive: Provides the perfect solution for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.Federated: 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.CSV: The CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.Blackhole: The Blackhole storage engine accepts but does not store data and retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.Example: The Example storage engine is “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.
It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.
Choosing a Storage Engine
The various storage engines provided with MySQL are designed with different use cases in mind. To use the pluggable storage architecture effectively, it is good to have an idea of the advantages and disadvantages of the various storage engines. The following table provides an overview of some storage engines provided with MySQL:
Table 13.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 | Row | 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 | Yes |
| Hash indexes | No | Yes | No | No | Yes |
| Full-text search indexes | Yes | No | No | 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[a] | No | Yes[b] | Yes | No |
| Encrypted data[c] | Yes | Yes | Yes | Yes | Yes |
| Cluster database support | No | No | No | No | Yes |
| Replication support[d] | Yes | Yes | Yes | Yes | Yes |
| Foreign key support | No | No | Yes | No | No |
| Backup / point-in-time recovery[e] | Yes | Yes | Yes | Yes | Yes |
| Query cache support | Yes | Yes | Yes | Yes | Yes |
| Update statistics for data dictionary | Yes | Yes | Yes | Yes | Yes |
[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. [b] Compressed InnoDB tables require the InnoDB Barracuda file format. [c] Implemented in the server (via encryption functions), rather than in the storage engine. [d] Implemented in the server, rather than in the storage product [e] Implemented in the server, rather than in the storage product | |||||