在諸多的管理類(lèi)，辦公類(lèi)等系統(tǒng)中，樹(shù)形結(jié)構(gòu)展示隨處可見(jiàn)，以“部門(mén)”或"機(jī)構(gòu)"來(lái)說(shuō)，接觸過(guò)的同學(xué)應(yīng)該都知道，最終展示到頁(yè)面的效果就是層級(jí)結(jié)構(gòu)的那種，下圖隨機(jī)列舉了一個(gè)部門(mén)的樹(shù)型結(jié)構(gòu)展示圖

設(shè)計(jì)考慮因素

1、表結(jié)構(gòu)設(shè)計(jì)

稍稍有點(diǎn)開(kāi)發(fā)和表結(jié)構(gòu)設(shè)計(jì)經(jīng)驗(yàn)的同學(xué)，設(shè)計(jì)出這樣一張表，應(yīng)該很容易，只需要在depart表中，添加一個(gè)pid/字段即可滿(mǎn)足要求，參考下表：

CREATE TABLE `depart` (
  `depart_id` varchar(32) NOT NULL COMMENT '部門(mén)ID',
  `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT '組織父ID',
  `name` varchar(64) NOT NULL COMMENT '部門(mén)名稱(chēng)',
  `description` varchar(512) DEFAULT NULL COMMENT '部門(mén)描述',
  `code` varchar(64) DEFAULT NULL COMMENT '部門(mén)編碼',
  PRIMARY KEY (`depart_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、業(yè)務(wù)設(shè)計(jì)

上圖是一個(gè)通用的樹(shù)狀結(jié)構(gòu)示意圖，適合大多數(shù)的業(yè)務(wù)場(chǎng)景，以此為例，如果“部門(mén)”不是單獨(dú)的存在，與部門(mén)相關(guān)的業(yè)務(wù)主要包括下面幾點(diǎn)：

• 部門(mén)是否單獨(dú)的存在，可能與部門(mén)存在直接關(guān)聯(lián)的業(yè)務(wù)，比如：部門(mén)下面可以關(guān)聯(lián)用戶(hù)
• 部門(mén)的效果展示，加載部門(mén)樹(shù)形列表時(shí)候，是動(dòng)態(tài)加載呢？還是一次性返回呢？
• 和部門(mén)增刪改查相關(guān)的業(yè)務(wù)
• 替他關(guān)聯(lián)部門(mén)的業(yè)務(wù)，即部門(mén)是被關(guān)聯(lián)的業(yè)務(wù)對(duì)象

3、性能考慮

• 全量加載不可取，沒(méi)有辦法的最后考慮，層級(jí)一旦過(guò)深，數(shù)據(jù)量一旦過(guò)大，查詢(xún)性能將會(huì)成為噩夢(mèng)
• 動(dòng)態(tài)加載效果比較好，服務(wù)端開(kāi)銷(xiāo)和壓力相對(duì)全量加載小很多，但動(dòng)態(tài)加載的問(wèn)題也很明顯，層級(jí)一旦深了，用戶(hù)操作體驗(yàn)不佳
• 層級(jí)結(jié)構(gòu)的功能，對(duì)數(shù)據(jù)導(dǎo)入即excel導(dǎo)入功能實(shí)現(xiàn)的編碼復(fù)雜度增加

關(guān)于第一點(diǎn)，第二點(diǎn)再做幾點(diǎn)額外的補(bǔ)充，全量加載和動(dòng)態(tài)加載的實(shí)現(xiàn)都可以，在小編歷經(jīng)的項(xiàng)目或產(chǎn)品中都有見(jiàn)到，實(shí)在是要看產(chǎn)品的設(shè)計(jì)和客戶(hù)的要求，因?yàn)槿亢蛣?dòng)態(tài)加載的不同設(shè)計(jì)也會(huì)帶來(lái)與之相對(duì)結(jié)果

