《MySQL 進階篇》十四：效能分析工具

ACatSmiling發表於2024-09-22
MySql

Author: ACatSmiling

Since: 2024-09-21

在資料庫調優中，目標就是 響應時間更快，吞吐量更大。利用宏觀的監控工具和微觀的日誌分析可以幫助快速找到調優的思路和方式。

資料庫伺服器的最佳化步驟

當遇到資料庫調優問題的時候，該如何思考呢？這裡把思考的流程整理成下面這張圖。

整個流程劃分成了 觀察（Show status）和行動（Action）兩個部分。字母 S 的部分代表觀察（會使用相應的分析工具），字母 A 代表的部分是行動（對應分析可以採取的行動）：

image-20230711090137783

資料庫調優時，可以透過觀察瞭解資料庫整體的執行狀態，透過效能分析工具，可以幫助瞭解執行慢的 SQL 有哪些，檢視具體的 SQL 執行計劃，甚至是 SQL 執行中的每一步的成本代價，這樣才能定位問題所在，找到了問題，再採取相應的行動。

  • 首先，在 S1 部分，我們需要觀察伺服器的狀態是否存在週期性的波動。如果存在週期性波動，有可能是週期性節點的原因，比如雙十一、促銷活動等。這樣的話，可以透過 A1 這一步驟解決，也就是加快取，或者更改快取失效策略。
  • 如果快取策略沒有解決，或者不是週期性波動的原因，就需要進一步分析查詢延遲和卡頓的原因。接下來進入 S2 這一步，需要開啟慢查詢，慢查詢可以幫助定位執行慢的 SQL 語句。可以透過設定long_query_time引數定義慢查詢的閾值，如果 SQL 執行時間超過了 long_query_time，則會認為是慢查詢。當收集上來這些慢查詢之後，就可以透過分析工具對慢查詢日誌進行分析。
  • 在 S3 這一步驟中，我們知道了執行慢的 SQL，可以針對性的用EXPLAIN檢視對應 SQL 語句的執行計劃，或者用SHOW PROFILE檢視 SQL 中每一個步驟的時間成本。然後，就可以瞭解 SQL 查詢慢是因為執行時間長，還是等待時間長。
  • 如果是 SQL 等待時間長，進入 A2 步驟。在這一步驟中，可以調優伺服器的引數，比如適當增加資料庫緩衝池等。如果是 SQL 執行時間長，就進入 A3 步驟，這一步中需要考慮是索引設計的問題，還是查詢關聯的資料表過多，亦或者是因為資料表的欄位設計問題導致了這一問題，然後在對應的維度上進行相應的調整。
  • 如果 A2 和 A3 都不能解決問題，我們需要考慮資料庫自身的 SQL 查詢效能是否已經達到了瓶頸，如果確認沒有達到效能瓶頸，就需要重新檢查，重複以上的步驟。如果已經達到了效能瓶頸，進入 A4 階段，需要考慮增加伺服器，採用讀寫分離的架構，或者考慮對資料庫進行分庫分表，比如垂直分庫、垂直分表和水平分表等。

以上就是資料庫調優的流程思路，如果發現執行 SQL 時存在不規則延遲或卡頓的時候，就可以採用分析工具幫忙定位有問題的 SQL，這三種分析工具可以理解是 SQL 調優的三個步驟：慢查詢、EXPLAIN和SHOW PROFILING。

小結：

image-20230712090052115

  • 可以看到資料庫調優的步驟中越往金字塔尖走，其成本越高，效果越差，因此在資料庫調優的過程中，要重點把握金字塔底部的 SQL 及索引調優，資料庫表結構調優，系統配置引數調優等軟體層面的調優。

檢視系統效能引數

可以使用SHOW STATUS語句查詢一些資料庫伺服器的效能引數和使用頻率，語法：

SHOW [GLOBAL][SESSION] STATUES LIKE '[引數]'

