MySQL聚簇索引（mysql聚簇索引和非聚簇索引区别）

25-02-09 26

如果您对MySQL聚簇索引感兴趣，那么本文将是一篇不错的选择，我们将为您详在本文中，您将会了解到关于MySQL聚簇索引的详细内容，我们还将为您解答mysql聚簇索引和非聚簇索引区别的相关问题，并且为您

如果您对MySQL聚簇索引感兴趣，那么本文将是一篇不错的选择，我们将为您详在本文中，您将会了解到关于MySQL聚簇索引的详细内容，我们还将为您解答mysql聚簇索引和非聚簇索引区别的相关问题，并且为您提供关于MySQL -- 聚合索引VS非聚簇索引、mysql InnoDB index 主键采用聚簇索引，二级索引不采用聚簇索引、Mysql InnoDB聚簇索引二级索引联合索引特点、mysql 优化（3）聚簇索引和非聚簇索引的有价值信息。

本文目录一览：

MySQL聚簇索引（mysql聚簇索引和非聚簇索引区别）
MySQL -- 聚合索引VS非聚簇索引
mysql InnoDB index 主键采用聚簇索引，二级索引不采用聚簇索引
Mysql InnoDB聚簇索引二级索引联合索引特点
mysql 优化（3）聚簇索引和非聚簇索引

MySQL聚簇索引（mysql聚簇索引和非聚簇索引区别）

MysqL作为迭代了很多个版本的数据库。在数据库的索引上实现了很多的优化版本，从一开始的只允许一个表有一个列为索引值，到目前版本可支持多个列建立索引值，更多关于索引优化版本的描述，以后有机会笔者再写一篇文章。本文主要介绍索引当中的聚簇索引。

MysqL官方对聚簇索引的定义是，聚簇索引并不是一种单独的索引类，而是一种数据存储方式，第一次看到这段描述，我相信很多人都会一头雾水，索引是一种数据存储结构？这怎么解释？下面笔者一步一步来讲述MysqL对聚簇索引的定义和具体运用。

首先上贴一张图

在MysqL中，有一列值，专门被设定为聚簇索引，这列值就是主键，通常为数字类型的字段。那么如果数据表中没有主键呢？MysqL的解决办法是隐式地将一个唯一的非空的列定义为聚簇。那如果这也没有呢？MysqL就自己创建一个聚簇索引，具体这个聚簇索引内部是怎么建立的，笔者还需要去学习学习。反正无论如何，MysqL都会创建一个聚簇索引。

那么为什么说聚簇索引是一种数据存储结构呢？原因是MysqL将索引（即主键）对应的每一条记录都以链表的形式存储在索引的叶子页中，那么很容易理解，聚簇索引就是表，而反过来说，表以聚簇索引的形式来存储。那么是所有的MysqL存储引擎都采用聚簇索引这种数据存储结构吗？答案是否定的，在MysqL中，只用Innodb引擎才采用聚簇索引，其他的存储引擎像MyISAM采用非聚簇索引。

那么接下来的问题是聚簇索引有什么优势，它的这种优势是因为什么产生的？本人学习过程中总结了以下几点。

1. 采用聚簇索引，索引和其他列值存储在一起，在查询过程中利用B-Tree查询，搜寻速率很快。而采用非聚簇索引（如MyISAM引擎），在查询上比Innodb相对要慢得多。

2. 聚簇索引内部实现了将相类似的数据存放在一起，当需要查询相类似的内容时，只需要查询比较少的数据页就可以实现对数据的获取。

为了理解第一点优势，我们还需要去理解二级索引的一些相关问题。

在Innodb中，二级索引除了存储本身的列值外，其叶子节点存储的不是‘行指针’，而是主键值，为什么是这样呢？原来这种方式在表结构发生变化的时候会有很大的优势。如果二级索引的存储顺序是以列值为基础的，那么在发生数据行的移动或者增加删除时候，必定会引起索引结构的巨大变化，举个例子：在MyISAM存储引擎的表结构，索引的存储顺序是以数值大小或者字符串的字母为基准的，如果有这么一列数据age是数字类型，当其中的一条数据的age改变之后，跟在它后面的数据的顺序要重新排列，修改后的数据插入原来的表中也需要花费一番功夫。而Innodb的优势在于，以主键为顺序（通常主键是没有任何实际意义的一列自增的数值），因为主键是顺序的，自增的，就不会出现说因为二级索引值的改变而使一条数据在表中移动过程中产生巨大影响甚至改变了表的结构。但有一个缺点是Innodb的二级索引的这类指针会加大了二级索引的存储空间。当数据量很大的时候，还是会有一定的空间压力的。

