|
|
51CTO旗下网站
|
|
移动端

为啥不能用uuid做MySQL的主键?

在 MySQL 中设计表的时候,MySQL 官方推荐不要使用 uuid 或者不连续不重复的雪花 id(long 形且唯一,单机递增),而是推荐连续自增的主键 id,官方的推荐是 auto_increment。

作者:Yrion来源:博客园|2020-09-08 09:04

在 MySQL 中设计表的时候,MySQL 官方推荐不要使用 uuid 或者不连续不重复的雪花 id(long 形且唯一,单机递增),而是推荐连续自增的主键 id,官方的推荐是 auto_increment。


图片来自 Pexels

那么为什么不建议采用 uuid,使用 uuid 究竟有什么坏处?本问我们从以下几个部分来分析这个问题,探讨一下内部的原因:

  • MySQL 程序实例
  • 使用 uuid 和自增 id 的索引结构对比
  • 总结

MySQL 程序实例

要说明这个问题,我们首先来建立三张表,分别是:

  • user_auto_key
  • user_uuid
  • user_random_key

他们分别表示自动增长的主键,uuid 作为主键,随机 key 作为主键,其他我们完全保持不变。

根据控制变量法,我们只把每个表的主键使用不同的策略生成,而其他的字段完全一样,然后测试一下表的插入速度和查询速度。

注:这里的随机 key 其实是指用雪花算法算出来的前后不连续不重复无规律的id:一串 18 位长度的 long 值。

id 自动生成表:

用户 uuid 表:

随机主键表:

光有理论不行,直接上程序,使用 Spring 的 jdbcTemplate 来实现增查测试。

技术框架:Spring Boot+jdbcTemplate+junit+hutool,程序的原理就是连接自己的测试数据库,然后在相同的环境下写入同等数量的数据,来分析一下 insert 插入的时间来进行综合其效率。

为了做到最真实的效果,所有的数据采用随机生成,比如名字、邮箱、地址都是随机生成:

  1. package com.wyq.mysqldemo; 
  2. import cn.hutool.core.collection.CollectionUtil; 
  3. import com.wyq.mysqldemo.databaseobject.UserKeyAuto; 
  4. import com.wyq.mysqldemo.databaseobject.UserKeyRandom; 
  5. import com.wyq.mysqldemo.databaseobject.UserKeyUUID; 
  6. import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService; 
  7. import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService; 
  8. import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService; 
  9. import com.wyq.mysqldemo.util.JdbcTemplateService; 
  10. import org.junit.jupiter.api.Test; 
  11. import org.springframework.beans.factory.annotation.Autowired; 
  12. import org.springframework.boot.test.context.SpringBootTest; 
  13. import org.springframework.util.StopWatch; 
  14. import java.util.List; 
  15.  
  16. @SpringBootTest 
  17. class MysqlDemoApplicationTests { 
  18.  
  19.     @Autowired 
  20.     private JdbcTemplateService jdbcTemplateService; 
  21.  
  22.     @Autowired 
  23.     private AutoKeyTableService autoKeyTableService; 
  24.  
  25.     @Autowired 
  26.     private UUIDKeyTableService uuidKeyTableService; 
  27.  
  28.     @Autowired 
  29.     private RandomKeyTableService randomKeyTableService; 
  30.  
  31.  
  32.     @Test 
  33.     void testDBTime() { 
  34.  
  35.         StopWatch stopwatch = new StopWatch("执行sql时间消耗"); 
  36.  
  37.  
  38.         /** 
  39.          * auto_increment key任务 
  40.          */ 
  41.         final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)"
  42.  
  43.         List<UserKeyAuto> insertData = autoKeyTableService.getInsertData(); 
  44.         stopwatch.start("自动生成key表任务开始"); 
  45.         long start1 = System.currentTimeMillis(); 
  46.         if (CollectionUtil.isNotEmpty(insertData)) { 
  47.             boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false); 
  48.             System.out.println(insertResult); 
  49.         } 
  50.         long end1 = System.currentTimeMillis(); 
  51.         System.out.println("auto key消耗的时间:" + (end1 - start1)); 
  52.  
  53.         stopwatch.stop(); 
  54.  
  55.  
  56.         /** 
  57.          * uudID的key 
  58.          */ 
  59.         final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)"
  60.  
  61.         List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData(); 
  62.         stopwatch.start("UUID的key表任务开始"); 
  63.         long begin = System.currentTimeMillis(); 
  64.         if (CollectionUtil.isNotEmpty(insertData)) { 
  65.             boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true); 
  66.             System.out.println(insertResult); 
  67.         } 
  68.         long over = System.currentTimeMillis(); 
  69.         System.out.println("UUID key消耗的时间:" + (over - begin)); 
  70.  
  71.         stopwatch.stop(); 
  72.  
  73.  
  74.         /** 
  75.          * 随机的long值key 
  76.          */ 
  77.         final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)"
  78.         List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData(); 
  79.         stopwatch.start("随机的long值key表任务开始"); 
  80.         Long start = System.currentTimeMillis(); 
  81.         if (CollectionUtil.isNotEmpty(insertData)) { 
  82.             boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true); 
  83.             System.out.println(insertResult); 
  84.         } 
  85.         Long end = System.currentTimeMillis(); 
  86.         System.out.println("随机key任务消耗时间:" + (end - start)); 
  87.         stopwatch.stop(); 
  88.  
  89.  
  90.         String result = stopwatch.prettyPrint(); 
  91.         System.out.println(result); 
  92.     } 

