MySQL Storage Engines

3 Tutorial

13.1. The MyISAM Storage Engine     [+/-]

13.1.1. MyISAM Startup Options

13.1.2. Space Needed for Keys

13.1.3. MyISAM Table Storage Formats

13.1.4. MyISAM Table Problems

13.2. The InnoDB Storage Engine     [+/-]

13.2.1. InnoDB Contact Information

13.2.2. InnoDB Configuration

13.2.3. InnoDB Startup Options and System Variables

13.2.4. Creating and Using InnoDB Tables

13.2.5. Adding, Removing, or Resizing InnoDB Data and Log Files

13.2.6. Backing Up and Recovering an InnoDB Database

13.2.7. Moving an InnoDB Database to Another Machine

13.2.8. The InnoDB Transaction Model and Locking

13.2.9. InnoDB Multi-Versioning

13.2.10. InnoDB Table and Index Structures

13.2.11. InnoDB Disk I/O and File Space Management

13.2.12. InnoDB Error Handling

13.2.13. InnoDB Performance Tuning and Troubleshooting

13.2.14. Restrictions on InnoDB Tables

13.3. The MERGE Storage Engine     [+/-]

13.3.1. MERGE Table Advantages and Disadvantages

13.3.2. MERGE Table Problems

13.4. The MEMORY (HEAP) Storage Engine

13.5. The BDB (BerkeleyDB) Storage Engine     [+/-]

13.5.1. Operating Systems Supported by BDB

13.5.2. Installing BDB

13.5.3. BDB Startup Options

13.5.4. Characteristics of BDB Tables

13.5.5. Restrictions on BDB Tables

13.5.6. Errors That May Occur When Using BDB Tables

13.6. The EXAMPLE Storage Engine

13.7. The FEDERATED Storage Engine     [+/-]

13.7.1. Description of the FEDERATED Storage Engine

13.7.2. How to Use FEDERATED Tables

13.7.3. Limitations of the FEDERATED Storage Engine

13.8. The ARCHIVE Storage Engine

13.9. The CSV Storage Engine

13.10. The BLACKHOLE Storage Engine

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: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
 Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
 Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
...

This chapter describes each of the MySQL storage engines except for NDBCLUSTER, which is covered in Chapter 17, MySQL Cluster.

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.0 FAQ: Storage Engines”.

When you create a new table, you can specify which storage engine to use by adding an ENGINE or TYPE table option to the CREATE TABLE statement:

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

If you omit the ENGINE or TYPE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the my.cnf configuration file.

You can set the default storage engine to be used during the current session by setting the storage_engine or table_type variable:

SET storage_engine=MYISAM;
SET table_type=BDB;

When MySQL is installed on Windows using the MySQL Configuration Wizard, the InnoDB or MyISAM storage engine can be selected as the default. See Section 2.10.3.5, “The Database Usage Dialog”.

To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:

ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

See Section 12.1.10, “CREATE TABLE Syntax”, and Section 12.1.4, “ALTER TABLE Syntax”.

If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table using the default storage engine. This behavior is convenient when you want to copy tables between MySQL servers that support different storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only nontransactional storage engines for greater speed.)

This automatic substitution of the default storage engine for unavailable engines can be confusing for new MySQL users. A warning is generated whenever a storage engine is automatically changed.

For new tables, MySQL always creates an .frm file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the storage engine. The server creates the .frm file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.

A database may contain tables of different types. That is, tables need not all be created with the same storage engine.

Transaction-safe tables (TSTs) have several advantages over nontransaction-safe tables (NTSTs):

• They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
• You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).
• You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
• If an update fails, all of your changes are reverted. (With nontransaction-safe tables, all changes that have taken place are permanent.)
• Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

You can combine transaction-safe and nontransaction-safe tables in the same statements to get the best of both worlds. However, although MySQL supports several transaction-safe storage engines, for best results, you should not mix different storage engines within a transaction with autocommit disabled. For example, if you do this, changes to nontransaction-safe tables still are committed immediately and cannot be rolled back. For information about this and other problems that can occur in transactions that use mixed storage engines, see Section 12.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

Nontransaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:

• Much faster
• Lower disk space requirements
• Less memory required to perform updates