MySQL 數(shù)據(jù)庫(kù)，基本上運(yùn)維都會(huì)接觸到，而對(duì)于數(shù)據(jù)庫(kù)的性能優(yōu)化，想必是大家比較關(guān)注的，下面分享一些調(diào)優(yōu)技巧。

先決條件

安裝 MySQL，版本大于 5.7
有部分?jǐn)?shù)據(jù)
操作系統(tǒng)、數(shù)據(jù)庫(kù)管理員權(quán)限

MySQL 系統(tǒng)性能優(yōu)化

在系統(tǒng)層面，調(diào)整硬件和軟件選項(xiàng)以提高 MySQL 性能。

1. 平衡四個(gè)主要硬件資源

存儲(chǔ)

如果使用的是傳統(tǒng)的 HDD，可以升級(jí)到 SSD。

使用 sysstat 工具包中的 iotop、sar 監(jiān)控輸入/輸出速率，如果磁盤使用率遠(yuǎn)遠(yuǎn)高于其他資源，請(qǐng)考慮添加更多存儲(chǔ)或升級(jí)到更快的存儲(chǔ)。

CPU

通常認(rèn)識(shí)處理器是衡量系統(tǒng)速度的指標(biāo)，使用 top 命令可以查看關(guān)于 CPU 詳細(xì)的使用信息，注意 MySQL 進(jìn)程及其CPU 使用率。

top - 10:41:15 up 205 days, 23:03,  1 user,  load average: 1.07, 1.02, 0.98
Tasks: 424 total,   1 running, 423 sleeping,   0 stopped,   0 zombie
%Cpu(s):  8.1 us,  0.5 sy,  0.0 ni, 91.1 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 13145304+total, 59450800 free, 53483316 used, 18518920 buff/cache
KiB Swap: 16777212 total, 16546300 free,   230912 used. 76746272 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                 
 7133 mysql     20   0  103.1g  45.5G  10148 S 205.6 36.3  94658:39 mysqld                                                                     
 6754 mongod    20   0 4504956   3.2g  11872 S  64.8  2.6   1916:34 mongod                                                                     
29632 root      20   0   36.5g 506904   6884 S   0.0  0.4 154:00.13 JAVA                                                                       
15795 gdm       20   0 5776296 199816  15260 S   0.0  0.2  72:39.84 gnome-shell                                                                
31684 root      20   0  353572  98864   1308 S   1.9  0.1 386:24.16 redis-server                                                               
31686 root      20   0  320804  98496   1108 S   1.9  0.1 288:01.69 redis-server                                                               
31688 root      20   0  326948  97988   1108 S   0.0  0.1 288:37.66 redis-server                                                               
16576 gdm       20   0  701592  60952    856 S   3.7  0.0   2688:28 gsd-color                                                                  
20199 root      20   0  292132  39696   1212 S   0.0  0.0 483:14.41 redis-server                                                               
 6437 root      20   0   84744  39352  39100 S   0.0  0.0   1:16.05 systemd-journal                                                            
17429 root      20   0  123500  30252   4668 S   0.0  0.0   4476:21 node_exporter                                                              
12977 root      20   0  411672  25976  21820 S   0.0  0.0   8:10.97 rsyslogd                                                                   
12480 polkitd   20   0  620076   8848   2552 S   0.0  0.0   9:42.49 polkitd                                                                    
30362 root      20   0  350468   6728   4636 S   0.0  0.0   0:00.01 abrt-dbus                                                                  
13071 root      20   0  435300   5552   3940 S   0.0  0.0   2:36.75 X                                                                          
30333 root      20   0  116212   4728   3680 S   0.0  0.0   0:00.02 sshd                                                                       
12491 root      20   0  519268   3872   1984 S   0.0  0.0  63:00.71 udisksd                                                                    
16584 gdm       20   0 1069144   3688   1248 S   0.0  0.0   0:31.78 gsd-media-keys                                                             
16544 root      20   0  398376   3612   1124 S   0.0  0.0   0:34.98 boltd

CPU 升級(jí)成本更高，但是如果已經(jīng)成為瓶頸了，可能還是需要升級(jí)的。

內(nèi)存

內(nèi)存表示 MySQL 服務(wù)器中的內(nèi)存總量。可以調(diào)整內(nèi)存緩存（下面細(xì)說(shuō)），以提高性能。但是如果沒(méi)有足夠的內(nèi)存，或者現(xiàn)有內(nèi)存沒(méi)有經(jīng)過(guò)優(yōu)化，那就不要亂來(lái)

