MySQL是一個(gè)強(qiáng)大的開(kāi)源數(shù)據(jù)庫(kù)。隨著MySQL上的應(yīng)用越來(lái)越多，MySQL逐漸遇到了瓶頸。這里提供 101 條優(yōu)化 MySQL 的建議。有些技巧適合特定的安裝環(huán)境，但是思路是相通的。我已經(jīng)將它們分成了幾類(lèi)以幫助你理解。

MySQL監(jiān)控MySQL服務(wù)器硬件和OS（操作系統(tǒng)）調(diào)優(yōu)：

1、有足夠的物理內(nèi)存，能將整個(gè)InnoDB文件加載到內(nèi)存里 —— 如果訪(fǎng)問(wèn)的文件在內(nèi)存里，而不是在磁盤(pán)上，InnoDB會(huì)快很多。

2、全力避免 Swap 操作 — 交換（swapping）是從磁盤(pán)讀取數(shù)據(jù)，所以會(huì)很慢。

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

4、使用一個(gè)高級(jí)磁盤(pán)陣列 — 最好是 RAID10 或者更高。

5、避免使用RAID5 — 和校驗(yàn)需要確保完整性，開(kāi)銷(xiāo)很高。

6、將你的操作系統(tǒng)和數(shù)據(jù)分開(kāi)，不僅僅是邏輯上要分開(kāi)，物理上也要分開(kāi) — 操作系統(tǒng)的讀寫(xiě)開(kāi)銷(xiāo)會(huì)影響數(shù)據(jù)庫(kù)的性能。

7、將臨時(shí)文件和復(fù)制日志與數(shù)據(jù)文件分開(kāi) — 后臺(tái)的寫(xiě)操作影響數(shù)據(jù)庫(kù)從磁盤(pán)文件的讀寫(xiě)操作。

8、更多的磁盤(pán)空間等于更高的速度。

9、磁盤(pán)速度越快越好。

10、SAS優(yōu)于SATA。

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

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

13、避免使用軟磁盤(pán)陣列。

14. 考慮使用固態(tài)IO卡（不是磁盤(pán)）來(lái)作為數(shù)據(jù)分區(qū) — 幾乎對(duì)所有量級(jí)數(shù)據(jù)，這種卡能夠支持 2 GBps 的寫(xiě)操作。

15、在 Linux 系統(tǒng)上，設(shè)置 swappiness 的值為0 — 沒(méi)有理由在數(shù)據(jù)庫(kù)服務(wù)器上緩存文件，這種方式在Web服務(wù)器或桌面應(yīng)用中用的更多。

16、盡可能使用 noatime 和 nodirtime 來(lái)掛載文件系統(tǒng) — 沒(méi)有必要為每次訪(fǎng)問(wèn)來(lái)更新文件的修改時(shí)間。

17、使用 XFS 文件系統(tǒng) — 一個(gè)比ext3更快的、更小的文件系統(tǒng)，擁有更多的日志選項(xiàng)，同時(shí)，MySQL在ext3上存在雙緩沖區(qū)的問(wèn)題。

18、優(yōu)化你的 XFS 文件系統(tǒng)日志和緩沖區(qū)參數(shù) – -為了獲取最大的性能基準(zhǔn)。

19、在Linux系統(tǒng)中，使用 NOOP 或 DEADLINE IO 調(diào)度器 — CFQ 和 ANTICIPATORY 調(diào)度器已經(jīng)被證明比 NOOP 和 DEADLINE 慢。

20、使用 64 位操作系統(tǒng) — 有更多的內(nèi)存能用于尋址和 MySQL 使用。

21、將不用的包和后臺(tái)程序從服務(wù)器上刪除 — 減少資源占用。

22、將使用 MySQL 的 host 和 MySQL自身的 host 都配置在一個(gè) host 文件中 — 這樣沒(méi)有 DNS 查找。

23、永遠(yuǎn)不要強(qiáng)制殺死一個(gè)MySQL進(jìn)程 — 你將損壞數(shù)據(jù)庫(kù)，并運(yùn)行備份。

24、讓你的服務(wù)器只服務(wù)于MySQL — 后臺(tái)處理程序和其他服務(wù)會(huì)占用數(shù)據(jù)庫(kù)的 CPU 時(shí)間。

MySQL 配置：

25、使用 innodb_flush_method=O_DIRECT 來(lái)避免寫(xiě)的時(shí)候出現(xiàn)雙緩沖區(qū)。

26、避免使用 O_DIRECT 和 EXT3 文件系統(tǒng) — 這會(huì)把所有寫(xiě)入的東西序列化。

