InnoDB磁盘结构-表

Tables

Creating InnoDB Tables

要创建InnoDB表，请使用以下 CREATE TABLE语句。

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

如果InnoDB被定义为默认的存储引擎，则不需要指定ENGINE=InnoDB子句。要检查默认存储引擎，请发出以下语句：

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

如果您计划使用mysqldump或replication在默认存储引擎不是InnoDB的服务器上重做CREATE TABLE语句，那么仍然可以使用ENGINE=InnoDB子句。 

InnoDB表及其索引可以在 system tablespace, in a file-per-table tablespace, or in a general tablespace中创建。当innodb_file_per_table被启用（这是默认设置）时，innodb table被隐式地创建在单个file-per-table tablespace中。相反，当innodb_file_per_table被禁用时，innodb table被隐式地创建在innodb系统表空间中。要在常规表空间中创建表，请使用 CREATE TABLE ... TABLESPACE语法。For more information, see Section 14.6.3.3, “General Tablespaces”.

创建InnoDB表时，MySQL会在MySQL数据目录下的数据库目录中创建一个.frm文件。有关.frm文件的更多信息，请参阅InnoDB Tables和.frm files。对于在每个表空间的文件中创建的表，默认情况下，MySQL还会在数据库目录中创建一个.ibd表空间文件。在 InnoDB系统表空间中创建的表是在现有的 ibdata file中创建的，该文件位于MySQL数据目录中。在常规表空间中创建的表在现有的常规表空间  .ibd file 中创建。常规表空间文件可以在MySQL数据目录内部或外部创建。有关更多信息，请参见 第14.6.3.3节“常规表空间”。

在内部，InnoDB为InnoDB数据字典中的每个表添加一个条目。条目包括数据库名称。例如，如果表t1是在test数据库中创建的，则数据库名称的数据字典条目为“test/t1”。这意味着您可以在不同的数据库中创建一个同名（t1）的表，并且表名在InnoDB中不会发生冲突。

InnoDB Tables and .frm Files

MySQL在数据库目录的.frm文件中存储表的数据字典信息。与其他MySQL存储引擎不同，InnoDB还将表的信息编码到系统表空间中自己的内部数据字典中。当MySQL删除一个表或数据库时，它会删除一个或多个.frm文件以及InnoDB数据字典中相应的条目。不能简单地通过移动.frm文件在数据库之间移动InnoDB表。有关移动InnoDB表的信息，see Section 14.6.1.4, “Moving or Copying InnoDB Tables”.

InnoDB Tables and Row Formats

InnoDB表的默认行格式由InnoDB_default_row_format配置选项定义，该选项的默认值为DYNAMIC。动态和压缩行格式允许您利用InnoDB的特性，例如表压缩和长列值的高效页外存储。要使用这些行格式，必须启用innodb_file_per_table（MySQL 5.6.6的默认值），并且innodb_file_format必须设置为 Barracuda。

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=barracuda;
CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC;
CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;

或者，您可以使用 CREATE TABLE ... TABLESPACE在常规表空间中创建InnoDB表的表空间语法。常规表空间支持所有行格式。For more information, see Section 14.6.3.3, “General Tablespaces”.

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;

CREATE TABLE ... TABLESPACE语法还可用于在系统表空间中创建具有动态行格式的InnoDB表，以及具有压缩或冗余行格式的表。

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE = innodb_system ROW_FORMAT=DYNAMIC;

有关InnoDB行格式的更多信息， see Section 14.11, “InnoDB Row Formats”.对于如何确定InnoDB表的行格式和InnoDB行格式的物理特性，see Section 14.11, “InnoDB Row Formats”.

InnoDB Tables and Primary Keys

始终为InnoDB表定义一个 primary key，并指定一个或多个满足以下条件的列：

• 被最重要的查询引用。

• 永远不会空白。

• 永远不要有重复的值。

• 一旦插入，就很少更改值。

例如，在包含人员信息的表中，您不会在（firstname，last name）上创建主键，因为不止一个人可以有相同的名称，有些人的姓氏为空，有时人们会更改他们的姓名。由于有这么多约束，通常没有一组明显的列用作主键，因此您可以创建一个具有数字ID的新列作为主键的全部或部分。可以声明自动递增列，以便在插入行时自动填充升序值：

# The value of ID can act like a pointer between related items in different tables.
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

# The primary key can consist of more than one column. Any autoinc column must come first.
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