與其他資源一樣，如果服務(wù)器不斷耗盡內(nèi)存，可以通過(guò)添加內(nèi)存升級(jí)。如果內(nèi)存不足，服務(wù)器將緩存數(shù)據(jù)存儲(chǔ)到硬盤上，降低性能。

網(wǎng)絡(luò)

監(jiān)控網(wǎng)絡(luò)流量，確保基礎(chǔ)架構(gòu)沒(méi)問(wèn)題。

網(wǎng)絡(luò)過(guò)載可能導(dǎo)致延遲，數(shù)據(jù)包丟失甚至服務(wù)中斷。

確保有足夠的網(wǎng)絡(luò)帶寬來(lái)容納正常水平的數(shù)據(jù)庫(kù)流量。

2.使用 InnoDB 而不是 Myisam

Myisam 是 MySQL 5.1 及之前的版本使用的存儲(chǔ)引擎，不支持事務(wù)以及行級(jí)鎖，而且一個(gè)毫無(wú)疑問(wèn)的缺陷是崩潰后無(wú)法安全恢復(fù)，效率也較低。InnoDB 是 MySQL 5.5 版本以后的默認(rèn)存儲(chǔ)引擎，InnoDB 是為 Mysql 處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的 CPU 效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的。在數(shù)據(jù)量大的網(wǎng)站或是應(yīng)用中 Innodb 是倍受青睞的。

InnoDB 使用聚集索引并將數(shù)據(jù)保留在頁(yè)面中，這些頁(yè)面存儲(chǔ)在連續(xù)的物理塊中。如果一個(gè)頁(yè)面的值太大，InnoDB會(huì)將其移動(dòng)到另一個(gè)位置，然后索引該值。此功能有助于將相關(guān)數(shù)據(jù)保存在存儲(chǔ)設(shè)備上的同一位置，這意味著物理硬盤驅(qū)動(dòng)器需要較少的時(shí)間來(lái)訪問(wèn)數(shù)據(jù)。

3. 使用最新版本的 MySQL

如果由于歷史遺留問(wèn)題，只能用舊版本的 MySQL，那沒(méi)辦法。但是只要有可能，都用最新版的 MySQL，因?yàn)樾掳嫘阅芨茫呒?jí)特效更多。

軟件性能調(diào)整

這些方法涉及調(diào)整 MySQL 配置文件，編寫更高效的 SQL 語(yǔ)句以及創(chuàng)建索引等等

注意：調(diào)整時(shí)，一次只進(jìn)行一個(gè)更改，進(jìn)行測(cè)試，然后進(jìn)行下一個(gè)。如果一次調(diào)整很多，出現(xiàn)性能下降或故障，排查問(wèn)題不好下手。

4. 使用自動(dòng)性能改進(jìn)工具

和大多數(shù)工具一樣，不一定適用所有的 MySQL 版本。下面提供幾個(gè)工具來(lái)評(píng)估 MySQL 數(shù)據(jù)庫(kù)及提供更改建議。

MySQL Tuning Primer 這個(gè)工具使用于 MySQL >= 5.5 <= 5.7，它可以分析數(shù)據(jù)庫(kù)并建議設(shè)置以提高性能。例如，如果感覺(jué)系統(tǒng)處理查詢較慢，則建議提高 query_cache_size 參數(shù)。

MySQLTuner-perl 使用 Perl 編寫的腳本，可快速查看 MySQL 安裝并進(jìn)行調(diào)整以提高性能和穩(wěn)定性。檢索當(dāng)前配置變量和狀態(tài)數(shù)據(jù)，并以簡(jiǎn)短的格式顯示一些基本的性能建議。

該腳本適用于 MySQL 多個(gè)版本。

日志文件建議是列表中的第一條建議，在底部，則可以看到有關(guān)提高M(jìn)ySQL性能的一般建議。

5. 優(yōu)化查詢

查詢是用于搜索數(shù)據(jù)庫(kù)中與特定值匹配的數(shù)據(jù)的編碼請(qǐng)求。有些查詢需要很長(zhǎng)時(shí)間才能返回結(jié)果。如果您的環(huán)境依賴于自動(dòng)查詢，它們可能會(huì)影響性能，檢查并及時(shí)終止堆積的 MySQL 進(jìn)程。