舉例來(lái)說(shuō)，全量加載的好處是，數(shù)據(jù)一次性的返回給頁(yè)面，頁(yè)面做了渲染之后存緩存，后續(xù)再次加載的時(shí)候速度非?？?，同時(shí)，類(lèi)似下面這種搜索效率就非常高，因?yàn)椴恍枰c接口交互啊

但問(wèn)題也隨之而來(lái)了，部門(mén)數(shù)據(jù)不是一成不變的，增刪改的操作也是常有的事情，設(shè)計(jì)成全量加載，意味著初次查詢(xún)的時(shí)候，一旦數(shù)據(jù)量超大，層級(jí)非常深，假如頁(yè)面還需要渲染部門(mén)下關(guān)聯(lián)的用戶(hù)數(shù)據(jù)時(shí)，這個(gè)對(duì)服務(wù)端的壓力就非常大了，稍有經(jīng)驗(yàn)的同學(xué)應(yīng)該能大概想到這個(gè)服務(wù)端的返回?cái)?shù)據(jù)結(jié)構(gòu)了吧

下面給出初步的實(shí)現(xiàn)思路

function(currentDepart_id){
  
  1、查找當(dāng)前部門(mén)
      DB ......
  2、查找當(dāng)前部門(mén)的子級(jí)部門(mén)
      DB ......
  
  3、以當(dāng)前部門(mén)的子部門(mén)列表為根基進(jìn)行遍歷，遞歸查詢(xún)，包裝返回?cái)?shù)據(jù)
      DB ......
}

從以上的代碼實(shí)現(xiàn)來(lái)看，數(shù)據(jù)量上去之后，預(yù)估查詢(xún)將會(huì)成為性能瓶頸，而且在小編的項(xiàng)目開(kāi)發(fā)中，做過(guò)類(lèi)似的測(cè)試，3個(gè)層級(jí)，每層1000條數(shù)據(jù)（未計(jì)算部門(mén)下關(guān)聯(lián)用戶(hù)的數(shù)據(jù)加載），在4核16G的服務(wù)器上（CPU性能普通），完成一次全量的數(shù)據(jù)加載平均在3秒左右，這個(gè)對(duì)于B端的產(chǎn)品，這種設(shè)計(jì)加上這種延時(shí)，用戶(hù)還能接受（1000個(gè)部門(mén)，這種數(shù)據(jù)量是比較大的了）

上面分析到，全量加載的性能瓶頸在于數(shù)據(jù)庫(kù)的IO，試想，查詢(xún)的時(shí)候，從頂級(jí)節(jié)點(diǎn)或者某個(gè)節(jié)點(diǎn)算起，數(shù)據(jù)量越大，層級(jí)越深，查詢(xún)的次數(shù)就越多，IO的開(kāi)銷(xiāo)自然就越大

解決的辦法是什么呢？實(shí)踐過(guò)程中，有2個(gè)經(jīng)驗(yàn)可以參考：

• 設(shè)計(jì)合理的緩存存儲(chǔ)結(jié)構(gòu)
• 改進(jìn)表結(jié)構(gòu)

關(guān)于第一點(diǎn)，也是大家容易想到的，但如何設(shè)計(jì)才比較合理呢？以下面的這張圖為例，我們可以考慮以非葉子節(jié)點(diǎn)為key，而葉子節(jié)點(diǎn)下面的集合為value，將所有的value存入一個(gè)redis的集合中，這種考慮來(lái)源于實(shí)際業(yè)務(wù)中，用戶(hù)的需求驗(yàn)證，即真正那些具有實(shí)際意義的部門(mén)或機(jī)構(gòu)數(shù)據(jù)都分布在葉子節(jié)點(diǎn)上面

如此一來(lái)，編碼的實(shí)現(xiàn)上面，也許可以改造成下面這樣，

