V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
RedisMasterNode
V2EX  ›  MySQL

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

  •  
  •   RedisMasterNode · 2019-12-28 18:42:46 +08:00 · 8598 次点击
    这是一个创建于 1807 天前的主题,其中的信息可能已经有所发展或是发生改变。

    博客完整译文: 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 性能,但是不管怎么样这都会提高查询的延迟。

    第 1 条附言  ·  2019-12-29 14:30:24 +08:00

    补充

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

    28 条回复    2019-12-30 14:22:48 +08:00
    wysnylc
        1
    wysnylc  
       2019-12-28 19:45:45 +08:00 via Android
    分布式没法自增主键,就这么简单
    xupefei
        2
    xupefei  
       2019-12-28 20:01:09 +08:00 via iPhone
    研究了这么多年数据库,我现在才知道还有用 uuid 做主键这种玩法😂
    RedisMasterNode
        3
    RedisMasterNode  
    OP
       2019-12-28 20:04:55 +08:00
    @wysnylc 其实这个 topic 的重要问题是在于 UUID 如何影响的,分布式也好,单点也好,都没有关系,需要找到一种方案减少数据页分裂后导致的碎片和散列数据对 Buffer Pool 的影响。。。其实应该关注文章内容,而不是标题。。。
    encro
        4
    encro  
       2019-12-28 20:06:39 +08:00
    InnoDB 本省主键采用 B+,是有序存储的,用 uuid 存储大量数据得不偿失。
    RedisMasterNode
        5
    RedisMasterNode  
    OP
       2019-12-28 20:53:31 +08:00
    @encro 但是要考虑到就算是 UUID 也可以做成区间有序的,这样同样可以减少页的分裂
    qsnow6
        6
    qsnow6  
       2019-12-28 21:44:16 +08:00
    分布式用 UUID 没毛病
    sagaxu
        7
    sagaxu  
       2019-12-28 21:51:25 +08:00 via Android
    @wysnylc 分布式可以按区自增,比如前 16bit 表示 node,每个 node 有 48bit 的自增区间
    love
        8
    love  
       2019-12-28 21:58:05 +08:00
    印象中我记得 UUID 不是 char(36)啊,而是 binary(16),节省一倍呢
    xuanbg
        9
    xuanbg  
       2019-12-28 22:37:32 +08:00
    我们一直用 uuid 作为主键,综合下来好处比坏处多。如果你们都是单表几千万上亿的,能够挽救你的也不可能是自增 ID,而是类似雪花算法的分布式唯一 id。
    laminux29
        10
    laminux29  
       2019-12-28 22:41:22 +08:00
    @wysnylc 分布式可以自增主键。
    laminux29
        11
    laminux29  
       2019-12-28 22:43:21 +08:00
    1.任何方案都有其优缺点。

    2.如果因业务限制,只能采用某方案,那就应该先解决业务,再来想办法优化。
    23571113
        12
    23571113  
       2019-12-29 04:21:01 +08:00
    吓得我又去看了遍教材
    whalegao
        13
    whalegao  
       2019-12-29 08:37:50 +08:00 via iPhone
    用近似自增主键就好了
    br00k
        14
    br00k  
       2019-12-29 09:11:03 +08:00 via iPhone
    我挺喜欢 mongodb 的 ObjectId 的生成规则。
    levelworm
        15
    levelworm  
       2019-12-29 09:44:27 +08:00 via Android
    @23571113 求问什么教材
    binux
        16
    binux  
       2019-12-29 09:53:09 +08:00 via Android
    Postgres yes!
    RedisMasterNode
        17
    RedisMasterNode  
    OP
       2019-12-29 09:53:50 +08:00   ❤️ 1
    @levelworm 这东西怎么会有啥教材,有教材无非也就是抄手册上的内容再复述一遍
    https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html

    当然偶尔也会有像姜承尧这种大牛出来写书,不过极少极少,至少到目前为止就 1 本
    zhxjdwh
        18
    zhxjdwh  
       2019-12-29 10:32:43 +08:00 via Android
    有序的 uuid 会好点吗
    tairan2006
        19
    tairan2006  
       2019-12-29 12:09:18 +08:00
    @wysnylc 阿里云 RDS 直接兼容 mysql 的 Auto Increment 啊…无缝升级
    MoccaCafe
        20
    MoccaCafe  
       2019-12-29 13:45:13 +08:00
    一般数据库有 uuid 类型的,实际上是类似于 int 有序的形式,性能也不会特别差
    optional
        21
    optional  
       2019-12-29 13:51:31 +08:00 via iPhone
    uuid 用 36 字节? 16 字节就够了啊
    RedisMasterNode
        22
    RedisMasterNode  
    OP
       2019-12-29 15:50:29 +08:00
    做了个简单的小测试,乱序 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
        23
    pabno  
       2019-12-29 17:19:15 +08:00
    @wysnylc 可以使用雪花算法
    FS1P7dJz
        24
    FS1P7dJz  
       2019-12-29 19:02:40 +08:00
    @wysnylc snowflake 了解一下,推特在用的,性能可以百万级
    我相信绝大部分同类场景应该都足够满足
    lolizeppelin
        25
    lolizeppelin  
       2019-12-29 22:59:40 +08:00
    要加上定语 MYSQL

    其他数据库 uuid 似乎没问题哦 哈哈哈
    更别说时间序的 uuid 了
    RedisMasterNode
        26
    RedisMasterNode  
    OP
       2019-12-30 00:05:01 +08:00
    @lolizeppelin 为什么,不是特别了解,但是 InnoDB 应该表达得比较明确了个人认为?据我所知 InnoDB 都是 B+树的索引组织表,如果不是的话请举出具体反例
    hooopo
        27
    hooopo  
       2019-12-30 13:09:17 +08:00
    单机搞什么 uuid,分布式 uuid 也没用
    RedisMasterNode
        28
    RedisMasterNode  
    OP
       2019-12-30 14:22:48 +08:00
    早知道就把标题改成散列 ID 的影响了...大家反正都不看内容只看标题..
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5463 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 06:34 · PVG 14:34 · LAX 22:34 · JFK 01:34
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.