Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  The InnoDB Storage Engine  /  Introduction to InnoDB

14.1 Introduction to InnoDB

InnoDB is a general-purpose storage engine that balances high reliability and high performance. As of MySQL 5.5, it is the default MySQL storage engine. Issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

Key Advantages of InnoDB

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. With foreign keys, inserts, updates, and deletes are 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.

  • InnoDB has been designed for CPU efficiency and maximum performance when processing large data volumes.

Table 14.1 InnoDB Storage Engine Features

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

[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.

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

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

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

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

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

[g] Implemented in the server, rather than in the storage 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.

To compare the features of InnoDB with other storage engines provided with MySQL, see the Storage Engine Features table in Chapter 15, Alternative Storage Engines.

InnoDB Enhancements and New Features

For information about InnoDB enhancements and new features in MySQL 5.7, refer to:

Additional Resources

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.