1、部門(mén)新增
functiob add(params){
    
    1、depart入庫(kù)
        DB ......
    2、判斷當(dāng)前的depart的層級(jí)，是否葉子節(jié)點(diǎn)（是否即將成為葉子節(jié)點(diǎn)）
      if(葉子節(jié)點(diǎn)){
        3、尋找上級(jí)節(jié)點(diǎn)ID，并查詢(xún)r(jià)edis中的key
        4、取出上級(jí)key對(duì)應(yīng)的緩存集合，加入當(dāng)前新增的part_id
      } else {
        5、創(chuàng)建一個(gè)新的key，即一個(gè)新的緩存空集合，等待后續(xù)數(shù)據(jù)添加（也可以不創(chuàng)建）
      }
}

2、刪除部門(mén)
functiob delete(params){
    
    1、depart自身的刪除
        DB ......
    2、如果當(dāng)前部門(mén)下存在子集部門(mén)，是否需要一起刪除子部門(mén)（結(jié)合自身的產(chǎn)品業(yè)務(wù)）
        DB......
        獲取所有的非葉子節(jié)點(diǎn)集合
    3、假設(shè)第二步成立，那么還需要以當(dāng)前部門(mén)節(jié)點(diǎn)創(chuàng)建的key，并取出key中的list集合，一起進(jìn)行刪掉
        Redis操作
        拿到第二步中的所有非葉子節(jié)點(diǎn)集合，組裝成key，循環(huán)遍歷刪除key（內(nèi)存型操作，性能不是問(wèn)題，也可以做異步）
}

全量加載結(jié)合redis是突破性能瓶頸的關(guān)鍵步驟，但從上面的實(shí)現(xiàn)上看，從編碼的復(fù)雜性上確實(shí)有所提升，而且對(duì)開(kāi)發(fā)者的編碼要求有一定高度，但這種實(shí)現(xiàn)之后，可以說(shuō)很大程度上將會(huì)提升查詢(xún)的性能

優(yōu)化查詢(xún)性能的第二種考慮，表結(jié)構(gòu)的改造

不少同學(xué)有疑問(wèn)，表結(jié)構(gòu)的改造對(duì)于性能影響能有多大呢？說(shuō)出來(lái)可能你不信，模擬數(shù)據(jù)壓測(cè)的時(shí)候，不采用改造后的實(shí)現(xiàn)，利用 5個(gè)層級(jí)的部門(mén)，每個(gè)部門(mén)1000的數(shù)據(jù)量（我指的是每個(gè)層級(jí)的每個(gè)部門(mén)數(shù)據(jù)量都是1000，大家可以計(jì)算下數(shù)據(jù)總量），每個(gè)部門(mén)下關(guān)聯(lián)了500個(gè)用戶(hù)，這樣的數(shù)據(jù)量的最后表現(xiàn)是5分鐘左右

看來(lái)，數(shù)據(jù)量上去了之后，查詢(xún)壓力確實(shí)很大，利用改造后的設(shè)計(jì)和測(cè)試效果，最終同樣的數(shù)據(jù)表現(xiàn)，平均在15~20秒之間，這個(gè)直接是10倍多的提升，或許在我說(shuō)出答案之前，也有不少同學(xué)用過(guò)，但是沒(méi)有真正體會(huì)到它的妙處

在本文開(kāi)始的表結(jié)構(gòu)基礎(chǔ)上面，我們加一個(gè)path字段，這樣改造后的表如下：

