讯享网

                <p id="0IHEV79T">来源：cnblogs.com/wyq178/p/12548864.html</p><p id="0IHEV79V"><strong>前言：</strong>在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究竟有什么坏处？本篇博客我们就来分析这个问题，探讨一下内部的原因。</p><p>一、mysql和程序实例</p><p id="0IHEV7A0"><strong>1.1 要说明这个问题,我们首先来建立三张表</strong></p><p id="0IHEV7A1">分别是user_auto_key，user_uuid，user_random_key，分别表示自动增长的主键，uuid作为主键，随机key作为主键，其它我们完全保持不变。根据控制变量法，我们只把每个表的主键使用不同的策略生成，而其他的字段完全一样，然后测试一下表的插入速度和查询速度：</p><p id="0IHEV7A2"><strong>注：这里的随机key其实是指用雪花算法算出来的前后不连续不重复</strong>无规律的id:一串18位长度的long值</p><p id="0IHEV7A3">id自动生成表：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F360429d8j00r5lv7z001rd200go00byg0096006k.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7A5">用户uuid表</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F0756efe0j00r5lv80001od200hk00bsg00970066.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7A7">随机主键表：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F524de2e5j00r5lv80001rd200h200bqg0099006c.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7AA"><strong>1.2 光有理论不行,直接上程序,使用spring的jdbcTemplate来实现增查测试：</strong></p><p id="0IHEV7AC">技术框架：</p><p id="0IHEV7AD">springboot+jdbcTemplate+junit+hutool，</p><p id="0IHEV7AE">程序的原理就是连接自己的测试数据库，然后在相同的环境下写入同等数量的数据，来分析一下insert插入的时间来进行综合其效率，为了做到最真实的效果,所有的数据采用随机生成，比如名字、邮箱、地址都是随机生成，程序已上传自gitee，地址链接在文底。</p><p><br/><ul><li id="0IHEV7BQ"></li><li id="0IHEV7BR"></li><li id="0IHEV7BS"></li><li id="0IHEV7BT"></li><li id="0IHEV7BU"></li><li id="0IHEV7BV"></li><li id="0IHEV7C0"></li><li id="0IHEV7C1"></li><li id="0IHEV7C2"></li><li id="0IHEV7C3"></li><li id="0IHEV7C4"></li><li id="0IHEV7C5"></li><li id="0IHEV7C6"></li><li id="0IHEV7C7"></li><li id="0IHEV7C8"></li><li id="0IHEV7C9"></li><li id="0IHEV7CA"></li><li id="0IHEV7CB"></li><li id="0IHEV7CC"></li><li id="0IHEV7CD"></li><li id="0IHEV7CE"></li><li id="0IHEV7CF"></li><li id="0IHEV7CG"></li><li id="0IHEV7CH"></li><li id="0IHEV7CI"></li><li id="0IHEV7CJ"></li><li id="0IHEV7CK"></li><li id="0IHEV7CL"></li><li id="0IHEV7CM"></li><li id="0IHEV7CN"></li><li id="0IHEV7CO"></li><li id="0IHEV7CP"></li><li id="0IHEV7CQ"></li><li id="0IHEV7CR"></li><li id="0IHEV7CS"></li><li id="0IHEV7CT"></li><li id="0IHEV7CU"></li><li id="0IHEV7CV"></li><li id="0IHEV7D0"></li><li id="0IHEV7D1"></li><li id="0IHEV7D2"></li><li id="0IHEV7D3"></li><li id="0IHEV7D4"></li><li id="0IHEV7D5"></li><li id="0IHEV7D6"></li><li id="0IHEV7D7"></li><li id="0IHEV7D8"></li><li id="0IHEV7D9"></li><li id="0IHEV7DA"></li><li id="0IHEV7DB"></li><li id="0IHEV7DC"></li><li id="0IHEV7DD"></li><li id="0IHEV7DE"></li><li id="0IHEV7DF"></li><li id="0IHEV7DG"></li><li id="0IHEV7DH"></li><li id="0IHEV7DI"></li><li id="0IHEV7DJ"></li><li id="0IHEV7DK"></li><li id="0IHEV7DL"></li><li id="0IHEV7DM"></li><li id="0IHEV7DN"></li><li id="0IHEV7DO"></li><li id="0IHEV7DP"></li><li id="0IHEV7DQ"></li><li id="0IHEV7DR"></li><li id="0IHEV7DS"></li><li id="0IHEV7DT"></li><li id="0IHEV7DU"></li><li id="0IHEV7DV"></li><li id="0IHEV7E0"></li><li id="0IHEV7E1"></li><li id="0IHEV7E2"></li><li id="0IHEV7E3"></li><li id="0IHEV7E4"></li><li id="0IHEV7E5"></li><li id="0IHEV7E6"></li><li id="0IHEV7E7"></li><li id="0IHEV7E8"></li><li id="0IHEV7E9"></li><li id="0IHEV7EA"></li><li id="0IHEV7EB"></li><li id="0IHEV7EC"></li><li id="0IHEV7ED"></li><li id="0IHEV7EE"></li><li id="0IHEV7EF"></li><li id="0IHEV7EG"></li><li id="0IHEV7EH"></li><li id="0IHEV7EI"></li><li id="0IHEV7EJ"></li><li id="0IHEV7EK"></li></ul><br/></p><p id="0IHEV7AG"><strong>1.3 程序写入结果</strong></p><p id="0IHEV7AH">user_key_auto写入结果：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2Fbc4b7502j00r5lv81001yd200u0009rg00vp00aa.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7AJ">user_random_key写入结果：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F9c9e0177j00r5lv81002dd200u000axg00vq00bj.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7AL">user_uuid表写入结果：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F553f0fe8j00r5lv82002md200u0009og00vt00a8.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7AO"><strong>1.4 效率测试结果</strong><br/></p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F211cee2cj00r5lv83001fd200ps00ayg00ps00ay.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7AR">在已有数据量为130W的时候：我们再来测试一下插入10w数据，看看会有什么结果：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F12f371b0j00r5lv83000qd200ri004og00pw004e.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7AT">可以看出在数据量100W左右的时候,uuid的插入效率垫底，并且在后序增加了130W的数据，uudi的时间又直线下降。时间占用量总体可以打出的效率排名为：auto_key&gt;random_key&gt;uuid,uuid的效率最低，在数据量较大的情况下，效率直线下滑。那么为什么会出现这样的现象呢？带着疑问,我们来探讨一下这个问题：</p><p>二、使用uuid和自增id的索引结构对比</p><p id="0IHEV7AU"><strong>2.1 使用自增id的内部结构</strong></p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F643363c6j00r5lv83000yd200u0008dg01pg00h4.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7B0">自增的主键的值是顺序的,所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16,会留出1/16的空间留作以后的 修改)：</p><p id="0IHEV7B1">①. 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费</p><p id="0IHEV7B2">②. 新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗</p><p id="0IHEV7B3">③. 减少了页分裂和碎片的产生</p><p id="0IHEV7B5"><strong>2.2 使用uuid的索引内部结构</strong></p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2022%2F0113%2F6136dde1j00r5lv84001hd200u000cfg01ca00jz.jpg&thumbnail=660x2147483647&quality=80&type=jpg"/><br/></p><p id="0IHEV7B7">因为uuid相对顺序的自增id来说是毫无规律可言的,新行的值不一定要比之前的主键的值要大,所以innodb无法做到总是把新行插入到索引的最后,而是需要为新行寻找新的合适的位置从而来分配新的空间。这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：</p><p id="0IHEV7B8">①. 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO</p><p id="0IHEV7B9"><strong>②.</strong>因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上</p><p id="0IHEV7BA"><strong>③.</strong>由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片</p><p id="0IHEV7BB">在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后,有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。</p><p id="0IHEV7BC">结论：使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行</p><p id="0IHEV7BE"><strong>2.3 使用自增id的缺点</strong></p><p id="0IHEV7BF">那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：</p><p id="0IHEV7BG">①. 别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况</p><p id="0IHEV7BH">②. 对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争</p><p id="0IHEV7BI">③. Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失</p><p id="0IHEV7BJ">附：</p><p id="0IHEV7BK">Auto_increment的锁争抢问题，如果要改善需要调优innodb_autoinc_lock_mode的配置</p><p>三、总结</p><p id="0IHEV7BL">本篇博客首先从开篇的提出问题,建表到使用jdbcTemplate去测试不同id的生成策略在大数据量的数据插入表现，然后分析了id的机制不同在mysql的索引结构以及优缺点，深入的解释了为何uuid和随机不重复id在数据插入中的性能损耗，详细的解释了这个问题。在实际的开发中还是根据mysql的官方推荐最好使用自增id，mysql博大精深，内部还有很多值得优化的点需要我们学习。</p><p id="0IHEV7BM">本篇博客demo地址：</p><p id="0IHEV7BN">https://gitee.com/Yrion/mysqlIdDemo</p><p id="0IHEV7BO">PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。</p>