第15 章：存储引擎和表类型

·         --myisam-recover=mode

设置为崩溃 MyISAM 表自动恢复的模式。

·         --delay-key-write=ALL

对任何 MyISAM 表的写操作之间不要刷新键缓冲区。

注释：如果你要这么做。当表在使用中之时，你应该不使用来自另一个程序的 MyISAM 表 (比如从另一个 MySQL 服务器或用myisamchk)。这么做会导致索引被破坏。

对使用--delay-key-write 的表，使用--external-locking 没有帮助。

·         bulk_insert_buffer_size

用在块插入优化中的树缓冲区的大小。注释：这是一个per thread的限制。

·         (OBSOLETE) myisam_max_extra_sort_file_size

这个参数已经不在 MySQL 中使用。

·         myisam_max_sort_file_size

如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出。

·         myisam_sort_buffer_size

设置恢复表之时使用的缓冲区的尺寸。

·         表被查错。

·         如果服务器发现一个错误，它试着做快速表修复 (排序且不重新创建数据文件)。

·         如果修复因为数据文件中的一个错误而失败 (例如，一个重复键错误)，服务器会再次尝试修复，这一次重建数据文件。

·         如果修复仍然失败，服务器用旧修复选项方法再重试一次修复 (一行接一行地写，不排序)。这个方法应该能修复任何类型的错误，并且需要很低的磁盘空间。

15.1.3.1. 静态 (固定长度) 表特征

15.1.3.2. 动态表特征

15.1.3.3. 已压缩表特征

静态格式是 MyISAM 表的默认存储格式。当表不包含变量长度列 (VARCHAR, BLOB，或 TEXT) 时，使用这个格式。每一行用固定字节数存储。

MyISAM 的三种存储格式中，静态格式就最简单也是最安全的 (至少对于崩溃而言)。静态格式也是最快的 on-disk 格式。快速来自于数据文件中的行在磁盘上被找到的容易方式：当按照索引中的行号查找一个行时，用行长度乘以行号。同样，当扫描一个表的 时候，很容易用每个磁盘读操作读一定数量的记录。

当 MySQL 服务器正往一个固定格式 MyISAM 文件写的时候，如果计算机崩溃了，安全是显然的。在这种情况下，myisamchk可以容易地决定每行从哪里开始到哪里结束，所以它通常可以收回所有记录，除了写了一部分的记录。注意，基于数据行，MyISAM 表索引可以一直被重新构建。

静态格式表的一般特征：

如果一个 MyISAM 表包含任何可变长度 列 (VARCHAR, BLOB 或 TEXTDynamic)，或者如果一个表被用 ROW_FORMAT=DYNAMIC 选项来创建，动态存储格式被使用。

这个格式更为复杂一点，因为每行有一个表明行有多长的头。当一个记录因为更新的结果被变得更长，该记录也可以在超过一个位置处结束。

你可以使用 OPTIMIZE TABLE 或myisamchk来对一个表整理碎片。如果在一个表中有你频繁访问或改变的固定长度 列，表中也有一些可变长度列，仅为避免碎片而把这些可变长度列移到其它表可能是一个好主意。

动态格式表的一般特征：

·                3

·                + (number of columns + 7) / 8

·                + (number of char columns)

·                + (packed size of numeric columns)

·                + (length of strings)

·                + (number of NULL columns + 7) / 8

已压缩存储格式是由myisampack工具创建的只读格式。

所有 MySQL 分发版里都默认包括myisampack。已压缩表可以用myisamchk来解压缩。

已压缩表有下列特征：

15.1.4.1. 损坏的MyISAM 表

15.1.4.2. 未被适当关闭的表的问题

即使 MyISAM 表格式非常可靠 (SQL 语句对表做的所有改变在语句返回之前被写下)，如果下列任何事件发生，你依然可以获得损坏的表：

一个损坏的表的典型症状如下：

·                Incorrect key file for table: '...'. Try to repair it

你可以用 CHECK TABLE statement语句来检查MyISAM表的健康，并用 REPAIR TABLE 修复一个 损坏的 MyISAM 表。当mysqld不运行之时，你也可以用myisamchk命令检查或修理一个表。请参阅13.5.2.3 节，“CHECK TABLE 语法”， 13.5.2.6 节，“REPAIR TABLE 语法”，和5.9.5 节，“myisamchk — MyISAM 表维护工具”。

如果你的表变得频繁损坏，你应该试着确定为什么会这样的原因。要明白的最重要的事是表变得损坏是不是因为服务器崩溃的结果。你可以在错误日志中查找最近的 restarted mysqld 消息来早期验证这个。如果存在这样一个消息，则表损坏是服务器死掉的一个结果是很有可能的。否则，损坏可能在正常操作中发生。这是一个缺陷。你应该试着创建一个展示这个问题的可重复生成的测试案例。请参阅A.4.2 节，“如果 MySQL 保持崩溃，该怎么做” 及E.1.6 节，“如果出现表崩溃，请生成测试案例”。

