27手把手帶你設(shè)計(jì)一個(gè)連鎖超市信息系統(tǒng)數(shù)據(jù)庫(kù)上

下載下載 27|手把手帶你設(shè)計(jì)一個(gè)完整的連鎖超市信息系統(tǒng)數(shù)據(jù)庫(kù)（上2021-05-1515:07大小從創(chuàng)建第一個(gè)MySQL數(shù)據(jù)庫(kù)開(kāi)始到現(xiàn)在，我們已經(jīng)學(xué)完了MySL的核心操作。最后這兩節(jié)課，我想帶你實(shí)際設(shè)計(jì)一個(gè)超市信息系統(tǒng)的數(shù)據(jù)庫(kù)。畢竟，設(shè)計(jì)數(shù)據(jù)庫(kù)很考驗(yàn)我們綜合運(yùn)用單個(gè)技術(shù)點(diǎn)的能力。所以，通過(guò)這個(gè)項(xiàng)目，我不僅會(huì)帶你把前面的內(nèi)容串聯(lián)起來(lái)，而且還會(huì)教你設(shè)計(jì)數(shù)據(jù)庫(kù)的通用思路。為什么選擇超市項(xiàng)目呢？一方面呢，超市的場(chǎng)景與我們的日常生活密切相關(guān)，你比較容易理解其中的業(yè)務(wù)邏輯，另一方面，超市的業(yè)務(wù)又相當(dāng)復(fù)雜，幾乎能用到我們學(xué)到的所有識(shí)點(diǎn)，利于我們對(duì)前面學(xué)過(guò)的內(nèi)容進(jìn)行整合。今天，我就帶你一起，從需求分析開(kāi)始入手，一直到容災(zāi)和備份，完成一個(gè)全流程的連鎖超市數(shù)據(jù)庫(kù)設(shè)計(jì)。這節(jié)課，我會(huì)主要給你講解需求分析、ER模型、分庫(kù)分表和數(shù)據(jù)庫(kù)設(shè)計(jì)方案。我們做項(xiàng)目，首先要從大處著眼，把握方向，這樣才不容易出現(xiàn)大的偏差。下節(jié)隨著互聯(lián)網(wǎng)使用的日益廣泛，傳統(tǒng)的桌面管理系統(tǒng)的數(shù)據(jù)不能互通、資源利用率低等弊端越來(lái)越明顯，顯然不能滿(mǎn)足用戶(hù)的需求了。因此，我們需要開(kāi)發(fā)一款基于云服務(wù)的連鎖超市管理信息系統(tǒng)。具體的要求是：用戶(hù)賬號(hào)開(kāi)通即可使用，所以必然要設(shè)計(jì)分層的數(shù)據(jù)結(jié)構(gòu)；數(shù)據(jù)要部署在云端，所以必然要使用云。根據(jù)這些要求，我們可以設(shè)計(jì)一個(gè)基于云服務(wù)的2層數(shù)據(jù)結(jié)構(gòu)，這個(gè)結(jié)構(gòu)的示意圖如下所示：其次，我們來(lái)看下數(shù)據(jù)結(jié)構(gòu)層面，主要有2層。第一層是商戶(hù)。每個(gè)入駐的商戶(hù)都有一個(gè)組號(hào)，所有與這個(gè)商戶(hù)有關(guān)的數(shù)據(jù)，通過(guò)這個(gè)組號(hào)進(jìn)行識(shí)別。第二層是分支機(jī)構(gòu)。分支機(jī)構(gòu)從屬于商戶(hù)，相同商戶(hù)的分支機(jī)構(gòu)有相同的組號(hào)。分支機(jī)構(gòu)分為幾種，包括總部、門(mén)店、配送中心等。門(mén)店又分為直營(yíng)店和加盟店。每個(gè)分支機(jī)構(gòu)有一個(gè)機(jī)構(gòu)編號(hào)，同一分支機(jī)構(gòu)的數(shù)據(jù)，有相同的組號(hào)和機(jī)構(gòu)編號(hào)。這個(gè)圖展示了應(yīng)用的3AppApp、小程序，以及通過(guò)瀏覽器訪問(wèn)的后數(shù)據(jù)層：MySQLAppApp，小程序等與數(shù)據(jù)庫(kù)設(shè)計(jì)無(wú)關(guān)，我就不多說(shuō)了。下面我盤(pán)點(diǎn)，簡(jiǎn)單來(lái)說(shuō)，就是把超市倉(cāng)庫(kù)里的商品都數(shù)一遍，然后跟電腦里的庫(kù)存數(shù)比對(duì)，看看有沒(méi)有不對(duì)的地方。實(shí)際數(shù)出來(lái)的庫(kù)存數(shù)量叫做盤(pán)存數(shù)量，電腦中的庫(kù)存數(shù)量叫做結(jié)存數(shù)量，比對(duì)的結(jié)果叫做盈虧數(shù)量。要是盤(pán)存數(shù)量比結(jié)存數(shù)量多，叫盤(pán)盈，否則叫做盤(pán)虧。盤(pán)點(diǎn)作業(yè)一般都在晚上門(mén)店?duì)I業(yè)結(jié)束以后進(jìn)行。這也很好理解，畢竟，在白天營(yíng)業(yè)的過(guò)程中，商品不斷被顧客取走，又不斷得到補(bǔ)充，庫(kù)存處于一種變化狀態(tài)，無(wú)法獲取準(zhǔn)確數(shù)據(jù)。盤(pán)點(diǎn)業(yè)務(wù)都是在門(mén)店進(jìn)行，由員工實(shí)際操作，對(duì)倉(cāng)庫(kù)中的商品進(jìn)行清點(diǎn)。因此，盤(pán)點(diǎn)業(yè)務(wù)模塊中肯定要包含員工、門(mén)店、倉(cāng)庫(kù)、商品等實(shí)體。這個(gè)時(shí)候，我們就可以使用ER模型這個(gè)工具，來(lái)理清盤(pán)點(diǎn)模塊的業(yè)務(wù)邏輯。ER這個(gè)ER模型中包括了5ER511接下來(lái)，我們?cè)俜治鲆幌逻@5除此之外，還有2ERER你還記得在第 講里學(xué)的轉(zhuǎn)換規(guī)則嗎？強(qiáng)實(shí)體和弱實(shí)體轉(zhuǎn)換成獨(dú)立的數(shù)據(jù)表，多對(duì)多的關(guān)系轉(zhuǎn)換成獨(dú)立的數(shù)據(jù)表，1對(duì)多的關(guān)系轉(zhuǎn)換成外鍵約束。2這樣一來(lái)，我們就把ER21在前面的課程中，每節(jié)課我們都是以具體技術(shù)點(diǎn)為核心展開(kāi)的。而分庫(kù)分表，只有在進(jìn)行數(shù)據(jù)庫(kù)系統(tǒng)整體設(shè)計(jì)的階段才會(huì)用到。所以，今天我就結(jié)合咱們這個(gè)項(xiàng)目的系統(tǒng)設(shè)計(jì)，來(lái)給你具體講一講如何進(jìn)行分庫(kù)分表。為什么要分庫(kù)分表呢？當(dāng)數(shù)據(jù)量足夠大的時(shí)候，即便我們把索引都建好，系統(tǒng)資源調(diào)優(yōu)到極致，仍然有可能遇到運(yùn)行緩慢、CPU使用率居高不下的情況。因?yàn)閱蝹€(gè)數(shù)據(jù)庫(kù)中單個(gè)表的數(shù)據(jù)量高到一定程度，超過(guò)了系統(tǒng)的承載能力。面對(duì)這種情況，我們有2種選擇：一種是購(gòu)買(mǎi)更多的資源，增加內(nèi)存，增加CPU的算力，但是這樣會(huì)增加系統(tǒng)的成本。這個(gè)時(shí)候，我們就可以用另一種方法，也就是接下來(lái)我要講的分庫(kù)分表。所謂的分庫(kù)分表，其實(shí)就是把大的數(shù)據(jù)庫(kù)拆成小數(shù)據(jù)庫(kù)，把大表拆成小表，讓單一數(shù)據(jù)庫(kù)、單一數(shù)據(jù)表的數(shù)據(jù)量變小。這樣每次查詢(xún)時(shí)，需要掃描的數(shù)據(jù)量減少了，也就達(dá)到了提升查詢(xún)執(zhí)行效率的目的。2每個(gè)商戶(hù)都有一個(gè)自己的商品信息表，數(shù)據(jù)量比較大。所以，我們可以拆分下這個(gè)表。我們把經(jīng)常使用的字段條碼、名稱(chēng)和價(jià)格，拆分成商品常用信息表（demo.goos_o）；把剩下的字段，也就是規(guī)格和單位拆分成商品不常用信息表（demo.ood_f）。至于商戶(hù)表、門(mén)店表、員工表、倉(cāng)庫(kù)表，這些表的數(shù)據(jù)量有限，不需要拆分。庫(kù)存表、盤(pán)點(diǎn)單頭表和盤(pán)點(diǎn)單明細(xì)表，雖然數(shù)據(jù)量大，但是評(píng)估之后，我們發(fā)現(xiàn)字段的使用頻率都很高，拆分的價(jià)值不大，所以也不需要拆分。垂直分庫(kù)的意思是，把不同模塊的數(shù)據(jù)表分別存放到不同的數(shù)據(jù)庫(kù)中。這樣做的好處是，每個(gè)數(shù)據(jù)庫(kù)只保存特定模塊的數(shù)據(jù)，系統(tǒng)只有用到特定模塊的數(shù)據(jù)時(shí)，才會(huì)訪問(wèn)這個(gè)數(shù)據(jù)庫(kù)。這樣就減少了數(shù)據(jù)庫(kù)訪問(wèn)的次數(shù)，就相當(dāng)于是把數(shù)據(jù)訪問(wèn)的流量分散了。在這個(gè)圖中，數(shù)據(jù)不再存儲(chǔ)在一個(gè)數(shù)據(jù)庫(kù)中，而是根據(jù)業(yè)務(wù)模塊的不同，分別存儲(chǔ)在不同的數(shù)據(jù)庫(kù)中，比如銷(xiāo)售數(shù)據(jù)庫(kù)、庫(kù)存數(shù)據(jù)庫(kù)、營(yíng)運(yùn)數(shù)據(jù)庫(kù)、會(huì)員數(shù)據(jù)庫(kù)等。這樣一來(lái)，業(yè)務(wù)模塊可以主要與自己的數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)交互。業(yè)務(wù)內(nèi)的數(shù)據(jù)交互多了，業(yè)務(wù)與業(yè)務(wù)之間的數(shù)據(jù)交互就可以大大減少了。3留當(dāng)前正在處理的數(shù)據(jù)，可以提升效率，避免在一個(gè)不斷增長(zhǎng)的大表中進(jìn)行DML操作。拆分出來(lái)的盤(pán)點(diǎn)單頭歷史表（demo.incounteaist）與盤(pán)點(diǎn)單頭表類(lèi)似，不同之處是增加了驗(yàn)收人編號(hào)（confirmer）和驗(yàn)收日期（confirmaindate）。拆分出來(lái)的盤(pán)點(diǎn)單明細(xì)歷史表（demo.invcunteailshist）的字段則與盤(pán)點(diǎn)單明細(xì)表一樣。第二步，我們把組號(hào)大于500、小于1000的商戶(hù)數(shù)據(jù)，拆分到另外的數(shù)據(jù)表里進(jìn)行保新的數(shù)據(jù)庫(kù)可以在相同的服務(wù)器上，也可以在不同的服務(wù)器上。比如，我們可以把組號(hào)大于500、小于1000的用戶(hù)數(shù)據(jù)拆分出來(lái)，保存到新的服務(wù)器的數(shù)據(jù)庫(kù)中。不過(guò)，保存到新的服務(wù)器，也就意味著增加系統(tǒng)的開(kāi)銷(xiāo)。因此，我們可以以500個(gè)商戶(hù)為單位，每5005000這節(jié)課，我們一起設(shè)計(jì)了一個(gè)基于云服務(wù)的連鎖超市管理系統(tǒng)數(shù)據(jù)庫(kù)。你要重點(diǎn)掌握如何進(jìn)行需求分析、如何把分析的結(jié)果轉(zhuǎn)換成數(shù)據(jù)庫(kù)的設(shè)計(jì)，以及如何在總體設(shè)計(jì)的階段通過(guò)使用分庫(kù)分表使設(shè)計(jì)出來(lái)的數(shù)據(jù)庫(kù)能夠處理大量數(shù)據(jù)。在實(shí)際項(xiàng)目中，你要重點(diǎn)關(guān)注3第一，要充分理解項(xiàng)目需求。有的時(shí)候，客戶(hù)自己也不清楚自己的需求。這個(gè)時(shí)候，就需要你幫助客戶(hù)理清思路。你可以把客戶(hù)的需求用圖表等方式整理出來(lái)，再跟客戶(hù)一起討論。在這個(gè)階段，投入較多的時(shí)間是值得的，如果等系統(tǒng)開(kāi)發(fā)完成之后再改，成本就很高了。第二，使用ER分庫(kù)分表的策略，需要在設(shè)計(jì)階段完成。如果缺乏整體分庫(kù)分表的策略而匆忙上線，遇到瓶頸時(shí)再解決，花費(fèi)的成本要遠(yuǎn)遠(yuǎn)高于在設(shè)計(jì)階段投入的時(shí)間成本。分庫(kù)分表的策略，必然帶來(lái)開(kāi)發(fā)和運(yùn)維方面成本的提升，因此，你需要在設(shè)計(jì)階段就有一個(gè)整體規(guī)劃。比如，在類(lèi)的設(shè)計(jì)里面，用正則表達(dá)式計(jì)算訪問(wèn)的服務(wù)器、數(shù)據(jù)庫(kù)和數(shù)據(jù)表的名稱(chēng)。分庫(kù)分表一般適用于比較大的項(xiàng)目，如果你開(kāi)發(fā)的應(yīng)用數(shù)據(jù)量小，系統(tǒng)規(guī)模有限，團(tuán)隊(duì)成員不多，不如就用一個(gè)服務(wù)器、一個(gè)數(shù)據(jù)庫(kù)。因?yàn)榉謳?kù)分表對(duì)數(shù)據(jù)