MySQL-性能優(yōu)化-優(yōu)化設(shè)計和設(shè)計原則

MySQL性能優(yōu)化目的

如何合理的設(shè)計數(shù)據(jù)庫？

什么樣的數(shù)據(jù)庫設(shè)計才能給后期DBA優(yōu)化提供基石？

數(shù)據(jù)庫設(shè)計與程序設(shè)計的差異？

數(shù)據(jù)庫設(shè)計早期優(yōu)化

1. 關(guān)系明確（理清表之間的關(guān)系，可以通過冗余的方式提高效率）

2. 節(jié)省空間（根據(jù)業(yè)務(wù)經(jīng)驗，設(shè)置字段長短）

3. 提高效率

數(shù)據(jù)庫表開發(fā)流程

原型=>逐步完善（表的設(shè)計也是如此）

數(shù)據(jù)庫種類

1. 層級數(shù)據(jù)庫（注冊表） 如：Windows操作系統(tǒng)的核心就是一個注冊表，由于配置項比較多，采用層級關(guān)系的數(shù)據(jù)存儲

2.?關(guān)系型數(shù)據(jù)庫?如：MySQL

3. 時序數(shù)據(jù)庫

4. 圖數(shù)據(jù)庫 如：最短路徑，地理信息

5. Key-value數(shù)據(jù)庫 如：Redis

6. 對象數(shù)據(jù)庫

7. BigTable數(shù)據(jù)庫

文件系統(tǒng)和數(shù)據(jù)庫系統(tǒng)之間的區(qū)別？

(1)文件系統(tǒng)用文件將數(shù)據(jù)長期保存在外存上，數(shù)據(jù)庫系統(tǒng)用數(shù)據(jù)庫統(tǒng)一存儲數(shù)據(jù);

(2)文件系統(tǒng)中的程序和數(shù)據(jù)有一定的聯(lián)系，數(shù)據(jù)庫系統(tǒng)中的程序和數(shù)據(jù)分離;

(3)文件系統(tǒng)用操作系統(tǒng)中的存取方法對數(shù)據(jù)進行管理，數(shù)據(jù)庫系統(tǒng)用DBMS統(tǒng)一管理和控制數(shù)據(jù);

(4)文件系統(tǒng)實現(xiàn)以文件為單位的數(shù)據(jù)共享，數(shù)據(jù)庫系統(tǒng)實現(xiàn)以記錄和字段為單位的數(shù)據(jù)共享。

優(yōu)化設(shè)計第一步

想要在表設(shè)計中節(jié)省空間，就必須精通各種數(shù)據(jù)類型的特點（能用在什么業(yè)務(wù)上）、長度等。

int類型只增主鍵字段=>4字節(jié)=>每個字節(jié)8位=>32位，在CPU加載一條指令的時候，4字節(jié)是和CPU寄存器的運算有關(guān)，如：64位，由于直接的系統(tǒng)一般都是32位的，所以在運算4字節(jié)的數(shù)據(jù)是剛好的，效率最高，而現(xiàn)今我們系統(tǒng)基本都是64位的時候，其實沒有更好的利用好CPU運算，所以在設(shè)計表字段建議，使用8字節(jié)的主鍵bigint，而不是直接使用int來做主鍵。

uuid做主鍵，字符類型做主鍵，在CPU的加載是需要消耗更多的運算過程

char(10) 不管該字段是否存儲數(shù)據(jù)，都占10個字符的存儲空間

char(10) 同時存在一個坑，就是存儲abc數(shù)據(jù)后改數(shù)據(jù)庫字段的值為“abc ?7個空格 ?”，在精準(zhǔn)查詢（where）就必須帶上后面的7個空格

varchar 不存的時候不占空間，存多長數(shù)據(jù)就占多少空間

優(yōu)化設(shè)計第二步

如何合理的設(shè)計出符合三范式數(shù)據(jù)庫表?

1NF：列不可分。每一列都是不可分割的基本數(shù)據(jù)項，如這樣的設(shè)計就不合理，姓名（王五，wangwu）

2NF：1NF的基礎(chǔ)上面，非主屬性完全依賴于主關(guān)鍵字，如學(xué)生姓名（非主屬性）就是依賴于學(xué)號（主屬性）的。

3NF：屬性不依賴于其它非主屬性 , 消除傳遞依賴，如這樣的設(shè)計就不合理，學(xué)號做主鍵，學(xué)生課程表（學(xué)號=課程），當(dāng)學(xué)號修改，對應(yīng)的課程表也需要修改，這就是屬于傳遞依賴

BCNF：符合3NF，每個表中只有一個候選鍵

4NF：沒有多值依賴

由于學(xué)號不能做主鍵，那用什么做主鍵？首先就有這樣的規(guī)則：不要用業(yè)務(wù)規(guī)則來做主鍵，主鍵就應(yīng)該和業(yè)務(wù)無關(guān)。

如經(jīng)常用的的order_no（業(yè)務(wù)訂單號），即使是唯一的，也不建議做主鍵的，容易產(chǎn)生傳遞依賴的問題，這樣就不符合第三范式了。

優(yōu)化設(shè)計第三步

數(shù)據(jù)庫優(yōu)化策略

1、選擇小的數(shù)據(jù)類型

2、單獨設(shè)計主鍵，并考慮分布式擴展

3、外鍵設(shè)計

