4.2 Storage Engines and Table Types

The MySQL server uses storage engines to manage data in tables. Each storage engine handles a particular table type. Each table type has differing characteristics and features; these are summarized in this section as an overview. Elsewhere, this study guide concentrates primarily on the MyISAM and InnoDB table types, which are also discussed in more detail in the "Professional Study Guide." For additional information on all table types, see the MySQL Reference Manual.

4.2.1 MyISAM Tables

The MyISAM storage engine manages tables that have the following characteristics:

• Each MyISAM table is represented on disk by an .frm format file, as well as an .MYD datafile and an .MYI index file. All these files are located in the database directory.

• MyISAM has the most flexible AUTO_INCREMENT column handling of all the table types.

• MyISAM tables can be used to set up MERGE tables.

• MyISAM tables can be converted into fast, compressed, read-only tables.

• MyISAM supports FULLTEXT searching.

• MySQL manages contention between queries for MyISAM table access using table-level locking. Query performance is very fast for retrievals. Multiple queries can read the same table simultaneously. For a write query, an exclusive table-level lock is used to prevent use of the table by other read or write queries, leading to reduced performance in environments with a mix of read and write queries. Deadlock cannot occur with table-level locking. (Deadlock occurs when two or more queries are blocked, or stopped from completing, because each is waiting for one of the others to finish.)

4.2.2 InnoDB Tables

The InnoDB storage engine manages tables that have the following characteristics:

• Each InnoDB table is represented on disk by an .frm format file in the database directory, as well as data and index storage in the InnoDB tablespace. The InnoDB tablespace is a logical single storage area that is made up of one or more files or partitions on disk. The tablespace is shared by all InnoDB tables.

• InnoDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance.

• InnoDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

• InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.

• MySQL manages query contention for InnoDB tables using multi-versioning and row-level locking. Multi-versioning gives each transaction its own view of the database. This, combined with row-level locking, keeps contention to a minimum. The result is good concurrency even in an environment consisting of mixed reads and writes. However, it's possible for deadlock to occur. Multi-versioning is discussed further in the "Professional Study Guide."

4.2.3 MERGE Tables

The MERGE storage engine manages tables that have the following characteristics:

• A MERGE table is a collection of identically structured MyISAM tables. Each MERGE table is represented on disk by an .frm format file and an .MRG file that lists the names of the constituent MyISAM files. Both files are located in the database directory.

• Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.

• A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.

4.2.4 BDB (Berkeley DB) Tables

The BDB storage engine manages tables that have the following characteristics:

• Each BDB table is represented on disk by an .frm format file and a .db file that stores data and index information. Both files are located in the database directory.

• BDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance.

• BDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

• MySQL manages query contention for BDB tables using page-level locking. This locking level provides concurrency performance that is intermediate to that of row-level and table-level locking. It's possible for deadlock to occur.

4.2.5 HEAP (MEMORY) Tables

The HEAP storage engine manages tables that have the following characteristics:

• Each HEAP table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.

• In-memory storage results in very fast performance.

• HEAP table contents do not survive a restart of the server. The table structure itself survives, but the table contains zero data rows after a restart.

• HEAP tables use up memory, so they should not be used for large tables.

• MySQL manages query contention for HEAP tables using table-level locking. Deadlock cannot occur.