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-4437e64f803e1

    UUID_SHORT()：用来生成唯一值（理论上也是有限的但是由于出现出现重复的概率低到了极致或者说出现的概率最大为 1/1000000000000000(以最低生成位数来算，实际生成位数一般都大于 16)），跟 UUID()相比 UUID_SHORT()生成的是有序的整数，长度在 16-21（或者更长）位之间，如果我们用 bigint 来存储处理后的 UUID_SHORT()作为主键是完全符合主键的选取规则的。

SELECT uuid_short();239062039102751541

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