14.1 Introduction to InnoDB
14.1.1 InnoDB as the Default MySQL Storage Engine
14.1.2 Checking InnoDB Availability
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine. Issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
InnoDB是一个兼顾了高可靠性高性能的多用途存储引擎。在MySQL5.6里，InnoDB是MySQL默认的存储引擎。执行CREATE TABLE语句没有使用ENGINE= clause的话就会创建InnoDB表。
Key Advantages of InnoDB
Key advantages of InnoDB tables include:
InnoDB的核心优势包括：
l Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
l 它的DML操作遵循ACID模式，具有事务的特性，commit，rollback，具有崩溃恢复的能力来保护用户数据。
l Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
l 行级锁和Oracle风格的一致性读增加了多用户情况下并发性和性能。
l InnoDB tables arrange your data on disk to optimize queries based on primary keys.
l InnoDB表在及磁盘上基于主键来的存储来优化查询。
l 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.
l 为了维持数据的完整性，InnoDB还支持外键约束。使用了外键，inserts, updates, and deletes会检查并确保不同表之间的结构不会不一致。
l 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.
l 你可以自由地混合使用InnoDB表及其他MySQL存储引擎的表，即使是在同一个语句里。例如，你可以在一个查询里对InnoDB和MEMORY表进行join操作。
l InnoDB has been designed for CPU efficiency and maximum performance when processing large data volumes.
l InnoDB被设计成在处理大量数据时对CPU的使用效率更高，性能更好。
Table 14.1 InnoDB Storage Engine Features
|
Storage limits |
64TB |
Transactions |
Yes |
Locking granularity |
Row |
|
MVCC |
Yes |
Geospatial data type support |
Yes |
Geospatial indexing support |
Yes[a] |
|
B-tree indexes |
Yes |
T-tree indexes |
No |
Hash indexes |
No[b] |
|
Full-text search indexes |
Yes[c] |
Clustered indexes |
Yes |
Data caches |
Yes |
|
Index caches |
Yes |
Compressed data |
Yes[d] |
Encrypted data[e] |
Yes |
|
Cluster database support |
No |
Replication support[f] |
Yes |
Foreign key support |
Yes |
|
Backup / point-in-time recovery[g] |
Yes |
Query cache support |
Yes |
Update statistics for data dictionary |
Yes |
[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.
InnoDB存储引擎维护它自己的buffer pool来在内存里缓存数据和索引。默认情况下，innodb_file_per_table设置是开启的，每个新的InnoDB表和对应的索引会存储在不同的文件里。当innodb_file_per_table是关闭的，InnoDB会把它所有的表和索引都存储在单个的系统表空间里。这个系统表空间也可以是由数个文件组成的（或者是raw磁盘分区）。InnoDB表能够处理大量的数据，甚至于是超过所在操作系统的最大2G的限制。
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.
相比MySQL提供的其他的存储引擎的特性，可以在Chapter 15, Alternative Storage Engines查看Storage Engine Features。
InnoDB Enhancements and New Features
For information about InnoDB enhancements and new features in MySQL 5.6, refer to:
MySQL5.6里关于InnoDB的增强信息及新特性可以参考下面的内容：
l The InnoDB enhancements list in Section 1.4, “What Is New in MySQL 5.6”, which provides an overview of the features added in MySQL 5.6.
l InnoDB的增强列表在Section 1.4, “What Is New in MySQL 5.6”，提供了在MySQL5.6新加特性的概述。
l The Release Notes, which provide information about changes in each version.
l Release Notes，提供了每个版本修改的信息。
Additional Resources
l For InnoDB-related terms and definitions, see MySQL Glossary.
l InnoDB相关的条目和定义，可以查看MySQL Glossary。
l A forum dedicated to the InnoDB storage engine is available here: MySQL Forums::InnoDB.
l 可获得InnoDB存储引擎的论坛：MySQL Forums::InnoDB(http://forums.mysql.com/list.php?22)。
l 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/.
l InnoDB遵循和MySQL一样的GNU GPL License Version 2 (of June 1991)。更多关于MySQL license的信息可以查看http://www.mysql.com/company/legal/licensing/。
14.1.1 InnoDB as the Default MySQL Storage Engine
InnoDB is the default storage engine in MySQL 5.6. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
在MySQL5.6里InnoDB是默认的存储引擎。InnoDB是一个事务安全的（ACID）的存储引擎，可以commit，rollback，以及崩溃恢复来保护用户数据。InnoDB的行级锁（不会扩大锁的粒度）和 Oracle风格的非锁定的一致性读提升了多用户的并发性和性能。InnoDB以主键索引的分布存储用户的数据，这样就能够减少了普通的基于主键查询的I/O。为了维护数据的完整行，InnoDB还支持外键约束。
Unless you have configured a different default storage engine, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
除非你要配置成不同的存储引擎，那么日常在执行CREATE TABLE没有指定ENGINE= clause的时候就会创建InnoDB表。
Benefits of InnoDB Tables
If you use MyISAM tables but are not committed to them for technical reasons, you may find InnoDB tables beneficial for the following reasons:
如果你 使用了MyISAM表因为技术原因而无法对其进行提交时，你就可以从下面的原因里找到InnoDB表的优点：
l If your server crashes 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 any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.
l 如果你的实例因为硬件或者软件原因而崩溃了，不管在那个时间点上数据库在做什么，数据库重新启动后你不需要做任何特定的动作就能完好地再次使用数据库。Innodb崩溃恢复会自动放弃崩溃时间点前未提交的数据，也会回滚任何为提交的操作。这样就重启之后就可以在崩溃的时间点上继续进行服务。
l The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to many types of information, and speeds up processing.
l InnoDB buffer pool会缓存访问数据的表和索引。经常使用的数据会直接从内存中进行处理。缓存适用于很多类型的信息，并能加快它们的处理速度。
l If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and the bad data gets kicked out automatically.
l 如果你从不同的表里分割像关联的数据，你可以设置外键来强制数据的完整性。在对数据进行update和delete的时候，其他表里相关的数据也会自动进行update和delete。如果没有通过相关的主表而尝试向副表插入数据时，这种坏的数据会被自动移除。
l If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
l 如果磁盘上或内存里的数据损坏了，一个checksum的机制会在你使用它之前对你进行警告。
l When you design your 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 WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join operations.
l 当你对数据里每个表都设计了适当的主键，对这些主键列的操作都是自动进行优化的。在WHERE，ORDER BY,GROUP BY，还有join操作的时候涉及到主键的列会非常快速的。
l 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.
l insert，update还有delete会被优化成自动修改缓存。InnoDB不允许对同一个表同时进行读和写，所以它会缓存修改的数据并以流线型的方式进行磁盘I/O的操作。
l Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.
l 性能的优势并不局限于对大表的长时间查询。当相同的行从表里一次又一次地访问， Adaptive Hash Index的特性会接管并使得查询变得十分快速，即使它们都不是hash表的数据。
l You can compress tables and associated indexes.
l 你可以压缩表和对应的索引。
l You can create and drop indexes with much less impact on performance and availability.
l 你能够创建删除索引，更少地影响性能及可用性。
l Truncating a file_per_table tablespace is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB could reuse.
l truncate一个file_per_table表空间会非常快速，并能为操作系统释放磁盘空间进行重新利用，这要好过只释放系统表空间的空间，这样只能让InnoDB能够重新利用。
l The storage layout for table data is more efficient for BLOB and long text fields, with the DYNAMIC row format.
l 使用DYNAMIC的行格式，使得BLOB和长文本的型的表数据分布在存储上分布得更有效率。
l You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.
l 你能够通过查询INFORMATION_SCHEMA表来监控存储引擎内部工作的情况。
l You can monitor the performance details of the storage engine by querying Performance Schema tables.
l 你能够通过查询Performance Schema表来监控存储引擎的性能细节。
For InnoDB-specific tuning techniques you can apply in your application code, see Section 8.5, “Optimizing for InnoDB Tables”.
对于适用于应用程序代码的InnoDB特定的调优技术，可以查看Section 8.5, “Optimizing for InnoDB Tables”。
Recent Improvements for InnoDB Tables
MySQL continues to work on addressing use cases that formerly required MyISAM tables. In MySQL 5.6 and higher:
在MySQL5.6及更高的版本里，InnoDB开始支持一些原来MyISAM表的特性：
l InnoDB can perform full-text search using the FULLTEXT index type. See Section 14.2.5.3, “InnoDB FULLTEXT Indexes” for details.
l InnoDB可以使用FULLTEXT类型的索引来执行全文搜索，详情可见Section 14.2.5.3, “InnoDB FULLTEXT Indexes”。
l InnoDB now performs better with read-only or read-mostly workloads. Automatic optimizations apply to InnoDB queries in autocommit mode, and you can explicitly mark transactions as read-only with the syntax START TRANSACTION READ ONLY. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for details.
l InnoDB现在能够更好地执行只读或者是大部分读的情况。自动优化适用于在autocommit模式下InnoDB查询，你也可以使用START TRANSACTION READ ONLY明确事务是只读的。详情可见Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”。
l Applications distributed on read-only media can now use InnoDB tables. See Section 14.4.2, “Configuring InnoDB for Read-Only Operation” for details.
l 现在可以把InnoDB的放到只读的媒体上了。详情可见Section 14.4.2, “Configuring InnoDB for Read-Only Operation”。
Best Practices for InnoDB Tables
Some general best practices for InnoDB tables include:
一些InnoDB表的实践包括：
l Specifying 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.
l 为每个表在查询最频繁的列上指定为主键，如果没有明显合适的主键列那就使用一个自增长的列。
l Using 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 or 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.
l 在多表join时最好是基于同一个的ID值。为了使得join的性能更佳，可以把join列定义成外键，在其他的每个表也把列定义成和它是相同的数据类型。添加外键的时候要确保相关的列上是有索引的，这样能够提升性能。外键能够把delete和update操作传播到所有受影响的表，如果相关的ID不存在父表的时候还能阻止网子表里插记录。
l Turning off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
l 关闭autocommit。一秒钟内进行数百次的提交会影响性能。（局限于你存储磁盘的写的速度）。
l Grouping 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 INSERT, UPDATE, or DELETE statements that run for hours without committing.
l 把相关的DML操作分组到START TRANSACTION和COMMIT框架的事务里。当你不想要频繁提交的时候，但也不要进行数个小时的INSERT, UPDATE, or DELETE批处理时而不进行任何的提交操作。
l Not using 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 SELECT ... FOR UPDATE syntax to lock just the rows you intend to update.
l 不要使用LOCK TABLES语句。InnoDB能够处理多个会话在同一个表上进行读和写，而不会牺牲可靠性和性能。要对一些行进行排他性的写访问时，可是使用SELECT ... FOR UPDATE来锁住你那些你想要更新的行记录。
l Enabling the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. This setting is required to use some of the other features, such as table compression and fast truncation.
l 开启innodb_file_per_table参数把数据和索引分散到不同的文件上，而不是都放在系统表空间里。这个设定还可以用于一些其他的特性上，例如表的压缩和快速的truncate操作。
l The innodb_file_per_table option is enabled by default as of MySQL 5.6.6.
l innodb_file_per_table参数化在MySQL5.6.6里默认就是开启的。
l Evaluating whether your data and access patterns benefit from the InnoDB table compression feature (ROW_FORMAT=COMPRESSED) on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/write capability.
l 评估你的数据和访问模式在InnoDB压缩特性下(ROW_FORMAT=COMPRESSED)是否是有益的。你能够压缩InnoDB表而不牺牲读写的性能。
l Running your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the engine specified in the ENGINE= clause of CREATE TABLE.
l 使用--sql_mode=NO_ENGINE_SUBSTITUTION参数来运行实例，来阻止那些在建表的时候指定了一个有问题的其他存储引擎。
Testing and Benchmarking with InnoDB as Default Storage Engine
If InnoDB is not your default storage engine, you can determine if your database server or applications work correctly with InnoDB by restarting the server with --default-storage-engine=InnoDB defined on the command line or with default-storage-engine=innodb defined in the [mysqld] section of the my.cnf configuration file.
如果InnoDB不是你默认的存储引擎，那么你就要确认你的数据库实例或者应用程序是否能够在InnoDB的情况下正确工作。修改默认存储引擎可以在实例启动的时候使用--default-storage-engine=InnoDB，或者在my.cnf配置文件的[mysqld]里面指定default-storage-engine=innodb。
Since changing the default storage engine only affects new tables as they are created, run all your application installation and setup steps to confirm that everything installs properly. Then exercise all the application features to make sure all the data loading, editing, and querying features work. If a table relies on some MyISAM-specific feature, you'll receive an error; add the ENGINE=MyISAM clause to the CREATE TABLE statement to avoid the error.
因为修改默认的存储引擎只会影响那些新建的表，所以在运行你应用程序的安装配置步骤的时候要确认每个安装步骤都是正确的。然后确保应用程序所有的数据加载，编辑，及查询都工作正常。如果表是依赖于MyISAM指定特性的话，你会接收到一个错误；那可以在CREATE TABLE的时候指定ENGINE=MyISAM来避免这个错误。
If you did not make a deliberate decision about the storage engine, and you just want to preview how certain tables work when they're created under InnoDB, issue the command ALTER TABLE table_name ENGINE=InnoDB; for each table. Or, to run test queries and other statements without disturbing the original table, make a copy like so:
如果你对存储引擎还不确认，只是想要预览某些个表在InnoDB环境下是怎么工作的，可以对每个表执行ALTER TABLE table_name ENGINE=InnoDB。又或者只是想在不改变原表分布的情况下检测查询或者其他的一些语句，可以像这样做一个拷贝：
CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;
To get a true idea of the performance with a full application under a realistic workload, install the latest MySQL server and run benchmarks.
如果想要在现实工作负载的情况下得到一个正确的性能意见，那么可以安装最新的MySQL并进行基准测试。
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.
从安装开始，通过大量的使用，并重启实例来测试整个应用程序的生命周期。在数据库正忙的时候kill实例进程来模仿电源故障，然后在重启实例的时候验证数据恢复得是否正确。
Test any replication configurations, especially if you use different MySQL versions and options on the master and the slaves.
验证任何的主从复制的配置，尤其是在master和slave之间使用了不同的MySQL版本和配置参数。
Verifying that InnoDB is the Default Storage Engine
To verify that InnoDB is the default storage engine:
验证InnoDB是否是默认的存储引擎：
l Issue the SHOW ENGINES command to view the different MySQL storage engines. Look for DEFAULT in the InnoDB line. Alternatively, query the INFORMATION_SCHEMA ENGINES table.
l 执行SHOW ENGINES查看不同的MySQL存储引擎。查看在InnoDB那一行的DEFAULT。另外啊，还可以查询INFORMATION_SCHEMA的ENGINES表。
l If InnoDB is not present, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one.
l 如果InnoDB是不存在的，那么可能你的mysqld二进制编译的时候没有支持InnoDB，那你就要重新获取一个了。
l If InnoDB is present but disabled, go back through your startup options and configuration file and get rid of any skip-innodb option.
l 如果InnoDB是存在的，但是被关闭了，那么就回到你的启动参数，配置文件来排除任何的skip-innodb参数。
14.1.2 Checking InnoDB Availability
To determine whether your server supports InnoDB, use the SHOW ENGINES statement. (Now that InnoDB is the default MySQL storage engine, only very specialized environments might not support it.)
为了确定你的实例是否支持InnoDB，可以使用SHOW ENGINES语句。（现在InnoDB是MySQL默认的存储引擎，之后特别特殊的环境才会不支持它。）
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.6, InnoDB is the default storage engine for new tables.
Oracle推荐InnoDB作为通常数据库应用的首选存储引擎，不论是本地系统的单用户的维科博客，还是推送到极致性能的高端应用。在MySQL5.6里，对于新的表InnoDB是默认的存储引擎。
If you do not want to use InnoDB tables:
如果你不想使用InnoDB表：
l Start the server with the --innodb=OFF or --skip-innodb option to disable the InnoDB storage engine.
l 在启动实例的时候使用--innodb=OFF或者--skip-innodb参数来关闭InnoDB存储引擎。
Note
As of MySQL 5.6.21, the --skip-innodb option still works but it is deprecated and will return a warning when used. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).
从MySQL5.6.21开始，--skip-innodb虽然仍然能够工作，但是这个参数已经是被废弃了的，而且使用的时候还会返回一个警告，在将来的MySQL版本里也会被移除。这种情况也适用于它的同义词(--innodb=OFF, --disable-innodb, and so forth)。
l Because the default storage engine is InnoDB, the server will not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.
l 因为默认的存储引擎是InnoDB，这是不会改变的除非在实例启动的时候使用了--default-storage-engine和--default-tmp-storage-engine把表的默认存储引擎改成其他的。
l To prevent the server from crashing when the InnoDB-related information_schema tables are queried, also disable the plugins associated with those tables. Specify in the [mysqld] section of the MySQL configuration file:
l 为了预防在查询InnoDB相关information_schema表的时候实例崩溃，可以关闭这些表的相关插件。在MySQL配置文件的[mysqld]里指定：
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0
======================================================================下面是重复内容======================================================================
14.1 Introduction to InnoDB
14.1.1 InnoDB as the Default MySQL Storage Engine
14.1.2 Checking InnoDB Availability
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine. Issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
InnoDB是一个兼顾了高可靠性高性能的多用途存储引擎。在MySQL5.6里，InnoDB是MySQL默认的存储引擎。执行CREATE TABLE语句没有使用ENGINE= clause的话就会创建InnoDB表。
Key Advantages of InnoDB
Key advantages of InnoDB tables include:
InnoDB的核心优势包括：
l Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
l 它的DML操作遵循ACID模式，具有事务的特性，commit，rollback，具有崩溃恢复的能力来保护用户数据。
l Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
l 行级锁和Oracle风格的一致性读增加了多用户情况下并发性和性能。
l InnoDB tables arrange your data on disk to optimize queries based on primary keys.
l InnoDB表在及磁盘上基于主键来的存储来优化查询。
l 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.
l 为了维持数据的完整性，InnoDB还支持外键约束。使用了外键，inserts, updates, and deletes会检查并确保不同表之间的结构不会不一致。
l 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.
l 你可以自由地混合使用InnoDB表及其他MySQL存储引擎的表，即使是在同一个语句里。例如，你可以在一个查询里对InnoDB和MEMORY表进行join操作。
l InnoDB has been designed for CPU efficiency and maximum performance when processing large data volumes.
l InnoDB被设计成在处理大量数据时对CPU的使用效率更高，性能更好。
Table 14.1 InnoDB Storage Engine Features
|
Storage limits |
64TB |
Transactions |
Yes |
Locking granularity |
Row |
|
MVCC |
Yes |
Geospatial data type support |
Yes |
Geospatial indexing support |
Yes[a] |
|
B-tree indexes |
Yes |
T-tree indexes |
No |
Hash indexes |
No[b] |
|
Full-text search indexes |
Yes[c] |
Clustered indexes |
Yes |
Data caches |
Yes |
|
Index caches |
Yes |
Compressed data |
Yes[d] |
Encrypted data[e] |
Yes |
|
Cluster database support |
No |
Replication support[f] |
Yes |
Foreign key support |
Yes |
|
Backup / point-in-time recovery[g] |
Yes |
Query cache support |
Yes |
Update statistics for data dictionary |
Yes |
[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.
InnoDB存储引擎维护它自己的buffer pool来在内存里缓存数据和索引。默认情况下，innodb_file_per_table设置是开启的，每个新的InnoDB表和对应的索引会存储在不同的文件里。当innodb_file_per_table是关闭的，InnoDB会把它所有的表和索引都存储在单个的系统表空间里。这个系统表空间也可以是由数个文件组成的（或者是raw磁盘分区）。InnoDB表能够处理大量的数据，甚至于是超过所在操作系统的最大2G的限制。
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.
相比MySQL提供的其他的存储引擎的特性，可以在Chapter 15, Alternative Storage Engines查看Storage Engine Features。
InnoDB Enhancements and New Features
For information about InnoDB enhancements and new features in MySQL 5.6, refer to:
MySQL5.6里关于InnoDB的增强信息及新特性可以参考下面的内容：
l The InnoDB enhancements list in Section 1.4, “What Is New in MySQL 5.6”, which provides an overview of the features added in MySQL 5.6.
l InnoDB的增强列表在Section 1.4, “What Is New in MySQL 5.6”，提供了在MySQL5.6新加特性的概述。
l The Release Notes, which provide information about changes in each version.
l Release Notes，提供了每个版本修改的信息。
Additional Resources
l For InnoDB-related terms and definitions, see MySQL Glossary.
l InnoDB相关的条目和定义，可以查看MySQL Glossary。
l A forum dedicated to the InnoDB storage engine is available here: MySQL Forums::InnoDB.
l 可获得InnoDB存储引擎的论坛：MySQL Forums::InnoDB(http://forums.mysql.com/list.php?22)。
l 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/.
l InnoDB遵循和MySQL一样的GNU GPL License Version 2 (of June 1991)。更多关于MySQL license的信息可以查看http://www.mysql.com/company/legal/licensing/。
14.1.1 InnoDB as the Default MySQL Storage Engine
InnoDB is the default storage engine in MySQL 5.6. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
在MySQL5.6里InnoDB是默认的存储引擎。InnoDB是一个事务安全的（ACID）的存储引擎，可以commit，rollback，以及崩溃恢复来保护用户数据。InnoDB的行级锁（不会扩大锁的粒度）和 Oracle风格的非锁定的一致性读提升了多用户的并发性和性能。InnoDB以主键索引的分布存储用户的数据，这样就能够减少了普通的基于主键查询的I/O。为了维护数据的完整行，InnoDB还支持外键约束。
Unless you have configured a different default storage engine, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
除非你要配置成不同的存储引擎，那么日常在执行CREATE TABLE没有指定ENGINE= clause的时候就会创建InnoDB表。
Benefits of InnoDB Tables
If you use MyISAM tables but are not committed to them for technical reasons, you may find InnoDB tables beneficial for the following reasons:
如果你 使用了MyISAM表因为技术原因而无法对其进行提交时，你就可以从下面的原因里找到InnoDB表的优点：
l If your server crashes 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 any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.
l 如果你的实例因为硬件或者软件原因而崩溃了，不管在那个时间点上数据库在做什么，数据库重新启动后你不需要做任何特定的动作就能完好地再次使用数据库。Innodb崩溃恢复会自动放弃崩溃时间点前未提交的数据，也会回滚任何为提交的操作。这样就重启之后就可以在崩溃的时间点上继续进行服务。
l The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to many types of information, and speeds up processing.
l InnoDB buffer pool会缓存访问数据的表和索引。经常使用的数据会直接从内存中进行处理。缓存适用于很多类型的信息，并能加快它们的处理速度。
l If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and the bad data gets kicked out automatically.
l 如果你从不同的表里分割像关联的数据，你可以设置外键来强制数据的完整性。在对数据进行update和delete的时候，其他表里相关的数据也会自动进行update和delete。如果没有通过相关的主表而尝试向副表插入数据时，这种坏的数据会被自动移除。
l If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
l 如果磁盘上或内存里的数据损坏了，一个checksum的机制会在你使用它之前对你进行警告。
l When you design your 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 WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join operations.
l 当你对数据里每个表都设计了适当的主键，对这些主键列的操作都是自动进行优化的。在WHERE，ORDER BY,GROUP BY，还有join操作的时候涉及到主键的列会非常快速的。
l 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.
l insert，update还有delete会被优化成自动修改缓存。InnoDB不允许对同一个表同时进行读和写，所以它会缓存修改的数据并以流线型的方式进行磁盘I/O的操作。
l Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.
l 性能的优势并不局限于对大表的长时间查询。当相同的行从表里一次又一次地访问， Adaptive Hash Index的特性会接管并使得查询变得十分快速，即使它们都不是hash表的数据。
l You can compress tables and associated indexes.
l 你可以压缩表和对应的索引。
l You can create and drop indexes with much less impact on performance and availability.
l 你能够创建删除索引，更少地影响性能及可用性。
l Truncating a file_per_table tablespace is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB could reuse.
l truncate一个file_per_table表空间会非常快速，并能为操作系统释放磁盘空间进行重新利用，这要好过只释放系统表空间的空间，这样只能让InnoDB能够重新利用。
l The storage layout for table data is more efficient for BLOB and long text fields, with the DYNAMIC row format.
l 使用DYNAMIC的行格式，使得BLOB和长文本的型的表数据分布在存储上分布得更有效率。
l You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.
l 你能够通过查询INFORMATION_SCHEMA表来监控存储引擎内部工作的情况。
l You can monitor the performance details of the storage engine by querying Performance Schema tables.
l 你能够通过查询Performance Schema表来监控存储引擎的性能细节。
For InnoDB-specific tuning techniques you can apply in your application code, see Section 8.5, “Optimizing for InnoDB Tables”.
对于适用于应用程序代码的InnoDB特定的调优技术，可以查看Section 8.5, “Optimizing for InnoDB Tables”。
Recent Improvements for InnoDB Tables
MySQL continues to work on addressing use cases that formerly required MyISAM tables. In MySQL 5.6 and higher:
在MySQL5.6及更高的版本里，InnoDB开始支持一些原来MyISAM表的特性：
l InnoDB can perform full-text search using the FULLTEXT index type. See Section 14.2.5.3, “InnoDB FULLTEXT Indexes” for details.
l InnoDB可以使用FULLTEXT类型的索引来执行全文搜索，详情可见Section 14.2.5.3, “InnoDB FULLTEXT Indexes”。
l InnoDB now performs better with read-only or read-mostly workloads. Automatic optimizations apply to InnoDB queries in autocommit mode, and you can explicitly mark transactions as read-only with the syntax START TRANSACTION READ ONLY. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for details.
l InnoDB现在能够更好地执行只读或者是大部分读的情况。自动优化适用于在autocommit模式下InnoDB查询，你也可以使用START TRANSACTION READ ONLY明确事务是只读的。详情可见Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”。
l Applications distributed on read-only media can now use InnoDB tables. See Section 14.4.2, “Configuring InnoDB for Read-Only Operation” for details.
l 现在可以把InnoDB的放到只读的媒体上了。详情可见Section 14.4.2, “Configuring InnoDB for Read-Only Operation”。
Best Practices for InnoDB Tables
Some general best practices for InnoDB tables include:
一些InnoDB表的实践包括：
l Specifying 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.
l 为每个表在查询最频繁的列上指定为主键，如果没有明显合适的主键列那就使用一个自增长的列。
l Using 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 or 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.
l 在多表join时最好是基于同一个的ID值。为了使得join的性能更佳，可以把join列定义成外键，在其他的每个表也把列定义成和它是相同的数据类型。添加外键的时候要确保相关的列上是有索引的，这样能够提升性能。外键能够把delete和update操作传播到所有受影响的表，如果相关的ID不存在父表的时候还能阻止网子表里插记录。
l Turning off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
l 关闭autocommit。一秒钟内进行数百次的提交会影响性能。（局限于你存储磁盘的写的速度）。
l Grouping 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 INSERT, UPDATE, or DELETE statements that run for hours without committing.
l 把相关的DML操作分组到START TRANSACTION和COMMIT框架的事务里。当你不想要频繁提交的时候，但也不要进行数个小时的INSERT, UPDATE, or DELETE批处理时而不进行任何的提交操作。
l Not using 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 SELECT ... FOR UPDATE syntax to lock just the rows you intend to update.
l 不要使用LOCK TABLES语句。InnoDB能够处理多个会话在同一个表上进行读和写，而不会牺牲可靠性和性能。要对一些行进行排他性的写访问时，可是使用SELECT ... FOR UPDATE来锁住你那些你想要更新的行记录。
l Enabling the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. This setting is required to use some of the other features, such as table compression and fast truncation.
l 开启innodb_file_per_table参数把数据和索引分散到不同的文件上，而不是都放在系统表空间里。这个设定还可以用于一些其他的特性上，例如表的压缩和快速的truncate操作。
l The innodb_file_per_table option is enabled by default as of MySQL 5.6.6.
l innodb_file_per_table参数化在MySQL5.6.6里默认就是开启的。
l Evaluating whether your data and access patterns benefit from the InnoDB table compression feature (ROW_FORMAT=COMPRESSED) on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/write capability.
l 评估你的数据和访问模式在InnoDB压缩特性下(ROW_FORMAT=COMPRESSED)是否是有益的。你能够压缩InnoDB表而不牺牲读写的性能。
l Running your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the engine specified in the ENGINE= clause of CREATE TABLE.
l 使用--sql_mode=NO_ENGINE_SUBSTITUTION参数来运行实例，来阻止那些在建表的时候指定了一个有问题的其他存储引擎。
Testing and Benchmarking with InnoDB as Default Storage Engine
If InnoDB is not your default storage engine, you can determine if your database server or applications work correctly with InnoDB by restarting the server with --default-storage-engine=InnoDB defined on the command line or with default-storage-engine=innodb defined in the [mysqld] section of the my.cnf configuration file.
如果InnoDB不是你默认的存储引擎，那么你就要确认你的数据库实例或者应用程序是否能够在InnoDB的情况下正确工作。修改默认存储引擎可以在实例启动的时候使用--default-storage-engine=InnoDB，或者在my.cnf配置文件的[mysqld]里面指定default-storage-engine=innodb。
Since changing the default storage engine only affects new tables as they are created, run all your application installation and setup steps to confirm that everything installs properly. Then exercise all the application features to make sure all the data loading, editing, and querying features work. If a table relies on some MyISAM-specific feature, you'll receive an error; add the ENGINE=MyISAM clause to the CREATE TABLE statement to avoid the error.
因为修改默认的存储引擎只会影响那些新建的表，所以在运行你应用程序的安装配置步骤的时候要确认每个安装步骤都是正确的。然后确保应用程序所有的数据加载，编辑，及查询都工作正常。如果表是依赖于MyISAM指定特性的话，你会接收到一个错误；那可以在CREATE TABLE的时候指定ENGINE=MyISAM来避免这个错误。
If you did not make a deliberate decision about the storage engine, and you just want to preview how certain tables work when they're created under InnoDB, issue the command ALTER TABLE table_name ENGINE=InnoDB; for each table. Or, to run test queries and other statements without disturbing the original table, make a copy like so:
如果你对存储引擎还不确认，只是想要预览某些个表在InnoDB环境下是怎么工作的，可以对每个表执行ALTER TABLE table_name ENGINE=InnoDB。又或者只是想在不改变原表分布的情况下检测查询或者其他的一些语句，可以像这样做一个拷贝：
CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;
To get a true idea of the performance with a full application under a realistic workload, install the latest MySQL server and run benchmarks.
如果想要在现实工作负载的情况下得到一个正确的性能意见，那么可以安装最新的MySQL并进行基准测试。
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.
从安装开始，通过大量的使用，并重启实例来测试整个应用程序的生命周期。在数据库正忙的时候kill实例进程来模仿电源故障，然后在重启实例的时候验证数据恢复得是否正确。
Test any replication configurations, especially if you use different MySQL versions and options on the master and the slaves.
验证任何的主从复制的配置，尤其是在master和slave之间使用了不同的MySQL版本和配置参数。
Verifying that InnoDB is the Default Storage Engine
To verify that InnoDB is the default storage engine:
验证InnoDB是否是默认的存储引擎：
l Issue the SHOW ENGINES command to view the different MySQL storage engines. Look for DEFAULT in the InnoDB line. Alternatively, query the INFORMATION_SCHEMA ENGINES table.
l 执行SHOW ENGINES查看不同的MySQL存储引擎。查看在InnoDB那一行的DEFAULT。另外啊，还可以查询INFORMATION_SCHEMA的ENGINES表。
l If InnoDB is not present, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one.
l 如果InnoDB是不存在的，那么可能你的mysqld二进制编译的时候没有支持InnoDB，那你就要重新获取一个了。
l If InnoDB is present but disabled, go back through your startup options and configuration file and get rid of any skip-innodb option.
l 如果InnoDB是存在的，但是被关闭了，那么就回到你的启动参数，配置文件来排除任何的skip-innodb参数。
14.1.2 Checking InnoDB Availability
To determine whether your server supports InnoDB, use the SHOW ENGINES statement. (Now that InnoDB is the default MySQL storage engine, only very specialized environments might not support it.)
为了确定你的实例是否支持InnoDB，可以使用SHOW ENGINES语句。（现在InnoDB是MySQL默认的存储引擎，之后特别特殊的环境才会不支持它。）
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.6, InnoDB is the default storage engine for new tables.
Oracle推荐InnoDB作为通常数据库应用的首选存储引擎，不论是本地系统的单用户的维科博客，还是推送到极致性能的高端应用。在MySQL5.6里，对于新的表InnoDB是默认的存储引擎。
If you do not want to use InnoDB tables:
如果你不想使用InnoDB表：
l Start the server with the --innodb=OFF or --skip-innodb option to disable the InnoDB storage engine.
l 在启动实例的时候使用--innodb=OFF或者--skip-innodb参数来关闭InnoDB存储引擎。
Note
As of MySQL 5.6.21, the --skip-innodb option still works but it is deprecated and will return a warning when used. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).
从MySQL5.6.21开始，--skip-innodb虽然仍然能够工作，但是这个参数已经是被废弃了的，而且使用的时候还会返回一个警告，在将来的MySQL版本里也会被移除。这种情况也适用于它的同义词(--innodb=OFF, --disable-innodb, and so forth)。
l Because the default storage engine is InnoDB, the server will not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.
l 因为默认的存储引擎是InnoDB，这是不会改变的除非在实例启动的时候使用了--default-storage-engine和--default-tmp-storage-engine把表的默认存储引擎改成其他的。
l To prevent the server from crashing when the InnoDB-related information_schema tables are queried, also disable the plugins associated with those tables. Specify in the [mysqld] section of the MySQL configuration file:
l 为了预防在查询InnoDB相关information_schema表的时候实例崩溃，可以关闭这些表的相关插件。在MySQL配置文件的[mysqld]里指定：
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0