InnoDB Table and Index Structures 的中文翻译和英文原文

InnoDB 中文参考手册 --- 犬犬(心帆)翻译 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 排序的记录顺序也就是插入时的物理顺序。

（注：关于innodb的聚簇索引的说明，下面这段话应该比manul更权威：Data rows are stored in the B-tree leaf nodes of a clustered index。 B-tree is organized by primary key or non-null unique key of table, if defined;
else, an internal column with 6-byte ROW_ID is added. 这段话来www.innodb.com出品的一个ppt）

通过聚簇索引访问一个记录行是非常快的，因为记录行数据与引导我们查找到它的索引在同一个页面上。 在大多数的数据库系统中记录行数据与索引记录通常并不是放在同一个页面上的。如果一个表太大了，那么聚簇索引体系通常比传统的方式更能减少磁盘 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 的值时不至产生索引页面的磁盘碎片

 

原文：（还是看原文爽，不但好理解，而且还能获取到更多的信息）

from:mysql文档http://dev.mysql.com/doc/refman/4.1/en/innodb-table-and-index.html

MySQL stores its data dictionary information for tables in .frm files in database directories. This is true for all MySQL storage engines, but every InnoDB table also has its own entry in the InnoDB internal data dictionary inside the tablespace. When MySQL drops a table or a database, it has to delete one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary. Consequently, you cannot move InnoDB tables between databases simply by moving the .frm files.

 

Every InnoDB table has a special index called the clustered index where the data for the rows is stored:

• If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

• If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic（合成的） column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous(有利，用益) to have a short primary key.

  

All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16KB. When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records.

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. If the fill factor of an index page drops below 1/2, InnoDB tries to contract the index tree to free the page.