慢SQL，壓垮團(tuán)隊(duì)的最后一根稻草！

作者：鴨血粉絲Tang 2022-08-15 07:32:03

我們都知道，我們每執(zhí)行一次 SQL，數(shù)據(jù)庫(kù)除了會(huì)返回執(zhí)行結(jié)果以外，還會(huì)返回 SQL 執(zhí)行耗時(shí)，以 MySQL 數(shù)據(jù)庫(kù)為例，當(dāng)我們開(kāi)啟了慢 SQL 監(jiān)控開(kāi)關(guān)后，默認(rèn)配置下，當(dāng) SQL 的執(zhí)行時(shí)長(zhǎng)大于 10 秒，會(huì)被記錄到慢 SQL 的日志文件中。

在實(shí)際的業(yè)務(wù)系統(tǒng)開(kāi)發(fā)中，雖然我們會(huì)嚴(yán)抓代碼質(zhì)量，但是慢 SQL 的檢測(cè)卻常常容易被忽視，今天我們就一起來(lái)總結(jié)一下關(guān)于慢 SQL 可能存在的系統(tǒng)運(yùn)行風(fēng)險(xiǎn)。

一、什么是慢 SQL

什么是慢SQL？顧名思義，運(yùn)行時(shí)間較長(zhǎng)的 SQL 語(yǔ)句即為慢 SQL！

那問(wèn)題來(lái)了，多久才算慢呢？

這個(gè)慢其實(shí)是一個(gè)相對(duì)值，不同的業(yè)務(wù)場(chǎng)景下，標(biāo)準(zhǔn)要求是不一樣的。

當(dāng)然，這個(gè)值還可以重新設(shè)置，生產(chǎn)環(huán)境慢 SQL 一般會(huì)設(shè)置為0.1~0.2s?。當(dāng)我們將其設(shè)置為0.2s?時(shí)，當(dāng)前數(shù)據(jù)庫(kù)所有 SQL 的執(zhí)行時(shí)長(zhǎng)超過(guò)0.2s的都會(huì)被視為慢 SQL。

可能有的同學(xué)會(huì)發(fā)出疑問(wèn)，我們?yōu)槭裁匆粉櫬?SQL，有什么意義呢？

二、慢 SQL 危害

這里要從慢 SQL 的危害談起，以 MySQL 數(shù)據(jù)庫(kù)為例，總結(jié)起來(lái)有以下幾點(diǎn)：

當(dāng)出現(xiàn)慢查詢，DDL 操作都會(huì)被阻塞，也就是說(shuō)創(chuàng)建表、修改表、刪除表、執(zhí)行數(shù)據(jù)備份等操作都需要等待，這對(duì)實(shí)時(shí)備份重要數(shù)據(jù)的系統(tǒng)來(lái)說(shuō)是不可容忍的。
慢查可能會(huì)占用 mysql 的大量?jī)?nèi)存，嚴(yán)重的時(shí)候會(huì)導(dǎo)致服務(wù)器直接掛掉，整個(gè)系統(tǒng)直接癱瘓。
慢 SQL 的執(zhí)行時(shí)間過(guò)長(zhǎng)，可能會(huì)導(dǎo)致應(yīng)用的進(jìn)程因超時(shí)被 kill，無(wú)法返回結(jié)果給到客戶端。
造成數(shù)據(jù)庫(kù)幻讀、不可重復(fù)讀的概率更大，假設(shè)該慢 SQL 是一個(gè)更新操作但因執(zhí)行時(shí)間過(guò)長(zhǎng)未提交，而另一條 SQL 也在更新數(shù)據(jù)并且已提交，用戶再次查詢的時(shí)候，看到的數(shù)據(jù)可能與實(shí)際結(jié)果不符。
嚴(yán)重影響用戶體驗(yàn)，SQL 的執(zhí)行時(shí)間越長(zhǎng)，頁(yè)面加載數(shù)據(jù)耗時(shí)也就越長(zhǎng)。

以千萬(wàn)級(jí)的訂單表為例，未優(yōu)化的情況下，單表分頁(yè)查詢 10 條數(shù)據(jù)，耗時(shí)：39s。

首先不說(shuō)可能對(duì)數(shù)據(jù)庫(kù)服務(wù)器造成的潛在壓力，沒(méi)有任何一個(gè)用戶會(huì)在頁(yè)面查詢訂單查詢等待 39 秒！

三、如何定位慢 SQL

說(shuō)了這么多，我們?nèi)绾稳ザㄎ宦?SQL 呢？

3.1開(kāi)啟慢 SQL 監(jiān)控

以 MySQL 為例，我們可以通過(guò)如下方式，查詢是否開(kāi)啟慢 SQL 的監(jiān)控。

show variables like 'slow_query_log%';

通過(guò)如下命令，開(kāi)啟慢 SQL 監(jiān)控，執(zhí)行成功之后，客戶端需要重新連接才能生效。

