Categories
程式開發

哪些因素會導致 MySQL 慢查詢


前言

不管是開發同學還是DBA,想必大家都遇到慢查詢(select,update,insert,delete 語句慢),影響業務穩定性。這裡說的 ,有兩個含義一是 比正常的慢 ,有可能正常執行時間是10ms,異常的是100ms 。二是 sql執行時間超過設置的慢查詢標準 比如500ms。

本文從IT架構以及數據庫緯度來分析導致sql執行慢的原因/場景,拋磚引玉,有不足之處還請大家多多提建議。

二、基礎知識

分析慢查詢之前,我們先看看sql執行的路徑,理清楚可能會影響sql執行速度的相關因素。

執行路徑

app —[proxy]—db

app — db

目前大部分的數據庫架構基本都是上面的路徑,sql從app的應用服務器發起經過proxy然後到db,db執行sql進過proxy或者直接返回給app應用服務器。分析這個過程我們可以得到幾個會影響sql執行速度的因素

1 网络,各个节点之间的网络
2 OS系统 ,即数据库服务器
3 MySQL数据库本身

三、基礎系統層面

3.1 網絡層面

網絡丟包,重傳

其實這個比較容易理解。當sql 從app端發送到數據庫,執行完畢,數據庫將結果返回給app端,這個將數據返回給app端的過程本質是網絡包傳輸。因為鏈路的不穩定性,如果在傳輸過程中發送丟包會導致數據包重傳,進而增加數據傳輸時間。從app端來看,就會覺得sql執行慢。

哪些因素會導致 MySQL 慢查詢 1

(圖來自 )

網卡滿 比如大字段

這個場景可能不容易遇到,如果公司業務體量很大,比如平時每天300w訂單的電商平台,平台大促(雙十一,618)的時候極有可能出現網卡被打滿。網卡帶寬被佔滿類似各種節假日高速公路收費站(網卡)擁堵導致車流(數據包傳輸的速度)行動緩慢。

哪些因素會導致 MySQL 慢查詢 2

網絡鏈路變長

該場景會影響應用緯度的一個事務比如交易下單整體耗時。

我們知道每個節點之間的數據傳輸是需要時間的,比如同城跨機房(15KM)之間的訪問一般網絡耗時1.5ms左右。

鏈路1 [app1]–調用–[app2]—[proxy]—[db] 相比 鏈路2[app1] – [proxy] –[db]

執行一條sql請求會增加 [app1]–[app2]之間的網絡傳輸耗時大約3ms。如果一個業務事件包含30個sql ,那麼鏈路1要比鏈路2 多花至少90ms的時間成本。導致業務整體變慢。

3.2 受到影響IO的場景

磁盤io被其他任務佔用

有些備份策略為了減少備份空間的使用,基於xtrabckup備份的時候 使用了 compress 選項將備份集壓縮。當我們需要在數據庫服務器上恢復一個比較大的實例,而解壓縮的過程需要耗費cpu和占用大量io導致數據庫實例所在的磁盤io使用率100%,會影響MySQL 從磁盤獲取數據的速度,導致大量慢查詢。

raid卡 充放電,raid 卡重置

RAID卡都有寫cache(Battery Backed Write Cache),寫cache對IO性能的提升非常明顯,因為掉電會丟失數據,所以必須由電池提供支持。電池會定期充放電,一般為90天左右,當發現電量低於某個閥值時,會將寫cache策略從writeback置為writethrough,相當於寫cache會失效,這時如果系統有大量的IO操作,可能會明顯感覺到IO響應速度變慢,cpu 隊列堆積系統load飆高。下面是一個raid充放電導致sql慢查的案例。