CREATE TABLE `depart` (
  `depart_id` varchar(32) NOT NULL COMMENT '部門(mén)ID',
  `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT '組織父ID',
  `name` varchar(64) NOT NULL COMMENT '部門(mén)名稱(chēng)',
  `description` varchar(512) DEFAULT NULL COMMENT '部門(mén)描述',
  `code` varchar(64) DEFAULT NULL COMMENT '部門(mén)編碼',
  PRIMARY KEY (`depart_id`),
  `path` varchar(128) NOT NULL COMMENT '部門(mén)路徑',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

這個(gè)path字段意義重大，通常的表現(xiàn)是，從第一個(gè)層級(jí)開(kāi)始，每個(gè)層級(jí)假設(shè)最多可容納10000個(gè)部門(mén)，這樣第一層的數(shù)據(jù)大概長(zhǎng)這樣， 00001 ，00002，00003 … 往上依次累加，而第二層級(jí)，假如我們?cè)?0002這個(gè)部門(mén)下新增第二層級(jí)的部門(mén)時(shí)，數(shù)據(jù)表現(xiàn)大概長(zhǎng)這樣， 00002/00001 , 00002/00002 , 00002/00003 …往上依次累加

那么更深的層級(jí)，我就算不舉例想必大家也能自行列舉出后面的結(jié)構(gòu)來(lái)

這么做有什么好處呢？

我們知道,mysql是支持正則表達(dá)式函數(shù)的，還有就是like，試想，我們要想一次性查詢(xún)出從某個(gè)層級(jí)開(kāi)始下面的所有的層級(jí)數(shù)據(jù)時(shí)，假如沒(méi)有path這個(gè)字段，會(huì)怎么做呢？很明顯，就是上文所說(shuō)的通過(guò)遞歸了

但是有了path字段之后，我們可以直接利用mysql的正則表達(dá)式函數(shù),，仍以上面的數(shù)據(jù)為例，通過(guò)下面的這兩種sql，一次性的可以將一級(jí)部門(mén)（測(cè)試）這條數(shù)據(jù)的所有子集數(shù)據(jù)全部查出來(lái)，這樣一來(lái)，可以說(shuō)大大減少了與數(shù)據(jù)庫(kù)的交互次數(shù)

此種實(shí)現(xiàn)容易踩坑，或者實(shí)際操作中比較容易出問(wèn)題的地方在路徑規(guī)則的生成上面，通常需要提前自定義一個(gè)函數(shù)，專(zhuān)門(mén)用戶(hù)生成path，只要確保生成的path字段數(shù)據(jù)準(zhǔn)確無(wú)誤，這種實(shí)現(xiàn)從優(yōu)化查詢(xún)的性能提升上面，是很大的突破，小編所在的開(kāi)發(fā)項(xiàng)目中，使用的便是這種方式

function generatePath(pid){
    
  1、pid是否為頂級(jí)
  2、獲取父級(jí)部門(mén)的depart
  3、列舉出父級(jí)部門(mén)下與當(dāng)前即將新增的部門(mén)同級(jí)的所有path字段
  4、取出第三步中的path最大值
  5、根據(jù)第四步的path最大值生成新的path
}

另外一個(gè)比較難啃的業(yè)務(wù)是，以path字段的設(shè)計(jì)之后，做部門(mén)數(shù)據(jù)的excel導(dǎo)入時(shí)，這個(gè)path的處理仍然是個(gè)比較復(fù)雜的實(shí)現(xiàn)點(diǎn)，這一點(diǎn)留待大家思考。

以上探討了全量加載下，從業(yè)務(wù)實(shí)現(xiàn)到代碼設(shè)計(jì)層面的優(yōu)化 ， 以及表結(jié)構(gòu)設(shè)計(jì)層面優(yōu)化的2個(gè)方面做了比較深入的探討，而動(dòng)態(tài)加載的實(shí)現(xiàn)，相對(duì)來(lái)說(shuō)，可以說(shuō)在上面這兩種實(shí)現(xiàn)方案的基礎(chǔ)上，稍作引用即可，難度更小

總結(jié)下來(lái)，這里推薦一個(gè)關(guān)于這種帶有層級(jí)結(jié)構(gòu)形狀的業(yè)務(wù)設(shè)計(jì)上的最佳實(shí)踐，

表結(jié)構(gòu)上，采用path字段數(shù)據(jù)加載上，盡量使用動(dòng)態(tài)加載如果部門(mén)（層級(jí)結(jié)構(gòu)的業(yè)務(wù)）變動(dòng)不大，可以考慮引入緩存，具體實(shí)踐參考本文上面所說(shuō)

到此這篇關(guān)于淺談mysql 樹(shù)形結(jié)構(gòu)表設(shè)計(jì)與優(yōu)化的文章就介紹到這了,更多相關(guān)mysql 樹(shù)形結(jié)構(gòu)表優(yōu)化內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

國(guó)外穩(wěn)定服務(wù)器

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。