MySQL慢查詢排查

第一步：查看當(dāng)前正在運(yùn)行的事務(wù)狀態(tài)

select trx_state,trx_started,trx_mysql_thread_id,trx_query from information_schema.innodb_trx;

其中：

• Trx_state：事務(wù)狀態(tài)
• Trx_started：事務(wù)啟動(dòng)時(shí)間
• Trx_mysql_thread_id：事務(wù)的線程id
• Trx_query：事務(wù)當(dāng)前執(zhí)行的查詢sql

當(dāng)Trx_state的值為：“LOCK_WAIT”時(shí)，說(shuō)明發(fā)生了鎖等待。等待時(shí)間過(guò)長(zhǎng)可能導(dǎo)致程序返回失敗。

示例：

第二步：查看線程狀態(tài)

show processlist;

• 返回結(jié)果包括：id，host，db，Command，Time，State等。
• 找到id等于第一步中的trx_mysql_thread_id對(duì)應(yīng)的記錄，如果Command是“Sleep”。
• 說(shuō)明這個(gè)線程的事務(wù)一直沒(méi)有提交或者卡住了。我們需要手動(dòng)kill掉。

第三步：殺死線程

Kill id

• 在mysql客戶端執(zhí)行上面的命令殺死線程。
• 一般使用以上的命令足以判斷是否是因?yàn)槭聞?wù)等待問(wèn)題發(fā)生錯(cuò)誤。

第四步：輔助判斷命令

此外，以下命令也可以輔助判斷：

1. 查詢行鎖狀態(tài)：

show status like 'InnoDB_row_lock%';

返回中，Innodb_row_lock_current_waits顯示正在等待行鎖的數(shù)量。

2. 查詢當(dāng)前使用的鎖

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Mysql8中對(duì)應(yīng)為：

select * from performance_schema.data_locks;

3. 查詢鎖等待的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Mysql8中對(duì)應(yīng)為：

select * from performance_schema.data_lock_waits;

4. 查詢當(dāng)前被鎖的表

show OPEN TABLES where In_use > 0;

5. 查詢最近一次的死鎖記錄日志

show engine innodb status

可以導(dǎo)出到本地查看：

mysql -u root -p --execute="show engine innodb status \G" > /root/test.log

根據(jù)以上命令，找到結(jié)果中存在等待異常的trx_id即事務(wù)id，再回到第一步，從當(dāng)前事務(wù)列表中找到對(duì)應(yīng)事務(wù)，然后殺死對(duì)應(yīng)線程。

第五步：開(kāi)啟慢查詢?nèi)罩?/h3>

通過(guò)慢查詢?nèi)罩荆涗泩?zhí)行時(shí)間超時(shí)的sql語(yǔ)句。超時(shí)默認(rèn)是10秒。

1. 查看慢查詢?nèi)罩臼欠耖_(kāi)啟，并開(kāi)啟日志

show variables like '%query%';

返回中，slow_query_log為“OFF”，表示關(guān)閉。開(kāi)啟日志：

set global slow_query_log='ON';

第六步：開(kāi)啟通用查詢?nèi)罩?/h3>

1. 查看通用查詢?nèi)罩臼欠耖_(kāi)啟

show variables like '%general%';

返回中，general_log為“OFF”，表示關(guān)閉。開(kāi)啟日志：

set global general_log='ON'

注意：

通用日志會(huì)記錄所有的sql執(zhí)行語(yǔ)句，會(huì)導(dǎo)致日志文件過(guò)大，以及執(zhí)行速度變慢，應(yīng)在查詢后及時(shí)關(guān)閉。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。