引用
一、主键设计原则
- MySQL主键应当是对用户没有意义的。
- MySQL主键应该是单列的,以便提高连接和筛选操作的效率
- 永远也不要更新MySQL主键
- MySQL主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等
- MySQL主键应当有计算机自动生成
二、主键选取原则
主键是一个索引,mysql的索引是B+树,Mysql会按照键值的大小进行顺序存放,如果我们设置自增id为主键,这个时候主键是按照一种紧凑的接近顺序写入的方式进行存储数据。如果我们用其他字段作为主键的话,此时Mysql不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多额外的开销,同时频繁的移动、分页操作造成了大量的碎片。
- 考虑性能消耗
- 考虑资源消耗
- 考虑分库分表
三、主键类型的选择
-
整数类型
整数类型往往是id列最好的选择,因为效率最高并且可以使用数据库的自增主键。 -
字符串类型
字符串类型相比整数类型肯定更消耗空间,也会比整数类型操作慢。我主要使用的是Mysql,关于这个话题的解释建议看《高性能MySQL》第三版 P125。
四、主键设计的常用方案
- 非分布式架构直接套用自增id做主键
- 小规模分布式架构用uuid或者自增id+步长做主键
- 大规模分布式架构用自建的id生成器做主键,参考twitter的[snowflake算法][2]
1、自增ID
优点
1、数据库自动编号,速度快,而且是增量增长,聚集型主键按顺序存放,对于检索非常有利;
2、 数字型,占用空间小,易排序,在程序中传递方便。-
缺点
1、不支持水平分片架构,水平分片的设计当中,这种方法显然不能保证全局唯一;
2、表锁;在MySQL5.1.22之前,InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。
在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode:
0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inc locking(表锁机制)。
1:默认值,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。
2:对所有的insert-like 自增长值的产生使用互斥量机制完成,性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式。3、自增主键不连续。
CREATE TABLE `tmp_auto_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `talkid` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk;
当插入10条记录的时候,因为AUTO_INCREMENT=16,所以下次再插入的时候,主键就会不连续。
2、UUID
优点
1、全局唯一性、安全性、可移植性。
2、能够保证独立性,程序可以在不同的数据库间迁移,效果不受影响。
3、保证生成的ID不仅是表独立的,而且是库独立的,在你切分数据库的时候尤为重要缺点
1、针对InnoDB引擎会徒增IO压力,InnoDB为聚集主键类型的引擎,数据会按照主键进行排序,由于UUID的无序性,InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关(簇类索引),uuid 主键可能会引起数据位置频繁变动,严重影响性能。
2、UUID长度过长,一个UUID占用128个比特(16个字节)。主键索引KeyLength长度过大,而影响能够基于内存的索引记录数量,进而影响基于内存的索引命中率,而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。