筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 （本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除） 19） --為什麼我只查一行的語句，也執行這麼慢？ 需要說明一下，如果MySQL資料庫本身就有很大的壓力，導致資料庫伺服器CPU占用率很高或ioutil(IO利用率)很高，這種情況下所 ...
筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》
（本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除）
19） --為什麼我只查一行的語句，也執行這麼慢？
需要說明一下，如果MySQL資料庫本身就有很大的壓力，導致資料庫伺服器CPU占用率很高或ioutil(IO利用率)很高，這種情況下所有的語句執行都有可能變慢，不屬於我們今天的討論範圍。為了便於描述，我們構造一個表，基於這個表來說明問題。這個表有兩個欄位id和c，並且我們事先插入了10萬行記錄。
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();
第一類：查詢長時間不返回
執行如下語句 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.schem_table_lock_waits這張表，我們就可以直接找出造成阻塞的process id，把這個連接用kill命令斷開即可。
等flush
如果我們的show processlist看到了"Waiting for table flush",說明現在有一個線程正要對錶t做flush操作。MySQL裡面對錶做flush操作的用法，一般有下麵兩個：
flush tables t with read lock; flush tables with read lock;
如果指定了表t的話，代表只關閉表t，如果不指定具體的表名，則表示關閉MySQL里的所有打開的表。但正常情況下，這兩個語句執行起來都很快，觸發它們也被別的線程堵住了。
所以，出現Waiting for table flush狀態的可能情況是：有一個flush tables命令被別的語句堵住了，然後它又堵住了我們的select語句。你可以按下麵的方式復現：
這樣在session A中每行都會調用sleep(1),這樣表t一直是被session"打開"，然後，session B的flush tables命令要去關閉表t，就需要等待session A的查詢結束。這樣，session C要再次查詢的話，就會被flush命令堵住了。這個例子的處理方式很前面的一樣，就不再贅述了。
等行鎖：
經過上面的考驗，我們的select語句終於來到了引擎里
mysql> select *from t where id - 1 lock in share model;
由於訪問id=1這個記錄時要加讀鎖，如果這個時候已經有一個事務在這行記錄上持有一個寫鎖，我們的select語句就會被堵住。復現步驟如下：
這時如果你使用show processlist命令會發現statistics。我們來看復現步驟，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;這個命令表示停止4號線程當前正在執行的語句，而這個方法其實是沒有用的。因為占有行鎖的是update語句，這個語句已經是之前執行完成了的，現在執行kill query，無法讓這個事務去掉id=1的行鎖。實際上，kill 4 才有效。也就是說直接斷開這個連接。這裡隱含的一個邏輯是，連接被斷開的時候，會自動回滾這個連接裡面正在執行的線程，也就釋放了id=1的行鎖。
第二類：查詢慢
我們先來看一條sql語句
mysql>select * from t where c=50000 limit 1;
由於欄位c上沒有索引，這個語句只能走id主鍵順序掃描，因此需要掃描50000行。你可以先在慢查詢日誌里確認一下。為了確保被慢查詢日誌記錄下來，你可以先執行set long_query_time=0。將慢查詢日誌的時間閾值設置為0.雖然實際上你可能會看到返回結果不是很慢，但有一個原則是這樣：壞查詢不一定是慢查詢。我們這個例子裡面只有10萬行記錄，數據量大起來的話，執行時間就會線性漲上去了。
掃描行數多，執行慢，這個很好理解。我們再來看一個只掃描了一行，但是執行很慢的語句。執行的語句是這樣的。
mysql>select * from t where id=1;
它的查詢日誌是這樣的：
還是這個查詢，如果我們再加上 lock in share model,執行時間居然會變快：
這是為什麼呢？按理來說，加上了鎖不應該更慢嗎？我們再來看一個提示信息，下圖是這兩個語句的執行輸出結果：
我們的第一個查詢返回的結果是c=1，而帶lock in share mode的語句返回的是c=1000001。如果你還是沒有頭緒，也彆著急，我們來看看復現步驟：
你看到了，session A先用start transaction with consistent snapshot命令啟動了一個事務，之後sessionB才開始執行update語句。SessionB執行完100萬次update語句後，id=1這一行處於什麼狀態呢？你可以從下圖找到答案：
session B 更新完成100萬次，生成了100萬個回滾日誌（undo log）.帶lock in share mode的SQL語句，是當前讀，因此會直接讀到10000001這個結果，所以速度很快；而select * from t where id = 1這個語句，是一致性讀，因此需要從1000001開始，依次執行updo log,執行了100萬次一行，才將結果1返回。
註意，undo log裡面的記錄其實是“把2改成1”這樣，而不是圖中的“-1”；
