Chapter 14 Storage Engines

Table of Contents

14.1 Setting the Storage Engine
14.2 Overview of MySQL Storage Engine Architecture
14.2.1 Pluggable Storage Engine Architecture
14.2.2 The Common Database Server Layer
14.3 The InnoDB Storage Engine
14.3.1 Introduction to InnoDB 1.1
14.3.2 Installing the InnoDB Storage Engine
14.3.3 Upgrading the InnoDB Storage Engine
14.3.4 Downgrading the InnoDB Storage Engine
14.3.5 InnoDB Concepts and Architecture
14.3.6 InnoDB Configuration
14.3.7 InnoDB Administration
14.3.8 InnoDB Tablespace Management
14.3.9 InnoDB Table Management
14.3.10 InnoDB Compressed Tables
14.3.11 InnoDB File-Format Management
14.3.12 InnoDB Row Storage and Row Formats
14.3.13 InnoDB Disk I/O and File Space Management
14.3.14 InnoDB Fast Index Creation
14.3.15 InnoDB Performance Tuning
14.3.16 InnoDB Startup Options and System Variables
14.3.17 InnoDB Backup and Recovery
14.3.18 InnoDB and MySQL Replication
14.3.19 InnoDB Troubleshooting
14.4 The MyISAM Storage Engine
14.4.1 MyISAM Startup Options
14.4.2 Space Needed for Keys
14.4.3 MyISAM Table Storage Formats
14.4.4 MyISAM Table Problems
14.5 The MEMORY Storage Engine
14.6 The CSV Storage Engine
14.6.1 Repairing and Checking CSV Tables
14.6.2 CSV Limitations
14.7 The ARCHIVE Storage Engine
14.8 The BLACKHOLE Storage Engine
14.9 The MERGE Storage Engine
14.9.1 MERGE Table Advantages and Disadvantages
14.9.2 MERGE Table Problems
14.10 The FEDERATED Storage Engine
14.10.1 FEDERATED Storage Engine Overview
14.10.2 How to Create FEDERATED Tables
14.10.3 FEDERATED Storage Engine Notes and Tips
14.10.4 FEDERATED Storage Engine Resources
14.11 The EXAMPLE Storage Engine
14.12 Other Storage Engines

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: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...

This chapter describes each of the MySQL storage engines except for NDBCLUSTER, which is covered in Chapter 17, MySQL Cluster NDB 7.2. It also contains a description of the pluggable storage engine architecture (see Section 14.2, “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

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 14.1 Storage Engines Feature Summary

FeatureMyISAMMemoryInnoDBArchiveNDB
Storage limits256TBRAM64TBNone384EB
TransactionsNoNoYesNoYes
Locking granularityTableTableRowTableRow
MVCCNoNoYesNoNo
Geospatial data type supportYesNoYesYesYes
Geospatial indexing supportYesNoNoNoNo
B-tree indexesYesYesYesNoNo
T-tree indexesNoNoNoNoYes
Hash indexesNoYesNo[a]NoYes
Full-text search indexesYesNoYes[b]NoNo
Clustered indexesNoNoYesNoNo
Data cachesNoN/AYesNoYes
Index cachesYesN/AYesNoYes
Compressed dataYes[c]NoYes[d]YesNo
Encrypted data[e]YesYesYesYesYes
Cluster database supportNoNoNoNoYes
Replication support[f]YesYesYesYesYes
Foreign key supportNoNoYesNoNo
Backup / point-in-time recovery[g]YesYesYesYesYes
Query cache supportYesYesYesYesYes
Update statistics for data dictionaryYesYesYesYesYes

[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.