设计套路:Mysql主键的选取

服务器

浏览数:69

2019-3-21

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

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生成器的高可用问题,需要多加注意。