19 | 為什麼我只查一行的語句，也執行這麼慢？有些情況下，“查一行”，也會執行得特別慢。需要說明的是，如果 MySQL 資料庫本身就有很大的壓力，導致資料庫伺服器 CPU 占用率很高或 ioutil（IO 利用率）很高，這種情況下所有語句的執行都有可能變慢，不屬於本章討論範圍。為了便於描述， ...

19 | 為什麼我只查一行的語句，也執行這麼慢？

有些情況下，“查一行”，也會執行得特別慢。

需要說明的是，如果 MySQL 資料庫本身就有很大的壓力，導致資料庫伺服器 CPU 占用率很高或 ioutil（IO 利用率）很高，這種情況下所有語句的執行都有可能變慢，不屬於本章討論範圍。

為了便於描述，構造一個表，基於這個表來說明問題。這個表有兩個欄位 id 和 c，並且在裡面插入了 10 萬行記錄。

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();

第一類：查詢長時間不返回

在表 t 執行下麵的 SQL 語句：

select * from t where id=1;

查詢結果長時間不返回。

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

然後再針對每種狀態，去分析它們產生的原因、如何復現，以及如何處理。

等 MDL 鎖

下圖就是使用 show processlist 命令查看 Waiting for table metadata lock 的示意圖。

出現這個狀態表示的是，現在有一個線程正在表 t 上請求或者持有 MDL 寫鎖，把 select 語句堵住了。

復現這個場景

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

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

但是，由於在 show processlist 的結果裡面，session A 的 Command 列是“Sleep”，導致查找起來很不方便。不過有了 performance_schema 和 sys 系統庫以後，就方便多了。（MySQL 啟動時需要設置 performance_schema=on，相比於設置為 off 會有 10% 左右的性能損失)

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

等 flush

另外一種查詢被堵住的情況

在表 t 上，執行下麵的 SQL 語句：

select * from information_schema.processlist where id=1;

查出來這個線程的狀態是 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 語句。

復現一下這種情況，復現步驟如下圖所示：

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

等行鎖

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

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

行鎖復現步驟和現場如下：

行鎖 show processlist 現場如下圖：

顯然，session A 啟動了事務，占有寫鎖，還不提交，是導致 session B 被堵住的原因。

這個問題並不難分析，但問題是怎麽查出是誰占著這個寫鎖。？？？(待解決)

mysql 查看誰在持有鎖

show engine innodb status查看，但是它只顯示誰在等待鎖，無法查看到誰持有鎖；

第二類：查詢慢

掃描行數多，所以執行慢

select * from t where c=50000 limit 1;

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

作為確認，可以看一下慢查詢日誌。這裡為了把所有語句記錄到 slow log 里，在連接後先執行了 set long_query_time=0，將慢查詢日誌的時間閾值設置為 0。

下圖為全表掃描 5 萬行的 slow log

Rows_examined 顯示掃描了 50000 行。不是很慢呀，11.5 毫秒，線上一般都配置超過 1 秒才算慢查詢。但要記住：壞查詢不一定是慢查詢。這個例子裡面只有 10 萬行記錄，數據量大起來的話，執行時間就線性漲上去了。

掃描一行卻執行得很慢

select * from t where id=1；

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

下一個語句

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

執行時掃描行數也是 1 行，執行時間是 0.2 毫秒

Q：按理說 lock in share mode 還要加鎖，時間應該更長才對，為什麼？

A：

下圖是這兩個語句的執行輸出結果。

第一個語句的查詢結果里 c=1，帶 lock in share mode 的語句返回的是 c=1000001。

復現步驟

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

session B 執行完 100 萬次 update 語句後id狀態如下：

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

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

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

MySQL實戰45講 19