6. 適當(dāng)?shù)臅r(shí)候使用索引

查詢經(jīng)常使用類似于以下的結(jié)構(gòu)：

SELECT … WHERE

這些查詢涉及評(píng)估，過(guò)濾和檢索結(jié)果。可以通過(guò)為相關(guān)表添加索引，加快查詢速度。

7. 謂詞中的函數(shù)

避免在謂詞查詢中使用函數(shù)，如：

SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'

UPPER 創(chuàng)建一個(gè)函數(shù)，這個(gè)函數(shù)必須在 SELECT 運(yùn)行期間進(jìn)行操作，這會(huì)使查詢正在做的工作加倍，盡量避免。

8. 避免在謂詞中使用％通配符

通配符％表示零個(gè)或多個(gè)字符。如果查詢?nèi)缦滤荆?/p>

SELECT * FROM MYTABLE WHERE COL1 LIKE '%123'

表達(dá)式 "％123" 包含所有以 123 結(jié)尾的值。％符號(hào)代表任何類型或數(shù)量的前面字符，包括任何字符。這樣必須執(zhí)行全表掃描，盡量避免

9.在 SELECT 函數(shù)中指定列

一種常見(jiàn)的表達(dá)方式是使用 SELECT *掃描所有數(shù)據(jù)庫(kù)列。如果指定所需的列，則查詢將不需要掃描不相關(guān)的列。

10.適當(dāng)使用 ORDER BY ORDER BY 表達(dá)式

按指定的列對(duì)結(jié)果進(jìn)行排序。可用于一次按兩列排序。這些應(yīng)該以相同的順序（升序或降序）進(jìn)行排序。如果嘗試按不同順序?qū)Σ煌牧羞M(jìn)行排序，則會(huì)降低性能。可以將其與索引結(jié)合使用以加快排序速度。

11. JOIN, UNION, DISTINCT

盡可能嘗試使用內(nèi)部聯(lián)接。外部聯(lián)接將查看指定列之外的其他數(shù)據(jù)。如果您需要這些數(shù)據(jù)，那很好，但是包含不必要的數(shù)據(jù)會(huì)浪費(fèi)性能。查詢有時(shí)包含UNION和DISTINCT命令。就像外部聯(lián)接一樣，可以在必要時(shí)使用這些表達(dá)式。但是，它們?cè)黾恿藬?shù)據(jù)庫(kù)的其他排序和讀取。如果您不需要它們，最好找到一個(gè)更有效的表達(dá)方式。

12.使用EXPLAIN功能

將 EXPLAIN 表達(dá)式附加到查詢的開(kāi)頭將讀取并評(píng)估查詢。如果表達(dá)式效率低下或結(jié)構(gòu)混亂，EXPLAIN 可以幫助您找到它們。然后，您可以調(diào)整查詢的措詞，以避免意外的表掃描或其他性能影響。

13.MySQL服務(wù)器配置

此配置涉及更改 my.cnf 文件。請(qǐng)謹(jǐn)慎操作，并一次進(jìn)行較小的更改。

query_cache_size –指定等待運(yùn)行的MySQL查詢的緩存大小。建議從較小的值開(kāi)始，大約10MB，然后再增加到不超過(guò) 100-200MB。如果緩存的查詢過(guò)多，您會(huì)遇到 Waiting for cache lock. 的級(jí)聯(lián)查詢。如果您的查詢一直在備份，則更好的過(guò)程是使用 EXPLAIN 評(píng)估每個(gè)查詢并找到提高效率的方法。

max_connection –指允許進(jìn)入數(shù)據(jù)庫(kù)的連接數(shù)。如果您由于Too many connections而出錯(cuò)，則增加此值可能會(huì)有所幫助。
innodb_buffer_pool_size –此設(shè)置將系統(tǒng)內(nèi)存分配為數(shù)據(jù)庫(kù)的數(shù)據(jù)緩存。如果有大量數(shù)據(jù)，請(qǐng)?jiān)黾哟酥怠Ｗ⒁膺\(yùn)行其他系統(tǒng)資源所需的RAM。
innodb_io_capacity -此變量設(shè)置存儲(chǔ)設(shè)備的輸入/輸出速率。這與存儲(chǔ)驅(qū)動(dòng)器的類型和速度直接相關(guān)。 5400 rpm HDD的容量將比高端 SSD 或 Intel Optane 低得多。可以調(diào)整此值以更好地匹配硬件。