色哟哟视频在线观看-色哟哟视频在线-色哟哟欧美15最新在线-色哟哟免费在线观看-国产l精品国产亚洲区在线观看-国产l精品国产亚洲区久久

0
  • 聊天消息
  • 系統消息
  • 評論與回復
登錄后你可以
  • 下載海量資料
  • 學習在線課程
  • 觀看技術視頻
  • 寫文章/發帖/加入社區
會員中心
創作中心

完善資料讓更多小伙伴認識你,還能領取20積分哦,立即完善>

3天內不再提示

id的機制不同在mysql的索引結構以及優缺點

jf_ro2CN3Fa ? 來源:芋道源碼 ? 2023-06-30 10:19 ? 次閱讀

前言

一、mysql和程序實例

1.1.要說明這個問題,我們首先來建立三張表

1.2.光有理論不行,直接上程序,使用spring的jdbcTemplate來實現增查測試:

1.3.程序寫入結果

1.4.效率測試結果

二、使用uuid和自增id的索引結構對比

2.1.使用自增id的內部結構

2.2.使用uuid的索引內部結構

2.3.使用自增id的缺點

三、總結

65b32dc2-16e6-11ee-962d-dac502259ad0.jpg

前言

在mysql中設計表的時候,mysql官方推薦不要使用uuid或者不連續不重復的雪花id(long形且唯一,單機遞增),而是推薦連續自增的主鍵id,官方的推薦是auto_increment,那么為什么不建議采用uuid,使用uuid究竟有什么壞處?

本篇博客我們就來分析這個問題,探討一下內部的原因。

本篇博客的目錄

mysql程序實例 使用uuid和自增id的索引結構對比 總結

基于 Spring Boot + MyBatis Plus + Vue & Element 實現的后臺管理系統 + 用戶小程序,支持 RBAC 動態權限、多租戶、數據權限、工作流、三方登錄、支付、短信、商城等功能

項目地址:https://github.com/YunaiV/ruoyi-vue-pro

視頻教程:https://doc.iocoder.cn/video/

一、mysql和程序實例

1.1.要說明這個問題,我們首先來建立三張表

分別是user_auto_key,user_uuid,user_random_key,分別表示自動增長的主鍵,uuid作為主鍵,隨機key作為主鍵,其它我們完全保持不變.

根據控制變量法,我們只把每個表的主鍵使用不同的策略生成,而其他的字段完全一樣,然后測試一下表的插入速度和查詢速度:

注:這里的隨機key其實是指用雪花算法算出來的前后不連續不重復無規律的id:一串18位長度的long值

id自動生成表:

65d4119a-16e6-11ee-962d-dac502259ad0.jpg

用戶uuid表

65ef7ba6-16e6-11ee-962d-dac502259ad0.jpg

隨機主鍵表:

66043e56-16e6-11ee-962d-dac502259ad0.png

1.2.光有理論不行,直接上程序,使用spring的jdbcTemplate來實現增查測試:

技術框架:springboot+jdbcTemplate+junit+hutool,程序的原理就是連接自己的測試數據庫,然后在相同的環境下寫入同等數量的數據,來分析一下insert插入的時間來進行綜合其效率,為了做到最真實的效果,所有的數據采用隨機生成,比如名字、郵箱、地址都是隨機生成。