27、分配足夠 innodb_buffer_pool_size ，來(lái)將整個(gè)InnoDB 文件加載到內(nèi)存 — 減少?gòu)拇疟P(pán)上讀。

28、不要讓 innodb_log_file_size 太大，這樣能夠更快，也有更多的磁盤(pán)空間 — 經(jīng)常刷新有利降低發(fā)生故障時(shí)的恢復(fù)時(shí)間。

29、不要同時(shí)使用 innodb_thread_concurrency 和 thread_concurrency 變量 — 這兩個(gè)值不能兼容。

30、為 max_connections 指定一個(gè)小的值 — 太多的連接將耗盡你的RAM，導(dǎo)致整個(gè)MySQL服務(wù)器被鎖定。

31、保持 thread_cache 在一個(gè)相對(duì)較高的數(shù)值，大約是 16 — 防止打開(kāi)連接時(shí)候速度下降。

32、使用 skip-name-resolve — 移除 DNS 查找。

33、如果你的查詢(xún)重復(fù)率比較高，并且你的數(shù)據(jù)不是經(jīng)常改變，請(qǐng)使用查詢(xún)緩存 — 但是，在經(jīng)常改變的數(shù)據(jù)上使用查詢(xún)緩存會(huì)對(duì)性能有負(fù)面影響。

34、增加 temp_table_size — 防止磁盤(pán)寫(xiě)。

35、增加 max_heap_table_size — 防止磁盤(pán)寫(xiě)。

36、不要將 sort_buffer_size 的值設(shè)置的太高 — 可能導(dǎo)致連接很快耗盡所有內(nèi)存。

37、監(jiān)控 key_read_requests 和 key_reads，以便確定 key_buffer 的值 — key 的讀需求應(yīng)該比 key_reads 的值更高，否則使用 key_buffer 就沒(méi)有效率了。

38、設(shè)置 innodb_flush_log_at_trx_commit = 0 可以提高性能，但是保持默認(rèn)值（1）的話(huà)，能保證數(shù)據(jù)的完整性，也能保證復(fù)制不會(huì)滯后。

39、有一個(gè)測(cè)試環(huán)境，便于測(cè)試你的配置，可以經(jīng)常重啟，不會(huì)影響生產(chǎn)環(huán)境。

MySQL Schema 優(yōu)化：

40、保證你的數(shù)據(jù)庫(kù)的整潔性。

41、歸檔老數(shù)據(jù) — 刪除查詢(xún)中檢索或返回的多余的行

42、在數(shù)據(jù)上加上索引。

43、不要過(guò)度使用索引，評(píng)估你的查詢(xún)。

44、壓縮 text 和 blob 數(shù)據(jù)類(lèi)型 — 為了節(jié)省空間，減少?gòu)拇疟P(pán)讀數(shù)據(jù)。

45、UTF 8 和 UTF16 比 latin1 慢。

46、有節(jié)制的使用觸發(fā)器。

47、保持?jǐn)?shù)據(jù)最小量的冗余 — 不要復(fù)制沒(méi)必要的數(shù)據(jù).

48、使用鏈接表，而不是擴(kuò)展行。

49、注意你的數(shù)據(jù)類(lèi)型，盡可能的使用最小的。

50、如果其他數(shù)據(jù)需要經(jīng)常需要查詢(xún)，而 blob/text 不需要，則將 blob/text 數(shù)據(jù)域其他數(shù)據(jù)分離。

51、經(jīng)常檢查和優(yōu)化表。

52、經(jīng)常做重寫(xiě) InnoDB 表的優(yōu)化。

53、有時(shí)，增加列時(shí)，先刪除索引，之后在加上索引會(huì)更快。

54、為不同的需求選擇不同的存儲(chǔ)引擎。

55、日志表或?qū)徲?jì)表使用ARCHIVE存儲(chǔ)引擎 — 寫(xiě)的效率更高。

56、將 session 數(shù)據(jù)存儲(chǔ)在 memcache 中，而不是 MySQL 中 — memcache 可以設(shè)置自動(dòng)過(guò)期，防止MySQL對(duì)臨時(shí)數(shù)據(jù)高成本的讀寫(xiě)操作。

57、如果字符串的長(zhǎng)度是可變的，則使用VARCHAR代替CHAR — 節(jié)約空間，因?yàn)镃HAR是固定長(zhǎng)度，而VARCHAR不是（utf8 不受這個(gè)影響）。

58、逐步對(duì) schema 做修改 — 一個(gè)小的變化將產(chǎn)生的巨大的影響。

59、在開(kāi)發(fā)環(huán)境測(cè)試所有 schema 變動(dòng)，而不是在生產(chǎn)環(huán)境的鏡像上去做。

