[翻译] UUID 作为 InnoDB 主键的性能影响

[RedisMasterNode]

博客完整译文： https://blog.2014bduck.com/archives/287

原文标题：UUIDs are Popular, but Bad for Performance — Let’s Discuss
原文链接： https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/
作者：Yves Trudeau
翻译时间：2019-12-28

UUID 值有什么问题？

InnoDB 将表中的行存储在主键的 B 树中，在数据库中我们称之为聚簇索引。聚簇索引自动将数据行按主键顺序排列。

当你插入一行随机主键值的数据，InnoDB 需要找到这行应该属于哪一页，如果页没在缓冲池中则将其加载进缓冲池，插入数据行，最后将脏页刷回磁盘。纯随机值加上大表使得 B 树上的每个叶子节点都有机会插入行，而没有热点数据页。数据行不按照主键顺序（译注：主键顺序指主键顺序的末端）插入会导致页的分裂，进一步导致页的填充因子降低。在缓冲池中，有新数据插入的页称为脏页。而缓冲池中的页在被刷回磁盘前再次有新数据需要写入的概率很低。所以大部分时间中，每次插入操作会导致两次 IO 过程——一次读取和一次写入。所以首先 UUID 会对 IO 操作的比例造成影响，而这个又是伸缩性的主要限制因素。

硬件上来讲，获得高性能的唯一方法就是使用低延迟和高耐久的存储介质。然而这又是一个对性能造成影响的因素。因为聚簇索引的存在，辅助索引需要使用主键值作为指针。主键 B 树的叶子节点存储数据行，而辅助索引的叶子节点存储主键值。

我们假定有一个 UUID 作主键的表，并且有 5 个辅助索引，一共 10 亿行数据。如果你读了前面的段落，你会知道每行主键值被存了 6 次。也就是说一共 6 亿的 36 字节字符串值，216GB。这只是冰山一角而已，因为表通常还会有外键，显式或者隐式地指向其他表。当表是基于 UUID 设计的时候，列或者索引需要以char(36)来容纳数据。最近我分析了一个基于 UUID 的表，发现 70%的存储空间都用来存放 UUID 值。

不止这些，UUID 还有第三点影响。整型在 CPU 中一次性可以比较 8 字节，而 UUID 是逐字节比较的。数据库很少会受限于 CPU 性能，但是不管怎么样这都会提高查询的延迟。

补充

原文很长很长很长，希望大家不要只看了标题就说UUID如何如何，不仅仅是UUID，还有很多hash类型的也是类似，主要讨论的是导致页分裂->每个页填充因子低 & 字段长->多个索引保存主键作为书签导致体积大的坑，然后作者给了几种Option并且给出了测试结果

[wysnylc]

分布式没法自增主键，就这么简单

[xupefei]

研究了这么多年数据库，我现在才知道还有用 uuid 做主键这种玩法😂

[RedisMasterNode]

@wysnylc 其实这个 topic 的重要问题是在于 UUID 如何影响的，分布式也好，单点也好，都没有关系，需要找到一种方案减少数据页分裂后导致的碎片和散列数据对 Buffer Pool 的影响。。。其实应该关注文章内容，而不是标题。。。

[encro]

InnoDB 本省主键采用 B+，是有序存储的，用 uuid 存储大量数据得不偿失。

[RedisMasterNode]

@encro 但是要考虑到就算是 UUID 也可以做成区间有序的，这样同样可以减少页的分裂

[qsnow6]

分布式用 UUID 没毛病

[sagaxu]

@wysnylc 分布式可以按区自增，比如前 16bit 表示 node，每个 node 有 48bit 的自增区间

[love]

印象中我记得 UUID 不是 char(36)啊，而是 binary(16)，节省一倍呢

[xuanbg]

我们一直用 uuid 作为主键，综合下来好处比坏处多。如果你们都是单表几千万上亿的，能够挽救你的也不可能是自增 ID，而是类似雪花算法的分布式唯一 id。

[laminux29]

@wysnylc 分布式可以自增主键。

[laminux29]

1.任何方案都有其优缺点。

2.如果因业务限制，只能采用某方案，那就应该先解决业务，再来想办法优化。

[23571113]

吓得我又去看了遍教材

[whalegao]

用近似自增主键就好了

[br00k]

我挺喜欢 mongodb 的 ObjectId 的生成规则。

[levelworm]

@23571113 求问什么教材

[binux]

Postgres yes!

[RedisMasterNode]

@levelworm 这东西怎么会有啥教材，有教材无非也就是抄手册上的内容再复述一遍
https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html

当然偶尔也会有像姜承尧这种大牛出来写书，不过极少极少，至少到目前为止就 1 本

[zhxjdwh]

有序的 uuid 会好点吗

[tairan2006]

@wysnylc 阿里云 RDS 直接兼容 mysql 的 Auto Increment 啊…无缝升级

[MoccaCafe]

一般数据库有 uuid 类型的，实际上是类似于 int 有序的形式，性能也不会特别差

[optional]

uuid 用 36 字节？ 16 字节就够了啊

[RedisMasterNode]

做了个简单的小测试，乱序 32 位 MD5 ID vs 顺序 32 位字符串整数 ID vs 子增 ID 导致的页分裂次数，50000 条数据 INSERT：

```SQL
mysql> SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
LIKE '%split%';
+---------------------------------+-------+
| NAME | COUNT |
+---------------------------------+-------+
| index_page_splits | 406 |
+---------------------------------+-------+
1 rows in set (0.00 sec)

mysql> SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
LIKE '%split%';
+---------------------------------+-------+
| NAME | COUNT |
+---------------------------------+-------+
| index_page_splits | 288 |
+---------------------------------+-------+
1 rows in set (0.00 sec)


mysql> SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%split%';
+---------------------------------+-------+
| NAME | COUNT |
+---------------------------------+-------+
| index_page_splits | 193 |
+---------------------------------+-------+
1 rows in set (0.00 sec)
```

[pabno]

@wysnylc 可以使用雪花算法

[FS1P7dJz]

@wysnylc snowflake 了解一下,推特在用的,性能可以百万级
我相信绝大部分同类场景应该都足够满足

[lolizeppelin]

要加上定语 MYSQL

其他数据库 uuid 似乎没问题哦 哈哈哈
更别说时间序的 uuid 了

[RedisMasterNode]

@lolizeppelin 为什么，不是特别了解，但是 InnoDB 应该表达得比较明确了个人认为？据我所知 InnoDB 都是 B+树的索引组织表，如果不是的话请举出具体反例

[hooopo]

单机搞什么 uuid，分布式 uuid 也没用

[RedisMasterNode]

早知道就把标题改成散列 ID 的影响了...大家反正都不看内容只看标题..