下图是Innodb存储引擎的二级索引储存结构。

图片介绍：keycolumns为二级索引（灰色行），primarykey为主键索引（白色行）

上面还提到了一点，主键是一列自增的数值。那么主键是否自增对索引的结构产生影响呢？

MysqL官方在这个问题上建议数据库管理者和应用开发者使用自增的索引，原因是如果使用非自增的索引和不确定长度的索引，会在数据库写入大量数据的时候产生很大的性能问题。

因为数据的写入顺序是不确定的，数据需要在原表中寻找一个位置插入，而这个位置通常不是在已有数据的最后，可能是中间，这样会增加很多额外的工作。同时，因为写入的位置不确定会使表频繁的分页和产生较多的空索引值或者说索引值之间产生很大的空隙，即产生了大量的碎片。这样一来，对数据的写入操作就会产生很大的性能问题。

下面放MyISAM存储引擎的图片作为和Innodb存储引擎对比

MyISAM存储引擎

从图片可以看出，MyISAM的二级索引是以具体的列值为顺序进行

下面两张图比较形象地对两种存储引擎的主键索引和二级索引的指针指向作了一个对比

回归到问题的最初点—为什么采用聚簇索引查询速度会更快？原因是聚簇索引的每一个索引值的叶子节点就为数据，只要查询到对应的主键索引值，就可以查询到对应的哪一行数据，读取速度相对来说提升了不少。而MyISAM需要特别为索引创建存储空间来存放，在查询过程中还要到磁盘中进行数据的读取。

相对来说聚簇索引的第二个优势就很好理解了，例如一个关于会员的表记录了会员的id值，姓名，年龄，性别和手机号，假设手机号是二级索引，在存储二级索引的时候，会将手机号码开头几位数字相似的手机号存放在一起，例如以135和170开头的手机号会分在不同的索引表中。在查询的过程中可以对二级索引的查询，快速查到手机号以135开头的那部分会员的信息。而在非聚簇索引表中（MyISAM存储引擎），主键索引和二级索引互相影响（如上图），在查询过程就很难实现优化了。

以上是本人对聚簇索引和非聚簇索引的应用场景和优劣势的对比。另外聚簇索引有时候也会跟覆盖索引拿出来一起比较，实际上两种索引是不同的概念，前者描述的是一种数据存储的结构，后者是描述在数据查询中直接对索引值的查询的过程。而在实现的结构基础上，聚簇索引是基于主键列的，覆盖索引是基于非主键列的任何数据列。

MySQL -- 聚合索引VS非聚簇索引

聚簇索引是指索引的结构跟数据记录的物理存储结构一致，也可以说，聚合索引就是数据记录的物理存储结构，innodb引擎中，聚簇索引就是主键索引。

以表t为例，分析在不同存储引擎下，其索引的结构：

create table t(
    id int primary key auto_increment,
    score int,
    name varchar(255),
    KEY idx_name (name)
);

Innodb引擎

主键索引(聚簇索引)

主键索引即聚簇索引，叶子节点存储完整的一条记录，非叶子节点存储索引字段的值。

聚簇索引的问题：记录插入查询依赖主键的顺序。
若按主键有序插入，那么主键索引是顺序写入，性能较高；
若主键不是有序插入，比如uuid，则每次插入需查找插入位置(可能还涉及页分裂)，变成随机写入，性能较差；

非主键索引

非主键索引，也称为二级索引，叶子节点存储主键的ID，若要查询记录的其它信息，则需要回表到主键索引。

MyISAM引擎

MyISAM引擎按照记录的插入顺序，保存到磁盘上。

MyISAM的主键索引和非主键索引均不是聚簇索引，都不同于记录的物理存储结构。

主键索引

主键索引，其叶子节点存储该条记录的地址。