每个 MyISAM 索引文件 (.MYI) 在头有一个计数器，它可以被用来检查一个表是否被恰当地关闭。如果你从 CHECK TABLE 或myisamchk得到下列警告，意味着这个计数器已经不同步了：

clients are using or haven't closed the table properly

这个警告并不是完全意味着表已被破坏，但你至少应该检查表。

计数器的工作方式如下：

换句话来说，计数器只有在下列情况会不同步：

·         只应该创建一个 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 文件的位置”。

·         /etc/my.cnf

全局选项。

·         $MYSQL_HOME/my.cnf

服务器专用选项。

·         defaults-extra-file

--defaults-extra-file 选项指定的文件。

·         ~/.my.cnf

用户专用选项。

·         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)&rdquo；支持超过 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 (1,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.1. 处理InnoDB 初始化问题

如果 InnoDB 在一个文件操作中打印一个操作系统错误，通常问题是如下中的一个：

当 InnoDB 试着初始化它的表空间或日志文件之时，如果出错了，你应该删除 InnoDB 创建的所有文件。这意味着是所有 ibdata 文件和所有 ib_logfiles 文件。万一你创建了一些 InnoDB 表，为这些表也从 MySQL 数据库目录删除相应的。frm 文件 (如果你使用多重表空间的话，也删除任何。ibd 文件)。然后你可以试着再次创建 InnoDB 数据库。最好是从命令提示符启动 MySQL 服务器 ，以便你可以查看发生了什么。

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 服务器的客户端开始之时是允许自动提交模式的，这个模式自动提交你运行的每个 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 (10, '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 (15, '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 也提供单独的专门的事务提交和回滚函数或者方法。

要点：你不应该在 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 节，“强制恢复”。

如果你为一个表指定 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 表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因，这个选项的影响在服务器重启后就无效了。

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}]

外键定义服从下列情况：

InnoDB 拒绝任何试着在子表创建一个外键值而不匹配在父表中的候选键值的 INSERT 或 UPDATE 操作。一个父表有一些匹配的行 的子表，InnoDB 对任何试图更新或删除该父表中候选键值的 UPDATE 或 DELETE 操作有所动作，这个动作取决于用 FOREIGN KEY 子句的 ON UPDATE 和 ON DETETE 子句指定的referential action。当用户试图从一个父表删除或更新一行之时，且在子表中有一个或多个匹配的行，InnoDB 根据要采取的动作有五个选择：

当父表中的候选键被更新的时候，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。

MySQL 复制就像对 MyISAM 表一样，也对 InnoDB 表起作用。以某种方式使用复制也是可能的，在这种方式中从服务器上表的类型不同于主服务器上 原始表的类型。例如，你可以复制修改到主服务器上一个 InnoDB 表，到从服务器上一个 MyISAM 表里。

要为一个主服务器建立一个新服务器，你不得不复制 InnoDB 表空间和日志文件，以及 InnoDB 表的。frm 文件，并且移动复件到从服务器。 关于其恰当步骤请参阅15.2.9 节，“移动 InnoDB 数据库到另一台机器”。

如果你可以关闭主服务器或者一个存在的从服务器。你可以采取 InnoDB 表空间和日志文件的冷备份，并用它来建立一个从服务器。要不关闭任何服务器来建立一个新的从服务器，你也可以使用非免费 (商业的)InnoDB 热备份工具。

InnoDB 复制里一个小的限制是 LOAD TABLE FROM MASTER 不对 InnoDB 类型表起作用。有两种可能的工作区：

在主服务器失败的事务根本不影响复制。MySQL 复制基于二进制日志，MySQ 在那里写修改数据的 SQL 语句。从服务器读主服务器的二进制日志，并执行同样的 SQL 语句。尽管如此，在事务里发生的语句不被写进二进制日志直到事务提交，在那一刻，事务里所有语句被一次性写进日志。如果语句失败了，比如，因为外键违例，或者，如果事务被回滚，没有 SQL 语句被写进二进制日志，并且事务根本不在从服务器上执行。

你可以存储每个 InnoDB 表和它的索引在它自己的文件在中，这个特征被称为&ldquo；多表空间” ，因为实际上每个表有它自己的表空间。

对那些想把特定表格移到分离物理磁盘的用户，或者那些希望快速恢复单个表的备份而无须打断其余 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 安装中：

2.      ALTER TABLE tbl_name DISCARD TABLESPACE;

5.      ALTER TABLE tbl_name IMPORT TABLESPACE;

在上下文中，一个。ibd 文件干净的备份意为：

你可以用下列方法生成一个。ibd 文件的干净备份：

