Are You OK？主键、聚集索引、辅助索引
source link: https://my.oschina.net/u/4641354/blog/5191846
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
每张表都一定存在主键吗？
关于这个问题，各位小伙伴们不妨先自己想一想，再往下寻找答案。
首先公布结论：对于 InnoDB 存储引擎来说，每张表都一定有个主键（Primary Key）！
让人非常遗憾的是，网络上至今仍然有非常多的文章是这样的描述：“一张表中必须有聚集索引，但不一定需要主键”。前半句是正确的，后半句是大错特错！
对于 InnoDB 存储引擎来说，表采用的存储方式称为索引组织表（index organizedtable），也即表都是根据主键的顺序来进行组织存放的。如果主键都没有，表怎么存？
那下面这段没定义主键的建表语句是正确的吗？
CREATE TABLE test(
a INT NOT NULL,
b INT NULL,
c INT NOT NULL,
d INT NOT NULL,
UNIQUE KEY(b),
UNIQUE KEY(d),
UNIQUE KEY(c)
);
当然是没有任何问题的。
因为 不显示定义主键 != 没有主键。
如果在创建表时没有显式地定义主键，InnoDB 存储引擎会按如下方式选择或创建主键：
- 首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键
-
如果不符合上述条件，InnoDB 存储引擎自动创建一个 6 字节大小的指针
_rowid作为主键
那如果表中有多个非空唯一索引时怎么办呢？ InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引为主键。需要注意的是！主键的选择根据的是非空唯一索引定义的顺序，而不是建表时列的顺序。
比如上面那段代码，有 a、b、c、d 四个列，b、c、d 三列上都有唯一索引。不过 b 列不是非空的，所以不可能成为主键了。而 d 列首先被定义为非空的唯一索引，所以 InnoDB 存储引擎将其视为主键。
B+ 树索引总览
InnoDB 存储引擎支持以下几种常见的索引：
- B+ 树索引
所谓哈希索引也就是得益于哈希算法的快速查找特性，不过哈希索引的致命缺点就是无法范围查询。并且 InnoDB 中哈希索引是自适应的，也就是说 InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
全文索引本文先暂且不做赘述。
再来看 B+ 树索引，B+ 树索引的本质就是 B+ 树在数据库中的实现，它是目前关系型数据库系统中查找最为常用的索引。
关于 B+ 树的数据结构我就不详细说了，B 代表平衡（Balance），而不是二叉（Binary），B+ 树是从最早的平衡二叉树演化而来的，但是 B+ 树不是一个二叉树。
简单介绍下：B+ 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，各叶子节点之间通过双向链表进行连接。
也就是说，B+ 树的叶子节点存储真正的记录，而非叶子节点的存在是为了更快速的找到对应记录所在的叶子节点。如下图是一个高度为 2 的 B+ 树：
另外，需要注意的是，B+ 树索引并不能找到一个给定键值的具体“行”！B+ 树索引能找到的只是被查找数据行所在的“页”。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。
肯定有些小伙伴会懵逼了，“页” 又是什么东西？
这就得说到 InnoDB 存储引擎的逻辑存储结构。
InnoDB 存储引擎中，所有数据都被逻辑地存放在一个空间中，称之为 表空间（tablespace），也就是说我们常说的表，可以看作是 InnoDB 存储引擎逻辑结构的最高层。表空间又由 段（segment）、区（extent）、页（page） 组成（页有时也称为块 block）。如下图：
页是 InnoDB 磁盘管理的最小单位，在 InnoDB 存储引擎中，默认每个页的大小为 16KB。而页里面存放的东西就是一行一行的记录。
我们接下来要说的 聚集索引（clustered inex）和辅助索引（secondary index）其实都是一种 B+ 树索引。也就是说不管是聚集索引还是辅助索引，其内部都是 B+树，即高度平衡的，叶子节点存放着所有的数据。（需要注意的是，索引是存储引擎负责实现的，因此不是所有的存储引擎都支持聚簇索引）
聚集索引与辅助索引不同之处就是，叶子节点存放的是否是一整行的信息。下文我们会详细解释。
主键和聚集索引的关系
先来看聚集索引，上面我们说过，InnoDB 存储引擎表是索引组织表结构，即表中数据都是按照主键顺序进行存放的。而聚集索引就是按照每张表的主键构造一棵 B+ 树，同时叶子节点中存放的即为表中一行一行的数据，所以聚集索引的叶子节点也被称为数据节点。
聚簇索引的数据分布
也就是说，聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。并且由于定义了数据的逻辑顺序，查询优化器能够快速发现到底是哪一段范围的数据页需要扫描。比如用户需要查询一张用户表，查询最后注册的 10 位用户，由于 B+ 树索引的叶子节点是基于双向链表的，所以用户可以快速找到最后一个数据页，并取出 10 条记录。这也就是为什么大部分情况下查询优化器倾向于采用聚集索引了。
可以这么说：在聚集索引中，索引即数据，数据即索引。
另外，由于数据页只能按照一棵 B+ 树进行查找排序，或者说无法同时把数据行存放在两个不同的地方，所以每张表只能拥有一个聚集索引。
讲了这么多，好像还没讲到主键和聚集索引有啥区别。一张表只能有一个主键，并且也只能有一个聚集索引，聚集索引还是按照主键来构建的，那这种种迹象不都表明主键就是聚集索引？
事实上，主键和索引就不是一个层次的东西！
主键是一种约束，这个约束用来强制表的实体完整性，一个表中只能有一个主键约束，并且主键约束中的列值必须是非空且唯一的。
而聚集索引它作为一种索引，其目的不是为了约束啥，而是为了对数据行进行排序以提高查询的效率，换句话说它决定的是数据库的物理存储结构。
⭐ 形象点说，一个没加聚集索引的表，它的数据是一行一行 无序 地存放在磁盘存储器上的。而如果给表添加了聚集索引，那么表在磁盘上的存储结构就由一行一行排列的结构转变成了 树状结构，也就是 B+ 树结构，换句话说，就是整个表就变成了一个索引，也就是上面提到的 “索引即数据，数据即索引”。
而至于 “主键就是索引” 这种观点的由来，是因为：InnoDB 存储引擎中，每张表都一定存在主键（显示或隐式），而聚集索引依赖于主键的建立，所以如果没有强制指定使用非聚集索引，InnoDB 在创建主键的同时会建立一个唯一的聚集索引（也有些文章称之为 主键索引）。
所以，不要说 “主键就是聚集索引”，应该这样说：“聚集索引一般都是加在主键上的”。
聚集索引和辅助索引的关系
辅助索引（Secondary Index）也称为 非聚集索引、二级索引。其和聚集索引的最大区别就在于，辅助索引的叶子节点并不包含行记录的全部数据。
简单来说，一行记录我们可以用 “主键 + 其他数据” 这样的组合来标识，聚集索引中的叶子节点存储的就是这一整个组合，而非聚集索引中的叶子节点只存储了这个组合中的主键，那其他数据我怎么获得呢？
非聚集索引的叶子节点说还包含了一个 书签（bookmark），该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。
那各位不妨想一想，行数据存储在哪里呢？
没错，上文说过，聚集索引中的叶子节点中存放的就是表中一行一行的数据，所以 InnoDB 存储引擎的辅助索引中的书签其实就是相应行数据的聚集索引键。
也就是说，辅助索引的叶子节点包含的是：每行数据的主键 + 该行数据对应的聚集索引键。
当通过辅助索引来寻找数据时，InnoDB 存储引擎会先遍历辅助索引并通过叶子节点获得某个主键对应的聚集索引键，然后再通过聚集索引来找到一个完整的行记录。
举个例子，如果在一棵高度为 3 的辅助索引树中查找数据，那需要对这棵辅助索引树遍历 3 次找到指定聚集索引键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页。
另外，很显然的是，辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。
-
博主小硕在读，深耕 Java，目前在维护一个教程类仓库
CS-Wiki「Gitee 官方推荐项目，现已 1.8k+ star，仓库地址：https://gitee.com/veal98/CS-Wiki」，公众号上的文章也会在此同步更新，欢迎各位前来交流学习。 -
准备春招秋招的小伙伴可以参考我的这个论坛项目
Echo「Gitee 官方推荐项目，现已 900+ star，仓库地址：https://gitee.com/veal98/Echo」。配套教程正在同步更新中，公众号后台回复 "Echo" 即可免费获取。 -
另外，欢迎大家加入技术交流群『
小牛肉和它的小伙伴们』，感兴趣的各位可以下方扫码加我微信回复 "进群"，我拉你进群：
本文分享自微信公众号 - 飞天小牛肉（CS-Wiki）。
如有侵权，请联系 [email protected] 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
Recommend
-
53
今天和大家简单聊聊MySQL的约束主键与唯一索引约束： PRIMARY KEY and UNIQUE Index Constraints 文章不长，保证有收获。
-
39
-
17
引言 在面试中，大家应该经历过如下场景 面试官:"用过mysql吧，你们是用自增主键还是UUID？" 你:"用的是自增主键" 面试官:"为什么是自增主键？" ...
-
20
MySQL - @wangbenjun5 - 最近接触了一个项目，里面的数据库设计让我“大开眼界”，很多表没有主键 id，取而代之的是复合主键，也就是几个字段同时作为主键，比如说：A 表：```nameageaddr```
-
6
SQL Server的聚集索引和非聚集索引 微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和...
-
5
单个表上亿行数据的主键、索引设计，及分页查询 - ChenJacklondon的个人空间 - OSCHINA - 中文开源技术交流社区 一，概述 一般而言，我们对关系型数据库系统，进行表结构设计时，会按数据的种类，进行分类，一般有如下种类:
-
2
[TOC] 之前松哥在前面的文章中介绍 MySQL 的索引时，有小伙伴表示被概念搞晕了，主键索引、非主键索引、聚簇索引、非聚簇索引、二级索引、辅助索引等等，今天咱们就来捋一捋这些概念。 1. 按照功能划分按照功能来划分，索引主要有四种...
-
3
我们在开发或者面试的过程中经常会涉及到索引。今天我们来详细剖析一下索引常用的知识点。首先我们先介绍mysql的常用的存储引擎，其次是索引分类。二、索引分类MySQL 的索引有两种分类方式：逻辑分类和物理分类
-
2
1. 讲故事 最近在看 SQL SERVER 2008 查询性能优化，书中说当一个表创建了聚集索引，那么表中的行会按照主键索引的顺序物理排列，这里有一个关键词叫：物理排列，如果不了解底层原理，真的会被忽悠过去，其实仔细...
-
5
ClickHouse主键索引最佳实践 在本文中，我们将...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK