MySQL 數據庫，基本上運維都會接觸到，而對于數據庫的性能優化，想必是大家比較關注的，下面分享一些調優技巧。

先決條件

安裝 MySQL，版本大于 5.7
有部分數據
操作系統、數據庫管理員權限

MySQL 系統性能優化

在系統層面，調整硬件和軟件選項以提高 MySQL 性能。

1. 平衡四個主要硬件資源

存儲

如果使用的是傳統的 HDD，可以升級到 SSD。

使用 sysstat 工具包中的 iotop、sar 監控輸入/輸出速率，如果磁盤使用率遠遠高于其他資源，請考慮添加更多存儲或升級到更快的存儲。

CPU

通常認識處理器是衡量系統速度的指標，使用 top 命令可以查看關于 CPU 詳細的使用信息，注意 MySQL 進程及其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 升級成本更高，但是如果已經成為瓶頸了，可能還是需要升級的。

內存

內存表示 MySQL 服務器中的內存總量。可以調整內存緩存（下面細說），以提高性能。但是如果沒有足夠的內存，或者現有內存沒有經過優化，那就不要亂來

與其他資源一樣，如果服務器不斷耗盡內存，可以通過添加內存升級。如果內存不足，服務器將緩存數據存儲到硬盤上，降低性能。

網絡

監控網絡流量，確保基礎架構沒問題。

網絡過載可能導致延遲，數據包丟失甚至服務中斷。

確保有足夠的網絡帶寬來容納正常水平的數據庫流量。

2.使用 InnoDB 而不是 Myisam

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

InnoDB 使用聚集索引并將數據保留在頁面中，這些頁面存儲在連續的物理塊中。如果一個頁面的值太大，InnoDB會將其移動到另一個位置，然后索引該值。此功能有助于將相關數據保存在存儲設備上的同一位置，這意味著物理硬盤驅動器需要較少的時間來訪問數據。

3. 使用最新版本的 MySQL

如果由于歷史遺留問題，只能用舊版本的 MySQL，那沒辦法。但是只要有可能，都用最新版的 MySQL，因為新版性能更好，高級特效更多。

軟件性能調整

這些方法涉及調整 MySQL 配置文件，編寫更高效的 SQL 語句以及創建索引等等

注意：調整時，一次只進行一個更改，進行測試，然后進行下一個。如果一次調整很多，出現性能下降或故障，排查問題不好下手。

4. 使用自動性能改進工具

和大多數工具一樣，不一定適用所有的 MySQL 版本。下面提供幾個工具來評估 MySQL 數據庫及提供更改建議。

MySQL Tuning Primer 這個工具使用于 MySQL >= 5.5 <= 5.7，它可以分析數據庫并建議設置以提高性能。例如，如果感覺系統處理查詢較慢，則建議提高 query_cache_size 參數。

MySQLTuner-perl 使用 Perl 編寫的腳本，可快速查看 MySQL 安裝并進行調整以提高性能和穩定性。檢索當前配置變量和狀態數據，并以簡短的格式顯示一些基本的性能建議。

該腳本適用于 MySQL 多個版本。

日志文件建議是列表中的第一條建議，在底部，則可以看到有關提高MySQL性能的一般建議。

5. 優化查詢

查詢是用于搜索數據庫中與特定值匹配的數據的編碼請求。有些查詢需要很長時間才能返回結果。如果您的環境依賴于自動查詢，它們可能會影響性能，檢查并及時終止堆積的 MySQL 進程。

6. 適當的時候使用索引

查詢經常使用類似于以下的結構：

SELECT … WHERE

這些查詢涉及評估，過濾和檢索結果。可以通過為相關表添加索引，加快查詢速度。

7. 謂詞中的函數

避免在謂詞查詢中使用函數，如：

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

UPPER 創建一個函數，這個函數必須在 SELECT 運行期間進行操作，這會使查詢正在做的工作加倍，盡量避免。

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

通配符％表示零個或多個字符。如果查詢如下所示：

SELECT * FROM MYTABLE WHERE COL1 LIKE '%123'

表達式 "％123" 包含所有以 123 結尾的值。％符號代表任何類型或數量的前面字符，包括任何字符。這樣必須執行全表掃描，盡量避免

9.在 SELECT 函數中指定列

一種常見的表達方式是使用 SELECT *掃描所有數據庫列。如果指定所需的列，則查詢將不需要掃描不相關的列。

10.適當使用 ORDER BY ORDER BY 表達式

按指定的列對結果進行排序。可用于一次按兩列排序。這些應該以相同的順序（升序或降序）進行排序。如果嘗試按不同順序對不同的列進行排序，則會降低性能。可以將其與索引結合使用以加快排序速度。

11. JOIN, UNION, DISTINCT

盡可能嘗試使用內部聯接。外部聯接將查看指定列之外的其他數據。如果您需要這些數據，那很好，但是包含不必要的數據會浪費性能。查詢有時包含UNION和DISTINCT命令。就像外部聯接一樣，可以在必要時使用這些表達式。但是，它們增加了數據庫的其他排序和讀取。如果您不需要它們，最好找到一個更有效的表達方式。

12.使用EXPLAIN功能

將 EXPLAIN 表達式附加到查詢的開頭將讀取并評估查詢。如果表達式效率低下或結構混亂，EXPLAIN 可以幫助您找到它們。然后，您可以調整查詢的措詞，以避免意外的表掃描或其他性能影響。

13.MySQL服務器配置

此配置涉及更改 my.cnf 文件。請謹慎操作，并一次進行較小的更改。

query_cache_size –指定等待運行的MySQL查詢的緩存大小。建議從較小的值開始，大約10MB，然后再增加到不超過 100-200MB。如果緩存的查詢過多，您會遇到 Waiting for cache lock. 的級聯查詢。如果您的查詢一直在備份，則更好的過程是使用 EXPLAIN 評估每個查詢并找到提高效率的方法。

max_connection –指允許進入數據庫的連接數。如果您由于Too many connections而出錯，則增加此值可能會有所幫助。
innodb_buffer_pool_size –此設置將系統內存分配為數據庫的數據緩存。如果有大量數據，請增加此值。注意運行其他系統資源所需的RAM。
innodb_io_capacity -此變量設置存儲設備的輸入/輸出速率。這與存儲驅動器的類型和速度直接相關。 5400 rpm HDD的容量將比高端 SSD 或 Intel Optane 低得多。可以調整此值以更好地匹配硬件。