-- 開(kāi)啟慢 SQL 監(jiān)控
set global slow_query_log = 1;

如果想關(guān)閉慢 SQL 監(jiān)控，將其配置為0就可以了。

-- 關(guān)閉慢 SQL 監(jiān)控
set global slow_query_log = 0;

需要特別注意的是，當(dāng)服務(wù)器重啟之后，當(dāng)前配置會(huì)失效！

3.2配置慢 SQL 閥值

默認(rèn)的慢 SQL 閥值是10秒，可以通過(guò)如下語(yǔ)句查詢慢 SQL 的閥值。

-- 查詢慢 SQL 的閥值
show variables like "long_query_time";

我們可以通過(guò)如下方式，將慢 SQL 閥值配置成0.2秒。

-- 修改慢 SQL 的閥值
set global long_query_time = 0.2;

然后，退出客戶端，重新連接服務(wù)器，就生效了！

與之類似，當(dāng)服務(wù)器重啟之后，當(dāng)前配置會(huì)失效！

3.3永久開(kāi)啟慢 SQL 監(jiān)控

以上的操作，當(dāng)服務(wù)器不重啟會(huì)一直有效，但是當(dāng)服務(wù)器一單重啟之后，配置就會(huì)失效，如果想永久生效，可以通過(guò)修改全局配置文件my.cnf使之永久生效。

以 CentOS 為例，打開(kāi)my.cnf配置文件，添加如下配置變量。

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/ecs-203056-slow.log
long_query_time = 1

重啟 mysql 服務(wù)器

systemctl restart mysqld

3.4慢 SQL 監(jiān)控測(cè)試

初始化一張日志表，數(shù)據(jù)量在 10 萬(wàn)左右就夠了，然后我們來(lái)執(zhí)行 SQL，看看是不是被正常抓取到。

很清晰的看到，慢 SQL 已經(jīng)被抓取記錄。

日志內(nèi)容詳解：

Time：表示客戶端查詢時(shí)間。
root[root]：表示客戶端查詢用戶和IP。
Query_time：表示查詢耗時(shí)。
Lock_time：表示等待 table lock 的時(shí)間，注意InnoDB的行鎖等待是不會(huì)反應(yīng)在這里的。
Rows_sent：表示返回了多少行記錄（結(jié)果集）。
Rows_examined：表示檢查了多少條記錄。

除此之外，我們還可以借助mysqldumpslow命令工具，分析慢 SQL 的數(shù)據(jù)情況，可以通過(guò)如下參數(shù)進(jìn)行組合分析

-s         表示按何種方式排序，支持的參數(shù)如下
            al: 平均鎖定時(shí)間
            ar: 平均返回記錄數(shù)
            at: 平均查詢時(shí)間
            c: 訪問(wèn)次數(shù)
            l: 鎖定時(shí)間
            r: 返回記錄
            t: 查詢時(shí)間
-t NUM       返回前面多少條的數(shù)據(jù)
-g PATTERN   后邊搭配一個(gè)正則匹配模式，大小寫(xiě)不敏感

常見(jiàn)的用法如下：

查詢返回記錄集最多的10個(gè) SQL；

mysqldumpslow -s r -t 10 /var/lib/mysql/ecs-203056-slow.log

查詢?cè)L問(wèn)次數(shù)最多的10個(gè)SQL；

mysqldumpslow -s c -t 10 /var/lib/mysql/ecs-203056-slow.log

查詢按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句。

mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/ecs-203056-slow.log

四、慢 SQL 是怎么發(fā)生的

面對(duì)這種耗時(shí)巨長(zhǎng)的 SQL，我們不禁會(huì)發(fā)出一個(gè)疑問(wèn)，它是怎么發(fā)生的呢？

這得從 SQL 的執(zhí)行過(guò)程說(shuō)起，我們先簡(jiǎn)單的看看下面這個(gè)圖。

一條 SQL 語(yǔ)句執(zhí)行時(shí)，總結(jié)起來(lái)大概分為以下幾個(gè)步驟：

若查詢緩存打開(kāi)則會(huì)優(yōu)先查詢緩存，若命中則直接返回結(jié)果給客戶端。
若緩存未命中，此時(shí) MySQL 需要搞清楚這條語(yǔ)句需要做什么，則通過(guò)分析器進(jìn)行詞法分析、語(yǔ)法分析。
搞清楚要做什么之后，MySQL 會(huì)通過(guò)優(yōu)化器對(duì) SQL 進(jìn)行優(yōu)化，生成一個(gè)最優(yōu)的執(zhí)行計(jì)劃。
最后通過(guò)執(zhí)行器與存儲(chǔ)引擎提供的接口進(jìn)行交互，將結(jié)果返回給客戶端。

在 MySQL 執(zhí)行過(guò)程中，優(yōu)化器可能會(huì)對(duì)我們即將要執(zhí)行的 SQL 進(jìn)行改造，改造思路如下：