InnoDB Tables - MySQL

The InnoDB table handler is the newest addition to the MySQL family. Developed by Heikki Tuuri of Innobase Oy in Helsinki, Finland, InnoDB was designed with transaction processing in mind and modeled largely after Oracle.

Storage

The InnoDB table handler breaks from MySQL tradition and stores all its data in a series of one or more data files that are collectively known as a tablespace.A tablespace is essentially a black box that is completely managed by InnoDB. If a tablespace if composed of several underlying files, you can’t choose or influence which of the underlying files will contain the data for any particular database or table.

InnoDB can also use raw disk partitions in building its tablespace, but that’s not very common. Using disk partitions makes it more difficult to back up InnoDB’s data, and the resulting performance boost is on the order of a few percent on most operating systems.

As of MySQL 4.1, you have the option of slightly more MyISAM-like storage for InnoDB. You can enable multiple tablespace support by adding innodb_file_per_ table to my.cnf; this makes InnoDB create one tablespace file per newly created InnoDB table. The filename will be of the form tablename.ibd. In all other respects, they’re simply dynamically sized InnoDB tablespace files. Each one just happens to contain data for only one specific table.

Locking and concurrency

InnoDB uses MVCC to achieve very high concurrency. InnoDB defaults to the repeatable read isolation level, and as of MySQL Version 4.0.5, it implements all four levels: read uncommitted, read committed, repeatable read, and serializable.

In an InnoDB transaction, You may explicitly obtain either exclusive or shared locks on rows using the MySQL statements: SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE.

Special features

Besides its excellent concurrency, InnoDB’s next most popular feature is referential integrity in the form of foreign key constraints. This means that given the following schema:

CREATE TABLE master (
 id INTEGER NOT NULL PRIMARY KEY,
 stuff TEXT NOT NULL
) TYPE = InnoDB;

CREATE TABLE detail (
 master_id INTEGER NOT NULL,
 detail1 VARCHAR(80) NOT NULL,
 detail2 VARCHAR(20) NOT NULL,
 INDEX master_idx (master_id),
 FOREIGN KEY (master_id) REFERENCES master(id)
) TYPE = InnoDB;

InnoDB doesn’t allow you to insert add records to the detail table until there is a corresponding record in the master table. Attempting to do so yields an error:

mysql> INSERT INTO detail VALUES (10, 'blah', 'blah');
ERROR 1216: Cannot add a child row: a foreign key constraint fails

InnoDB also provides lightning fast record lookups for queries that use a primary key. Its clustered index system (described in more detail in Chapter 4) explains how it works.

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.