生成一个。ibd 文件的干净复制的另一个方法是使用商业的InnoDB 热备份工具：

1.    使用mysqldump转储所有 InnoDB 表。

2.    停止服务器。

3.    删除所有已存在的表空间文件。

4.    配置新表空间。

5.    重启服务器。

6.    导入转储文件。

15.2.8.1. 强制恢复

15.2.8.2. 检查点

1.    关闭 MySQL 服务器，确信它是无错误关闭。

2.  复制你所有数据文件 (ibdata 文件和。ibd 文件) 到一个安全的地方。 

3.   复制你所有 ib_logfile 文件到一个安全的地方。

4.    复制 my.cnf 配置文件或文件到一个安全的地方。

5.    为你 InnoDB 表复制。frm 文件到一个安全的地方。

如果数据库页被破坏，你可能想要用 SELECT INTO OUTFILE 从从数据库转储你的表，通常以这种方法获取的大多数数据是完好的。即使这样，损坏可能导致 SELECT * FROM tbl_name或者 InnoDB 后台操作崩溃或断言，或者甚至使得 InnoDB 前滚恢复崩溃。 尽管如此，你可以用它来强制 InnoDB 存储引擎启动同时阻止后台操作运行，以便你能转储你的表。例如：你可以在重启服务器之前，在选项文件的 [mysqld] 节添加如下的行：

[mysqld]

innodb_force_recovery = 4

innodb_force_recovery 被允许的非零值如下。一个更大的数字包含所有更小数字的预防措施。如果你能够用一个多数是 4 的选项值来转储你的表，那么你是比较安全的，只有一些在损坏的单独页面上的数据会丢失。一个为 6 的值更夸张，因为数据库页被留在一个陈旧的状态，这个状态反过来可以引发对 B 树和其它数据库结构的更多破坏。

数据库不能另外地带着这些选项中被允许的选项来使用。作为一个安全措施，当 innodb_force_recovery 被设置 为大于 0 的值时，InnoDB 阻止用户执行 INSERT, UPDATE 或 DELETE 操作。

即使强制恢复被使用，你也可以 DROP 或 CREATE 表。如果你知道一个给定的表正在导致回滚崩溃，你可以移除它。你也可以用这个来停止由失败的大宗导入或失败的 ALTER TABLE 导致的失控 回滚。你可以杀掉mysqld进程，然后设置 innodb_force_recovery 为 3，使得数据库被 挂起而不需要回滚，然后舍弃导致失控回滚的表。

InnoDB 实现一种被认识为&ldquo；模糊”检查点设置的检查点机制。InnoDB 以小批量从缓冲池 刷新已修改的数据库页。没必要以单个批次刷新缓冲池，单批次刷新实际操作中可能会在检查点设置进程中停止用户 SQL 语句的处理。

在崩溃恢复中，InnoDB 找寻被写进日志的检查点标签。它知道所有在该标签之前对数据库的修改被呈现在数据库的磁盘映像中。然后 InnoDB 从检查点往前扫描日志文件，对数据库应用已写入日志的修改。

InnoDB 以循环方式写日志文件。所有使得缓冲池里的数据库页与磁盘上的映像不同的已提交修改必须出现在日志文件中 ，以备万一 InnoDB 需要做一个恢复。这意味着，当 InnoDB 开始重新使用一个日志文件，它需要确认在磁盘上的数据库页映像包含已写进 InnoDB 准备重新使用的日志文件里的修改。换句话 说，InnoDB 必须创建一个检查点，这经常涉及已修改 数据库页到磁盘的刷新。

前面的叙述解释了为什么使你的日志文件非常大会在设置检查点中节约磁盘 I/O。设置日志文件总的大小和缓冲池一样大或者甚至比缓冲池大通常是有意义的。大日志文件的缺点是崩溃恢复要花更长的时间，因为有更多写入日志的信息要应用到数据库上。

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 SHARE MODE

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 实现标准行级锁定，在这里有两种类型的锁： locks:

如果事务A 在 tuple t上持有独占锁定，来自不同事务B的对t上任一类型的锁的请求不被马上许可，取而代之地，事务B 不得不等待事务 t 释放在 tuple t上的锁。

如果事务 A 在 tuple t上持有一个共享的锁 (S)，那么

不仅如此，InnoDB 支持多间隔尺寸锁定，它允许记录锁和对整个表的锁共存。要使得多间隔尺寸级别的锁定实际化，额外类型的锁，被称为intention locks被使用。在 InnoDB 中，意图锁定是表锁定。 对于一个事务，意图锁定之后理想的是指明在该表中对一个行随后需要哪一类型的锁定 (共享还是独占)。有两种意图锁被用在 InnoDB 中 (假设事务T 在表R中要求一个已指出的类型的锁)：

意图锁协议如下：

