單行資料查詢之謎：為什麼慢？細解查詢長時間不傳回和

單行資料查詢速度緩慢，你有沒有想過其中的原因是什麼？本文深入分析了單行資料查詢為何會變慢的兩大類原因：查詢長時間不傳回和查詢慢。我們将揭開這個謎團，并探索其中的細緻原因。

首先來看一下這張思維導圖，對本文内容有個直覺的認識。

接下來進入正文。

一般情況下，如果我跟你說查詢性能優化，你首先會想到一些複雜的語句，想到查詢需要傳回大量的資料。但有些情況下，“查一行”，也會執行得特别慢。

SQL複制代碼CREATE TABLE `t_color` ( `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_color values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();
           

查詢長時間不傳回

SQL複制代碼select * from t_color where id=1;
           

查詢結果長時間不傳回。

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

等 MDL 寫鎖

基于 MySQL 5.7版本，按照如下操作步驟執行這兩條 SQL，來複現查詢結果長時間不傳回的情況。

其中在 MySQL5.5及以後的版本中，lock table t1 write 就是通過加 MDL 寫鎖，來阻止其他線程通路的。

SQL複制代碼// 先執行
lock table t_color write;
// 後執行
select * from t_color where id=1;
           

為了查找解決方案，可以先從執行 show processlist 指令，看看目前語句處于什麼狀态。

SQL複制代碼show processlist;
           

執行結果如下：

背後原因分析：

session A 通過 lock table 指令持有表 t_color 的 MDL 寫鎖，而 session B 的查詢需要擷取 MDL 讀鎖。是以，session B 進入等待狀态。

通過上述指令并不能準确定位到是哪個 session 持有 MDL 寫鎖，接下來我們通過另一條指令來定位。

通過查詢 sys.schema_table_lock_waits 這張表，我們就可以直接找出造成阻塞的 process id，把這個連接配接用 kill 指令斷開即可。（ performance_schema 和 sys 系統庫都包含該張表，MySQL 啟動時如果設定 performance_schema=on，相比于設定為 off 會有 10% 左右的性能損失 ）

我們新開一個連接配接，執行下述語句，結果為：

SQL複制代碼select blocking_pid from sys.schema_table_lock_waits;
           

根據 MySQL 官網文章介紹可知，先執行下述語句，看看結果如何。

SQL複制代碼select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
           

看一下ENABLED和TIMED是不是都是YES，隻有兩個都是YES的時候才能通過查詢 schema_table_lock_waits 表得到阻塞的程序id。是以我們需要執行 update 操作。

SQL複制代碼UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
           

最後再嘗試執行查詢阻塞程序id，結果如下：

等 flush

MySQL 裡面對表做 flush 操作的用法，一般有以下兩個：

SQL複制代碼flush tables t with read lock;

flush tables with read lock;
           

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

但是正常這兩個語句執行起來都很快，除非它們也被别的線程堵住了。是以，出現 Waiting for table flush 狀态的可能情況是：有一個 flush tables 指令被别的語句堵住了，然後它又堵住了我們的 select 語句。

複現步驟

SQL複制代碼select sleep(1) from t_color;

flush tables t_color;

select * from t_color where id=1;
           

在 session A 中，故意每行都調用一次 sleep(1)，這樣這個語句預設要執行 10 萬秒（全表掃描，每行sleep 1秒 ），在這期間表 t_color 一直是被 session A“打開”着。然後，session B 的 flush tables t_color指令再要去關閉表 t_color，就需要等 session A 的查詢結束。這樣，session C 要再次查詢的話，就會被 flush 指令堵住了。

最後再執行 show processlist 指令。

等行鎖（目前讀）

Shell複制代碼mysql> select * from t_color where id=1 lock in share mode; 
           

由于通路 id=1 這個記錄時要加讀鎖，如果這時候已經有一個事務在這行記錄上持有一個寫鎖，我們的 select 語句就會被堵住。

複現步驟

SQL複制代碼begin;
update t_color set c=c+1 where id=1;

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

執行 show processlist 指令。

如果你用的是 MySQL 5.7 版本，可以通過 sys.innodb_lock_waits 表查到。

SQL複制代碼select * from sys.innodb_lock_waits where locked_table='`mysql_db`.`t_color`'\G
           

上圖中有這樣兩個參數：sql_kill_blocking_query、sql_kill_blocking_connection，前者表示停止 19 号線程目前正在執行的語句，而這個方法其實是沒有用的。因為占有行鎖的是 update 語句，這個語句已經是之前執行完成了的，現在執行 KILL QUERY，無法讓這個事務去掉 id=1 上的行鎖。

嘗試執行 KILL QUERY 19，沒有效果，如下圖所示：

因為 blocking_pid 為 19，是以執行 kill 19，直接斷開這個連接配接，這裡隐含的一個邏輯就是，連接配接被斷開的時候，會自動復原這個連接配接裡面正在執行的線程，也就釋放了 id=1 上的行鎖。

查詢慢

無索引 limit 1

SQL複制代碼select * from t_color where c=50000 limit 1;
           

由于字段 c 上沒有索引，這個語句隻能走 id 主鍵順序掃描，是以需要掃描 5 萬行。

此處我們需要通過設定慢查詢，可以看到掃描了多少資料行。

開啟慢查詢的兩個步驟，全局變量設定：

1. 将 slow_query_log 全局變量設定為“ON”狀态 Shell複制代碼mysql> set global slow_query_log='ON';
2. 查詢超過0秒就記錄 Shell複制代碼set global long_query_time=0;

檢視配置效果：

Shell複制代碼mysql> show variables like 'slow_query%';
mysql> show variables like 'long_query_time';
           

執行上述查詢語句後，檢視日志（注意，需要使用sudo指令才可以有權限看日志）：

掃描行數多，是以執行慢。

主鍵索引 一緻性讀，undolog空間激增

如果根據主鍵來查詢單行資料呢？會不會出現很慢的情況。

Shell複制代碼mysql> select * from t_color where id=1
           

雖然掃描行數是 1，但執行時間卻長達 1.2秒。

如果我把這個 slow log 的截圖再往下拉一點，你可以看到下一個語句，select * from t where id=1 lock in share mode，執行時掃描行數也是 1 行，執行時間是 0.08 毫秒。

複現過程

SQL複制代碼start transaction with consistent snapshot;

delimiter ;;
create procedure iudata()
begin
  declare i int;
  set i=1;
  while(i<=1000000) do
    update t_color set c=c+1 where id=1;
    set i=i+1;
  end while;
end;;
delimiter ;

call iudata();


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

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

帶 lock in share mode 的 SQL 語句，是目前讀，是以會直接讀到 1000001 這個結果，是以速度很快；而 select * from t where id=1 這個語句，是一緻性讀，是以需要從 1000001 開始，依次執行 undo log，執行了 100 萬次以後，才将 1 這個結果傳回。