[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

MySQL · ruzuojun · 于 4年前 发布 · 2784 次阅读

我们先了解下InnoDB引擎表的一些关键特征:

  • InnoDB引擎表是基于B+树的索引组织表(IOT);
  • 每个表都需要有一个聚集索引(clustered index);
  • 所有的行记录都存储在B+树的叶子节点(leaf pages of the tree);
  • 基于聚集索引的增、删、改、查的效率相对是最高的;
  • 如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择其作为聚集索引;
  • 如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引;
  • 如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。

综上总结,如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的,也就是下面这几种情况的存取效率最高:

  • 使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;
  • 该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;
  • 除此以外,如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。

实际情况是如何呢?经过简单TPCC基准测试,修改为使用自增列作为主键与原始表结构分别进行TPCC测试,前者的TpmC结果比后者高9%倍,足见使用自增列做InnoDB表主键的明显好处,其他更多不同场景下使用自增列的性能提升可以自行对比测试下。


本帖已被设为精华帖!
共收到 3 条回复 MySQL InnoDB
fecommerce#14年前 0 个赞

1.对应mysql的innodb表,你不设置主键,他自己也会生成一个主键

2.为什么主要要用递增主键,这个是对查询的优化,对于innodb,是区别于myisam的,myisam的索引,是直接指向的数据的指针,而innodb的索引,指向的是主键,然后主键指向数据的指针,因此对于索引的长度,在innodb是索引本身+主键的总和,所以,主键要尽量小,用递增主键是最小的,这样,在通过索引查询的时候,同样的内存,可以加载更多的索引。

3.innodb为什么要这样做呢?这是innodb的机制,为了可以在磁盘中顺序存储,这样在高并发读磁盘的时候,磁头可以顺序读,不用经常的变换磁道,当然,如果用的是ssd,测试结果又会不同。

4.建议读取一本我认为最好的mysql原理的数:高性能mysql,是淘宝dba翻译并加入实战想法的,吃透这本书,mysql优化不会有障碍。 @forecho

forecho#34年前 0 个赞

@fecommerce #2楼 这本书不错,但是太厚了,没敢看。

MySQL 这块我不是很精通,欢迎多分享一下经验。

添加回复 (需要登录)
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册