Chapter 15. InnoDB Tables

This chapter describes the InnoDB table type that is supported by the InnoDB storage engine. It covers the following topics:

• The InnoDB tablespace and log files

• InnoDB support for transactions

• Optimizations that apply to working with InnoDB tables

• How the InnoDB storage engine handles locking

• Backup and recovery options for InnoDB tables

• How to check and repair InnoDB tables

• InnoDB maintenance issues, such as how to configure the tablespace

Questions on the material in this chapter make up approximately 10% of the exam.

The InnoDB storage engine is one of the table handlers that MySQL provides. InnoDB was introduced to MySQL during the 3.23 release series and is a standard feature in binary distributions as of MySQL 4. It has several notable features:

• InnoDB supports transactions, with commit and rollback. It provides full ACID (atomicity, consistency, isolation, durability) compliance. Multi-versioning is used to isolate transactions from one another. If the MySQL server or the server host crashes, InnoDB performs automatic crash recovery.

• InnoDB manages contention for tables using multi-versioning and row-level locking. This provides good query concurrency even when clients are performing a mix of reads and writes.

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

• Each InnoDB table is represented on disk by an .frm format file in the database directory, along with data and index information stored in a tablespace that is shared among InnoDB tables. The tablespace is stored in machine-independent format. It is implemented such that table sizes can exceed the maximum file size allowed by the filesystem.

To create an InnoDB table, nothing more is necessary than to add a TYPE option to the end of the CREATE TABLE statement:

CREATE TABLE t (i INT) TYPE = InnoDB;

To convert an existing table from some other type to InnoDB, use ALTER TABLE:

ALTER TABLE t TYPE = InnoDB;