尽管表在不定义主键的情况下工作正常，但主键涉及到性能的许多方面，对于任何大型或常用表来说，它都是一个关键的设计方面。建议始终在CREATE TABLE语句中指定主键。如果创建表、加载数据，然后运行ALTER table在以后添加主键，则该操作比创建表时定义主键慢得多。

查看InnoDB表属性

要查看InnoDB表的属性，请发出一条SHOW TABLE STATUS 语句：

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-16 15:13:31
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

有关SHOW TABLE STATUS输出的信息，请参见 第13.7.5.36节“ SHOW TABLE STATUS语句”。

InnoDB还可以使用Information Schema系统表查询InnoDB表属性：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 45
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 35
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)

有关更多信息，请参见 第14.16.3节“ InnoDB INFORMATION_SCHEMA系统表”。

在外部创建表

在InnoDB 外部创建表有多种原因。也就是说，在数据目录之外创建表。例如，那些原因可能包括空间管理，I / O优化或将表放置在具有特定性能或容量特性的存储设备上。

InnoDB 支持以下用于在外部创建表的方法：

• 使用数据目录子句

• 使用CREATE TABLE ... TABLESPACE语法

• 在外部通用表空间中创建表

使用数据目录子句

通过在create table语句中指定DATA DIRECTORY 子句，可以在外部目录中创建InnoDB表。

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

在file-per-table表空间的文件中创建的表支持DATA DIRECTORY子句。当启用innodb_file_per_table变量时（默认情况下为该变量），将在 file-per-table tablespaces中隐式创建表。

mysql> SELECT @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+

有关file-per-table表空间文件的更多信息，请参阅第14.6.3.2节“file-per-table表空间文件”。

请确定您选择的目录位置，因为DATA directory子句不能与ALTER TABLE一起使用以稍后更改位置。

在CREATE TABLE语句中指定数据目录子句时，将在指定目录下的架构目录中创建表的数据文件（TABLE_name.ibd），并在MySQL数据目录下的架构目录中创建包含数据文件路径的.isl文件（TABLE_name.isl）。

.isl文件在功能上类似于符号链接。（不支持与InnoDB数据文件一起使用实际的符号链接。）

下面的示例演示如何使用DATA directory子句在外部目录中创建表。假定innodb_file_per_table变量已启用。

mysql> USE test;
Database changed

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

# MySQL creates the table's data file in a schema directory 
# under the external directory

shell> cd /external/directory/test
shell> ls
t1.ibd

# An .isl file that contains the data file path is created
# in the schema directory under the MySQL data directory

shell> cd /path/to/mysql/data/test
shell> ls
db.opt  t1.frm  t1.isl

使用说明：

• MySQL最初使表空间数据文件保持打开状态，以防止您卸下设备，但如果服务器繁忙，则可能最终关闭该文件。注意不要在运行MySQL时意外卸除外部设备，或在断开连接时启动MySQL。缺少关联数据文件时尝试访问表会导致严重错误，需要重新启动服务器。

如果在预期路径中找不到数据文件，则服务器重新启动可能会失败。在这种情况下，请从架构目录中手动删除.isl文件。重新启动后，删除表以从数据字典中删除.frm文件和有关该表的信息。

• 在将表放在NFS装载的卷上之前，请查看将NFS与MySQL结合使用 Using NFS with MySQL中概述的潜在问题。
• 如果使用LVM快照、文件副本或其他基于文件的机制备份表的数据文件，请始终使用 FLUSH TABLES ... FOR EXPORT 语句首先确保在备份发生之前将内存中缓冲的所有更改刷新到磁盘。
• 使用DATA DIRECTORY子句在外部目录中创建表的替代方法是使用符号链接 symbolic links，InnoDB不支持这种方法。

使用CREATE TABLE ... TABLESPACE语法

CREATE TABLE ... TABLESPACE语法可以与该DATA DIRECTORY子句结合使用， 以在外部目录中创建表。为此，将 innodb_file_per_table指定为表空间名称。

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
       DATA DIRECTORY = '/external/directory';

此方法仅支持在file-per-table tablespaces中创建的表，但不要求启用innodb_file_per_table变量。在所有其他方面，此方法等同于CREATE TABLE ... DATA DIRECTORY 上面描述的方法。同样的用法说明也适用。

在外部通用表空间中创建表

您可以在位于外部目录中的常规表空间中创建表。

• 有关在外部目录中创建常规表空间的信息，请参见 创建常规表空间。

• 有关在常规表空间中创建表的信息，请参见 将表添加到常规表空间。

导入InnoDB表

