MySQL索引_聚集索引 - 墨天轮文档

MySQL索引_聚集索引

鸿惊九天

132

3页

3次

2022-09-09

免费下载

资源由 www.eimhe.com 美河学习在线收集分享

MySQL 索引聚集索引

如果你想了解 MySQL 索引查询优化，你首先应该对 MySQL 数据组织结构、B-Tree 索引、聚集索引，次要索

引有一定的了解，才能够更好地理解 MySQL 查询优化行为。这里主要探讨 MySQL InnoDB 的聚集索引。

InnoDB 数据存储结构

1.MySQL 将所有数据都逻辑地存放在 ib_data1 文件中，我们称之为表空间。当然，你也可以一个表对应一

个物理文件,将 innodb_file_per_table 设置成 ON 即可。

2.表空间又划为成段，有数据段(leaf node segment)，索引段(none-leaf node segment)，回滚段(rollback

segment)。表空间由这些段和页组成，比如 32 页碎片页。

3.每段又划为成区，InnoDB 每次最多可以申请 4 个区，即 4M 的存储空间。

4.每个区又划为成页，一个区划分成 64 页，每个页的大小是 16KB，大小不能够改，这也固定了一个区的大

小为 4M。页是 MySQL 操作的最小逻辑单位。

5.InnoDB 是面向行的，这就意味着数据行存放在页中，每页最多能记录 7992 行数据。

6.MySQL 定义了不同作用的页类型，比如 B-Tree Page, Undo Log Page 等，我们最关心的是 B-Tree Page(数据

页)。实际数据就以这样的页逻辑实体存在于表空间,总是以 B+树结构索引组织的。

7.换句话就说，实际数据一行一行地存放在 B-Tree 页中，这些页都放在数据段 leaf node segment 中。B-Tree

Page 是 B+树的叶子节点。

8.一个 B-Tree 树，由 7 部分构成

8-1.File Header，这里记录了页在表空间的一些信息，比如上一页，下一页，属于哪个表空间等等

8-2.Page Header, 这里记录了页本身的一些存储信息。比如第一个记录的位置，记录数，最后插入记录行的

位置，该页的索引 ID 等等

8-3.Infimum & Supermum Records, MySQL 虚拟的二个行记录，用来界定记录的边界。分别代表此页中任何

pk 值还小的值和任何 pk 值还大的值。

8-4.user records, 实际存储的行记录。

8-5.free space,空闲空间，同样是链表结构。当一个数据记录删除后，就会加入到空闲链表中

8-6.page directory, 存放了记录的相对位置。注:聚集索引本身找不到具体的一条记录。而是通过聚集索引

找到该记录所在的页，然后再通过 Page Directory 进行二分查找找到具体数据。

8-7.File Trailer, MySQL InnoDB 利用它来保证页完整地写入磁盘。

B-Tree 索引查找数据

资源由 www.eimhe.com 美河学习在线收集分享

B+树，由 B 树和索引顺序访问方法演化面来的一种数据结构，较为复杂，常用于磁盘等存储设备的一种平

衡查找树(所谓平衡，请查阅二叉树和平衡二叉树)。在 B+树中,所有记录节点都按键值的大小顺序存放在同

一层叶子节点中，各叶节子节点指针进行链接，每个叶子节点到达根节点的距离都是一样的。下图分别演

示了 G 和 H 记录查询过程。

MySQL InnoDB 一般按照每张表的主键构造一颗 B+树，存放在表空间，其中整张表的行记录就是这颗 B+树

的叶子节点，存放在表空间的数据段(leaf node segment)。这就意味着数据行在表空间存储是有序的。MySQL

通过 B+树查找算法能够加速数据的访问，避免扫描整个表，大大减少了 I/O 逻辑操作。MySQL InnoDB 查找

某行数据的时候，通常先从 root 节点，找到子节点，直到找到叶子节点。叶子节点就是数据页，即(B-Tree

类型页)，然后通过该页的 Page Directory 进行二分查找，找到数据行(上文有提到过，MySQL 是不能通过 B-Tree

索引找到用户数据行)。

执行 select * from user where uid = 92 这样的一句查询。MySQL 依照主键 uid 建立的 B+树索引，MySQL 从这

颗 B+树的根节点查找，一直找到叶子页的上一层节点，比较键值 92>91(右边)最终找到数据叶，在数据叶

中找到数据行。当然，也有可能找不到数据行(数据行根本不存在)。

InnoDB 聚集索引

MySQL 有没有支持聚集索引，取决于采用哪种存储引擎。MySQL InnoDB 一定会建立聚集索引，所谓聚集，

指实际数据行和相关的键值保存在一块(如上图)，这也决定了一个表只能有一个聚集索引，即 MySQL 不会

一次把数据行保存在二个地方。InnoDB 通常根据主键值(primary key)进行聚集，但是当一个表没有 PK 怎么

办?InnoDB 选取聚集索引参照列的顺序是

1.如果声声明了主键(primary key)，则这个列会被做为聚集索引

2.如果没有声明主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚集索引