[email protected]#megacli -FwTermLog dsply -aALL
11/08/143:36:58: prCallback: PR completed for pd=0a
11/08/143:36:58: PR cycle complete
11/08/143:36:58: EVT#14842-11/03/12 3:36:58: 35=Patrol Read complete
11/08/143:36:58: Next PR scheduled to start at 11/10/123:01:59
11/08/140:48:04: EVT#14843-11/04/12 0:48:04: 44=Time established as 11/04/12 0:48:04; (25714971 seconds since power on)
11/08/1415:30:13: EVT#14844-11/05/12 15:30:13: 195=BBU disabled; changing WB virtual disks to WT ---问题的原因
11/08/1415:30:13: Changein current cache property detected for LD : 0!
11/08/1415:30:13: EVT#14845-11/05/12 15:30:13: 54=Policy change on VD 00/0 to [ID=00,dcp=0d,ccp=0c,ap=0,dc=0,dbgi=0,S=0|0] from [ID=00,dcp=0d,ccp=0d,ap=0,dc=0,dbgi=0,S=0|0]

raid 卡充電 將磁盤的寫策略有write back 修改為write through ,io性能急劇下降導致sql慢查,進而影響應用層的邏輯處理。

raid 卡重置 當raid卡遇到異常時,會進行重置,相當於程序重啟,導致系統io hang。此時也會導致sql慢。下圖是生產中遇到的 RAID卡重置案例。

哪些因素會導致 MySQL 慢查詢 3

哪些因素會導致 MySQL 慢查詢 4

io調度算法

noop(電梯式調度策略):

NOOP實現了一個FIFO隊列,它像電梯的工作方式一樣對I/O請求進行組織,當有一個新的請求到來時,它將請求合併到最近的請求之後,以此來保證請求同一個介質。 NOOP傾向於餓死讀而利於寫,因此NOOP對於閃存設備,RAM以及嵌入式是最好的選擇。

deadline(介質時間調度策略):

Deadline確保了在一個截至時間內服務請求,這個截至時間是可調整的,而默認讀期限短於寫期限。這樣就防止了寫操作因為不能被讀取而餓死的現象。 Deadline對數據庫類應用是最好的選擇。

anticipatory(預料I/O調度策略):

本質上與Deadline一樣,但在最後一次讀操作後,要等待6ms,才能繼續進行對其他I/O請求進行調度。它會在每個6ms中插入新的I/O操作,而會將一些小寫入流合併成一個大寫入流,用寫入延時換取最大的寫入吞吐量。 AS適合於寫入較多的環境,比如文件服務器,AS對數據庫環境表現很差。

3.3 cpu 類型

cpu 電源策略是控制cpu運行在哪種模式下的耗電策略的,對於數據庫服務器推薦 最大性能模式 以下內容摘自 《Red Hat Enterprise Linux7 電源管理指南》:

哪些因素會導致 MySQL 慢查詢 5

 https://access.redhat.com/documentation/zh-cn/red_hat_enterprise_linux/7/pdf/power_management_guide/Red_Hat_Enterprise_Linux-7-Power_Management_Guide-zh-CN.pdf

指令集 最近遇到的一個性能案例是hw的機器,因為指令集合默認關閉導致性能下降15%。

哪些因素會導致 MySQL 慢查詢 6

https://support.huawei.com/enterprise/zh/doc/EDOC1000039566/c2662e35

自己對CPU並不精通,所以這裡的2個點並非CPU優化配置的全部,自建機房的運維朋友依賴官方技術支持的建議或者技術資料的指導來設置cpu相關參數。

四、數據庫層面

4.1 沒有索引,或者索引不正確

這個場景其實比較容易理解。相信每個DBA工作過程中都會或多或少遇到性能案例都和索引設計有關:創建表,沒有索引,sql隨著數據量增大全表掃描而變慢。這個就不額外舉例子了。

4.2 隱式轉換

發生隱式轉換時,MySQL選擇執行計劃並不能利用到合適的索引而是選擇全表掃描導致慢查詢。常見的引發隱式轉換的場景如下:

in 參數包含多個類型, 簡單說,就是在IN的入口有一個判斷, 如果in中的字段類型不兼容, 則認為不可使用索引. 例如 –圖
判斷符號左邊是字符串,右邊是數字 ,比如 where a=1;其中a是字符串
多表join時,where 左右兩邊的字段的字符集類型不一致。

