Mysql选择主键

    对于一个表来说主键选用的好坏直接关系到对于该表的操作性能，因此主键选用的好坏很大程度上决定了表的相关性能。一般来说选用主键需要遵循以下规则：

 

• 数据类型用 INT（bigint）类型

1. Int类型在做比较运算时会获取更好的性能（cpu比较周期缩短）。

2. Int类型是顺序排列的这样在索引中逻辑上相邻的数据就分布在磁盘相邻的地方（大大减少IO次数）

• 要尽可能的避免使用字符串来做主键

1. 主键长度尽可能短。如果选用bigint做主键由于bigint做主键只占8个字节所以比较节约空间，同时查询性能也很好。

2. 字符串来做主键，myisam默认的情况下为字符串使用了压缩索引这使查找更加缓慢。

• 还要特别注意是随机字符串，如MD5()，UUID()。他们产生的每一个新值都会被保存在很大的空间范围（通常会占用32个字节），这会减慢inset 及一些select查询原因如下：

1. 他们会减慢insert查询，因为插入的值会被随机放入索引页中，导致分页，随机磁盘访问及聚集存储引擎的聚集索引碎片。
2. 他们会减慢查询速度，因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。
3. 随机值会导致缓存对所有类型的查询性能都很差，因为他们会使缓存依赖以工作的访问局部性失效，如果整个数据集都变的同样“热”的时候，那么把特定部分的数据缓存到内存中就没有任何优势了。并且如果工作集部能被装入内存中，缓存就会进行很多刷写的工作，并且会导致很多缓存未命中。

• 对于mysql来说存储不同的存储引擎对主键的影响也稍有不同。

1. Myisam 引擎支持和聚集索引（主要是B树索引），不支持聚集索引。

2. Innodb 引擎支持聚集索引，非聚集索引（主要是B树索引，hash索引）。

3. 因此对于不支持聚集索引的myisam来说即使建立了主键也是不能为其建立聚集索引的，因此数据的物理排列顺序则是插入数据先后的顺序（myisam插入数据时是直接插入表的尾部的）。虽然数据是插入在表的尾部但是对于索引来说随机的主键值则是按一定的规则进行排列的。这样随机主键就容易导致索引频繁分页，进而出现索引碎片，最终导致insert慢select查询慢。

4. 对于innodb来说我们可以为主键建立聚集索引，聚集索引存储记录是物理上连续存在的。因此insert时插入排序规则(uuid_short())的值做主键可以直接将该值追加到表的尾部，且索引没有发生分页。更重要的是对于聚集索引来说索引下面直接对应的就是数据因此按主键查找时效率会比myisam要高很多。（myisam索引下面对应的是指向数据的一个指针）

5.  单纯对于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内完成，可能出现不单调递增（虽然这个可能性微乎其微）。