關於MySQL資料庫效能優化方法，看這一篇文章就夠了！

資料庫大量應用程式開發專案中，大多數情況下，資料庫的操作效能成為整個應用的效能瓶頸。資料庫的效能是程式設計師需要去關注的事情，當設計資料庫表結構以及運算元據庫（尤其是查詢資料時），都需要注意資料操作的效能。本文我們以MySQL資料庫為例進行討論。 加我VX：atstudy-js 回覆“測試”，進入 自動化測試學習交流群~~

一、資料庫優化目標

1、減少 IO 次數

IO永遠是資料庫最容易瓶頸的地方，這是由資料庫的職責所決定的，大部分資料庫操作中超過90%的時間都是 IO 操作所佔用的，減少 IO 次數是 SQL 優化中需要第一優先考慮，當然，也是收效最明顯的優化手段。

2、降低 CPU 計算

除了 IO 瓶頸之外，SQL優化中需要考慮的就是 CPU 運算量的優化了。order by,group by,distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理記憶體中的資料比較運算）。當我們的 IO 優化做到一定階段之後，降低 CPU計算也就成為了我們 SQL 優化的重要目標。

MySql查詢過程

二 資料庫優化方法

1.SQL語句優化

明確了優化目標之後，我們需要確定達到我們目標的方法。對於SQL語句來說，達到上述2個優化目標的方法其實只有一個，那就是改變SQL的執行計劃，讓他儘量“少走彎路”，儘量通過各種“捷徑”來找到我們需要的資料，以達到“減少IO次數”和“降低CPU計算”的目標。

1)儘量少 join。MySQL 的優勢在於簡單，但這在某些方面其實也是其劣勢。MySQL優化器效率高，但是由於其統計資訊的量有限，優化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join，一方面由於其優化器受限，再者在Join這方面所下的功夫還不夠，所以效能表現離Oracle等關係型資料庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優於這些資料庫前輩。

2)儘量少排序

3)排序操作會消耗較多的 CPU 資源，所以減少排序可以在快取命中率高等 IO 能力足夠的場景下會較大影響 SQL的響應時間。

4)儘量避免 select *，並儘量用join代替子查詢

5)儘量少使用“or”關鍵字

當 where 子句中存在多個條件以“或”並存的時候，MySQL 的優化器並沒有很好的解決其執行計劃優化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構方式，造成了其效能比較低下，很多時候使用 union all 或者是union（必要的時候）的方式來代替“or”會得到更好的效果。

6)儘量用 union all 代替 union

union 和 union all 的差異主要是前者需要將兩個（或者多個）結果集合並後再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。所以當我們可以確認不可能出現重複結果集或者不在乎重複結果集的時候，儘量使用 union all 而不是 union。

7)避免型別轉換

8)能用DISTINCT的就不用GROUP BY

9)儘量不要用SELECT INTO語句 ?

10)從全域性出發優化，而不是片面調整

SQL 優化不能是單獨針對某一個進行，而應充分考慮系統中所有的 SQL，尤其是在通過調整索引優化 SQL的執行計劃的時候，千萬不能顧此失彼，因小失大。

2.表結構優化

MySQL資料庫是基於行（Row）儲存的資料庫，而資料庫操作 IO 的時候是以 page（block）的方式，也就是說，如果我們每條記錄所佔用的空間量減小，就會使每個page中可存放的資料行數增大，那麼每次 IO 可訪問的行數也就增多了。反過來說，處理相同行數的資料，需要訪問的 page 就會減少，也就是 IO 操作次數降低，直接提升效能。

資料型別選擇

原則是：資料行的長度不要超過8020位元組，如果超過這個長度的話在物理頁中這條資料會佔用兩行從而造成儲存碎片，降低查詢效率；欄位的長度在最大限度的滿足可能的需要的前提下，應該儘可能的設得短一些，這樣可以提高查詢的效率，而且在建立索引的時候也可以減少資源的消耗。 ? ?

1)數字型別：非萬不得已不要使用DOUBLE，不僅僅只是儲存長度的問題，同時還會存在精確性的問題。同樣，固定精度的小數，也不建議使用DECIMAL，建議乘以固定倍數轉換成整數儲存，可以大大節省儲存空間，且不會帶來任何附加維護成本。