本节介绍如何使用 Transportable Tablespaces 特性导入表，该特性允许导入表、分区表或驻留在每个表空间的文件中的单个表分区。您可能希望导入表的原因有很多：

• 在非生产MySQL服务器实例上运行报告，以避免在生产服务器上增加额外的负载。
• 将数据复制到新的从属服务器。
• 从备份的表空间文件还原表。
• 与导入转储文件相比，移动数据是一种更快的方法，这需要重新插入数据和重建索引。
• 使用适合您的存储要求的存储介质将数据移动到服务器。例如，您可以将繁忙的表移至SSD设备，或将大型表移至高容量HDD设备。

 Transportable Tablespaces 功能在本节的以下主题中进行了说明：

• 先决条件

• 导入表格

• 导入分区表

• 导入表分区

• 局限性

• 使用说明

• 内部构造

先决条件

• innodb_file_per_table 变量必须启用，默认情况下为启用。
• 表空间的页面大小必须与目标MySQL服务器实例的页面大小匹配。 InnoDB页面大小由innodb_page_size变量定义，该 变量在初始化MySQL服务器实例时配置。
• 如果表处于外键关系中，则在执行放弃表空间 （DISCARD TABLESPACE）之前必须禁用外键检查 foreign_key_checks 。另外，您应该在同一逻辑时间点导出所有与外键相关的表，如ALTER TABLE ... IMPORT TABLESPACE不对导入的数据强制外键约束。为此，请停止更新相关表，提交所有事务，获取表上的共享锁，并执行导出操作。
• 从另一个MySQL服务器实例导入表时，这两个MySQL服务器实例必须具有通用可用性（GA）状态，并且必须是相同的版本。否则，必须在导入表的同一MySQL服务器实例上创建该表。
• 如果该表是通过在CREATETABLE语句中指定 DATA DIRECTORY子句在外部目录中创建的，则必须使用相同的 DATA DIRECTORY子句定义在目标实例上替换的表。如果子句不匹配，则报告架构不匹配错误。要确定源表是否使用 DATA DIRECTORY子句定义，请使用SHOW CREATE TABLE查看表定义。有关使用DATA DIRECTORY子句的信息， see Section 14.6.1.2, “Creating Tables Externally”.
• 如果在表定义中未显式定义ROW_FORMAT选项，或者使用ROW_FORMAT=DEFAULT，则源实例和目标实例上的 innodb_default_row_format 设置必须相同。否则，尝试导入操作时将报告架构不匹配错误。使用 SHOW CREATE TABLE 检查表定义。使用SHOW VARIABLES检查 innodb_default_row_format 设置。有关相关信息，请参见 Defining the Row Format of a Table。

导入数据表

此示例演示如何导入驻留在每个表文件的表空间中的常规非分区表。

• 在目标实例上，创建一个定义与要导入的表相同的表。（可以使用 SHOW CREATE TABLE语法获取表定义。）如果表定义不匹配，则在尝试导入操作时将报告架构不匹配错误。

 

mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;

• 在目标实例上，放弃刚刚创建的表的表空间。（导入之前，必须放弃接收表的表空间。）

ALTER TABLE t1 DISCARD TABLESPACE;

• 在源实例上，运行 FLUSH TABLES ... FOR EXPORT 以使要导入的表静止。当表静止时，表上只允许只读事务。

mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;

FLUSH TABLES ... FOR EXPORT确保已将对命名表的更改刷新到磁盘，以便在服务器运行时可以进行二进制表复制。 当FLUSH TABLES ... FOR EXPORT 运行时，InnoDB在表的schema目录中生成一个.cfg元数据文件。.cfg文件包含在导入操作期间用于架构验证的元数据。

• 将.ibd文件和.cfg元数据文件从源实例复制到目标实例。比如：

shell> scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test

必须在释放共享锁之前复制.ibd文件和.cfg文件，如下一步所述。

Note

如果要从加密表空间导入表，InnoDB除了生成.cfg元数据文件外，还会生成一个.cfp文件。.cfp文件必须与.cfg文件一起复制到目标实例。.cfp文件包含一个传输密钥和一个加密的表空间密钥。在导入时，InnoDB使用传输密钥来解密表空间密钥。For related information, see Section 14.14, “InnoDB Data-at-Rest Encryption”.

• 在源实例上，使用UNLOCK TABLES释放 FLUSH TABLES ... FOR EXPORT语句获取的锁：

mysql> USE test;
mysql> UNLOCK TABLES;

• 关于目的地实例，导入表空间：

mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;

移动或复制InnoDB表

本节介绍将某些或所有InnoDB表移动或复制 到其他服务器或实例的技术。例如，您可以将整个MySQL实例移至更大，速度更快的服务器上。您可以将整个MySQL实例克隆到新的复制从属服务器；您可以将单个表复制到另一个实例以开发和测试应用程序，或复制到数据仓库服务器以生成报告。

在Windows上，InnoDB总是在内部以小写形式存储数据库和表名。要将二进制格式的数据库从Unix移动到Windows或从Windows移动到Unix，请使用小写名称创建所有数据库和表。一种方便的方法是在创建任何数据库或表之前，将以下行添加到my.cnf或my.ini文件的[mysqld]部分：

[mysqld]
lower_case_table_names=1

移动或复制InnoDB表的技术包括：

• 导入表格

• MySQL企业备份

• 复制数据文件（冷备份方法）

• 从逻辑备份还原

导入表格

可以使用可移植表空间功能从另一个MySQL服务器实例或从备份导入表空间中的每个表文件。See Section 14.6.1.3, “Importing InnoDB Tables”.

MySQL企业备份

MySQL Enterprise Backup产品使您可以在不影响操作的情况下备份正在运行的MySQL数据库，同时生成一致的数据库快照。当MySQL Enterprise Backup复制表时，读取和写入可以继续。此外，MySQL Enterprise Backup可以创建压缩备份文件，并备份表的子集。结合MySQL二进制日志，您可以执行时间点恢复。MySQL Enterprise Backup是MySQL Enterprise订阅的一部分。

有关MySQL企业备份的更多详细信息，请参见 第29.2节“ MySQL企业备份概述”。

复制数据文件（冷备份方法）

只需复制 Section 14.19.1, “InnoDB Backup”.中“冷备份”下列出的所有相关文件，就可以移动InnoDB数据库。

InnoDB数据和日志文件在具有相同浮点数格式的所有平台上都是二进制兼容的。如果浮点格式不同，但表中没有使用FLOAT或DOUBLE数据类型，则过程相同：只需复制相关文件。

在按表.ibd文件移动或复制文件时，源系统和目标系统上的数据库目录名必须相同。存储在InnoDB共享表空间中的表定义包括数据库名。因此，存储在表空间文件中的事务id和日志序列号在数据库之间是不同的。

要将.ibd文件和关联的表从一个数据库移动到另一个数据库，请使用 RENAME TABLE语句：

RENAME TABLE db1.tbl_name TO db2.tbl_name;

 如果您有一个.ibd文件的“干净”备份，您可以将其还原到MySQL安装，它的起源如下：

• 复制.ibd文件后，不能删除或截断表，因为这样做会更改存储在表空间中的表ID。
• 发出此 ALTER TABLE 语句以删除当前的.ibd文件：

ALTER TABLE tbl_name DISCARD TABLESPACE;

• 将backup.ibd文件复制到正确的数据库目录。
• 发出这个ALTER TABLE语句，告诉InnoDB为表使用新的.ibd文件：

ALTER TABLE tbl_name IMPORT TABLESPACE;

Note

 ALTER TABLE ... IMPORT TABLESPACE功能不对导入的数据强制外键约束。

在此上下文中，“clean”.ibd文件备份”满足以下要求：

• .ibd文件 中没有事务未提交的修改 。

• 该文件中没有未合并的插入缓冲区条目 .ibd。

• 清除已从.ibd文件中删除所有删除标记的索引记录 。

• mysqld已将.ibd文件的所有修改页从缓冲池刷新到文件。

您可以使用以下方法创建一个干净的备份.ibd文件：

1. 停止mysqld服务器上的所有活动并提交所有事务。
2. 等到 SHOW ENGINE INNODB STATUS 显示数据库中没有活动事务，并且INNODB的主线程状态正在等待服务器活动。然后可以复制.ibd文件。

制作.ibd文件干净副本的另一种方法是使用MySQL企业备份产品：

1. 使用MySQL Enterprise back up备份InnoDB安装。
2. 在备份上启动第二个mysqld服务器，让它清理备份中的.ibd文件。

从逻辑备份还原

您可以使用诸如mysqldump之类的实用程序执行逻辑备份，该备份将生成一组SQL语句，这些语句可用于复制原始数据库对象定义和表数据，以便传输到另一个SQL服务器。使用此方法，格式是否不同或表是否包含浮点数据并不重要。

要提高此方法的性能，请在导入数据时禁用autocommit。仅在导入整个表或表段后执行提交。

