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個空格  ”,，在精準查詢（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ù)量,。

（三）列意味著唯一的值

如果表示坐標（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- “每個鍵必須唯一標識實體,，每個非鍵熟悉必須描述實體,。”

4NF

三元關(guān)系（實體:實體:實體）,。
潛伏的多值屬性,。（如多個手機號。）
臨時數(shù)據(jù)或歷史值,。（需要將歷史數(shù)據(jù)的主體提出,，否則將存在大量冗余。）

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

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

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

命名規(guī)則

表名規(guī)則

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

2、下劃線分隔

3,、全小寫

列名規(guī)則

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

2、下劃線分隔

3,、全小寫

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