Menu:
innodb-turning-off:: Turning Off InnoDB
'InnoDB' is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.7, 'InnoDB' is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a *note 'CREATE TABLE': create-table. statement without an 'ENGINE' clause creates an 'InnoDB' table.
Key Advantages of InnoDB
Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. See *note mysql-acid::.
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance. See *note innodb-locking-transaction-model::.
'InnoDB' tables arrange your data on disk to optimize 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. See *note innodb-index-types::.
To maintain data integrity, 'InnoDB' supports 'FOREIGN KEY' constraints. With foreign keys, inserts, updates, and deletes are checked to ensure they do not result in inconsistencies across related tables. See *note create-table-foreign-keys::.
InnoDB Storage Engine Features
Feature Support
B-tree indexes Yes
Backup/point-in-time recovery Yes (Implemented in the server, rather than
in the storage engine.)
Cluster database support No
Clustered indexes Yes
Compressed data Yes
Data caches Yes
Encrypted data Yes (Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest encryption is supported.)
Foreign key support Yes
Full-text search indexes Yes (Support for FULLTEXT indexes is available in MySQL 5.6 and later.)
Geospatial data type support Yes
Geospatial indexing support Yes (Support for geospatial indexing is available in MySQL 5.7 and later.)
Hash indexes No (InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.)
Index caches Yes
Locking granularity Row
MVCC Yes
Replication support (Implemented in the Yes server, rather than in the storage
engine.)
Storage limits 64TB
T-tree indexes No
Transactions Yes
Update statistics for data dictionary Yes
To compare the features of 'InnoDB' with other storage engines provided with MySQL, see the Storage Engine Features table in *note storage-engines::.
InnoDB Enhancements and New Features
For information about 'InnoDB' enhancements and new features, refer to:
The 'InnoDB' enhancements list in *note mysql-nutshell::.
The Release Notes (https://dev.mysql.com/doc/relnotes/mysql/5.7/en/).
Additional InnoDB Information and Resources
For 'InnoDB'-related terms and definitions, see the *note glossary::.
For a forum dedicated to the 'InnoDB' storage engine, see MySQL Forums::InnoDB (http://forums.mysql.com/list.php?22).
'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/.
File: manual.info.tmp, Node: innodb-benefits, Next: innodb-best-practices, Prev: innodb-introduction, Up: innodb-introduction
'InnoDB' tables have the following benefits:
If the server unexpectedly exits because of a hardware or software issue, regardless of what was happening in the database at the time, you don't need to do anything special after restarting the database. 'InnoDB' crash recovery automatically finalizes changes that were committed before the time of the crash, and undoes changes that were in process but not committed, permitting you to restart and continue from where you left off. See *note innodb-recovery::.
The 'InnoDB' storage engine maintains its own buffer pool that caches table and index data in main memory as data is accessed. Frequently used data is processed directly from memory. This cache applies to many types of information and speeds up processing. On dedicated database servers, up to 80% of physical memory is often assigned to the buffer pool. See *note innodb-buffer-pool::.
If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. See *note create-table-foreign-keys::.
If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it. The 'innodb_checksum_algorithm' variable defines the checksum algorithm used by 'InnoDB'.
When you design a database with appropriate primary key columns for each table, operations involving those columns are automatically optimized. It is very fast to reference the primary key columns in note 'WHERE': select. clauses, note 'ORDER BY': select. clauses, note 'GROUP BY': select. clauses, and join operations. See note innodb-index-types::.
Inserts, updates, and deletes are optimized by an automatic mechanism called change buffering. 'InnoDB' not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O. See *note innodb-change-buffer::.
Performance benefits are not limited to large tables with long-running queries. When the same rows are accessed over and over from a table, the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table. See *note innodb-adaptive-hash::.
You can compress tables and associated indexes. See *note innodb-compression::.
You can encrypt your data. See *note innodb-data-encryption::.
You can create and drop indexes and perform other DDL operations with much less impact on performance and availability. See *note innodb-online-ddl-operations::.
Truncating a file-per-table tablespace is very fast and can free up disk space for the operating system to reuse rather than only 'InnoDB'. See *note innodb-file-per-table-tablespaces::.
The storage layout for table data is more efficient for note 'BLOB': blob. and long text fields, with the 'DYNAMIC' row format. See note innodb-row-format::.
You can monitor the internal workings of the storage engine by querying 'INFORMATION_SCHEMA' tables. See *note innodb-information-schema::.
You can monitor the performance details of the storage engine by querying Performance Schema tables. See *note innodb-performance-schema::.
You can 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 *note 'MEMORY': memory-storage-engine. tables in a single query.
'InnoDB' has been designed for CPU efficiency and maximum performance when processing large data volumes.
'InnoDB' tables can handle large quantities of data, even on operating systems where file size is limited to 2GB.
For 'InnoDB'-specific tuning techniques you can apply to your MySQL server and application code, see *note optimizing-innodb::.
File: manual.info.tmp, Node: innodb-best-practices, Next: innodb-check-availability, Prev: innodb-benefits, Up: innodb-introduction
This section describes best practices when using 'InnoDB' tables.
Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there is no obvious primary key.
Use joins wherever data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same data type in each table. Adding foreign keys ensures that referenced columns are indexed, which can improve performance. Foreign keys also propagate deletes and updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
Group sets of related DML operations into transactions by bracketing them with 'START TRANSACTION' and 'COMMIT' statements. While you don't want to commit too often, you also don't want to issue huge batches of note 'INSERT': insert, note 'UPDATE': update, or *note 'DELETE': delete. statements that run for hours without committing.
Do not use note 'LOCK TABLES': lock-tables. statements. 'InnoDB' can handle multiple sessions all reading and writing to the same table at once without sacrificing reliability or high performance. To get exclusive write access to a set of rows, use the note 'SELECT ... FOR UPDATE': innodb-locking-reads. syntax to lock just the rows you intend to update.
Enable the 'innodb_file_per_table' variable or use general tablespaces to put the data and indexes for tables into separate files instead of the system tablespace. The 'innodb_file_per_table' variable is enabled by default.
Evaluate whether your data and access patterns benefit from the 'InnoDB' table or page compression features. You can compress 'InnoDB' tables without sacrificing read/write capability.
Run the server with the '--sql_mode=NO_ENGINE_SUBSTITUTION' option to prevent tables from being created with storage engines that you do not want to use.
File: manual.info.tmp, Node: innodb-check-availability, Next: innodb-benchmarking, Prev: innodb-best-practices, Up: innodb-introduction
Issue the *note 'SHOW ENGINES': show-engines. statement to view the available MySQL storage engines. Look for 'DEFAULT' in the 'SUPPORT' column.
mysql> SHOW ENGINES;Alternatively, query the Information Schema *note 'ENGINES': information-schema-engines-table. table.
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;File: manual.info.tmp, Node: innodb-benchmarking, Next: innodb-turning-off, Prev: innodb-check-availability, Up: innodb-introduction
If 'InnoDB' is not the default storage engine, you can determine if your database server and applications work correctly with 'InnoDB' by restarting the server with '--default-storage-engine=InnoDB' on the command line or with 'default-storage-engine=innodb' defined in the '[mysqld]' section of the MySQL server option file.
Since changing the default storage engine only affects newly created tables, run your application installation and setup steps to confirm that everything installs properly, then exercise the application features to make sure the data loading, editing, and querying features work. If a table relies on a feature that is specific to another storage engine, you receive an error. In this case, add the 'ENGINE=OTHER_ENGINE_NAME' clause to the *note 'CREATE TABLE': create-table. statement to avoid the error.
If you did not make a deliberate decision about the storage engine, and you want to preview how certain tables work when created using 'InnoDB', issue the command *note 'ALTER TABLE table_name ENGINE=InnoDB;': alter-table. for each table. Alternatively, to run test queries and other statements without disturbing the original table, make a copy:
CREATE TABLE ... ENGINE=InnoDB AS SELECT * FROM OTHER_ENGINE_TABLE;To assess performance with a full application under a realistic workload, install the latest MySQL server and run benchmarks.
Test the full application lifecycle, from installation, through heavy usage, and server restart. Kill the server process while the database is busy to simulate a power failure, and verify that the data is recovered successfully when you restart the server.
Test any replication configurations, especially if you use different MySQL versions and options on the source server and replicas.
File: manual.info.tmp, Node: innodb-turning-off, Prev: innodb-benchmarking, Up: innodb-introduction
Oracle recommends 'InnoDB' as the preferred storage engine for typical database applications, from single-user wikis and blogs running on a local system, to high-end applications pushing the limits of performance. In MySQL 5.7, 'InnoDB' is the default storage engine for new tables.
Important:
'InnoDB' cannot be disabled. The '--skip-innodb' option is deprecated and has no effect, and its use results in a warning. Expect it to be removed in a future MySQL release. This also applies to its synonyms ('--innodb=OFF', '--disable-innodb', and so forth).
File: manual.info.tmp, Node: mysql-acid, Next: innodb-multi-versioning, Prev: innodb-introduction, Up: innodb-storage-engine