一些常用的效能引數如下：

  • Connections：連線 MySQL 伺服器的次數。
  • Uptime：MySQL 伺服器的上線時間。
  • Slow_queries：慢查詢的次數。
  • Innodb_rows_read：SELECT 查詢返回的行數。
  • Innodb_rows_inserted：執行 INSERT 操作插入的行數。
  • Innodb_rows_updated：執行 UPDATE 操作更新的行數。
  • Innodb_rows_deleted：執行 DELETE 操作刪除的行數。
  • Com_select：查詢操作的次數。
  • Com_insert：插入操作的次數。對於批次插入的 INSERT 操作，只累加一次。
  • Com_update：更新操作的次數。
  • Com_delete：刪除操作的次數。

統計 SQL 的查詢成本：last_query_cost

一條 SQL 查詢語句在執行前需要確定查詢執行計劃，如果存在多種執行計劃的話，MySQL 會計算每個執行計劃所需要的成本，從中選擇成本最小的一個作為最終執行的執行計劃。

如果想要檢視某條 SQL 語句的查詢成本，可以在執行完這條 SQL 語句之後，透過檢視當前會話中的last_query_cost變數值來得到當前查詢的成本。last_query_cost 也是評價一個查詢的執行效率的一個常用指標，這個查詢成本對應的是SQL 語句所需要讀取的頁的數量。

以 student_info 表為例：

  1. 查詢 id = 900001 的記錄，可以直接在聚簇索引上進行查詢。檢視查詢最佳化器的成本，實際上只需要檢索一個頁即可，Value表示 I/O 載入的資料頁的頁數。

    mysql> SELECT * FROM student_info WHERE id = 900001;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 |     103954 | BfYzAg |     10064 |    10138 | 2023-07-02 18:54:38 |
+--------+------------+--------+-----------+----------+---------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

  2. 擴大下查詢範圍，student_id > 199900 的學生記錄，大概需要進行 102120 個頁的查詢。

    mysql> SELECT * FROM student_info WHERE student_id > 199900;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
|    607 |     199908 | PYxrsM |     10000 |    10080 | 2023-07-02 18:53:47 |
|   1788 |     199993 | XLQSTN |     10092 |    10070 | 2023-07-02 18:53:47 |
|   3042 |     199909 | OTcESy |     10023 |    10148 | 2023-07-02 18:53:47 |
| 999891 |     199922 | mlvtHf |     10087 |    10184 | 2023-07-02 18:54:44 |
+--------+------------+--------+-----------+----------+---------------------+
514 rows in set (0.27 sec)

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+---------------+
| Variable_name   | Value         |
+-----------------+---------------+
| Last_query_cost | 102120.464739 |
+-----------------+---------------+
1 row in set (0.00 sec)

  3. 再次擴大查詢範圍，student_id > 199000 的學生記錄，大概需要進行 100512 個頁的查詢。

    mysql> SELECT * FROM student_info WHERE student_id > 199000;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
|     18 |     199656 | CNfphR |     10058 |    10029 | 2023-07-02 18:53:47 |
|     30 |     199615 | gEEhXr |     10015 |    10058 | 2023-07-02 18:53:47 |
|     57 |     199193 | uaUups |     10021 |    10105 | 2023-07-02 18:53:47 |
| 999891 |     199922 | mlvtHf |     10087 |    10184 | 2023-07-02 18:54:44 |
+--------+------------+--------+-----------+----------+---------------------+
5023 rows in set (0.19 sec)

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+---------------+
| Variable_name   | Value         |
+-----------------+---------------+
| Last_query_cost | 100512.649000 |
+-----------------+---------------+
1 row in set (0.00 sec)

SQL查詢是一個動態的過程，從頁載入的角度，我們可以得到以下兩點結論：

  • 位置決定效率：如果頁就在資料庫緩衝池中，那麼效率是最高的，否則還需要從記憶體或者磁碟中進行讀取，當然針對單個頁的讀取來說，如果頁存在於記憶體中，會比在磁碟中讀取效率高很多。即資料庫緩衝池 > 記憶體 > 磁碟。
  • 批次決定效率：如果我們從磁碟中單一頁進行隨機讀，那麼效率是很低的（差不多 10 ms），而採用順序讀取的方式，批次對頁進行讀取，平均一頁的讀取效率就會提升很多，甚至要快於單個頁面在記憶體中的隨機讀取。即順序讀取 > 隨機讀取。

所以說，遇到 I/O 並不用擔心，方法找對了，效率還是很高的。我們首先要考慮資料存放的位置，如果是經常使用的資料就要儘量放到緩衝池中，其次我們可以充分利用磁碟的吞吐能力，一次性批次讀取資料，這樣單個頁的讀取效率也就得到了提升。

注：緩衝池和查詢快取並不是一個東西。

定位執行慢的 SQL：慢查詢日誌

MySQL 的慢查詢日誌，用來記錄在 MySQL 中響應時間超過閾值的語句，具體指執行時間超過long_query_time值的 SQL，會被記錄到慢查詢日誌中。long_query_time 的預設值是 10，意思是執行 10 秒以上（不含 10 秒）的語句，認為是超出了最大的忍耐時間值。

它的主要作用是，幫助我們發現那些執行時間特別長的 SQL 查詢，並且有針對性的進行最佳化，從而提高系統的整體效率。當我們的資料庫伺服器發生阻塞、執行變慢的時候，檢查一下慢查詢日誌，找到那些慢查詢，對解決問題很有幫助。

預設情況下，MySQL 資料庫沒有開啟慢查詢日誌，需要手動來設定這個引數。如果不是調優需要的話，一般不建議啟動該引數。因為開啟慢查詢日誌，會或多或少來帶一定的效能影響。

慢查詢日誌，支援將日誌記錄寫入檔案。

開啟慢查詢日誌

開啟 slow_query_log

檢視慢查詢日誌是否開啟，以及日誌的位置：

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/de5e82a9b92d-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

修改慢查詢日誌狀態為開啟，注意這裡要加 global，因為它是全域性系統變數，否則會報錯：

mysql> SET GLOBAL slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)

再次檢視：

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/de5e82a9b92d-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

修改 long_query_time 閾值

檢視慢查詢的時間閾值設定：

mysql> SHOW VARIABLES LIKE '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

按需重新設定，例如設定為 1 秒：

# 測試發現設定 global 的方式對當前 session 的 long_query_time 失效，只對新連線的客戶端有效，所以可以一併執行下列語句
mysql> SET GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

補充：配置檔案中設定引數

如下的方式相較於前面的命令列方式，可以看作是永久設定。

修改my.cnf檔案，在 [mysqld] 下增加或修改引數slow_query_log，slow_query_log_file和long_query_time後，然後重啟 MySQL 伺服器。

[mysqld]
slow_query_log=ON # 開啟慢查詢日誌的開關
slow_query_log_file=/var/lib/mysql/slow.log # 慢查詢日誌的目錄和檔名資訊
long_query_time=3 # 設定慢查詢的閾值為3秒，超出此設定值的SQL即被記錄到慢查詢日誌
log_output=FILE

如果不指定儲存路徑，慢查詢日誌將預設儲存到 MySQL 資料庫的資料資料夾下，如果不指定檔名，預設檔名為 hostname-slow.log。

案例演示

第一步，建表：

mysql> CREATE TABLE `student` (
    ->     `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->     `stuno` INT NOT NULL ,
    ->     `name` VARCHAR(20) DEFAULT NULL,
    ->     `age` INT(3) DEFAULT NULL,
    ->     `classId` INT(11) DEFAULT NULL,
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 4 warnings (0.03 sec)

第二步，開啟允許建立函式：

mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL log_bin_trust_function_creators='ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

第三步，建立函式：

# 隨機產生字串函式 rand_string，同前一章節

# 隨機數函式 rand_num，同前一章節

第四步，建立儲存過程：

mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_stu1(  START INT , max_num INT )
    -> BEGIN 
    -> DECLARE i INT DEFAULT 0; 
    -> SET autocommit = 0; # 設定手動提交事務
    -> REPEAT # 迴圈
    -> SET i = i + 1; # 賦值
    -> INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
    -> ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000)); 
    -> UNTIL i = max_num 
    -> END REPEAT; 
    -> COMMIT; # 提交事務
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

第五步，呼叫儲存過程：

mysql> CALL insert_stu1(100001,4000000);
Query OK, 0 rows affected (4 min 8.07 sec)

第六步，驗證是否成功：

mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
|  4000000 |
+----------+
1 row in set (0.21 sec)

慢查詢演示

執行下面的查詢操作，進行慢查詢語句的測試：

mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
| 3355654 | 3455655 | WOcWyE |   60 |     968 |
+---------+---------+--------+------+---------+
1 row in set (1.21 sec)

mysql> SELECT * FROM student WHERE name = 'ZfCwDz';
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
|  427220 |  527221 | zfcWDZ |   33 |     311 |
|  781361 |  881362 | ZFcwdZ |   80 |     898 |
|  812704 |  912705 | ZFcwdZ |   81 |     965 |
| 2138264 | 2238265 | zFCWdz |   31 |     727 |
| 2602748 | 2702749 | zFCWdz |   35 |     938 |
| 2763745 | 2863746 | zfcWDZ |   31 |     239 |
| 2978248 | 3078249 | zFCWdz |   30 |     708 |
+---------+---------+--------+------+---------+
7 rows in set (1.26 sec)

因為此時慢日誌的時間閾值為 1 秒，上面的兩條 SQL，都屬於慢日誌。檢視下慢查詢的記錄：

mysql> SHOW STATUS LIKE 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.00 sec)

在 MySQL 中，除了上述slow_queries變數，控制慢查詢日誌的還有另外一個變數min_examined_row_limit。這個變數的意思是，查詢掃描過的最少記錄數。這個變數和查詢執行時間，共同組成了判別一個查詢是否慢查詢的條件。如果查詢掃描過的記錄數大於等於這個變數的值，並且查詢執行時間超過 long_query_time 的值，那麼這個查詢就被記錄到慢查詢日誌中。反之，則不被記錄到慢查詢日誌中。另外，min_examined_row_limit 預設是 0，我們也一般不會去修改它。

mysql> SHOW VARIABLES LIKE 'min%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

當這個值為預設值 0 時，與 long_query_time=10 合在一起，表示只要查詢的執行時間超過 10 秒鐘，哪怕一個記錄也沒有掃描過，都要被記錄到慢查詢日誌中。你也可以根據需要，透過修改 "my.ini" 檔案，來修改查詢時長，或者透過 SET 指令，用 SQL 語句修改 min_examined_row_limit 的值。

慢查詢日誌分析工具

在生產環境中，如果要手工分析日誌，查詢、分析 SQL，顯然是個體力活，MySQL 提供了日誌分析工具mysqldumpslow。

說明：

  1. 該工具並不是 MySQL 內建的，不要在 MySQL 下執行，可以直接在根目錄或者其他位置執行。
  2. 該工具只有 Linux 下才是開箱可用的，實際上生產中 MySQL 資料庫一般也是部署在 Linux 環境中的。如果是 Windows 環境，可以參考 https://www.cnblogs.com/-mrl/p/15770811.html。

透過 mysqldumpslow 可以檢視慢查詢日誌幫助：

# 建立軟連線
xisun@xisun-develop:~/mysql/mysql-8.0.33-linux-glibc2.17-x86_64-minimal/bin$ sudo ln -sf /home/xisun/mysql/mysql-8.0.33-linux-glibc2.17-x86_64-minimal/bin/mysqldumpslow /usr/bin/

# 分析慢日誌
xisun@xisun-develop:~/apps$ sudo mysqldumpslow -s t -t 5 mysql/data/de5e82a9b92d-slow.log 

Reading mysql slow query log from mysql/data/de5e82a9b92d-slow.log
Count: 1  Time=248.07s (248s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[127.0.0.1]
  CALL insert_stu1(N,N)

Count: 1  Time=1.26s (1s)  Lock=0.00s (0s)  Rows=7.0 (7), root[root]@[127.0.0.1]
  SELECT * FROM student WHERE name = 'S'

Count: 1  Time=1.21s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[127.0.0.1]
  SELECT * FROM student WHERE stuno = N

Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.

# 新增 -a 引數，顯示 SQL 中的真實資料，不將具體的數值被 N 代替，字串被 S 代替
xisun@xisun-develop:~/apps$ sudo mysqldumpslow -s t -t 5 -a mysql/data/de5e82a9b92d-slow.log 

Reading mysql slow query log from mysql/data/de5e82a9b92d-slow.log
Count: 1  Time=248.07s (248s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[127.0.0.1]
  CALL insert_stu1(100001,4000000)

Count: 1  Time=1.26s (1s)  Lock=0.00s (0s)  Rows=7.0 (7), root[root]@[127.0.0.1]
  SELECT * FROM student WHERE name = 'ZfCwDz'

Count: 1  Time=1.21s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[127.0.0.1]
  SELECT * FROM student WHERE stuno = 3455655

Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.

Docker 容器中可能沒有 mysqldumpslow 命令，此時，可以下載 MySQL 原始碼，上傳到宿主機。MySQL 慢日誌路徑為 /var/lib/mysql，此路徑在啟動 Docker 容器時，已經被掛載到宿主機，可以在宿主機上，使用下載的 MySQL 原始碼中的 mysqldumpslow 命令分析慢日誌。

檢視 mysqldumpslow 幫助：

$ mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

--verbose    verbose
--debug      debug
--help       write this text to standard output

-v           verbose
-d           debug
-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
    al: average lock time
    ar: average rows sent
    at: average query time
     c: count
     l: lock time
     r: rows sent
     t: query time  
-r           reverse the sort order (largest last instead of first)
-t NUM       just show the top n queries
-a           don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   grep: only consider stmts that include this string
-h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
   default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           don't subtract lock time from total time

mysqldumpslow 命令的具體引數如下：

  • -a：不將數字抽象成 N，字串抽象成 S。

  • -s：是表示按照何種方式排序：

    • c：訪問次數
    • l：鎖定時間
    • r：返回記錄
    • t：查詢時間
    • al：平均鎖定時間
    • ar：平均返回記錄數
    • at：平均查詢時間 （預設方式）
    • ac：平均查詢次數

  • -t：即為返回前面多少條的資料。

  • -g：後邊搭配一個正則匹配模式，大小寫不敏感的；

mysqldumpslow 常用查詢：

# 得到返回記錄集最多的 10 個 SQL
$ mysqldumpslow -s r -t 10 /var/lib/mysql/xisun-slow.log

#  得到訪問次數最多的 10 個 SQL
$ mysqldumpslow -s c -t 10 /var/lib/mysql/xisun-slow.log

# 得到按照時間排序的前 10 條裡面含有左連線的查詢語句
$ mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xisun-slow.log

# 另外建議在使用這些命令時結合 | 和 more 使用，否則有可能出現爆屏情況
$ mysqldumpslow -s r -t 10 /var/lib/mysql/xisun-slow.log | more

關閉慢查詢日誌

臨時關閉

mysql> SET GLOBAL slow_query_log='OFF';

永久關閉

修改 my.cnf 或 my.ini 檔案，把 mysqld 組下的 slow_query_log 值設定為 OFF，修改儲存後，再重啟 MySQL 服務，即可生效。

刪除慢查詢日誌

慢查詢日誌都是使用mysqladmin -uroot -p flush-logs slow 命令來刪除重建的。使用時一定要注意，一旦執行了這個命令，慢查詢日誌都只存在於新的日誌檔案中，如果需要舊的查詢日誌，就必須事先備份。或者也可以直接手動刪除慢查詢日誌。

檢視 SQL 執行成本：SHOW PROFILE

// TODO

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md

相關文章