将表从MyISAM转换为InnoDB

如果有要转换为InnoDB以获得更好的可靠性和可伸缩性的MyISAM表，请在转换之前查看以下指南和提示。

• 调整MyISAM和InnoDB的内存使用量

• 处理太长或太短的事务

• 处理死锁

• 规划存储布局

• 转换现有表

• 克隆表的结构

• 传输现有数据

• 储存要求

• 为每个表定义一个主键

• 应用程序性能注意事项

• 了解与InnoDB表关联的文件

调整MyISAM和InnoDB的内存使用量

从MyISAM表转换时，降低key_buffer_size配置选项的值，以释放缓存结果不再需要的内存。增加innodb_buffer_pool_size配置选项的值，该选项执行类似的角色，为innodb表分配缓存。InnoDB缓冲池缓存表数据和索引数据，加速查询查找，并将查询结果保存在内存中以供重用。有关缓冲池大小配置的指导，请参阅 Section 8.12.4.1, “How MySQL Uses Memory”.

在繁忙的服务器上，在查询缓存关闭的情况下运行基准测试。InnoDB缓冲池提供了类似的好处，因此查询缓存可能会不必要地占用内存。有关查询缓存的信息， Section 8.10.3, “The MySQL Query Cache”.

处理太长或太短的事务

因为MyISAM表不支持事务，所以您可能没有太多注意autocommit配置选项以及COMMIT和ROLLBACK语句。这些关键字对于允许多个会话同时读写InnoDB表非常重要，在写繁重的工作负载中提供了巨大的可伸缩性优势。

当事务处于打开状态时，系统会保留在事务开始时看到的数据快照，如果系统在杂散事务继续运行时插入、更新和删除数百万行，则可能会导致大量开销。因此，请注意避免运行时间过长的事务：

• 如果使用mysql会话进行交互式实验，请在完成后始终提交（以完成更改）或回滚（以撤消更改）。关闭交互式会话而不是让它们长时间打开，以避免意外地使事务长时间打开。
• 确保应用程序中的任何错误处理程序也回滚o ROLLBACK未完成的更改或提交 COMMIT 已完成的更改。
• 回滚是一个相对昂贵的操作，因为INSERT、UPDATE和DELETE操作是在提交之前写入到InnoDB表中的，期望大多数更改都能成功提交，回滚很少。在尝试使用大量数据时，请避免对大量行进行更改，然后回滚这些更改。
• 当使用一系列INSERT语句加载大量数据时，请定期提交结果，以避免事务持续数小时。在数据仓库的典型加载操作中，如果发生错误，您将截断表（使用truncate table）并从头开始，而不是执行回滚。

前面的提示节省了内存和磁盘空间，在太长的事务中可能会浪费这些空间。当事务比应该的时间短时，问题是I/O过多。每次提交时，MySQL都会确保将每个更改安全地记录到磁盘，这涉及到一些I/O。

• 对于InnoDB表上的大多数操作，应该使用设置autocommit=0。从效率的角度来看，这避免了在发出大量连续的INSERT、UPDATE或DELETE语句时不必要的I/O。从安全的角度来看，如果在mysql命令行或应用程序中的异常处理程序中出错，这允许您发出ROLLBACK语句来恢复丢失或混乱的数据。
• autocommit=1适用于InnoDB表的时间是运行一系列查询以生成报告或分析统计数据的时间。在这种情况下，没有与提交或回滚相关的I/O判罚，InnoDB可以自动优化只读工作负载。
• 如果您进行了一系列相关的更改，请在最后一次提交后立即完成所有更改。例如，如果将相关信息插入多个表中，则在进行所有更改后执行一次提交。或者，如果运行多个连续的INSERT语句，则在加载所有数据后执行一次COMMIT；如果正在执行数百万条INSERT语句，则可能通过每隔一万或十万条记录发出COMMIT来拆分庞大的事务，因此事务不会变得太大。
• 请记住，即使SELECT语句也会打开一个事务，因此在交互式mysql会话中运行一些报表或调试查询之后，要么发出COMMIT，要么关闭mysql会话。

处理死锁

