MySQL 5.1参考手册 :: 15. 存储引擎和表类型(6)

15.2.13.1. 索引的物理结构

15.2.13.2. 缓冲插入

15.2.13.3. 适应的哈希索引

15.2.13.4. 物理记录结构

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

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

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

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

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

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

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

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

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

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

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

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

15.2.14.1. 磁盘I/O

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

15.2.14.3.文件空间管理

15.2.14.4. 整理表碎片

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

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

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

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

你也可以使用原始磁盘分区作为表空间数据文件。通过使用原始磁盘，你可以在Windows和一些Unix系统上执行non-buffered I/O 而无须文件系统开支，这样可以改善性能

当你创建一个新数据文件之时，你必须在innodb_data_file_path里紧接着数据文件尺寸之后放置关键字newraw。分区必须至少和你指定的尺寸一样大，注意，在InnoDB中，1MB是1024x1024字节， 但是在磁盘规格中，1MB通常意为1,000,000字节。

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

下次你启动服务器之时，InnoDB注意到关键字newraw并初始化新分区。但是仍然并不创建或改变任何InnoDB表。另外，当你重启服务器之时，InnoDB重新初始化分区，你的改变会丢失。（从3.23.44启动，作为一个安全措施， 当用newraw指定任何分区之时，InnoDB阻止用户修改数据）。

InnoDB初始化新分区之后，停止服务器，在对行的数据文件规格中改变newraw：

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

然后重启动服务器，并且InnoDB允许做改变。

在Windows上，你可以象这样分配磁盘分区为一个数据文件：

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Gnewraw

对于访问物理驱动器，//./ 相当于Windows语法的\\.\ 。

当你使用原始磁盘分区之时，确信它们有允许被用来运行MySQL服务器的帐号读和写访问的许可。

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

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

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

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

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

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

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

如果有随机插入到表的索引或从表的索引随机删除，索引可能变成碎片的。碎片意思是索引页在磁盘上的物理排序并不接近页上记录的索引排序，或者在分配给索引的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.1. InnoDB错误代码

15.2.15.2. 操作系统错误代码

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

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

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

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

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

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

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

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

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

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

·         一个表不能包含超过1000列。

·         内部最大键长度是3500字节，但MySQL自己限制这个到1024字节。

·         除了VARCHAR, BLOB和TEXT列，最大行长度稍微小于数据库页的一半。即，最大行长度大约8000字节。LONGBLOB和LONGTEXT列必须小于4GB, 总的行长度，页包括BLOB和TEXT列，必须小于4GB。InnoDB在行中存储VARCHAR，BLOB或TEXT列的前768字节，余下的存储的分散的页面中。

·         虽然InnoDB内部地支持行尺寸大于65535，你不能定义一个包含VARCHAR列的，合并尺寸大于65535的行。

·                mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),

·                    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

·                    -> f VARCHAR(10000), g VARCHAR(10000));

·                ERROR 1118 (42000): Row size too large. The maximum row size for the

·                used table type, not counting BLOBs, is 65535. You have to change some

·                columns to TEXT or BLOBs

·         在一些更老的操作系统上，数据文件必须小于2GB。

·         InnoDB日志文件的合并尺寸必须小于4GB。

·         最小的表空间尺寸是10MB。最大的表空间尺寸是4,000,000,000个数据库页（64TB）。这也是一个表的最大尺寸。

·         InnoDB表不支持FULLTEXT索引。

·         ANALYZE TABLE 通过对每个索引树做八次随机深入并相应地更新索引集估值，这样来计数集。注意，因为这是仅有的估值，反复运行ANALYZE TABLE会产生不同数。这使得 ANALYZE TABLE 在 InnoDB 表上很快，不是百分百准确，因为它没有考虑所有的行。

MySQL 不仅在汇合优化中使用索引集估值。如果一些汇合没有以正确的方式优化，你可以试一下 ANALYZE TABLE 。很少有情况，ANALYZE TABLE 没有产生对你特定的表足够好的值，你可以使用 FORCE INDEX 在你查询中来强制使用特定索引，或者设置 max_seeks_for_key 来确保MySQL在表扫描之上运行索引查找。请参阅5.3.3节，“服务器系统变量”。请参阅A.6节，“优化器相关的问题”。

·         在Windows上，InnoDB总是内部地用小写字母存储数据库和表名字。要把数据库以二进制形式从Unix 移到Windows，或者从Windows移到Unix，你应该让所有数据库和表的名字都是小写。

·         警告: 不要在MySQL数据库内的把MySQL系统表从MyISAM转为InnoDB表！这是一个不被支持的操作。如果你这么做了，MySQL直到你从备份恢复旧系统表，或用mysql_install_db脚本重建系统表才重启动。