packagecom.wyq.mysqldemo;
importcn.hutool.core.collection.CollectionUtil;
importcom.wyq.mysqldemo.databaseobject.UserKeyAuto;
importcom.wyq.mysqldemo.databaseobject.UserKeyRandom;
importcom.wyq.mysqldemo.databaseobject.UserKeyUUID;
importcom.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
importcom.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
importcom.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
importcom.wyq.mysqldemo.util.JdbcTemplateService;
importorg.junit.jupiter.api.Test;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.boot.test.context.SpringBootTest;
importorg.springframework.util.StopWatch;
importjava.util.List;
@SpringBootTest
classMysqlDemoApplicationTests{

@Autowired
privateJdbcTemplateServicejdbcTemplateService;

@Autowired
privateAutoKeyTableServiceautoKeyTableService;

@Autowired
privateUUIDKeyTableServiceuuidKeyTableService;

@Autowired
privateRandomKeyTableServicerandomKeyTableService;


@Test
voidtestDBTime(){

StopWatchstopwatch=newStopWatch("執行sql時間消耗");


/**
*auto_incrementkey任務
*/
finalStringinsertSql="INSERTINTOuser_key_auto(user_id,user_name,sex,address,city,email,state)VALUES(?,?,?,?,?,?,?)";

ListinsertData=autoKeyTableService.getInsertData();
stopwatch.start("自動生成key表任務開始");
longstart1=System.currentTimeMillis();
if(CollectionUtil.isNotEmpty(insertData)){
booleaninsertResult=jdbcTemplateService.insert(insertSql,insertData,false);
System.out.println(insertResult);
}
longend1=System.currentTimeMillis();
System.out.println("autokey消耗的時間:"+(end1-start1));

stopwatch.stop();


/**
*uudID的key
*/
finalStringinsertSql2="INSERTINTOuser_uuid(id,user_id,user_name,sex,address,city,email,state)VALUES(?,?,?,?,?,?,?,?)";

ListinsertData2=uuidKeyTableService.getInsertData();
stopwatch.start("UUID的key表任務開始");
longbegin=System.currentTimeMillis();
if(CollectionUtil.isNotEmpty(insertData)){
booleaninsertResult=jdbcTemplateService.insert(insertSql2,insertData2,true);
System.out.println(insertResult);
}
longover=System.currentTimeMillis();
System.out.println("UUIDkey消耗的時間:"+(over-begin));

stopwatch.stop();


/**
*隨機的long值key
*/
finalStringinsertSql3="INSERTINTOuser_random_key(id,user_id,user_name,sex,address,city,email,state)VALUES(?,?,?,?,?,?,?,?)";
ListinsertData3=randomKeyTableService.getInsertData();
stopwatch.start("隨機的long值key表任務開始");
Longstart=System.currentTimeMillis();
if(CollectionUtil.isNotEmpty(insertData)){
booleaninsertResult=jdbcTemplateService.insert(insertSql3,insertData3,true);
System.out.println(insertResult);
}
Longend=System.currentTimeMillis();
System.out.println("隨機key任務消耗時間:"+(end-start));
stopwatch.stop();


Stringresult=stopwatch.prettyPrint();
System.out.println(result);
}

1.3.程序寫入結果

user_key_auto寫入結果:

661f2fd6-16e6-11ee-962d-dac502259ad0.png

user_random_key寫入結果:

66459d6a-16e6-11ee-962d-dac502259ad0.png

user_uuid表寫入結果:

66989fc4-16e6-11ee-962d-dac502259ad0.png

1.4.效率測試結果

66f5c6e0-16e6-11ee-962d-dac502259ad0.png

在已有數據量為130W的時候:我們再來測試一下插入10w數據,看看會有什么結果:

670c64a4-16e6-11ee-962d-dac502259ad0.png

可以看出在數據量100W左右的時候,uuid的插入效率墊底,并且在后序增加了130W的數據,uudi的時間又直線下降。

時間占用量總體可以打出的效率排名為:auto_key>random_key>uuid,uuid的效率最低,在數據量較大的情況下,效率直線下滑。那么為什么會出現這樣的現象呢?帶著疑問,我們來探討一下這個問題:

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實現的后臺管理系統 + 用戶小程序,支持 RBAC 動態權限、多租戶、數據權限、工作流、三方登錄、支付、短信、商城等功能

項目地址:https://github.com/YunaiV/yudao-cloud

視頻教程:https://doc.iocoder.cn/video/

二、使用uuid和自增id的索引結構對比

2.1.使用自增id的內部結構

67213ca8-16e6-11ee-962d-dac502259ad0.png

自增的主鍵的值是順序的,所以Innodb把每一條記錄都存儲在一條記錄的后面。當達到頁面的最大填充因子時候(innodb默認的最大填充因子是頁大小的15/16,會留出1/16的空間留作以后的 修改):

①下一條記錄就會寫入新的頁中,一旦數據按照這種順序的方式加載,主鍵頁就會近乎于順序的記錄填滿,提升了頁面的最大填充率,不會有頁的浪費

②新插入的行一定會在原有的最大數據行下一行,mysql定位和尋址很快,不會為計算新行的位置而做出額外的消耗

③減少了頁分裂和碎片的產生

2.2.使用uuid的索引內部結構

67332436-16e6-11ee-962d-dac502259ad0.jpg

因為uuid相對順序的自增id來說是毫無規律可言的,新行的值不一定要比之前的主鍵的值要大,所以innodb無法做到總是把新行插入到索引的最后,而是需要為新行尋找新的合適的位置從而來分配新的空間。

這個過程需要做很多額外的操作,數據的毫無順序會導致數據分布散亂,將會導致以下的問題:

①寫入的目標頁很可能已經刷新到磁盤上并且從緩存上移除,或者還沒有被加載到緩存中,innodb在插入之前不得不先找到并從磁盤讀取目標頁到內存中,這將導致大量的隨機IO

②因為寫入是亂序的,innodb不得不頻繁的做頁分裂操作,以便為新的行分配空間,頁分裂導致移動大量的數據,一次插入最少需要修改三個頁以上

③由于頻繁的頁分裂,頁會變得稀疏并被不規則的填充,最終會導致數據會有碎片

在把隨機值(uuid和雪花id)載入到聚簇索引(innodb默認的索引類型)以后,有時候會需要做一次OPTIMEIZE TABLE來重建表并優化頁的填充,這將又需要一定的時間消耗。

結論:使用innodb應該盡可能的按主鍵的自增順序插入,并且盡可能使用單調的增加的聚簇鍵的值來插入新行

2.3.使用自增id的缺點

那么使用自增的id就完全沒有壞處了嗎?并不是,自增id也會存在以下幾點問題:

①別人一旦爬取你的數據庫,就可以根據數據庫的自增id獲取到你的業務增長信息,很容易分析出你的經營情況

②對于高并發的負載,innodb在按主鍵進行插入的時候會造成明顯的鎖爭用,主鍵的上界會成為爭搶的熱點,因為所有的插入都發生在這里,并發插入會導致間隙鎖競爭

③Auto_Increment鎖機制會造成自增鎖的搶奪,有一定的性能損失

附:Auto_increment的鎖爭搶問題,如果要改善需要調優innodb_autoinc_lock_mode的配置

三、總結

本篇博客首先從開篇的提出問題,建表到使用jdbcTemplate去測試不同id的生成策略在大數據量的數據插入表現,然后分析了id的機制不同在mysql的索引結構以及優缺點,深入的解釋了為何uuid和隨機不重復id在數據插入中的性能損耗,詳細的解釋了這個問題。

在實際的開發中還是根據mysql的官方推薦最好使用自增id,mysql博大精深,內部還有很多值得優化的點需要我們學習。

聲明:本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人,不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用,如有內容侵權或者其他違規問題,請聯系本站處理。 舉報投訴
  • 磁盤
    +關注

    關注

    1

    文章

    375

    瀏覽量

    25201
  • 數據庫
    +關注

    關注

    7

    文章

    3794

    瀏覽量

    64362
  • MySQL
    +關注

    關注

    1

    文章

    804

    瀏覽量

    26531

原文標題:用雪花 id 和 uuid 做 MySQL 主鍵,被領導懟了

文章出處:【微信號:芋道源碼,微信公眾號:芋道源碼】歡迎添加關注!文章轉載請注明出處。

收藏 人收藏

    評論

    相關推薦

    MySQL索引的創建與刪除

    MySQL——索引技巧以及注意事項
    發表于 10-31 09:27

    mysql索引使用技巧有哪些?

    mysql索引使用技巧
    發表于 05-20 06:09

    Oracle與MySQL優缺點和使用區別

    關系型數據庫(Oracle與MySQL優缺點、使用區別)
    發表于 06-04 16:48

    MySQL索引使用優化和規范

    MySQL - 索引使用優化和規范
    發表于 06-15 16:01

    闡述FreeRTOS系統中的機制及在應用中的優缺點

    :FreeRTOS是一個源碼公開的免費的嵌入式實時操作系統,通過研究其內核可以更好地理解嵌入式操作系統的實現原理.本文主要闡述FreeRTOS系統中的任務調度機制、時間管理機制、任務管理機制
    發表于 12-20 06:34

    DMA的傳輸過程與優缺點

    系列索引:《嵌入式系統原理與應用》 | 嵌入式系統 重點知識梳理目錄DMA的定義及傳輸要素DMA的傳輸過程DMA的優缺點及其適用場景STM32中DMA的特點STM32中DMA的優先級機制STM32中
    發表于 12-22 06:15

    開關電源拓撲結構優缺點

    開關電源的相關知識學習教材資料——開關電源拓撲結構優缺點
    發表于 09-20 16:10 ?0次下載

    MySQL索引使用原則

    一般來說, MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結構來存儲的,也就是所有實際需要的數據都存放于 Tree 的 Leaf Node(葉子節點) ,而且
    的頭像 發表于 02-11 15:17 ?2715次閱讀
    <b class='flag-5'>MySQL</b><b class='flag-5'>索引</b>使用原則

    MySQL索引的使用問題

    一、前言 在MySQL中進行SQL優化的時候,經常會在一些情況下,對MySQL能否利用索引有一些迷惑。譬如:1、MySQL 在遇到范圍查詢條件的時候就停止匹配了,那么到底是哪些范圍條件
    的頭像 發表于 01-06 16:13 ?1605次閱讀

    MySQL高級進階:索引優化

    MySQL官方對于索引的定義:索引是幫助MySQL高效獲取數據的數據結構
    的頭像 發表于 06-11 11:13 ?569次閱讀
    <b class='flag-5'>MySQL</b>高級進階:<b class='flag-5'>索引</b>優化

    MySQL為什么選擇B+樹作為索引結構

    MySQL中,無論是Innodb還是MyIsam,都使用了B+樹作索引結構(這里不考慮hash等其他索引)。本文將從最普通的二叉查找樹開始,逐步說明各種樹解決的問題
    的頭像 發表于 07-20 11:28 ?948次閱讀
    <b class='flag-5'>MySQL</b>為什么選擇B+樹作為<b class='flag-5'>索引</b><b class='flag-5'>結構</b>?

    MySQL索引的常用知識點

    索引結構:B+樹 索引其實是一種數據結構 注意B+樹是MySQL索引默認的
    的頭像 發表于 09-30 16:43 ?455次閱讀

    索引是什么意思 優缺點有哪些

    的數據結構,以協助快速查詢、更新數據庫表中數據。索引的實現通常使用B樹及其變種B+樹。更通俗的說,索引就相當于目錄。為了方便查找書中的內容,通過對內容建立索引形成目錄。而且
    的頭像 發表于 10-09 10:19 ?2920次閱讀

    導致MySQL索引失效的情況以及相應的解決方法

    導致MySQL索引失效的情況以及相應的解決方法? MySQL索引的目的是提高查詢效率,但有些情況下索引
    的頭像 發表于 12-28 10:01 ?752次閱讀

    一文了解MySQL索引機制

    的呢?一起靜下心來,耐心看完這篇文章吧,干貨不啰嗦,相信你一定會有所收獲。 一、索引模型 模型也就是數據結構,常見的三種模型分別是哈希表、有序數組和搜索樹。 了解MySQL的朋友已經知道,現在
    的頭像 發表于 07-25 14:05 ?283次閱讀
    一文了解<b class='flag-5'>MySQL</b><b class='flag-5'>索引</b><b class='flag-5'>機制</b>
    主站蜘蛛池模板: 三级黄色在线视频| 精品亚洲永久免费精品| 99精品免费久久久久久久久蜜桃| 亚洲国产精品久久无套麻豆| 特级毛片AAAAAA| 日韩av无码在线直播| 青柠在线视频| 青青热久精品国产亚洲AV无码| 蜜桃传媒视频| 暖暖 免费 高清 日本视频5| 美女强奷到抽搐在线播放| 免费观看成人www精品视频在线| 久久综合给会久久狠狠狠| 久久午夜夜伦痒痒想咳嗽P| 久久AV喷吹AV高潮欧美| 久久国产免费观看精品1| 久久青青无码AV亚洲黑人| 久久精品影院永久网址| 久久亚洲国产成人影院| 麻豆XXXX乱女少妇精品| 嫩草AV久久伊人妇女| 秋霞电影院兔费理论观频84mb| 全黄h全肉细节全文| 天上人间影院久久国产| 亚洲不卡视频在线| 野花韩国高清完整版在线| 又黄又湿免费高清视频| 97国产成人精品视频| www.免费视频| 国产精品成人免费| 国自产精品手机在线视频| 久久精品热播在线看| 妹妹我要操| 日本精品久久久久中文字幕| 水蜜桃亚洲一二三四在线| 亚洲国产欧美在线看片| 在线观看国产亚洲| WWW国产亚洲精品久久| 国产跪地吃黄金喝圣水合集| 黑丝美女被人操| 免费看国产精品麻豆|