InnoDB 中文参考手册 --- 11 表和索引结构

11 表和索引结构

MySQL 在数据库目录下的 .frm 文件中存储它的数据字典信息。但是每个 InnoDB 类型表也同样在 InnoDB 表空间内的内部的数据字典中存在它自己的进入点。当 MySQL 移除(drop) 一个表或一个数据库时，它将同时删除 .frm 文件，以及在 InnoDB 的数据字典中相对应的进入点。这就是为什么不能通过简单的删除 .frm 文件为移除数据库中的 InnoDB 类型表的原因，以及为什么在 MySQL 版本 <= 3.23.43 的版本中，DROP DATABASE 不能用于 InnoDB 表的原因。

每一个 InnoDB 表都有一个被称为聚簇索引的特殊索引用于保存记录行信息。如果一个表定义一个 PRIMARY KEY ，那么主键的索引就是聚簇索引。

如果表没有定义一个 PRIMARY KEY ，MySQL 将选出第一个 NOT NULL 字段的 UNIQUE 键做为主键，InnoDB 也将用这个键的索引做为聚簇索引。如果表中没有这样的键，InnoDB 将在内部产生一个聚簇索引，它是由按 InnoDB 分配给它们的 row id 顺序排序的记录行组成。这个 row id 是一个单调地增加并插入新行的 6-byte 字段。因而由 row id 排序的记录顺序也就是插入时的物理顺序。

通过聚簇索引访问一个记录行是非常快的，因为记录行数据与引导我们查找到它的索引在同一个页面上。 在大多数的数据库系统中记录行数据与索引记录通常并不是放在同一个页面上的。如果一个表太大了，那么聚簇索引体系通常比传统的方式更能减少磁盘 I/O 。

在非-聚簇索引(non-clustered indexes)中的记录 (我们通常称它为辅助索引secondary indexes)，在 InnoDB 中会为这行包含主键值。InnoDB 将使用这个主键值来在聚簇索引中查找这行。注意如果主键太长，那么辅助索引将会占用更多的空间。

InnoDB 在比较不同长度的 CHAR 和 VARCHAR 时，较短字串的多余长度将被空格(spaces)填充。

11.1 索引的物理结构

All indexes in InnoDB 中所有的索引是索引记录存放在树的叶页面(leaf pages)上的 B-trees。一个索引页面的大小默认为 16 kB。当新的记录被插入时，InnoDB 将试图为将来的插入与更新索引记录保留页面的 1 / 16 空余。

如果索引记录以一个连续的 (升序或降序) 被插入，那么索引页面的将会被使用约 15/16 。如果以一个随机的顺序插入，那么页面大约使用了 1/2 - 15/16 。如果一个索引页面被撤销(drop)地低于 1/2，那么 InnoDB 将缩短索引树并释放页面空间。

11.2 插入缓冲

主键是一个唯一标识符，新的记录以主键的升序被插入，这在数据库系统中是一个普遍的情形。因而在聚簇索引内插入的值不需要在硬盘上随意读取。

另一方面，辅助索引通常是非唯一的(non-unique)，插入在辅助索引中是相当随意的顺序。如果在 InnoDB 中不使用一个特殊的机制这将会引起大量随机的磁盘 I/O。

如果一个索引记录被插入到一个非唯一的辅助索引中去，InnoDB 将检查辅助索引页面是否已在缓冲池(buffer pool)中。在这种情形下，InnoDB 直接地将它插入到索引页面中去。但是，如果在缓冲池中没有发现索引页面，InnDB 将索引记录插入到一个特殊的插入缓冲结构中去。插入缓冲被控制地如些小以至于可以完全放在缓冲池中， 因而插入速度很快。

插入缓冲会定时地归并到数据库中的辅助索引树中去。为了减少磁盘 I/O，通常将同一个页面上的几个插入同时归并到索引树上。插入缓冲可以提高向一个表中插入速度 15倍。

11.3 适应性的散列索引 (Adaptive hash indexes)

如果一个数据库几乎占满了所有主同存，那么在其上运行查询的一个快捷之路就是使用散列索引(hash indexes)。InnoDB 有一个用于监视在表定义的索引上的索引搜索动作的自动调整结构，如果 InnoDB 发现一个散列索引对查询有益，那么它将自动地建造这个散列索引。

但是要注意地就是散列索引通常是基于表中已存在的 B-tree 创建的。InnoDB 可能通过 B-tree 中定义的任何长度的键的前缀来构建散列索引，这依赖于 InnoDB 观察 B-tree 上索引的模式。一个散列索引可以是部分的：它在缓冲池中并不需要有整个 B-tree 索引的高速缓冲。InnoDB 按照需要来为经常访问的索引页面构建散列索引。

在理论上，通过这个适应性的散列索引机制，InnpDB 使它自己更适合于大的主存(ample main memory)，更接近于主存储器数据库系统体系(the architecture of main memory databases)。

11.4 记录的物理结构

• InnoDB 中每个索引记录都包含一个 6 字节的头。这个头部用于联连相连贯的记录，也同样用于行锁定。
• 聚簇索引中的记录包含了所有用户定义的字段。另外，有一个 6-byte 字段用于记录事务 id(transaction id)和一个 7-byte 字段用于行指针(roll pointer)。
• 在一个表中，如果用户没有定义一个主键，那么每个聚簇索引记录包含一个 6-byte 的行 id 字段(row id field)。
• 每个辅助索引记录包含为聚簇索引键定义的所有字段。
• 一个索引包含着一个指向对应字段记录的指针。如果一个记录的所有字段总长度< 128 bytes，那么这个指针为 1 byte，否则为 2 bytes。
• InnoDB 在内部也是以一个固定长度来存储定长的字符型字段，比如 CHAR(10)。对于 VARCHAR 型字段，InnoDB 将截去结尾的空间。注意，MySQL 可能内部地将 CHAR 转换为 VARCHAR。查看 MySQL 用户手册有关“列规约的默认地改变”( 'Silent column specification changes')。
• 如果存储一个变长的字段，一个 SQL NULL 为一个 0 bytes 被存储，但是如果是一个定长字段将以固定的长度被存储。以相应的 NULLs 存储定长的空间的目的地就在于在将 NULL 字段值更新为 非 NULL 的值时不至产生索引页面的磁盘碎片。