关系数据库中的聚集索引和主键

很多小伙伴在面试的时候会碰到如下一些数据库设计方面的问题：

1、你知道什么是聚集索引吗？

2、聚集索引有什么优势？

3、聚集索引怎么用？

4、听说过bookmark lookup吗？

那么这都是些什么玩意呢？这得先废话一下索引是什么了。

一般现代关系数据库中，从索引结构角度分，有以下几种索引：

B树索引、哈希索引、全文索引

其中本篇文章主题只涉及B树索引，其他两种另外再写，现在先把B树这种在关系数据库里应用最普遍的索引整明白。

B树索引其实应该叫B+树索引，和B树差不多，关系数据库中用的就是B+树索引，在不引起混淆的情况下就简单叫成B树了。

B树索引是最主要的一种索引结构，索引被组织成一棵树（不是二叉树哦），见下图：

除树叶节点以外，每个非叶子节点都若干栏，每栏里有一个数值和一个指向下一级某个节点的指针，表示说：要找大于等于这个值且小于下一个（在右边）那个值的数据，你顺着我的手指看，在那嘎达。这些栏按照里面的数值从小到大排排坐。你如果想找一个数（比如说：39）在哪，你就先从树根找起（就是最上面的那个节点），看看每栏里面的数值。最左面一栏的数值比如说是5，那意思是“5≤要找的数＜28”的数，你按着我手指的方向去找（就是那个P1），大于等于28的，你到下一栏里去碰碰运气。然后你看向右面一栏，那里的数值是28，再下一栏是65，你知道小于“28≤要找的数＜65”的数就找他问就成，然后你顺着他手指的方向（P2）找到下一级。重复前面的动作，最终找到了一个叶子节点，就是那个（35，38，50）的那个。

然后你就在叶子节点里挨个看那个值在不在，实际上因为叶子节点里的数值都是排好序的，你也可以折半查找，但是这不重要，后面我会说为什么不重要。然后你没找到，这时候，你可以拍着胸脯保证说“39这个数没有！”。

在数据库里按照一个key值检索数据发生的事情和上面说的是类似的。在数据库里，数据和索引都是存储在磁盘上的，为了能检索数据，数据库就得把磁盘上的数据或者索引读到内存里来。那么每次读多少呢？答案是n个页面大小。到底读几个页面视情况而定，但每个页面的大小在每个数据库里是固定的，有的是4KB，有的是8KB，还有的是64KB甚至更大，可以配置（但也不能随便瞎配啊）。

在非叶节点能放多少栏，取决于页面的大小以及为存储key值需要的空间大小，还有就是为存储页面号所需要的空间大小。大概其可以这么估算：

栏数 ≈ 页面大小 / (key有多胖 + 指针有多胖)

指针就是页面号，至于页面到底怎么编号，视数据库产品和存储引擎而定。

前面为什么说在叶子节点查找的时候是从头扫到尾还是用折半查找无所谓呢？这个就得说说磁盘访问速度和内存访问速度的巨大差距了。和内存比起来，磁盘访问是超级慢的。如果一个页面已经读到内存里了，那么是从头到尾扫描一遍还是用二分查找所耗费的时间和读取磁盘的巨大时间消耗比起来微不足道。所以说数据库优化并不是想法提高内存里面的算法效率，而是想方设法地减少磁盘访问！这句话非常重要，是所有数据库优化的总原则。

B树索引就说这么多了，下面说说聚集索引（又叫聚簇索引，Clustered Index）。咱们还是先看看索引的叶节点，我们有两种方案，一种是叶子节点只存放key值和指向该key值对应数据的磁盘页（块）的编号；另一种是直接把整行数据就放在叶子节点里。

前一种就叫做非聚集索引（Nonclustered Index），后一种就是聚集索引。就这么简单！

那么聚集索引有什么好处呢？这个就得说一说什么叫Bookmark Lookup了。检索数据就是找数据，就是lookup嘛，那bookmark是啥玩意儿呢？所谓bookmark就是书签，就是说通过书签来翻到书里对应的页面。说白了就是在索引里只找到了书签，还得再访问一次磁盘（如果不幸地数据所在的页面还没被读进内存的话），这无疑增加了一次磁盘访问。但好处是每个索引所能容纳的key值比较多，索引占的空间比较少，有可能事先把索引缓存到内存里，这样更有可能减少磁盘的读取。