您可能会在MySQL错误日志中看到有关“死锁”的警告消息，或者 SHOW ENGINE INNODB STATUS的输出。尽管名称听起来很吓人，但死锁对于InnoDB表来说并不是一个严重的问题，而且通常不需要任何纠正措施。当两个事务开始修改多个表，并以不同的顺序访问这些表时，它们可以达到每个事务都在等待另一个事务而两个事务都无法继续的状态。当启用死锁检测 deadlock detection（默认设置）时，MySQL会立即检测到这种情况，并取消（rolls back）“较小”的事务，允许另一个事务继续进行。如果使用innodb_deadlock_detect配置选项禁用死锁检测，innodb将依赖innodb_lock_wait_timeout设置在死锁情况下回滚事务。

不管怎样，应用程序都需要错误处理逻辑来重新启动由于死锁而被强制取消的事务。当您重新发出与以前相同的SQL语句时，原来的计时问题不再适用。另一个事务已经完成，您的事务可以继续，或者另一个事务仍在进行中，您的事务将一直等到它完成。

如果死锁警告不断出现，您可以查看应用程序代码，以一致的方式重新排序SQL操作，或者缩短事务。您可以使用 innodb_print_all_deadlocks选项进行测试，以查看MySQL错误日志中的所有死锁警告，而不仅仅是 SHOW ENGINE INNODB STATUS输出中的最后一个警告。

For more information, see Section 14.7.5, “Deadlocks in InnoDB”.

规划存储布局

为了从InnoDB表中获得最佳性能，您可以调整许多与存储布局相关的参数。

当您转换大型、频繁访问且保存重要数据的MyISAM表时，请研究并考虑 innodb_file_per_table, innodb_file_format, and innodb_page_size配置选项，以及 CREATE TABLE 语句的 ROW_FORMAT和KEY_BLOCK_SIZE子句。

在最初的实验中，最重要的设置是innodb_file_per_table。启用此设置（从MySQL 5.6.6开始为默认设置）后，将在每个表空间的文件中隐式创建新的InnoDB表。与InnoDB系统表空间不同，当表被截断或删除时，每个表空间的文件允许操作系统回收磁盘空间。 File-per-table表空间还支持 Barracuda文件格式和相关功能，如表压缩、长可变长度列的高效页外存储和大索引前缀。For more information, see Section 14.6.3.2, “File-Per-Table Tablespaces”.

您还可以将InnoDB表存储在共享的通用表空间中。通用表空间支持Barracuda文件格式，可以包含多个表。 For more information, see Section 14.6.3.3, “General Tablespaces”.

转换现有表

要将非InnoDB表转换为InnoDB使用 ALTER TABLE，请执行以下操作：

ALTER TABLE table_name ENGINE=InnoDB;

Warning

不要将 mysql数据库中的MySQL系统表从MyISAM转换为InnoDB表。这是不支持的操作。如果这样做，MySQL不会重新启动，除非您从备份中还原旧的系统表，或者通过重新初始化数据目录来重新生成它们 (see Section 2.10.1, “Initializing the Data Directory”).

克隆表的结构

您可以创建一个InnoDB表，它是MyISAM表的克隆，而不是使用 ALTER TABLE来执行转换，在切换之前并排测试新旧表。创建一个具有相同列和索引定义的空InnoDB表。使用SHOW CREATE TABLE TABLE_name\G查看使用的完整CREATE TABLE语句。将ENGINE子句更改为ENGINE=INNODB。

传输现有数据

要将大量数据传输到如前一节所示创建的空InnoDB表中，请使用 INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.插入行。

也可以在插入数据之后为InnoDB表创建索引。历史上，对于InnoDB来说，创建新的二级索引是一个缓慢的操作，但是现在您可以在加载数据之后创建索引，而从索引创建步骤开始，开销相对较小。

如果对次关键字有唯一约束，则可以通过在导入操作期间暂时关闭唯一性检查来加快表导入：

SET unique_checks=0;... import operation ...
SET unique_checks=1;

对于大表，这可以节省磁盘I/O，因为InnoDB可以使用它的更改缓冲区将辅助索引记录作为批处理写入。确保数据不包含重复的键。unique_checks 许可，但不要求存储引擎忽略重复密钥。

为了更好地控制插入过程，可以将大表分片插入：

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

插入所有记录后，可以重命名表。

在大表的转换过程中，增加NIANDB缓冲池的大小，以减少磁盘I/O，最多达到物理内存的80%。您还可以增加InnoDB日志文件的大小。

储存要求

如果您打算在转换过程中对InnoDB表中的数据进行几个临时拷贝，建议您在file-per-table tablespaces中创建表，以便在删除表时回收磁盘空间。当 innodb_file_per_table 选项被启用（默认设置）时，新创建的innodb tables将隐式地在file-per-table tablespaces中创建。

