作者介紹
錢芳園，專注數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)自動(dòng)化領(lǐng)域的工程師，擅長(zhǎng)MySQL、redis運(yùn)維以及基于Go語(yǔ)言的數(shù)據(jù)庫(kù)自動(dòng)化開(kāi)發(fā)。
一、背景
所謂 MySQL 慢查詢，是指在 MySQL 中執(zhí)行時(shí)間超過(guò)指定閾值的語(yǔ)句將被記錄到慢查詢文件中，它是我們 DBA 經(jīng)常討論的話題。
但在慢查詢方面，做得更多的工作，基本都是集中做一個(gè)慢查詢平臺(tái)，可以很好的把慢查詢收集起來(lái)，然后管理起來(lái)，方便查看各種信息，方便和開(kāi)發(fā)溝通，方便看慢查詢的發(fā)展趨勢(shì)等等。但這些工作，對(duì)于解決慢查詢來(lái)講，作用比較小，因?yàn)榫枚弥?dāng)我們成功地把慢查詢平臺(tái)變?yōu)槁樵兒Ｑ髸r(shí)，不管是開(kāi)發(fā)，還是 DBA ，都不知道我應(yīng)該要去解決哪個(gè)慢查詢了，再加上，解決一個(gè)慢查詢，本身其周期非常長(zhǎng)，比如涉及到發(fā)現(xiàn)慢查詢、分析并優(yōu)化慢查詢、測(cè)試優(yōu)化效果、修改業(yè)務(wù)代碼、發(fā)布上線以及觀察效果等等。這么長(zhǎng)的流程，這么長(zhǎng)的周期，很明顯給我們解決慢查詢?cè)斐闪朔浅４蟮淖枇Α?/p>
慢查詢太多，對(duì)于業(yè)務(wù)而言，是有很大風(fēng)險(xiǎn)的，可能隨時(shí)都會(huì)因?yàn)槟撤N原因而被觸發(fā)，并且根據(jù)我們的經(jīng)驗(yàn)，數(shù)據(jù)庫(kù)最常出現(xiàn)的問(wèn)題，都是因?yàn)槁樵儗?dǎo)致數(shù)據(jù)庫(kù)慢了，進(jìn)而導(dǎo)致整個(gè)實(shí)例雪崩，從而導(dǎo)致了線上故障。
從另外一個(gè)角度來(lái)考慮，解決慢查詢，是業(yè)務(wù)和 DBA 雙方面的問(wèn)題，但通常情況下，業(yè)務(wù)并不關(guān)心自己使用的數(shù)據(jù)庫(kù)是不是有慢查詢，只關(guān)心數(shù)據(jù)庫(kù)是不是能返回正確的數(shù)據(jù)，對(duì)數(shù)據(jù)庫(kù)造成什么影響，并不太關(guān)注。而這個(gè)時(shí)候， DBA 只能去“被動(dòng)接受”，并且只能是在問(wèn)題出現(xiàn)之后，再去討論解決相應(yīng)的問(wèn)題。
可能有人會(huì)問(wèn)，有慢查詢，難道 DBA 不知道嗎？為什么不提前解決，非要等到出了問(wèn)題才解決，這個(gè)問(wèn)題，就是本文今天的主題，我們?nèi)绾伟驯粍?dòng)解決，變?yōu)橹鲃?dòng)。
二、分析
根據(jù)上面的背景講述，我們其實(shí)知道，為什么不能提前把問(wèn)題發(fā)現(xiàn)并解決呢？主要原因是， DBA 面對(duì)慢查詢的海洋時(shí)，并不能有效地知道，每個(gè)慢查詢對(duì)業(yè)務(wù)影響的嚴(yán)重程度，再加上解決慢查詢的周期很長(zhǎng)，可能針對(duì)一個(gè)慢查詢，從開(kāi)始到解決完成，需要跟蹤半個(gè)月都不止，從而造成了慢查詢的被動(dòng)解決，成為 DBA 內(nèi)心的痛。
所以，其實(shí)最根本的原因是慢查詢太多，同時(shí)慢查詢沒(méi)有明確的優(yōu)先級(jí)，不知道我們最先應(yīng)該要解決哪個(gè)慢查詢，業(yè)務(wù)同學(xué)也是不知道的。雖然有平臺(tái)可查，但他們?cè)诿鎸?duì)大量的慢查詢時(shí)，解決的意愿就不是太高，最終慢查詢也越積來(lái)越多，直到最后影響業(yè)務(wù)運(yùn)行。
所以，最有效的解決辦法就是，需要建立一種評(píng)分機(jī)制，將當(dāng)前慢查詢系統(tǒng)中的慢查詢進(jìn)行評(píng)分，按照分?jǐn)?shù)給出優(yōu)先級(jí)，然后根據(jù)優(yōu)先級(jí)，將慢查詢信息推送給對(duì)應(yīng)的業(yè)務(wù)方，要求他優(yōu)先解決可能會(huì)對(duì)線上產(chǎn)生嚴(yán)重問(wèn)題的慢查詢，再逐步解決次優(yōu)先級(jí)的慢查詢，以此類推。
三、解決思路
通過(guò)建立一套評(píng)分的模型，給定任何一個(gè)慢查詢，根據(jù)慢查詢的關(guān)鍵屬性，計(jì)算出分?jǐn)?shù)。假定總分?jǐn)?shù)為100，分?jǐn)?shù)越高則風(fēng)險(xiǎn)指數(shù)越高。
評(píng)分模型可以簡(jiǎn)單描述為：
score=func(x)
四、設(shè)計(jì)模型
1、選取評(píng)分項(xiàng)
慢查詢主要因素是由查詢次數(shù)( QueryCount )和查詢其他各項(xiàng)指標(biāo)(例如鎖等待時(shí)間、掃描行數(shù)、查詢時(shí)間、發(fā)送數(shù)據(jù)等)組成。
2、查詢次數(shù)
一個(gè)慢查詢?nèi)绻麍?zhí)行時(shí)間為 1s ，查詢次數(shù)(QueryCount)為 1 和查詢次數(shù)為 1000 時(shí)，對(duì)系統(tǒng)的影響不同，次數(shù)越多危害越大，量變會(huì)引發(fā)質(zhì)變。QueryCount 最正確的值是這個(gè)慢查詢當(dāng)天執(zhí)行的的最大執(zhí)行次數(shù)。
但是預(yù)測(cè)未來(lái)并不可靠，對(duì)于線上業(yè)務(wù)沒(méi)有人會(huì)準(zhǔn)確知道下一時(shí)刻的查詢次數(shù)會(huì)有多少，故我們使用昨天的數(shù)據(jù)，通過(guò)計(jì)算出單個(gè)時(shí)間窗口內(nèi)的執(zhí)行次數(shù)的最大值，來(lái)計(jì)算出這個(gè)慢查詢對(duì)當(dāng)前系統(tǒng)的影響。單個(gè)時(shí)間窗口選取太小，比如 10s 、1min 等計(jì)算出來(lái)的 QueryCount 會(huì)太小，并不能清楚的反應(yīng)指標(biāo)的危害程度；如果選取太大，比如 30min，1hour 會(huì)造成計(jì)算出來(lái)的 QueryCount 太大，顯得指標(biāo)的危害程度非常高。
故我們選取 10min 作為一個(gè)參考值，通過(guò)以 10min 為窗口，滑動(dòng)計(jì)算出 QueryCount 的最大值，作為慢查詢?cè)u(píng)分模型的指標(biāo)之一。
3、查詢其他各項(xiàng)指標(biāo)
慢查詢各項(xiàng)因素主要是由慢查詢?nèi)罩局杏涗浀母黜?xiàng)指標(biāo)。
mysql的慢查詢說(shuō)明，慢查詢示例
# Time: 210818 9:54:25
# User@Host: fangyuan.qian[fangyuan.qian] @ [127.0.0.1] Id: 316538768
# Schema: Last_errno: 0 Killed: 0
# Query_time: 3.278988 Lock_time: 0.001516 Rows_sent: 284 Rows_examined: 1341 Rows_affected: 0
# Bytes_sent: 35600
SET timestamp=1629251665;
SELECT
a.ts_min AS slowlog_time,
a.checksum,
SUM(a.ts_cnt) AS d_ts_cnt,
ROUND(SUM(a.Query_time_sum), 2) AS d_query_time,
ROUND(SUM(a.Query_time_sum) / SUM(a.ts_cnt), 2) AS d_query_time_avg,
a.host_max AS host_ip,
a.db_max AS db_name,
a.user_max AS user_name,
b.first_seen AS first_seen_time
FROM mysql_slowlog_192_168_0_84_3306.query_history a force index(idx_ts_min),
mysql_slowlog_192_168_0_84_3306.query_review b
WHERE a.checksum = b.checksum
AND length(a.checksum)>=15
AND ts_min >= '2021-06-04'
AND ts_min < '2021-06-21'
GROUP BY a.checksum;
4、慢查詢字段說(shuō)明
5、選取評(píng)分項(xiàng)
其中 Time、User@Host 、Id、Schema 、Last_errno 都是描述性的信息不會(huì)造成查詢變成慢查詢；
Query_time 是真實(shí)記錄慢查詢的查詢時(shí)間，查詢時(shí)間越長(zhǎng)對(duì)系統(tǒng)的影響越大；
Lock_time 是當(dāng)前查詢獲取數(shù)據(jù)時(shí)獲取記錄鎖而等待的時(shí)間，等待時(shí)間越長(zhǎng)，越可能造成慢查詢；
Rows_sent 是發(fā)送多少行數(shù)據(jù)給 client ，同一個(gè)查詢語(yǔ)句發(fā)送的數(shù)據(jù)行數(shù)越大，越可能會(huì)造成慢查詢；
Rows_examined 是 server 層檢索的數(shù)據(jù)，檢索的數(shù)據(jù)越多，需要的IO和cpu資源也就越多，越可能造成慢查詢，并影響服務(wù)穩(wěn)定性；
Rows_affected 只針對(duì)修改請(qǐng)求，由于絕大部分慢查詢都是 select ，并不會(huì)修改數(shù)據(jù)，故此值可以忽略；
Bytes_sent 是發(fā)送多少字節(jié)數(shù)據(jù)給 client ，發(fā)送的數(shù)據(jù)量越多，越可能會(huì)造成慢查詢；
由于不同的表行大小不同，并且并不是所有列都需要返回，所以一個(gè)發(fā)送 10 行的數(shù)據(jù)，可能會(huì)比一個(gè)發(fā)送 100 行數(shù)據(jù)的查詢更慢，Rows_sent 不如 Bytes_sent 更為直觀，故我們選取 Bytes_sent ，忽略 Rows_sent 。
所以，慢查詢指標(biāo)中 Query_time、Lock_time 、 Bytes_sent 、 Rows_examined 作為慢查詢?cè)u(píng)分模型中的指標(biāo)。
綜上所述，慢查詢?cè)u(píng)分項(xiàng)共有五項(xiàng)，分別是QueryCount、Query_time、Lock_time、Bytes_sent、Rows_examined。
評(píng)分模型可以簡(jiǎn)單描述為：
score=sum(評(píng)分項(xiàng)*權(quán)重)
6、選取評(píng)分項(xiàng)邊界
評(píng)分模型的評(píng)分項(xiàng)確認(rèn)之后，為了防止單項(xiàng)分?jǐn)?shù)過(guò)高，需要對(duì)評(píng)分項(xiàng)進(jìn)行百分化，并且所有權(quán)重總和為 100 ，根據(jù)評(píng)分項(xiàng)計(jì)分模型可以算出符合增長(zhǎng)曲線的分?jǐn)?shù)，這樣評(píng)分模型計(jì)算出來(lái)的總分?jǐn)?shù)為 100 ，故需要確認(rèn)每項(xiàng)的分?jǐn)?shù)邊界、權(quán)重、計(jì)分模型。
只有各項(xiàng)的邊界、權(quán)重、計(jì)分模型確認(rèn)之后，給定一個(gè)慢查詢，評(píng)分模型才能計(jì)算出合理的分?jǐn)?shù)。評(píng)分項(xiàng)的邊界可以根據(jù)當(dāng)前歷史數(shù)據(jù)設(shè)置。計(jì)分模型和權(quán)重可以首先進(jìn)行假設(shè)，測(cè)試完成之后如果不符合預(yù)期則修改權(quán)重、計(jì)分模型，并重復(fù)測(cè)試-修改過(guò)程，直至測(cè)試結(jié)果符合預(yù)期。
7、邊界選取標(biāo)準(zhǔn)
根據(jù)當(dāng)前慢查詢的歷時(shí)記錄，由于極值數(shù)據(jù)可能會(huì)存在干擾，導(dǎo)致真實(shí)值失真，故需要去除最高部分 5% 的異常值，將 95 分位的值作為每個(gè)評(píng)分項(xiàng)的最高邊界。如果單項(xiàng)值超過(guò)最高邊界的值評(píng)分項(xiàng)，單項(xiàng)分?jǐn)?shù)都將被設(shè)置為最大分?jǐn)?shù)。
8、查詢時(shí)間
95 分位的 sql 慢查詢耗時(shí)約在 60s 左右
9、鎖等待時(shí)間
95分位的慢查詢鎖等待時(shí)間約為0.00629s
10、掃描行數(shù)
95分位的慢查詢掃描行數(shù)約為1785w行
11、查詢次數(shù)
95分位的慢查詢次數(shù)約為180個(gè)
12、發(fā)送流量
由于流量字段缺失，暫時(shí)不計(jì)入評(píng)分系統(tǒng)。
13、計(jì)分項(xiàng)邊界值
14、計(jì)分模型
每一項(xiàng)計(jì)分項(xiàng)的邊界得以確認(rèn)，值越大分?jǐn)?shù)越高。但是存在以下情況：
某些評(píng)分項(xiàng)的值對(duì)系統(tǒng)的影響程度并不是成正比例，超過(guò)某個(gè)臨界點(diǎn)，對(duì)系統(tǒng)的壓力會(huì)迅速增長(zhǎng)。
比如：查詢次數(shù)，一條超時(shí)為1s的sql，查詢1次、查詢10次、查詢100次，對(duì)系統(tǒng)的壓力是不一樣的，量變會(huì)引發(fā)質(zhì)變。
設(shè)計(jì)有一下四種計(jì)分模型：
計(jì)分代碼如下：
/**
* @Description: 計(jì)算單項(xiàng)得分，分?jǐn)?shù)介于最小分?jǐn)?shù)和最大分?jǐn)?shù)之間，可選的計(jì)分模型有：類正弦模型、正弦模型、指數(shù)模型、正比例模型
* @Param val: 單項(xiàng)當(dāng)前值
* @Param minVal: 單項(xiàng)最小值
* @Param maxVal: 單項(xiàng)最大值
* @Param minScore: 單項(xiàng)最小得分
* @Param maxScore: 單項(xiàng)最大得分
* @Param calWay: 計(jì)分模型方式
* @Return float64: 單項(xiàng)得分
*/
func calSingleScore(val, minVal, maxVal, minScore, maxScore float64, calWay string) float64 {
if maxVal == 0 { // 如果值為0則返回0
return 0
}
if val >= maxVal { // 如果值超過(guò)上邊界，則設(shè)置為最大分?jǐn)?shù)
return maxScore
}
if val <= minVal { // 如果值低于下邊界，則設(shè)置為最小分?jǐn)?shù)
return minScore
}
var scoreRatio float64
switch calWay {
case "likeSin": // 類正弦曲線
// Y = a + b·X + c·X2 + d·X3 + e·X4 + f·X5
b := 0.0547372760360247
c := -0.0231045458864445
d := 0.00455283203705563
e := -0.000281663561505204
f := 5.57101673606083e-06
// 使用20個(gè)函數(shù)繪制點(diǎn)位擬合出來(lái)的
ratio := (val - minVal) / (maxVal - minVal) * 20
scoreRatio = b*ratio + c*(ratio*ratio) + d*(ratio*ratio*ratio) +
e*(ratio*ratio*ratio*ratio) + f*(ratio*ratio*ratio*ratio*ratio)
case "sin": // 正弦曲線
ratio := (val - minVal) / (maxVal - minVal)
scoreRatio = math.Sin(math.Pi / 2 * ratio)
case "exponent": // 指數(shù)曲線
ratio := (val - minVal) / (maxVal - minVal)
a := math.Log2(maxScore - minScore)
scoreRatio = math.Pow(2, a*ratio)
return scoreRatio
default: // 默認(rèn)是正比例
scoreRatio = (val - minVal) / (maxVal - minVal)
}
return scoreRatio * (maxScore - minScore)
}
模型曲線如下：
我們期望某些計(jì)分項(xiàng)在各個(gè)不同的階段對(duì)分?jǐn)?shù)的影響是不一樣的，故首先假設(shè)計(jì)分模型和權(quán)重如下：
整體的評(píng)分模型如下：
慢查詢風(fēng)險(xiǎn)指數(shù) = sum(func(慢查詢?cè)u(píng)分項(xiàng)) * 權(quán)重)
ps:風(fēng)險(xiǎn)指數(shù)總分?jǐn)?shù)上限為100
五、測(cè)試
1、測(cè)試一
1）權(quán)重分配
2）計(jì)算結(jié)果數(shù)據(jù)分布
3）樣本SQL分析
不符合的原因：掃描行數(shù)越多對(duì)系統(tǒng)的影響越大，所需要的 IO 和 CPU 資源也就越多，系統(tǒng)處于無(wú)響應(yīng)狀態(tài)的幾率越大，其影響比例應(yīng)該要遠(yuǎn)高于其他評(píng)分項(xiàng)。我們期望掃描行數(shù)越多，分值越高，越需要關(guān)注，故需要調(diào)整各個(gè)評(píng)分項(xiàng)權(quán)重和計(jì)分模型。
2、測(cè)試二
1）權(quán)重分配
重新分片權(quán)重并修改計(jì)分模型之后，整體模型如下：
2）計(jì)算結(jié)果數(shù)據(jù)分布
3）樣本sql分析
六、結(jié)論
評(píng)分模型
采用權(quán)重分配二，需要重點(diǎn)關(guān)注所有分?jǐn)?shù)為50以上的慢查詢。
七、展望
1、更合理的評(píng)分模型
percona sever/mariadb 版本的mysql可以有更豐富的統(tǒng)計(jì)項(xiàng)
percona server/mariadb的慢查詢示例
# Time: 210818 9:54:57
# User@Host: fangyuan.qian[fangyuan.qian] @ [127.0.0.1] Id: 316541341
# Schema: Last_errno: 0 Killed: 0
# Query_time: 2.777965 Lock_time: 0.000289 Rows_sent: 284 Rows_examined: 1341 Rows_affected: 0
# Bytes_sent: 35600 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 1044920
# InnoDB_trx_id: 52AFB919
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000107
# InnoDB_pages_distinct: 1862
SET timestamp=1629251697;
SELECT
a.ts_min AS slowlog_time,
a.checksum,
SUM(a.ts_cnt) AS d_ts_cnt,
ROUND(SUM(a.Query_time_sum), 2) AS d_query_time,
ROUND(SUM(a.Query_time_sum) / SUM(a.ts_cnt), 2) AS d_query_time_avg,
a.host_max AS host_ip,
a.db_max AS db_name,
a.user_max AS user_name,
b.first_seen AS first_seen_time
FROM mysql_slowlog_192_168_0_84_3306.query_history a force index(idx_ts_min),
mysql_slowlog_192_168_0_84_3306.query_review b
WHERE a.checksum = b.checksum
AND length(a.checksum)>=15
AND ts_min >= '2021-06-04'
AND ts_min < '2021-06-21'
GROUP BY a.checksum;
未來(lái)可以將更多的指標(biāo)納入評(píng)分模型，評(píng)分維度會(huì)更多，模型也會(huì)更精確，慢查詢風(fēng)險(xiǎn)指數(shù)也會(huì)更合理。
2、與業(yè)務(wù)相結(jié)合
針對(duì)不同的業(yè)務(wù)，需要關(guān)注的慢查詢風(fēng)險(xiǎn)指數(shù)也應(yīng)該是不一樣的，核心業(yè)務(wù)的慢查詢風(fēng)險(xiǎn)指數(shù)應(yīng)該比較低。不同的業(yè)務(wù)之間慢查詢風(fēng)險(xiǎn)指數(shù)相同的其表示的影響程度也不一定相同。
故引入一個(gè)「業(yè)務(wù)等級(jí)權(quán)重」，目的是將所有業(yè)務(wù)的慢查詢風(fēng)險(xiǎn)指數(shù)量化為同一個(gè)標(biāo)準(zhǔn)。高優(yōu)先級(jí)的業(yè)務(wù)其「業(yè)務(wù)等級(jí)權(quán)重」也會(huì)越高，低優(yōu)先級(jí)的業(yè)務(wù)其「業(yè)務(wù)等級(jí)權(quán)重」也會(huì)越低。按照AppCode維度，將每個(gè)appCode的慢查詢TopN發(fā)送給業(yè)務(wù)方，指數(shù)越高業(yè)務(wù)應(yīng)該越優(yōu)先處理。同時(shí)需要設(shè)置慢查詢平臺(tái)的「慢查詢風(fēng)險(xiǎn)安全指數(shù)」水位線，超過(guò)這個(gè)水位線的所有慢查詢都需要關(guān)注。
最終慢查詢風(fēng)險(xiǎn)指數(shù) = 慢查詢風(fēng)險(xiǎn)指數(shù) * 業(yè)務(wù)等級(jí)權(quán)重
八、總結(jié)
通過(guò)我們的慢查詢分級(jí)模型，可以很好的把一個(gè)慢查詢抽象化為一個(gè)具體的數(shù)字，將其數(shù)字化，給我們的運(yùn)維帶來(lái)了非常大的便捷性，這個(gè)數(shù)字，我們可以稱之為“慢查詢業(yè)務(wù)風(fēng)險(xiǎn)指數(shù)”。
有了數(shù)字化慢查詢，我們就可以很好地去界定一個(gè)慢查詢是不是真的有風(fēng)險(xiǎn)，或者風(fēng)險(xiǎn)有多大，這樣就可以以上帝視角的方式，來(lái)管理所有的慢查詢， 這樣自上而下地去解決問(wèn)題，相比讓DBA整天盯著一個(gè)個(gè)具體的慢查詢?nèi)ソ鉀Q的話，效率會(huì)非常高。
根據(jù)我們抽象化出來(lái)的風(fēng)險(xiǎn)指數(shù)(慢查詢業(yè)務(wù)風(fēng)險(xiǎn)指數(shù))，我們可以按照一定的周期，將風(fēng)險(xiǎn)大的慢查詢，推送給對(duì)應(yīng)的具體的負(fù)責(zé)人，然后不斷地解決，不斷地迭代，最終實(shí)現(xiàn)解決慢查詢從“被動(dòng)”到“主動(dòng)”的完美轉(zhuǎn)換。
我們最終的目標(biāo)是，讓所有慢查詢的風(fēng)險(xiǎn)指數(shù)的中位數(shù)，或者90%、70%分位數(shù)，不斷地下降，最終處于一個(gè)良性的狀態(tài)。
作者丨去哪兒網(wǎng)DBA團(tuán)隊(duì)
來(lái)源丨公眾號(hào)：Qunar技術(shù)沙龍（ID：QunarTL）
dbaplus社群歡迎廣大技術(shù)人員投稿，投稿郵箱：editor@dbaplus.cn
關(guān)注公眾號(hào)【dbaplus社群】，獲取更多原創(chuàng)技術(shù)文章和精選工具下載
