MySql索引——索引的结构2-创新互联

1、常见索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集 索引称为二级索引或者辅助索引。一般来说，以主键建立的索引是聚簇索引，以非主键构建的索引叫非聚簇索引(二级索引)。

颍东ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18980820575（备注：SSL证书合作）期待与您的合作！1、聚簇索引

【关键词】主键、叶子节点即完整用户记录，存储引擎自动创建、一个表中只有一个聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，（所有用户记录都存在了叶子节点上），也就是所谓的索引即数据，数据即索引。

在InnoDB 里的ibd文件，就是数据和索引都包含在内的。

而MyISAM里 myd和myi 就是数据和索引分开存放的。

特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
• 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
• 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。

（同一层级的页，页内记录单链表连接，页与页之间是双链表，跨层级(目录项页，用户记录页等)以树结构存储）

2. B+树的 叶子节点 存储的是完整的用户记录。 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为 聚簇索引，所有完整的用户记录都存放在这个聚簇索引的 叶子节点处。这种聚簇索引并不需要我们在Mysql语句中显示的使用 INDEX 语句去创建，InnoDB存储引擎会自动为我们创建聚簇索引(因为主键的存在)。

优点：

• 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非 聚簇索引更快
• 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快(存储的时候，就是有序的，查找用到了二分法，时间复杂度O(log2n))
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作 。（详情，可以了解磁盘读取数据的过程，涉及操作系统磁盘管理）

缺点

• 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新（这也是为什么索引不适合在频繁增删改的表中使用）
• 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

限制：

• 对于Mysql数据库，目前只有InnoDB支持聚簇索引，而MyISAM并不支持聚簇索引
• 由于数据物理存储排序方式只能有一种，索引每个Mysql的表只能有一个聚簇索引。一般情况为该表的主键
• 如果没有定义主键，InnoDB会选择 非空的唯一索引 代替。如果没有这样的索引，InnoDB会隐式的定义一个主键，作为聚簇索引。
• 为了充分利用聚簇索引的聚簇特性，所以InnoDB表的主键尽量选择有序的顺序id，而不建议用无序id，比如UUID、MD5,HASH,字符串列作为主键无法保证数据的顺序增长。

2、二级索引（辅助索引、非聚簇索引）

聚簇索引只能在搜索条件是主键的情况才能发挥作用，因为B+tree中的数据都是按照主键进行排序的，那么入股我们想以别的列 作为搜索条件该怎么办？肯定不能从头到尾沿着链表依次遍历记录。

我们可以多建几棵B+tree，不同的B+tree采用不同的排序规则，比如我们用C2列的大小 作为数据页中记录的排序规则，再建一棵B+tree，如下图（蓝色）：

索引中是不直接存储表中的用户记录的，上图二级索引中，叶子节点并没有完整存储用户记录。

此B+树与上边的聚簇索引有几处不同：

• 使用记录C2的大小进行记录和页的排序，这包括三方面的意义
  • 页内记录是按照C2的大小排列成的单向链表
  • 各个存放用户记录的页也是根据页中记录的C2列排列成一个双向链表
  • 存放目录项记录的页 分为不同层次，再同一层次中的页也是根据页中目录项记录的C2列大小顺序排成的双向链表
• B+树的叶子节点 存储的并不是完整的用户记录，而是C2+主键 这两个列的值。
• 目录项记录中不再是主键+页号，而变成了C2+页号。

现在我们根据C2的值为条件，进行查找，比如 想找到C2列的值为4的记录：

SELECT C1，C2，C3 FROM   `table_name`  WHERE C2=4;

1. 确定目录项记录页                                                                                                                  根据根页面，也就是44页，可以快速定位到目录项所在的页为页42（2<4<9）
2. 通过目录项记录页确定用户真实记录所在页                                                                              在页42中可以快速定位到使用存储用户记录的页，但是C2列并没有唯一性约束，所以C2列值为4的记录可能分布在多个数据页中，又因为（2<4<=4）,所以确定实际存储存储用户记录的页，在页34和页35中。
3. 在真实存储用户记录的页中定位到具体的记录。                                                                       到页34和页35中确定具体的记录。
4. 但是B+树的叶子节点中的记录只存储了C2和C1（也就是主键）两个列，所以，我们必须要根据主键值去 聚簇索引 中再查找一遍完整的用户记录(回表)。

概念：回表

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

问题：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？

如果把完整的用户记录都放到叶子节点中，可以不用回表，但是太占空间了，相当于每建立一棵B+tree都需要把所有的用户记录都拷贝一遍，这就太浪费存储空间了。

因为这种按照非主键列建立的 B+树需要一次回表操作，才可以定位到完整的用户记录，所以这种B+tree也称为二级索引，或者辅助索引。

由于我们使用的是C2列的大小作为B+树的排序规则，所以我们也称这个B+树是为C2建立的索引。

非聚簇索引的存在，不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

小结：聚簇索引和非聚簇索引的区别：

1、聚簇索引的 叶子节点 存储的就是我们的 数据记录，非聚簇索引的叶子节点存储的是 数据位置 。非聚簇索引不会影响数据表的物理存储顺序。
2、一个表 只能有一个聚蹊索引，因为只能有一种排序存储的方式，但可以有 多个非聚簇索引，也就是多个索引目录提供数据检索。
3、使用聚簇索引的时候，数据的 查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

3、联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按 照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意 思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立 联合索引 只会建立如上图一样的1棵B+树。
• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会 占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价​​​​​​​

时间上的代价 每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每 层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录，还 是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序 而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需 要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果 我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

你是否还在寻找稳定的海外服务器提供商？创新互联www.cdcxhl.cn海外机房具备T级流量清洗系统配攻击溯源，准确流量调度确保服务器高可用性，企业级服务器适合批量采购，新人活动首月15元起，快前往官网查看详情吧

当前题目：MySql索引——索引的结构2-创新互联
文章转载：http://cdxtjz.cn/article/dohojo.html