无论是直接转换MyISAM表还是创建一个克隆的InnoDB表，都要确保有足够的磁盘空间来保存这个过程中的旧表和新表。InnoDB表需要比MyISAM表更多的磁盘空间。如果ALTER TABLE操作空间不足，它将启动回滚，如果它是磁盘绑定的，则可能需要几个小时。对于插入，InnoDB使用插入缓冲区将二级索引记录批量合并到索引中。这样可以节省大量的磁盘I/O。对于回滚，不使用这种机制，回滚可能需要比插入长30倍的时间。

在失控回滚的情况下，如果数据库中没有有价值的数据，建议终止数据库进程，而不是等待数百万个磁盘I/O操作完成。For the complete procedure, see Section 14.22.2, “Forcing InnoDB Recovery”.

为每个表定义一个主键

 PRIMARY KEY 子句是影响MySQL查询性能和表和索引空间使用的关键因素。主键唯一标识表中的行。表中的每一行都必须有主键值，并且任何两行都不能有相同的主键值。

以下是主键的指导原则，随后是更详细的解释。

• 为每个表声明主键。通常，在查找单行时，它是WHERE子句中引用的最重要的列。
• 在原始CREATETABLE语句中声明PRIMARYKEY子句，而不是稍后通过ALTERTABLE语句添加它。
• 请仔细选择列及其数据类型。与字符或字符串列相比，更喜欢数字列。
• 如果没有其他要使用的稳定、唯一、非空的数值列，请考虑使用自动递增列。
• 如果对主键列的值是否可以更改有任何疑问，那么自动递增列也是一个不错的选择。更改主键列的值是一项代价高昂的操作，可能涉及在表内和每个辅助索引内重新排列数据。

考虑将主键添加到任何还没有主键的表中。根据表的最大投影大小使用最小实用数值类型。这可以使每一行稍微紧凑一点，从而为大型表节省大量空间。如果表有任何辅助索引，则节省的空间将成倍增加，因为主键值在每个辅助索引项中重复。除了减少磁盘上的数据大小之外，一个小主键还可以让更多的数据放入缓冲池，从而加快各种操作并提高并发性。

如果表在某个较长的列（如VARCHAR）上已经有主键，请考虑添加一个新的无符号自动递增列并将主键切换到该列，即使查询中未引用该列。这种设计更改可以在二级索引中节省大量空间。您可以将以前的主键列指定为UNIQUE NOT NULL，以强制执行与主键子句相同的约束，即防止所有这些列中出现重复或空值。

如果将相关信息分散到多个表中，通常每个表的主键都使用同一列。例如，人员数据库可能有几个表，每个表的主键都是employee number。销售数据库可能包含一些主键为customer number的表，以及其他主键为order number的表。因为使用主键的查找非常快，所以可以为此类表构造有效的连接查询。

如果完全不使用主键子句，MySQL会为您创建一个不可见的子句。它是一个6字节的值，可能比您需要的长，从而浪费空间。因为它是隐藏的，所以不能在查询中引用它。

应用程序性能注意事项

InnoDB的可靠性和可伸缩性特性需要比等效MyISAM表更多的磁盘存储空间。您可以稍微更改列和索引定义，以获得更好的空间利用率，减少处理结果集时的I/O和内存消耗，以及更好的查询优化计划，从而有效地使用索引查找。

如果确实为主键设置了数字ID列，请使用该值与任何其他表中的相关值交叉引用，特别是对于联接查询。例如，与其接受一个国家名称作为输入并执行查询以搜索相同的名称，不如执行一次查找以确定国家ID，然后执行其他查询（或单个联接查询）以跨多个表查找相关信息。与其将客户号或目录项号存储为一串数字（可能会占用几个字节），不如将其转换为数字标识以进行存储和查询。一个4字节的无符号INT列可以索引超过40亿个项（美国的意思是十亿：1000一百万）。

了解与InnoDB表关联的文件

InnoDB文件比MyISAM文件需要更多的关注和计划。

1. 不能删除表示InnoDB系统表空间的ibdata文件。
2.  Section 14.6.1.4, “Moving or Copying InnoDB Tables”描述了将InnoDB表移动或复制到不同服务器的方法。

InnoDB中的AUTO_INCREMENT处理

InnoDB提供了一种可配置的锁定机制，可以显著提高SQL语句的可伸缩性和性能，这些SQL语句向具有自动递增列的表中添加行。若要使用 InnoDB表的 AUTO_INCREMENT 增量机制， AUTO_INCREMENT列必须定义为索引的一部分，以便在表上执行索引 SELECT MAX(ai_col) 查找的等价物，以获得最大列值。通常，这是通过使列成为某个表索引的第一列来实现的。