60、不要隨意改變你的配置文件，這可能產(chǎn)生非常大的影響。

61、有時(shí)候，少量的配置會(huì)更好。

62、質(zhì)疑使用通用的MySQL配置文件。

查詢(xún)優(yōu)化：

63、使用慢查詢(xún)?nèi)罩荆页鰣?zhí)行慢的查詢(xún)。

64、使用 EXPLAIN 來(lái)決定查詢(xún)功能是否合適。

65、經(jīng)常測(cè)試你的查詢(xún)，看是否需要做性能優(yōu)化 — 性能可能會(huì)隨著時(shí)間的變化而變化。

66、避免在整個(gè)表上使用count(*) ，它可能會(huì)將整個(gè)表鎖住。

67、保持查詢(xún)一致，這樣后續(xù)類(lèi)似的查詢(xún)就能使用查詢(xún)緩存了。

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

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

70、保證索引簡(jiǎn)單，不要在同一列上加多個(gè)索引。

71、有時(shí)，MySQL 會(huì)選擇錯(cuò)誤的索引，這種情況使用 USE INDEX。

72、使用 SQL_MODE=STRICT 來(lái)檢查問(wèn)題。

73、索引字段少于5個(gè)時(shí)，UNION 操作用 LIMIT，而不是 OR。

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

75、使用索引字段和 ORDER BY 來(lái)代替 MAX。

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

77、LIMIT M,N 在特定場(chǎng)景下會(huì)降低查詢(xún)效率，有節(jié)制使用。

78、使用 UNION 來(lái)代替 WHERE 子句中的子查詢(xún)。

79、對(duì) UPDATE 來(lái)說(shuō)，使用 SHARE MODE 來(lái)防止排他鎖。

80、重啟 MySQL 時(shí)，記得預(yù)熱數(shù)據(jù)庫(kù)，確保將數(shù)據(jù)加載到內(nèi)存，提高查詢(xún)效率。

81、使用 DROP TABLE ，然后再 CREATE TABLE ，而不是 DELETE FROM ，以刪除表中所有數(shù)據(jù)。

82、最小化你要查詢(xún)的數(shù)據(jù)，只獲取你需要的數(shù)據(jù)，通常來(lái)說(shuō)不要使用 *。

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

84、基準(zhǔn)查詢(xún)，包括服務(wù)器的負(fù)載，有時(shí)一個(gè)簡(jiǎn)單的查詢(xún)會(huì)影響其他的查詢(xún)。

85、當(dāng)服務(wù)器的負(fù)載增加時(shí)，使用SHOW PROCESSLIST來(lái)查看慢的/有問(wèn)題的查詢(xún)。

86、在存有生產(chǎn)環(huán)境數(shù)據(jù)副本的開(kāi)發(fā)環(huán)境中，測(cè)試所有可疑的查詢(xún)。

MySQL備份過(guò)程：

87、在二級(jí)復(fù)制服務(wù)器上進(jìn)行備份。

88、備份過(guò)程中停止數(shù)據(jù)的復(fù)制，以防止出現(xiàn)數(shù)據(jù)依賴(lài)和外鍵約束的不一致。

89、徹底停止MySQL之后，再?gòu)臄?shù)據(jù)文件進(jìn)行備份。

90、如果使用MySQL dump進(jìn)行備份，請(qǐng)同時(shí)備份二進(jìn)制日志 — 確保復(fù)制過(guò)程不被中斷。

91、不要信任 LVM 快照的備份 — 可能會(huì)創(chuàng)建不一致的數(shù)據(jù)，將來(lái)會(huì)因此產(chǎn)生問(wèn)題。

92、為每個(gè)表做一個(gè)備份，這樣更容易實(shí)現(xiàn)單表的恢復(fù) — 如果數(shù)據(jù)與其他表是相互獨(dú)立的。

93、使用 mysqldump 時(shí)，指定 -opt 參數(shù)。

94、備份前檢測(cè)和優(yōu)化表。

95、臨時(shí)禁用外鍵約束，來(lái)提高導(dǎo)入的速度。

96、臨時(shí)禁用唯一性檢查，來(lái)提高導(dǎo)入的速度。

97、每次備份完后，計(jì)算數(shù)據(jù)庫(kù)/表數(shù)據(jù)和索引的大小，監(jiān)控其增長(zhǎng)。

98、使用定時(shí)任務(wù)（cron）腳本，來(lái)監(jiān)控從庫(kù)復(fù)制的錯(cuò)誤和延遲。

99、定期備份數(shù)據(jù)。

100、定期測(cè)試備份的數(shù)據(jù)