MySQL資料庫優化大全方法彙總

随着資料和負載增加，MySQL資料庫會日漸緩慢，性能越來越差，使用者體驗也随之變差，是以資料庫性能優化十分緊迫，本文分享MySQL資料庫優化大全：

MySQL資料庫優化

本文先模拟一下資料庫通路流程，然後逐漸的優化這些環節，可以從減少資料通路（減少磁盤通路）、傳回更少資料（減少網絡傳輸或磁盤通路）、減少互動次數（減少網絡傳輸）、減少伺服器CPU開銷（減少CPU及記憶體開銷）和利用更多資源（增加資源）幾個方面來提升資料庫性能。

可以從以下及方面優化MySQL資料庫：

一：資料庫結構優化

• 1）範式優化：表的設計合理化（符合3NF），比如消除備援（節省空間）；
• 2）反範式優化：比如适當加備援等（減少join）
• 3）拆分表：分區将資料在實體上分隔開，不同分區的資料可以制定儲存在處于不同磁盤上的資料檔案裡。這樣，當對這個表進行查詢時，隻需要在表分區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，另外處于不同磁盤的分區也将對這個表的資料傳輸分散在不同的磁盤I/O，一個精心設定的分區可以将資料傳輸對磁盤I/O競争均勻地分散開。對資料量大的時時表可采取此方法，可按月自動建表分區。

二：優化SQL語句

• 1）應盡量避免在 where 子句中使用!=或<>操作符，否則将引擎放棄使用索引而進行全表掃描；
• 2）應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null           

可以在num上設定預設值0，確定表中num列沒有null值，然後這樣查詢：

select id from t where num=0           

• 3）很多時候用exists代替in是一個好的選擇；
• 4）用Where子句替換HAVING子句，因為HAVING隻會在檢索出所有記錄之後才對結果集進行過濾；
• 5）迅速的定位執行速度慢的語句、開啟慢查詢、設定慢查詢時間、啟用慢查詢日志、通過mysqldumoslow工具對慢日志進行分類彙總；
• 6）析SQL語句，通過explain分析查詢、通profiling可以得到更詳細的資訊；
• 7）建立索引（主鍵索引/唯一索引/全文索引/普通索引）；
• 8）避免Select * （不查詢多餘的列與行）；
• 9）使用視圖（經常被查詢的列資料，并且這些資料不被經常的修改，删除）；

三：分表技術（水準分割、垂直分割）、分區技術

如果遇到大表的情況下，SQL語句優化已經無法繼續優化了，我們可以考慮分表和分區，目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表的增删改查效率。

分表是将一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個檔案，MYD資料檔案，.MYI索引檔案，.frm表結構檔案。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。

分區和分表相似，都是按照規則分解表。不同在于分表将大表分解為若幹個獨立的實體表，而分區是将資料分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區後，表面上還是一張表，但資料散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的資料。

• 1）都能提高mysql的性能，在高并發狀态下都有一個良好的表現；
• 2）分表和分區不沖突，可以互相配合的，對于那些大通路量，并且表資料比較多的表，我們可以采取分表和分區結合的方式，通路量不大，但是表資料很多的表，我們可以采取分區的方式等；
• 3）分表技術是比較麻煩的，需要手動去建立子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要建立子表和配置子表間的union關系；
• 4）表分區相對于分表，操作友善，不需要建立子表。

四：讀寫分離

MySQL讀寫分離可以參考：

阿裡雲MySQL讀寫分離詳解

，本文來說說抛開阿裡雲來實作讀寫分離的方法：

• 方法一：php程式上自己做邏輯判斷，寫php代碼的時候，自己在程式上做邏輯判讀寫比對。select，insert、update、delete做正則比對，根據結果選擇寫伺服器（主伺服器）。如果是select操作則選擇讀伺服器（從伺服器器） mysql_connect('讀寫的區分')
• 方法二：MySQL中間件，基本的原理是讓主資料庫處理寫操作（insert、update、delete），而從資料庫處理查詢操作（select）。而資料庫的一緻性則通過主從複制來實作。是以說主從複制是讀寫分離的基礎。

五：存儲過程 [子產品化程式設計，可以提高速度]

存儲過程是SQL語句和控制語句的預編譯集合，儲存在資料庫中，可有應用程式調用執行，而且允許使用者聲明變量、邏輯控制語句及其他強大的程式設計功能。包含邏輯控制語句和資料操作語句，可以接收參數、輸出參數、傳回單個或多個結果值及傳回值。

使用存儲過程的優點：子產品化程式設計，隻需建立一次，以後即可調用該存儲過程任意次；執行速度快，效率高；減少網絡流量；具有良好的安全性。

六：對mysql配置優化 [配置最大并發數, 調整緩存大小]

MySQL資料庫優化大全方法彙總

七：MySQL伺服器硬體更新

MySQL伺服器硬體更新本文就不多贅述了，也可以使用雲資料庫，參考：

阿裡雲MySQL雲伺服器詳解

八：定時的去清除不需要的資料,定時進行碎片整理

• 1）檢視表碎片的方法

select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='table_name' limit 1;           

• 2）Innodb存儲引擎清理碎片方法：

ALTER TABLE tablename ENGINE=InnoDB           

• 3）Myisam存儲引擎清理碎片方法：

OPTIMIZE TABLE table_name           

注意：MySQL碎片整理盡量選擇業務不繁忙時清理，一個月清理一次即可。