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資料從其他資料分離出來.

51.檢查和經常優化表.

52. 經常重寫InnoDB表優化.

53. 有時，當添加列時删除索引，然後在添加回來索引，這樣就會更快.

54. 針對不同的需求，使用不同的存儲引擎.

55. 使用歸檔存儲引擎日志表或審計表-這是更有效地寫道.

56.  會話資料存儲在緩存（memcache）的而不是MySQL中 – 緩存允許自動自動填值的，并阻止您建立難以讀取和寫入到MySQL的時空資料.

57.存儲可變長度的字元串時使用VARCHAR而不是CHAR – 節省空間，因為固定長度的CHAR，而VARCHAR長度不固定（UTF8不受此影響）.

58. 逐漸進行模式的變化 – 一個小的變化，可以有巨大的影響.

59.在開發環境中測試所有模式，反映生産變化.

60. 不要随意更改你的配置檔案中的值，它可以産生災難性的影響.

61. 有時候，在MySQL的configs少即是多.

62.有疑問時使用一個通用的MySQL配置檔案.

查詢優化:

63. 使用慢查詢日志去發現慢查詢。

64. 使用執行計劃去判斷查詢是否正常運作。

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. 為了 避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去實作。

75. 不要使用 MAX,使用索引字段和ORDER BY子句。

76. 避免使用ORDER BY RAND().

77。LIMIT M，N實際上可以減緩查詢在某些情況下，有節制地使用。

78。在WHERE子句中使用UNION代替子查詢。

79。對于UPDATES（更新），使用 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. 通過自動排程腳本監控複制執行個體的錯誤和延遲。

99. 定期執行備份。

100. 定期測試你的備份。