InnoDB Primary key-tiny's blog

随着MySQL，InnoDB在互联网的广泛应用，围绕MySQL展开的调优经验逐渐演化成准则，其中众所周知的InonDB自增主键，很多公司已经列为基础规范。比如这样：

CREATE TABLE `t_t` (
  `id` int NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB

为什么InnoDB需要一个自增主键？或许有以下的答案：

应该这样
大家都这么做，自然有他的好处
老师/前辈告诉/博客是这样
统一规范
查询效率
运维依赖
表体积（data，index）
更改主键需要重建表
隐藏主键
聚集索引(clusterd index)
B+ tree
二级索引
物理结构

统一规范

很多情况下，表的自增主键已经成为了InnoDB的建表规范，而这个规范的来源，不管是经验，还是通过测试数据对比，或者是重点在“统一“上，这样做都带来若干的好处：

建立参考基准，这一类还包括MySQL目录分配，基准配置，命名规范等。生产线采用符合基准的部署策略，对于初期部署，日常管理都会有很大的效率提升。
业务。关系型数据库中，范式作为解除集合函数依赖的法则，实体经常会在最终形成独立数据表，而最多的情况是，实例的属性中并没有能够唯一标识集合的key，比如user name，那么此时的自增主键就可以完美成为主键。说到范式，经常听到反（三）范式设计，范式只有满足与不满足，它既不是公理也不是真理，没有违反一说，在保证数据完整性的原则上做出任何手段的拆表都是可行的。
沟通成本。很多时候都会被问到，为什么要有建自增主键啊，32位md5行不行，64位uuid行不行，128位hash行不行。假设这个沟通结果为达成一致比例是7/10。单次消耗时间为t（因人而异），那么一段时间内花在这个点上的时间是t * n，并且，其中 t * n * 0.3为无效沟通。有了统一规范，自然沟通的成本就降低了。
还有一个好处，让越来越多的人认为就应该如此。：）

查询效率

对于上面提到的0.3，可能需要进一步的论据来支持。那怎么办？talk is cheap, show me the code。同理，只能拿数据说话，能动手的时候坚决不动口。做一个对比的压力测试，当然是选择性能更好的方式。

查询效率，准确说查询响应时间，包括单次查询和并发查询。分2种情况：

带key查询，这个key可是自定义主键，自增主键，二级索引。
不带key查询，做全表扫描。

运维依赖

管理工具。日常维护脚本工具对主键的依赖。
第三方工具，比如pecona toolkit（pt-online-schema-change ，pt-table-checksum）
高可用架构对显式定义主键的依赖：Percona XtraDB Cluster

表体积

表级的优化，很重要的一点是保证数据表处于最紧凑，体积最小的状态，尽可能占用最小的空间，减少碎片空间，在进行数据检索时，占用更少的IO资源，自然查询的效率就提升了。

表的体积：cluster index ，secondary index

对比下几种情况下主键的空间占用：

int 4 bytes  
    unsigned 0 ~ 4294967295
    signed：-2147483648 to 2147483647
 bigint 8 bytes  
     unsigned 0 ~ 18446744073709551615
     signed -9223372036854775808 to9223372036854775807
 隐藏主键  6 bypes  
     48位
 自定义（uuid） len(key) +1

所有的二级索引存放的是所依赖的主键信息，主键大小直接影响二级索引的大小。
主键的空间占用：int < 隐藏主键 < bigint < 自定义
InnoDB 使用主键来作为cluster index来形成整个索引树，单调递增的整数在b+ tree的形成，遍历，分裂，性能最优。需要注意的是，int 和bigint都是有限的取值域。

隐藏主键

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values.
 The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

对于每一个InnoDB表，InnoDB都需要一个cluster key来组织表数据，一般情况下是主键（PRIMAYR KEY）.如果用户没有指定主键，如果表中没有唯一性约束且有NOT NULL属性的数据列，InnoDB会创建一个6个字节隐藏主键列作为cluter key，这个隐藏主键（ROW_ID）在数据插入时根据全局序列单调递增,这个ROW_ID对用户不可见，也不会用于内部功能（行级复制）。

分配策略

在系统表空间中的数据字典中记录了所有ID的当前可用最大值, 可以理解为max(ID)+step, 这个全局的计算器就根据它来分配ID。

file: include/dict0boot.ic
38  UNIV_INLINE
39  row_id_t
40  dict_sys_get_new_row_id(void)
41  /*=========================*/
42  {
43          row_id_t        id;
44  
45          mutex_enter(&(dict_sys->mutex));
47          id = dict_sys->row_id;
49          if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {
51                  dict_hdr_flush_row_id();
52          }
54          dict_sys->row_id++;
56          mutex_exit(&(dict_sys->mutex));
57  
58          return(id);
59  }

全局序列使用dict_sys->mutex 互斥锁进行保护，即使是增值（和原子递增相反）。实现中并没有对ID值超过48位的溢出问题做保护处理，但是假设一个每秒100万的插入量也需要9年左右才能耗尽ID的取值域。

冲突的保护

DICT_HDR_ROW_ID_WRITE_MARGIN(256) 这个值决定了序列的落盘机制，如果取得的id对DICT_HDR_ROW_ID_WRITE_MARGIN取模为0（倍数），就修改数据字典中SYS中的DICT_HDR_ROW_ID并flush disk。InnoDB启动时，InnoDB会将磁盘上DICT_HDR_ROW_ID的增加到256-511范围内。这样就保证了已经产生的ID都小于当前的起始值，也就不会造成ID冲突。

性能

在InnoDB的代码中很多地方都使用到dict_sys->mutex。如果这个锁被占用（如drop table），那么包含隐藏主键的InnoDB表的插入操作将受到阻塞。
ROW_ID的分配时，共享的互斥锁和全局共享计数器都需要序列化，这也会形成性能瓶颈，如果对包含隐藏主键进行并发插入，其性能必然下降。
无论事务是否提交，每DICT_HDR_ROW_ID_WRITE_MARGIN个ROW_ID分配，都会进行SYS的刷盘操作，磁盘本身就是数据库系统中硬件短板，大多数优化手段都会将减少磁盘IO作为重要的考虑因素，使用隐藏主键自然会增加IO负载。

关于隐藏主键：How does InnoDB behave without a Primary Key?

长主键： Long PRIMARY KEY for Innodb tables

InnoDB Primary key