MySQL如何檢視正在執行的SQL詳解

2023-08-24 18:03:50

前言

在安裝MySQL的時候會預設初始化幾個MySQL執行所需的資料庫：mysql, sys, information_schema, performance_schema.這幾個庫儲存了MySQL在執行過程中的設定資訊，執行資訊，引數設定，資料庫資訊，表資訊等等。今天這個要檢視正在執行的SQL主要用到的是information_schema和performance_schema這兩個庫。

processlist

processlist表位於information_schema庫中，主要是儲存的MySQL執行緒的一些基本資訊。我們使用

desc information_schema.processlist來檢視表結構：

ID: 執行緒的id
USER: 執行緒屬於哪一個使用者
HOST：使用者端的host資訊：hostname+埠
DB：執行緒在哪一個資料庫下
COMMAND：執行緒使用哪一種命令在執行，空閒的執行緒狀態為sleep
TIME:執行緒已經執行的時間，秒為單位
STATE：執行緒正在做什麼：當前的狀態，行為，或者事件
INFO：執行緒正在執行的語句，但是這個並不是很準確，所以需要使用其他的方式來完成我們的目標。

使用show processlist 或者 select * from information_schema.processlist檢視processlist表

threads

threads 位於performance_schema庫中，每一行記錄的是一條伺服器執行緒。當performance_schema初始化的時候，它會根據當時存在的執行緒填充執行緒表，之後每當伺服器建立執行緒時，都會新增一條新資料。當執行緒結束執行緒表中也會刪除這條資料。使用 desc performance_schema.threads 來檢視表結構：

THREAD_ID：執行緒唯一id
NAME:與伺服器中的執行緒監測程式碼相關聯的名稱
TYPE：執行緒型別。分為前臺和後臺。使用者連執行緒是前臺執行緒，與內部伺服器活動相關的執行緒是後端執行緒。例如InnoDB內部執行緒。
PROCESSLIST_ID：上邊的processlist的id
PROCESSLIST_USER：上邊的processlist的user
PROCESSLIST_HOST:上邊的processlist的host
PROCESSLIST_DB:上邊的processlist的DB
PROCESSLIST_COMMAND:上邊的processlist的COMMAND
PROCESSLIST_TIME:上邊的processlist的TIME
PROCESSLIST_STATE:上邊的processlist的STATE
PROCESSLIST_INFO:上邊的processlist的INFO
PARENT_THREAD_ID:衍生執行緒的id值
ROLE：未使用
INSTRUMENTED：執行緒執行的事件是否插裝，（YES or ON)
HISTORY:是否為執行緒記錄歷史事件
CONNECTION_TYPE：用於建立連線的協定，或者NULL用於後臺執行緒。
THREAD_OS_ID：MySQL執行緒對應於作業系統的執行緒id

events_statements_current

events_statements_current 位於performance_schema庫中,它儲存的是當前的語句事件，表為每個執行緒儲存一行，顯示賢臣哥最近監視的語句事件的當前狀態。使用desc performance_schema.events_statements_current檢視表結構：

THREAD_ID：與事件關聯的執行緒id，與上邊threads的id對應
EVENT_ID:事件啟動時的執行緒當前事件號

THREAD_ID和EVENT_ID一起標誌唯一一行，沒有兩行具有相同的鍵值對

END_EVENT_ID：在事件開始時設定null，並在事件結束時更新為執行緒當前事件號
EVENT_NAME：事件的名稱
SOURCE：包含生成事件的監測程式碼的原始檔名稱和監測發生所在檔案的行號
TIMER_START，TIMER_END，TIMER_WAIT：事件的時間資訊，開始時間，結束時間，事件的執行時間，單位是皮秒（萬分之一秒）。
LOCK_TIME：等待表鎖花費的時間。微妙為單位
SQL_TEXT：SQL語句的文字，對於沒有關聯SQL語句的命令為null
DIGEST:MD5的32個字元
DIGEST_TEXT：規範化語句摘要文字。
CURRENT_SCHEMA：語句的預設資料庫
OBJECT_TYPE，OBJECT_SCHEMA，OBJECT_NAME ：對於巢狀語句這些列包含有關父語句的資訊
OBJECT_INSTANCE_BEGIN：語句在記憶體的物件地址
MYSQL_ERRNO：語句錯誤號
RETURNED_SQLSTATE：sql狀態返回
MESSAGE_TEXT：錯誤資訊
ERRORS：該語句是否發生錯誤。
WARNINGS：警告次數
ROWS_AFFECTED：受語句影響的行數
ROWS_SENT：語句返回的行數
ROWS_EXAMINED：伺服器層檢查的行數
- CREATED_TMP_DISK_TABLES
- CREATED_TMP_TABLES
- SELECT_FULL_JOIN
- SELECT_FULL_RANGE_JOIN
- SELECT_RANGE
- SELECT_RANGE_CHECK
- SELECT_SCAN
- SORT_MERGE_PASSES
- SORT_RANGE
- SORT_ROWS
- SORT_SCAN
- NO_INDEX_USED
- NO_GOOD_INDEX_USED
- NESTING_EVENT_ID
- NESTING_EVENT_TYPE
- NESTING_EVENT_LEVEL

如何檢視正在執行的SQL

1、processlist表記錄的是MySQL正在執行的執行緒資訊，而每一個執行緒在threads表中都有用執行緒的一個唯一id >>> thread_id。events_statements_current表中記錄著唯一執行緒id和該執行緒對應的SQL語句sql_text.

2、所以我們可以先在processlist拿到processlist對應的id

3、通過threads表的欄位分析，我們之後一個processlist_id和thread_id一一對應，所以之後在threads表中通過processlist_id拿到thread_id

4、最後一步就是關鍵，我們通過thread_id在events_statements_current表中拿到sql_text,也就是我們需要拿到的sql語句。

拿到正在執行的processlist_id

select id from information_schema.processlist

拿到與processlist_id對應的thread_id

select thread_id from performance_schema.threads where processlist_id in (上一步拿到的processlist_id列表)

拿到正在執行的sql語句

select thread_id, sql_text from performance_schema.events_statements_current where thread_id in (上一步拿到的thread_id列表)

完整SQL

SELECT a.*, c.thread_id, c.sql_text from information_schema.processlist a
LEFT JOIN performance_schema.threads b on a.id = b.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current c on c.THREAD_ID = b.THREAD_ID