InnoDB磁盘架构之索引

1 问题背景

前面简单了解了InnoDB磁盘架构之表， 今天研究索引。

2 前言

如果不了解二级索引，可以先看看笔者这篇比较底层的索引研究，MySQL索引底层分析，方便理解下文的内容。

3 回顾

InnoDB架构如下所示：

4 索引

4.1 聚集索引和二级索引

详情见Clustered and Secondary Indexes

原文
Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, it is important to understand how InnoDB uses the clustered index to optimize the common lookup and DML operations.

每一个InnoDB表都有一个特别的索引称为聚集索引，该索引存储行数据。通常地，聚集索引与主键同义。为了达到更好的查询、插入以及其他数据库操作的性能，理解InnoDB是如何使用聚集索引来优惠常见的查找以及DML操作是非常重要的。

原文

• When you define a PRIMARY KEY on a table, InnoDB uses it as the clustered index. A primary key should be defined for each table. If there is no logical unique and non-null column or set of columns to use a the primary key, add an auto-increment column. Auto-increment column values are unique and are added automatically as new rows are inserted.

• If you do not define a PRIMARY KEY for a table, InnoDB uses the first UNIQUE index with all key columns defined as NOT NULL as the clustered index.

• If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB assigns. 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 order of insertion.

• 当你在一张表定义了一个主键，InnoDB使用它作为聚集索引。每张表都应该定义一个主键。如果没有逻辑唯一并且非空的列或者作为逐渐的列集合，请新增一个自增的列。自增的列值是唯一的并且能在插入新行时自动增加。
• 如果你没有在一张表中定义一个主键，InnoDB使用第一个唯一索引、并将所有键列定义为非空的聚集索引。
• 如果表没有索引PRIMARY KEY或没有合适的 UNIQUE索引，则InnoDB 生成以GEN_CLUST_INDEX包含行 ID 值的合成列命名的隐藏聚集索引 。行按InnoDB分配的行 ID 排序。行 ID 是一个 6 字节的字段，随着插入新行而单调增加。因此，按行 ID 排序的行在物理上是按插入顺序排列的。

聚集索引如何加快查询速度?

原文
Accessing a row through the clustered index is fast because the index search leads directly to the page that contains the row data. 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.

通过聚集索引访问一行很快，因为索引搜索直接指向包含行数据的页面。如果表很大，与使用与索引记录不同的页面存储行数据的存储组织相比，聚簇索引体系结构通常可以节省磁盘 I/O 操作。

二级索引与聚集索引的关系

原文
Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

聚集索引以外的索引称为二级索引。在 中InnoDB，二级索引中的每条记录都包含该行的主键列，以及为二级索引指定的列。 InnoDB使用此主键值搜索聚集索引中的行。

原文
If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

如果主键很长，二级索引会占用更多的空间，所以主键短是有利的。