InnoDBInnoDB Startup Options and System VariablesInnoDB TablesInnoDB Data and Log
FilesInnoDB DatabaseInnoDB Tables to Another MachineInnoDB Transaction Model and LockingInnoDB Multi-VersioningInnoDB Table and Index StructuresInnoDB Disk I/O and File Space ManagementInnoDB Error HandlingInnoDB Performance Tuning and TroubleshootingInnoDB Tables
InnoDB is a high-reliability and high-performance
storage engine for MySQL. Starting with MySQL 5.5, it is the default
MySQL storage engine. Key advantages of InnoDB include:
Its design follows 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 common queries based on
primary keys. Each
InnoDB table has a primary key index called
the clustered index
that organizes the data to minimize I/O for primary key lookups.
To maintain data integrity, InnoDB also
supports FOREIGN
KEY referential-integrity constraints.
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.
To determine whether your server supports InnoDB
use the SHOW ENGINES statement. See
Section 13.7.5.17, “SHOW ENGINES Syntax”.
Table 14.2. InnoDB Storage Engine
Features
| Storage limits | 64TB | Transactions | Yes | Locking granularity | Row |
| MVCC | Yes | Geospatial data type support | Yes | Geospatial indexing support | No |
| B-tree indexes | Yes | T-tree indexes | No | Hash indexes | No[a] |
| Full-text search indexes | Yes[b] | Clustered indexes | Yes | Data caches | Yes |
| Index caches | Yes | Compressed data | Yes[c] | Encrypted data[d] | Yes |
| Cluster database support | No | Replication support[e] | Yes | Foreign key support | Yes |
| Backup / point-in-time recovery[f] | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes |
[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.
InnoDB stores its tables and indexes in a
tablespace, which may consist of several files (or raw disk
partitions). This is different from, for example,
MyISAM tables where each table is stored using
separate files. InnoDB tables can be very large
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/.
A forum dedicated to the InnoDB storage
engine is available at http://forums.mysql.com/list.php?22.
The InnoDB storage engine in MySQL 5.5 releases
includes a number performance improvements that in MySQL 5.1
were only available by installing the InnoDB Plugin. This latest
InnoDB (now known as InnoDB 1.1) offers new features, improved
performance and scalability, enhanced reliability and new
capabilities for flexibility and ease of use. Among the top
features are Fast Index Creation, table and index compression,
file format management, new
INFORMATION_SCHEMA tables, capacity tuning,
multiple background I/O threads, multiple buffer pools, and
group commit.
For information about these features, see Section 14.4, “New Features of InnoDB 1.1”.
The MySQL Enterprise Backup product lets you back up a running
MySQL database, including InnoDB and
MyISAM tables, with minimal disruption to
operations while producing a consistent snapshot of the
database. When MySQL Enterprise Backup is copying
InnoDB tables, reads and writes to both
InnoDB and MyISAM tables
can continue. During the copying of MyISAM
and other non-InnoDB tables, reads (but not writes) to those
tables are permitted. In addition, MySQL Enterprise Backup can
create compressed backup files, and back up subsets of
InnoDB tables. In conjunction with the MySQL
binary log, you can perform point-in-time recovery. MySQL
Enterprise Backup is included as part of the MySQL Enterprise
subscription.
For a more complete description of MySQL Enterprise Backup, see MySQL Enterprise Backup User's Guide (Version 3.8.1).