程序写入结果

user_key_auto 写入结果:

user_random_key 写入结果:

user_uuid 表写入结果:

效率测试结果

在已有数据量为 130W 的时候:我们再来测试一下插入 10w 数据,看看会有什么结果:

可以看出在数据量 100W 左右的时候,uuid 的插入效率垫底,并且在后序增加了 130W 的数据,uuid 的时间又直线下降。

时间占用量总体可以打出的效率排名为:auto_key>random_key>uuid。

uuid 的效率最低,在数据量较大的情况下,效率直线下滑。那么为什么会出现这样的现象呢?带着疑问,我们来探讨一下这个问题:

使用 uuid 和自增 id 的索引结构对比

使用自增 id 的内部结构

自增的主键的值是顺序的,所以 InnoDB 把每一条记录都存储在一条记录的后面。

当达到页面的最大填充因子时候(InnoDB 默认的最大填充因子是页大小的 15/16,会留出 1/16 的空间留作以后的修改)。

①下一条记录就会写入新的页中,一旦数据按照这种顺序的方式加载,主键页就会近乎于顺序的记录填满,提升了页面的最大填充率,不会有页的浪费。

②新插入的行一定会在原有的最大数据行下一行,MySQL 定位和寻址很快,不会为计算新行的位置而做出额外的消耗。

③减少了页分裂和碎片的产生。

使用 uuid 的索引内部结构

因为 uuid 相对顺序的自增 id 来说是毫无规律可言的,新行的值不一定要比之前的主键的值要大,所以 innodb 无法做到总是把新行插入到索引的最后,而是需要为新行寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作,数据的毫无顺序会导致数据分布散乱,将会导致以下的问题:

①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除,或者还没有被加载到缓存中,innodb 在插入之前不得不先找到并从磁盘读取目标页到内存中,这将导致大量的随机 IO。

②因为写入是乱序的,innodb 不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据,一次插入最少需要修改三个页以上。

③由于频繁的页分裂,页会变得稀疏并被不规则的填充,最终会导致数据会有碎片。

在把随机值(uuid 和雪花 id)载入到聚簇索引(InnoDB 默认的索引类型)以后,有时候会需要做一次 OPTIMEIZE TABLE 来重建表并优化页的填充,这将又需要一定的时间消耗。

结论:使用 InnoDB 应该尽可能的按主键的自增顺序插入,并且尽可能使用单调的增加的聚簇键的值来插入新行。

使用自增 id 的缺点

那么使用自增的 id 就完全没有坏处了吗?并不是,自增 id 也会存在以下几点问题:

①别人一旦爬取你的数据库,就可以根据数据库的自增 id 获取到你的业务增长信息,很容易分析出你的经营情况。

②对于高并发的负载,InnoDB 在按主键进行插入的时候会造成明显的锁争用,主键的上界会成为争抢的热点,因为所有的插入都发生在这里,并发插入会导致间隙锁竞争。

③Auto_Increment 锁机制会造成自增锁的抢夺,有一定的性能损失。

附:Auto_increment的锁争抢问题,如果要改善需要调优 innodb_autoinc_lock_mode 的配置。

总结

本篇博客首先从开篇的提出问题,建表到使用 jdbcTemplate 去测试不同 id 的生成策略在大数据量的数据插入表现,然后分析了 id 的机制不同在 MySQL 的索引结构以及优缺点,深入的解释了为何 uuid 和随机不重复 id 在数据插入中的性能损耗,详细的解释了这个问题。

在实际的开发中还是根据 MySQL 的官方推荐最好使用自增 id,MySQL 博大精深,内部还有很多值得优化的点需要我们学习。

作者:Yrion

编辑:陶家龙

出处:cnblogs.com/wyq178/p/12548864.html

【编辑推荐】

  1. 免安装,还原生产环境,运行中切换版本,这不是我认识的MySQL
  2. 开源数据库SQLite、MySQL和PostgreSQL比较
  3. MySQL的锁到底有多少内容 ?再和腾讯大佬的技术面谈,我还是小看锁了!
  4. MySQL索引凭什么让查询效率提高这么多?
  5. 阿里面试:MySQL索引凭什么能让查询效率提高这么多?
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

160人订阅学习

搭建数据中心实验Lab

搭建数据中心实验Lab

实验平台Datacenter
共5章 | ITGO(老曾)

107人订阅学习

大数据安全运维实战

大数据安全运维实战

CDH+Ambari
共20章 | 大数据陈浩

91人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微