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)黾哟酥怠W⒁膺\(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)整此值以更好地匹配硬件。
結(jié)論
現(xiàn)在,您應(yīng)該知道如何提高 MySQL 性能和調(diào)整數(shù)據(jù)庫(kù)。
查找瓶頸(硬件和軟件)、執(zhí)行更多工作所需的查詢,并考慮使用自動(dòng)化工具和EXPLAIN 函數(shù)來(lái)評(píng)估數(shù)據(jù)庫(kù)。