对于一个表来说主键选用的好坏直接关系到对于该表的操作性能,因此主键选用的好坏很大程度上决定了表的相关性能。一般来说选用主键需要遵循以下规则:
- 数据类型用 INT(bigint)类型
-
Int类型在做比较运算时会获取更好的性能(cpu比较周期缩短)。
-
Int类型是顺序排列的这样在索引中逻辑上相邻的数据就分布在磁盘相邻的地方(大大减少IO次数)
- 要尽可能的避免使用字符串来做主键
-
主键长度尽可能短。如果选用bigint做主键由于bigint做主键只占8个字节所以比较节约空间,同时查询性能也很好。
-
字符串来做主键,myisam默认的情况下为字符串使用了压缩索引这使查找更加缓慢。
- 还要特别注意是随机字符串,如MD5(),UUID()。他们产生的每一个新值都会被保存在很大的空间范围(通常会占用32个字节),这会减慢inset 及一些select查询原因如下:
- 他们会减慢insert查询,因为插入的值会被随机放入索引页中,导致分页,随机磁盘访问及聚集存储引擎的聚集索引碎片。
- 他们会减慢查询速度,因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。
- 随机值会导致缓存对所有类型的查询性能都很差,因为他们会使缓存依赖以工作的访问局部性失效,如果整个数据集都变的同样“热”的时候,那么把特定部分的数据缓存到内存中就没有任何优势了。并且如果工作集部能被装入内存中,缓存就会进行很多刷写的工作,并且会导致很多缓存未命中。
- 对于mysql来说存储不同的存储引擎对主键的影响也稍有不同。
-
Myisam 引擎支持和聚集索引(主要是B树索引),不支持聚集索引。
-
Innodb 引擎支持聚集索引,非聚集索引(主要是B树索引,hash索引)。
-
因此对于不支持聚集索引的myisam来说即使建立了主键也是不能为其建立聚集索引的,因此数据的物理排列顺序则是插入数据先后的顺序(myisam插入数据时是直接插入表的尾部的)。虽然数据是插入在表的尾部但是对于索引来说随机的主键值则是按一定的规则进行排列的。这样随机主键就容易导致索引频繁分页,进而出现索引碎片,最终导致insert慢select查询慢。
-
对于innodb来说我们可以为主键建立聚集索引,聚集索引存储记录是物理上连续存在的。因此insert时插入排序规则(uuid_short())的值做主键可以直接将该值追加到表的尾部,且索引没有发生分页。更重要的是对于聚集索引来说索引下面直接对应的就是数据因此按主键查找时效率会比myisam要高很多。(myisam索引下面对应的是指向数据的一个指针)
-
单纯对于myisam来说在选用主键类型时也是要避免使用字符串的。因为myisam上的字符串类型所建立的索引默认采用的是压缩处理后的格式,因此在查询时效率要稍慢一些。
UUID_SHORT() PK UUID()
UUID():用来生成唯一值 该值类型为string长度为32位且为无序的值(所谓无序的值是指每次生成的值没有规律可言或者说是随机的),这决定了他不能做主键
SELECT UUID(); fe534759-be25-11e4-ba95-4437e64f803e
UUID_SHORT():用来生成唯一值(理论上也是有限的但是由于出现出现重复的概率低到了极致或者说出现的概率最大为1/1000000000000000(以最低生成位数来算,实际生成位数一般都大于16)),跟UUID()相比UUID_SHORT()生成的是有序的整数,长度在16-21(或者更长)位之间,如果我们用bigint来存储处理后的UUID_SHORT()作为主键是完全符合主键的选取规则的。
SELECT uuid_short(); 23906203910275154
与uuid返回固定长度字符串不同, uuid_short的返回值是一个unsigned long long类型。MySQL启动后第一次执行的值是通过server_id << 56 + server_start_time << 24来初始化。server_start_time单位是秒。 之后每次执行都加1。
由于每次加1都会加全局mutex锁,因此多线程安全,可以当作sequence来用,只是初始值有点大。
Sequence
MySQL没有Oracle那样的sequence,在不是很精确的情况下,可以考虑上面提到的uuid_short。有一些不足:
1、初始值太大,无法重设
2、存在一个问题是每次重启后第一次执行的值不是重启前的那个值+1
3、而且如果重启在1s内完成,可能出现不单调递增(虽然这个可能性微乎其微)。
相关推荐
MySQL数据库主键重复原因分析及处理.pdf
MySQL 创建主键,外键和复合主键的方法,需要的朋友可以参考下。
mysql修改自增主键初始值,简单易操作,数据库维护小技巧。
主要介绍了Python3 操作 MySQL 插入一条数据并返回主键 id的实例,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
mysql用触发器实现char类型主键自增长
本文实例讲述了mysql非主键自增长用法。分享给大家供大家参考,具体如下: mysql并非只有主键才能自增长,而是设为键的列就可以设置自增长。 如下: CREATE TABLE t1 ( id INT, col1 INT auto_increment NOT NULL...
mysql雪花算法生成唯一整型ID主键的实现方法,整型ID作为主键好处有很多,比如节省存储空间、插入和查询排序快、具有一定规律性(时间顺序)等。
主要给大家介绍了关于spring boot整合mybatis利用Mysql实现主键UUID的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。
Mysql面试题主键自增
MySQL AUTO_INCREMENT 和主键等解释
关系数据库依赖于主键,它是数据库物理模式的基石。主键在物理层面上只有两个用途: 惟一地标识一行。 作为一个可以被外键有效引用的对象。 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),...
表中的主键值很少更改,因此在选择主键是需要小心,要选择很少发生更改的地方。一个表的主键可以被另一个表的外键引用。 为了更好地理解主键,我们创建一个名为Student的表,它具有roll_number、name、batch、phone_...
在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别? 上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。 1、主键索引 主键索引,简称主键,原文是PRIMARY KEY,...
主要介绍了MySQL中主键与外键的区别和联系,是MySQL入门学习中的基础知识,需要的朋友可以参考下
本文主要介绍了关于MySQL主键为0与主键自排约束的关系,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。 开始不设置主键表的设计如下: 如果id的位置有好几个0的话:设置主键并且自动排序时,0...
MySQL8自增主键变化.doc
设置一张MySQL表,表里有一个自增主键ID,往表里插入数据,假如插入数据之后表后一行的ID是100,我先删除这条ID为100的记录,然后重新启动服务器,按理说如果再往这个表里插入新的记录,新纪录的ID将为101,对吧...