本节描述自动递增锁定模式的行为，不同自动递增锁定模式设置的使用含义，以及InnoDB如何初始化自动递增计数器。

• InnoDB AUTO_INCREMENT锁定模式

• InnoDB AUTO_INCREMENT锁定模式的用法含义

• InnoDB AUTO_INCREMENT计数器初始化

• 笔记

InnoDB AUTO_INCREMENT锁定模式

本节介绍用于生成自动增量值的自动增量锁定模式的行为，以及每个锁定模式如何影响复制。自动增量锁定模式在启动时使用innodb_autoinc_lock_mode配置参数进行配置。

以下术语用于描述innodb_autoinc_lock_模式设置：

• “INSERT-like” statements

在表中生成新行的所有语句，包括 INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA。包括“简单插入”、“大容量插入”和“混合模式”插入。

• “Simple inserts”

可以预先确定要插入行数的语句（在最初处理该语句时）。这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不是 INSERT ... ON DUPLICATE KEY UPDATE。

• “Bulk inserts”

要插入的行数（以及所需的自动增量值）事先未知的语句。这包括 INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA 语句，但不是简单的插入。InnoDB在处理每一行时，一次为AUTO_INCREMENT列分配一个新值。

• “Mixed-mode inserts”

这些是“简单插入”语句，为某些（但不是全部）新行指定自动增量值。下面是一个示例，其中c1是表t1的自动递增列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE时，在最坏的情况下是插入后更新，其中自动递增列的分配值可以在更新阶段使用，也可以不使用。

innodb_autoinc_lock_mode配置参数有三种可能的设置。“传统”、“连续”或“交错”锁定模式的设置分别为0、1或2。

• innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

这是默认的锁定模式。在这种模式下，“批量插入”使用特殊的AUTO-INC表级锁并将其保持到语句结束。这适用于所有INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. 一次只能执行一个持有AUTO-INC锁的语句。如果大容量插入操作的源表与目标表不同，则在从源表中选择的第一行上获取共享锁后，将获取目标表上的AUTO-INC锁。如果大容量插入操作的源和目标是同一个表，则在对所有选定行执行共享锁之后，将执行AUTO-INC锁。

“简单插入”（要插入的行数预先知道）通过在互斥锁（一个轻量级锁）的控制下获得所需的自动增量值来避免表级的AUTO-INC锁，该互斥锁只在分配过程中保持，而不是在语句完成之前。除非AUTO-INC锁由另一个事务持有，否则不使用表级AUTO-INC锁。如果另一个事务持有AUTO-INC锁，“简单插入”会等待AUTO-INC锁，就好像它是“大容量插入”。

这种锁定模式确保，在INSERT语句存在的情况下，如果事先不知道行数（并且在语句进行时分配了自动增量号），则任何“INSERT like”语句分配的所有自动增量值都是连续的，并且操作对于基于语句的复制是安全的。

简单地说，这种锁定模式显著地提高了可伸缩性，同时可以安全地用于基于语句的复制。此外，与“传统”锁定模式一样，由任何给定语句指定的自动递增数都是连续的。与“传统”模式相比，任何使用自动增量的语句的语义都没有变化，但有一个重要的例外。

“混合模式插入”除外，用户为多行“简单插入”中的某些行（而不是所有行）的自动增量列提供显式值。对于这种插入，InnoDB分配的自动增量值比要插入的行数还要多。但是，自动分配的所有值都是连续生成的（因此高于）由最近执行的前一条语句生成的自动增量值。“多余”的数字会丢失。

• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

在这种锁模式下，没有“INSERT like”语句使用表级AUTO-INC锁，并且可以同时执行多个语句。这是最快、最可扩展的锁定模式，但当从二进制日志中重放SQL语句时，使用基于语句的复制或恢复方案时，这是不安全的。

在这种锁定模式下，自动增量值保证在所有并发执行的“类插入”语句中是唯一的和单调递增的。但是，由于多个语句可以同时生成数字（即，数字的分配是跨语句交叉的），因此为任何给定语句插入的行生成的值可能不是连续的。

如果执行的唯一语句是“简单插入”，其中要插入的行数是提前知道的，则为单个语句生成的数字中没有间隙，除了“混合模式插入”。但是，当执行“大容量插入”时，任何给定语句指定的自动增量值中都可能有间隙。