不知道讀者有沒有遇到過這么一種異常情況，在使用MySQL時，僅僅是一次很簡單的查詢響應(yīng)時間居然需要上百毫秒甚至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 查詢長時間不返回

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

select * from t where id = 1;

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

1.1 等待MDL鎖

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

圖片

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

在MySQL8.0版本可以使用三個連接client，一個執(zhí)行select sleep(1) from t，一個執(zhí)行alter，一個執(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通過鎖表獲取MDL寫鎖，寫鎖具有排他性，因此sessionB雖然是執(zhí)行讀僅需要MDL讀鎖，也會被阻塞。

這類問題的處理方式，就是找到誰持有 MDL 寫鎖，然后把它 kill 掉。

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

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

圖片

1.2 等待flush

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

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

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

圖片

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

flush表有兩種格式：

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

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

圖片

圖片

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

1.3 等待行鎖

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

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

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

圖片

圖片

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

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

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

圖片

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

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

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

2 查詢慢

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

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

圖片

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

此時可以通過如下場景復(fù)現(xiàn)：

圖片

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

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

圖片

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

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

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