推薦閱讀《聊聊隱式轉換

4.3 執行計劃錯誤

由於MySQL優化器本身的不足,選擇執行計劃時會導致錯誤的執行計劃使sql走了錯誤的索引或者沒有做索引。比如

在檢查某業務數據庫的slowlog 時發現一個慢查詢,查詢時間1.57s ,檢查表結構where條件字段存在正確的組合索引,正確的情況下優化器應該選擇組合索引,而非為啥會導致慢查詢呢?

  [email protected] 10:48:11>explain select id,gmt_create, gmt_modified,order_id,service_id, seller_id,seller_nick, sale_type from lol where seller_id= 1501204and service_id= 1and sale_type in(3, 4) and use_status in(3, 4, 5, 6) and process_node_id= 6 order by id desc limit 0,20 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lol
         type: index
possible_keys:idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype
          key: PRIMARY  --- 应该选择 idx_sidustsvidtype
      key_len: 8
ref: NULL
         rows: 3076
Extra: Usingwhere
1 row inset (0.00 sec)

推薦閱讀《order by 主鍵id導致全表掃描的問題

4.4 數據巨大

比如select count(*) from t1 where a=‘xxxx’; 儘管字段a有索引,但是如果 符合條件的記錄數超高10w ,查詢速度還是會比較慢。

select  count(*) from t1 where app = 'marketing';
+----------+
| count(*) |
+----------+
|  2671690 |
+----------+
1 row inset (0.92 sec)

4.5 MetaData Lock鎖等待 ****

MDL鎖這個場景其實蠻多案例的,比如ddl開始時,針對同一個表的長查詢還沒結束,後續的寫操作都會被堵住導致 thread running 飆高。實例整體的sql執行慢。

案例一 長查詢/mysqldump 阻塞DDL

哪些因素會導致 MySQL 慢查詢 7

未提交事務阻塞ddl 阻塞查詢

哪些因素會導致 MySQL 慢查詢 8

推薦閱讀《MetaData Lock 之三

4.6 並發更新同一行

常見的秒殺場景:數據庫並發執行update,更新同一行的動作會被其他已經持有鎖的會話堵住,並且需要要進行判斷會不會由於自己的加入導致死鎖,這個時間複雜度O(n ),如果有1000個請求,每個線程都要檢測自己和其他999個線程是否死鎖。如果其他線程都沒有持有其他鎖,約比較50w次(計算方式 999+998+…+1)。這個種鎖等待和檢查死鎖衝突帶來巨大的時間成本。對於OLTP 業務高並發大流量訪問的情況下,鎖等待會直接導致thread running飆高,所有的請求會被阻塞並等待innodb引擎層處理,於是sql 會變慢。

推薦閱讀《熱點更新優化方案

4.7 數據分佈不均

其實和數據分佈相關,常見的比如 字段a 是標記狀態0,1,總行數1000w,a=0的值大概幾千條,a=1的有999w多。顯然執行

select count(*) from tab where a=1 ;

的查詢效率肯定比查詢a=0的要慢很多。

select count(*) from tab where a=0 ;

4.8 sql 姿勢不合理

常見的分頁查詢 ,使用大分頁深度查詢。

SELECT * FROM table where kid=1342 and type=1 order id desc limit 149420 ,20;

該SQL是一個非常典型的排序+分頁查詢:order by col desc limit N,M MySQL 執行此類SQL時需要先掃描到N行,然後再去取 M行。對於此類操作,取前面少數幾行數據會很快,但是掃描的記錄數越多,SQL的性能就會越差,因為N越大,MySQL需要掃描越多的數據來定位到具體的N行,這樣耗費大量的IO 成本和時間成本。

針對limit 優化有很多種方式:

1 前端加緩存、搜索,減少落到庫的查詢操作。比如海量商品可以放到搜索里面,使用瀑布流的方式展現數據,很多電商網站採用了這種方式。
2 優化SQL 訪問數據的方式,直接快速定位到要訪問的數據行。
3 使用書籤方式 ,記錄上次查詢最新/大的id值,向後追溯 M行記錄。對於第二種方式 我們推薦使用”延遲關聯”的方法來優化排序操作,何謂”延遲關聯” :通過使用覆蓋索引查詢返回需要的主鍵,再根據主鍵關聯原表獲得需要的數據。

