目錄

• 查詢長時間不返回
• • 等MDL鎖
  • 等flush
  • 等行鎖
• 查詢慢

構造一張表，表有兩個字段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() begindeclare i int;set i = 1;while( i <= 100000) doinsert into t values(i,i);set i = i+1;end while; end;; delimiter ;call idata();

查詢長時間不返回

在表t執(zhí)行：

select * from t where id = 1;

查詢結果長時間不返回。

等MDL鎖

大概率是表t被鎖住了，接下來分析原因：一般都是首先執(zhí)行show processlist命令，看看當前語句處于什么狀態(tài)。

表示現(xiàn)在有個線程正在表t上請求或者持有MDL寫鎖，把select語句阻塞了：

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

處理方式：找到誰持有MDL寫鎖，然后把它kill掉。

通過

select blocking_pid from sys.schema_table_lock_waits;

得到blocking_pid = 4;

然后用kill命令斷開即可。

等flush

在表t上執(zhí)行下面語句：

select * from information_schema.processlist where id=1;

可以查看出該線程的狀態(tài)是Waiting for table flush;

表示現(xiàn)在有一個線程正要對表t做flush操作。

flush tables t with read lock; --只關閉表t --or flush tables with read lock; --關閉MySQL里面所有打開的表

正常來說，這兩個語句執(zhí)行起來都很快，除非它們也被別的線程堵住了。

所以可能是：有一個flush tables命令被別的語句堵住了，然后它又堵住了我們的select語句。

下圖是執(zhí)行結果：

等行鎖

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

由于訪問id = 1這個記錄時要加讀鎖，如果這時候已經(jīng)有一個事務在這行記錄上持有一個寫鎖，我們的select語句就會被堵住，如下：

session A啟動事務，占用寫鎖，但是不提交，導致session B被堵住。

可以通過：

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

進行查詢，查出是誰占著這個寫鎖

發(fā)現(xiàn)是4號線程，然后我們kill 4

查詢慢

select * from t where c = 50000 limit 1;

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

掃描行數(shù)多，所以執(zhí)行慢，這個很好理解。

而下面的這條語句掃描行數(shù)為1，但是執(zhí)行時間取卻較長

select * from t where id = 1;

在這個場景下，session A先啟動了一個事務，之后session B才開始執(zhí)行update語句。

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

帶lock in share mode 的sql是當前讀，因此會直接讀到 1000001 ，所以速度很快。

select * from t where id = 1語句是一致性讀，因此需要從 1000001 開始，依次執(zhí)行undo log，執(zhí)行100萬次后，才將1返回

**回滾日志過大引起的一致性讀慢，當前讀快 **

總結

以上是生活随笔為你收集整理的《MySQL——查询长时间不返回的三种原因与查询慢的原因》的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。