為什么MySQL不推薦使用 UUID 或者雪花id作為主鍵？
作者：Yrion
原文鏈接：cnblogs.com/wyq178/p/12548864.html
MySQL 和程序?qū)嵗?/span>
注：這里的隨機(jī)key其實(shí)是指用雪花算法算出來(lái)的前后不連續(xù)不重復(fù)無(wú)規(guī)律的id:一串18位長(zhǎng)度的long值
測(cè)試代碼
package?com.wyq.mysqldemo;
import?cn.hutool.core.collection.CollectionUtil;
import?com.wyq.mysqldemo.databaseobject.UserKeyAuto;
import?com.wyq.mysqldemo.databaseobject.UserKeyRandom;
import?com.wyq.mysqldemo.databaseobject.UserKeyUUID;
import?com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
import?com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
import?com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
import?com.wyq.mysqldemo.util.JdbcTemplateService;
import?org.junit.jupiter.api.Test;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.springframework.boot.test.context.SpringBootTest;
import?org.springframework.util.StopWatch;
import?java.util.List;
@SpringBootTest
class?MysqlDemoApplicationTests?{
????@Autowired
????private?JdbcTemplateService?jdbcTemplateService;
????@Autowired
????private?AutoKeyTableService?autoKeyTableService;
????@Autowired
????private?UUIDKeyTableService?uuidKeyTableService;
????@Autowired
????private?RandomKeyTableService?randomKeyTableService;
????@Test
????void?testDBTime()?{
????????StopWatch?stopwatch?=?new?StopWatch("執(zhí)行sql時(shí)間消耗");
????????/**
?????????*?auto_increment?key任務(wù)
?????????*/
????????final?String?insertSql?=?"INSERT?INTO?user_key_auto(user_id,user_name,sex,address,city,email,state)?VALUES(?,?,?,?,?,?,?)";
????????List?insertData?=?autoKeyTableService.getInsertData();
????????stopwatch.start("自動(dòng)生成key表任務(wù)開(kāi)始");
????????long?start1?=?System.currentTimeMillis();
????????if?(CollectionUtil.isNotEmpty(insertData))?{
????????????boolean?insertResult?=?jdbcTemplateService.insert(insertSql,?insertData,?false);
????????????System.out.println(insertResult);
????????}
????????long?end1?=?System.currentTimeMillis();
????????System.out.println("auto?key消耗的時(shí)間:"?+?(end1?-?start1));
????????stopwatch.stop();
????????/**
?????????*?uudID的key
?????????*/
????????final?String?insertSql2?=?"INSERT?INTO?user_uuid(id,user_id,user_name,sex,address,city,email,state)?VALUES(?,?,?,?,?,?,?,?)";
????????List?insertData2?=?uuidKeyTableService.getInsertData();
????????stopwatch.start("UUID的key表任務(wù)開(kāi)始");
????????long?begin?=?System.currentTimeMillis();
????????if?(CollectionUtil.isNotEmpty(insertData))?{
????????????boolean?insertResult?=?jdbcTemplateService.insert(insertSql2,?insertData2,?true);
????????????System.out.println(insertResult);
????????}
????????long?over?=?System.currentTimeMillis();
????????System.out.println("UUID?key消耗的時(shí)間:"?+?(over?-?begin));
????????stopwatch.stop();
????????/**
?????????*?隨機(jī)的long值key
?????????*/
????????final?String?insertSql3?=?"INSERT?INTO?user_random_key(id,user_id,user_name,sex,address,city,email,state)?VALUES(?,?,?,?,?,?,?,?)";
????????List?insertData3?=?randomKeyTableService.getInsertData();
????????stopwatch.start("隨機(jī)的long值key表任務(wù)開(kāi)始");
????????Long?start?=?System.currentTimeMillis();
????????if?(CollectionUtil.isNotEmpty(insertData))?{
????????????boolean?insertResult?=?jdbcTemplateService.insert(insertSql3,?insertData3,?true);
????????????System.out.println(insertResult);
????????}
????????Long?end?=?System.currentTimeMillis();
????????System.out.println("隨機(jī)key任務(wù)消耗時(shí)間:"?+?(end?-?start));
????????stopwatch.stop();
????????String?result?=?stopwatch.prettyPrint();
????????System.out.println(result);
????}
程序?qū)懭虢Y(jié)果
效率測(cè)試結(jié)果
使用uuid和自增id的索引結(jié)構(gòu)對(duì)比
使用自增id的內(nèi)部結(jié)構(gòu)
下一條記錄就會(huì)寫(xiě)入新的頁(yè)中，一旦數(shù)據(jù)按照這種順序的方式加載，主鍵頁(yè)就會(huì)近乎于順序的記錄填滿，提升了頁(yè)面的最大填充率，不會(huì)有頁(yè)的浪費(fèi)
新插入的行一定會(huì)在原有的最大數(shù)據(jù)行下一行,mysql定位和尋址很快，不會(huì)為計(jì)算新行的位置而做出額外的消耗
減少了頁(yè)分裂和碎片的產(chǎn)生
使用uuid的索引內(nèi)部結(jié)構(gòu)
寫(xiě)入的目標(biāo)頁(yè)很可能已經(jīng)刷新到磁盤(pán)上并且從緩存上移除，或者還沒(méi)有被加載到緩存中，innodb在插入之前不得不先找到并從磁盤(pán)讀取目標(biāo)頁(yè)到內(nèi)存中，這將導(dǎo)致大量的隨機(jī)IO
因?yàn)閷?xiě)入是亂序的,innodb不得不頻繁的做頁(yè)分裂操作,以便為新的行分配空間,頁(yè)分裂導(dǎo)致移動(dòng)大量的數(shù)據(jù)，一次插入最少需要修改三個(gè)頁(yè)以上
由于頻繁的頁(yè)分裂，頁(yè)會(huì)變得稀疏并被不規(guī)則的填充，最終會(huì)導(dǎo)致數(shù)據(jù)會(huì)有碎片
使用自增id的缺點(diǎn)
別人一旦爬取你的數(shù)據(jù)庫(kù),就可以根據(jù)數(shù)據(jù)庫(kù)的自增id獲取到你的業(yè)務(wù)增長(zhǎng)信息，很容易分析出你的經(jīng)營(yíng)情況
對(duì)于高并發(fā)的負(fù)載，innodb在按主鍵進(jìn)行插入的時(shí)候會(huì)造成明顯的鎖爭(zhēng)用，主鍵的上界會(huì)成為爭(zhēng)搶的熱點(diǎn)，因?yàn)樗械牟迦攵及l(fā)生在這里，并發(fā)插入會(huì)導(dǎo)致間隙鎖競(jìng)爭(zhēng)
Auto_Increment鎖機(jī)制會(huì)造成自增鎖的搶奪,有一定的性能損失
總結(jié)
好文章，我在看
評(píng)論
圖片
表情