设计套路：Mysql主键的选取

最近在对一些大表进行优化，发现主键和索引设计都有争议，就此从原理上分析主键设计该如何选取。

Mysql的数据结构

Mysql是由B+树构成，搞清楚下面两个问题，就知道为什么用B+树了。

1.B+Tree是为磁盘或者其他直接存取辅助设备而设计的一种平衡二叉树?

答：数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B+Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

②B+Tree的节点都是按照键值的大小顺序存放的，叶节点之间也通过指针连接起来，为了提高取数据时的效率。

主键的选取

我先把结论给出来，方便喜欢直接套用的童鞋，但是对技术有追求的人来说，还是了解下原理比较有说服力。

• 非分布式架构直接套用自增id做主键

• 小规模分布式架构用uuid或者自增id+步长做主键

• 大规模分布式架构用自建的id生成器做主键，参考twitter的snowflake算法

---

一.自增id

1.性能消耗

从上面的原理可以得知，Mysql会按照键值的大小进行顺序存放，如果我们设置自增id为主键，这个时候主键是按照一种紧凑的接近顺序写入的方式进行存储数据。

如果我们用其他字段作为主键的话，此时Mysql不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多额外的开销，同时频繁的移动、分页操作造成了大量的碎片。

2.资源消耗

根据mysql官方的文档，非聚集（二级）索引都包含主键索引的列，所以如果主键太大，非聚集索引
会占用更多的磁盘空间。

How Secondary Indexes Relate to the Clustered Index

---

二.uuid或者自增id+步长

小规模分布式在数据量不大，使用成本最低的方式就直接用uuid，或者自增id+步长的方式，省时省力。

---

三.自建的id生成器

当数据量比较大，又是分布式架构的时候，可能我们需要考虑各种分库分表方案了，这个时候就不能贪图方便，必须有更好更长远的方案来替代。自建id生成器，可以保证全局唯一，可以参考snowflake的算法方案，具体实施也可以根据自身业务进行调整算法。唯一麻烦的就是id生成器的高可用问题，需要多加注意。