Mysql性能優化全揭秘-庖丁解牛

• 2020 年 1 月 19 日
• 筆記

「為什麼寫」

一直想寫資料庫相關的文章，最直接的原因是資料庫這塊我們工作中每天都會用到，也是面試求職繞不開的話題，無論你是何種測試，優秀的資料庫能力都會非常加分，最近我在總結資料庫性能優化這塊內容，性能這塊就如庖丁解牛，首先你需要知道牛的全貌，筋脈布局，才能解之，今天這篇文章，也先從全局的角度來看資料庫性能優化這件事。

「管中窺豹」

我們常常聽到一句話，性能優化皆io，可以認為性能優化的核心思想就是減少io操作，之前和一同學聊天，發現不少人認為減少io就是減少磁碟操作，這其實很不全面，Cpu->Cache->Net->Mem->Disk, 每一步都是io，片面的認為磁碟，那你只是看到了牛腿，解不了全牛，下面我羅列的每一塊硬體資源，在資料庫層面都具備著相應的職能，成本以及開銷，如下圖a

圖a

硬體資源的速度排序大體可以這麼認為:CPU>Cache>記憶體>網路>硬碟,一直說的減少io操作本質是減少性能相對較差的io開銷，在性能較好的資源上進行操作，通過圖a知道，每一種優化法則都是解決其對應硬體的性能問題，當我們遇到性能問題時，不應該簡單說通過加硬體資源去解決，這種解法一般叫做揚湯止沸，解決不了本質問題，而是應當通過我們的圖譜或者叫知識體系提出性價比最高的解決方案

「常用的優化手段」

1.表設計(符合3NF)

NF的意思是範式，粗略而言資料庫分為三個範式。即：第一範式 第二範式 第三範式。

第一範式的意思是：數據表中的欄位都是不可分割的，原子的。第一範式是最簡單的，也是必需的。

第二範式：在第一範式的基礎上，數據表中各欄位和主鍵之間完全依賴，不存在部分依賴。例如：數據表中存在欄位<學號，姓名，分數>組成，其中<學號，姓名>組合在一起構成主鍵。如果學生的姓名不重複的情況下，那麼就存在部分依賴。

第三範式：第二範式的基礎上，不存在傳遞依賴。

所謂傳遞依賴是指：例如：數據表中存在欄位<學號，姓名，分數>組成，其中<學號>是主鍵。如果學生的姓名不重複的情況下，那麼就存在傳遞依賴。即：學號可以唯一確定姓名，而姓名是不重複的，所以，姓名也可以唯一確定分數。那麼，學號決定分數就不唯一了，中間還有姓名可以確定。這就是所謂的傳遞依賴。

2.優化SQL語句

a.獲取到慢sql，processlist，或者慢查詢

b.explain 分析

c.索引分析，sql規範分析

d.業務層面邏輯分析

3.分表

a.垂直切分:將同一個表的內容，按照數據熱度不同切分欄位，按照3nf原則，劃分為多個表。

分表理由：根據數據的活躍度進行分離，隨著目前nosql的流行，豎直切分用的不多

b.水平切分:把大的表結構，橫向切割為同樣結構的不同表,表結構是完全一樣， 按照數量級切割。

分表理由：根據數據量的規模來劃分，保證單表的容量不會太大，保證單表的查詢等處理能力。

4. 讀寫分離

a. 主庫update，insert，delete

b. 從庫select

5. 存儲過程

a.批量運算，執行速度相對較高

b.減少網路開銷

6.對mysql配置優化

a.查詢排序時所能使用的緩衝區大小 read_buffer_size

b.查詢快取 query_cache_size

c. 最大連接數 max_connections

調優套路之思維導圖: