MySQL 調優/優化的 101 個建議！

原文：http://www.monitis.com/blog/101-tips-to-mysql-tuning-and-optimization/

MySQL是一個強大的開源資料庫。随着MySQL上的應用越來越多，MySQL逐漸遇到了瓶頸。這裡提供 101 條優化 MySQL 的建議。有些技巧适合特定的安裝環境，但是思路是相通的。我已經将它們分成了幾類以幫助你了解。

Mysql 監控

MySQL伺服器硬體和OS（作業系統）調優：

1、有足夠的實體記憶體，能将整個InnoDB檔案加載到記憶體裡 —— 如果通路的檔案在記憶體裡，而不是在磁盤上，InnoDB會快很多。

2、全力避免 Swap 操作 — 交換（swapping）是從磁盤讀取資料，是以會很慢。

3、使用電池供電的RAM（Battery-Backed RAM）。

4、使用一個進階磁盤陣列 — 最好是 RAID10 或者更高。

5、避免使用RAID5 — 和校驗需要確定完整性，開銷很高。

6、将你的作業系統和資料分開，不僅僅是邏輯上要分開，實體上也要分開 — 作業系統的讀寫開銷會影響資料庫的性能。

7、将臨時檔案和複制日志與資料檔案分開 — 背景的寫操作影響資料庫從磁盤檔案的讀寫操作。

8、更多的磁盤空間等于更高的速度。

9、磁盤速度越快越好。

10、SAS優于SATA。

11、小磁盤的速度比大磁盤的更快，尤其是在 RAID 中。

12、使用電池供電的緩存 RAID（Battery-Backed Cache RAID）控制器。

13、避免使用軟磁盤陣列。

14. 考慮使用固态IO卡（不是磁盤）來作為資料分區 — 幾乎對所有量級資料，這種卡能夠支援 2 GBps 的寫操作。

15、在 Linux 系統上，設定 swappiness 的值為0 — 沒有理由在資料庫伺服器上緩存檔案，這種方式在Web伺服器或桌面應用中用的更多。

16、盡可能使用 noatime 和 nodirtime 來挂載檔案系統 — 沒有必要為每次通路來更新檔案的修改時間。

17、使用 XFS 檔案系統 — 一個比ext3更快的、更小的檔案系統，擁有更多的日志選項，同時，MySQL在ext3上存在雙緩沖區的問題。

18、優化你的 XFS 檔案系統日志和緩沖區參數 – -為了擷取最大的性能基準。

19、在Linux系統中，使用 NOOP 或 DEADLINE IO 排程器 — CFQ 和 ANTICIPATORY 排程器已經被證明比 NOOP 和 DEADLINE 慢。

20、使用 64 位作業系統 — 有更多的記憶體能用于尋址和 MySQL 使用。

21、将不用的包和背景程式從伺服器上删除 — 減少資源占用。

22、将使用 MySQL 的 host 和 MySQL自身的 host 都配置在一個 host 檔案中 — 這樣沒有 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 變量 — 這兩個值不能相容。

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 的讀需求應該比 key_reads 的值更高，否則使用 key_buffer 就沒有效率了。

38、設定 innodb_flush_log_at_trx_commit = 0 可以提高性能，但是保持預設值（1）的話，能保證資料的完整性，也能保證複制不會滞後。

39、有一個測試環境，便于測試你的配置，可以經常重新開機，不會影響生産環境。

Mysql Schema優化

40、保證你的資料庫的整潔性。

41、歸檔老資料 — 删除查詢中檢索或傳回的多餘的行

42、在資料上加上索引。

43、不要過度使用索引，評估你的查詢。

44、壓縮 text 和 blob 資料類型 — 為了節省空間，減少從磁盤讀資料。

45、UTF 8 和 UTF16 比 latin1 慢。

46、有節制的使用觸發器。

47、保持資料最小量的備援 — 不要複制沒必要的資料.

48、使用連結表，而不是擴充行。

49、注意你的資料類型，盡可能的使用最小的。

50、如果其他資料需要經常需要查詢，而 blob/text 不需要，則将 blob/text 資料域其他資料分離。

51、經常檢查和優化表。

52、經常做重寫 InnoDB 表的優化。