·         InnoDB在表内不保留行的内部计数。（因为多版本化，这可能确实有些复杂）。要处理一个SELECT COUNT(*) FROM t语句，InnoDB必须扫描表的一个索引，如果这个索引不在缓冲池中，扫描需要花一些时间。要获得快速计数，你不得不使用一个自己创建的计数器表，并让你的应用按照它做的插入和删除来更新它。如果你的表格不经常改变，使用MySQL查询缓存时一个好的解决方案。如果大致的行数就足够了，则SHOW TABLE STATUS也可被使用。请参阅15.2.11节，“InnoDB性能调节提示”。

·         对于AUTO_INCREMENT列，你必须总是为表定义一个索引，并且索引必须包含AUTO_INCREMENT列。在MyISAM表中，AUTO_INCREMENT列可能时多列索引的一部分。

·         当你重启MySQL服务器之时，InnoDB可能为一个AUTO_INCREMENT列重使用一个旧值（即，一个被赋给一个老的已回滚的事务的值）。

·         当一个AUTO_INCREMENT列用完值，InnoDB限制一个BIGINT到－9223372036854775808以及BIGINT UNSIGNED到1。尽管如此，BIGINT值有由64位，所以注意到，如果你要一秒输入100万个行，在BIGINT到达它上限之前，可能还需要将近30万年。用所有其它整数类型列，产生一个重复键错误。这类似于MyISAM如何工作的，因为它主要是一般MySQL行为，并不特别关于任何存储引擎。

·         DELETE FROM tbl_name不重新生成表，但取而代之地删除所有行，一个接一个地删除。

·         TRUNCATE tbl_name为InnoDB而被映射到DELETE FROM tbl_name 并且不重置AUTO_INCREMENT计数器。

·         SHOW TABLE STATUS不能给出关于InnoDB表准确的统计数据，除了被表保留的物理尺寸。行计数仅是在SQL优化中粗略的估计。

·         在MySQL 5.1中，如果innodb_table_locks=1(1是默认值） MySQL LOCK TABLES操作在每一个表上获取两个锁定。除了在MySQL层的表锁定，它也获得一个InnoDB表锁定。旧版的MySQL不获取InnoDB表锁定，旧行为可以通过设置innodb_table_locks=0 来选择。如果没有InnoDB表锁定被获得，即使表的一些记录被其它事务锁定，LOCK TABLES完成。 

·         所有被一个事务持有的InnoDB锁定在该事务被提交或中止之时被释放。因此在AUTOCOMMIT=1模式，在InnoDB表上调用是没有太多意义的，因为被需求的InnoDB表锁定可能会被立即释放。

·         有时，在事务的过程中锁定更多的表可能是有用的。不幸地，MySQL中的LOCK TABLES执行一个暗地的COMMIT和UNLOCK TABLES。LOCK TABLES的一个InnoDB变量已经被计划， 该计划在事务的中间被执行。

·         为建立复制从服务器的LOAD TABLE FROM MASTER语句对InnoDB表不起作用。一个工作区在主服务器上更换表为MyISAM的，然后做负载，之后更换主服务器表回到InnoDB中。

·         在InnoDB中默认数据库页的大小是16KB。通过编译代码，你可以在8KB到64KB之间来设置这个值。你不得不更新在univ.i源文件中的UNIV_PAGE_SIZE和UNIV_PAGE_SIZE_SHIFT的值。

·         在MySQL 5.1中，触发器不被级联的外键行为激活。

15.2.17.1. InnoDB数据词典操作的错误诊断和排除

·         一个总的规则是，当一个操作失败或这你怀疑有一个缺陷。你应该查看MySQL服务器的错误日志，该日志典型地有一个有些象hostname.err这样的名字，或者在Windows上是mysql.err这样的。

·         故障诊断与排除之时，通常最好从命令提示符运行MySQL服务器，而不是从mysqld_safe包运行，或不作为一个Windows服务来运行。你可以看mysqld打印到控制台上的内容，因此更好掌握发生了什么。在Windows上，你必须用--console选项启动服务器将输出定向到控制台窗口

·         使用InnoDB Monitors获取关于某问题的信息。如果问题是性能相关的，或者你的服务器看起来被挂起，你应该使用innodb_monitor来打印InnoDB内部状态的信息，如果问题是关于锁定，则使用innodb_lock_monitor。如果问题是在表的创建或其它数据词典操作，使用innodb_table_monitor来打印InnoDB内部数据词典的内容。

·         如果你猜测一个表被破坏，则在该表上运行CHECK TABLE。