2)字元型別：定長欄位，建議使用 CHAR 型別（char查詢快，但是耗儲存空間，可用於使用者名稱、密碼等長度變化不大的欄位），不定長欄位儘量使用 VARCHAR（varchar查詢相對慢一些但是節省儲存空間，可用於評論等長度變化大的欄位），且僅僅設定適當的最大長度，而不是非常隨意的給一個很大的最大長度限定，因為不同的長度範圍，MySQL也會有不一樣的儲存處理。

3)時間型別：儘量使用TIMESTAMP型別，因為其儲存空間只需要DATETIME 型別的一半。對於只需要精確到某一天的資料型別，建議使用DATE型別，因為他的儲存空間只需要3個位元組，比TIMESTAMP還少。不建議通過INT型別類儲存一個unix timestamp 的值，因為這太不直觀，會給維護帶來不必要的麻煩，同時還不會帶來任何好處。

4)ENUM &SET：對於狀態欄位，可以嘗試使用 ENUM 來存放，因為可以極大的降低儲存空間，而且即使需要增加新的型別，只要增加於末尾，修改結構也不需要重建表資料。

字元編碼

字符集直接決定了資料在MySQL中的儲存編碼方式，由於同樣的內容使用不同字符集表示所佔用的空間大小會有較大的差異，所以通過使用合適的字符集，可以幫助我們儘可能減少資料量，進而減少IO操作次數。

儘量使用 NOT NULL

NULL 型別比較特殊，SQL 難優化。雖然 MySQL NULL型別和 Oracle 的NULL有差異，會進入索引中，但如果是一個組合索引，那麼這個NULL 型別的欄位會極大影響整個索引的效率。雖然 NULL空間上可能確實有一定節省，倒是帶來了很多其他的優化問題，不但沒有將IO量省下來，反而加大了SQL的IO量。所以儘量確保 DEFAULT 值不是 NULL，也是一個很好的表結構設計優化習慣。

3.資料庫架構優化

分散式和叢集化

1)負載均衡。負載均衡叢集是由一組相互獨立的計算機系統構成，通過常規網路或專用網路進行連線，由路由器銜接在一起，各節點相互協作、共同負載、均衡壓力，對客戶端來說，整個群集可以視為一臺具有超高效能的獨立伺服器。MySQL一般部署的是高可用性負載均衡叢集，具備讀寫分離，一般只對讀進行負載均衡。

2)讀寫分離。讀寫分離簡單的說是把對資料庫讀和寫的操作分開對應不同的資料庫伺服器，這樣能有效地減輕資料庫壓力，也能減輕io壓力。主資料庫提供寫操作，從資料庫提供讀操作，其實在很多系統中，主要是讀的操作。當主資料庫進行寫操作時，資料要同步到從的資料庫，這樣才能有效保證資料庫完整性。

3)資料切分。通過某種特定的條件，將存放在同一個資料庫中的資料分散存放到多個資料庫上，實現分佈儲存，通過路由規則路由訪問特定的資料庫，這樣一來每次訪問面對的就不是單臺伺服器了，而是N臺伺服器，這樣就可以降低單臺機器的負載壓力。

4.其他優化

1)適當使用檢視加速查詢。把表的一個子集進行排序並建立檢視，有時能加速查詢（特別是要被多次執行的查詢）。它有助於避免多重排序操作，而且在其他方面還能簡化優化器的工作。檢視中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁碟I/O，所以查詢工作量可以得到大幅減少。

2)演算法優化。儘量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。使用基於遊標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。與臨時表一樣，遊標並不是不可使用。對小型資料集使用 FAST_FORWARD 遊標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的資料時。

3)封裝儲存過程。經編譯和優化後儲存在資料庫伺服器中，執行效率高，可以降低客戶機和伺服器之間的通訊量，有利於集中控制，易於維護。

最後：

可以我的個人V：atstudy-js，可以免費領取一份10G軟體測試工程師面試寶典文件資料。以及相對應的視訊學習教程免費分享！，其中包括了有基礎知識、Linux必備、Mysql資料庫、抓包工具、介面測試工具、測試進階-Python程式設計、Web自動化測試、APP自動化測試、介面自動化測試、測試高階持續整合、測試架構開發測試框架、效能測試等。

這些測試資料，對於做【軟體測試】的朋友來說應該是最全面最完整的備戰倉庫，這個倉庫也陪伴我走過了最艱難的路程，希望也能幫助到你！