不知道讀者有沒(méi)有遇到過(guò)這么一種異常情況，在使用MySQL時(shí)，僅僅是一次很簡(jiǎn)單的查詢響應(yīng)時(shí)間居然需要上百毫秒甚至1秒以上，到底是什么原因?qū)е碌倪@種非常異常的情況？這節(jié)課我們一起探究一下。

本篇文章使用的SQL數(shù)據(jù)如下所示。

mysql> CREATE TABLE `t` (


  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;


call idata();

1 查詢長(zhǎng)時(shí)間不返回

假設(shè)存在如下這種場(chǎng)景，根據(jù)主鍵id查詢?nèi)绻霈F(xiàn)長(zhǎng)時(shí)間不返回，比如如下的語(yǔ)句：

select * from t where id = 1;

像這種根據(jù)主鍵查詢還會(huì)長(zhǎng)時(shí)間等待的語(yǔ)句，一般的猜測(cè)是有可能被鎖。一般是執(zhí)行show processlist命令查看當(dāng)前的語(yǔ)句狀態(tài)。

1.1 等待MDL鎖

使用show processlist命令查看Waiting for table metadata lock的示意圖。出現(xiàn)這個(gè)狀態(tài)原因是：現(xiàn)在正在有一個(gè)線程正在表t上請(qǐng)求或者持有MDL寫(xiě)鎖，把select語(yǔ)句阻塞。

圖片

在MySQL5.6版本可以用鎖的章節(jié)進(jìn)行復(fù)現(xiàn)；

在MySQL8.0版本可以使用三個(gè)連接client，一個(gè)執(zhí)行select sleep(1) from t，一個(gè)執(zhí)行alter，一個(gè)執(zhí)行select，可以復(fù)現(xiàn)。

在MySQL5.7.30版本：

sessionA：begin; select c from t order by rand() limit 3;
sessionB: alter table t add f int;[blocked]
sessionC: select c from t order by rand() limit 3;[blocked]

文中的實(shí)例是在MySQL5.7復(fù)現(xiàn)，為：

圖片

sessionA通過(guò)鎖表獲取MDL寫(xiě)鎖，寫(xiě)鎖具有排他性，因此sessionB雖然是執(zhí)行讀僅需要MDL讀鎖，也會(huì)被阻塞。

這類問(wèn)題的處理方式，就是找到誰(shuí)持有 MDL 寫(xiě)鎖，然后把它 kill 掉。

但是，由于在 show processlist 的結(jié)果里面，session A 的 Command 列是“Sleep”，導(dǎo)致查找起來(lái)很不方便。不過(guò)有了 performance_schema 和 sys 系統(tǒng)庫(kù)以后，就方便多了。

通過(guò)查詢 sys.schema_table_lock_waits 這張表，我們就可以直接找出造成阻塞的 process id，把這個(gè)連接用 kill 命令斷開(kāi)即可。

圖片

1.2 等待flush

如果是執(zhí)行如下語(yǔ)句出現(xiàn)卡頓：

mysql> select * from information_schema.processlist where id=1;

注意其中的STATE字段，顯示為：Waiting for table flush，也就是等待刷盤(pán)。

圖片

即，此時(shí)數(shù)據(jù)不在內(nèi)存中，會(huì)從磁盤(pán)讀取到數(shù)據(jù)后加載到buffer pool中，如果此時(shí)buffer pool已經(jīng)被占滿，則會(huì)使用LRU淘汰掉舊數(shù)據(jù)，如果要淘汰的數(shù)據(jù)時(shí)臟頁(yè)，就會(huì)觸發(fā)flush，造成卡頓。

flush表有兩種格式：

/**
指定表t，代表只關(guān)閉表t
*/
flush tables t with read lock;
/**
沒(méi)有指定表，代表只關(guān)閉MySQL打開(kāi)的所有表
*/
flush tables with read lock;

關(guān)閉所有已打開(kāi)的表對(duì)象，同時(shí)將查詢緩存中的結(jié)果清空。就是說(shuō)Flush tables的一個(gè)效果就是會(huì)等待所有正在運(yùn)行的SQL請(qǐng)求結(jié)束。 因?yàn)?，SQL語(yǔ)句在執(zhí)行前，都會(huì)打開(kāi)相應(yīng)的表對(duì)象，如select * from t1語(yǔ)句，會(huì)找到t1表的frm文件，并打開(kāi)表內(nèi)存對(duì)象。為了控制表對(duì)象使用的內(nèi)存空間和其他資源，MySQL會(huì)隱式（后臺(tái)表對(duì)象管理線程）或顯式（flush tables等）來(lái)關(guān)閉已打開(kāi)但并沒(méi)有使用的表對(duì)象。 然而，正在使用的表對(duì)象是不能關(guān)閉的（如SQL請(qǐng)求仍在運(yùn)行），因此，F(xiàn)lush Tables操作會(huì)被正在運(yùn)行的SQL請(qǐng)求阻塞。

圖片

圖片

根據(jù)show processlist查詢的id，將select sleep(1) from t的進(jìn)行先結(jié)束，然后flush table t的命令執(zhí)行完，sessionC就會(huì)執(zhí)行。

1.3 等待行鎖

還有第三種情況就是我們最為熟悉的鎖。假設(shè)執(zhí)行語(yǔ)句如下，在查詢時(shí)開(kāi)啟共享鎖：

mysql> select * from t where id=1 lock in share mode;

在語(yǔ)句執(zhí)行的加鎖會(huì)增加鎖沖突的幾率，從而導(dǎo)致語(yǔ)句之間的相互等待鎖釋放。

圖片

圖片

此時(shí)，由于sessionA啟動(dòng)了事務(wù)，占用了寫(xiě)鎖，阻塞了sessionB的共享鎖的獲取。

在MySQL5.7可以使用sys.innodb_lock_waits表查詢到占用寫(xiě)鎖的線程：

mysql> select * from t sys.innodb_lock_waits where 
locked_table='`test`.`t`'\G

圖片

可以看到，這個(gè)信息很全，4 號(hào)線程是造成堵塞的罪魁禍?zhǔn)?。而干掉這個(gè)罪魁禍?zhǔn)椎姆绞剑褪?KILL QUERY 4 或 KILL 4。不過(guò)，這里不應(yīng)該顯示“KILL QUERY 4”。

這個(gè)命令表示停止 4 號(hào)線程當(dāng)前正在執(zhí)行的語(yǔ)句，而這個(gè)方法其實(shí)是沒(méi)有用的。因?yàn)檎加行墟i的是 update 語(yǔ)句，這個(gè)語(yǔ)句已經(jīng)是之前執(zhí)行完成了的，現(xiàn)在執(zhí)行 KILL QUERY，無(wú)法讓這個(gè)事務(wù)去掉 id=1 上的行鎖。

實(shí)際上，KILL 4 才有效，也就是說(shuō)直接斷開(kāi)這個(gè)連接。這里隱含的一個(gè)邏輯就是，連接被斷開(kāi)的時(shí)候，會(huì)自動(dòng)回滾這個(gè)連接里面正在執(zhí)行的線程，也就釋放了 id=1 上的行鎖。

2 查詢慢

我們知道MySQL的使用規(guī)范中，長(zhǎng)事務(wù)是嚴(yán)禁使用的，或者說(shuō)不建議使用的。那么長(zhǎng)事務(wù)是否也會(huì)導(dǎo)致慢查詢呢？

在如下情況下，可能會(huì)出現(xiàn)查詢慢的情況，如圖所示：

圖片

第一條sql查詢的是當(dāng)前事務(wù)版本時(shí)，id = 1 時(shí)的值，但是第二條sql 查詢可以得知當(dāng)前值得最新版本的值為1000001，所以在查詢數(shù)據(jù)時(shí)需要進(jìn)行記錄版本的回滾，拿到自己事務(wù)可見(jiàn)的記錄的版本。所以如果當(dāng)前事務(wù)比較老并且當(dāng)前這個(gè)數(shù)據(jù)存在大量的版本，那么就對(duì)該記錄進(jìn)行大量的回滾操作，消費(fèi)個(gè)更多的時(shí)間。

此時(shí)可以通過(guò)如下場(chǎng)景復(fù)現(xiàn)：

圖片

你看到了，session A 先用 start transaction with consistent snapshot 命令啟動(dòng)了一個(gè)事務(wù)，之后 session B 才開(kāi)始執(zhí)行 update 語(yǔ)句。

session B 執(zhí)行完 100 萬(wàn)次 update 語(yǔ)句后，id=1 這一行處于什么狀態(tài)呢？

圖片

session B 更新完 100 萬(wàn)次，生成了 100 萬(wàn)個(gè)回滾日志 (undo log)。

帶 lock in share mode 的 SQL 語(yǔ)句，是當(dāng)前讀（讀最新版本的數(shù)據(jù)），因此會(huì)直接讀到 1000001 這個(gè)結(jié)果，所以速度很快；而 select * from t where id=1 這個(gè)語(yǔ)句，是一致性讀，因此需要從 1000001 開(kāi)始，依次執(zhí)行 undo log，執(zhí)行了 100 萬(wàn)次回滾以后，才將 1 這個(gè)結(jié)果返回。

注意，undo log 里記錄的其實(shí)是“把 2 改成 1”，“把 3 改成 2”這樣的操作邏輯，畫(huà)成減 1 的目的是方便你看圖。