MySQL實戰45講 19

来源:https://www.cnblogs.com/ydssx7/archive/2022/07/28/16530137.html
-Advertisement-
Play Games

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;

查詢結果長時間不返回。

image-20220728170411761

一般碰到這種情況的話,大概率是表 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 的目的是方便看圖。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • GraphPad Prism 9 for Mac是一款專業的科研統計分析繪圖軟體,世界領先的科學家都在使用Prism 9 Mac,使用Prism 9 Mac版幫助您有效的分析、統計並繪製出直觀的圖形,幫您節省大量的時間,讓您更加專註於您的科研。 詳情:GraphPad Prism 9 for Mac ...
  • 前言 製作centos7無人值守安裝,在windows下也可以進行,但是由於U盤文件系統的問題,難免需要在windows和centos7下來回切換,進行配置,才能完成製作,比如主分區exfat格式用於存放ISO和ks.cfg文件通常情況下只能被windows讀取,centos7下讀取不到,掛載不上, ...
  • DxO PureRAW Mac版是發佈了,該軟體採用了智能技術,以解決影響所有RAW文件的七個問題:去馬賽克,降噪,波紋,變形,色差,不想要的漸暈,以及缺乏清晰度。 詳情:DxO PureRAW for mac(raw照片處理器) 功能特色 釋放Adobe Photoshop和Lightroom的R ...
  • 想給自己的照片變得高大上嗎?那就來試試這裡的DxO PhotoLab for Mac版raw圖片處理軟體吧。它可以隨時手動調整,控制照片的各個方面。dxo photolab mac版還能去除噪音有效地消除了光的限制,檢索顏色的細節,應用複雜的光學校正和增強細節。 詳情:DxO PhotoLab 5 ...
  • 換源(建議清華源或者阿裡源) sudo apt update sudo apt upgrade # 軟體升級 sudo apt dist-upgrade #內核更新 grub-customizer 引導控制工具 sudo add-apt-repository ppa:danielrichter200 ...
  • 一、電腦基礎和Linux基礎知識 1.馮諾依曼體繫結構 1946年美籍匈牙利數學家馮·諾依曼於提出存儲程式原理,把程式本身當作數據來對待,程式和該程式處理的數據用同樣的方式儲存。馮·諾依曼體系的要點是: 數字電腦的數制採用二進位,bit位, byte位元組1 byte = 8 bit 電腦應該按 ...
  • #資料庫的CRUD語句 ##INSERT語句 ###在指定列中插入數據 INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1,value2,value3,...); INSERT INTO website (url,country) ...
  • 使用flink的時候難免和redis打交道,相信大家都使用過flink-connector-redis來處理,但是當我想要使用RedisSink寫入集群時,發現居然不支持使用密碼,於是有了這篇筆記。 ...
一周排行
    -Advertisement-
    Play Games
  • Github / Gitee QQ群(1群) : 813100564 / QQ群(2群) : 579033769 視頻教學 介紹 MiniWord .NET Word模板引擎,藉由Word模板和數據簡單、快速生成文件。 Getting Started 安裝 nuget link : https:// ...
  • Array.Sort Array類中相當實用的我認為是Sort方法,相比起冗長的冒泡排序,它的出現讓排序更加的簡化 結果如下: 還可以聲明一個靜態方法用來專門調用指定數組排序,從名為 array 的一維數組中 a 索引處開始,到 b 元素 從小到大排序。 註意: a + b 不能大於 array 的 ...
  • 前言 在上一篇文章CLR類型系統概述里提到,當運行時掛起時, 垃圾回收會執行堆棧遍歷器(stack walker)去拿到堆棧上值類型的大小和堆棧根。這裡我們來翻譯BotR里一篇專門介紹Stackwalking的文章,希望能加深理解。 順便說一句,StackWalker在中文里似乎還沒有統一的翻譯,J ...
  • 使用過 nginx 的小伙伴應該都知道,這個中間件是可以設置跨域的,作為今天的主角,同樣的 反向代理中間件的 YARP 毫無意外也支持了跨域請求設置。 有些小伙伴可能會問了,怎樣才算是跨域呢? 在 HTML 中,一些標簽,例如 img、a 等,還有我們非常熟悉的 Ajax,都是可以指向非本站的資源的 ...
  • 什麼是Git Git 是一個開源的分散式版本控制系統,用於敏捷高效地處理任何或小或大的項目。 Git 是 Linus Torvalds 為了幫助管理 Linux 內核開發而開發的一個開放源碼的版本控制軟體。 Git 與常用的版本控制工具 CVS, Subversion 等不同,它採用了分散式版本庫的 ...
  • 首先CR3是什麼,CR3是一個寄存器,該寄存器內保存有頁目錄表物理地址(PDBR地址),其實CR3內部存放的就是頁目錄表的記憶體基地址,運用CR3切換可實現對特定進程記憶體地址的強制讀寫操作,此類讀寫屬於有痕讀寫,多數驅動保護都會將這個地址改為無效,此時CR3讀寫就失效了,當然如果能找到CR3的正確地址... ...
  • 說明 onlyoffice為一款開源的office線上編輯組件,提供word/excel/ppt編輯保存操作 以下操作均基於centos8系統,officeonly鏡像版本7.1.2.23 鏡像下載地址:https://yunpan.360.cn/surl_y87CKKcPdY4 (提取碼:1f92 ...
  • 二叉樹查找指定的節點 前序查找的思路 1.先判斷當前節點的no是否等於要查找的 2.如果是相等,則返回當前節點 3.如果不等,則判斷當前節點的左子節點是否為空,如果不為空,則遞歸前序查找 4.如果左遞歸前序查找,找到節點,則返回,否繼續判斷,當前的節點的右子節點是否為空,如果不為空,則繼續向右遞歸前 ...
  • ##Invalid bound statement (not found)出現原因和解決方法 ###前言: 想必各位小伙伴在碼路上經常會碰到奇奇怪怪的事情,比如出現Invalid bound statement (not found),那今天我就來分析以下出現此問題的原因。 其實出現這個問題實質就是 ...
  • ###一、背景知識 爬蟲的本質就是一個socket客戶端與服務端的通信過程,如果我們有多個url待爬取,只用一個線程且採用串列的方式執行,那隻能等待爬取一個結束後才能繼續下一個,效率會非常低。 需要強調的是:對於單線程下串列N個任務,並不完全等同於低效,如果這N個任務都是純計算的任務,那麼該線程對c ...