推薦閱讀《性能優化之分頁查詢

4.9 表結構設計

表結構設計是否合理也是影響sql性能的重要因素之一。以下表格展示了字段類型不同帶來的rt性能差異。其中字段c1 為int類型的字段,字段c2則是表名對應的字符串長度類型varchar(200)到varchar(5000) ,還有text字段。

哪些因素會導致 MySQL 慢查詢 9

對於讀請求,單獨查詢c1 int類型的性能並無差異。查詢字段c2時,隨著字段佔用的實際字節大小增大,耗費的時間增加,也即rt增大。帶寬逐步增大,text的帶寬147MB 對於千兆網卡已經滿了。

哪些因素會導致 MySQL 慢查詢 10

對於寫請求,因為binlog為row模式,字段長度越大,binlog也越大,網絡傳輸帶寬增加。整體rt也增加。

4.10 innodb 刷臟頁

對數據庫運行機制有一定了解的朋友都會知道InnoDB引擎採用Write Ahead Log(WAL)策略,即事務提交時,先寫日誌(redo log),再寫磁盤。 為了提高IO效率,在寫日誌的時候會先寫buffer,然後集中flush buffer pool 到磁盤。 這個過程 我們稱之為 刷臟頁 。官方文檔中描述:

With heavy DML activity, flushing can fall behind if it is not aggressive enough, resulting in excessive memory use in the buffer pool; or, disk writes due to flushing can saturate your I/O capacity if that mechanism is too aggressive. 這個過程中就有可能導致平時執行很快的SQL突然變慢。

推薦閱讀:

https://dev.mysql.com/doc/refman/5.7/en/innodb-lru-background-flushing.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-adaptive_flushing.html

4.11 undo 沒有被purge/回收

UNDO 日誌是 MVCC 的重要組成部分,當一條數據被修改時,UNDO 日誌裡面保存了記錄的歷史版本。當事務需要查詢記錄的歷史版本時,可以通過 UNDO 日誌構建特定版本的數據。

哪些因素會導致 MySQL 慢查詢 11

哪些因素會導致 MySQL 慢查詢 12

每條行記錄上面都有一個指針 DATA_ROLL_PTR,指向最近的 UNDO 記錄。同時每條 UNDO 記錄包含一個指向前一個 UNDO 記錄的指針,這樣就構成了一條記錄的所有 UNDO 歷史的鍊錶。當 UNDO 的記錄還存在,那麼對應的記錄的歷史版本就能被構建出來。
當記錄對應的版本通過DATA_TRX_ID 比對發現不可見時,通過系統列DATAROLLPTR,找到對應的回滾段記錄,繼續通過上​​述判斷記錄可見的規則,進行判斷,如果記錄依舊不可見,繼續通過回滾段查找之前的版本,直到找到對應可見的版本。

所以當有長事務/異常未提交的情況就會因為其他查詢需要構建快照導致undo 不能被及時回收。查詢遍歷的undo越多sql執行的越慢。

推薦閱讀《一次大量刪除導致 MySQL 慢查的分析

五、小結

這裡總結了我工作經歷中遇到的一部分可能會影響SQL執行效率的場景或者案例,經歷有限,難免有遺漏的案例/場景,拋磚引玉,歡迎各位有興趣的讀者朋友留言說說你們遇到的場景。

本文轉載自公眾號有贊coder(ID:youzan_coder)。

原文鏈接

https://mp.weixin.qq.com/s?__biz=MzAxOTY5MDMxNA==&mid=2455760748&idx=1&sn=67115eba10829ca3fb92da2c40c07307&chksm=8c686949bb1fe05f21d2b6e39674d0b35dadfb070fa7641217efc771f5db759866ba3da5a022&scene=27#wechat_redirect