现在问题复杂了：

不用聚集索引，因为索引占空间少，有可能减少磁盘访问；

用聚集索引，因为叶子节点就存储的是数据，也会减少磁盘访问。

那到底用不用好呢？这个就得具体分析了，如果表比较横宽，每行占用的空间超大，用聚集索引可能使得叶子节点退化，这样会增加中间节点的数量，使索引变大，降低索引被缓存的几率；而如果表比较瘦长，每行占用空间比较小，使用聚集索引的收获就比较可观。这只是一个方面啊。

另外一方面，聚集索引既然是把数据直接存储在索引的叶子节点里，那一个表就只能有一个聚集索引喽，不然得存多少份数据啊！那就贵了！那对于聚集索引的使用和选择就得讲究一些了，毕竟就只有一个机会，选错了没得后悔。

先按下这边不表，咱们再来说说主键。所谓主键，就是一个表里能唯一确定一条记录的列的组合（当然也可以不组合）。比如：产品ID啦，客户ID啦等等之类的，能在对应的表里唯一确定一条记录。那么数据库靠什么来保证唯一性呢？答案是索引！唯一索引！给一个表建了唯一索引后，对该表的新增和对索引键值的修改，都会先查找索引，看看会不会有冲突，没有才可以，有的话就报错没商量！

那么一般每个表都会有一个主键，很多人就说“诶，就在主键上建聚集索引就好了！”

我可以负责任地说：“大部分情况是对的！”事实上，很多书上也是这么建议的，甚至于数据库引擎自带的设计工具也会缺省用聚集索引来作为主键的索引。

如果事情就这么结束了的话，我也就没必要费辣么多唾沫说这个事了。那在什么情况下不适合这么做呢？

首先，在没有建主键必要的表上就不一定适用。“什么？还有不需要主键的表吗？”老实说，这样的表还真不多，至少我一时半会想不出什么例子。但是等等，还有一种情况约等于不需要建主键。

那就是使用自增ID，而且几乎不会删除记录的，又几乎会被其他表引用的表，最后访问这个表的程序也基本上不会用主键来定位记录。这种表有吗？业务系统中还真不多，大部分是分析系统里会有。这类表几乎很少会修改，也基本上是用来统计的表。这种表的ID几乎很少会用到，以这个ID来做聚集索引的索引键是没有什么实际意义的。

还有一种使用UUID做主键的表，不过这个会随着不同的数据库引擎而异。MySQL中同一批生成的UUID，前面基本一样，就是在后几位上会有所变化。数据还略微有序一点，而SQL Server，使用GUID（其实就是UUID，只是叫法不同）生成的GUID就完全没有章法可循，天马行空。而且MySQL官方也没保证其UUID的生成是有序的。如果是用一个程序生成的完全没有任何现实含义的码来做聚集索引主键的话，从数据检索角度看不到任何收益，也只有作为会被其他表引用的表，他的主键才会用来作为连接键而在查询执行的时候起到作用。

总结一下：

1、如果一个表会被好些其他的表引用，那用这个表的主键做聚集索引的索引键准没错；

2、如果一个表的主键会被程序直接使用，比如购物明细记录表，程序要用其主键来查找，修改，以至于删除记录，用它做聚集索引；

3、如果一个表的主键是自增的，而且有使用价值，比如可以简单地把它当做时间戳，用它做聚集索引也好使；

4、如果一个表的主键是程序自动生成的，也不大会被用到（一般没有按主键统计的需求），也没有别的表引用这个表，那还是另外找一个（或一些）有实际使用价值（用来做检索条件）的列或者列的组合来做聚集索引吧；

5、聚集索引不等同于唯一索引啊！这个一定得费几句话，如果是用来做统计的索引键，那么一个合适的对数据的分辨度可能会带来比唯一更高的收益。因为统计一般都是框范围，很少定位单条数据。那么索引能够快速定位到比较少的数据页面，就能带来足够的统计收益，而为了索引的唯一，可能会增加索引键的宽度，从而增加索引的体积，使得缓存里对该索引的命中率下降，带来负面效果。

写了这么多，最后再强调一下：

A、把主键做成聚集索引在大部分时候是可取的

B、聚集索引并不等同于唯一索引

C、在比较少的统计应用的场合，也许用更有统计价值的列做聚集索引带来的收益更大

我要说的就这么多了，欢迎小伙伴们拍砖。