这些结果可以方便地用一个锁类型兼容矩阵来总结：

如果一个锁定与现在锁定兼容的话，它被授给一个委托事务。如果一个锁定与现存锁定冲突，它就不被授予一个委托事务。事务等待着直到冲突的现存锁定被释放掉。如果一个锁定请求与现存锁定相冲突，且不能被授予，因为它可能会导致死锁，一个错误产生。

因此，意图锁定不阻碍任何东西，除了完全表请求 (比如 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 从表中删除行。

在 InnoDB 中，所有用户行为都在事务内发生。如果自动提交模式被允许，每个 SQL 语句在它自己上形成一个单独的事务。MySQL 总是带着允许自动提交来开始一个新连接。

如果自动提交模式被用 SET AUTOCOMMIT = 0 关闭，那么我们可以认为一个用户总是有一个事务打开着。一个 SQL COMMIT 或 ROLLBACK 语句结束当前事务并且一个新事务开始。两个语句都释放所有在当前事务中被设置的 InnoDB 锁定。一个 COMMIT 语句意味着在当前事务中做的改变被生成为永久的，并且变成其它用户可见的。一个 ROLLBACK 语句，在另一方面，撤销所有当前事务做的修改。

如果连接有被允许的自动提交，通过用明确的 START TRANSACTION 或 BEGIN 语句来开始一个事务，并用 COMMIT 或者 ROLLBACK 语句来结束它，这样用户仍旧可以执行一个多重语句事务。

按照 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 中每个隔离级别的详细描述如下：

持续读意味着 InnoDB 使用它的多版本化来给一个查询展示某个时间点处数据库的快照。查询看到在那个时间点之前被提交的那些确切事务做的更改，并且没有其后的事务或未提交事务做的改变。这个规则的例外是，查询看到发布该查询的事务本身所做的改变。

如果你运行在默认的 REPEATABLE READ 隔离级别，则在同一事务内的所有持续读读取由该事务中第一个这样的读所确立的快照。你可以通过提交当前事务并在发布新查询的事务之后，为你的查询获得一个更新鲜的快照。

持续读是默认模式，在其中 InnoDBzai 在 READ COMMITTED 和 REPEATABLE READ 隔离级别处理 SELECT 语句。持续读不在任何它访问的表上设置锁定，因此，其它用户可自由地在持续读在一个表上执行的同一时间修改这些表。

注意，持续读不在 DROP TABLE 和 ALTER TABLE 上作用。持续读不在 DROP TABLE 上作用，因为 MySQL 不能使用已经被移除的表，并且 InnoDB 破坏了该表。持续读不在 ALTER TABLE 上作用，因为它在某事务内执行，该事务创建一个新表，并且从旧表往新表中插入行。现在，当你重新发出持续读之时，它不能在新表中看见任何行，因为它们被插入到一个在持续读读取的快照中不可见的事务 里。

在一些环境中，一个持续读是不方便的。比如，你可能想要往表的子表里添加一个新行，并确信该子表在父表中有一个根。下列例子显示如何在你应用程序代码中实现参考的完整性。

假设你使用一个持续读去读取父表并且看到表中子表的根。不能安全地往子表添加子行吗？不，因为可能同时发生一些其它用户从父表删除父行，而你没有注意到它的情况。

解决办法是在使用 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 语句仅仅取回识别符信息 (专门对当前连接)。它不访问任何表。

在行级锁定中，InnoDB 使用一个名为next-key locking的算法。InnoDB 以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。

InnoDB 对索引记录设置的锁定也映像索引记录之前的&ldquo；间隙”。如果一个用户对一个索引上的记录 R 有共享或独占的锁定，另一个用户 不能紧接在 R 之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的&ldquo；幽灵问题”。假设你想要从有一个标识符值大于 100 的子表读并锁定所有子记录，并想着随后在选定行中更新一些 列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

假设在 id 列有一个索引。查询从 id 大于 100 的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外，一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的 SELECT，你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的：一个事务应该能够运行，以便它已经读的数据在事务过程中不改变。如果我们把一套行视为数据项，新的&ldquo；幽灵&rdquo；子记录可能会违反这一隔离原则。

当 InnoDB 扫描一个索引之时，它也锁定所以记录中最后一个记录之后的间隙。刚在前一个例子中发生：InnoDB 设置的锁定防止任何插入到 id 可能大过 100 的表。

你可以用 next-key 锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据， 并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且知道在读过程中对你的行的继承者设置的 next-key 锁定与此同时阻止任何人对你的 行插入一个重复。因此，the next-key 锁定允许你锁住在你的表中并不存在的一些东西。

假设你正运行在默认的 REPEATABLE READ 隔离级别。当你发出一个持续读之时，即一个普通的 SELECT 语句，InnoDB 按照你的查询看到的数据库，给你的事务一个时间点。如果另一个事务在你的时间点被指定之后删除一行并提交，你不会看见已被删除的行。插入和更新被相似地处理。

你可以通过提交你的事务来提前你的时间点，然后做另一个 SELECT。

这被称为多版本并发控制 (multi-versioned concurrency control)。

               User A                 User B

           SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;

time

|          SELECT * FROM t;

|          empty set

|                                 INSERT INTO t VALUES (1, 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;

在 SQL 查询处理中，一个锁定读，一个 UPDATE 或一个 DELETE 一般地对被扫描的每个索引记录设置记录锁定。如果在某查询中有一个 WHERE 条件是没什么关系的，而这个查询可能从查询的结果包中排除行。InnoDB 不记得确切的 WHERE 条件，但是仅知道哪个索引范围被扫描。记录锁定是正常的 next-key 锁定，它也阻止对紧接着记录之前的间隙的插入。

如果锁定被设置为独占，则 InnoDB 总是取回集束的索引目录并对其设置锁定。

如果你没有适合查询的索引，MySQL 不得不扫描整个表来处理查询，表的每行变成被锁定的，这样反过来阻止其它用户的所有对表的插入。创建一个好的索引让你的查询不必要扫描很多行是很重要的。

MySQL 以默认允许 autocommit 模式来开始每一个客户端连接。当 autocommit 被允许之时，如果 SQL 语句不返回错误的话，MySQL 在每个 SQL 语句之后，做一个提交。

如果你关闭 autocommit 模式并且关闭一个连接而不调用你的事务的明确提交，则 MySQL 回滚你的事务。

如果 SQL 语句返回一个错误，提交/回滚行为取决于这个错误。请参阅15.2.15 节，“InnoDB 错误处理”。

下列每一个语句 (以及它们的任何同义词) 隐式结束一个事务，就好像在执行这个语句之前你做了一个 COMMIT：

事务不能被嵌套。当你发出 START TRANSACTION 语句或与之同义的语句之时，这是对任何当前事务 隐式提交的一个结果。

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 语句设定的。

死锁是事务型数据库典型的问题，但是除非它们频繁出现以至于你更本不能运行某个事务，它们一般是不危险的。正常地，你必须编写你的应用程序使得它们总是准备如果因为死锁而 回滚一个事务就重新发出一个事务。

InnoDB 使用自动行级锁定。即使在只插入或删除单个行的事务的情况下，你可以遇到死锁。这是因为这些操作不是真正的&ldquo；极小的”，它们自动对插入或删除的行的 (可能是数个) 索引记录设置锁定。

你可以用下列技术对付死锁减少它们发生的可能性：

·                SET AUTOCOMMIT=0;

·                LOCK TABLES t1 WRITE, t2 READ, ...;

·                [do something with tables t1 and t2 here];

·                COMMIT;

·                UNLOCK TABLES;

15.2.11.1. SHOW INNODB STATUS 和 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 (1,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

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 选项来启动服务器。

监视器输出包括下列类型的信息：

要让标准 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 (5, '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 (1519229,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 (1603393,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 (5, '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)

关于这个输出一些要注意的：

InnoDB 发送诊断输出到 stderr 或文件，而不是到 stdout 或者固定尺寸内存缓冲，以避免 底层缓冲溢出。作为一个副效果，SHOW INNODB STATUS 的输出每 15 秒钟写到一个状态文件。这个文件的名字是 innodb_status.pid，其中pid 是服务器进程 ID。这个文件在 MySQL 数据目录里创建。正常关机之时，InnoDB 删除这个文件。如果发生不正常的关机， 这些状态文件的实例可能被展示，而且必须被手动删除。在移除它们之前，你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项 innodb_status_file=1 被设置之时，innodb_status.pid文件被创建。

15.2.13.1. 索引的物理结构

15.2.13.2. 缓冲插入

15.2.13.3. 适应的哈希索引

15.2.13.4. 物理记录结构

所有 InnoDB 的索引是 B 数，其中索引记录被存储在树的树叶页。一个索引页的默认大小是 16KB。当新记录被插入，InnoDB 试着为将来索引记录的插入和更新留下十六分之一的空白页。

如果索引记录以连续的顺序被插入 (升序或者降序)，结果索引页大约是 15/16 满。如果记录被以随机的顺序被插入，页面是从 1/2 到 15/16 满。如果索引页的填充因子降到低于 1/2，InnoDB 试着搜索索引树来释放页。

在数据库应用中，主键是一个唯一的识别符，并且新行被以主键的升序来插入，这是个常见的情况。因此，到集束索引的插入不需要从一个磁盘随机读。

另一方面，第二索引通常是非唯一的，到第二索引的插入以相对随机次序发生。这可能会导致大量的随机磁盘 I/O 操作，而没有一个被用在 InnoDB 中的专用机制。

如果一个索引记录应该被插入到一个非唯一第二索引，InnoDB 检查第二索引页是否在缓冲池中。如果是，InnoDB 直接插入到索引页。如果索引页没有在缓冲池中被发现，InnoDB 插入记录到一个专门的插入缓冲结构。插入缓冲被保持得如此小以至于它完全适合在缓冲池，并且可以非常快地做插入。

插入缓冲周期地被合并到数据库中第二索引树里。把数个插入合并到索引树的同一页，节省磁盘 I/O 操作，经常地这是有可能的。据测量，插入缓冲可以提高到表的插入速度达 15 倍。

在插入事务被提交之后，插入缓冲合并可能连续发生。实际上，服务器关闭和重启之后，这会连续发生。(请参阅15.2.8.1 节，“强制恢复”)。

当许多第二索引必须被更新之时，并且许多行已被插入之时，插入缓冲合并可能需要数个小时。在这个时间内，磁盘 I/O 将会增加，这样会导致磁盘绑定查询明显缓慢。另一个明显的后台 I/O 操作是净化线程 (请参阅15.2.12 节，“实现多版本化”)。

如果一个表几乎完全配合主内存，在其上执行查询最快的方法就是使用哈希索引。InnoDB 有一个自动机制，它监视对为一个表定义的索引的索引搜索。如果 InnoDB 注意到查询会从建立一个哈希索引中获益，它会自动地这么做。

注意，哈希索引总是基于表上已存在的 B 树索引来建立。根据 InnoDB 对 B 树索引观察的搜索方式，InnoDB 会在为该 B 树定义的任何长度的 键的一个前缀上建立哈希索引。 哈希索引可以是部分的：它不要求整个 B 树索引被缓存在缓冲池。InnoDB 根据需要对被经常访问的索引的那些页面建立哈希索引。

在某种意义上，InnoDB 通过针对丰富主内存的适应的哈希索引机制来剪裁自己，更加靠近主内存数据库的架构。

InnoDB 表中的记录有如下特征：

15.2.14.1. 磁盘 I/O

15.2.14.2. 为表空间使用原始设备

15.2.14.3.文件空间管理

15.2.14.4. 整理表碎片

InnoDB 使用模拟异步磁盘 I/O：InnoDB 创建许多线程来处理 I/O 操作，比如 read-ahead.

在 InnoDB 中有两个read-ahead 试探：

InnoDB 使用一个被称为doublewrite的新颖的文件 刷新技术。它给操作体统崩溃或掉电后的恢复添加了安全，并且通过减少对 fsync() 操作的需要，它在多数 Unix 变种上改善了性能。.

Doublewrite 意为在向一个数据文件写页之前，InnoDB 首先把它们写到一个毗邻的表空间区域，称为 doublewrite 缓冲。仅在写然后 刷新到 doublewrite 已经完成之前，InnoDB 写页面到它们在表空间中恰当的位置。如果操作系统在写页面的中间崩溃，在恢复过程中，InnoDB 可以在随后从 doublewrite 缓冲中找到页面的一个良好复制。

你也可以使用原始磁盘分区作为表空间数据文件。通过使用原始磁盘，你可以在 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 服务器的帐号读和写访问的许可。

你在配置文件中定义的数据文件形成 InnoDB 的表空间。文件被简单地连起来以形成表空间。没有条纹在使用。当前你不能定义你的表被分配到表空间中的位置。但是，在一个新创建的表中间中，InnoDB 从第一个数据文件开始分配空间。

表空间包含数据库页，默认大小是 16KB。这些页被分组成 64 个连续页的范围。表空间内的文件在 InnoDB 中被称为片断。术语“rollback segment&rdquo；有一些混淆，因为它确切地包含许多表空间片断。

在 InnoDB 中，每个索引分配两个片断。一个是给 B 树的非树叶节点的，另一个是给树叶节点的。在这里，理想的是为包含数据的树叶节点达到更好的有序性。

当一个片断在表空间内长大，InnoDB 单独地分配最先的 32 页给它。此后 InnoDB 开始分配整个范围给该片断。InnoDB 可以一次给一个大片断添加多达 4 个范围以确保数据良好的连续性。

在表空间中的一些页包含其它页的位图，因此在一个 InnoDB 表空间中的一些范围不能被整个地分配给片断，只能作为单个页被分配。

当你发出 SHOW TABLE STATUS 询问表空间里可用的自由空间之时，InnoDB 报告在表空间中完全自由的范围。InnoDB 总是为扫除和其它内部目的保留一些范围，这些保留的范围不包括在自由空间里。

当你从一个表中删除数据之时，InnoDB 联系相应的 B 树索引。是否释放单独页或是范围到表空间取决删除的方式，因此被释放的空间变成对其它用户可用，但是记住，已删除的行仅在该行不再被事务 回滚或持续读所需要之后的一个 (自动) 净化操作中被物理删除。

如果有随机插入到表的索引或从表的索引随机删除，索引可能变成碎片的。碎片意思是索引页在磁盘上的物理排序并不接近页上记录的索引排序，或者在分配给索引的 64 页块上有许多没有被使用的页。

碎片的一个&ldquo；同义词&rdquo；是一个表占据的空间超过它应该占据的空间的大 小。确切是多少，这是很难去确定的。所有 InnoDB 数据和索引被存在 B 树中，并且它们的填充因子可能从 50% 到 100%。碎片的另一个&ldquo；同 义词&rdquo；是一个表扫描例如：

SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

花了超过它应该花的时间。(在上面的查询中我们&ldquo；欺骗”SQL 优化器来扫描集束索引，而不是一个第二索引 )。多数磁盘可以读 10MB/s 到 50MB/s，这可以被用来评估一个表扫描可以多快地运行。

如果你周期地执行“null” ALTER TABLE 操作，它就可以加速索引扫描： 

ALTER TABLE tbl_name ENGINE=INNODB

这导致 MySQL 重建表。另一个执行碎片整理操作的办法是使用mysqldump来转储一个表到一个文本文件，移除表，并重新从转储文件重装载它。

如果到一个索引的插入总是升序的，并且记录仅从末尾被删除，InnoDB 文件空间管理保证在索引中的碎片不会发生。

15.2.15.1. InnoDB 错误代码

15.2.15.2. 操作系统错误代码

·         如果你耗尽表空间中的文件空间，你使得 MySQL 表完全错误，并且 InnoDB 返回 SQL 语句。

·         一个事务死锁导致 InnoDB 回滚整个事务，在锁定等待超时的情况，InnoDB 仅回滚最近的 SQL 语句。

当一个事务回滚因为死锁或锁定等待超时而发生，它在事务中撤销语句的作用。但是如果事务是用 START TRANSACTION 或 BEGIN 语句开始的，它就不撤销该语句。进一步，SQL 语句变成事务的一部分直到 COMMIT, ROLLBACK 或者导致暗地提交的 SQL 语句发生。

·         如果你没有在语句中指定 IGNORE 选项，重复键错误回滚 SQL 语句。

·         行太长错误回滚 SQL 语句。

·         其它错误主要被代码的 MySQL 层 (在 InnoDB 存储引擎级别以上) 探测，它们回滚相应的 SQL 语句。在单个 SQL 语句中的回滚中锁定不被释放。

下面的这个不完全列表是你可能遇见的常见的 InnoDB 专有错误，带着为什么发生的原因以及如何该解决问题的相关信息

要打印一个操作系统错误号的意思，请使用 MySQL分发版里的perror程序。

下面表提供一些常用 Linux 系统错误代码。更完整的列表请参阅Linux source code。

下列表提供一列常用 Windows 系统错误代码。完整列表请访问Microsoft website。

·         一个表不能包含超过 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.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。

表的一个特殊问题是 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试着构建个表名字的列表，当一个正如描述的问题存在之时，这个列表就不起作用)。

不同步数据词典的另一个&ldquo；同义词&rdquo；是 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) 里，你可以在名字包含“#&rdquo；字符的表上执行 SQL 语句。因此，你可以用前述的的方法象移除其它孤表一样移除这样一个孤表。注意，要在 Unix 外壳里复制或重命名一个文件，如果文件名包含"#"字符，你需要把文件名放在双引号里。

·         如果你使用 ALTER TABLE 来把 MERGE 表变为其它表类型，到 底层表的映射就被丢失了。取而代之的，来自底层 MyISAM 表的行被复制到已更换的表中，该表随后被指定新类型。

·         REPLACE不起作用。

·         没有 WHERE 子句，或者在任何被映射到一个打开的 MERGE 表上的任何一个表上的 REPAIR TABLE，TRUNCATE TABLE, OPTIMIZE TABLE 或 ANALYZE TABLE，你不能使用 DROP TABLE, ALTER TABLE, DELETE FROM。如果你这么做了，MERGE 表将仍旧指向原始表，这样产生意外结果。解决这个不足最简单的办法是在执行任何一个这些操作之前发出一个 FLUSH TABLES 语句来确保没有 MERGE 表仍旧保持打开。

·         一个 MERGE 表不能在整个表上维持 UNIQUE 约束。当你执行一个 INSERT，数据进入第一个或者最后一个 MyISAM 表 (取决于 INSERT_METHOD 选项的值)。MySQL 确保唯一 键值在那个 MyISAM 表里保持唯一，但不是跨集合里所有的表。

·         当你创建一个 MERGE 表之时，没有检查去确保底层表的存在以及有相同的机构。当 MERGE 表被使用之时，MySQL 检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的 MyISAM 表创建一个 MERGE 表，你非常有可能撞见奇怪的问题。

·         在 MERGE 表中的索引的顺序和它的 底层表中的索引应该一样。如果你使用 ALTER TABLE 给一个被用在 MERGE 表中的表添加一个 UNIQUE 索引，然后使用 ALTER TABLE 在 MERGE 表上添加一个非唯一索引，如果在 底层表上已经有一个非唯一索引，对表的索引排序是不同的。(这是因为 ALTER TABLE 把 UNIQUE 索引放在非唯一索引之前以利于重复键的快速检测 )。因此对使用这样索引的表的查询可能返回不期望的结果。

·         在 Windows 中，在一个被MERGE 表使用的表上DROP TABLE 不起作用，因为MERGE 引擎的表映射对 MySQL 的更上层隐藏。因为 Windows 不允许已打开文件的删除，你首先必须 刷新所有 MERGE 表 (使用 FLUSH TABLES) 或在移除该表之前移除 MERGE 表。

-        如果一个内部表变得太大，服务器自动把它转换为一个磁盘表。尺寸限制由 tmp_table_size 系统变量的值来确定。

-        MEMORY 表决不会转换成磁盘表。要确保你不会偶尔做点傻事，你可以设置max_heap_table_size 系统变量给 MEMORY 表加以最大尺寸。对于单个的表，你也可以在 CREATE TABLE 语句中指定一个 MAX_ROWS 表选项。

·         Linux 2.x Intel

·         Sun Solaris (SPARC and x86)

·         FreeBSD 4.x/5.x (x86, sparc64)

·         IBM AIX 4.3.x

·         SCO OpenServer

·         SCO UnixWare 7.1.x

·         Windows NT/2000/XP

·         Linux 2.x Alpha

·         Linux 2.x AMD64

·         Linux 2.x IA-64

·         Linux 2.x s390

·         Mac OS X

·         --bdb-home=path

BDB 表的基础目录。这应该和你为--datadir 使用的目录相同。

·         --bdb-lock-detect=method

BDB 锁定检测方式。选项值应该为 DEFAULT, OLDEST, RANDOM 或 YOUNGEST。

·         --bdb-logdir=path

BDB 日志文件目录。

·         --bdb-no-recover

不在恢复模式启动 Berkeley DB。

·         --bdb-no-sync

不同步刷新 BDB 日志。这个选项不被赞成，取而代之地使用--skip-sync-bdb-logs(请参阅对--sync-bdb-logs 的描述)。

·         --bdb-shared-data

以多处理模式启动 Berkeley DB。(初始化 Berkeley DB 之时，不要使用 DB_PRIVATE)。

·         --bdb-tmpdir=path

BDB 临时文件目录。

·         --skip-bdb

禁止 BDB 存储引擎。

·         --sync-bdb-logs

同步刷新 BDB 日志。这个选项默认被允许，请使用--skip-sync-bdb-logs 来禁止它。

·         如果你正运行着，同时随着 autocommit 的被允许 (这是默认的)，对 BDB 表的改变被立即提交并且不能被 回滚。

·         如果你正运行着，同时随着 autocommit 的被禁止，改变不变成永久的直到你执行一个 COMMIT 语句。作为提交的替代，你可以执行 ROLLBACK 来忘记改变。

你可以用 BEGIN WORK 语句开始一个事务来挂起 autocommit，或者用 SET AUTOCOMMIT=0 来明确禁止 autocommit。

·         BDB 表可以有多达每表 31 个索引，每个索引 16 列，并且 1024 字节的最大 键尺寸。

·         MySQL 在每个 BDB 表中需要一个 PRIMARY KEY 以便每一行可以被唯一地识别。如果你不明确创建一个，MySQL 为你创建并维持一个隐藏的 PRIMARY KEY。隐藏的 键有一个 5 字节的长度，并且为每个插入的企图而被增加。这个键不出现在 SHOW CREATE TABLE 或 DESCRIBE 的输出之中。

·         PRIMARY KEY 比任何其它索引都要快，因为 PRIMARY KEY 被与行的数据一起存储。其它索引被存储为键数据＋PRIMARY KEY，所以保持 PRIMARY KEY 尽可能地短以节约磁盘空间并获得更好速度是重要的。

这个行为类似于 InnoDB 的，在其中较短的 primary keys 不仅在主索引也在第二索引节约空间 。

·         如果在 BDB 表中，你访问的所有列是同一索引的一部分或主键的一部分，MySQL 可以执行查询而不访问确实的行。在一个 MyISAM 表中，只有 列是同一索引的一部分之时，才可以这么做。

·         连续扫描比对 MyISAM 表的扫描更慢，因为在 BDB 表中的数据被存储在 B 树而不是在分离的数据文件中。