Mysql查詢一行資料超時分析

小王曾是少年發表於2020-12-18
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();

建立表並在裡面插入了10萬行記錄。

1 查詢長時間不返回

在這裡插入圖片描述

圖1 查詢長時間不返回

  一般碰到這種情況的話，大概率是表t被鎖住了。接下來分析原因的時候，一般都是首先執行一下show processlist命令，看看當前語句處於什麼狀態。

1.1 等MDL鎖

  使用show processlist命令檢視Waiting for table metadata lock的示意圖
在這裡插入圖片描述

圖2 Waiting for table metadata lock狀態示意圖

  出現這個狀態表示的是，現在有一個執行緒正在表t上請求或者持有MDL寫鎖，把select語句堵住了。在這裡插入圖片描述

圖3 MySQL 5.7中Waiting for table metadata lock的復現步驟

  session A 通過lock table命令持有表t的MDL寫鎖，而session B的查詢需要獲取MDL讀鎖。所以，session B進入等待狀態。

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

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

圖4 查獲加表鎖的執行緒id

1.2 等flush

select * from information_schema.processlist where id=1;

在這裡插入圖片描述

圖5 Waiting for table flush狀態示意圖

  這個狀態表示的是，現在有一個執行緒正要對錶t做flush操作。MySQL裡面對錶做flush操作的用法，一般有以下兩個：

flush tables t with read lock;

flush tables with read lock;

  這兩個flush語句，如果指定表t的話，代表的是隻關閉表t；如果沒有指定具體的表名，則表示關閉MySQL裡所有開啟的表。

  但是正常這兩個語句執行起來都很快，除非它們也被別的執行緒堵住了。

  所以，出現Waiting for table flush狀態的可能情況是：有一個flush tables命令被別的語句堵住了，然後它又堵住了select語句。

在這裡插入圖片描述

圖6 Waiting for table flush的復現步驟

  在session A中，故意每行都呼叫一次sleep(1)，這樣這個語句預設要執行10萬秒，在這期間表t一直是被session A“開啟”著。然後，session B的flush tables t命令再要去關閉表t，就需要等session A的查詢結束。這樣，session C要再次查詢的話，就會被flush 命令堵住了。

show processlist結果：

在這裡插入圖片描述

圖 7 Waiting for table flush的show processlist 結果

1.3 等行鎖

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

  由於訪問id=1這個記錄時要加讀鎖，如果這時候已經有一個事務在這行記錄上持有一個寫鎖，select語句就會被堵住。
在這裡插入圖片描述

圖 8 行鎖復現

在這裡插入圖片描述

圖 9 行鎖show processlist 現場

  session A啟動了事務，佔有寫鎖，還不提交，是導致session B被堵住的原因。MySQL 5.7版本，可以通過sys.innodb_lock_waits 表查到是誰佔著這個寫鎖。

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

在這裡插入圖片描述

圖10 通過sys.innodb_lock_waits 查行鎖

  這個資訊很全，4號執行緒是造成堵塞的罪魁禍首。

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

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

2 第二類：查詢慢

select * from t where c=50000 limit 1;

  由於欄位c上沒有索引，這個語句只能走id主鍵順序掃描，因此需要掃描5萬行。

在這裡插入圖片描述

圖11 全表掃描5萬行的slow log 

select * from t where id=1;

  雖然掃描行數是1，但執行時間卻長達800毫秒。
在這裡插入圖片描述

圖12 掃描一行卻執行得很慢 

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

  執行時掃描行數也是1行，執行時間是0.2毫秒。
在這裡插入圖片描述

圖 13 加上lock in share mode的slow log

在這裡插入圖片描述

圖14 兩個語句的輸出結果

在這裡插入圖片描述

圖15 復現步驟

  session A先用start transaction with consistent snapshot命令啟動了一個事務，之後session B才開始執行update 語句。
在這裡插入圖片描述

圖16 id=1的資料狀態

  session B更新完100萬次，生成了100萬個回滾日誌(undo log)。帶lock in share mode的SQL語句，是當前讀，因此會直接讀到1000001這個結果，所以速度很快；而select * from t where id=1這個語句，是一致性讀，因此需要從1000001開始，依次執行undo log，執行了100萬次以後，才將1這個結果返回。

3 進一步思考

  在舉例加鎖讀的時候，用的是select * from t where id=1 lock in share mode。由於id上有索引，所以可以直接定位到id=1這一行，因此讀鎖也是隻加在了這一行上。

  但如果是下面的SQL語句，

begin;
select * from t where c=5 for update;
commit;
  • 在 Read Committed 隔離級別下，在語句執行完成後，是隻有行鎖的。而且語句執行完成後，InnoDB就會把不滿足條件的行行鎖去掉。
  • 在 Repeatable Read 隔離級別下，會鎖上聚簇索引中的所有記錄，並且會鎖上聚簇索引內的所有 GAP；

相關文章