MySQL 效能最佳化，最佳化設計及設計原則解讀

標籤：策略   邏輯   ima   維護   曆史   資料庫設計   圖片   座標   類型   

MySQL效能最佳化的目的

如何合理的設計資料庫？

什麼樣的資料庫設計才能給後期DBA最佳化提供基石？

資料庫設計與程式設計的差異？

資料庫設計早期最佳化

1. 關係明確（理清表之間的關係，可以通過冗餘的方式提高效率）

2. 節省空間的（根據業務經驗，設定欄位長短）

3. 提高效率

資料庫表開發流程

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

資料庫種類

1. 層級資料庫（註冊表） 如：Windows作業系統的核心就是一個註冊表，由於配置項比較多，採用層級關係的資料存放區

2. 關係型資料庫 如：MySQL

3. 時序資料庫

4. 圖資料庫 如：最短路徑，地理資訊

5. Key-value資料庫 如：Redis

6. 對象資料庫

7. BigTable資料庫

檔案系統和資料庫系統之間的區別

(1)檔案系統用檔案將資料長期儲存在外存上，資料庫系統用資料庫統一儲存資料;

(2)檔案系統中的程式和資料有一定的聯絡，資料庫系統中的程式和資料分離;

(3)檔案系統用作業系統中的存取方法對資料進行管理，資料庫系統用DBMS統一管理和控制資料;

(4)檔案系統實現以檔案為單位的資料共用，資料庫系統實現以記錄和欄位為單位的資料共用。

最佳化設計第一步

想要在表設計中節省空間的，就必須精通各種資料類型的特點（能用在什麼業務上）、長度等。

int類型只增主鍵欄位=>4位元組=>每個位元組8位=>32位，在CPU載入一條指令的時候，4位元組是和CPU寄存器的運算有關，如：64位，由於直接的系統一般都是32位的，所以在運算4位元組的資料是剛好的，效率最高，而現今我們系統基本都是64位的時候，其實沒有更好的利用好CPU運算，所以在設計表欄位建議，使用8位元組的主鍵bigint，而不是直接使用int來做主鍵。

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

char(10) 不管該欄位是否儲存資料，都佔10個字元的儲存空間

char(10) 同時存在一個坑，就是儲存abc資料後改資料庫欄位的值為“abc 7個空格 ”，在精準查詢（where）就必須帶上後面的7個空格

varchar 不存的時候不佔空間，存多長資料就佔多少空間

最佳化設計第二步

如何合理的設計出符合三範式資料庫表?

1NF：列不可分。每一列都是不可分割的基本資料項目，如這樣的設計就不合理，姓名（王五，wangwu）

2NF：1NF的基礎上面，非主屬性完全依賴於主關鍵字，如學生姓名（非主屬性）就是依賴於學號（主屬性）的。

3NF：屬性不依賴於其它非主屬性 , 消除傳遞依賴，如這樣的設計就不合理，學號做主鍵，學生課程表（學號=課程），當學號修改，對應的課程表也需要修改，這就是屬於傳遞依賴

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

4NF：沒有多值依賴

由於學號不能做主鍵，那用什麼做主鍵？首先就有這樣的規則：不要用商務規則來做主鍵，主鍵就應該和業務無關。

如經常用的的order_no（業務訂單號），即使是唯一的，也不建議做主鍵的，容易產生傳遞依賴的問題，這樣就不符合第三範式了。

最佳化設計第三步

資料庫最佳化策略

1、選擇小的資料類型

2、單獨設計主鍵，並考慮分布式擴充

3、外鍵設計

（重要，我們之前開發都是直接使用的弱外鍵來設定主外鍵關係，而實際項目中，如果要是刪除了主鍵對應的記錄後，外鍵表中的記錄是沒有刪除的，這樣對於資料庫的資料是很容易混亂的，不便於維護，那我要是使用的是強外鍵的方式，這樣直接刪除主鍵記錄，沒有刪除外鍵表中的記錄，這樣是要報錯的，這樣容易找到代碼上的問題，外鍵的設計能對於資料完整性有一個好的約束，當你開發的系統已經完全不會出現資料不完整的問題的時候，你可以考慮使用弱外鍵來關聯表操作，也同時會省去外鍵消耗，具體的設定外鍵方法查考部落格：外鍵及其約束理解）

4、索引設計

（對於業務上的欄位，那些需要欄位需要建立索引？）

5、關聯關係表設計，多對一，多對多

6、讀寫頻繁的資訊，與不頻繁的資訊分開

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

7、配置表，日誌表，定時任務表等

8、匯總表設計

（多表關聯查詢會很慢，還容易卡死的情況，可以考慮在業務上匯總，記錄到匯總表）

最佳化設計第四步

經過業務的沉澱，積累出一些設計思路或抽取出多項目的共同點，減少開發成本

1、通用型設計

例：人員，部門，角色

2、特別設計

附件，日誌，配置，監控等

3、儲存設計

類型劃分便於分區

4、一些附加欄位

建立日期，修改日期，排序

5、流水表

類似於日誌，但由業務處理結果組成，帳戶變動或業務處理的中間值

在設計資料庫的時候應當落實如下的原則

（一）降低對資料庫功能的依賴（如在業務上使用了MySQL特性，且這個特性是只有MySQL存在的，對以後的資料庫遷移會帶來很大的麻煩）

（二）定義實體關聯的原則

牽涉到的實體 識別出關係所涉及的所有實體。

所有權 考慮一個實體“擁有”另一個實體的情況。

基數 考量一個實體的執行個體和另一個實體執行個體關聯的數量。

（三）列意味著唯一的值

如果表示座標（0,0），應該使用兩列表示，而不是將“0,0”放在1個列中。

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

（五）定義主鍵和外鍵

資料表必須定義主鍵和外鍵（如果有外鍵）。

（六）選擇鍵

（七）是否允許NULL

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

所有與NULL進行的數學操作都返回NULL。

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

（八）正常化——範式

1NF

包含分隔字元類字元的字串資料。

名字尾端有數位屬性。

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

2NF

多個屬性有同樣的首碼。

重複的資料群組。

匯總的資料，所引用的資料在一個完全不同的實體中。

BCNF- “每個鍵必須唯一標識實體，每個非鍵熟悉必須描述實體。”

4NF

三元關係（實體:實體:實體）。

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

臨時資料或曆史值。（需要將曆史資料的主體提出，否則將存在大量冗餘。）

（九）選擇資料類型

（十）最佳化並行

設計DB時就應該考慮到對並行進行最佳化，比如，timestamp類型。

命名規則表名規則

1、要用首碼，但不要用無意義的首碼

2、底線分隔

3、全小寫

列名規則

1、一般不用首碼（當和關鍵詞衝突的可以考慮加首碼區別）

2、底線分隔

3、全小寫

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

MySQL 效能最佳化，最佳化設計及設計原則解讀