3.上面二个条件都不满足，InnoDB 会自己产生一个虚拟的聚集索引。

资源由 www.eimhe.com 美河学习在线收集分享

MySQL 索引聚集索引

如果你想了解 MySQL 索引查询优化，你首先应该对 MySQL 数据组织结构、B-Tree 索引、聚集索引，次要索

引有一定的了解，才能够更好地理解 MySQL 查询优化行为。这里主要探讨 MySQL InnoDB 的聚集索引。

InnoDB 数据存储结构

1.MySQL 将所有数据都逻辑地存放在 ib_data1 文件中，我们称之为表空间。当然，你也可以一个表对应一

个物理文件,将 innodb_file_per_table 设置成 ON 即可。

2.表空间又划为成段，有数据段(leaf node segment)，索引段(none-leaf node segment)，回滚段(rollback

segment)。表空间由这些段和页组成，比如 32 页碎片页。

3.每段又划为成区，InnoDB 每次最多可以申请 4 个区，即 4M 的存储空间。

4.每个区又划为成页，一个区划分成 64 页，每个页的大小是 16KB，大小不能够改，这也固定了一个区的大

小为 4M。页是 MySQL 操作的最小逻辑单位。

5.InnoDB 是面向行的，这就意味着数据行存放在页中，每页最多能记录 7992 行数据。

6.MySQL 定义了不同作用的页类型，比如 B-Tree Page, Undo Log Page 等，我们最关心的是 B-Tree Page(数据

页)。实际数据就以这样的页逻辑实体存在于表空间,总是以 B+树结构索引组织的。

7.换句话就说，实际数据一行一行地存放在 B-Tree 页中，这些页都放在数据段 leaf node segment 中。B-Tree

Page 是 B+树的叶子节点。

8.一个 B-Tree 树，由 7 部分构成

8-1.File Header，这里记录了页在表空间的一些信息，比如上一页，下一页，属于哪个表空间等等

8-2.Page Header, 这里记录了页本身的一些存储信息。比如第一个记录的位置，记录数，最后插入记录行的

位置，该页的索引 ID 等等

8-3.Infimum & Supermum Records, MySQL 虚拟的二个行记录，用来界定记录的边界。分别代表此页中任何

pk 值还小的值和任何 pk 值还大的值。

8-4.user records, 实际存储的行记录。

8-5.free space,空闲空间，同样是链表结构。当一个数据记录删除后，就会加入到空闲链表中

8-6.page directory, 存放了记录的相对位置。注:聚集索引本身找不到具体的一条记录。而是通过聚集索引

找到该记录所在的页，然后再通过 Page Directory 进行二分查找找到具体数据。

8-7.File Trailer, MySQL InnoDB 利用它来保证页完整地写入磁盘。

B-Tree 索引查找数据

资源由 www.eimhe.com 美河学习在线收集分享

B+树，由 B 树和索引顺序访问方法演化面来的一种数据结构，较为复杂，常用于磁盘等存储设备的一种平

衡查找树(所谓平衡，请查阅二叉树和平衡二叉树)。在 B+树中,所有记录节点都按键值的大小顺序存放在同

一层叶子节点中，各叶节子节点指针进行链接，每个叶子节点到达根节点的距离都是一样的。下图分别演

示了 G 和 H 记录查询过程。

MySQL InnoDB 一般按照每张表的主键构造一颗 B+树，存放在表空间，其中整张表的行记录就是这颗 B+树

的叶子节点，存放在表空间的数据段(leaf node segment)。这就意味着数据行在表空间存储是有序的。MySQL

通过 B+树查找算法能够加速数据的访问，避免扫描整个表，大大减少了 I/O 逻辑操作。MySQL InnoDB 查找

某行数据的时候，通常先从 root 节点，找到子节点，直到找到叶子节点。叶子节点就是数据页，即(B-Tree

类型页)，然后通过该页的 Page Directory 进行二分查找，找到数据行(上文有提到过，MySQL 是不能通过 B-Tree

索引找到用户数据行)。

执行 select * from user where uid = 92 这样的一句查询。MySQL 依照主键 uid 建立的 B+树索引，MySQL 从这

颗 B+树的根节点查找，一直找到叶子页的上一层节点，比较键值 92>91(右边)最终找到数据叶，在数据叶

中找到数据行。当然，也有可能找不到数据行(数据行根本不存在)。

InnoDB 聚集索引

MySQL 有没有支持聚集索引，取决于采用哪种存储引擎。MySQL InnoDB 一定会建立聚集索引，所谓聚集，

指实际数据行和相关的键值保存在一块(如上图)，这也决定了一个表只能有一个聚集索引，即 MySQL 不会

一次把数据行保存在二个地方。InnoDB 通常根据主键值(primary key)进行聚集，但是当一个表没有 PK 怎么

办?InnoDB 选取聚集索引参照列的顺序是

1.如果声声明了主键(primary key)，则这个列会被做为聚集索引

2.如果没有声明主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚集索引

3.上面二个条件都不满足，InnoDB 会自己产生一个虚拟的聚集索引。

of 3

免费下载

mysql

关注

评论