非主键索引

非主键索引，同主键索引类似，叶子节点存储该条记录的地址。

mysql InnoDB index 主键采用聚簇索引，二级索引不采用聚簇索引

原文链接

我的归纳：
（1）InnoDB 的主键采用聚簇索引存储，使用的是 B+Tree 作为索引结构，但是叶子节点存储的是索引值和数据本身（注意和 MyISAM 的不同）。
（2）InnoDB 的二级索引不使用聚蔟索引，叶子节点存储的是 KEY 字段加主键值。因此，通过二级索引查询首先查到是主键值，然后 InnoDB 再根据查到的主键值通过主键索引找到相应的数据块。
（3）MyISAM 的主键索引和二级索引叶子节点存放的都是列值与行号的组合，叶子节点中保存的是数据的物理地址
（4）MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址
（5）为什么用 B+Tree 不是 BTree：
B-Tree：如果一次检索需要访问 4 个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次 I/O 操作，完成这次检索操作，最多需要 3 次 I/O (根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O 操作就少了，检索效率也就上去了。
B+Tree：非叶子节点只存 key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O 操作更少了。所以 B+Tree 拥有更好的性能。

下面是原文中对聚簇索引的介绍，介绍的很简单易懂：

1. 聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

因此，MySQL 中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。

2. 下面，我们可以看一下 mysql 中 MYISAM 和 INNODB 两种引擎的索引结构。

如原始数据为：
这里写图片描述

MyISAM 引擎的数据存储方式如图：
这里写图片描述

（1）MYISAM 是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。
从 MYISAM 存储的物理文件我们能看出，MYISAM 引擎的索引文件（.MYI）和数据文件 (.MYD) 是相互独立的。

（2）而 InnoDB 按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储数据的结构大致如下：
这里写图片描述

注：聚簇索引中的每个叶子节点包含主键值、事务 ID、回滚指针 (rollback pointer 用于事务和 MVCC）和余下的列 (如 col2)。

（3）INNODB 的二级索引与主键索引有很大的不同。InnoDB 的二级索引的叶子包含主键值，而不是行指针 (row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为 InnoDB 不需要更新索引的行指针。其结构大致如下：
这里写图片描述

INNODB 和 MYISAM 的主键索引与二级索引的对比：
这里写图片描述

（4）InnoDB 的的二级索引的叶子节点存放的是 KEY 字段加主键值。因此，通过二级索引查询首先查到是主键值，然后 InnoDB 再根据查到的主键值通过主键索引找到相应的数据块。而 MyISAM 的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出 MYISAM 的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做 PRIMARY 的唯一、非空的索引，且 MYISAM 引擎中可以不设主键。

Mysql InnoDB聚簇索引二级索引联合索引特点

一、聚簇索引

其实之前内容中介绍的 B+ 树就是聚簇索引。

这种索引不需要我们显示地使用 INDEX 语句去创建，InnoDB 引擎会自动创建。另外，在 InnoDB 引擎中，聚簇索引就是数据的存储方式。

它有 2 个特点：

特点 1

使用记录主键值的大小进行记录和页的排序。

其中又包含了下面 3 个点：

页(包括叶节点和内节点)内的记录按照主键的大小顺序排成一个单向链表。页内记录划分为若干组，每个组中主键值最大的记录在页内的偏移量被当做槽依次存放在页目录中。我们可以通过二分法快速定位主键值等于某个值的记录。
各存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
各存放目录项记录的页分为不同层级。在同一层级中的页，也是根据页中目录项记录的主键大小顺序排成一个双向链表。

特点 2

B+树的叶子节点存储的是完整的用户记录。

这里完整的用户记录就是指，这个记录中存储了所有的列的值(包括隐藏列)。

二、二级索引

聚簇索引只能在我们搜索主键值时才能发挥作用，因为 B+ 树中的数据都是按照主键进行排序。

如果现在我用“别的列”作为搜索条件，怎么办？

答案：再建一个 B+ 树，用这个“别的列”(非主键列)的值大小作为排序规则。

比如之前的内容都是以 c1 列为主键，现在用 c2 列再来创建一个 B+ 树：

看起来跟之前的聚簇索引没啥区别啊？实际上还是存在不同的：

使用记录 c2 列的大小进行记录和页的排序。细分的 3 点与上面聚簇索引介绍的一样，只不过上面是主键，这里是用的 c2 列(非主键)。
B+ 树的叶子节点存储的不是完整的用户记录，只有c2 列 + 主键这2个列的值。
目录项记录中不再是主键 + 页号，变成了c2 列 + 页号。

另外需要注意的是，因为 c2 列不是主键，所以没有唯一性约束，可能存在多条满足搜索条件的数据。

现在根据条件 c2 = 4 来查找数据记录，过程如下：

确定第一条符合 c2 = 4 的目录项所在页，也就是页 42。

到页 42 中，进一步确定第一条符合条件的记录所在的用户记录页。因为 2 < 4 <= 4，所以可能存在页 34 或 35 中。

先到页 34 中定位第一条满足 c2 = 4 的用户记录，如果有就不需要再到页 35 中继续定位了。

在页 34 中定位到第一条记录。因为这条用户记录不完整，所以拿到这条记录的主键，再到聚簇索引中找到完整的用户记录。

上面最后一步，通过携带主键信息到聚簇索引中重新定位完整的用户记录的过程也叫回表。

回表后，再回到这颗新的 B+ 树，找到刚才那个第一个符合条件的记录，并沿着记录的单向链表向后继续搜索其他也满足 c2 = 4 的记录，每找到一条就继续回表操作，重复这个过程。

这种以非主键列的大小为排序规则而建立 B+ 树需要执行回表操作才可以定位到完整的用户记录，这种 B+树就称为二级索引或者辅助索引。

为什么要回表？直接把完整用户记录都放叶子节点不就可以了？

没错，思路没问题。但是这样操作就相当于每建立一颗 B+ 树都把所有的用户记录复制一遍，太浪费存储空间。

三、联合索引

我们可以同时为多个列建立索引，比如 c2 列和 c3 列，以这 2 个列的大小为排序规则建立的 B+ 树索引就称为联合索引，也称为符合索引或多列索引。

这里的按照 c2 和 c3 列大小进行排序，需要注意两点：

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

现在，给c2 和 c3 建立联合索引，如图所示：

需要注意的是：

每条目录项记录都是由 c2、c3、页号这 3 部分组成。各记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列进行排序。
B+ 树叶子节点的用户记录由 c2、c3、和主键c1 列组成。

本质上，联合索引也是一个二级索引，只不过它的索引列包括 c2、c3 这2个列。

本文参考书籍：《mysql是怎样运行的》

以上就是Mysql InnoDB聚簇索引二级索引联合索引特点详解的详细内容，更多关于Mysql InnoDB聚簇二级联合索引的资料请关注其它相关文章！

您可能感兴趣的文章:

深入讲解MySQL Innodb索引的原理
Mysql InnoDB引擎的索引与存储结构详解
关于InnoDB索引的底层实现和实际效果

mysql 优化（3）聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是btree 大类

按照算法 btree

hash散列（memory引擎）在内存里

mysam

innodb

组织数据上有区别虽然都是btree 按照算法分

mysam 指向改行磁盘位置索引和数据独立非聚簇相互独立

innodb 指向引用本身有数据全部聚簇聚在这！因为既存储主键值又存储行数据因此称为聚簇索引

主索引藏着改行数据称为聚簇索引，次索引指向对主键的引用。

没有主键会去找是不是有一个unique key ... 没有 unique key 系统会生成一个row id
不回行不规则插入会造成页分裂

innodb

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势: 如果碰到不规则数据插入时,造成频繁的页分裂

分别规则插入10000条数据,不规则插入10000条数据

观察时间的差异,体会聚簇索引,页分裂的影响.

以上就是mysql 优化（3）聚簇索引和非聚簇索引的内容。

关于MySQL聚簇索引和mysql聚簇索引和非聚簇索引区别的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于MySQL -- 聚合索引VS非聚簇索引、mysql InnoDB index 主键采用聚簇索引，二级索引不采用聚簇索引、Mysql InnoDB聚簇索引二级索引联合索引特点、mysql 优化（3）聚簇索引和非聚簇索引的相关知识，请在本站寻找。

本文标签：