MySQL：為什么查詢一行數據也要花費上百毫秒

作者：果凍布丁兔 2023-12-05 18:00:27

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

不知道讀者有沒有遇到過這么一種異常情況，在使用MySQL時，僅僅是一次很簡單的查詢響應時間居然需要上百毫秒甚至1秒以上，到底是什么原因導致的這種非常異常的情況？這節課我們一起探究一下。

本篇文章使用的SQL數據如下所示。

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 查詢長時間不返回

假設存在如下這種場景，根據主鍵id查詢如果出現長時間不返回，比如如下的語句：

select * from t where id = 1;

像這種根據主鍵查詢還會長時間等待的語句，一般的猜測是有可能被鎖。一般是執行show processlist命令查看當前的語句狀態。

1.1 等待MDL鎖

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

圖片

在MySQL5.6版本可以用鎖的章節進行復現；

在MySQL8.0版本可以使用三個連接client，一個執行select sleep(1) from t，一個執行alter，一個執行select，可以復現。

在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]

文中的實例是在MySQL5.7復現，為：

圖片

sessionA通過鎖表獲取MDL寫鎖，寫鎖具有排他性，因此sessionB雖然是執行讀僅需要MDL讀鎖，也會被阻塞。

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

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

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

圖片

1.2 等待flush

如果是執行如下語句出現卡頓：

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

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

圖片

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

flush表有兩種格式：

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

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

圖片

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

1.3 等待行鎖

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

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

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

圖片

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

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

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

圖片

可以看到，這個信息很全，4 號線程是造成堵塞的罪魁禍首。而干掉這個罪魁禍首的方式，就是 KILL QUERY 4 或 KILL 4。不過，這里不應該顯示“KILL QUERY 4”。

這個命令表示停止 4 號線程當前正在執行的語句，而這個方法其實是沒有用的。因為占有行鎖的是 update 語句，這個語句已經是之前執行完成了的，現在執行 KILL QUERY，無法讓這個事務去掉 id=1 上的行鎖。

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

2 查詢慢

我們知道MySQL的使用規范中，長事務是嚴禁使用的，或者說不建議使用的。那么長事務是否也會導致慢查詢呢？

在如下情況下，可能會出現查詢慢的情況，如圖所示：

圖片

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

此時可以通過如下場景復現：

圖片

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

session B 執行完 100 萬次 update 語句后，id=1 這一行處于什么狀態呢？

圖片

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

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

責任編輯：武曉燕來源：陸隊長

MySQL SQL