53、有時，增加列時，先删除索引，之後在加上索引會更快。

54、為不同的需求選擇不同的存儲引擎。

55、日志表或審計表使用ARCHIVE存儲引擎 — 寫的效率更高。

56、将 session 資料存儲在 memcache 中，而不是 MySQL 中 — memcache 可以設定自動過期，防止MySQL對臨時資料高成本的讀寫操作。

57、如果字元串的長度是可變的，則使用VARCHAR代替CHAR — 節約空間，因為CHAR是固定長度，而VARCHAR不是（utf8 不受這個影響）。

58、逐漸對 schema 做修改 — 一個小的變化将産生的巨大的影響。

59、在開發環境測試所有 schema 變動，而不是在生産環境的鏡像上去做。

60、不要随意改變你的配置檔案，這可能産生非常大的影響。

61、有時候，少量的配置會更好。

62、質疑使用通用的MySQL配置檔案。

Mysql 查詢優化

63、使用慢查詢日志，找出執行慢的查詢。

64、使用 EXPLAIN 來決定查詢功能是否合适。

65、經常測試你的查詢，看是否需要做性能優化 — 性能可能會随着時間的變化而變化。

66、避免在整個表上使用count(*) ，它可能會将整個表鎖住。

67、保持查詢一緻，這樣後續類似的查詢就能使用查詢緩存了。

68、如果合适，用 GROUP BY 代替 DISTINCT。

69、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。

70、保證索引簡單，不要在同一列上加多個索引。

71、有時，MySQL 會選擇錯誤的索引，這種情況使用 USE INDEX。

72、使用 SQL_MODE=STRICT 來檢查問題。

73、索引字段少于5個時，UNION 操作用 LIMIT，而不是 OR。

74、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 來代替 UPDATE，避免 UPDATE 前需要先 SELECT。

75、使用索引字段和 ORDER BY 來代替 MAX。

76、避免使用 ORDER BY RAND()。

77、LIMIT M,N 在特定場景下會降低查詢效率，有節制使用。

78、使用 UNION 來代替 WHERE 子句中的子查詢。

79、對 UPDATE 來說，使用 SHARE MODE 來防止排他鎖。

80、重新開機 MySQL 時，記得預熱資料庫，確定将資料加載到記憶體，提高查詢效率。

81、使用 DROP TABLE ，然後再 CREATE TABLE ，而不是 DELETE FROM ，以删除表中所有資料。

82、最小化你要查詢的資料，隻擷取你需要的資料，通常來說不要使用 *。

83、考慮持久連接配接，而不是多次建立連接配接，已減少資源的消耗。

84、基準查詢，包括伺服器的負載，有時一個簡單的查詢會影響其他的查詢。

85、當伺服器的負載增加時，使用SHOW PROCESSLIST來檢視慢的/有問題的查詢。

86、在存有生産環境資料副本的開發環境中，測試所有可疑的查詢。

Mysql 備份過程

87、在二級複制伺服器上進行備份。

88、備份過程中停止資料的複制，以防止出現資料依賴和外鍵限制的不一緻。

89、徹底停止MySQL之後，再從資料檔案進行備份。

90、如果使用MySQL dump進行備份，請同時備份二進制日志 — 確定複制過程不被中斷。

91、不要信任 LVM 快照的備份 — 可能會建立不一緻的資料，将來會是以産生問題。

92、為每個表做一個備份，這樣更容易實作單表的恢複 — 如果資料與其他表是互相獨立的。

93、使用 mysqldump 時，指定 -opt 參數。

94、備份前檢測和優化表。

95、臨時禁用外鍵限制，來提高導入的速度。

96、臨時禁用唯一性檢查，來提高導入的速度。

97、每次備份完後，計算資料庫/表資料和索引的大小，監控其增長。

98、使用定時任務（cron）腳本，來監控從庫複制的錯誤和延遲。

99、定期備份資料。

100、定期測試備份的資料。

101、執行MySQL 監控: Monitis Unveils The World’s First Free On-demand MySQL Monitoring。

推薦閱讀 推薦： 分享一套進階Java筆試題(實拍高清圖) 春節跳槽最新Java面試題及答案整理

長按關注，更多精彩！