還在寫慢SQL？

一前言

不管是開發同學還是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執行慢。

網絡卡滿比如大欄位

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

網路鏈路變長

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

我們知道每個節點之間的資料傳輸是需要時間的，比如同城跨機房（15KM）之間的訪問一般網路耗時1。5ms左右。

鏈路1 ［app1］——呼叫——［app2］——-［proxy］——-［db］相比鏈路2［app1］ —— ［proxy］ ——［db］

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

3.2 受到影響IO的場景

1 磁碟io被其他任務佔用

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

2 raid卡充放電，raid 卡重置

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

root@rac1#megacli -FwTermLog dsply -aALL11/08/143：36：58： prCallback： PR completed for pd=0a11/08/143：36：58： PR cycle complete11/08/143：36：58： EVT#14842-11/03/12 3：36：58： 35=Patrol Read complete11/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卡重置案例。

3 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 電源管理指南》

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

2 指令集最近遇到的一個性能案例是hw的機器，因為指令集合預設關閉導致效能下降15%。

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條件欄位存在正確的組合索引，正確的情況下最佳化器應該選擇組合索引，而非為啥會導致慢查詢呢？

root@rac1 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： indexpossible_keys：idx_sellerid，idx_usestatus_saletype，idx_sellerid_saletype，idx_sidustsvidtype key： PRIMARY ——- 應該選擇 idx_sidustsvidtype key_len： 8ref： NULL rows： 3076Extra： Usingwhere1 row inset （0。00 sec）

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

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

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 tablewhere kid=1342 and type=1 order id desc limit 149420 ，20；

該SQL是一個非常典型的排序+分頁查詢：order by col desc limit N，MMySQL 執行此類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欄位。

對於讀請求，單獨查詢c1 int型別的效能並無差異。查詢欄位c2時，隨著欄位佔用的實際位元組大小增大，耗費的時間增加，也即rt增大。頻寬逐步增大，text的頻寬147MB 對於千兆網絡卡已經滿了。

對於寫請求，因為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突然變慢。

4.11

undo 沒有被purge/回收

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