**前言：**

在資料庫運維過程中，我們時常會關注資料庫的連結情況，比如總共有多少連結、有多少活躍連結、有沒有執行時間過長的連結等。資料庫的各種異常也能透過連結情況間接反應出來，特別是資料庫出現死鎖或嚴重卡頓的時候，我們首先應該檢視資料庫是否有異常連結，並殺掉這些異常連結。本篇文章將主要介紹如何檢視資料庫連結及如何殺掉異常連結的方法。

#### 1.檢視資料庫連結

檢視資料庫連結最常用的語句就是 show processlist 了，這條語句可以檢視資料庫中存在的執行緒狀態。普通使用者只可以檢視當前使用者發起的連結，具有 PROCESS 全域性許可權的使用者則可以檢視所有使用者的連結。

show processlist 結果中的 Info 欄位僅顯示每個語句的前 100 個字元，如果需要顯示更多資訊，可以使用 show full processlist 。同樣的，檢視 information_schema.processlist 表也可以看到資料庫連結狀態資訊。

```sql
# 普通使用者只能看到當前使用者發起的連結
mysql> select user();
+--------------------+
| user()             |
+--------------------+
| testuser@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@%                                                |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 授予了PROCESS許可權後，可以看到所有使用者的連結
mysql> grant process on *.* to 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@%                                                |
+----------------------------------------------------------------------+
| GRANT PROCESS ON *.* TO 'testuser'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

透過 show processlist 所得結果，我們可以清晰瞭解各執行緒連結的詳細資訊。具體欄位含義還是比較容易理解的，下面具體來解釋下各個欄位代表的意思：

- **Id**：就是這個連結的唯一標識，可透過 kill 命令，加上這個Id值將此連結殺掉。
- **User**：就是指發起這個連結的使用者名稱。
- **Host**：記錄了傳送請求的客戶端的 IP 和 埠號，可以定位到是哪個客戶端的哪個程式傳送的請求。
- **db**：當前執行的命令是在哪一個資料庫上。如果沒有指定資料庫，則該值為 NULL 。
- **Command**：是指此刻該執行緒連結正在執行的命令。
- **Time**：表示該執行緒連結處於當前狀態的時間。
- **State**：執行緒的狀態，和 Command 對應。
- **Info**：記錄的是執行緒執行的具體語句。

當資料庫連結數過多時，篩選有用資訊又成了一件麻煩事，比如我們只想查某個使用者或某個狀態的連結。這個時候用 show processlist 則會查詢出一些我們不需要的資訊，此時使用 information_schema.processlist 進行篩選會變得容易許多，下面展示幾個常見篩選需求：

```sql
# 只檢視某個ID的連結資訊
select * from information_schema.processlist where id = 705207;

# 篩選出某個使用者的連結
select * from information_schema.processlist where user = 'testuser';

# 篩選出所有非空閒的連結
select * from information_schema.processlist where command != 'Sleep';

# 篩選出空閒時間在600秒以上的連結
select * from information_schema.processlist where command = 'Sleep' and time > 600;

# 篩選出處於某個狀態的連結
select * from information_schema.processlist where state = 'Sending data';

# 篩選某個客戶端IP的連結
select * from information_schema.processlist where host like '192.168.85.0%';
```

#### 2.殺掉資料庫連結

如果某個資料庫連結異常，我們可以透過 kill 語句來殺掉該連結，kill 標準語法是：KILL [CONNECTION | QUERY] processlist_id;

KILL 允許使用可選的 CONNECTION 或 QUERY 修飾符：

- KILL CONNECTION 與不含修改符的 KILL 一樣，它會終止該 process 相關連結。
- KILL QUERY 終止連結當前正在執行的語句，但保持連結本身不變。

殺掉連結的能力取決於 SUPER 許可權：

- 如果沒有 SUPER 許可權，則只能殺掉當前使用者發起的連結。
- 具有 SUPER 許可權的使用者，可以殺掉所有連結。

遇到突發情況，需要批次殺連結時，可以透過拼接 SQL 得到 kill 語句，然後再執行，這樣會方便很多，分享幾個可能用到的殺連結的 SQL ：

```sql
# 殺掉空閒時間在600秒以上的連結，拼接得到kill語句
select concat('KILL ',id,';') from information_schema.`processlist` 
where command = 'Sleep' and time > 600;

# 殺掉處於某個狀態的連結，拼接得到kill語句
select concat('KILL ',id,';') from information_schema.`processlist` 
where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.`processlist` 
where state = 'Waiting for table metadata lock';

# 殺掉某個使用者發起的連結，拼接得到kill語句
select concat('KILL ',id,';') from information_schema.`processlist` 
 user = 'testuser';
```

這裡提醒下，kill 語句一定要慎用！特別是此連結執行的是更新語句或表結構變動語句時，殺掉連結可能需要比較長時間的回滾操作。

**總結：**

本篇文章講解了檢視及殺掉資料庫連結的方法，以後懷疑資料庫有問題，可以第一時間看下資料庫連結情況。

MySQL檢視及殺掉連結方法大全

相關文章