101個MySQL調試和最佳化技巧

 　　MySQL是一個功能強大的開來源資料庫。隨著越來越多的資料庫驅動的應用程式，人們一直在推動MySQL發展到它的極限。這裡是101條調節和最佳化MySQL安裝的技巧。一些技巧是針對特定的安裝環境的，但這些思路是通用的。我已經把他們分成幾類，來協助你掌握更多MySQL的調節和最佳化技巧。

　　MySQL 伺服器硬體和作業系統調節:

　　1. 擁有足夠的實體記憶體來把整個InnoDB檔案載入到記憶體中——在記憶體中訪問檔案時的速度要比在硬碟中訪問時快的多。

　　2. 不惜一切代價避免使用Swap交換分區 – 交換時是從硬碟讀取的，它的速度很慢。

　　3. 使用電池供電的RAM(註：RAM即隨機儲存空間)。

　　4. 使用進階的RAID(註：Redundant Arrays of Inexpensive Disks，即磁碟陣列) – 最好是RAID10或更高。

　　5. 避免RAID5(註：一種儲存效能、資料安全和儲存成本兼顧的儲存解決方案) – 確保資料庫完整性的校正是要付出代價的。

　　6. 將作業系統和資料分區分開，不僅僅是邏輯上，還包括物理上 – 作業系統的讀寫操作會影響資料庫的效能。

　　7. 把MySQL臨時空間和複製日誌與資料放到不同的分區 – 當資料庫後台從磁碟進行讀寫操作時會影響資料庫的效能。

　　8. 更多的磁碟空間等於更快的速度。

　　9. 更好更快的磁碟。

　　10. 使用SAS(註： Serial Attached SCSI，即串列串連SCSI)代替SATA(註：SATA，即串口硬碟)。

　　11. 較小的硬碟 比 較大的硬碟快，尤其是在RAID配置的情況下。

　　12. 使用電池支援的快取RAID控制器。

　　13. 避免使用軟體磁碟陣列。

　　14. 考慮為資料分區使用固態IO卡 (不是磁碟機) – 這些卡能夠為幾乎任何數量的資料支援2GB/s的寫入速度。

　　15. 在Linux中設定swappiness的值為0 – 在資料庫伺服器中沒有理由快取檔案，這是一個伺服器或台式機的優勢。

　　16. 如果可以的話，使用 noatime 和 nodirtime 掛載檔案系統 – 沒有理由更新訪問資料庫檔案的修改時間。

　　17. 使用 XFS 檔案系統 – 一種比ext3更快、更小的檔案系統，並且有許多日誌選項， 而且ext3 已被證實與MySQL有雙緩衝問題。

　　18. 調整 XFS 檔案系統日誌和緩衝變數 – 為了最高效能標準。

　　19. 在 Linux 系統中, 使用 NOOP 或者 DEADLINE IO 定時發送器 – 同 NOOP 和 DEADLINE定時發送器相比，這個 CFQ 和 ANTICIPATORY 定時發送器 顯得非常慢。

　　20. 使用64位的作業系統 – 對於MySQL，會有更大的記憶體支援和使用。

　　21. 刪除伺服器上未使用的安裝包和守護進程 – 更少的資源佔用。

　　22. 把使用MySQL的host和你的MySQL host放到一個hosts檔案中 – 沒有DNS尋找。

　　23. 切勿強制殺死一個MySQL進程 – 你會損壞資料庫和正在運行備份的程式。

　　24. 把伺服器貢獻給MySQL – 後台進程和其他服務能夠縮短資料庫佔用CPU的時間。

　　MySQL 配置:

　　25. 當寫入時，使用 innodb_flush_method=O_DIRECT 來避免雙緩衝。

　　26. 避免使用 O_DIRECT 和 EXT3 檔案系統 – 你將序列化所有要寫入的。

　　27. 分配足夠的 innodb_buffer_pool_size 來載入整個 InnoDB 檔案到記憶體中– 少從磁碟中讀取。

　　28. 不要將 innodb_log_file_size 參數設定太大， 這樣可以更快同時有更多的磁碟空間 – 丟掉多的日誌通常是好的，在資料庫崩潰後可以降低恢複資料庫的時間。

　　29. 不要混用 innodb_thread_concurrency 和 thread_concurrency 參數– 這2個值是不相容的。

　　30. 分配一個極小的數量給 max_connections 參數 – 太多的串連會用盡RAM並鎖定MySQL服務。

　　31. 保持 thread_cache 在一個相對較高的數字，大約 16 – 防止開啟串連時緩慢。

　　32. 使用skip-name-resolve參數 – 去掉 DNS 尋找。

　　33.如果你的查詢都是重複的，並且資料不常常發生變化，那麼可以使用查詢快取。但是如果你的資料經常發生變化，那麼使用查詢快取會讓你感到失望。

　　34.增大temp_table_size值，以防止寫入磁碟

　　35.增大max_heap_table_size值，以防止寫入磁碟

　　36.不要把sort_buffer_size值設定的太高，否則的話你的記憶體將會很快耗盡

　　37.根據key_read_requests和key_reads值來決定key_buffer的大小，一般情況下key_read_requests應該比key_reads值高，否則你不能高效的使用key_buffer

　　38.將innodb_flush_log_at_trx_commit設定為0將會提高效能，但是如果你要保持預設值(1)的話，那麼你就要確保資料的完整性，同時你也要確保複製不會滯後。

　　39.你要有一個測試環境，來測試你的配置，並且在不影響正常生產的情況下，可以常常進行重啟。

　　MySQL模式最佳化:

　　40. 保持你的資料庫清理性。

　　41. 舊資料歸檔 – 刪除多餘的行返回或搜尋查詢。

　　42. 將您的資料加上索引.

　　43. 不要過度使用索引，比較與查詢.

　　44. 壓縮文字和BLOB資料類型 – 以節省空間的和減少磁碟讀取次數.

　　45. UTF 8和UTF16都低於latin1執行效率.

　　46. 有節制地使用觸發器.

　　47. 冗餘資料保持到最低限度 – 不重複不必要的資料.

　　48. 使用連結資料表，而不是擴充行.

　　49. 注意資料類型，在您的真實資料中，儘可能使用最小的一個.

　　50. 如果其他資料經常被用於查詢時，而BLOB / TEXT資料不是，就把BLOB / TEXT資料從其他資料分離出來.