Home arrow MySQL arrow Page 14 - Storage Engine (Table Types)

InnoDB Tables - MySQL

This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)

  1. Storage Engine (Table Types)
  2. Locking
  3. Multi-Version Concurrency Control
  4. Transactions
  5. Bene
  6. Deadlocks
  7. Transactions in MySQL
  8. Selecting the Right Engine
  9. Practical Examples
  10. Table Conversions
  11. The Storage Engines
  12. MyISAM Tables
  13. Compressed MyISAM Tables
  14. InnoDB Tables
  15. Heap (In-Memory) Tables
By: O'Reilly Media
Rating: starstarstarstarstar / 55
August 02, 2004

print this article



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.


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:

) TYPE = InnoDB;

 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.

Buy the book!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.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: