CPU 占用過高常見現象

在使用 MySQL 的過程中會遇到各種瓶頸問題，常見的是 IO 瓶頸，但是有時候會出現服務器 CPU 使用率超過 100%，應用頁面訪問慢，登錄服務器負載很高。而導致這個問題竟然是 MySQL 進程，按理說如果 MySQL 運行穩定，服務器的 CPU 資源并不會跑滿，如果出現這個問題，初步可以斷定，是 MySQL 實例中出現了問題 SQL。

CPU 占用過高常見原因

CPU 占用過高常見原因：

服務器硬件問題
內存溢出
業務高并發

如果是業務高并發引起，可以理解為一種業務繁忙的狀態，有可能業務猛增，有可能是定期或者臨時的并發窗口：

數據庫對象設計不合理
觸發器導致
表索引設計不合理
數據庫鎖導致，如行鎖沖突、行鎖等待、鎖超時、死鎖等
系統架構沒有緩存中間件
讀寫分離配置不合理
OLTP 系統承載了 OLAP 的業務需求
未合理升級改造為集群環境
未配置異構數據分析系統
MySQL 系統參數設置不合理
問題 SQL 導致

SQL 問題導致 CPU 使用率過高是最常見的現象，比如 group by、order by、join 等，這些很大程度影響 SQL 執行效率，從而占用大量的系統資源。

說了這么多常見原因，其實總結一句話來說就是現有系統的現有配置下的現有環境提供不了所需要的數據查詢、分析、執行能力，針對這個問題，首先我們要發現問題的所在，就是說我們要準確的定位問題，然后針對問題進行優化，再考慮其他升級改造的事情。

定位分析問題 SQL

光說不練假把式，接下來我們比劃比劃。

首先，搭建一套模擬環境，這里我讓我的得力助手 DBdeployer 來幫我搭建一套模擬環境。

DBdeployer 是用 Go 語言實現的一款非常強大與高效的部署數據庫環境的開源工具，我們完全可以用它來部署開發、測試環境。如果你對這個助手感興趣，可以看我的另外一篇 Chat。

MySQL 安裝部署：我是如何“秒級”部署 MySQL 集群

使用 DBdeployer 來部署一個單點 5.7.27 版本的 MySQL，并配置一個模擬 CPU 占比高的測試環境。

[root@localhost ~]# dbdeployer deploy single 5.7.27
Database installed in /dbdata/sandboxes/msb_5_7_27
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started

創建測試表：

[root@localhost ~]# cd /dbdata/sandboxes/msb_5_7_27/
[root@localhost msb_5_7_27]# ./use 
mysql [localhost:5727] {msandbox} ((none)) > use test
Database changed
mysql [localhost:5727] {msandbox} (test) > create table t_cpu(id int primary key auto_increment);        
Query OK, 0 rows affected (0.39 sec)

插入大量模擬測試數據：

mysql [localhost:5727] {msandbox} (test) > insert into t_cpu values(),(),(),(),();
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql [localhost:5727] {msandbox} (test) > insert into t_cpu select id+(select count(*) from t_cpu) from t_cpu;
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

......
......

mysql [localhost:5727] {msandbox} (test) > insert into t_cpu select id+(select count(*) from t_cpu) from t_cpu;
Query OK, 5242880 rows affected (1 min 8.84 sec)
Records: 5242880  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {msandbox} (test) > select count(*) from t_cpu;                                         
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (2.22 sec)

至此我們已經造了 10485760 條數據。

有 1000W+ 的數據量了，我們模擬一個業務場景，讓 CPU 嗨（high）起來。

執行一個慢查詢：

mysql [localhost:5727] {msandbox} (test) > select * from t_cpu order by rand() limit 1;

另外開一個會話，top 看看進程：

top - 15:21:20 up 33 days, 23:10,  3 users,  load average: 0.38, 0.19, 0.21
Tasks: 202 total,   2 running, 200 sleeping,   0 stopped,   0 zombie
%Cpu(s): 23.3 us,  3.6 sy,  0.0 ni, 73.1 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  8173716 total,  2867684 free,  1059276 used,  4246756 buff/cache
KiB Swap:  2097148 total,  2097140 free,        8 used.  6761912 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                              
32232 root      20   0 1443252 356688  11748 S 107.0  4.4   2:03.82 mysqld                                                                               
  296 root      20   0   67044  15588   1440 S   0.7  0.2 325:32.46 plymouthd                                                                            
  600 root      20   0  162032   2360   1580 R   0.3  0.0   0:04.21 top                                                                                  
 1901 gnome-i+  20   0  714116  22436   9168 S   0.3  0.3  98:46.20 gsd-color                                                                            
    1 root      20   0  191264   4192   2632 S   0.0  0.1  18:39.97 systemd                                                                              
    2 root      20   0       0      0      0 S   0.0  0.0   0:01.28 kthreadd                                                                             
    4 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H                                                                         
    6 root      20   0       0      0      0 S   0.0  0.0   0:00.95 ksoftirqd/0                                                                          
    7 root      rt   0       0      0      0 S   0.0  0.0   0:02.08 migration/0                                                                          
    8 root      20   0       0      0      0 S   0.0  0.0   0:00.00 rcu_bh

通過 top 我們發現有一個 32232 的進程使得 CPU 使用率已經超過了 100%。

接下來我們具體分析分析，究竟是什么導致 CPU 使用率達到 100%以上的。

檢查內存使用情況

[root@localhost ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           7982        1033        2801          40        4147        6604
Swap:          2047           0        2047

內存使用率還可以，應該不是內存影響導致的問題。

檢查服務器線程

方法一：

top -H -p <mysqld 進程 id>

先通過 top 找出占用 CPU 使用率 100% 的 MySQL 進程 32232，在具體查看該進程下的線程情況。

[root@localhost ~]# top -H -p 32232
top - 15:30:32 up 33 days, 23:20,  3 users,  load average: 0.18, 0.15, 0.18
Threads:  28 total,   1 running,  27 sleeping,   0 stopped,   0 zombie
%Cpu(s): 24.4 us,  3.2 sy,  0.0 ni, 72.4 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  8173716 total,  2867420 free,  1059520 used,  4246776 buff/cache
KiB Swap:  2097148 total,  2097140 free,        8 used.  6761668 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                               
32272 root      20   0 1443252 356688  11748 R 99.7  4.4   2:25.74 mysqld                                                                                
32244 root       0 -20 1443252 356688  11748 S  3.7  4.4   0:08.68 mysqld                                                                                
32236 root      20   0 1443252 356688  11748 S  1.7  4.4   0:01.45 mysqld                                                                                
32237 root      20   0 1443252 356688  11748 S  1.7  4.4   0:01.43 mysqld                                                                                
32238 root      20   0 1443252 356688  11748 S  1.3  4.4   0:01.43 mysqld                                                                                
32239 root      20   0 1443252 356688  11748 S  1.3  4.4   0:01.42 mysqld                                                                                
32241 root      20   0 1443252 356688  11748 S  0.3  4.4   0:00.75 mysqld                                                                                
32250 root      20   0 1443252 356688  11748 S  0.3  4.4   0:00.98 mysqld                                                                                
32232 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.31 mysqld                                                                                
32233 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32234 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.23 mysqld                                                                                
32235 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.31 mysqld                                                                                
32240 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.74 mysqld                                                                                
32242 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.72 mysqld                                                                                
32243 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.69 mysqld                                                                                
32247 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.16 mysqld                                                                                
32248 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.23 mysqld                                                                                
32249 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.02 mysqld                                                                                
32251 root      20   0 1443252 356688  11748 S  0.0  4.4   0:08.04 mysqld                                                                                
32252 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32253 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32254 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32255 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32256 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.18 mysqld                                                                                
32257 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.03 mysqld                                                                                
32258 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32259 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld                                                                                
32260 root      20   0 1443252 356688  11748 S  0.0  4.4   0:00.00 mysqld

結果可以明顯的看到 32272 這個線程的 CPU 使用率異常。

方法二：

pidstat -t -p <mysqld 進程 id> 1 5

先通過 top 找出占用 CPU 使用率 100% 的 MySQL 進程 32232，使用 pidstat 工具在具體查看該進程下的線程情況。

pidstat -t -p 32232 1 5

Average:      UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
Average:        0     32232         -   85.00   12.60    0.00   97.60     -  mysqld
Average:        0         -     32232    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32233    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32234    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32235    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32236    1.00    0.40    0.00    1.40     -  |__mysqld
Average:        0         -     32237    0.80    0.40    0.00    1.20     -  |__mysqld
Average:        0         -     32238    0.80    0.40    0.00    1.20     -  |__mysqld
Average:        0         -     32239    1.00    0.20    0.00    1.20     -  |__mysqld
Average:        0         -     32240    0.20    0.00    0.00    0.20     -  |__mysqld
Average:        0         -     32241    0.00    0.20    0.00    0.20     -  |__mysqld
Average:        0         -     32242    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32243    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32244    1.20    1.80    0.00    3.00     -  |__mysqld
Average:        0         -     32247    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32248    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32249    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32250    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32251    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32252    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32253    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32254    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32255    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32256    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32257    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32258    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32259    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32260    0.00    0.00    0.00    0.00     -  |__mysqld
Average:        0         -     32272   80.00    9.80    0.00   89.80     -  |__mysqld

結果可以明顯的看到 32272 這個線程的 CPU 使用率異常。

查看 MySQL 進程

在 MySQL 中看看當前連接是否有什么異常。

方法一：

mysql [localhost:5727] {msandbox} ((none)) > show full processlist;
+----+----------+-----------+------+---------+------+---------------------+---------------------------------------------+
| Id | User     | Host      | db   | Command | Time | State               | Info                                        |
+----+----------+-----------+------+---------+------+---------------------+---------------------------------------------+
|  3 | msandbox | localhost | test | Query   |    7 | Creating sort index | select * from t_cpu order by rand() limit 1 |
|  4 | msandbox | localhost | NULL | Query   |    0 | starting            | show full processlist                       |
+----+----------+-----------+------+---------+------+---------------------+---------------------------------------------+
2 rows in set (0.00 sec)

方法二：

mysql [localhost:5727] {msandbox} ((none)) > select * from information_schema.processlist;
+----+----------+-----------+------+---------+------+--------------+----------------------------------------------+
| ID | USER     | HOST      | DB   | COMMAND | TIME | STATE        | INFO                                         |
+----+----------+-----------+------+---------+------+--------------+----------------------------------------------+
|  5 | msandbox | localhost | test | Query   |    2 | Sending data | select * from t_cpu order by rand() limit 1  |
|  6 | msandbox | localhost | NULL | Query   |    0 | executing    | select * from information_schema.processlist |
|  4 | msandbox | localhost | NULL | Sleep   | 1401 |              | NULL                                         |
|  3 | msandbox | localhost | test | Sleep   | 1405 |              | NULL                                         |
+----+----------+-----------+------+---------+------+--------------+----------------------------------------------+
4 rows in set (0.00 sec)

參數說明：

id：標識位，MySQL 進程 ID，使用 kill id，可以殺掉某一個進程
user：當前連接的用戶
host：當前連接的主機
db：連接的數據庫
command：執行的數據庫操作類型
sleep：休眠狀態
Query：查詢狀態
connect：連接狀態
time：已經執行的時間，單位秒
info：已經執行的 SQL
state：SQL 執行的狀態，結果是 SQL 語句整個執行狀態中的一個，其中包含很多狀態，我們整理如下表：

MySQL 5.7 官方參考MySQL 8.0 官方參考

狀態	含義
After create	當線程使用函數創建表（包括內部臨時表）最后階段會出現這個狀態，即使由于某些錯誤未能創建成功，也會標識該狀態
altering table	服務端正在執行 ALTER TABLE
Analyzing	線程正在計算 MyISAM 引擎表鍵值分布（例如 ANALYZE TABLE）
checking permissions	線程正在檢查服務端是否有執行語句所需要的的權限
Checking table	線程正在執行表的檢查工作
cleaning up	線程已經處理了一個命令，并準備釋放內存并重置某些狀態
Closing tables	線程正在將表中修改的數據刷新到磁盤，同時正在關閉已經用完的表。是一個能很快完成的動作，如果持續完不成，需要確認磁盤空間是否用滿或者磁盤使用的負載很高
converting HEAP to ondisk	線程正在將內部臨時表從 MEMORY 表轉換為磁盤表
copy to tmp table	線程正在執行一個 ALTER TABLE 語句。在創建一個新表，然后將老表記錄復制到新表之前將是此狀態
Copying to group table	如果語句具有不同的條件 ORDER BY 和 GROUP BY 標準，則按組對行進行排序并將其復制到臨時表
Copying to tmp table	服務端正在復制到內存中的臨時表
Copying to tmp table on disk	服務端正在復制到磁盤上的臨時表，臨時結果集變的太大（參考 8.4.4 節，MySQL 內部臨時表的使用），線程正在將臨時表從內存更改為基于磁盤的格式來節省內存
Creating index	線程正在執行 MyISAM 表的 ALTER TABLE ... ENABLE KEYS
Creating sort index	線程正在處理 SELECT 使用內部臨時表
creating table	線程正在創建表，包括創建臨時表
Creating tmp table	線程正在內存中或者磁盤上創建臨時表，如果臨時表在內存中創建之后轉換到磁盤，狀態為 Copying to tmp table on disk
committing alter table to storage engine	服務端完成 ALTER TABLE 并提交結果
deleting from main table	服務端正在執行多表刪除的第一部分，僅刪除第一個表，保存列和偏移量用于刪除其他表
deleting from reference tables	服務端正在執行多表刪除的第二部分，在其他表中刪除匹配的行
discard_or_import_tablespace	線程正在執行 ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE 語句
end	結束狀態，結束之前需要清理 ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE
executing	線程正在執行語句
Execution of init_command	線程正在執行變量 init_command system 中的語句
freeing items	線程執行了一條命令，在此期間完成了一些項目的資源釋放，涉及如查詢緩存，這個狀態之后通常為 cleaning up
FULLTEXT initialization	服務端正在準備執行 natural-language 的全文檢索
init	該狀態在 ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE 初始化之前，服務端在該狀態下會刷新二進制日志和 InnoDB 日志，清理查詢緩存
Killed	有發送了一個 KILL 線程的請求，下次終止前會檢查 kill 標志位。MySQL 會在每次主循環中檢查 kill 標志位，有時該線程并不能立馬殺掉會保留短暫時間，如果該線程被其他線程鎖定，那么 kill 會在鎖釋放后生效
logging slow query	線程正在往慢日志中寫入語句
login	連接線程的初始狀態，直到客戶端成功完成驗證
manage keys	服務端開啟或者關閉表的索引
Opening tables	線程正在打開表，這應該是一個很快的過程，除非有些事情組織了打開表。例如 ALTER TABLE or a LOCK TABLE 會在結束前阻止打開表操作。再就是有必要檢查 table_open_cache 參數的值設置是否足夠大。
optimizing	服務端正在對查詢進行初始化優化
preparing	查詢優化狀態
Purging old relay logs	線程正在刪除不需要的中繼日志
query end	處理完查詢，在 freeing items 之前的狀態
Receiving from client	服務端正在讀取客戶端的包，該狀態在 5.7.8 叫 Reading from net
Removing duplicates	查詢正在使用 SELECT DISTINCT，這個過程 MySQL 在早期階段無法完成，因此需要一個額外的階段在把結果返回給客戶端前需要刪除重復行
removing tmp table	線程在執行完 SELECT 后刪除中間臨時表，如果沒有使用臨時表，無該狀態
rename	重命名表
rename result table	線程正在執行 ALTER TABLE 語句，創建一個新表，重命名新表替換老表
Reopen tables	線程給表加鎖，但是發現在獲取表后發現表結構發生了變化。當釋放鎖，關閉表之后嘗試重新打開表
Repair by sorting	使用排序創建索引修復代碼
preparing for alter table	服務端正準備就地執行 ALTER TABLE
Repair done	線程已完成對 MyISAM 表的多線程修復
Repair with keycache	創建索引緩存修復代碼，比 Repair by sorting 慢的多
Rolling back	線程在回滾事務
Saving state	對于 MyISAM 表的修復和分析操作，線程將新表保存到.MYI 文件頭，狀態包含行數，AUTO_INCREMENT 計數器，鍵值分布之類的信息
Searching rows for update	線程正在執行第一階段查詢未更新的數據，如果更新了索引所在行就會執行有該狀態
Sending data	線程正在讀取和處理 SELECT 的行記錄，發送給客戶端，由于在這個狀態下的操作會執行大量的磁盤訪問（讀），因此它通常是查詢生命周期最長的狀態。
Sending to client	服務端正在給客戶端發送包，在 5.7.8 該狀態叫 Writing to net
setup	線程開始執行 ALTER TABLE 操作
Sorting for group	線程正在做排序來滿足 GROUP BY
Sorting for order	線程正在做排序來滿足 ORDER BY
Sorting index	線程正在做索引頁優化來使得 MyISAM 表優化操作期間有更高效的訪問
Sorting result	對于一個 SELECT 語句，類似不使用臨時表的 Creating sort index
starting	語句執行的第一個狀態
statistics	服務端正在計算統計信息對一個查詢創建執行計劃，如果持續很長時間，服務端可能正在執行其他的操作
System lock	線程調用了 mysqllocktables()，并且一直未更新狀態，這個狀態非常常見，可能有很多原因造成。
update	線程正準備執行更新操作
Updating	線程正在檢索要更新的行并且更新行
updating main table	服務端正在執行多表更新的第一部分，僅更新第一個表，保存列和偏移量用于更新其他表
updating reference tables	服務端正在執行多表更新的第二部分，在其他表中更新匹配的行
User lock	線程正在使用 GET_LOCK()請求或等待一個意向鎖，對于 SHOW PROFILE 這個狀態意味著線程請求鎖（但是不等鎖）
User sleep	線程正在調用 SLEEP()
Waiting for commit lock	FLUSH TABLES WITH READ LOCK 正在等待一個提交鎖
Waiting for global read lock	FLUSH TABLES WITH READ LOCK 正在等待全局讀鎖或者設置系統全局變量 read_only
Waiting for tables	線程得到一個表結構變化的通知，需要重新打開表獲取新的表結構，該線程需要等待其他線程關閉該表。這個通知發生在另外的線程執行了 FLUSH TABLES，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE or OPTIMIZE TABLE
Waiting for table flush	線程正在執行 FLUSH TABLES，正在等待其他線程關閉他們的表，或者是線程得到一個表結構變化的通知，需要重新打開表獲取新的表結構，該線程需要等待其他線程關閉該表。這個通知發生在另外的線程執行了 FLUSH TABLES，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE or OPTIMIZE TABLE
Waiting for lock_type lock	服務端正在等待 THR_LOCK 鎖，或者是元數據鎖定子系統的鎖，其中 lock_type 是鎖的類型，等待 THR_LOCK 鎖（Waiting for table level lock），等待元數據鎖（Waiting for event metadata lock、Waiting for global read lock、Waiting for schema metadata lock、Waiting for stored function metadata lock、Waiting for stored procedure metadata lock、Waiting for table metadata lock、Waiting for trigger metadata lock）
Waiting on cond	線程正在等待條件變為 true 的狀態，沒有具體可用的狀態信息
Writing to net	服務端正在網絡中寫包，在 5.7.8 中該狀態叫 Sending to client

通過結果我們可以大致看看如下的情況：

是否有大量的未執行查詢語句
是否有正在執行的 DML 語句
是否有執行了很長時間的 DDL 語句
是否有鎖等情況

我們模擬的這個環境，show 的結果只有兩條，結果非常明顯，但是真正的生產環境不止這兩條結果，通過這個查詢結果，可以大致分析一下，并不能準確定位，準確的定位問題，需要進行后續的操作。

根據問題線程 id 定位 MySQL 中的 SQL：

mysql [localhost:5727] {msandbox} ((none)) > select a.user,a.host,a.db,b.thread_os_id,b.thread_id,a.id processlist_id,a.command,a.time,a.state,a.info from information_schema.processlist a,performance_schema.threads b where a.id = b.processlist_id and b.thread_os_id=32272;
+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+
| user     | host      | db   | thread_os_id | thread_id | processlist_id | command | time | state        | info                                        |
+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+
| msandbox | localhost | test |        32272 |        32 |              7 | Query   |    2 | Sending data | select * from t_cpu order by rand() limit 1 |
+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

thread_os_id=32272，代表的是操作系統級別的線程 id
thread_id=32，代表的是 MySQL 內部級別的線程 id
processlist_id=7，代表的是會話發起者的進程 id，可 kill 的 id

根據問題線程 id 查看其他監控指標：

mysql [localhost:5727] {msandbox} (performance_schema) > select * from performance_schema.events_statements_current where thread_id in (select thread_id from performance_schema.threads where thread_os_id = 32272)G;
*************************** 1. row ***************************
              THREAD_ID: 32
               EVENT_ID: 11
           END_EVENT_ID: 11
             EVENT_NAME: statement/sql/select
                 SOURCE: 
            TIMER_START: 93213362472684000
              TIMER_END: 93221551162460000
             TIMER_WAIT: 8188689776000
              LOCK_TIME: 993000000
               SQL_TEXT: select * from t_cpu order by rand() limit 1
                 DIGEST: 83dacab9df764f11291d0001e420012e
            DIGEST_TEXT: SELECT * FROM `t_cpu` ORDER BY `rand` ( ) LIMIT ? 
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 20971521
CREATED_TMP_DISK_TABLES: 1
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 1
              SORT_SCAN: 1
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

通過這個結果我們可以查看具體的 SQL，看到有使用臨時表、使用了排序等信息。

CREATED_TMP_DISK_TABLES: 1  
CREATED_TMP_TABLES: 1  
SORT_ROWS: 1  
SORT_SCAN: 1

其他排查手段之慢查詢日志

查詢慢日志情況：

mysql [localhost:5727] {msandbox} ((none)) > show variables like 'slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name       | Value                                                |
+---------------------+------------------------------------------------------+
| slow_query_log      | ON                                                   |
| slow_query_log_file | /dbdata/sandboxes/msb_5_7_27/data/localhost-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set (0.01 sec)

在慢日志中查看慢 SQL：

cat /dbdata/sandboxes/msb_5_7_27/data/localhost-slow.log

# Time: 2021-08-17T09:11:06.768816Z
# User@Host: msandbox[msandbox] @ localhost []  Id:     5
# Query_time: 7.984593  Lock_time: 0.000184 Rows_sent: 1  Rows_examined: 20971521
SET timestamp=1629191466;
select * from t_cpu order by rand() limit 1;

可以很清楚的查到慢 SQL：

select * from t_cpu order by rand() limit 1

執行了有 7.984593 秒，返回結果為 1 行。

其他排查手段之排查日志

開啟排查日志功能：

更改排查日志為 TABLE 方式，默認為 FILE 方式
mysql [localhost:5727] {msandbox} ((none)) > set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5727] {msandbox} ((none)) > set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)

執行慢查詢：

select * from t_cpu order by rand() limit 1

查詢排查日志內容：

mysql [localhost:5727] {msandbox} ((none)) > SELECT * from mysql.general_log ORDER BY event_time DESC;
+----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------------------------+
| event_time                 | user_host                         | thread_id | server_id | command_type | argument                                                 |
+----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------------------------+
| 2021-08-17 17:19:19.863849 | msandbox[msandbox] @ localhost [] |         2 |         0 | Query        | SELECT * from mysql.general_log ORDER BY event_time DESC |
| 2021-08-17 17:18:45.017615 | msandbox[msandbox] @ localhost [] |         5 |         0 | Query        | select * from t_cpu order by rand() limit 1              |
+----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------------------------+
2 rows in set (0.00 sec)

結果顯示了剛才的慢 SQL，還有我們查詢排查日志結果的 SQL，其實慢日志會記錄所有的 SQL，我們只能通過這個日志內容查看一些不太正常的 SQL，比如大量相同的 SQL；定期出現的 SQL；分析一個特定的事務等，排查日志模式非常影響 MySQL 性能，慎用。

關閉排查日志模式：

mysql [localhost:5727] {msandbox} ((none)) > set global general_log=OFF;
Query OK, 0 rows affected (0.05 sec)

mysql [localhost:5727] {msandbox} ((none)) > truncate table mysql.general_log;
Query OK, 0 rows affected (0.05 sec)

到目前為止，介紹了幾種常用的 MySQL 占用 CPU 過高的定位方法，你是否掌握了。找到問題了，接下來，我們就需要看看如何優化這個問題了。

優化一下也不難

前面介紹了幾種常用的排查 MySQL 占用 CPU 過高問題排查的方法，那么當我們遇到問題了，如何解決，如何優化呢？

首先，最暴力的方法，如果我們確定這個 SQL 可以被干掉，我們直接 kill id 即可。id 為 process 的 id。

其次，具體問題具體分析，如何排查問題是如何發生的，根據不同的問題作出相應的解決方法。

1. 優化 SQL，從邏輯上優化 SQL，降低 SQL 復雜度，降低 MySQL 執行成本。

2. 對 where、join、max()、min()、order by、group by 等子句用到的字段，創建相應的索引。

3. 二級索引的正確使用。

4. 參數優化：

增加 tmp_table_size 大小
增加 max_heap_table_size 大小
調整 key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size 參數大小

其他常用優化方法

1. 檢查 MySQL 連接數當前使用是否超過限制

如果超出限制，而且之前的連接沒有得到釋放，那新的連接肯定會連接不到，造成連接延遲，影響效率。

2. MySQL 的 timeout 參數設置問題

這里介紹兩個參數：

wait_timeout：MySQL 在關閉一個非交互的連接之前所要等待的秒數，其取值范圍在 windows 系統中為 1-2147483，linux 系統中為 1-31536000，默認值 28800。
interactive_time：MySQL 在關閉一個交互的連接之前所要等待的秒數（如 mysql gui tool 中的連接），其取值范圍隨 wait_timeout 變動，默認值 28800。

交互連接即在 mysql_real_connect() 函數中使用了 CLIENT_INTERACTIVE 選項。簡單說，就是通過 MySQL 客戶端連接數據庫是交互式連接，通過 jdbc 連接數據庫是非交互式連接。在 MySQL 默認設置下，當一個連接的空閑時間超過 8 小時后，當業務出現了高峰期，肯定會造成有太多的 TCP 連接沒關閉，數據庫連接數肯定會不夠用。從而會產生 CPU 占用過高，服務器告警等問題。因 EPG 的一個訪問一次對數據庫操作量不大，查詢完數據就完成 ok 了，wait_timeout 設置在 120s 內就可以了。

在 MySQL 的官網了解到修改以上兩個參數必須修改 interactive_timeout。如果修改 interactive_timeout，則 wait_timeout 也發生變化，如果只修改 wait_timeout，不生效。

3. 老生常談，規范的 SQL 也是非常必要的

好了，至此我們基本學習完本節內容了，內容比較多，當然也有不足之處，歡迎朋友們指正補充。