15.2. InnoDB存储引擎
15.2.1. InnoDB概述
15.2.2. InnoDB联系信息
15.2.3. InnoDB配置
15.2.4. InnoDB启动选项
15.2.5. 创建InnoDB表空间
15.2.6. 创建InnoDB表
15.2.7. 添加和删除InnoDB数据和日志文件
15.2.8. InnoDB数据库的备份和恢复atabase
15.2.9. 把InnoDB数据库移到另一台机器上
15.2.10. InnoDB事务模型和锁定
15.2.11. InnoDB性能调节提示
15.2.12. 多版本的实施
15.2.13. 表和索引结构
15.2.14.文件空间管理和磁盘I/O
15.2.15. InnoDB错误处理
15.2.16. 对InnoDB表的限制
15.2.17. InnoDB故障诊断和排除
15.2.1. InnoDB概述
InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。
InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。
InnoDB在和MySQL一样在同一个GNU GPL证书，第2版（1991年6月版）下发行。更多有关MySQL证书的信息，请参阅http://www.mysql.com/company/legal/licensing/。
关于InnoDB存储引擎，在http://forums.mysql.com/list.php?22有一个详细的论坛。
15.2.2. InnoDB联系信息
InnoDB引擎的厂家的联系信息，Innobase Oy的联系方式如下：
Web site: http://www.innodb.com/
Email: <sales@innodb.com>
Phone: +358-9-6969 3250 (office)
+358-40-5617367 (mobile)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland
15.2.3. InnoDB配置
InnoDB存储引擎是默认地被允许的。如果你不想用InnoDB表，你可以添加skip-innodb选项到MySQL选项文件。
被InnoDB存储引擎管理的两个重要的基于磁盘的资源是InnoDB表空间数据文件和它的日志文件。
如果你指定无InnoDB配置选项，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。
注释：InnoDB给MySQL提供具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。如果拟运行的操作系统和硬件不能如广告说的那样运行，InnoDB就不能实现如上能力。许多操作系统或磁盘子系统可能为改善性能而延迟或记录写操作。在一些操作系统上，就是系统调用（fsync()） 也要等着，直到所有未写入已被刷新文件的数据在被刷新到稳定内存之前可以确实返回了。因为这个，操作系统崩溃或掉电可能损坏当前提交的数据，或者在最坏的 情况，因为写操作已被记录了，甚至破坏了数据库。如果数据完整性对你很重要，你应该在用任何程序于生产中之前做一些“pull-the-plug”测试。Mac OS X 10.3 及以后版本，InnoDB使用一个特别的fcntl()文件刷新方法。在Linux下，建议禁止回写缓存。
在ATAPI硬盘上，一个类似hdparm -W0 /dev/hda命令可能起作用。小心某些驱动器或者磁盘控制器可能不能禁止回写缓存。
注释：要获得好的性能，你应该如下面例子所讨论那样，明确提供InnoDB参数。自然地，你应该编辑设置来适合你的硬件和要求。
要建立InnoDB表空间文件，在my.cnf选项文件里的[mysqld]节里使用innodb_data_file_path选项。在Windows上，你可以替代地使用my.ini文件。innodb_data_file_path的值应该为一个或多个数据文件规格的列表。如果你命名一个以上的数据文件，用分号(‘;’)分隔它们：
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
例如：把明确创建的具有相同特征的表空间作为默认设置的设置操作如下：
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。
尺寸大小用M或者G后缀来指定说明单位是MB或者GB。
一个表空间，它在数据目录里包含一个名为ibdata1的固定尺寸50MB的数据文件和一个名为ibdata2大小为50MB的自动扩展文件，其可以像这样被配置：
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
一个指定数据文件的完全后缀包括文件名，它的尺寸和数个可选属性：
file_name:file_size[:autoextend[:max:max_file_size]]
autoextend属性和后面跟着的属性只可被用来对innodb_data_file_path行里最后一个数据文件。
如果你对最后的数据文件指定autoextend选项。如果数据文件耗尽了表空间中的自由空间，InnoDB就扩展数据文件。扩展的幅度是每次8MB。
如果磁盘已满，你可能想要把其它数据添加到另一个硬盘上。重新配置一个已存在表空间的指令见15.2.7节，“添加和删除InnoDB数据和日志文件”。
InnoDB并不感知最大文件尺寸，所以要小心文件系统，在那上面最大的文件尺寸是2GB。要为一个自动扩展数据文件指定最大尺寸，请使用max属性。下列配置允许ibdata1涨到极限的500MB：
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB默认地在MySQL数据目录创建表空间文件。要明确指定一个位置，请使用innodb_data_home_dir选项。比如，要使用两个名为ibdata1和ibdata2的文件，但是要把他们创建到/ibdata，像如下一样配置InnoDB：
[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
注释：InnoDB不创建目录，所以在启动服务器之前请确认/ibdata目录的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。
通过把innodb_data_home_dir的值原原本本地部署到数据文件名，并在需要的地方添加斜杠或反斜杠，InnoDB为每个数据文件形成目录路径。如果innodb_data_home_dir选项根本没有在my.cnf中提到，默认值是“dot”目录 ./，这意思是MySQL数据目录。
如果你指定innodb_data_home_dir为一个空字符串，你可以为列在innodb_data_file_path值里的数据文件指定绝对路径。下面的例子等价于前面那个例子：
[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
一个简单的my.cnf例子。假设你有一台配备128MB内存和一个硬盘的计算机。下面的例子显示在my.cnf或my.ini里对InnoDB可能的配置参数，包括autoextend属性。
这个例子适合大多数在Unix和Windows上，不想分配InnoDB数据文件和日志文件到数个磁盘上的用户。它在MySQL数据目录创建一个自动扩展数据文件ibdata1和两个日志文件ib_logfile0及ib_logfile1。同样，InnoD在数据目录里自动创建的小型档案InnoDB日志文件ib_arch_log_0000000000也结束。
[mysqld]
# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
请确认MySQL服务器有适当的权限在数据目录里创建文件。更一般地，服务器必须对任何它需要创建数据文件或日志文件的目录有访问权限。
注意，在一些文件系统上，数据文件必需小于2GB。数据文件的合并尺寸必须至少10MB。
当你第一次创建一个InnoDB表空间，最好从命令行来启动MySQL服务器。InnoDB然后把数据库创建的信息打印到屏幕，所以你可以看见正在发生什么。比如，在Windows上，如果mysqld-max位于C:\mysql\bin，你可以如下来启动它：
C:\> C:\mysql\bin\mysqld-max --console
如果你不发送服务器输出到屏幕上，请检查服务器的错误日志来看在启动过程中InnoDB打印了什么。
请参阅15.2.5节，“创建InnoDB表空间”，以获得InnoDB显示的信息看起来应该像什么的例子。
Windows上如何指定选项? 在Windows上选项文件的规则如下：
·只应该创建一个my.cnf或文件。
·my.cnf文件应该被放在C盘根目录。
·my.ini文件应该被放置在WINDIR目录；例如C:\WINDOWS或C:\WINNT。你可以在Windows控制台的命令提示符使用SET命令来打印WINDIR的值：
·C:\> SET WINDIR
·windir=C:\WINNT
·如果你的PC在C盘不是启动盘的地方使用启动装载机，你唯一的选择是使用my.ini文件。
· 如果你使用安装和配置向导安装的MySQL，my.ini文件被放在MySQL的安装目录。请参阅2.3.5.14节，“my.ini文件的位置”。
Unix上在哪里指定选项？ 在Unix上，mysqld从下列文件，如果它们存在的话。以下列的顺序读取选项：
·/etc/my.cnf
全局选项。
·$MYSQL_HOME/my.cnf
服务器专用选项。
·defaults-extra-file
--defaults-extra-file选项指定的文件。
·~/.my.cnf
用户专用选项。
MYSQL_HOME代表环境变量，它内含着到包含服务器专用my.cnf文件的目录的路径。
如果你确信mysqld只从指定文件读取选项，你可以在启动服务器之时在命令行使用--defaults-option作为第一个选项：
mysqld --defaults-file=your_path_to_my_cnf
一个高级的my.cnf例子。假设你有一台Linux计算机，有2GB内存和三个60GB硬盘（在目录路径/, /dr2和/dr3)。下列例子显示了在my.cnf里对InnoDB可能的配置参数。
[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Data files must be able to hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
innodb_log_files_in_group = 2
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
#
# Uncomment the next lines if you want to use them
#innodb_thread_concurrency=5
注意，该例子把两个数据文件放在不同磁盘上。InnoDB开始用第一个数据文件填充表空间。在一些情况下，如果所有数据不被放置在同一物理磁盘上，这样将改善数据库的性能。把日志文件放在与数据文件不同的磁盘上对性能是经常很有好处的。你也可以使用原始磁盘分区（原始设备）作为InnoDB数据文件，这样可以加速I/O。请参阅15.2.14.2节，“为表空间使用原始设备”。
警告：在32位GNU/Linux x86上,你必须要小心不要设置过高的内存用量。glibc可能允许进程堆积在线程堆栈上发展，它会造成你的服务器崩溃。如果下列表达式的值接近或者超过2GB，系统会面临危机：
innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB
每个线程使用一个堆栈（通常是2MB，但在MySQL AB二进制分发版里只有256KB）并且在最坏的情况下也使用sort_buffer_size + read_buffer_size附加内存。
你可以自己编译MySQL，在32位Windows上使用高达64GB物理内存。请参阅15.2.4节，“InnoDB启动选项”里对innodb_buffer_pool_awe_mem_mb的描述。
如何调整其它mysqld服务器参数？下列值是典型的，且适用于多数用户：
[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
key_buffer_size=value
15.2.4. InnoDB启动选项
这一节叙述InnoDB相关的服务器选项，所有这些选项可以以--opt_name=value的形式在命令行或在选项文件里被指定。
·innodb_additional_mem_pool_size
InnoDB用来存储数据目录信息＆其它内部数据结构的内存池的大小。你应用程序里的表越多，你需要在这里分配越多的内存。如果InnoDB用光了这个池内的内存，InnoDB开始从操作系统分配内存，并且往MySQL错误日志写警告信息。默认值是1MB。
·innodb_autoextend_increment
当自动扩展表空间被填满之时，为扩展而增加的尺寸（MB为单位）。默认值是8。这个选项可以在运行时作为全局系统变量而改变。
·innodb_buffer_pool_awe_mem_mb
如果缓冲池被放在32位Windows的AWE内存里，这个参数就是缓冲池的大小（MB为单位）。(仅在32位Windows上相关）如果你的32位Windows操作系统使用所谓的“地址窗口扩展（AWE)”支持超过4GB内存，你可以用这个参数把InnoDB缓冲池分配进AWE物理内存。这个参数最大的可能值是64000。如果这个参数被指定了，innodb_buffer_pool_size是在32位地址空间的mysqld内的窗口，InnoDB把那个AWE内存映射上去。对innodb_buffer_pool_size参数，一个比较好的值是500MB。
·innodb_buffer_pool_size
InnoDB用来缓存它的数据和索引的内存缓冲区的大小。你把这个值设得越高，访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上，你可以设置这个参数达机器物理内存大小的80%。尽管如此，还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。
·innodb_checksums
InnoDB在所有对磁盘的页面读取上使用校验和验证以确保额外容错防止硬件损坏或数据文件。尽管如此，在一些少见的情况下（比如运行标准检查之时）这个额外的安全特征是不必要的。在这些情况下，这个选项（默认是允许的）可以用--skip-innodb-checksums来关闭。
·innodb_data_file_path
到单独数据文件和它们尺寸的路径。通过把innodb_data_home_dir连接到这里指定的每个路径，到每个数据文件的完整目录路径可被获得。文件大小通过给尺寸值尾加M或G以MB或者GB（1024MB）为单位被指定。文件尺寸的和至少是10MB。在一些操作系统上，文件必须小于2GB。如果你没有指定innodb_data_file_path，开始的默认行为是创建一个单独的大小10MB名为ibdata1的自扩展数据文件。在那些支持大文件的操作系统上，你可以设置文件大小超过4GB。你也可以使用原始磁盘分区作为数据文件，请参阅15.2.14.2节，“为表空间使用原始设备”。
·innodb_data_home_dir
目录路径对所有InnoDB数据文件的共同部分。如果你不设置这个值，默认是MySQL数据目录。你也可以指定这个值为一个空字符串，在这种情况下，你可以在innodb_data_file_path中使用绝对文件路径。
·innodb_doublewrite
默认地，InnoDB存储所有数据两次，第一次存储到doublewrite缓冲，然后存储到确实的数据文件。这个选项可以被用来禁止这个功能。类似于innodb_checksums，这个选项默认是允许的；因为标准检查或在对顶级性能的需要超过对数据完整性或可能故障的关注之时，这个选项用--skip-innodb-doublewrite来关闭。
·innodb_fast_shutdown
如果你把这个参数设置为0，InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间，设置在极端情况下要几个小时。如果你设置这个参数为1，InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值)， InnoDB将刷新它的日志然后冷关机，仿佛MySQL崩溃一样。已提交的事务不会被丢失，但在下一次启动之时会做一个崩溃恢复。
·innodb_file_io_threads
InnoDB中文件I/O线程的数量。正常地，这个参数是用默认的，默认值是4，但是大数值对Windows磁盘I/O有益。在Unix上，增加这个数没有效果，InnoDB总是使用默认值。
·innodb_file_per_table
这个选项致使InnoDB用自己的.ibd文件为存储数据和索引创建每一个新表，而不是在共享表空间中创建。请参阅15.2.6.6节，“使用Per-Table表空间”。
·innodb_flush_log_at_trx_commit
当innodb_flush_log_at_trx_commit被 设置为0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。当设置为2之时，在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。尽管如此，在对日志文件的刷新在值为2的情况也每秒发生一次。我们必须注意到，因为进程安排问题，每秒一次的刷新不是100%保证每秒都发生。你可以通过设置这个值不为1来获得较好的性能，但随之你会在一次崩溃中损失二分之一价值的事务。如果你设置这个值为0，那么任何mysqld进程的崩溃会删除崩溃前最后一秒的事务，如果你设置这个值为2，那么只有操作系统崩溃或掉电才会删除最后一秒的事务。尽管如此，InnoDB的崩溃恢复不受影响，而且因为这样崩溃恢复开始作用而不考虑这个值。注意，许多操作系统和一些磁盘硬件会欺骗刷新到磁盘操作。尽管刷新没有进行，你可以告诉mysqld刷新已经进行。即使设置这个值为1，事务的持久程度不被保证，且在最坏情况下掉电甚至会破坏InnoDB数据库。在SCSI磁盘控制器中，或在磁盘自身中，使用有后备电池的磁盘缓存会加速文件刷新并且使得操作更安全。你也可以试着使用Unix命令hdparm来在硬件缓存中禁止磁盘写缓存，或使用其它一些对硬件提供商专用的命令。这个选项的默认值是1。
·innodb_flush_method
这个选项只在Unix系统上有效。如果这个选项被设置为fdatasync （默认值），InnoDB使用fsync()来刷新数据和日志文件。如果被设置为O_DSYNC，InnoDB使用O_SYNC来打开并刷新日志文件，但使用fsync()来刷新数据文件。如果O_DIRECT被指定了（在一些GNU/Linux版本商可用），InnoDB使用O_DIRECT来打开数据文件，并使用fsync()来刷新数据和日志文件。注意，InnoDB使用fsync()来替代fdatasync()，并且它默认不使用O_DSYNC，因为这个值在许多Unix变种上已经发生问题。
·innodb_force_recovery
警告：这个选项仅在一个紧急情况下被定义，当时你想要从损坏的数据库转储表。可能的值为从1到6。这些值的意思在15.2.8.1节，“强制恢复”中叙述。作为一个安全措施，当这个选项值大于零之时，InnoDB阻止用户修改数据。
·innodb_lock_wait_timeout
InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒。
为在一个复制建立中最大可能的持久程度和连贯性，你应该在主服务器上的my.cnf文件里使用innodb_flush_log_at_trx_commit=1和sync-binlog=1。
·innodb_locks_unsafe_for_binlog
这个选项在InnoDB搜索和索引扫描中关闭下一键锁定。这个选项的默认值是假（false）。
正常地，InnoDB使用一个被称为next-key locking的算法。当搜索或扫描一个表索引之时，InnoDB以这样一种方式实行行级锁定，它对任何遇到的索引记录设置共享的或独占的锁定。因此，行级锁定实际是索引记录锁定。InnoDB对索引记录设置的锁定也影响被锁定索引记录之前的“gap”。如果一个用户对某一索引内的记录R又共享的或独占的锁定，另一个用户不能立即在R之前以索引的顺序插入一个新的索引记录。这个选项导致InnoDB不在搜索或索引扫描中使用下一键锁定。下一键锁定仍然被用来确保外键强制及重复键核查。注意，使用这个选项可能会导致一些诡异的问题：假设你想要用值大于100的标识符从子表里读取并锁定所有的子记录，同时向随后在选定的行更新一些列：
SELECT * FROM child WHERE id > 100 FOR UPDATE;
假设在id列有一个索引。查询从id大于100的第一个记录开始扫描索引。如果在索引记录上的锁定不把在间隙处生成的插入排除锁定，同时一个新行被插进表中。如果你在同一个事务之内执行同样的SELECT，你会在查询返回的结果包里看到一个新行。这也意味着，如果新条目被加进数据库，InnoDB不保证连续性；尽管如此，对应连续性仍被保证。因此，如果这个选项被使用，InnoDB在大多数孤立级别保证READ COMMITTED。
这个选项甚至更不安全。InnoDB在一个UPDATE或DELETE中只锁定它更新或删除的行。这大大减少了死锁的可能性，但是可以发生死锁。注意，即使在当类似的操作影响不同行时的情况下，这个选项仍然不允许诸如UPDATE这样的操作压倒相似选项（比如另一个UPDATE）。考虑下列例子：
CREATE TABLE A(A INT NOT NULL, B INT);
INSERT INTO A VALUES (NULL,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
如果一个连接执行一个查询：
SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;
并且其它连接跟着第一个连接执行其它查询：
SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;
接着查询2要等查询1的提交或回滚，因为查询1对行（2，3）有一个独占的锁定，并且查询2在扫描行的同时也试着对它不能锁定的同一个行（2，3）采取一个独占的锁定。这是因为当innodb_locks_unsafe_for_binlog选项被使用之时，查询2首先对一个行采取一个独占的锁定，然后确定是否这个行属于结果包，并且如果不属于，就释放不必要的锁定。
因此，查询1按如下执行：
x-lock(1,2)
unlock(1,2)
x-lock(2,3)
update(2,3) to (2,5)
x-lock(3,2)
unlock(3,2)
x-lock(4,3)
update(4,3) to (4,5)
x-lock(5,2)
unlock(5,2)
并且查询2按如下执行：
x-lock(1,2)
update(1,2) to (1,4)
x-lock(2,3) - 等待查询1提交或回滚
·innodb_log_arch_dir
如果我们使用日志档案，被完整写入的日志文件所在的目录也被归档。这个参数值如果被使用了，应该被设置得与innodb_log_group_home_dir一样。尽管如此，它不是必需的。
·innodb_log_archive
这个值当前被设为0。因为MySQL使用它自己的日志文件从备份来恢复，所以当前没有必要来归档InnoDB日志文件。这个选项的默认值是0。
·innodb_log_buffer_size
InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。明智的值是从1MB到8MB。默认的是1MB。一个大的日志缓冲允许大型事务运行而不需要在事务提交之前往磁盘写日志。因此，如果你有大型事务，使日志缓冲区更大以节约磁盘I/O。
·innodb_log_file_size
在日志组里每个日志文件的大小。在32位计算机上日志文件的合并大小必须少于4GB。默认是5MB。明智的值从1MB到N分之一缓冲池大小，其中N是组里日志文件的数目。值越大，在缓冲池越少需要检查点刷新行为，以节约磁盘I/O。但更大的日志文件也意味这在崩溃时恢复得更慢。
·innodb_log_files_in_group
在日志组里日志文件的数目。InnoDB以循环方式写进文件。默认是2（推荐）。
·innodb_log_group_home_dir
到InnoDB日志文件的目录路径。它必须有和innodb_log_arch_dir一样的值。如果你不指定任何InnoDB日志参数，默认的是在MySQL数据目录里创建两个5MB大小名为ib_logfile0和ib_logfile1的文件。
·innodb_max_dirty_pages_pct
这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面，使得脏页（没有被写的页面）的百分比不超过这个值。如果你有SUPER权限，这个百分比可以在服务器运行时按下面来改变：
SET GLOBAL innodb_max_dirty_pages_pct = value;
·innodb_max_purge_lag
这个选项控制在净化操作被滞后之时，如何延迟INSERT, UPDATE和DELETE操作。（请参阅15.2.12节，“多版本的实施”）。这个参数的默认值是零，意为无延迟。这个选项可以在运行时作为全局系统变量而被改变。
InnoDB事务系统维持一个事务列表，该列表有被UPDATE或DELETE操作标志为删除的索引记录。让这个列表的长度为purge_lag。当purge_lag超过innodb_max_purge_lag之时，每个INSERT, UPDATE和DELETE操作延迟 ((purge_lag/innodb_max_purge_lag)*10)-5毫秒。在净化批处理的开始，延迟每隔10秒计算。如果因为一个旧的可以看到行被净化的一致的读查看，删除操作不被延迟。
对有问题的工作量，典型设置可能是1百万，假设我们的事务很小，只有100字节大小，我们就可以允许在我们的表之中有100MB未净化的行。
·innodb_mirrored_log_groups
我们为数据库保持的日志组内同样拷贝的数量。当前这个值应该被设为1。
·innodb_open_files
在InnoDB中，这个选项仅与你使用多表空间时有关。它指定InnoDB一次可以保持打开的.ibd文件的最大数目。最小值是10。默认值300。
对.ibd文件的文件描述符是仅对InnoDB的。它们独立于那些由--open-files-limit服务器选项指定的描述符，且不影响表缓存的操作。
·innodb_status_file
这个选项让InnoDB为周期的SHOW INNODB STATUS输出创建一个文件<datadir>/innodb_status.<pid>。
·innodb_support_xa
当被设置为ON或者1（默认地），这个变量允许InnoDB支持在XA事务中的双向提交。允许innodb_support_xa导致一个额外的对事务准备的磁盘刷新。如果你对使用XA并不关心，你可以通过设置这个选项为OFF或0来禁止这个变量，以减少磁盘刷新的次数并获得更好的InnoDB性能。
·innodb_table_locks
InnoDB重视LOCK TABLES，直到所有其它线程已经释放他们所有对表的锁定，MySQL才从LOCK TABLE .. WRITE返回。默认值是1，这意为LOCK TABLES让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1的应用里，InnoDB的内部表锁定会导致死锁。你可以在my.cnf文件（Windows上是my.ini文件）里设置innodb_table_locks=0 来消除这个问题。
·innodb_thread_concurrency
InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题，并且SHOW INNODB STATUS显示许多线程在等待信号，可以让线程“thrashing” ，并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘，你可以试着这个值更大以更好地利用计算机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20，并且如果设置大于或等于20，并发检查将被禁止。
·innodb_status_file
这个选项让InnoDB为周期的SHOW INNODB STATUS输出创建一个文件<datadir>/innodb_status.<pid>。
15.2.5. 创建InnoDB表空间
15.2.5.1. 处理InnoDB初始化问题
假设你已经安装了MySQL，并且已经编辑了选项文件，使得它包含必要的InnoDB配置参数。在启动MySQL之前，你应该验证你为InnoDB数据文件和日志文件指定的目录是否存在，并且MySQL有访问这些目录的权限。InnoDB不能创建目录，只能创建文件。也检查你有足够的空间来放数据和日志文件。
当创建InnoDB数据库时，最好从命令提示符运行MySQL服务器mysqld， 而不要从mysqld_safe包装或作为Windows的服务来运行。当你从命令提示符运行，你可看见mysqld打印什么以及发生了什么。在Unix上，只需要调用mysqld。在Windows上，使用--console选项。
当在选项文件里初始地配置InnoDB后，开始启动MySQL服务器之时，InnoDB创建一个数据文件和日志文件。InnoDB打印如下一些东西：
InnoDB: The first specified datafile /home/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
new to be created
InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size
to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size
to 5242880
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
InnoDB: Started
mysqld: ready for connections
一个新的InnoDB数据库被创建了。你可以用mysql这样通常的MySQL客户端程序连接到MySQL服务器。当你用mysqladmin shutdown关闭MySQL服务器之时，输出类似如下：
010321 18:33:34 mysqld: Normal shutdown
010321 18:33:34 mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed
你可以看数据文件和日志文件，并且你可以看见文件被创建。日志目录也包含一个名为ib_arch_log_0000000000的小文件。这个文件是数据库被创建的结果，数据库被创建之后InnoDB切断日志归档。当MySQL再次启动之时，数据文件＆日志文件已经被创建，所以输出更简洁：
InnoDB: Started
mysqld: ready for connections
你可以添加innodb_file_per_table选项到my.cnf文件，并且让InnoDB存储每一个表到MySQL数据库目录里自己的.ibd文件。请参阅15.2.6.6节，“使用Per-Table表空间”。
15.2.5.1. 处理InnoDB初始化问题
如果InnoDB在一个文件操作中打印一个操作系统错误，通常问题是如下中的一个：
·你没有创建一个InnoDB数据文件目录或InnoDB日志目录。
·mysqld没有访问这些目录的权限以创建文件。
·mysqld不能恰当地读取my.cnf或my.ini选项文件，因此不能看到你指定的选项。
·磁盘已满，或者超出磁盘配额。
·你已经创建一个子目录，它的名字与你指定的数据文件相同。
·在innodb_data_home_dir或innodb_data_file_path有一个语法错误。
当InnoDB试着初始化它的表空间或日志文件之时，如果出错了，你应该删除InnoDB创建的所有文件。这意味着是所有ibdata文件和所有ib_logfiles文件。万一你创建了一些InnoDB表，为这些表也从MySQL数据库目录删除相应的.frm文件(如果你使用多重表空间的话，也删除任何.ibd文件）。然后你可以试着再次创建InnoDB数据库。最好是从命令提示符启动MySQL服务器，以便你可以查看发生了什么。
15.2.6. 创建InnoDB表
15.2.6.1. 如何在InnoDB用不同API来使用事务
15.2.6.2. 转换MyISAM表到InnoDB
15.2.6.3. AUTO_INCREMENT列如何在InnoDB中工作
15.2.6.4.外键约束
15.2.6.5. InnoDB和MySQL复制
15.2.6.6. 使用Per-Table表空间
假如你用mysql test命令启动MySQL客户端。要创建一个InnoDB表，你必须在表创建SQL语句中指定ENGINE = InnoDB或者TYPE = InnoDB选项：
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
SQL语句在表空间的列上创建一个表和索引，表空间包含你在my.cnf指定的数据文件。此外，MySQL在MySQL数据库目录下的test目录里创建一个名为customers.frm的文件。内部地，InnoDB为'test/customers'表往自己的数据目录添加一个条目。这意味这你可以在其它数据库创建一个具有相同名字customers的表，表的名字不会与InnoDB内的冲突。
你可以对任何InnoDB表，通过使用SHOW TABLE STATUS语句，查询在InnoDB表空间内空闲空间的数量。表空间内空闲空间的数量出现在SHOW TABLE STATUS的输出结果内的Comment节里。例如：
SHOW TABLE STATUS FROM test LIKE 'customers'
注意，统计的SHOW只给出关于InnoDB表的大概情况。它们被用于SQL优化。可是，表和索引保留的大小，以字节为单位是准确的。
15.2.6.1. 如何在InnoDB中用不同的API来使用事务
默认地，每个连接到MySQL服务器的客户端开始之时是允许自动提交模式的，这个模式自动提交你运行的每个SQL语句。要使用多语句事务，你可以用SQL语句SET AUTOCOMMIT = 0禁止自动提交，并且用COMMIT和ROLLBACK来提交或回滚你的事务。 如果你想要autocommit保持打开状态，可以在START TRANSACTION与COMMIT或ROLLBACK之间封装你的事务。下列的例子演示两个事务。第一个是被提交的，第二个是被回滚的：
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (NULL, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (NULL, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
在类似PHP, Perl DBI/DBD, JDBC, ODBC, 或者MySQL的标准C调用接口这样的API上，你能够以字符串形式发送事务控制语句，如COMMIT，到MySQL服务器，就像其它任何的SQL语句那样，诸如SELECT或INSERT。一些API也提供单独的专门的事务提交和回滚函数或者方法。
15.2.6.2. 转换MyISAM表到InnoDB
要点：你不应该在mysql数据库（比如，user或者host）里把MySQL系统表转换为InnoDB类型。系统表总是MyISAM型。
如果你想要所有（非系统）表都被创建成InnoDB表，你可以简单地把default-table-type=innodb行添加到my.cnf或my.ini文件的[mysqld]节里。
InnoDB对MyISAM存储引擎采用的单独索引创建方法没有做专门的优化。因此，它不值得导出或导入表以及随后创建索引。改变一个表为InnoDB型最快的办法就是直接插入进一个InnoDB表。即，使用ALTER TABLE ... ENGINE=INNODB，或用相同的定义创建一个空InnoDB表，并且用INSERT INTO ... SELECT * FROM ...插入行。
如果你对第二个键有UNIQUE约束，你可以在导入阶段设置：SET UNIQUE_CHECKS=0，以临时关掉唯一性检查好加速表的导入。对于大表，这节省了大量的磁盘I/O，因为InnoDB随后可以使用它的插入缓冲区来第二个索引记录作为一批来写入。
为获得对插入进程的更好控制，分段插入大表可能比较好：
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
所有记录已经本插入之后，你可以重命名表。
在大表的转换中，你应该增加InnoDB缓冲池的大小来减少磁盘I/O。尽管如此，不要使用超过80%的内部内存。你也可以增加InnoDB日志文件和日志文件的大小。
确信你没有填满表空间：InnoDB表比MyISAM表需要大得多的磁盘空间。如果一个ALTER TABLE耗尽了空间，它就开始一个回滚，并且如果它是磁盘绑定的，回滚可能要几个小时。对于插入，InnoDB使用插入缓冲区来以成批地合并第二个索引记录到索引中。那样节省了大量磁盘I/O。在回滚中，没有使用这样的机制，而回滚要花比插入长30倍的时间来完成。
在失控的回滚情况下，如果你在数据库中没有有价值的数据，比较明智的是杀掉数据库进程而不是等几百万个磁盘I/O被完成。 完整的过程，请参阅15.2.8.1节，“强制恢复”。
15.2.6.3. AUTO_INCREMENT列在InnoDB里如何工作
如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。自动增长计数器仅被存储在主内存中，而不是存在磁盘上。
InnoDB使用下列算法来为包含一个名为ai_col的AUTO_INCREMENT列的表T初始化自动增长计数器：服务器启动之后，当一个用户对表T做插入之时，InnoDB执行等价如下语句的动作：
SELECT MAX(ai_col) FROM T FOR UPDATE;
语句取回的值逐次加一，并被赋给列和自动增长计数器。如果表是空的，值1被赋予该列。如果自动增长计数器没有被初始化，而且用户调用为表T显示输出的SHOW TABLE STATUS语句，则计数器被初始化（但不是增加计数）并被存储以供随后的插入使用。注意，在这个初始化中，我们对表做一个正常的独占读锁定，这个锁持续到事务的结束。
InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。
注意，如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0，InnoDB处理行，就仿佛值还没有被指定，且为它生成一个新值。
自动增长计数器被初始化之后，如果用户插入一个明确指定该列值的行，而且该值大于当前计数器值，则计数器被设置为指定列值。如果没有明确指定一个值，InnoDB给计数器增加一，并且赋新值给该列。
当访问自动增长计数器之时，InnoDB使用专用的表级的AUTO-INC锁定，该锁持续到当前SQL语句的结束而不是到业务的结束。引入了专用锁释放策略，来为对一个含AUTO_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。
注意，如果你回滚从计数器获得数的事务，你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。
如果用户给列赋一个赋值，或者，如果值大过可被以指定整数格式存储的最大整数，自动增长机制的行为不被定义。
在CREATE TABLE和ALTER TABLE语句中，InnoDB支持AUTO_INCREMENT = n 表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因，这个选项的影响在服务器重启后就无效了。
15.2.6.4.外键约束
InnoDB也支持外键约束。InnoDB中对外键约束定义的语法看起来如下：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]外键定义服从下列情况：
· 所有tables必须是InnoDB型，它们不能是临时表。
·在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在，它必须在引用表里被自动创建。
·在引用表中，必须有一个索引，被引用的列以同样的顺序被列在其中作为第一列。
·不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中，这是因为对这些列的索引必须总是包含一个前缀长度。
·如果CONSTRAINTsymbol被给出，它在数据库里必须是唯一的。如果它没有被给出，InnoDB自动创建这个名字。
InnoDB拒绝任何试着在子表创建一个外键值而不匹配在父表中的候选键值的INSERT或UPDATE操作。一个父表有一些匹配的行的子表，InnoDB对任何试图更新或删除该父表中候选键值的UPDATE或DELETE操作有所动作，这个动作取决于用FOREIGN KEY子句的ON UPDATE和ON DETETE子句指定的referential action。当用户试图从一个父表删除或更新一行之时，且在子表中有一个或多个匹配的行，InnoDB根据要采取的动作有五个选择：
·CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间，你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。
·SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
·NO ACTION: 在ANSI SQL-92标准中，NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行（Gruber, 掌握SQL, 2000:181）。 InnoDB拒绝对父表的删除或更新操作。
·RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。（一些数据库系统有延期检查，并且NO ACTION是一个延期检查。在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的）。
·SET DEFAULT: 这个动作被解析程序识别，但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。
当父表中的候选键被更新的时候，InnoDB支持同样选择。选择CASCADE，在子表中的外键列被设置为父表中候选键的新值。以同样的方式，如果在子表更新的列参考在另一个表中的外键，更新级联。
注意，InnoDB支持外键在一个表内引用，在这些情况下，子表实际上意味这在表内附属的记录。
InnoDB需要对外键和被引用键的索引以便外键检查可以快速进行且不需要一个表扫描。对外键的索引被自动创建。这是相对于一些老版本，在老版本中索引必须明确创建，否则外键约束的创建会失败。
在InnoDB内，外键里和被引用列里相应的列必须有类似的内部数据类型，以便它们不需类型转换就可被比较。整数类型的大小和符号必须相同。字符串类型的长度不需要相同。如果你指定一个SET NULL动作，请确认你没有在子表中宣告该列为为NOT NULL。
如果MySQL从CREATE TABLE语句报告一个错误号1005，并且错误信息字符串指向errno 150，这意思是因为一个外键约束被不正确形成，表创建失败。类似地，如果ALTER TABLE失败，且它指向errno 150， 那意味着对已变更的表，外键定义会被不正确的形成。你可以使用SHOW INNODB STATUS来显示一个对服务器上最近的InnoDB外键错误的详细解释。
注释：InnoDB不对那些外键或包含NULL列的被引用键值检查外键约束。
对SQL标准的背离：如果在父表内有数个行，其中有相同的被引用键值，然后InnoDB在外键检查中采取动作，就仿佛其它有相同键值的父行不存在一样。例如，如果你已定义一个RESTRICT类型的约束，并且有一个带数个父行的子行，InnoDB不允许任何对这些父行的删除。
居于对应外键约束的索引内的记录，InnoDB通过深度优先选法施行级联操作。
对SQL标准的背离： 如果ON UPDATE CASCADE或ON UPDATE SET NULL递归更新相同的表，之前在级联过程中该表一被更新过，它就象RESTRICT一样动作。这意味着你不能使用自引用ON UPDATE CASCADE或者ON UPDATE SET NULL操作。这将阻止级联更新导致的无限循环。另一方面，一个自引用的ON DELETE SET NULL是有可能的，就像一个自引用ON DELETE CASCADE一样。级联操作不可以被嵌套超过15层深。
对SQL标准的背离： 类似一般的MySQL，在一个插入，删除或更新许多行的SQL语句内，InnoDB逐行检查UNIQUE和FOREIGN KEY约束。按照SQL的标准，默认的行为应被延迟检查，即约束仅在整个SQL语句被处理之后才被检查。直到InnoDB实现延迟的约束检查之前，一些事情是不可能的，比如删除一个通过外键参考到自身的记录。
注释：当前，触发器不被级联外键的动作激活。
一个通过单列外键联系起父表和子表的简单例子如下：
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
如下是一个更复杂的例子，其中一个product_order表对其它两个表有外键。一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引：
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
InnoDB允许你用ALTER TABLE往一个表中添加一个新的外键约束：
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]记住先创建需要的索引。你也可以用ALTER TABLE往一个表添加一个自引用外键约束。
InnoDB也支持使用ALTER TABLE来移除外键：
ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
当年创建一个外键之时，如果FOREIGN KEY子句包括一个CONSTRAINT名字，你可以引用那个名字来移除外键。另外，当外键被创建之时，fk_symbol值被InnoDB内部保证。当你想要移除一个外键之时，要找出标记，请使用SHOW CREATE TABLE语句。例子如下：
mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
InnoDB解析程序允许你在FOREIGN KEY ... REFERENCES ...子句中用`（backticks)把表和列名名字围起来。InnoDB解析程序也考虑到lower_case_table_names系统变量的设置。
InnoDB返回一个表的外键定义作为SHOW CREATE TABLE语句输出的一部分：
SHOW CREATE TABLE tbl_name;
从这个版本起，mysqldump也将表的正确定义生成到转储文件中，且并不忘记外键。
你可以如下对一个表显示外键约束：
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
外键约束被列在输出的Comment列。
当执行外键检查之时，InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束，检查不对事务提交延迟。
要使得对有外键关系的表重新载入转储文件变得更容易，mysqldump自动在转储输出中包括一个语句设置FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量：
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
如果转储文件包含对外键是不正确顺序的表，这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0，对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。
InnoDB不允许你删除一个被FOREIGN KEY表约束引用的表，除非你做设置SET FOREIGN_KEY_CHECKS=0。当你移除一个表的时候，在它的创建语句里定义的约束也被移除。
如果你重新创建一个被移除的表，它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型，并且如前所述，它必须对被引用的键有索引。如果这些不被满足，MySQL返回错误号1005 并在错误信息字符串中指向errno 150。
15.2.6.5. InnoDB和MySQL复制
MySQL复制就像对MyISAM表一样，也对InnoDB表起作用。以某种方式使用复制也是可能的，在这种方式中从服务器上表的类型不同于主服务器上原始表的类型。例如，你可以复制修改到主服务器上一个InnoDB表，到从服务器上一个MyISAM表里。
要为一个主服务器建立一个新服务器，你不得不复制InnoDB表空间和日志文件，以及InnoDB表的.frm文件，并且移动复件到从服务器。关于其恰当步骤请参阅15.2.9节，“移动InnoDB数据库到另一台机器”。
如果你可以关闭主服务器或者一个存在的从服务器。你可以采取InnoDB表空间和日志文件的冷备份，并用它来建立一个从服务器。要不关闭任何服务器来建立一个新的从服务器，你也可以使用非免费（商业的）InnoDB热备份工具。
InnoDB复制里一个小的限制是LOAD TABLE FROM MASTER不对InnoDB类型表起作用。有两种可能的工作区：
·转储主服务器上的表并导入转储文件到从服务器。
·在用LOAD TABLE tbl_name FROM MASTER建立复制之前，在主服务器上使用ALTER TABLE tbl_name TYPE=MyISAM，并且随后使用ALTER TABLE把主表转换回InnoDB。
在主服务器失败的事务根本不影响复制。MySQL复制基于二进制日志，MySQ在那里写修改数据的SQL语句。从服务器读主服务器的二进制日志，并执行同样的SQL语句。尽管如此，在事务里发生的语句不被写进二进制日志直到事务提交，在那一刻，事务里所有语句被一次性写进日志。如果语句失败了，比如，因为外键违例，或者，如果事务被回滚，没有SQL语句被写进二进制日志，并且事务根本不在从服务器上执行。
15.2.6.6. 使用Per-Table表空间
你可以存储每个InnoDB表和它的索引在它自己的文件在中，这个特征被称为“多表空间” ，因为实际上每个表有它自己的表空间。
对那些想把特定表格移到分离物理磁盘的用户，或者那些希望快速恢复单个表的备份而无须打断其余InnoDB表的使用的用户，使用多表空间会是有益的。
你可以往my.cnf的[mysqld]节添加下面行来允许多表空间：
[mysqld]
innodb_file_per_table
重启服务器之后，InnoDB存储每个新创建的表到表格所属于的数据库目录下它自己的文件tbl_name.ibd里。这类似于MyISAM存储引擎所做的，但MyISAM 把表分成数据文件tbl_name.MYD和索引文件tbl_name.MYI。对于InnoDB，数据和所以被一起存到.ibd文件。tbl_name.frm文件照旧依然被创建。
如果你从my.cnf文件删除innodb_file_per_table行，并重启服务器，InnoDB在共享的表空间文件里再次创建表。
innodb_file_per_table只影响表的创建。如果你用这个选项启动服务器，新表被用.ibd文件来创建，但是你仍旧能访问在共享表空间里的表。如果你删掉这个选项，新表在共享表空间内创建，但你仍旧可以访问任何用多表空间创建的表。
InnoDB总是需要共享标空间。.ibd文件对InnoDB不足以去运行，共享表空间包含熟悉的ibdata文件，InnoDB把内部数据词典和未作日志放在这个文件中。
注释：你不能像对MyISAM一样，在数据目录之间随意地移动.ibd文件。这是因为表定义被存在InnoDB共享表空间内，而且InnoDB必须保持事务ID和日志顺序号的一致性。
在一个给定的MySQL安装里，你可以用RENAME TABLE语句把.ibd文件和关联的表从一个数据库移到另一个数据库：
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
如果你有.ibd文件的一个干净的备份，你可以按如下操作从被起源的地方恢复它到MySQL安装中：
1. 发出这个ALTER TABLE语句：
2. ALTER TABLE tbl_name DISCARD TABLESPACE;
警告：这个语句删除当前.ibd文件。
3. 把备份的.ibd文件放回到恰当的数据库目录。
4. 发出这个ALTER TABLE语句:
5. ALTER TABLE tbl_name IMPORT TABLESPACE;
在上下文中，一个.ibd文件干净的备份意为：
·.ibd文件里没有尚未提交的事务做的修改。
· .ibd文件里无未合并的插入混充条目。
·净化已经从.ibd文件移除所有已标注删除的索引记录。
·mysqld已经把.ibd文件的所有已修改页面从缓冲池刷新到文件。
你可以用下列方法生成一个.ibd文件的干净备份：
1. 停止所有来自mysqld服务器的活动，并提交所有事务。
2. 等待直至SHOW INNODB STATUS显示在数据库被已经没有激活的事务，并且InnoDB主线程的状态是Waiting for server activity。然后你就可以复制.ibd文件了。
生成一个.ibd文件的干净复制的另一个方法是使用商业的InnoDB热备份工具：
1. 使用InnoDB热备份工具备份InnoDB安装。
2. 在备份上启动第二个mysqld服务器，让它清洁备份里的.ibd文件。
15.2.7. 添加和删除InnoDB数据和日志文件
这一节描述在InnoDB表空间耗尽空间之时，或者你想要改变日志文件大小之时，你可以做的一些事情。
最简单的，增加InnoDB表空间大小的方法是从开始配置它为自动扩展的。为表空间定义里的最后一个数据文件指定autoextend属性。然后在文件耗尽空间之时，InnoDB以8MB为增量自动增加该文件的大小。增加的大小可以通过设置innodb_autoextend_increment值来配置,这个值以MB为单位，默认的是8。
作为替代，你可以通过添加另一个数据文件来增加表空间的尺寸。要这么做的话，你必须停止MySQL服务器，编辑my.cnf文件，添加一个新数据文件到innodb_data_file_path的末尾，然后再次启动服务器。
如果最后一个数据文件是用关键字autoextend定义的，编辑my.cnf文件的步骤必须考虑最后一个数据文件已经增长到多大。获取数据文件的尺寸，把它四舍五入到最接近乘积1024 × 1024bytes (= 1MB)，然后在innodb_data_file_path中明确指定大致的尺寸。然后你可以添加另一个数据文件。记得只有innodb_data_file_path里最后一个数据可以被指定为自动扩展。
作为一个例子。假设表空间正好有一个自动扩展文件ibdata1：
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
假设这个数据文件过一段时间已经长到988MB。下面是添加另一个总扩展数据文件之后的配置行：
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
当你添加一个新文件到表空间的之后，请确信它并不存在。当你重启服务器之时，InnoDB创建并初始化这个文件。
当前，你不能从表空间删除一个数据文件。要增加表空间的大小，使用如下步骤：
1. 使用mysqldump转储所有InnoDB表。
2. 停止服务器。
3. 删除所有已存在的表空间文件。
4. 配置新表空间。
5. 重启服务器。
6. 导入转储文件。
如果你想要改变你的InnoDB日志文件的数量和大小，你必须要停止MySQL服务器，并确信它被无错误地关闭。随后复制旧日志文件到一个安全的地方以防万一某样东西在关闭时出错而你需要用它们来恢复表空间。从日志文件目录删除所有旧日志文件，编辑my.cnf来改变日志文件配置，并再次启动MySQL服务器。mysqld在启动之时发现没有日志文件，然后告诉你它正在创建一个新的日志文件。
15.2.8. 备份和恢复InnoDB数据库
15.2.8.1. 强制恢复
15.2.8.2. 检查点
安全数据库管理的关键是定期做备份。
InnoDB热备份工具是一个在线备份工具，你可以用它来在InnoDB数据库运行之时备份你的InnoDB数据库。InnoDB热备份工具不要求你关闭数据库，并且它不设置任何锁定或干扰你正常的数据库处理。InnoDB热备份工具是非免费（商业的）附加软件，它每年的证书费用是每台MySQL服务器运行的计算机€390。请参阅InnoDB热备份主页以获得更详细的信息以及屏幕截图。
如果你可以关闭你的MySQL服务器，你可以生成一个包含InnoDB用来管理它的表的所有文件的二进制备份。使用如下步骤：
1. 关闭MySQL服务器，确信它是无错误关闭。
2. 复制你所有数据文件（ibdata文件和.ibd文件）到一个安全的地方。
3. 复制你所有ib_logfile文件到一个安全的地方。
4. 复制my.cnf配置文件或文件到一个安全的地方。
5. 为你InnoDB表复制.frm文件到一个安全的地方。
复制对InnoDB表起作用，所以你可以使用MySQL复制能力来在需要高可用性的数据库站点保有一份数据库的复制。
除了刚才描述的二进制备份，你也应该周期性地用mysqldump转储你的数据库。这么做的原因是，二进制文件可能被破坏而你没有注意到。转储的文件被存储成为人可读的文本文件，所以定点表的损坏修复变得更容易。再者，因为形式更简单，严重数据损坏的机会就更小。mysqldump 也有一个--single-transaction选项，你可以用它来做一个一致的快照而不用把其它客户端排除在外面。
要能够从上述的二进制备份恢复InnoDB数据库到现在，你必须让二进制日志功能打开正在运行的MySQL服务器。然后你可以应用二进制日志到备份数据库以实现point-in-time恢复：
mysqlbinlog yourhostname-bin.123 | mysql
要从MySQL服务器的崩溃恢复，唯一需要的是重新启动它。InnoDB自动检查日志并执行到现在的数据库前滚。InnoDB自动回滚在崩溃时呈现的未提交的事务。在恢复过程中，mysqld显示如下一些输出：
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
如果数据库被损坏或磁盘出错，你必须从备份做恢复。在损坏的情况下，你首先应该找出一个没有被损坏的备份。恢复数据库备份之后，从二进制日志文件恢复。
在一些数据库损坏的情况下，仅仅转储，移除并重建一个或数个被破坏的表是足够的。你可以用CHECK TABLE SQL语句来检查表是否损坏，虽然CHECK TABLE正常地不检查每种可能的损坏，你可以使用innodb_tablespace_monitor来检查表空间文件内文件空间管理的完整性。
在一些情况下，明显地数据库损坏是因为操作系统损坏它自己的文件缓存，磁盘上的数据可能完好，最好是首先重启计算机。它可以消除那些显得是数据库页损坏的错误。
15.2.8.1. 强制恢复
如果数据库页被破坏，你可能想要用SELECT INTO OUTFILE从从数据库转储你的表，通常以这种方法获取的大多数数据是完好的。即使这样，损坏可能导致SELECT * FROM tbl_name或者InnoDB后台操作崩溃或断言，或者甚至使得InnoDB前滚恢复崩溃。 尽管如此，你可以用它来强制InnoDB存储引擎启动同时阻止后台操作运行，以便你能转储你的表。例如：你可以在重启服务器之前，在选项文件的[mysqld]节添加如下的行：
[mysqld]
innodb_force_recovery = 4
innodb_force_recovery被允许的非零值如下。一个更大的数字包含所有更小数字的预防措施。如果你能够用一个多数是4的选项值来转储你的表，那么你是比较安全的，只有一些在损坏的单独页面上的数据会丢失。一个为6的值更夸张，因为数据库页被留在一个陈旧的状态，这个状态反过来可以引发对B树和其它数据库结构的更多破坏。
·1 (SRV_FORCE_IGNORE_CORRUPT)
即使服务器检测到一个损坏的页，也让服务器运行着；试着让SELECT * FROM tbl_name 跳过损坏的索引记录和页，这样有助于转储表。
·2 (SRV_FORCE_NO_BACKGROUND)
阻止主线程运行，如果崩溃可能在净化操作过程中发生，这将阻止它。
·3 (SRV_FORCE_NO_TRX_UNDO)
恢复后不运行事务回滚。
·4 (SRV_FORCE_NO_IBUF_MERGE)
也阻止插入缓冲合并操作。如果你可能会导致一个崩溃。最好不要做这些操作，不要计算表统计表。
·5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
启动数据库之时不查看未完成日志：InnoDB把未完成的事务视为已提交的。
·6 (SRV_FORCE_NO_LOG_REDO)
不要在恢复连接中做日志前滚。
数据库不能另外地带着这些选项中被允许的选项来使用。作为一个安全措施，当innodb_force_recovery被设置为大于0的值时，InnoDB阻止用户执行INSERT, UPDATE或DELETE操作.
即使强制恢复被使用，你也可以DROP或CREATE表。如果你知道一个给定的表正在导致回滚崩溃，你可以移除它。你也可以用这个来停止由失败的大宗导入或失败的ALTER TABLE导致的失控回滚。你可以杀掉mysqld进程，然后设置innodb_force_recovery为3，使得数据库被挂起而不需要回滚，然后舍弃导致失控回滚的表。
15.2.8.2. 检查点
InnoDB实现一种被认识为“模糊”检查点设置的检查点机制。InnoDB以小批量从缓冲池刷新已修改的数据库页。没必要以单个批次刷新缓冲池，单批次刷新实际操作中可能会在检查点设置进程中停止用户SQL语句的处理。
在崩溃恢复中，InnoDB找寻被写进日志的检查点标签。它知道所有在该标签之前对数据库的修改被呈现在数据库的磁盘映像中。然后InnoDB从检查点往前扫描日志文件，对数据库应用已写入日志的修改。
InnoDB以循环方式写日志文件。所有使得缓冲池里的数据库页与磁盘上的映像不同的已提交修改必须出现在日志文件中，以备万一InnoDB需要做一个恢复。这意味着，当InnoDB开始重新使用一个日志文件，它需要确认在磁盘上的数据库页映像包含已写进InnoDB准备重新使用的日志文件里的修改。换句话说，InnoDB必须创建一个检查点，这经常涉及已修改数据库页到磁盘的刷新。
前面的叙述解释了为什么使你的日志文件非常大会在设置检查点中节约磁盘I/O。设置日志文件总的大小和缓冲池一样大或者甚至比缓冲池大通常是有意义的。大日志文件的缺点是崩溃恢复要花更长的时间，因为有更多写入日志的信息要应用到数据库上。
15.2.9. 把一个InnoDB数据库移到另一台机器
在Windows上， InnoDB 总是在内部以小写名字的方式存储数据库和表。要从Unix把二进制格式的数据库移到Windows，或者从Windows移到Unix，你应该让所有表和数据库的名字小写。要实现这个，一个方便的方式是在创建任何数据库和表之前，在你的my.cnf或my.ini文件的[mysqld]节内添加如下行：
[mysqld]
lower_case_table_names=1
类似于MyISAM数据文件，InnoDB数据和日志文件在所有有相同浮点数格式的平台上是二进制兼容的。你可以拷贝所有列在15.2.8节，“InnoDB数据库的备份和恢复”里的相关文件来简单地移动一个InnoDB数据库。如果浮点格式不同，但你没有在表中使用FLOAT或DOUBLE数据类型，则过程是一样：简单地拷贝相关文件。如果格式不容，且你的表包含浮点数据，你必须使用mysqldump在一台机器转储你的表，然后在另一台机器导入转储文件。
假设表空间有足够的空间供导入事务产生的大型回滚片断使用，则提高性能的一个方法是在导入数据时关掉autocommit模式。仅在导入整个表或表的一个片断之后提交。
15.2.10. InnoDB事务模型和锁定
15.2.10.1. InnoDB锁定模式
15.2.10.2. InnoDB和AUTOCOMMIT
15.2.10.3. InnoDB和TRANSACTION ISOLATION LEVEL
15.2.10.4. 持续非锁定读
15.2.10.5. 锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHAREMODE
15.2.10.6. Next-Key锁定：避免匪夷所思的问题
15.2.10.7. 持续读如何在InnoDB中作用的例子
15.2.10.8. 在InnoDB中用不同的SQL语句设定锁
15.2.10.9.MySQL什么时候提交或回滚一个事务？
15.2.10.10. 死锁检测＆回滚
15.2.10.11. 如何应对死锁
在InnoDB事务模式中，目的是把多版本数据库的最好特性与传统的二相锁定合并起来。InnoDB以Oracle的风格，对行级进行锁定，并且默认运行查询作为非锁定持续读。在InnoDB中锁定的表被存储得如此节省空间，以至于不需要锁定增大：典型地，数个用户被允许在数据库中锁定每一行，或者行的任何随机子集，而InnoDB不会耗尽内存。
15.2.10.1. InnoDB锁定模式
InnoDB实现标准行级锁定，在这里有两种类型的锁： locks:
·共享的(S)锁允许一个事务去读一行（tuple）。
·独占的锁(X)允许一个事务更新或删除一行。
如果事务A 在tuple t上持有独占锁定，来自不同事务B的对t上任一类型的锁的请求不被马上许可，取而代之地，事务B 不得不等待事务t释放在tuple t上的锁。
如果事务 A 在tuple t上持有一个共享的锁(S)，那么
·来自不同的事务B对在t 上X的锁定请求不能被马上许可。
·来自不同的事务B对在t 上S的锁定请求可以被马上获准。因此A和B持有t上的S锁定。
不仅如此，InnoDB支持多间隔尺寸锁定，它允许记录锁和对整个表的锁共存。要使得多间隔尺寸级别的锁定实际化，额外类型的锁，被称为intention locks被使用。在InnoDB中，意图锁定是表锁定。 对于一个事务，意图锁定之后理想的是指明在该表中对一个行随后需要哪一类型的锁定（共享还是独占）。有两种意图锁被用在InnoDB中（假设事务T 在表R中要求一个已指出的类型的锁）：
·意图共享(IS)：事务T 意图给表T上单独的tuple设置S 锁定。
·意图独占(IX)：事务T 意图给这些tuple设置X 锁定。
意图锁协议如下：
·在假设的事务可以获得对某假定行的S 锁定之前，它必须首先获得对包含该行的表的一个IS 或者更强的锁定。
·在假设的事务可以获得对某假定行的X 锁定之前，它必须首先获得对包含该行的表的一个IX 锁定。
这些结果可以方便地用一个锁类型兼容矩阵来总结：
X | IX | S | IS | |
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
如果一个锁定与现在锁定兼容的话，它被授给一个委托事务。如果一个锁定与现存锁定冲突，它就不被授予一个委托事务。事务等待着直到冲突的现存锁定被释放掉。如果一个锁定请求与现存锁定相冲突，且不能被授予，因为它可能会导致死锁，一个错误产生。
因此，意图锁定不阻碍任何东西，除了完全表请求（比如LOCK TABLES ... WRITE）。IX 和IS锁定的主要目的是显示某人正锁定一行，或将要在表中锁定一行。
下列的例子演示当锁定请求可能会导致死锁之时一个错误会如何发生。例子中包括两个客户端A和B。
首先客户端A创建一个包含一个行的表，然后开始一个事务。在这个事务内，A通过在共享模式选择行获得对行的S 锁定：
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.10 sec)
接着，客户端B开始一个事务并尝试从该表删除行：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
删除操作要求一个X 锁定。因为这个锁定不兼容客户端A持有的S锁定，所以X 锁定不被允许，所以请求进入对行及客户端阻挡的锁定请求队列。
最后，客户端A也试图从表中删除该行：
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
因为客户端A需要一个X 锁定来删除该行，所以在这里发生死锁。尽管如此，锁定请求不被允许，因为客户端B已经有一个对X锁定的请求并且它正等待客户端A释放S锁定。因为客户端B之前对X 锁定的请求，被客户端A持有的S锁定也不能升级到X锁定。因此，InnoDB对客户端A产生一个错误，并且释放它的锁定。在那一点上，客户端B的锁定请求可以被许可，并且客户端B从表中删除行。
15.2.10.2. InnoDB和 AUTOCOMMIT
在InnoDB中，所有用户行为都在事务内发生。如果自动提交模式被允许，每个SQL语句在它自己上形成一个单独的事务。MySQL总是带着允许自动提交来开始一个新连接。
如果自动提交模式被用SET AUTOCOMMIT = 0关闭，那么我们可以认为一个用户总是有一个事务打开着。一个SQL COMMIT或ROLLBACK语句结束当前事务并且一个新事务开始。两个语句都释放所有在当前事务中被设置的InnoDB锁定。一个COMMIT语句意味着在当前事务中做的改变被生成为永久的，并且变成其它用户可见的。一个ROLLBACK语句，在另一方面，撤销所有当前事务做的修改。
如果连接有被允许的自动提交，通过用明确的START TRANSACTION或BEGIN语句来开始一个事务，并用COMMIT或者ROLLBACK语句来结束它，这样用户仍旧可以执行一个多重语句事务。
15.2.10.3. InnoDB和TRANSACTION ISOLATION LEVEL
按照SQL:1992 事务隔离级别，InnoDB默认是可重复读的（REPEATABLE READ）。MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别。你可以在命令行用--transaction-isolation选项，或在选项文件里，为所有连接设置默认隔离级别。例如，你可以在my.inf文件的[mysqld]节里类似如下设置该选项：globally
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED| REPEATABLE-READ | SERIALIZABLE}
用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED| REPEATABLE READ | SERIALIZABLE}
注意，对--transaction-isolation选项的级别名中有连字符，但在对SET TRANSACTION语句的级别名中没有。
默认的行为是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER全县来做这个。使用SESSION 关键字集为将来在当前连接上执行的事务设置默认事务级别。
任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。
你可以用下列语句查询全局和会话事务隔离级别：
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
在行级锁定中，InnoDB使用next-key锁定。这意味着除了索引记录，InnoDB也可以把索引记录前的间隙锁定到其它用户所做的紧接该索引记录之前的块插入上。一个next-key锁定指向一个锁定住一个索引记录和它之前的间隙的锁定。一个间隙锁定指仅锁住一些索引记录之前的间隙的锁定。
InnoDB中每个隔离级别的详细描述如下：
·READ UNCOMMITTED
SELECT语句以非锁定方式被执行，但是一个可能更早期版本的记录会被用到。因此，使用这个隔离级别，比如，读是不连贯的。着也被称为“脏读”（dirty read）。另外，这个隔离级别象READ COMMITTED一样作用。
·READ COMMITTED
一个有些象Oracle的隔离级别。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD语句仅锁定索引记录，而不锁定记录前的间隙，因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录，而不包括记录前的间隙。在范围类型UPDATE和DELETE语句，InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的，因为要让MySQL复制和恢复起作用，“幽灵行”必须被阻止掉。
持续读行为如同在Oracle中：即使在同一事务内， 每个持续读设置并读取它自己的新快照。请参阅15.2.10.4节，“持续非锁定读”。
·REPEATABLE READ
这是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录，而不锁定记录前的间隙。用其它搜索条件，这些操作采用next-key锁定，用next-key锁定或者间隙锁定锁住搜索的索引范围，并且阻止其它用户的新插入。
在持续读中，有一个与之前隔离级别重要的差别：在这个级别，在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句，这些SELECT语句对相互之间也是持续的，请参阅15.2.10.4节，“持续非锁定读”。
·SERIALIZABLE
这个级别类似REPEATABLE READ，但是所有无格式SELECT语句被隐式转换成SELECT ... LOCK IN SHARE MODE。
15.2.10.4. 持续非锁定读
持续读意味着InnoDB使用它的多版本化来给一个查询展示某个时间点处数据库的快照。查询看到在那个时间点之前被提交的那些确切事务做的更改，并且没有其后的事务或未提交事务做的改变。这个规则的例外是，查询看到发布该查询的事务本身所做的改变。
如果你运行在默认的REPEATABLE READ隔离级别，则在同一事务内的所有持续读读取由该事务中第一个这样的读所确立的快照。你可以通过提交当前事务并在发布新查询的事务之后，为你的查询获得一个更新鲜的快照。
持续读是默认模式，在其中InnoDBzai在READ COMMITTED和REPEATABLE READ隔离级别处理SELECT语句。持续读不在任何它访问的表上设置锁定，因此，其它用户可自由地在持续读在一个表上执行的同一时间修改这些表。
注意，持续读不在DROP TABLE和ALTER TABLE上作用。持续读不在DROP TABLE上作用，因为MySQL不能使用已经被移除的表，并且InnoDB 破坏了该表。持续读不在ALTER TABLE上作用，因为它在某事务内执行，该事务创建一个新表，并且从旧表往新表中插入行。现在，当你重新发出持续读之时，它不能在新表中看见任何行，因为它们被插入到一个在持续读读取的快照中不可见的事务里。
15.2.10.5. 锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
在一些环境中，一个持续读是不方便的。比如，你可能想要往表的子表里添加一个新行，并确信该子表在父表中有一个根。下列例子显示如何在你应用程序代码中实现参考的完整性。
假设你使用一个持续读去读取父表并且看到表中子表的根。不能安全地往子表添加子行吗？不，因为可能同时发生一些其它用户从父表删除父行，而你没有注意到它的情况。
解决办法是在使用LOCK IN SHARE MODE的锁定模式执行SELECT：
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
在共享模式执行一个读意味着我们读最新的可用数据，并在我们读的行设置一个共享锁定。共享模式锁防止其它人更新或删除我们已读的行。同时，如果最新的数据属于其它客户端尚未提交的事务，我们等着知道那个事务被提交。我们看到前述的查询返回父'Jones'，我们可以安全地 往子表添加子记录并提交我们的事务。
让我们看另外一个例子：我们在表child_codes 中有一个整数计数器域，我们用该表给每个添加到子表里的子项指派一个唯一的识别符。显然，使用持续读或者共享模式读去读取当前计数器的值并是一个好主意， 因为数据库的两个用户可能看到计数器的同一个值，如果两个用户试着用同一识别符往该表添加子项，就会发生一个重复键（duplicate-key）错误。
在这里，如果两个用户同时读计数器，当试图更新计数器之时，至少它们中有一个会发生死锁错误并终止，因此LOCK IN SHARE MODE并不是一个好的解决方法。
在这种情况下，有两个好方法去实现读计数器和增长计数器值：(1) 先更新计数器，让计数器值增1，之后读计数器，或者(2)用锁定模式FOR UPDATE先读计数器，之后计数器值增加。后一个途径可被如下实现：
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
SELECT ... FOR UPDATE读最新的可见数据，在每个它读取的行设置独占锁定。因此，它设置与搜索的SQL UPDATE可能会在行上设置的锁定同样的锁定。
请注意，以上仅是一个SELECT ... FOR UPDATE如何起作用的例子。在MySQL中，事实上生成一个唯一识别符的特殊任务可被用对该表的单独访问来完成：
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
SELECT语句仅仅取回识别符信息（专门对当前连接）。它不访问任何表。
15.2.10.6. Next-Key锁定：避免幽灵问题
在行级锁定中，InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。
InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定，另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。假设你想要从有一个标识符值大于100的子表读并锁定所有子记录，并想着随后在选定行中更新一些列：
SELECT * FROM child WHERE id > 100 FOR UPDATE;
假设在id列有一个索引。查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外，一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT，你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的：一个事务应该能够运行，以便它已经读的数据在事务过程中不改变。如果我们把一套行视为数据项，新的“幽灵”子记录可能会违反这一隔离原则。
当InnoDB扫描一个索引之时，它也锁定所以记录中最后一个记录之后的间隙。刚在前一个例子中发生：InnoDB设置的锁定防止任何插入到id可能大过100的表。
你可以用next-key锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据，并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此，the next-key锁定允许你锁住在你的表中并不存在的一些东西。
15.2.10.7. 持续读如何在InnoDB中工作的例子
假设你正运行在默认的REPEATABLE READ隔离级别。当你发出一个持续读之时，即一个普通的SELECT语句，InnoDB按照你的查询看到的数据库，给你的事务一个时间点。如果另一个事务在你的时间点被指定之后删除一行并提交，你不会看见已被删除的行。插入和更新被相似地处理。
你可以通过提交你的事务来提前你的时间点，然后做另一个SELECT。
这被称为多版本并发控制（multi-versioned concurrencycontrol）。
User A User B
SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (NULL, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
1 row in set
在这个例子中，只有当用户B已经提交插入，并且用户A也已经提交之时，用户A可看见被用户B插入的行，因此时间点是在用户B提交之前。
如果你想看数据库的最新状态，你应该用READ COMMITTED隔离级别或用一个锁定读：
SELECT * FROM t LOCK IN SHARE MODE;
15.2.10.8. 在InnoDB中不同SQL语句设置的锁定
在SQL查询处理中，一个锁定读，一个UPDATE或一个DELETE一般地对被扫描的每个索引记录设置记录锁定。如果在某查询中有一个WHERE条件是没什么关系的，而这个查询可能从查询的结果包中排除行。InnoDB不记得确切的WHERE条件，但是仅知道哪个索引范围被扫描。记录锁定是正常的next-key锁定，它也阻止对紧接着记录之前的间隙的插入。
如果锁定被设置为独占，则InnoDB总是取回集束的索引目录并对其设置锁定。
如果你没有适合查询的索引，MySQL不得不扫描整个表来处理查询，表的每行变成被锁定的，这样反过来阻止其它用户的所有对表的插入。创建一个好的索引让你的查询不必要扫描很多行是很重要的。
·SELECT ... FROM是一个持续读，读取数据库的快照并且设置不锁定，除非事务隔离级别被设为SERIALIZABLE。对于 SERIALIZABLE级别，这个设置对它遇到的索引记录设置共享的next-key锁定。
·SELECT ... FROM ... LOCK IN SHARE MODE对读遇到的所有索引记录设置共享的next-key锁定。
·SELECT ... FROM ... FOR UPDATE对读遇到的所有索引记录设置独占的next-key锁定。
·INSERT INTO ... VALUES (NULL, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, UNLOCK TABLES.
·UNLOCK TABLES 仅在如果任何表当前被锁定之时，提交一个事务。
·在InnoDB中的CREATE TABLE语句被作为一个单独的事务来处理。这意味着来自用户的ROLLBACK不撤销用户在事务过程中生成的CREATE TABLE语句。
事务不能被嵌套。当你发出START TRANSACTION语句或与之同义的语句之时，这是对任何当前事务隐式提交的一个结果。
15.2.10.10. 死锁检测和回滚
InnoDB自动检测事务的死锁，并回滚一个或几个事务来防止死锁。InnoDB试着挑选小事务来回滚，事务的大小通过被插入、更新或删除的行的数量来确定。
如果innodb_table_locks=1 （1是默认值），InnoDB意识到表锁定，其上的MySQL层知道row-level锁定。另外InnoDB不能在MySQL LOCK TABLES设定表锁定的地方或者涉及InnoDB之外的存储引擎设置锁定的地方检测死锁。你必须通过设定innodb_lock_wait_timeout系统变量的值来解决这些情况。
当InnoD执行完全的事务回滚之时，该事务的所有锁定被释放。尽管如此，如果单个SQL语句被因为错误的原因被回滚，该SQL语句设定的部分锁定可能被保留。这是因为InnoDB以一种方式存储行锁定，在这种方式中它不能知道随后的哪个锁定是被哪个SQL语句设定的。
15.2.10.11. 如何处理死锁
死锁是事务型数据库典型的问题，但是除非它们频繁出现以至于你更本不能运行某个事务，它们一般是不危险的。正常地，你必须编写你的应用程序使得它们总是准备如果因为死锁而回滚一个事务就重新发出一个事务。
InnoDB使用自动行级锁定。即使在只插入或删除单个行的事务的情况下，你可以遇到死锁。这是因为这些操作不是真正的“极小的”，它们自动对插入或删除的行的（可能是数个）索引记录设置锁定。
你可以用下列技术对付死锁减少它们发生的可能性：
·用Use SHOW INNODB STATUS来确定最后一个死锁的原因。这样可以帮助你调节应用程序来避免死锁。
·总是准备着重新发出事务，如果它因为死锁而失败了。死锁不危险，再试一次。
·经常提交你的事务。小事务更少地倾向于冲突。
·如果你正使用锁定读，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。
·以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。
·添加精心选定的索引到你的表。则你的查询需要扫描更少的索引记录并且因此设置更少的锁定。使用EXPLAIN SELECT来确定对于你的查询,MySQL认为哪个索引是最适当的。
·使用更少的锁定。如果你可以接受允许一个SELECT从一个旧的快照返回数据，不要给它添加FOR UPDATE或LOCK IN SHARE MODE子句。这里使用READ COMMITTED隔离级别是比较好的，因为每个在同一事务里的持续读从它自己新鲜的快照里读取。
·如果没有别的有帮助的了，用表级锁定系列化你的事务。用LOCK TABLES对事务型表（如InnoDB)的正确方法是设置AUTOCOMMIT = 0 并且不调用UNLOCK TABLES直到你明确地提交了事务。例如，如果你需要写表t1并从表t读，你可以按如下做：
·SET AUTOCOMMIT=0;
·LOCK TABLES t1 WRITE, t2 READ, ...;
·[do something with tables t1 and t2 here];
·COMMIT;
·UNLOCK TABLES;
表级锁定使得你的事务很好地排队，并且死锁被避免了。
·领一个系列化事务的方法是创建一个辅助的“semaphore” 表，它只包含一个单行。让每个事务在访问其它表之前更新那个行。以这种方式，所有事务以序列的方式发生。注意，InnoDB即时死锁检测算法也能在这种情况下起租用，因为系列化锁定是行级锁定。超时方法，用MySQL表级锁定，必须被用来解决死锁。
·在应用程序中使用LOCK TABLES命令，如果AUTOCOMMIT=1,MySQL不设定InnoDB表锁定。
15.2.11. InnoDB性能调节提示
15.2.11.1. SHOW INNODBSTATUS和InnoDB Monitors
·如果Unix顶层工具或者Windows任务管理器显示，你的数据库的工作负荷的CPU使用率小于70%，则你的工作负荷可能是磁盘绑定的，可能你正生成太多的事务和提交，或者缓冲池太小。使得缓冲池更大一些会有帮助的，但不要设置缓冲池等于或超过物理内存的80%.
·把数个修改裹进一个事务里。如果事务对数据库修改，InnoDB在该事务提交时必须刷新日志到磁盘。因为磁盘旋转的速度至多167转/秒，如果磁盘没有骗操作系统的话，这就限制提交的数目为同样的每秒167次。
·如果你可以接受损失一些最近的已提交事务，你可以设置my.cnf文件里的参数innodb_flush_log_at_trx_commit为0。 无论如何InnoDB试着每秒刷新一次日志，尽管刷新不被许可。
·使用大的日志文件，让它甚至与缓冲池一样大。当InnoDB写满日志文件时，它不得不在一个检查点把缓冲池已修改的内容写进磁盘。小日志文件导致许多不必要的吸盘写操作。大日志文件的缺点时恢复时间更长。
·也让日志缓冲相当大（与8MB相似的数量)。
·如果你存储变长度字符串，或者列可能包含很多NULL值，则使用VARCHAR列类型而不是CHAR类型。一个CHAR(N)列总是占据N 字节来存储，即使字符串更短或字符串的值是NULL。越小的表越好地适合缓冲池并且减少磁盘I/O。
当使用row_format=compact （MySQL 5.1中默认的InnoDB记录格式）和可变长度字符集，比如UTF-8或sjis，CHAR(N)将占据可变数量的空间，至少为N 字节。
·在一些版本的GNU/Linux和Unix上，用Unix的fsync()（InnoDB默认使用的）把文件刷新到磁盘，并且其他相似的方法是惊人的慢。如果你不满意数据库的写性能，你可以试着设置my.cnf里的innodb_flush_method为O_DSYNC，虽然O_DSYNC在多数系统上看起来更慢。
·当在Solaris 10上，为x86_64架构（AMD Opteron)使用InnoDB存储引擎，重要的是使用forcedirectio选项来安装任何为存储与InnoDB相关的文件而使用的数据系统。（默认在Solaris 10/x86_64上不使用这个文件系统安装选项）。使用forcedirectio 失败会导致InnoDB在这个平台上的速度和性能严重退化。
·当导入数据到InnoDB中之时，请确信MySQL没有允许autocommit模式，因为允许autocommit模式会需要每次插入都要刷新日志到磁盘。要在导入操作规程中禁止autocommit模式，用SET AUTOCOMMIT和COMMIT语句来包住导入语句：
·SET AUTOCOMMIT=0;
·/* SQL import statements ... */
·COMMIT;
如果你使用mysqldump 选项--opt，即使不用SET AUTOCOMMIT和COMMIT语句来包裹，你也使得快速的转储文件被导入到InnoDB表中。
·小心大宗插入的大回滚：InnoDB在插入中使用插入缓冲来节约磁盘I/O，但是在相应的回滚中没有使用这样的机制。一个磁盘绑定的回滚可以用相应插入花费时间的30倍来执行。杀掉数据库进程没有是帮助的，因为回滚在服务器启动时会再次启动。除掉一个失控的回滚的唯一方法是增大缓冲池使得回滚变成CPU绑定且跑得快，或者使用专用步骤，请参阅15.2.8.1节，“强制恢复”。
·也要小心其它大的磁盘绑定操作。用DROP TABLE或CREATE TABLE来清空一个表，而不是用DELETE FROM tbl_name。
·如果你需要插入许多行，则使用多行插入语法来减少客户端和服务器之间的通讯开支：
·INSERT INTO yourtable VALUES (NULL,2), (5,5), ...;
这个提示对到任何表类型的插入都是合法的，不仅仅是对InnoDB类型。
·如果你在第二个键上有UNIQUE约束，你可以在导入会话中暂时关闭唯一性检查以加速表的导入：
·SET UNIQUE_CHECKS=0;
对于大表，这节约了大量磁盘I/O，因为InnoDB可以使用它的插入缓冲来在一批内写第二个索引记录。
·如果你对你的表有FOREIGN KEY约束，你可以在导入会话过程中通过关闭外键检查来提速表的导入：
·SET FOREIGN_KEY_CHECKS=0;
对于大表，这可以节约大量的磁盘I/O。
·如果你经常有对不经常更新的表的重发查询，请使用查询缓存：
·[mysqld]
·query_cache_type = ON
·query_cache_size = 10M
15.2.11.1. SHOW INNODB STATUS和 InnoDB Monitors
InnoDB包含InnoDB Monitors，它打印有关InnoDB内部状态的信息。你可以使用SQL语句SHOW INNODB STATUS来取标准InnoDB Monitor的输出送到你的SQL客户端。这个信息在性能调节时有用。（如果你正使用mysql事务SQL客户端，如果你用\G替代通常的语句终止符分号，输出结果会更可读）。关于InnoDB锁定模式的讨论，请参阅15.2.10.1节，“InnoDB锁定模式”。
mysql> SHOW INNODB STATUS\G
另一个使用InnoDB Monitors的方法时让它们不断写数据到mysqld服务器的标准输出。在这种情况下，没有输出被送到客户端。当被打开的时候，InnoDB Monitors每15秒打印一次数据。服务器输出通常被定向到MySQL数据目录里的.err日志。这个数据在性能调节时有用。在Windows上，如果你想定向输出到窗口而不是到日志文件，你必须从控制台窗口的命令行使用--console选项来启动服务器。
监视器输出包括下列类型的信息：
·被每个激活事务持有的表和记录锁定
·事务的锁定等待
·线程的信号等待
·未解决的文件I/O请求
·缓冲池统计数据
·主InnoDB线程的净化和插入缓冲合并活动
要让标准InnoDB Monitor写到mysqld的标准输出，请使用下列SQL语句：
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
监视器可以通过发出下列语句来被停止：
DROP TABLE innodb_monitor;
CREATE TABLE语法正是通过MySQL的SQL解析程序往InnoDB引擎传递命令的方法：唯一有问题的事情是表名字innodb_monitor及它是一个InnoDB表。对于InnoDB Monitor, 表的结构根本不相关。如果你在监视器正运行时关闭服务器，并且你想要再次启动监视器，你必须在可以发出新CREATE TABLE语句启动监视器之前移除表。这个语法在将来的发行版本中可能会改变。
你可以以相似的方式使用innodb_lock_monitor。除了它也提供大量锁定信息之外，它与innodb_monitor相同。一个分离的 innodb_tablespace_monitor打印存在于表空间中已创建文件片断的一个列表，并且确认表空间分配数据结构。此外，有innodb_table_monitor，用它你可以打印InnoDB内部数据词典的内容。
InnoDB Monitor输出的一个例子：
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:
X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser
ting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) VALUES (NULL, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;
hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex
00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a
sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......
;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser
ting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'
,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser
ting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'
h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa
35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in
serting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc VALUES (NULL,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh
gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co
mmitting
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm VALUES (NULL,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh
hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st
arting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM
ARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro
llback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) VALUES (NULL, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f
','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in
serting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,
'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to 18 1212665295
Last checkpoint at 18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size 3200
Free buffers 110
Database pages 3074
Modified db pages 2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.05 sec)
关于这个输出一些要注意的：
·如果TRANSACTIONS节报告锁定等待，你的应用程序可能有锁定竞争。输出也帮助跟踪事务死锁的原因。
·SEMAPHORES节报告等待一个信号的线程和关于线程对一个互斥体或rw-lock信号需要多少次回滚或等待的统计数据。大量等待信号的线程可能是磁盘I/O的结果或者InnoDB内竞争问题的结果。竞争可能是因为查询的严重并行，或者操作系统线程计划的问题。设置innodb_thread_concurrency小于默认值在这种情况下是有帮助的。
·BUFFER POOL AND MEMORY节给你关于页面读写的统计数据。你可以从这些数计算你当前的查询正做多少文件数据I/O操作。
·ROW OPERATIONS节显示主线程正在做什么。
InnoDB发送诊断输出到stderr或文件，而不是到stdout或者固定尺寸内存缓冲，以避免底层缓冲溢出。作为一个副效果，SHOW INNODB STATUS的输出每15秒钟写到一个状态文件。这个文件的名字是innodb_status.pid，其中pid 是服务器进程ID。这个文件在MySQL数据目录里创建。正常关机之时，InnoDB删除这个文件。如果发生不正常的关机， 这些状态文件的实例可能被展示，而且必须被手动删除。在移除它们之前，你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项innodb_status_file=1被设置之时，innodb_status.pid文件被创建。
15.2.12. 多版本的实施
因为InnoDB是多版本化的数据库, 它必须保持关于表空间中旧版本行的信息。这个信息被存在名为rollback segment（在Oracle中模拟数据结构之后）的数据结构中。
内部地，InnoDB往存在数据库中的每一行中添加两个域。一个6字节的域说明插入或更新该行的最后一个事务的事务识别符。同时，一个删除也被内部处理为一个更新，其中行中一个特殊的位被设置来标注该行为已删除。每一行也包含一个称为滚动指针的7字节域。滚动指针指向一个被写到回滚片断的撤销日志记录。如果该行被更新，撤销日志记录包含在该行被更新之前重建该行的内容必需的的信息。
InnoDB使用在回滚片断中的信息来执行在事务回滚中需要的撤销操作。它也使用这个信息来为一个持续读构建更早版本的行。
在回滚片断中的撤销日志被分为插入和更新撤销日志。插入撤销日志仅在事务回滚中需要，且只要事务一提交就可以被丢弃。更新撤销日志也被用在持续读中，而且它们仅在当前没有被InnoDB分配给一个快照的事务之后被丢弃，这个快照在持续读中可能会需要更新撤销日志的信息来建立一个数据库行的早期版本。
你必须记得规律地提交你的事务，包括那些只发布持续读的事务。否则, InnoDB不能从更新撤销日志丢弃数据，并且回滚片断可能变得太大，填满你的表空间。
在一个回滚片断里，一个撤销日志记录的物理尺寸典型地小于相应的已插入行或已更新行。你可以用这个信息来计算回滚片断需要的空间。
在InnoDB多版本化方案中，当你用SQL语句删除一行之时，该行没有被从数据库立即物理删除掉。只有当InnoDB可以丢弃为删除而被写的更新撤销日志记录时，InnoDB也物理地从数据库删除相应行和它的索引记录。这个删除操作被成为精华，它运行得很快，通常与做删除的SQL语句花的时间在一个数量级。
在某一情景下，在那里，用户以几乎相同的比率，小批次地在表中插入和删除行，净化线程开始滞后是可能的，并且表变得越来越大，使得每样事都是磁盘绑定的而且非常慢。即使表仅载有10MB有用的数据，它可能变得用所有的死行占据10GB空间。在这种情况下，节流新操作，并分配更多的资源来净化线程可能是比较好的。启动选项和可设置全球变量innodb_max_purge_lag就是为这个目的而存在的。请参阅15.2.4节，“InnoDB 启动选项” 以获得更多信息。
15.2.13. 表和索引结构
15.2.13.1.索引的物理结构
15.2.13.2. 缓冲插入
15.2.13.3. 适应的哈希索引
15.2.13.4. 物理记录结构
MySQL为表把它的数据词典信息以.frm文件的形式存在数据库目录里，这对所有MySQL存储引擎都是真的。但是每个InnoDB表在表空间内的InnoDB内部数据词典里有它自己的条目。当MySQL移除表或数据库，它不得不删除.frm文件和InnoDB数据词典内的相应条目。这就是为什么你不能在数据库之间简单地移动.frm文件来移动InnoDB表。
每个InnoDB表有专门索引，被称为clustered index，对行的数据被存于其中。如果你对你的表定义一个PRIMARY KEY，主键的索引是集束索引。
如果你没有为表定义PRIMARY KEY，MySQL拾取第一个仅有NOT NULL列的UNIQUE索引作为主键，并且InnoDB把它当作集束索引来用。如果表中没有这样一个索引，InnoDB内部产生一个集束索引，其中用InnoDB在这样一个表内指定给行的行ID来排序行。行ID是一个6字节的域，它在新行被插入的时候单一地增加。因此被行ID排序的行是物理地按照插入顺序排的。
通过集束索引访问一个行是较快的，因为行数据是在索引搜索引导的同一页面。如果表是巨大的，当对比于传统解决方案，集束索引构架经常节约磁盘I/O。（在许多数据库，数据传统地被存在与索引记录不同的页）。
在InnoDB中，非集束索引里的记录（也称为第二索引）包含对行的主键值。InnoDB用这个主键值来从集束索引中搜索行。注意，如果主键是长的，第二索引使用更多空间。
InnoDB比较CHAR和VARCHAR字符串不同长度，以便在较短字符串中剩下的长度被处理视为用空格补上的。
15.2.13.1. 索引的物理结构
所有InnoDB的索引是B数，其中索引记录被存储在树的树叶页。一个索引页的默认大小是16KB。当新记录被插入，InnoDB试着为将来索引记录的插入和更新留下十六分之一的空白页。
如果索引记录以连续的顺序被插入（升序或者降序），结果索引页大约是15/16满。如果记录被以随机的顺序被插入，页面是从1/2到 15/16满。如果索引页的填充因子降到低于1/2，InnoDB试着搜索索引树来释放页。
15.2.13.2. 缓冲插入
在数据库应用中，主键是一个唯一的识别符，并且新行被以主键的升序来插入，这是个常见的情况。因此，到集束索引的插入不需要从一个磁盘随机读。
另一方面，第二索引通常是非唯一的，到第二索引的插入以相对随机次序发生。这可能会导致大量的随机磁盘I/O操作，而没有一个被用在InnoDB中的专用机制。
如果一个索引记录应该被插入到一个非唯一第二索引，InnoDB检查第二索引页是否在缓冲池中。如果是，InnoDB直接插入到索引页。如果索引页没有在缓冲池中被发现，InnoDB插入记录到一个专门的插入缓冲结构。插入缓冲被保持得如此小以至于它完全适合在缓冲池，并且可以非常快地做插入。
插入缓冲周期地被合并到数据库中第二索引树里。把数个插入合并到索引树的同一页，节省磁盘I/O操作，经常地这是有可能的。据测量，插入缓冲可以提高到表的插入速度达15倍。
在插入事务被提交之后，插入缓冲合并可能连续发生。实际上，服务器关闭和重启之后，这会连续发生。（请参阅15.2.8.1节，“强制恢复”）。
当许多第二索引必须被更新之时，并且许多行已被插入之时，插入缓冲合并可能需要数个小时。在这个时间内，磁盘I/O将会增加，这样会导致磁盘绑定查询明显缓慢。另一个明显的后台I/O操作是净化线程（请参阅15.2.12节，“实现多版本化”）。
15.2.13.3. 适应的哈希索引
如果一个表几乎完全配合主内存，在其上执行查询最快的方法就是使用哈希索引。InnoDB有一个自动机制，它监视对为一个表定义的索引的索引搜索。如果InnoDB注意到查询会从建立一个哈希索引中获益，它会自动地这么做。
注意，哈希索引总是基于表上已存在的B树索引来建立。根据InnoDB对B树索引观察的搜索方式，InnoDB会在为该B树定义的任何长度的键的一个前缀上建立哈希索引。 哈希索引可以是部分的：它不要求整个B树索引被缓存在缓冲池。InnoDB根据需要对被经常访问的索引的那些页面建立哈希索引。
在某种意义上，InnoDB通过针对丰富主内存的适应的哈希索引机制来剪裁自己，更加靠近主内存数据库的架构。
15.2.13.4. 物理记录结构
InnoDB表中的记录有如下特征：
·InnoDB中每个索引记录包含一个6字节的头。这个头被用来将连续的记录连接在一起，并且也用在row-level锁定中。
·集束索引中的记录包含对所有自定义列的域。此外，有一个6字节的域给事务ID以及一个7字节的域给滚动指针。
·如果没有为一个表定义初级键，每个集束索引记录也包含一个6字节的行ID域。
·每个第二索引记录也包含为集束索引键定义的所有域。
·一个记录也包含一个指向该记录每一个域的指针，如果在一个记录中域的总长度小于128字节，该指针时一个字节；否则就是2字节。这些指针的阵列被称为记录目录。这些指针指向的区域被称为记录的数据部分。
·内部地，InnoDB以固定长度格式存储固定长度字符列，比如CHAR(10)。InnoDB从VARCHAR列截短跟踪空间。注意，MySQL可以内部地把CHAR列转换为VARCHAR列。请参阅13.1.5.1节，“安静的列规格改变”。
·一个SQL的NULL值在记录目录里保留1或2字节。此外，SQL的NULL值如果被保存在可变长度列，则在记录数据部分保留零字节。在一个固定长度列，它在记录的数据部分保留该列的固定长度。为NULL值保留固定空间背后的动机是之后该列从NULL值到非NULL值的更新可以就地完成，且不会导致索引页的碎片。
15.2.14.文件空间管理和磁盘I/O
15.2.14.1. 磁盘I/O
15.2.14.2. 为表空间使用原始设备
15.2.14.3.文件空间管理
15.2.14.4.整理表碎片
15.2.14.1. 磁盘 I/O
InnoDB使用模拟异步磁盘I/O：InnoDB创建许多线程来处理I/O操作，比如read-ahead.
在InnoDB中有两个read-ahead试探：
·在连续read-ahead中，如果InnoDB注意到在表空间中对一个片断的访问方式是连续的，它就预先布置一批数据库页的读 给I/O系统。
·在随机read-ahead中，如果InnoDB注意到表空间中的一些区域看起来进入完全读进缓冲池中的处理中，它就布置剩余的读到I/O系统。
InnoDB使用一个被称为doublewrite的新颖的文件刷新技术。它给操作体统崩溃或掉电后的恢复添加了安全，并且通过减少对fsync()操作的需要，它在多数Unix变种上改善了性能。.
Doublewrite意为在向一个数据文件写页之前，InnoDB首先把它们写到一个毗邻的表空间区域，称为doublewrite缓冲。仅在写然后刷新到doublewrite已经完成之前，InnoDB写页面到它们在表空间中恰当的位置。如果操作系统在写页面的中间崩溃，在恢复过程中，InnoDB可以在随后从doublewrite缓冲中找到页面的一个良好复制。
15.2.14.2. 为表空间使用原始设备
你也可以使用原始磁盘分区作为表空间数据文件。通过使用原始磁盘，你可以在Windows和一些Unix系统上执行non-buffered I/O 而无须文件系统开支，这样可以改善性能
当你创建一个新数据文件之时，你必须在innodb_data_file_path里紧接着数据文件尺寸之后放置关键字newraw。分区必须至少和你指定的尺寸一样大，注意，在InnoDB中，1MB是1024x1024字节， 但是在磁盘规格中，1MB通常意为1,000,000字节。
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
下次你启动服务器之时，InnoDB注意到关键字newraw并初始化新分区。但是仍然并不创建或改变任何InnoDB表。另外，当你重启服务器之时，InnoDB重新初始化分区，你的改变会丢失。（从3.23.44启动，作为一个安全措施， 当用newraw指定任何分区之时，InnoDB阻止用户修改数据）。
InnoDB初始化新分区之后，停止服务器，在对行的数据文件规格中改变newraw：
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw
然后重启动服务器，并且InnoDB允许做改变。
在Windows上，你可以象这样分配磁盘分区为一个数据文件：
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw
对于访问物理驱动器，//./ 相当于Windows语法的\\.\ 。
当你使用原始磁盘分区之时，确信它们有允许被用来运行MySQL服务器的帐号读和写访问的许可。
15.2.14.3.文件空间管理
你在配置文件中定义的数据文件形成InnoDB的表空间。文件被简单地连起来以形成表空间。没有条纹在使用。当前你不能定义你的表被分配到表空间中的位置。但是，在一个新创建的表中间中，InnoDB 从第一个数据文件开始分配空间。
表空间包含数据库页，默认大小是16KB。这些页被分组成64个连续页的范围。表空间内的文件在InnoDB中被称为片断。术语“rollback segment”有一些混淆，因为它确切地包含许多表空间片断。
在InnoDB中，每个索引分配两个片断。一个是给B树的非树叶节点的，另一个是给树叶节点的。在这里，理想的是为包含数据的树叶节点达到更好的有序性。
当一个片断在表空间内长大，InnoDB单独地分配最先的32页给它。此后InnoDB开始分配整个范围给该片断。InnoDB可以一次给一个大片断添加多达4个范围以确保数据良好的连续性。
在表空间中的一些页包含其它页的位图，因此在一个InnoDB表空间中的一些范围不能被整个地分配给片断，只能作为单个页被分配。
当你发出SHOW TABLE STATUS询问表空间里可用的自由空间之时，InnoDB报告在表空间中完全自由的范围。InnoDB总是为扫除和其它内部目的保留一些范围，这些保留的范围不包括在自由空间里。
当你从一个表中删除数据之时，InnoDB联系相应的B树索引。是否释放单独页或是范围到表空间取决删除的方式，因此被释放的空间变成对其它用户可用，但是记住，已删除的行仅在该行不再被事务回滚或持续读所需要之后的一个（自动）净化操作中被物理删除。
15.2.14.4. 整理表碎片
如果有随机插入到表的索引或从表的索引随机删除，索引可能变成碎片的。碎片意思是索引页在磁盘上的物理排序并不接近页上记录的索引排序，或者在分配给索引的64页块上有许多没有被使用的页。
碎片的一个“同义词”是一个表占据的空间超过它应该占据的空间的大小。确切是多少，这是很难去确定的。所有InnoDB数据和索引被存在B树中，并且它们的填充因子可能从50%到100%。碎片的另一个“同义词”是一个表扫描例如：
SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;
花了超过它应该花的时间。（在上面的查询中我们“欺骗”SQL优化器来扫描集束索引，而不是一个第二索引）。多数磁盘可以读10MB/s到50MB/s，这可以被用来评估一个表扫描可以多快地运行。
如果你周期地执行“null” ALTER TABLE操作，它就可以加速索引扫描：
ALTER TABLE tbl_name ENGINE=INNODB
这导致MySQL重建表。另一个执行碎片整理操作的办法是使用mysqldump来转储一个表到一个文本文件，移除表，并重新从转储文件重装载它。
如果到一个索引的插入总是升序的，并且记录仅从末尾被删除，InnoDB文件空间管理保证在索引中的碎片不会发生。
15.2.15. InnoDB错误处理
15.2.15.1. InnoDB错误代码
15.2.15.2.操作系统错误代码
在InnoDB中错误处理不像SQL标准中指定的一样。按照标准，在SQL语句过程中的任何错误应该导致该语句的回滚。InnoDB有时仅回滚部分语句，或者整个事务。下列条目叙述InnoDB如何执行错误处理：
·如果你耗尽表空间中的文件空间，你使得MySQL表完全错误，并且InnoDB返回SQL语句。
·一个事务死锁导致InnoDB回滚整个事务，在锁定等待超时的情况，InnoDB仅回滚最近的SQL语句。
当一个事务回滚因为死锁或锁定等待超时而发生，它在事务中撤销语句的作用。但是如果事务是用START TRANSACTION或BEGIN语句开始的，它就不撤销该语句。进一步，SQL语句变成事务的一部分直到COMMIT, ROLLBACK或者导致暗地提交的SQL语句发生。
·如果你没有在语句中指定IGNORE选项，重复键错误回滚SQL语句。
·行太长错误回滚SQL语句。
·其它错误主要被代码的MySQL层（在InnoDB存储引擎级别以上）探测，它们回滚相应的SQL语句。在单个SQL语句中的回滚中锁定不被释放。
在暗地回滚过程中，就像在一个明确的ROLLBACK SQL命令的执行过程中一样，SHOW PROCESSLIST在State列为有关的连接显示Rolling back。
15.2.15.1. InnoDB错误代码
下面的这个不完全列表是你可能遇见的常见的InnoDB专有错误，带着为什么发生的原因以及如何该解决问题的相关信息
·1005 (ER_CANT_CREATE_TABLE)
不能创建表。如果错误信息字符串指向errno 150，因为外键约束被不正确地形成，所以表创建失败。
·1016 (ER_CANT_OPEN_FILE)
虽然对某表的.frm文件存在，但不能从InnoDB数据文件找到该InnoDB表。请参阅15.2.17.1节，“InnoDB数据词典操作故障诊断和排除”。
·1114 (ER_RECORD_FILE_FULL)
InnoDB耗尽表空间中的可用空间，你应该重新配置表空间来添加一个新数据文件。
·1205 (ER_LOCK_WAIT_TIMEOUT)
锁定等待超时过期。事务被回滚。
·1213 (ER_LOCK_DEADLOCK)
事务死锁。你应该重运行事务。
·1216 (ER_NO_REFERENCED_ROW)
你正试着添加一行，但没有父行，并且一个外键约束失败。你应该先添加父行。
·1217 (ER_ROW_IS_REFERENCED)
你正试图删除一个有子行的父行，并且一个外键约束失败。你应该先删除子行。
15.2.15.2. 操作系统错误代码
要打印一个操作系统错误号的意思，请使用MySQL分发版里的perror程序。
下面表提供一些常用Linux系统错误代码。更完整的列表请参阅Linuxsource code。
·1 (EPERM)
操作不被允许
·2 (ENOENT)
无此文件或目录
·3 (ESRCH)
无此进程
·4 (EINTR)
中断的系统调用
·5 (EIO)
I/O 错误
·6 (ENXIO)
无此设备或地址
·7 (E2BIG)
Arg列表太长
·8 (ENOEXEC)
Exec合适错误
·9 (EBADF)
坏文件号
·10 (ECHILD)
无子进程
·11 (EAGAIN)
再试一次
·12 (ENOMEM)
内存耗尽
·13 (EACCES)
许可被否定
·14 (EFAULT)
坏地址
·15 (ENOTBLK)
阻止需求的设备
·16 (EBUSY)
设备或资源忙
·17 (EEXIST)
文件存在
·18 (EXDEV)
交叉设备连接
·19 (ENODEV)
无此设备
·20 (ENOTDIR)
不是一个目录
·21 (EISDIR)
是一个目录？
·22 (EINVAL)
非法参量
·23 (ENFILE)
文件表溢出
·24 (EMFILE)
打开的文件过多
·25 (ENOTTY)
设备不适合的ioctl
·26 (ETXTBSY)
文本文件忙
·27 (EFBIG)
文件太大
·28 (ENOSPC)
设备上没空间了
·29 (ESPIPE)
非法查找
·30 (EROFS)
只读文件系统
·31 (EMLINK)
太多连接
下列表提供一列常用Windows系统错误代码。完整列表请访问Microsoft website。
·1 (ERROR_INVALID_FUNCTION)
不正确函数。
·2 (ERROR_FILE_NOT_FOUND)
系统不能找到指定的文件。
·3 (ERROR_PATH_NOT_FOUND)
系统不能找到指定的路径。
·4 (ERROR_TOO_MANY_OPEN_FILES)
系统不能打开文件。
·5 (ERROR_ACCESS_DENIED)
访问被拒绝。
·6 (ERROR_INVALID_HANDLE)
句柄非法。
·7 (ERROR_ARENA_TRASHED)
存储控制块被破坏。
·8 (ERROR_NOT_ENOUGH_MEMORY)
无足够存储来处理这个命令。
·9 (ERROR_INVALID_BLOCK)
存储控制块地址非法。
·10 (ERROR_BAD_ENVIRONMENT)
环境不正确。
·11 (ERROR_BAD_FORMAT)
试图用不正确的格式装载一个程序。
·12 (ERROR_INVALID_ACCESS)
访问代码不合法。
·13 (ERROR_INVALID_DATA)
数据不合法。
·14 (ERROR_OUTOFMEMORY)
无足够的存储来完成这个操作。
·15 (ERROR_INVALID_DRIVE)
系统不能找到指定的驱动器。
·16 (ERROR_CURRENT_DIRECTORY)
目录不能被删除。
·17 (ERROR_NOT_SAME_DEVICE)
系统不能移动此文件到一个不同的磁盘驱动器。
·18 (ERROR_NO_MORE_FILES)
没有更多文件。
·19 (ERROR_WRITE_PROTECT)
媒质写保护。
·20 (ERROR_BAD_UNIT)
系统不能找到指定的设备。
·21 (ERROR_NOT_READY)
设备未准备好。
·22 (ERROR_BAD_COMMAND)
设备不能识别此命令。
·23 (ERROR_CRC)
数据错误(循环冗余码校验).
·24 (ERROR_BAD_LENGTH)
程序发出一个命令，但是命令长度不正确。
·25 (ERROR_SEEK)
驱动器不能在磁盘上定位指定区域或磁道。
·26 (ERROR_NOT_DOS_DISK)
指定的磁盘或软盘不能被访问。
·27 (ERROR_SECTOR_NOT_FOUND)
驱动器不能找到请求的扇区。
·28 (ERROR_OUT_OF_PAPER)
打印机缺纸。
·29 (ERROR_WRITE_FAULT)
系统不能写指定设备。
·30 (ERROR_READ_FAULT)
系统不能从指定设备读。
·31 (ERROR_GEN_FAILURE)
附加到系统的设备不起作用。
·32 (ERROR_SHARING_VIOLATION)
进程不能访问文件，因为它正被另一个进程使用。
·33 (ERROR_LOCK_VIOLATION)
进程不能访问文件，因为另一个进程已经锁定文件的一部分。
·34 (ERROR_WRONG_DISK)
驱动器的的磁盘不正确，请插入 %2 (卷系列号: %3) 到驱动器 %1.
·36 (ERROR_SHARING_BUFFER_EXCEEDED)
太多文件被打开以共享。
·38 (ERROR_HANDLE_EOF)
到达文件的末尾。
·39 (ERROR_HANDLE_DISK_FULL)
磁盘已满。
·87 (ERROR_INVALID_PARAMETER)
参数不正确。（如果你在Windows中得到这个错误，并且已经在my.cnf或my.ini文件中设置innodb_file_per_table，则添加innodb_flush_method=unbuffered到你的my.cnf或my.ini文件）。
·112 (ERROR_DISK_FULL)
磁盘已满。
·123 (ERROR_INVALID_NAME)
文件名，目录名或者卷标语法不正确。
·1450 (ERROR_NO_SYSTEM_RESOURCES)
存在系统资源不够完成请求的服务。
15.2.16. 对InnoDB表的限制
·一个表不能包含超过1000列。
·内部最大键长度是3500字节，但MySQL自己限制这个到1024字节。
·除了VARCHAR, BLOB和TEXT列，最大行长度稍微小于数据库页的一半。即，最大行长度大约8000字节。LONGBLOB和LONGTEXT列必须小于4GB, 总的行长度，页包括BLOB和TEXT列，必须小于4GB。InnoDB在行中存储VARCHAR，BLOB或TEXT列的前768字节，余下的存储的分散的页面中。
·虽然InnoDB内部地支持行尺寸大于65535，你不能定义一个包含VARCHAR列的，合并尺寸大于65535的行。
·mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
· -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
· -> f VARCHAR(10000), g VARCHAR(10000));
·ERROR 1118 (42000): Row size too large. The maximum row size for the
·used table type, not counting BLOBs, is 65535. You have to change some
·columns to TEXT or BLOBs
·在一些更老的操作系统上，数据文件必须小于2GB。
·InnoDB日志文件的合并尺寸必须小于4GB。
·最小的表空间尺寸是10MB。最大的表空间尺寸是4,000,000,000个数据库页（64TB）。这也是一个表的最大尺寸。
·InnoDB表不支持FULLTEXT索引。
·ANALYZE TABLE 通过对每个索引树做八次随机深入并相应地更新索引集估值，这样来计数集。注意，因为这是仅有的估值，反复运行ANALYZE TABLE会产生不同数。这使得 ANALYZE TABLE 在 InnoDB 表上很快，不是百分百准确，因为它没有考虑所有的行。
MySQL 不仅在汇合优化中使用索引集估值。如果一些汇合没有以正确的方式优化，你可以试一下 ANALYZE TABLE 。很少有情况，ANALYZE TABLE 没有产生对你特定的表足够好的值，你可以使用 FORCE INDEX 在你查询中来强制使用特定索引，或者设置 max_seeks_for_key 来确保MySQL在表扫描之上运行索引查找。请参阅5.3.3节，“服务器系统变量”。请参阅A.6节，“优化器相关的问题”。
·在Windows上，InnoDB总是内部地用小写字母存储数据库和表名字。要把数据库以二进制形式从Unix 移到Windows，或者从Windows移到Unix，你应该让所有数据库和表的名字都是小写。
·警告: 不要在MySQL数据库内的把MySQL系统表从MyISAM转为InnoDB表！这是一个不被支持的操作。如果你这么做了，MySQL直到你从备份恢复旧系统表，或用mysql_install_db脚本重建系统表才重启动。
·InnoDB在表内不保留行的内部计数。（因为多版本化，这可能确实有些复杂）。要处理一个SELECT COUNT(*) FROM t语句，InnoDB必须扫描表的一个索引，如果这个索引不在缓冲池中，扫描需要花一些时间。要获得快速计数，你不得不使用一个自己创建的计数器表，并让你的应用按照它做的插入和删除来更新它。如果你的表格不经常改变，使用MySQL查询缓存时一个好的解决方案。如果大致的行数就足够了，则SHOW TABLE STATUS也可被使用。请参阅15.2.11节，“InnoDB性能调节提示”。
·对于AUTO_INCREMENT列，你必须总是为表定义一个索引，并且索引必须包含AUTO_INCREMENT列。在MyISAM表中，AUTO_INCREMENT列可能时多列索引的一部分。
·当你重启MySQL服务器之时，InnoDB可能为一个AUTO_INCREMENT列重使用一个旧值（即，一个被赋给一个老的已回滚的事务的值）。
·当一个AUTO_INCREMENT列用完值，InnoDB限制一个BIGINT到－9223372036854775808以及BIGINT UNSIGNED到1。尽管如此，BIGINT值有由64位，所以注意到，如果你要一秒输入100万个行，在BIGINT到达它上限之前，可能还需要将近30万年。用所有其它整数类型列，产生一个重复键错误。这类似于MyISAM如何工作的，因为它主要是一般MySQL行为，并不特别关于任何存储引擎。
·DELETE FROM tbl_name不重新生成表，但取而代之地删除所有行，一个接一个地删除。
·TRUNCATE tbl_name为InnoDB而被映射到DELETE FROM tbl_name 并且不重置AUTO_INCREMENT计数器。
·SHOW TABLE STATUS不能给出关于InnoDB表准确的统计数据，除了被表保留的物理尺寸。行计数仅是在SQL优化中粗略的估计。
·在MySQL 5.1中，如果innodb_table_locks=1(1是默认值） MySQL LOCK TABLES操作在每一个表上获取两个锁定。除了在MySQL层的表锁定，它也获得一个InnoDB表锁定。旧版的MySQL不获取InnoDB表锁定，旧行为可以通过设置innodb_table_locks=0 来选择。如果没有InnoDB表锁定被获得，即使表的一些记录被其它事务锁定，LOCK TABLES完成。
·所有被一个事务持有的InnoDB锁定在该事务被提交或中止之时被释放。因此在AUTOCOMMIT=1模式，在InnoDB表上调用是没有太多意义的，因为被需求的InnoDB表锁定可能会被立即释放。
·有时，在事务的过程中锁定更多的表可能是有用的。不幸地，MySQL中的LOCK TABLES执行一个暗地的COMMIT和UNLOCK TABLES。LOCK TABLES的一个InnoDB变量已经被计划， 该计划在事务的中间被执行。
·为建立复制从服务器的LOAD TABLE FROM MASTER语句对InnoDB表不起作用。一个工作区在主服务器上更换表为MyISAM的，然后做负载，之后更换主服务器表回到InnoDB中。
·在InnoDB中默认数据库页的大小是16KB。通过编译代码，你可以在8KB到64KB之间来设置这个值。你不得不更新在univ.i源文件中的UNIV_PAGE_SIZE和UNIV_PAGE_SIZE_SHIFT的值。
·在MySQL 5.1中，触发器不被级联的外键行为激活。
15.2.17. InnoDB故障诊断和排除
15.2.17.1.InnoDB数据词典操作的错误诊断和排除
·一个总的规则是，当一个操作失败或这你怀疑有一个缺陷。你应该查看MySQL服务器的错误日志，该日志典型地有一个有些象hostname.err这样的名字，或者在Windows上是mysql.err这样的。
·故障诊断与排除之时，通常最好从命令提示符运行MySQL服务器，而不是从mysqld_safe包运行，或不作为一个Windows服务来运行。你可以看mysqld打印到控制台上的内容，因此更好掌握发生了什么。在Windows上，你必须用--console选项启动服务器将输出定向到控制台窗口
·使用InnoDB Monitors获取关于某问题的信息。如果问题是性能相关的，或者你的服务器看起来被挂起，你应该使用innodb_monitor来打印InnoDB内部状态的信息，如果问题是关于锁定，则使用innodb_lock_monitor。如果问题是在表的创建或其它数据词典操作，使用innodb_table_monitor来打印InnoDB内部数据词典的内容。
·如果你猜测一个表被破坏，则在该表上运行CHECK TABLE。
15.2.17.1. InnoDB数据词典操作错误诊断和排除
表的一个特殊问题是MySQL服务器以.frm文件来保存数据词典信息，它被放在数据库目录，然而InnoDB也存储信息到表空间文件里它自己的数据词典里。如果你把.frm文件移来移去；或者，如果服务器在数据词典操作的中间崩溃，.frm文件可能结束与InnoDB内部数据词典的同步。
一个不同步的数据词典的症状是CREATE TABLE语句失败。如果发生这种情况，你应该查看服务器的错误日志。如果日志说表已经存在于InnoDB内部数据词典当中，你在InnoDB表空间文件内有一个孤表，它没有对应的.frm文件。错误信息看起来象如下的：
InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
你可以按照错误日志里给的指示移除一个孤表。如果还是不能成功地使用DROP TABLE，问题可能是因为在mysql客户端里的名字完成。要解决这个问题，用--disable-auto-rehash选项来启动mysql客户端并再次尝试DROP TABLE 。（有名字完成打开着，mysql试着构建个表名字的列表，当一个正如描述的问题存在之时，这个列表就不起作用）。
不同步数据词典的另一个“同义词”是MySQL打印一个不能打开.InnoDB文件的错误：
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
在错误日志你可以发现一个类似于此的信息：
InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?
这意味这有一个孤单的.frm文件，在InnoDB内没有相对应的表。你可以通过手动删除来移除这个孤单的.frm文件。
如果MySQL在一个 ALTER TABLE操作的中间崩溃，你可以用InnoDB表空间内临时孤表来结束。你可以用innodb_table_monitor看一个列出的表，名为#sql-...。如果你把表的名字包在`(backticks)里，你可以在名字包含“#”字符的表上执行SQL语句。因此，你可以用前述的的方法象移除其它孤表一样移除这样一个孤表。注意，要在Unix外壳里复制或重命名一个文件，如果文件名包含"#"字符，你需要把文件名放在双引号里。