（重要，我們之前開發(fā)都是直接使用的弱外鍵來設(shè)置主外鍵關(guān)系，而實際項目中，如果要是刪除了主鍵對應(yīng)的記錄后，外鍵表中的記錄是沒有刪除的，這樣對于數(shù)據(jù)庫的數(shù)據(jù)是很容易混亂的，不便于維護，那我要是使用的是強外鍵的方式，這樣直接刪除主鍵記錄，沒有刪除外鍵表中的記錄，這樣是要報錯的，這樣容易找到代碼上的問題，外鍵的設(shè)計能對于數(shù)據(jù)完整性有一個好的約束，當(dāng)你開發(fā)的系統(tǒng)已經(jīng)完全不會出現(xiàn)數(shù)據(jù)不完整的問題的時候，你可以考慮使用弱外鍵來關(guān)聯(lián)表操作，也同時會省去外鍵消耗，具體的設(shè)置外鍵方法查考博客：外鍵及其約束理解）

4、索引設(shè)計

（對于業(yè)務(wù)上的字段，那些需要字段需要建立索引？）

5、關(guān)聯(lián)關(guān)系表設(shè)計，多對一，多對多

6、讀寫頻繁的信息，與不頻繁的信息分開

（如在設(shè)計支付系統(tǒng)的時候，會同時存在訂單表和訂單記錄表，訂單表讀寫頻繁，而訂單記錄表就管理人員用，讀寫一般）

7、配置表，日志表，定時任務(wù)表等

8、匯總表設(shè)計

（多表關(guān)聯(lián)查詢會很慢，還容易卡死的情況，可以考慮在業(yè)務(wù)上匯總，記錄到匯總表）

優(yōu)化設(shè)計第四步

經(jīng)過業(yè)務(wù)的沉淀，積累出一些設(shè)計思路或抽取出多項目的共同點，減少開發(fā)成本

1、通用型設(shè)計

例：人員，部門，角色

2、特別設(shè)計

附件，日志，配置，監(jiān)控等

3、存儲設(shè)計

類型劃分便于分區(qū)

4、一些附加字段

創(chuàng)建日期，修改日期，排序

5、流水表

類似于日志，但由業(yè)務(wù)處理結(jié)果組成，帳戶變動或業(yè)務(wù)處理的中間值

在設(shè)計數(shù)據(jù)庫的時候應(yīng)當(dāng)落實如下的原則

（一）降低對數(shù)據(jù)庫功能的依賴（如在業(yè)務(wù)上使用了MySQL特性，且這個特性是只有MySQL存在的，對以后的數(shù)據(jù)庫遷移會帶來很大的麻煩）

（二）定義實體關(guān)系的原則

牽涉到的實體 識別出關(guān)系所涉及的所有實體。

所有權(quán) 考慮一個實體“擁有”另一個實體的情況。

基數(shù) 考量一個實體的實例和另一個實體實例關(guān)聯(lián)的數(shù)量。

（三）列意味著唯一的值

如果表示坐標(biāo)（0,0），應(yīng)該使用兩列表示，而不是將“0,0”放在1個列中。

（四）列的順序，可讀性問題

（五）定義主鍵和外鍵

數(shù)據(jù)表必須定義主鍵和外鍵（如果有外鍵）。

（六）選擇鍵

（七）是否允許NULL

任何值和NULL拼接后都為NULL。

所有與NULL進行的數(shù)學(xué)操作都返回NULL。

引入NULL后，邏輯不易處理。

（八）規(guī)范化——范式

1NF

包含分隔符類字符的字符串?dāng)?shù)據(jù)。

名字尾端有數(shù)字的屬性。

沒有定義鍵或鍵定義不好的表。

2NF

多個屬性有同樣的前綴。

重復(fù)的數(shù)據(jù)組。

匯總的數(shù)據(jù)，所引用的數(shù)據(jù)在一個完全不同的實體中。

BCNF- “每個鍵必須唯一標(biāo)識實體，每個非鍵熟悉必須描述實體?！?/p>

4NF

三元關(guān)系（實體:實體:實體）。

潛伏的多值屬性。（如多個手機號。）

臨時數(shù)據(jù)或歷史值。（需要將歷史數(shù)據(jù)的主體提出，否則將存在大量冗余。）

（九）選擇數(shù)據(jù)類型

（十）優(yōu)化并行

設(shè)計DB時就應(yīng)該考慮到對并行進行優(yōu)化，比如，timestamp類型。

在此我向大家推薦一個架構(gòu)學(xué)習(xí)交流群。交流學(xué)習(xí)群號：575745314? 里面會分享一些資深架構(gòu)師錄制的視頻錄像：有Spring，MyBatis，Netty源碼分析，高并發(fā)、高性能、分布式、微服務(wù)架構(gòu)的原理，JVM性能優(yōu)化、分布式架構(gòu)等這些成為架構(gòu)師必備的知識體系。還能領(lǐng)取免費的學(xué)習(xí)資源，目前受益良多

命名規(guī)則

表名規(guī)則

1、要用前綴，但不要用無意義的前綴

2、下劃線分隔

3、全小寫

列名規(guī)則

1、一般不用前綴（當(dāng)和關(guān)鍵詞沖突的可以考慮加前綴區(qū)別）

2、下劃線分隔

3、全小寫

不管是表名設(shè)計還是列名設(shè)計，都不要使用拼音來命名，過一段時間就完全不記得了，就用英文，即使英語不好設(shè)計的時候也建議設(shè)置為英文。