### 第15章：存储引擎和表类型 **目录** [15.1. MyISAM存储引擎](#) [15.1.1. MyISAM启动选项](#) [15.1.2.键所需的空间](#) [15.1.3. MyISAM表的存储格式](#) [15.1.4. MyISAM表的问题](#) [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数据库的备份和恢复](#) [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.3. MERGE存储引擎](#) [15.3.1. MERGE表方面的问题](#) [15.4. MEMORY(HEAP)存储引擎](#) [15.5. BDB(BerkeleyDB)存储引擎](#) [15.5.1. BDB支持的操作系统](#) [15.5.2. 安装BDB](#) [15.5.3. BDB启动选项](#) [15.5.4. BDB表的特性](#) [15.5.5. 修改BDB所需的事宜](#) [15.5.6. 对BDB表的限制](#) [15.5.7. 使用BDB表时可能出现的错误](#) [15.6. EXAMPLE存储引擎](#) [15.7. FEDERATED存储引擎](#) [15.7.1. 安装FEDERATED存储引擎](#) [15.7.2. FEDERATED存储引擎的介绍](#) [15.7.3. 如何使用FEDERATED表](#) [15.7.4. FEDERATED存储引擎的局限](#) [15.8. ARCHIVE存储引擎](#) [15.9. CSV存储引擎](#) [15.10.BLACKHOLE存储引擎](#) MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎： ·        MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。 ·        MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。 **注释：**MEMORY存储引擎正式地被确定为HEAP引擎。 ·        InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。 ·        EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。 ·        NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。它在MySQL-Max 5.1二进制分发版里提供。这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。在未来的MySQL分发版中，我们想要添加其它平台对这个引擎的支持，包括Windows。 ·        ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。 ·        CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。 ·         BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。 ·        FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。 寻求选择一个存储引擎的帮助，请参阅[14.4节，“选择一个存储引擎”](# "14.4. Choosing a Storage Engine")。 这一章讲述除NDB Cluster外的每一个MySQL存储引擎，NDB Cluster在[第17章：*MySQL Cluster*](# "Chapter 17. MySQL Cluster")中介绍。 当年创建一个新表的时候，你可以通过添加一个ENGINE 或TYPE 选项到CREATE TABLE语句来告诉MySQL你要创建什么类型的表： ~~~ CREATE TABLE t (i INT) ENGINE = INNODB; ~~~ ~~~ CREATE TABLE t (i INT) TYPE = MEMORY; ~~~ 虽然TYPE仍然在MySQL 5.1中被支持，现在ENGINE是首选的术语。 如果你省略掉ENGINE或TYPE选项，默认的存储引擎被使用。一般的默认是MyISAM，但你可以用--default-storage-engine或--default-table-type服务器启动选项来改变它，或者通过设置storage_engine或table_type系统变量来改变。 当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引擎替代MyISAM存储引擎作为替代，请参阅[2.3.5.1节，“介绍”](# "2.3.5.1. Introduction")。 要把一个表从一个类型转到另一个类型，可使用ALTER TABLE语句，这个语句指明新的类型： ~~~ ALTER TABLE t ENGINE = MYISAM; ~~~ ~~~ ALTER TABLE t TYPE = BDB; ~~~ 请参阅[13.1.5节，“CREATE TABLE语法”](# "13.1.5. CREATE TABLE Syntax")和[13.1.2节，“ALTER TABLE语法”](# "13.1.2. ALTER TABLE Syntax")。 如果你试着使用一个未被编译进MySQL的存储引擎，或者试着用一个被编译进MySQL但没有被激活的存储引擎，MySQL取而代之地创建一个MyISAM类型的表。当你在支持不同存储引擎的MySQL服务器之间拷贝表的时候，上述的行为是很方便的。（例如，在一个复制建立中，可能你的主服务器为增加安全而支持事务存储引擎，但从服务器为更快的速度而仅使用非事务存储引擎。) 在不可用的类型被指定时，自动用MyISAM表来替代，这会对MySQL的新用户造成混淆。无论何时一个表被自动改变之时，产生一个警告。 MySQL总是创建一个.frm文件来保持表和列的定义。表的索引和数据可能被存储在一个或多个文件里，这取决于表的类型。服务器在存储引擎级别之上创建.frm文件。单独的存储引擎创建任何需要用来管理表的额外文件。 一个数据库可以包含不同类型的表。 事务安全表(TST) 比起非事务安全表 (NTST)有几大优势： ·        更安全。即使MySQL崩溃或遇到硬件问题，要么自动恢复，要么从备份加事务日志恢复，你可以取回数据。 ·        你可以合并许多语句，并用COMMIT语句同时接受它们全部（如果autocommit被禁止掉）。 ·         你可以执行ROLLBACK来忽略你的改变（如果autocommit被禁止掉）。 ·        如果更新失败，你的所有改变都变回原来。（用非事务安全表，所有发生的改变都是永久的）。 ·        事务安全存储引擎可以给那些当前用读得到许多更新的表提供更好的部署。 虽然MySQL支持数个事务安全存储引擎，为获得最好结果，你不应该在一个事务那混合不同表类型。如果你混合表类型会发生问题，更多信息请参阅[13.4.1节，“START TRANSACTION, COMMIT和ROLLBACK Syntax”](# "13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax")。 如果你没有指定配置值的话，InnoDB使用默认的配置值。请参阅[15.2.3节，“InnoDB配置”](# "15.2.3. InnoDB Configuration")。 非事务安全表自身有几个优点，因为没有事务开支，所有优点都能出现： ·        更快 ·        需要更少的磁盘空间 ·        执行更新需要更少的内存 你可以在同一个语句中合并事务安全和非事务安全表来获得两者最好的情况。尽管如此，在autocommit被禁止掉的事务里，变换到非事务安全表依旧即时提交，并且不会被回滚。 ### 15.1. MyISAM存储引擎 [15.1.1. MyISAM启动选项](#) [15.1.2.键需要的空间](#) [15.1.3. MyISAM表存储格式](#) [15.1.4. MyISAM表的问题](#) MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 要明确表示你想要用一个MyISAM表格，请用ENGINE表选项指出来： ~~~ CREATE TABLE t (i INT) ENGINE = MYISAM; ~~~ **注释**：老版本的MySQL使用TYPE而不是ENGINE（例如，TYPE = MYISAM）。MySQL 5.1为向下兼容而支持这个语法，但TYPE现在被轻视，而ENGINE是首先的用法。 一般地，ENGINE选项是不必要的；除非默认已经被改变了，MyISAM是默认存储引擎。 你可以用**myisamchk**工具来检查或修复MyISAM表。请参阅[5.9.5.6节，“使用**myisamchk**做崩溃恢复”](# "5.9.5.6. Using myisamchk for Crash Recovery")。你也可以用**myisampack**来压缩MyISAM表，让它们占更少的空间。请参阅[8.2节，“myisampack，产生压缩、只读的MyISAM表”](# "8.2. myisampack — Generate Compressed, Read-Only MyISAM Tables")。 如下是MyISAM存储引擎的一些特征： ·        所有数据值先存储低字节。这使得数据机和操作系统分离。二进制轻便性的唯一要求是机器使用补码（如最近20年的机器有的一样）和IEEE浮点格式（在主流机器中也完全是主导的）。唯一不支持二进制兼容性的机器是嵌入式系统。这些系统有时使用特殊的处理器。 先存储数据低字节并不严重地影响速度；数据行中的字节一般是未联合的，从一个方向读未联合的字节并不比从反向读更占用更多的资源。服务器上的获取列值的代码与其它代码相比并不显得时间紧。 ·        大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。 ·        当把删除和更新及插入混合的时候，动态尺寸的行更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。 ·        每个MyISAM表最大索引数是64。 这可以通过重新编译来改变。每个索引最大的列数是16个。 ·        最大的键长度是1000字节。这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的的键块被用上。 ·        BLOB和TEXT列可以被索引。 ·        NULL值被允许在索引的列中。这个占每个键的0-1个字节。 ·        所有数字键值以高字节为先被存储以允许一个更高地索引压缩。 ·        当记录以排好序的顺序插入（就像你使用一个AUTO_INCREMENT列之时），索引树被劈开以便高节点仅包含一个键。这改善了索引树的空间利用率。 ·        每表一个AUTO_INCREMEN列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。（当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。AUTO_INCREMENT值可用ALTER TABLE或**myisamch**来重置。 ·        如果数据文件中间的表没有自由块了，在其它线程从表读的同时，你可以INSERT新行到表中。（这被认识为并发操作）。自由块的出现是作为删除行的结果，或者是用比当前内容多的数据对动态长度行更新的结果。当所有自由块被用完（填满），未来的插入又变成并发。 ·        你可以把数据文件和索引文件放在不同目录，用DATA DIRECTORY和INDEX DIRECTORY选项CREATE TABLE以获得更高的速度，请参阅[13.1.5节，“CREATE TABLE语法”](# "13.1.5. CREATE TABLE Syntax")。 ·        每个字符列可以又不同的字符集，请参阅[第10章：“字符集支持”](# "Chapter 10. Character Set Support")。 ·        在MyISAM索引文件里又一个标志，它表明表是否被正确关闭。如果用--myisam-recover选项启动**mysqld**，MyISAM表在打开得时候被自动检查，如果被表被不恰当地关闭，就修复表。 ·        如果你用--update-state选项运行**myisamchk**，它标注表为已检查。**myisamchk --fast**只检查那些没有这个标志的表。 ·        **myisamchk --analyze**为部分键存储统计信息，也为整个键存储统计信息。 ·        **myisampack**可以打包BLOB和VARCHAR列。 MyISAM也支持下列特征： ·        支持true VARCHAR类型；VARCHAR列以存储在2个字节中的长度来开始。 ·        有VARCHAR的表可以有固定或动态记录长度。 ·        VARCHAR和CHAR列可以多达64KB。 ·         一个被搞乱的已计算索引对可对UNIQUE来使用。这允许你在表内任何列的合并上有UNIQUE。（尽管如此，你不能在一个UNIQUE已计算索引上搜索）。 对MyISAM存储引擎，有一个更详细的论坛在[http://forums.mysql.com/list.php?21](http://forums.mysql.com/list.php?21)。 ### 15.1.1. MyISAM启动选项 下列对**mysqld** 的选项可用来改变MyISAM表的行为： ·        --myisam-recover=*mode* 设置为崩溃MyISAM表自动恢复的模式。 ·        --delay-key-write=ALL 对任何MyISAM表的写操作之间不要刷新键缓冲区。 **注释：**如果你要这么做。当表在使用中之时，你应该不使用来自另一个程序的MyISAM表（比如从另一个MySQL服务器或用**myisamchk**）。这么做会导致索引被破坏。 对使用--delay-key-write的表，使用--external-locking没有帮助。 请参阅[5.3.1节，“**mysqld**命令行选项”](# "5.3.1. mysqld Command-Line Options")。 下列系统变量影响MyISAM表的行为： ·        bulk_insert_buffer_size 用在块插入优化中的树缓冲区的大小。**注释**：这是一个*per thread*的限制。 ·        (*OBSOLETE*) myisam_max_extra_sort_file_size 这个参数已经不在MySQL中使用。 ·        myisam_max_sort_file_size 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。**注释**：这个参数以字节的形式给出。 ·        myisam_sort_buffer_size 设置恢复表之时使用的缓冲区的尺寸。 请参阅[5.3.3节，“服务器系统变量”](# "5.3.3. Server System Variables")。 如果用--myisam-recover选项启动**mysqld**，自动恢复被激活。在这种情况下，当服务器打开一个MyISAM表之时，服务器会检查是否表被标注为崩溃，或者表的打开计数变量是否不为0且你正用--skip-external-locking运行服务器。如果这些条件的任何一个为真，下列情况发生： ·        表被查错。 ·        如果服务器发现一个错误，它试着做快速表修复（排序且不重新创建数据文件）。 ·        如果修复因为数据文件中的一个错误而失败（例如，一个重复键错误），服务器会再次尝试修复，这一次重建数据文件。 ·         如果修复仍然失败，服务器用旧修复选项方法再重试一次修复（一行接一行地写，不排序）。这个方法应该能修复任何类型的错误，并且需要很低的磁盘空间。 如果恢复不能够从先前完成的语句里恢复所有行，而且你不能在--myisam-recover选项值指定FORCE，自动修复会终止，并在错误日志里写一条错误信息： ~~~ Error: Couldn't repair table: test.g00pages ~~~ 如果你指定FORCE，取而代之地，类似这样的一个警告被给出： ~~~ Warning: Found 344 of 354 rows when repairing ./test/g00pages ~~~ 注释：如果自动恢复值包括BACKUP，恢复进程创建文件并用*tbl_name-datetime*.BAK形式取名。你应该有一个**cron**脚本，它自动把这些文件从数据库目录移到备份媒质上。 ### 15.1.2.键所需的空间 MyISAM表使用B型树索引。你可以粗略地计算索引文件的大小为(key_length+4)/0.67, 加上所有的键之和。当所有键以排序的顺序插入并且表没有任何压缩的键之时，以上估计是对最坏的情况的。 字符串索引是被空间压缩的。如果第一个字符串索引部分是字符串，它也被加前缀压缩。如果字符串列有许多拖曳空间，或字符串列是一个总是不用完全长度的VARCHAR列，空间压缩使得索引文件比最坏情况时的数值要小。前缀压缩被用在以字符串开始的键上。如果有许多具有同一前缀的字符串，前缀压缩是有帮助的。 在MyISAM表，你也可以在创建表的时候通过指定PACK_KEYS=1来前缀压缩数字。当数字被以高字节优先存储之时，若你有许多具有同一前缀的整数键，上述方法是有帮助的。 ### 15.1.3. MyISAM表的存储格式 [15.1.3.1. 静态（固定长度）表特征](#) [15.1.3.2. 动态表特征](#) [15.1.3.3. 已压缩表特征](#) MyISAM支持三种不同存储格式。其中两个（固定格式和动态格式)根据正使用的列的类型来自动选择。第三个，即已压缩格式，只能使用**myisampack**工具来创建。 当你CREATE或ALTER一个没有BLOB或TEXT列的表，你可以用ROW_FORMAT表选项强制表的格式为FIXED或DYNAMIC。这会导致CHAR和VARCHAR列因FIXED格式变成CHAR，或因DYNAMIC格式变成VARCHAR。 通过用ALTER TABLE指定ROW_FORMAT={COMPRESSED | DEFAULT}，你可以压缩或解压缩表，请参阅[13.1.5节，“CREATE TABLE语法”](# "13.1.5. CREATE TABLE Syntax")。 #### 15.1.3.1. 静态（固定长度）表特征 静态格式是MyISAM表的默认存储格式。当表不包含变量长度列（VARCHAR, BLOB, 或TEXT）时，使用这个格式。每一行用固定字节数存储。 MyISAM的三种存储格式中，静态格式就最简单也是最安全的（至少对于崩溃而言）。静态格式也是最快的on-disk格式。快速来自于数据文件中的行在磁盘上被找到的容易方式：当按照索引中的行号查找一个行时，用行长度乘以行号。同样，当扫描一个表的时候，很容易用每个磁盘读操作读一定数量的记录。 当MySQL服务器正往一个固定格式MyISAM文件写的时候，如果计算机崩溃了，安全是显然的。在这种情况下，**myisamchk**可以容易地决定每行从哪里开始到哪里结束，所以它通常可以收回所有记录，除了写了一部分的记录。注意，基于数据行，MyISAM表索引可以一直被重新构建。 静态格式表的一般特征： ·        CHAR列对列宽度是空间填补的。 ·        非常快。 ·        容易缓存。 ·         崩溃后容易重建，因为记录位于固定位置。 ·        重新组织是不必要的，除非你删除巨量的记录并且希望为操作系统腾出磁盘空间。为此，可使用OPTIMIZE TABLE或者**myisamchk -r**。 ·        通常比动态格式表需要更多的磁盘空间。 #### 15.1.3.2. 动态表特征 如果一个MyISAM表包含任何可变长度列（VARCHAR, BLOB或TEXTDynamic），或者如果一个表被用ROW_FORMAT=DYNAMIC选项来创建，动态存储格式被使用。 这个格式更为复杂一点，因为每行有一个表明行有多长的头。当一个记录因为更新的结果被变得更长，该记录也可以在超过一个位置处结束。 你可以使用OPTIMIZE TABLE或**myisamchk**来对一个表整理碎片。如果在一个表中有你频繁访问或改变的固定长度列，表中也有一些可变长度列，仅为避免碎片而把这些可变长度列移到其它表可能是一个好主意。 动态格式表的一般特征： ·        除了长度少于4的列外，所有的字符串列是动态的。 ·        在每个记录前面是一个位图，该位图表明哪一列包含空字符串（对于字符串列）或者0（对于数字列）。注意，这并不包括包含NULL值的列。如果一个字符列在拖曳空间移除后长度为零，或者一个数字列为零值，这都在位图中标注了且列不被保存到磁盘。 非空字符串被存为一个长度字节加字符串的内容。 ·        通常比固定长度表需要更少的磁盘空间。 ·        每个记录仅使用必需大小的空间。尽管如此，如果一个记录变大，它就按需要被分开成多片，造成记录碎片的后果。比如，你用扩展行长度的信息更新一行，该行就变得有碎片。在这种情况下，你可以时不时运行OPTIMIZE TABLE或**myisamchk -r**来改善性能。可使用**myisamchk -ei**来获取表的统计数据。 ·         动态格式表在崩溃后要比静态格式表更难重建，因为一个记录可能被分为多个碎片且链接（碎片）可能被丢失。 ·        动态尺寸记录期望的行长度用下列表达式来计算： ~~~ ·                3 ~~~ ~~~ ·                + (number of columns + 7) / 8 ~~~ ~~~ ·                + (number of char columns) ~~~ ~~~ ·                + (packed size of numeric columns) ~~~ ~~~ ·                + (length of strings) ~~~ ~~~ ·                + (number of NULL columns + 7) / 8 ~~~ 对每个链接需要额外的6字节。在一个更新导致一个记录的扩大之时，一个动态记录被链接了。每个新链接至少是20字节，所以下一个扩大可能在同样的链接里进行。如果不是，则另一个链接将被建立。你可以使用**myisamchk -ed**来找出链接的数目。所有的链接可以用**myisamchk -r**来移除。 #### 15.1.3.3. 已压缩表特征 已压缩存储格式是由**myisampack**工具创建的只读格式。 所有MySQL分发版里都默认包括**myisampack**。已压缩表可以用**myisamchk**来解压缩。 已压缩表有下列特征： ·        已压缩表占据非常小的磁盘空间。这最小化了磁盘用量，当使用缓慢的磁盘（如CD-ROM)之时，这是很有用的。 ·         每个记录是被单独压缩的，所以只有非常小的访问开支。依据表中最大的记录，一个记录的头在每个表中占据1到3个字节。每个列被不同地压缩。通常每个列有一个不同的Huffman树。一些压缩类型如下： o        后缀空间压缩。 -        前缀空间压缩。 -        零值的数用一个位来存储。 -        如果在一个整型列中的值有一个小的范围，列被用最小可能的类型来存储。比如，一个BIGINT列（8字节），如果所有它的值在-128到127范围内，它可以被存储为TINYINT列（1字节） -        如果一个列仅有一小组可能的值，列的类型被转化成ENUM。 -        一个列可以使用先前压缩类型的任意合并。 ·        可以处理固定长度或动态长度记录。 ### 15.1.4. MyISAM表方面的问题 [15.1.4.1. 损坏的MyISAM表](#) [15.1.4.2. 未被适当关闭的表的问题](#) MySQL用来存储数据的文件格式已经被广泛测试过，但总是有导致数据表变得损坏的环境。 #### 15.1.4.1. 损坏的MyISAM表 即使MyISAM表格式非常可靠（SQL语句对表做的所有改变在语句返回之前被写下），如果下列任何事件发生，你依然可以获得损坏的表： ·        **mysqld**进程在写中间被杀掉。 ·        发生未预期的计算机关闭（例如，计算机被关闭）。 ·        硬件故障。 ·        你可以同时在正被服务器修改的表上使用外部程序（如**myisamchk**）。 ·        MySQL或MyISAM代码的软件缺陷。 一个损坏的表的典型症状如下： ·        当在从表中选择数据之时，你得到如下错误： ~~~ ·                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.3. CHECK TABLE Syntax")， [13.5.2.6节，“REPAIR TABLE语法”](# "13.5.2.6. REPAIR TABLE Syntax")，和[5.9.5节，“myisamchk — MyISAM表维护工具”](# "5.9.5. myisamchk — MyISAM Table-Maintenance Utility")。 如果你的表变得频繁损坏，你应该试着确定为什么会这样的原因。要明白的最重要的事是表变得损坏是不是因为服务器崩溃的结果。你可以在错误日志中查找最近的restarted mysqld消息来早期验证这个。如果存在这样一个消息，则表损坏是服务器死掉的一个结果是很有可能的。否则，损坏可能在正常操作中发生。这是一个缺陷。你应该试着创建一个展示这个问题的可重复生成的测试案例。请参阅[A.4.2节，“如果MySQL保持崩溃，该怎么做”](# "A.4.2. What to Do If MySQL Keeps Crashing") 及[E.1.6节，“如果出现表崩溃，请生成测试案例”](# "E.1.6. Making a Test Case If You Experience Table Corruption")。 #### 15.1.4.2. 未被适当关闭的表的问题 每个MyISAM索引文件(.MYI)在头有一个计数器，它可以被用来检查一个表是否被恰当地关闭。如果你从CHECK TABLE或**myisamchk**得到下列警告，意味着这个计数器已经不同步了： ~~~ clients are using or haven't closed the table properly ~~~ 这个警告并不是完全意味着表已被破坏，但你至少应该检查表。 计数器的工作方式如下： ·        表在MySQL中第一次被更新，索引文件头的计数器加一。 ·        在未来的更新中，计数器不被改变。 ·        当表的最后实例被关闭（因为一个操作FLUSH TABLE或因为在表缓冲区中没有空间）之时，若表已经在任何点被更新，则计数器减一。 ·        当你修理或检查表并且发现表完好之时，计数器被重置为零。 ·        要避免与其它可能检查表的进程进行事务的问题，若计数器为零，在关闭时计数器不减一。 换句话来说，计数器只有在下列情况会不同步： ·        MyISAM表不随第一次发出的LOCK TABLES和FLUSH TABLES被复制。 ·        MySQL在一次更新和最后关闭之间崩溃（注意，表可能依然完好，因为MySQL总是在每个语句之间为每件事发出写操作）。 ·        一个表被**myisamchk --recover**或**myisamchk --update-state**修改，同时被**mysqld**使用。 ·        多个**mysqld**服务器正使用表，并且一个服务器在一个表上执行REPAIR TABLE或CHECK TABLE，同时该表也被另一个服务器使用。在这个结构中，使用CHECK TABLE是安全的，虽然你可能从其它服务器上得到警告。尽管如此，REPAIR TABLE应该被避免，因为当一个服务器用一个新的数据文件替代旧的之时，这并没有发送信号到其它服务器上。 总的来说，在多服务器之间分享一个数据目录是一个坏主意。请参阅[5.12节，“在同一个机器上运行多个MySQL服务器”](# "5.12. Running Multiple MySQL Servers on the Same Machine") 获得更多地讨论 ### 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/](http://www.mysql.com/company/legal/licensing/)。 关于InnoDB存储引擎，在[http://forums.mysql.com/list.php?22](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数据和日志文件”](# "15.2.7. Adding and Removing InnoDB Data and Log Files")。 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表空间”](# "15.2.5. Creating the InnoDB Tablespace")，以获得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文件的位置”](# "2.3.5.14. The Location of the my.ini File")。 **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节，“为表空间使用原始设备”](# "15.2.14.2. Using Raw Devices for the Tablespace")。 **警告**：在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启动选项”](# "15.2.4. InnoDB Startup Options")里对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节，“为表空间使用原始设备”](# "15.2.14.2. Using Raw Devices for the Tablespace")。 ·        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表空间”](# "15.2.6.6. Using Per-Table Tablespaces")。 ·        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节，“强制恢复”](# "15.2.8.1. Forcing Recovery")中叙述。作为一个安全措施，当这个选项值大于零之时，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节，“多版本的实施”](# "15.2.12. Implementation of Multi-Versioning")）。这个参数的默认值是零，意为无延迟。这个选项可以在运行时作为全局系统变量而被改变。 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.6.6. Using Per-Table Tablespaces")。 #### 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 (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也提供单独的专门的事务提交和回滚函数或者方法。 #### 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.8.1. Forcing Recovery")。 #### 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列不被包括在一个外键中，这是因为对这些列的索引必须总是包含一个前缀长度。 ·        如果CONSTRAINT*symbol*被给出，它在数据库里必须是唯一的。如果它没有被给出，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数据库到另一台机器”](# "15.2.9. Moving an InnoDB Database to Another Machine")。 如果你可以关闭主服务器或者一个存在的从服务器。你可以采取InnoDB表空间和日志文件的冷备份，并用它来建立一个从服务器。要不关闭任何服务器来建立一个新的从服务器，你也可以使用非免费（商业的）[InnoDB热备份工具](http://www.innodb.com/order.html)。 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**热备份](http://www.innodb.com/order.html)[主页](http://www.innodb.com/order.html)以获得更详细的信息以及屏幕截图。 如果你可以关闭你的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数据库的备份和恢复”](# "15.2.8. Backing Up and Recovering an InnoDB Database")里的相关文件来简单地移动一个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* 锁定。 这些结果可以方便地用一个锁类型兼容矩阵来总结： <table border="1" cellpadding="0"><colgroup><col/><col/><col/><col/><col/></colgroup><tbody><tr><td> <p><span> </span></p> </td> <td> <p><span>X</span></p> </td> <td> <p><span>IX</span></p> </td> <td> <p><span>S</span></p> </td> <td> <p><span>IS</span></p> </td> </tr><tr><td> <p><span>X</span></p> </td> <td> <p><span>冲突 </span></p> </td> <td> <p><span>冲突</span></p> </td> <td> <p><span>冲突</span></p> </td> <td><span>冲突</span></td> </tr><tr><td> <p><span>IX</span></p> </td> <td> <p><span>冲突</span></p> </td> <td> <p><span>兼容</span></p> </td> <td> <p><span>冲突</span></p> </td> <td> <p><span>兼容</span></p> </td> </tr><tr><td> <p><span>S</span></p> </td> <td> <p><span>冲突</span></p> </td> <td> <p><span>冲突</span></p> </td> <td> <p><span>兼容</span></p> </td> <td> <p><span>兼容</span></p> </td> </tr><tr><td> <p><span>IS</span></p> </td> <td> <p><span>冲突</span></p> </td> <td> <p><span>兼容</span></p> </td> <td> <p><span>兼容</span></p> </td> <td> <p><span>兼容</span></p> </td> </tr></tbody></table> 如果一个锁定与现在锁定兼容的话，它被授给一个委托事务。如果一个锁定与现存锁定冲突，它就不被授予一个委托事务。事务等待着直到冲突的现存锁定被释放掉。如果一个锁定请求与现存锁定相冲突，且不能被授予，因为它可能会导致死锁，一个错误产生。 因此，意图锁定不阻碍任何东西，除了完全表请求（比如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节，“持续非锁定读”](# "15.2.10.4. Consistent Non-Locking Read")。 ·        REPEATABLE READ 这是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录，而不锁定记录前的间隙。用其它搜索条件，这些操作采用next-key锁定，用next-key锁定或者间隙锁定锁住搜索的索引范围，并且阻止其它用户的新插入。 在持续读中，有一个与之前隔离级别重要的差别：在这个级别，在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句，这些SELECT语句对相互之间也是持续的，请参阅[15.2.10.4节，“持续非锁定读”](# "15.2.10.4. Consistent Non-Locking Read")。 ·        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 (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; ~~~ #### 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 (...)对被插入的行设置独占锁定。注意，这不是一个next-key锁定，并且不阻止其它用户在已插入行之前的间隙插入。如果发生重复键错误，对重复的索引记录设置共享锁定。 ·        在一个表上初始化之前指定的AUTO_INCREMENT列之时，InnoDB在与AUTO_INCREMENT列相关联的索引的末尾设置独占锁定。在访问自动增长计数器中，InnoDB使用专用的表锁定模式AUTO-INC，其中锁定仅持续到当前SQL语句的结束，而不是到整个事务的结束。 请参阅[15.2.10.2节，“InnoDB和AUTOCOMMIT”](# "15.2.10.2. InnoDB and AUTOCOMMIT")。 InnoDB取回先前初始化的AUTO_INCREMENT列的值而不设定任何锁定。 ·        INSERT INTO T SELECT ... FROM S WHERE ... 对每个插入到T的行设置独占（非next-key）锁定。它在S上把搜索当作一个持续读，但是如果MySQL二进制日志功能被打开，它就对S设置一个共享的next-key锁定。InnoDB在后一种情况不得不设置锁定：在从一个备份的前滚恢复中，每个SQL语句不得不以与它最初被执行的方式完全同样的方式执行。 ·        CREATE TABLE ... SELECT ... 把SELECT当作一个持续读来执行，或者带着共享锁定来执行，如前面的条目所述。 ·        如果唯一键没有冲突，REPLACE象一个插入一样被做。另外，对必须更新的行设置一个独占的next-key锁定。 ·        UPDATE ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。 ·        DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。 ·        如果对一个表定义FOREIGN KEY约束，任何需要检查约束条件的插入，更新或删除对它看着检查约束的记录设置共享行级锁定。InnoDB在约束失败的情况下也设置这些锁定。 ·        LOCK TABLES设置表锁定，但是是InnoDB层之上更高的MySQL层设置这些锁定。如果innodb_table_locks=1并且 and AUTOCOMMIT=0，InnoDB意识到表锁定，并且InnoDB之上的MySQL层知道行级锁定。另外，InooDB的自动死锁检测不能检测在这个表锁定被涉及之处的死锁。同样，既然更高的MySQL层不知道行级锁定，很可能对另一个用户当前对其有行锁定的表获得一个表锁定。尽管如此，这并不破坏事务的完整性，如[15.2.10.10节，“死锁检测和回滚”](# "15.2.10.10. Deadlock Detection and Rollback")中讨论的一样。请参阅[15.2.16节，“对InnoDB表的限制”](# "15.2.16. Restrictions on InnoDB Tables")。 #### 15.2.10.9.  MySQL何时隐式提交或回滚一个事务？ MySQL以默认允许autocommit模式来开始每一个客户端连接。当autocommit被允许之时，如果SQL语句不返回错误的话，MySQL在每个SQL语句之后，做一个提交。 如果你关闭autocommit模式并且关闭一个连接而不调用你的事务的明确提交，则MySQL回滚你的事务。 如果SQL语句返回一个错误，提交/回滚行为取决于这个错误。请参阅[15.2.15节，“InnoDB错误处理”](# "15.2.15. InnoDB Error Handling")。 下列每一个语句（以及它们的任何同义词）隐式结束一个事务，就好像在执行这个语句之前你做了一个COMMIT： ·        ALTER FUNCTION, 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节，“强制恢复”](# "15.2.8.1. Forcing Recovery")。 ·        也要小心其它大的磁盘绑定操作。用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 ~~~ #### 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锁定模式”](# "15.2.10.1. InnoDB Lock Modes")。 ~~~ 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 (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) ~~~ 关于这个输出一些要注意的： ·        如果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.4. InnoDB Startup Options") 以获得更多信息。 ### 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节，“强制恢复”](# "15.2.8.1. Forcing Recovery")）。 当许多第二索引必须被更新之时，并且许多行已被插入之时，插入缓冲合并可能需要数个小时。在这个时间内，磁盘I/O将会增加，这样会导致磁盘绑定查询明显缓慢。另一个明显的后台I/O操作是净化线程（请参阅[15.2.12节，“实现多版本化”](# "15.2.12. Implementation of Multi-Versioning")）。 #### 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节，“安静的列规格改变”](# "13.1.5.1. Silent Column Specification Changes")。 ·        一个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数据词典操作故障诊断和排除”](# "15.2.17.1. Troubleshooting InnoDB Data Dictionary Operations")。 ·        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](http://www.iglu.org.il/lxr/source/include/asm-i386/errno.h)。 ·        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](http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes.asp)。 ·        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节，“服务器系统变量”](# "5.3.3. Server System Variables")。请参阅[A.6节，“优化器相关的问题”](# "A.6. Optimizer-Related Issues")。 ·        在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性能调节提示”](# "15.2.11. InnoDB Performance Tuning Tips")。 ·        对于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外壳里复制或重命名一个文件，如果文件名包含"#"字符，你需要把文件名放在双引号里。 ### 15.3. MERGE存储引擎 [15.3.1. MERGE表的问题](#) MERGE存储引擎，也被认识为MRG_MyISAM引擎，是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。你不能合并列被以不同顺序列于其中的表，没有恰好同样列的表，或有不同顺序索引的表。而且，任何或者所有的表可以用**myisampack**来压缩。请参阅[8.2节，“myisampack — 生成压缩的只读MyISAM表”](# "8.2. myisampack — Generate Compressed, Read-Only MyISAM Tables")。表选项的差异，比如AVG_ROW_LENGTH, MAX_ROWS或PACK_KEYS都不重要。 当你创建一个MERGE表之时，MySQL在磁盘上创建两个文件。文件名以表的名字开始，并且有一个扩展名来指明文件类型。一个.frm文件存储表定义，一个.MRG文件包含被当作一个来用的表的名字。这些表作为MERGE表自身，不必要在同一个数据库中。 你可以对表的集合用SELECT, DELETE, UPDATE和INSERT。你必须对你映射到一个MERGE表的这些表有SELECT, UPDATE和DELETE 的权限。 如果你DROP MERGE表，你仅在移除MERGE规格。底层表没有受影响。 当你创建一个MERGE表之时，你必须指定一个UNION=(*list-of-tables*)子句，它说明你要把哪些表当作一个来用。如果你想要对MERGE表的插入发生在UNION列表中的第一个或最后一个表上，你可以选择地指定一个INSERT_METHOD选项。使用FIRST或LAST值使得插入被相应地做在第一或最后一个表上。如果你没有指定INSERT_METHOD选项，或你用一个NO值指定该选项。往MERGE表插入记录的试图导致错误。 下面例子说明如何创建一个MERGE表： ~~~ mysql> CREATE TABLE t1 ( ~~~ ~~~     ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ~~~ ~~~     ->    message CHAR(20)); ~~~ ~~~ mysql> CREATE TABLE t2 ( ~~~ ~~~     ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ~~~ ~~~     ->    message CHAR(20)); ~~~ ~~~ mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); ~~~ ~~~ mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); ~~~ ~~~ mysql> CREATE TABLE total ( ~~~ ~~~     ->    a INT NOT NULL AUTO_INCREMENT, ~~~ ~~~     ->    message CHAR(20), INDEX(a)) ~~~ ~~~     ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; ~~~ 注意，一个列在MERGEN表中被索引，但没有被宣告为一个PRIMARY KEY，因为它是在更重要的MyISAM表中。这是必要的，因为MERGE表在更重要的表中的设置上强制非唯一性。 创建MERGE表之后，你可以发出把一组表当作一体来操作的查询： ~~~ mysql> SELECT * FROM total; ~~~ ~~~ +---+---------+ ~~~ ~~~ | a | message | ~~~ ~~~ +---+---------+ ~~~ ~~~ | 1 | Testing | ~~~ ~~~ | 2 | table   | ~~~ ~~~ | 3 | t1      | ~~~ ~~~ | 1 | Testing | ~~~ ~~~ | 2 | table   | ~~~ ~~~ | 3 | t2      | ~~~ ~~~ +---+---------+ ~~~ 注意，你也可以直接从MySQL之外直接操作.MRG文件： ~~~ shell> cd /mysql-data-directory/current-database ~~~ ~~~ shell> ls -1 t1 t2 > total.MRG ~~~ ~~~ shell> mysqladmin flush-tables ~~~ 要重映射一个MERGE表到一个不同的MyISAM表集，你可以执行下列之一： ·        DROP MERGE表并且重建它。 ·        使用ALTER TABLE *tbl_name* UNION=(...)来改变底层表的列表。 ·        改变.MRG文件，并对MERGE表或者所有底层表发出一个FLUSH TABLE语句来强制存储引擎去读新的定义文件。 MERGE表可以帮你解决以下问题： ·        容易地管理一套日志表。比如，你可以把不同月的数据放进分离的表中，用**myisampack**压缩其中的一些，并随后创建一个MERGE表来把它们当作一个来使用。 ·        获得更快的速度。你可以以一些标准来分割大的只读表，然后放进不同磁盘上的单个表中。基于此的一个MERGE表可比使用大表要快得多。 ·        执行更有效的搜索。如果你确切知道要搜索什么，对一些查询你可以只在被分割的表的其中之一来搜索，并且对其它使用MERGE。你甚至有许多不同的MERGE表，它们使用有重叠的表套。 ·        执行更有效的修补。修补被映射到一个MERGE表中的单个表比修补单个大型表要更轻松。 ·        即刻映射许多表成一个。MERGE表不需要维护它自己的索引，因为它使用大哥表的所用。因此，MERGE表集合是非常块地创建或重映射。（注意，当你创建一个MERGE表之时，即使没有索引被创建，你必须仍然指定索引定义）。 ·        如果根据需要或按照批次，你有一组要合起来作为一个大表的表，你应该根据需要对它们创建一个MERGE表来替代大表。这样要快得多而且节约大量的磁盘空间。 ·        超过操作系统的文件尺寸限制。每个MyISAM表都受制于这个限制，但是一个MyISAM表的集合则不然。 ·        你可以通过定义一个映射到单个表的MERGE表来为一个MyISAM表创建一个别名或“同物异名”。这样做应该没有真实的可察觉的性能影响 （对每个读只有一些间接调用和memcpy()调用）。 MERGE表的缺点： ·        你可以对MERGE表使用仅相同的MyISAM表。 ·        你不能在MERGE表中使用很多MyISAM功能。比如，你不能在MERGE表上创建FULLTEXT索引。（当然，你可以在底层MERGE 表上创建FULLTEXT索引，但是你不能用全文搜索来搜索MERGE表）。 ·        如果MERGE表是非临时的，所有底层MyISAM表也必须是永久的。如果MERGE表是临时的，MyISAM表可以是任何临时＆非临时的混合。 ·        MERGE表使用更多的文件描述符。如果是个客户端正使用一个映射到10个表的MERGE表，服务器使用(10*10)+10个文件描述符。（10个数据文件描述符给10个客户端每人一个，并且在客户端之间共享10个索引文件描述符）。 · 键读会更慢。当你读一个键的时候，MERGE存储引擎需要在所有 底层表上发出一个读以检查哪一个接近匹配给定的键。如果你随后做了一个read-next，MERGE存储引擎需要搜索读缓冲来找出下一个键。只有当一个键缓冲被耗尽，存储引擎才需要读下一个键块。这使得MERGE键在eq_ref搜索中非常慢，但在ref搜索中不是太慢。请参阅[7.2.1节，“EXPLAIN 语法(获取SELECT相关信息)”](# "7.2.1. EXPLAIN Syntax (Get Information About a SELECT)") 以获得更多关于eq_ref和ref的信息。 ### 15.3.1. MERGE表方面的问题 下列是已知关于MERGE表的问题： ·        如果你使用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表。 对于MERGE存储引擎，在[http://forums.mysql.com/list.php?93](http://forums.mysql.com/list.php?93)上有一个专门的论坛。 ### 15.4. MEMORY (HEAP)存储引擎 MEMORY存储引擎用存在内存中的内容来创建表。这些在以前被认识为HEAP表。MEMORY是一个首选的术语，虽然为向下兼容，HEAP依旧被支持。 每个MEMORY表和一个磁盘文件关联起来。文件名由表的名字开始，并且由一个.frm的扩展名来指明它存储的表定义。 要明确指出你想要一个MEMORY表，可使用ENGINE选项来指定： ~~~ CREATE TABLE t (i INT) ENGINE = MEMORY; ~~~ 如它们名字所指明的，MEMORY表被存储在内存中，且默认使用哈希索引。这使得它们非常快，并且对创建临时表非常有用。可是，当服务器关闭之时，所有存储在MEMORY表里的数据被丢失。因为表的定义被存在磁盘上的.frm文件中，所以表自身继续存在，在服务器重启动时它们是空的。 这个例子显示你如何可以创建，使用并删除一个MEMORY表： ~~~ mysql> CREATE TABLE test ENGINE=MEMORY ~~~ ~~~     ->     SELECT ip,SUM(downloads) AS down ~~~ ~~~     ->     FROM log_table GROUP BY ip; ~~~ ~~~ mysql> SELECT COUNT(ip),AVG(down) FROM test; ~~~ ~~~ mysql> DROP TABLE test; ~~~ MEMORY表有下列特征： ·        给MEMORY表的空间被以小块来分配。表对插入使用100%动态哈希来。不需要溢出区或额外键空间。自由列表无额外的空间需求。已删除的行被放在一个以链接的列表里，并且在你往表里插入新数据之时被重新使用。MEMORY表也没有通常与在哈希表中删除加插入相关的问题。 ·        MEMORY表可以有多达每个表32个索引，每个索引16列，以及500字节的最大键长度。 ·        MEMORY存储引擎执行HASH和BTREE索引。你可以通过添加一个如下所示的USING子句为给定的索引指定一个或另一个： ~~~ ·                CREATE TABLE lookup ~~~ ~~~ ·                    (id INT, INDEX USING HASH (id)) ~~~ ~~~ ·                    ENGINE = MEMORY; ~~~ ~~~ ·                CREATE TABLE lookup ~~~ ~~~ ·                    (id INT, INDEX USING BTREE (id)) ~~~ ~~~ ·                    ENGINE = MEMORY; ~~~ B树的一般特征和哈希索引在[7.4.5节，“MySQL如何使用索引”](# "7.4.5. How MySQL Uses Indexes")里描述。 ·        你可以在一个MEMORY表中有非唯一键。（对哈希索引的实现，这是一个不常用的功能）。 ·        你页可以对MEMORY表使用INSERT DELAYED。请参阅[13.2.4.2节，“INSERT DELAYED语法”](# "13.2.4.2. INSERT DELAYED Syntax") ·        如果你在一个有高度键重复的（许多索引条目包含同一个值）MEMORY表上有一个哈希索引，对影响键值的表的更新及所有删除都是明显地慢的。这个变慢的程度比例于重复的程度（或者反比于索引cardinality）。你可以使用一个B树索引来避免这个问题。 ·        MEMORY表使用一个固定的记录长度格式。 ·        MEMORY不支持BLOB或TEXT列。 ·        MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。 ·        MEMORY表在所有客户端之间共享（就像其它任何非TEMPORARY表）。 ·        MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理之时的空闲中创建的内部表共享。可是，两个类型的表不同在于MEMORY表不会遇到存储转换，而内部表是： -        如果一个内部表变得太大，服务器自动把它转换为一个磁盘表。尺寸限制由tmp_table_size系统变量的值来确定。 -        MEMORY表决不会转换成磁盘表。要确保你不会偶尔做点傻事，你可以设置max_heap_table_size系统变量给MEMORY表加以最大尺寸。对于单个的表，你也可以在CREATE TABLE语句中指定一个MAX_ROWS表选项。 ·        服务器需要足够内存来维持所有在同一时间使用的MEMORY表。 ·        当你不再需要MEMORY表的内容之时，要释放被MEMORY表使用的内存，你应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE）。 ·        当MySQL服务器启动时，如果你想开拓MEMORY表，你可以使用--init-file选项。例如，你可以把INSERT INTO ... SELECT 或LOAD DATA INFILE这样的语句放入这个文件中以便从持久稳固的的数据源装载表。请参阅[5.3.1节，“**mysqld** 命令行选项”](# "5.3.1. mysqld Command-Line Options") and [13.2.5节，“LOAD DATA INFILE 语法”](# "13.2.5. LOAD DATA INFILE Syntax")。 ·        如果你正使用复制，当主服务器被关闭且重启动之时，主服务器的MEMORY表变空。可是从服务器意识不到这些表已经变空，所以如果你从它们选择数据，它就返回过时的内容。自从服务器启动后，当一个MEMORY表在主服务器上第一次被使用之时，一个DELETE FROM语句被自动写进主服务器的二进制日志，因此再次让从服务器与主服务器同步。注意，即使使用这个策略，在主服务器的重启和它第一次使用该表之间的间隔中，从服务器仍旧在表中有过时数据。可是，如果你使用--init-file选项于主服务器启动之时在其上推行MEMORY表。它确保这个时间间隔为零。 ·        在MEMORY表中，一行需要的内存使用下列表达式来计算： ~~~ ·                SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4) ~~~ ~~~ ·                + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2) ~~~ ~~~ ·                + ALIGN(length_of_row+1, sizeof(char*)) ~~~ ALIGN()代表round-up因子，它使得行的长度为char指针大小的确切倍数。sizeof(char*)在32位机器上是4，在64位机器上是8。 对于MEMORY存储引擎，在[http://forums.mysql.com/list.php?92](http://forums.mysql.com/list.php?92)上有一个专门的论坛。 ### 15.5. BDB (BerkeleyDB)存储引擎 [15.5.1. BDB支持的操作系统](#) [15.5.2. 安装BDB](#) [15.5.3. BDB启动选项](#) [15.5.4. BDB表的特征](#) [15.5.5. 修改BDB所需的事宜](#) [15.5.6. BDB表的限制](#) [15.5.7. 使用BDB表时可能发生的错误](#) Sleepycat Software给MySQL提供Berkeley DB事务性存储引擎。这个存储引擎典型被简称为BDB。对BDB存储引擎的支持包括在MySQL源码分发版里，在MySQL-Max二进制分发版里被激活。 BDB表可能有一个更大的崩溃幸存机会，并且也具有对事务COMMIT和ROLLBACK操作的能力。MySQL源码分发版和被补丁过可以与MySQL一起工作的BDB分发版一起提供。你不能使用一个未补丁过的BDB版本与MySQL一起工作。。 我们在MySQL AB上与Sleepycat紧密合作工作以保持MySQL/BDB接口的质量在高水平。（即使Berkeley DB其本身是非常能经受考验和非常可靠的。MySQL接口仍然被认为是第三等质量的。我们将继续改善和优化它）。 当它达到对所有涉及BDB表的问题的支持之时，我们答应负责帮助我们的用户定位问题并创建可重复产生的测试案例。任何这样的测试案例被转交给Sleepycat，它反过来帮助我们找到并解决问题。因为这是一个二阶段的操作，任何关于BDB表的问题我们可能要花比对其它存储引擎稍微更长一点的时间来解决它。可是，我们期望这个过程没有显著的困难，因为Berkeley DB 代码本身被用在MySQL之外许多的应用中。 要获得关于Berkeley DB的一般信息，请访问Sleepycat网站，[http://www.sleepycat.com/](http://www.sleepycat.com/)。 ### 15.5.1. BDB支持的操作系统 当前，我们知道BDB存储引擎支持下列操作系统： ·        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 BDB不支持下列操作系统： ·        Linux 2.x Alpha ·        Linux 2.x AMD64 ·        Linux 2.x IA-64 ·        Linux 2.x s390 ·        Mac OS X **注释：**前一个列表还不完全，我们收到更多信息时我们会更新它。 如果你从支持BDB表的源码建立的MySQL，但是，当你启动**mysqld**之时，发生下列错误，这意味着对你的架构BDB不被支持： ~~~ bdb: architecture lacks fast mutexes: applications cannot be threaded ~~~ ~~~ Can't init databases ~~~ 在这种情况下，你必须重建MySQL不带BDB表支持或用--skip-bdb选项启动服务器。 ### 15.5.2. 安装BDB 如果你已经下载一个包括对Berkeley DB支持的的二进制版的MySQL， 只要简单地按照通常的二进制分发版安装指令。（MySQL-Max 分发版包括BDB支持）。 如果你从源码建立MySQL，你可以在其它任何常用选项之外用--with-berkeley-db选项运行**configure**来允许支持BDB。下载一个MySQL 5.1分发版，改变位置到它的最顶层目录，然后运行下面命令： ~~~ shell> ./configure --with-berkeley-db [other-options] ~~~ 更多信息，请参阅[2.7节，“在其它类似Unix系统上安装MySQL”](# "2.7. Installing MySQL on Other Unix-Like Systems"), [5.1.2节，“**mysqld-max**扩展MySQL服务器”](# "5.1.2. The mysqld-max Extended MySQL Server"), 和[2.8节，“使用源码分发版版安装MySQL”](# "2.8. MySQL Installation Using a Source Distribution")。 ### 15.5.3. BDB启动选项 下列对**mysqld**的选项可被用来改变BDB存储引擎的行为： ·        --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来禁止它。 请参阅[5.3.1节，“**mysqld**命令行选项”](# "5.3.1. mysqld Command-Line Options")。 如果你使用--skip-bdb选项，MySQL不初始化Berkeley DB库，而且这样节省大量的内存。尽管如此，如果你使用这个选项，你不能使用BDB表。如果你试着创建一个BDB表，MySQL取而代之地创建一个MyISAM。 通常，如果你象使用BDB表，你应该不用--bdb-no-recover选项启动**mysqld**。可是，如果BDB日志被破坏则当你试着启动**mysqld**时，上述办法启动服务器可能导致问题。请参阅[2.9.2.3节，“MySQL服务器的启动和故障诊断排除”](# "2.9.2.3. Starting and Troubleshooting the MySQL Server")。 使用bdb_max_lock 变量，你可以指定在BDB表上被激活的锁定的最大数目。默认值是10,000。当你执行长事务或当**mysqld**不得不检查许多行来执行一个查询之时，如果发生如下错误，你应该增加这个数目： ~~~ bdb: Lock table is out of available locks ~~~ ~~~ Got error 12 from ... ~~~ 如果你正使用大型多语句事务，你可能也想改变binlog_cache_size和max_binlog_cache_size变量。请参阅[5.11.3节，“二进制日志”](# "5.11.3. The Binary Log")。 也请参阅[5.3.3节，“服务器系统变量”](# "5.3.3. Server System Variables")。 ### 15.5.4. BDB表的特征 每个BDB表用两个文件被存在磁盘上。文件的名字用表的名字做开头，并且有一个扩展名来指明文件类型。一个.frm文件存储表定义，一个.db文件包含表数据和索引。 要明确指出你想要一个BDB表，用ENGINE或TYPE表选项来指明： ~~~ CREATE TABLE t (i INT) ENGINE = BDB; ~~~ ~~~ CREATE TABLE t (i INT) TYPE = BDB; ~~~ BerkeleyDB是用ENGINE或者TYPE选项的BDB存储引擎的“同义词”。 BDB存储引擎提供事务性表，你使用这些表的方法取决于autocommit模式： ·        如果你正运行着，同时随着autocommit的被允许（这是默认的），对BDB表的改变被立即提交并且不能被回滚。 ·        如果你正运行着，同时随着autocommit的被禁止，改变不变成永久的直到你执行一个COMMIT语句。作为提交的替代，你可以执行ROLLBACK来忘记改变。 你可以用BEGIN WORK语句开始一个事务来挂起autocommit，或者用SET AUTOCOMMIT=0来明确禁止autocommit。 请参阅[13.4.1节，“START TRANSACTION, COMMIT和ROLLBACK语法”](# "13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax")。 BDB存储引擎有下列特征： ·        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树而不是在分离的数据文件中。 ·        键值象MyISAM表中的键值一样不是前缀或后缀压缩的。换句话说，在BDB表中键信息只比在MyISAM表中稍微多占据一点空间。 ·        在BDB表中经常有洞允许你在索引树的中间插入新行。这个使得BDB表比MyISAM表稍微大一些 ·        SELECT COUNT(*) FROM *tbl_name*对BDB表很慢，因为在该表中没有行计数被维持。 ·        优化器需要知道表中的大概行数。MySQL通过计数插入以及在每个BDB表中的单独片断内维持它来解决了问题。如果你不发出大量的DELETE或ROLLBACK语句，这个数对MySQL优化器来说是足够精确了。可是，MySQL仅在关闭的时候才存储这个数，所以，如果服务器被意外地终止，这个数可能是不正确的。即使数不是100%正确，它不是明确的。你可以使用ANALYZE TABLE或者 OPTIMIZE TABLE语句来更新行计数。请参阅[13.5.2.1节，“ANALYZE TABLE语法”](# "13.5.2.1. ANALYZE TABLE Syntax") 和[13.5.2.5节，“OPTIMIZE TABLE语法”](# "13.5.2.5. OPTIMIZE TABLE Syntax")。 ·        BDB表上的内部锁定在页面级别上做。 ·        LOCK TABLES在BDB表上就想在其它表上一样工作。如果你不使用LOCK TABLES，MySQL对该表发出一个内部多重写锁定 （一个不阻止其它作者的锁定）来确保即使另一个线程发出一个表锁定，该表也被恰当地锁定了。 ·        要能够回滚一个事务，BDB存储引擎维护日志文件。多实现最高性能，你可以使用--bdb-logdir选项来把BDB日志放在不同的磁盘上，而不是放在数据库被放置的那个磁盘。 ·        每次新BDB日志文件被启动之时，MySQL执行一个检查点，并且删掉当前事务不需要的任何BDB日志文件。你也可以在任何时候使用FLUSH LOGS来给Berkeley DB表设置检查点。 对灾难恢复，你应该使用表备份加MySQL的二进制日志，请参阅[5.9.1节，“数据库备份”](# "5.9.1. Database Backups")。 **警告：**如果你删除仍在使用中的旧日志文件，BDB根本不能做恢复，并且如果有些事不对，你可能会丢失数据。 ·        应用程序必须总是被准备来处理情况，即BDB表任何的改变可能导致一个自动回滚并且任何读可能会带着一个死锁错误而失败。 ·        如果你在BDB表内遇到磁盘满，你得到一个错误（可能是错误28），并且事务应该回滚。这与MyISAM表相反，对于MyISAM 表，**mysqld**在继续之前等待足够的自由磁盘空间。 ### 15.5.5. 修改BDB所需的事宜 ·        同时打开许多BDB表可能是非常慢的。如果你要使用BDB表，你不应使用非常大的表缓存（例如，大于256）并且当你使用**mysql**客户端之时，你应该使用--no-auto-rehash选项。 ·        SHOW TABLE STATUS 不为BDB表提供一些信息： ~~~ ·                mysql> SHOW TABLE STATUS LIKE 'bdbtest'\G ~~~ ~~~ ·                *************************** 1. row *************************** ~~~ ~~~ ·                           Name: bdbtest ~~~ ~~~ ·                         Engine: BerkeleyDB ~~~ ~~~ ·                        Version: 10 ~~~ ~~~ ·                     Row_format: Dynamic ~~~ ~~~ ·                           Rows: 154 ~~~ ~~~ ·                 Avg_row_length: 0 ~~~ ~~~ ·                    Data_length: 0 ~~~ ~~~ ·                Max_data_length: 0 ~~~ ~~~ ·                   Index_length: 0 ~~~ ~~~ ·                      Data_free: 0 ~~~ ~~~ ·                 Auto_increment: NULL ~~~ ~~~ ·                    Create_time: NULL ~~~ ~~~ ·                    Update_time: NULL ~~~ ~~~ ·                     Check_time: NULL ~~~ ~~~ ·                      Collation: latin1_swedish_ci ~~~ ~~~ ·                       Checksum: NULL ~~~ ~~~ ·                 Create_options: ~~~ ~~~ ·                        Comment: ~~~ ·        优化性能。 ·        改变为对表扫描操作使用无页面锁定。 ### 15.5.6. 对BDB表的限制 下列表说明使用BDB表之时你必须要遵从的限制： ·        每个BDB表在.db文件里存储文件被创建之时到该文件的路径。这个被做来允许在支持symlinks的多用户环境里检测锁定。因此，从一个数据库目录往另一个目录移动BDB表是不能的。 ·        当制作BDB表的备份之时，你必须要么使用**mysqldump**要么做一个包含对每个BDB表的文件（.frm和.db文件）及BDB日志文件的备份。BDB存储引擎在它的日志文件存储未完成的事务以及要求它们在**mysqld**启动的时候被呈出来。BDB日志在数据目录里，具有log.XXXXXXXXXX（10位数字）形式名字的文件。 ·        如果允许NULL值的列有唯一的索引，只有单个NULL值是被允许的。这不同于其它存储引擎。 ### 15.5.7. 使用BDB表时可能发生的错误 ·        如果你升级之后启动**mysqld**时发生下列错误，它意味着新BDB版本不支持旧日志文件格式： ~~~ ·                bdb:  Ignoring log file: .../log.XXXXXXXXXX: ~~~ ~~~ ·                unsupported log version # ~~~ 在这种情况下，你必须删除从数据目录所有BDB日志（名字为log.XXXXXXXXXX这样格式的文件）并重新启动**mysqld**。我们也推荐你随后用**mysqldump --opt**来转储你的BDB表，移除表，并且从转储文件恢复它们。 ·        如果autocommit模式被禁止，你移除在另一个事务中被参考的BDB表，你会从你的MySQL错误日志得到如下的错误信息： ~~~ ·                001119 23:43:56  bdb:  Missing log fileid entry ~~~ ~~~ ·                001119 23:43:56  bdb:  txn_abort: Log undo failed for LSN: ~~~ ~~~ ·                                       1 3644744: Invalid ~~~ 这不是一个致命错误，但是知道问题被解决，我们推荐你不要移除BDB表，除了autocommit模式被允许之时。（修复不是微不足道的）。 ### 15.6. EXAMPLE存储引擎 EXAMPLE存储引擎是一个不做任何事情的存根引擎。它的目的是作为MySQL源代码中的一个例子，用来演示如何开始编写一个新存储引擎。 同样，它的主要兴趣是对开发者。 要对EXAMPLE引擎检查源码，请查看MySQL源码分发版的sql/examples目录。 要允许这个存储引擎，使用在建立MySQL之时使用--with-example-storage-engine选项来**configure**。 当你创建一个EXAMPLE之时，服务器创建在数据库目录创建一个表定义文件。文件由表名字开始，并由一个.frm的扩展名。没有其它文件被创建。没有数据被存储进表或者从中取回。 ~~~ mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE; ~~~ ~~~ Query OK, 0 rows affected (0.78 sec) ~~~ ~~~   ~~~ ~~~ mysql> INSERT INTO test VALUES(1),(2),(3); ~~~ ~~~ ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option ~~~ ~~~   ~~~ ~~~ mysql> SELECT * FROM test; ~~~ ~~~ Empty set (0.31 sec) ~~~ EXAMPLE存储引擎不支持编索引。 ### 15.7. FEDERATED存储引擎 [15.7.1. 安装FEDERATED存储引擎](#) [15.7.2. FEDERATED存储引擎的介绍](#) [15.7.3. 如何使用FEDERATED表](#) [15.7.4. FEDERATED存储引擎的局限性](#) FEDERATED存储引擎访问在远程数据库的表中的数据，而不是本地的表。 FEDERATED存储引擎仅在－MAX版的MySQL上可用。 要为FEDERATED引擎检查源码，请查看MySQL源码分发版的sql目录。 对于FEDERATED存储引擎，在[http://forums.mysql.com/list.php?105](http://forums.mysql.com/list.php?105)上有一个专门的论坛。 ### 15.7.1. 安装FEDERATED存储引擎 要允许这个存储引擎，当你构建MySQL时请使用--with-federated-storage-engine来**configure**。 ### 15.7.2. FEDERATED存储引擎的描述 当你创建一个FEDERATED表的时候，服务器在数据库目录创建一个表定义文件。文件由表的名字开始，并有一个.frm扩展名。无其它表被创建，因为实际的数据在一个远程数据库上。这不同于为本地表工作的存储引擎的方式。 对于本地的数据录表，数据文件是本地的。例如，如果你创建一个名为user的MyISAM表，MyISAM处理器创建一个名为users.MYD的数据文件。对本地表读，插入，删除和更新在本地数据文件里的数据的处理器和记录被以对处理器的特定格式存储。为了读记录，处理器必须把数据分解进列。为了写记录，列值必须被转换到被处理器使用的行格式并且被写进本地的数据文件。 使用MySQL FEDERATED存储引擎，没有对表的本地数据文件（比如，没有.MYD文件）。取而代之地，一个远程数据库存储那些正常地应该在表中的数据。这使得MySQL客户端API来读，删除，更新和插入数据的使用成为必要。数据取回被通过SELECT * FROM *tbl_name* SQL语句来初始化。要读这个结果，通过使用mysql_fetch_row() C API函数，行被一次取一个，然后从SELECT结果包中的列转换成FEDERATED处理器期望的格式。 基本流程如下： 1.    SQL调用被本地发布 2.    MySQL处理器API （数据以处理器格式） 3.    MySQL客户端API （数据被转换成SQL调用） 4.    远程数据库-> MySQL客户端API 5.    转换结果包（如果有的话）到处理器格式 6.    处理器 API -> 结果行或受行影响的对本地的计数 ### 15.7.3. 如何使用FEDERATED表 使用FEDERATED表的步骤是非常简单的。通常，你运行两个服务器，要么在同一个主机上，要么在不同主机上。（一个FEDERATED表使用其它被同一服务器管理的表也是可能的。虽然只有极少的点要这么做）。 首先，你必须在你想要用FEDERATED表访问的远程服务器上有一个表。假设，远程的表在FEDERATED数据库中并且被如下定义： ~~~ CREATE TABLE test_table ( ~~~ ~~~     id     int(20) NOT NULL auto_increment, ~~~ ~~~     name   varchar(32) NOT NULL default '', ~~~ ~~~     other  int(20) NOT NULL default '0', ~~~ ~~~     PRIMARY KEY  (id), ~~~ ~~~     KEY name (name), ~~~ ~~~     KEY other_key (other) ~~~ ~~~ ) ~~~ ~~~ ENGINE=MyISAM ~~~ ~~~ DEFAULT CHARSET=latin1; ~~~ ENGINE表选项可能命名任何存储引擎，该表需要不是一个MyISAM表。 接着，在本地服务器上为访问远程表创建一个FEDERATED表： ~~~ CREATE TABLE federated_table ( ~~~ ~~~     id     int(20) NOT NULL auto_increment, ~~~ ~~~     name   varchar(32) NOT NULL default '', ~~~ ~~~     other  int(20) NOT NULL default '0', ~~~ ~~~     PRIMARY KEY  (id), ~~~ ~~~     KEY name (name), ~~~ ~~~     KEY other_key (other) ~~~ ~~~ ) ~~~ ~~~ ENGINE=FEDERATED ~~~ ~~~ DEFAULT CHARSET=latin1 ~~~ ~~~ CONNECTION='mysql://root@remote_host:9306/federated/test_table'; ~~~ （**注意**: CONNECTION 替代 用在先前版本的MySQL里的COMMENT）。 除了ENGINE表选项应该是FEDERATED，并且CONNECTION表选项是给FEDERATED指明如何连接到远程服务器上的连接字符串之外，这个表的结构必须完全与远程表的结构相同。 FEDERATED引擎仅创建在已联盟数据库中的test_table.frm文件。 远程主机信息指明本地服务器要连接到的远程服务器，数据库和表信息指明哪一个远程表要被作为数据文件来用。在这个例子中。远程服务器被指定来作为远程主机在9306端口上运行，所以你要启动服务器，让它监听9306端口。 在CONNECTION选项中的连接字符串的一般形式如下： ~~~ scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name ~~~ 只有mysql在这一点被支持为*scheme*，密码和端口号时可选的。 这里有一些连接字符串的例子： ~~~ CONNECTION='mysql://username:password@hostname:port/database/tablename' ~~~ ~~~ CONNECTION='mysql://username@hostname/database/tablename' ~~~ ~~~ CONNECTION='mysql://username:password@hostname/database/tablename' ~~~ 为指定连接字符串使用CONNECTION是非可选，并且在将来可能会改变。当你使用FEDERATED表的时候，要记得这个，因为这意味着当将来发生那种改变之时，可能被要求。 因为任何被用的密码作为纯文本被存在连接字符串中，它可以被任何使对FEDERATED表使用SHOW CREATE TABLE或SHOW TABLE STATUS的用户，或者在INFORMATION_SCHEMA数据库中查询TABLES表的用户看见。 对于FEDERATED存储引擎，在[http://forums.mysql.com/list.php?105](http://forums.mysql.com/list.php?105)上有一个专门的论坛。 ### 15.7.4. FEDERATED存储引擎的局限性 FEDERATED支持及不支持的如下： ·        在第一个版本中，远程服务器必须是一个MySQL服务器。FEDERATED对其它数据库引擎的支持可能会在将来被添加。 ·        FEDERATED表指向的远程表在你通过FEDERATED表访问它之前必须存在。 ·        一个FEDERATED表指向另一个FEDERATED表是可能的，但是你必须小心不要创建一个循环。 ·        没有对事务的支持。 ·        如果远程表已经改变，对FEDERATED引擎而言是没有办法知道的。这个的原因是因为这个表必须象数据文件一样工作，除了数据库其它任何都不会被写入。如果有任何对远程数据库的改变，本地表中数据的完整性可能会被破坏。 ·        FEDERATED存储引擎支持SELECT, INSERT, UPDATE, DELETE和索引。它不支持ALTER TABLE, DROP TABLE或任何其它的数据定义语言语句。当前的实现不使用预先准备好的语句。 ·        执行使用SELECT, INSERT, UPDATE和DELETE，但不用HANDLER。 ·        FEDERATED表不能对查询缓存不起作用。 这些限制中的一些在FEDERATED处理机的将来版本可能被消除。 ### 15.8. ARCHIVE存储引擎 ARCHIVE存储引擎被用来以非常小的覆盖区存储大量无索引数据。  要允许这个存储引擎，在建立MySQL之时使用--with-archive-storage-engine选项来**configure**。如果这个存储引擎可带这个语句使用，你可以看见： ~~~ mysql> SHOW VARIABLES LIKE 'have_archive'; ~~~ 当年创建一个ARCHIVE表，服务器在数据库目录创建一个表定义文件。文件由表的名字开始，并由一个.frm的扩展名。存储引擎创建其它文件，所有都有由表名字开头的名字。数据和元数据文件有扩展名.ARZ和.ARM。一个.ARN文件可能在优化操作中出现。 ARCHIVE引擎仅支持INSERT和SELEC（无删除，替换或更新）。它支持ORDER BY操作，BLOB 域，以及基本地所有数据类型，除了几何数据类型（请参阅[19.4.1节，“MySQL 空间数据类型”](# "19.4.1. MySQL Spatial Data Types")）。ARCHIVE引擎使用行级锁定。 **存储:** 当记录被插入时，它们被压缩。ARCHIVE引擎使用[zlib](http://www.zlib.net/)无损数据压缩。OPTIMIZE TABLE的使用可以分析表，并把它打包为更小的格式（ 使用OPTIMIZE TABLE的原因，往下看）。引擎页支持CHECK TABLE。有数种被使用的插入类型： ·        直接插入INSERT之时把一行推仅压缩缓冲，并且缓冲在它需要的时候刷新。到缓冲的插入被锁定保护。一个SELECT强制一个刷新发生，除非进来的唯一插入是INSERT DELAYED（那些刷新如同需要一样）。请参阅[13.2.4.2节，“INSERT DELAYED 语法”](# "13.2.4.2. INSERT DELAYED Syntax")。 ·        块插入旨在它完成后可见，除非其它插入在同一时间发生，在这种情况下，块插入可以被部分看见。一个SELECT不会导致一个块插入的刷新，除非SELECT在被装载时发生一个正常插入。 **取回：** 在取回时，记录根据需要被解压缩，没有行缓存。一个SELECT操作执行完全表格扫描：当一个SELECT发生之时，它找出当前有多少行可用，并读行的数量。SELECT被当作持续读来执行。注意，许多SELECT语句在插入过程中会损坏压缩，除非块插入或者延迟的插入被使用。要修复任何已发生压缩问题，你可以总是做OPTIMIZE TABLE（也支持REPAIR TABLE）。被SHOW TABLE STATUS报告的行数总是正确的。请参阅[13.5.2.6节，“REPAIR TABLE语法”](# "13.5.2.6. REPAIR TABLE Syntax")，[13.5.2.5节，“OPTIMIZE TABLE语法”](# "13.5.2.5. OPTIMIZE TABLE Syntax")，[13.5.4.18节，“SHOW TABLE STATUS 语法”](# "13.5.4.18. SHOW TABLE STATUS Syntax")。 对于ARCHIVE存储引擎，在[http://forums.mysql.com/list.php?112](http://forums.mysql.com/list.php?112) 上有专门论坛。 ### 15.9. CSV存储引擎 CSV存储引擎使用逗号分隔值格式的文本文件存储数据。 要允许使用这个存储引擎，当你建立MySQL之时，使用--with-csv-storage-engine选项来**configure**。 当你创建一个CSV表之时，服务器在数据库目录创建一个表定义文件。文件由表的名字开始，并且由一个.frm的扩展名。存储引擎也创建一个数据文件。它的名字由表的名字开始，并且有一个.CSV的扩展名。数据文件是无格式文本文件。当你把数据存储进表时，存储引擎用CSV格式把它存进数据文件。 ~~~ mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV; ~~~ ~~~ Query OK, 0 rows affected (0.12 sec) ~~~ ~~~   ~~~ ~~~ mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); ~~~ ~~~ Query OK, 2 rows affected (0.00 sec) ~~~ ~~~ Records: 2  Duplicates: 0  Warnings: 0 ~~~ ~~~   ~~~ ~~~ mysql> SELECT * FROM test; ~~~ ~~~ +------+------------+ ~~~ ~~~ | i    | c          | ~~~ ~~~ +------+------------+ ~~~ ~~~ |    1 | record one | ~~~ ~~~ |    2 | record two | ~~~ ~~~ +------+------------+ ~~~ ~~~ 2 rows in set (0.00 sec) ~~~ 如果你检查在执行前述语句所创建的数据库目录里的test.CSV文件，它的内容应该看起来象如下所示： ~~~ "1","record one" ~~~ ~~~ "2","record two" ~~~ CSV存储引擎不支持索引。 ### 15.10. BLACKHOLE存储引擎 BLACKHOLE存储引擎就像“黑洞”一样，它接收数据但丢弃它而不是存储它。取回总是返回空集： ~~~ mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; ~~~ ~~~ Query OK, 0 rows affected (0.03 sec) ~~~ ~~~   ~~~ ~~~ mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); ~~~ ~~~ Query OK, 2 rows affected (0.00 sec) ~~~ ~~~ Records: 2  Duplicates: 0  Warnings: 0 ~~~ ~~~   ~~~ ~~~ mysql> SELECT * FROM test; ~~~ ~~~ Empty set (0.00 sec) ~~~ 当你创建一个BLACKHOLE表的时候，服务器在数据库目录创建一个表定义文件。文件用表的名字开头，并且有一个.frm扩展名。没有其它文件关联到这个表格。 BLACKHOLE存储引擎支持所有种类的索引。 要允许这个存储引擎，在你建立MySQL之时使用--with-blackhole-storage-engine选项来**configure**。BLACKHOLE存储引擎在MySQ供应的服务器二进制版里可以找到；通过查看SHOW ENGINES或SHOW VARIABLES LIKE 'have%'的输出，你可以确定你的版本是否支持这个引擎。 到BLACKHOLE表的插入不存储任何数据，但如果二进制日志被允许，SQL语句被写入日志（并被复制到从服务器）。这可以有用帮助地作为重复器或过滤器机制。例如，假设你的应用需要从服务器侧的过滤规则，但传输所有二进制日志数据到从服务器首先导致过多交通的结果。在这种情况下，在主服务器主机上建立一个伪从服务器进程，它的存储引擎是BLACKHOLE，描述如下：  主服务器写它的二进制日志。伪mysqld进程作为从服务器，应用期望的replicate-do和replicate-ignore规则的合并，并且写一个新的，被过滤的属于它自己的二进制日志 。（请参阅[6.8节，“复制启动选项”](# "6.8. Replication Startup Options")）。这个已过滤日志被提供给从服务器。 既然伪进程不确实地存储任何数据，只有很小的由在复制主服务器主机上额外的mysqld进程招致的处理开支。这个类型的建立可以用额外复制从服务器来重复。 其它可能对BLACKHOLE存储引擎的使用包括： ·        转储文件语法的验证。 ·        来自二进制日志记录的开销测量，通过比较允许二进制日志功能的BLACKHOLE的性能与禁止二进制日志功能的BLACKHOLE的性能。 ·        因为BLACKHOLE本质上是一个“no-op” 存储引擎，它可能被用来查找与存储引擎自身不相关的性能瓶颈。 这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问[dev.mysql.com](http://dev.mysql.com/doc/mysql/en)。 原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。