Skip navigation links
MySQL 5.6 Reference Manual :: 14 Storage Engines :: 14.2 The InnoDB Storage Engine
14.2.1 Getting Started with InnoDB Tables »
Section Navigation      [Toggle]

14.2. The InnoDB Storage Engine

[+/-]

14.2.1. Getting Started with InnoDB Tables
14.2.2. Administering InnoDB
14.2.3. InnoDB Concepts and Architecture
14.2.4. InnoDB Performance Tuning and Troubleshooting
14.2.5. InnoDB Features for Flexibility, Ease of Use and Reliability
14.2.6. InnoDB Startup Options and System Variables
14.2.7. Limits on InnoDB Tables
14.2.8. MySQL and the ACID Model
14.2.9. InnoDB Integration with memcached

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, issuing the CREATE TABLE statement with no ENGINE= clause creates an InnoDB table.

Key advantages of InnoDB tables include:

  • Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.

  • Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.

  • InnoDB tables arrange your data on disk to optimize queries based on primary keys.

  • To maintain data integrity, InnoDB also supports FOREIGN KEY constraints. Inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.

  • You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

  • The latest InnoDB offers significant new features over MySQL 5.1 and earlier. These features focus on performance and scalability, reliability, flexibility, and usability:

Table 14.2. InnoDB Storage Engine Features

Storage limits64TBTransactionsYesLocking granularityRow
MVCCYesGeospatial data type supportYesGeospatial indexing supportNo
B-tree indexesYesT-tree indexesNoHash indexesNo[a]
Full-text search indexesYes[b]Clustered indexesYesData cachesYes
Index cachesYesCompressed dataYes[c]Encrypted data[d]Yes
Cluster database supportNoReplication support[e]YesForeign key supportYes
Backup / point-in-time recovery[f]YesQuery cache supportYesUpdate statistics for data dictionaryYes

[a] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

[b] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

[c] Compressed InnoDB tables require the InnoDB Barracuda file format.

[d] Implemented in the server (via encryption functions), rather than in the storage engine.

[e] Implemented in the server, rather than in the storage engine.

[f] Implemented in the server, rather than in the storage engine.


InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. By default, with the innodb_file_per_table setting enabled, each new InnoDB table and its associated indexes are stored in a separate file. When the innodb_file_per_table option is disabled, InnoDB stores all its tables and indexes in the single system tablespace, which may consist of several files (or raw disk partitions). InnoDB tables can handle large quantities of data, even on operating systems where file size is limited to 2GB.

InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as MySQL. For more information on MySQL licensing, see http://www.mysql.com/company/legal/licensing/.

Additional Resources

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices