MySQL 慢查詢

基礎命令

# 檢視慢查詢是否開啟
show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/79168aa559e4-slow.log |
+---------------------+--------------------------------------+
# slow_query_log 開啟狀態
# slow_query_log_file 慢查詢日誌檔案位置

# 1,開啟慢查詢
set global slow_query_log = on;
# 2,設定慢查詢日誌位置, 有點需要注意如果當前位置無許可權的,會設定失敗
set global slow_query_log_file = /tmp/slow.log;
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| slow_query_log      | ON            |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+
# 如果設定失敗,就用此命令檢視目錄位置
show variables like '%dir%';
# 3,設定慢日誌的限定時間 3s
set global long_query_time = 3;
# 4, 檢視時間設定, 需要注意的是,更改之後,需要切換一個客戶端才能看到設定的3s
show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 3.000000 |
+-----------------+-----------+

測試慢查詢

上面我開啟的是3s所以我寫一條4s的sqlselect sleep(4),這樣這條sql就會進到慢查詢日誌中,日誌檔案位置就是我們上面設定的路徑.

root@79168aa559e4:/tmp# cat slow.log 
/usr/sbin/mysqld, Version: 8.0.22 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.22 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2021-01-26T15:40:23.700187Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 4.000201  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1611675619;
select sleep(4);

慢查詢檢測工具

1. mysqldumpslow

   // todo

2. show profile

   1. 簡介

      Show Profile是mysql提供的可以用來分析當前會話中sql語句執行的資源消耗情況的工具，可用於sql調優的測量。預設情況下處於關閉狀態，並儲存最近15次的執行結果。

   2. 基礎操作

      # 檢視是否開啟;
      show variables like 'profiling';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | profiling     | OFF   |
      +---------------+-------+
      # 開啟profile
      set profiling = on;
      

   3. 測試

      select sleep(4);
      
      # 檢視記錄
      show profiles;
      +----------+------------+---------------------------------+
      | Query_ID | Duration   | Query                           |
      +----------+------------+---------------------------------+
      |        1 | 0.00122625 | show variables like 'profiling' |
      |        2 | 4.00031050 | select sleep(4)                 |
      |        3 | 0.00060675 | show databases                  |
      +----------+------------+---------------------------------+
      # 檢視單條
      show profile cpu,block io for query Query_ID;
      # 例子
      show profile cpu,block io for query 3;
      +----------------------------+----------+----------+------------+--------------+---------------+
      | Status                     | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
      +----------------------------+----------+----------+------------+--------------+---------------+
      | starting                   | 0.000067 | 0.000067 |   0.000000 |            0 |             0 |
      | checking permissions       | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
      | Opening tables             | 0.000179 | 0.000179 |   0.000000 |            0 |             0 |
      | init                       | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
      | System lock                | 0.000012 | 0.000011 |   0.000000 |            0 |             0 |
      | optimizing                 | 0.000015 | 0.000016 |   0.000000 |            0 |             0 |
      | statistics                 | 0.000043 | 0.000043 |   0.000000 |            0 |             0 |
      | preparing                  | 0.000038 | 0.000038 |   0.000000 |            0 |             0 |
      | Creating tmp table         | 0.000084 | 0.000083 |   0.000000 |            0 |             0 |
      | executing                  | 0.000085 | 0.000086 |   0.000000 |            0 |             0 |
      | end                        | 0.000006 | 0.000005 |   0.000000 |            0 |             0 |
      | query end                  | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
      | waiting for handler commit | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
      | removing tmp table         | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
      | waiting for handler commit | 0.000004 | 0.000005 |   0.000000 |            0 |             0 |
      | closing tables             | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
      | freeing items              | 0.000020 | 0.000020 |   0.000000 |            0 |             0 |
      | cleaning up                | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
      +----------------------------+----------+----------+------------+--------------+---------------+
      

   4. show profile 常用引數

      1. All – 顯示所有的開銷
      2. Block Io – 顯示塊IO
      3. CONTEXT SWITCHES：上下文切換開銷。
      4. CPU：顯示CPU開銷資訊。
      5. IPC：顯示傳送和接收開銷資訊。
      6. MEMORY：顯示記憶體開銷資訊。
      7. PAGE FAULTS：顯示頁面錯誤開銷資訊。
      8. SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷資訊。
      9. SWAPS：顯示交換次數開銷資訊。

   5. 需要重點關注部分

      1. converting HEAP to MyISAM：查詢結果太大，記憶體不夠，資料往磁碟上搬了。
      2. Creating tmp table：建立臨時表。先拷貝資料到臨時表，用完後再刪除臨時表。
      3. Copying to tmp table on disk：把記憶體中臨時表複製到磁碟上，危險！！！
      4. locked – 鎖

本作品採用《CC 協議》，轉載必須註明作者和本文連結