關(guān)于慢SQL，我和面試官扯了很久，面試官也是很謙虛的，總是點頭，自己以為回答的還可以。最后的最后，還是說了“你先回去等通知吧！”。

所以，我決定把這個慢SQL技術(shù)點，好好和你分享分享。希望你下次在遇到類似的面試，能順順利利輕輕松松的斬獲自己想要的offer。

人生最大的喜悅是每個人都說你做不到，你卻完成它了!

什么是慢SQL?

MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，它用來記錄MySQL中查詢時間超過（大于）設(shè)置閾值（long_query_time）的語句，記錄到慢查詢?nèi)罩局小?/p>

其中，long_query_time的默認值是10，單位是秒，也就是說默認情況下，你的SQL查詢時間超過10秒就算慢SQL了。

如何開啟慢SQL日志？

在MySQL中，慢SQL日志默認是未開啟的，也就說就算出現(xiàn)了慢SQL，也不會告訴你的，如果需要知道哪些SQL是慢SQL，需要我們手動開啟慢SQL日志的。

關(guān)于慢SQL是否開啟，我們可以通過下面這個命令來查看：

-- 查看慢查詢?nèi)罩臼欠耖_啟
show variables like '%slow_query_log%';

通過命令，我們就可以看到slow_query_log項為OFF，說明我們的慢SQL日志并未開啟。另外我們也可以看到我們慢SQL日志存放于哪個目錄下和日志文件名。

下面我們來開啟慢SQL日志，執(zhí)行下面的命令：

set global slow_query_log = 1;

這里需要注意，這里開啟的是我們當(dāng)前的數(shù)據(jù)庫，并且，我們重啟數(shù)據(jù)庫后會失效的。

開啟慢SQL日志后，再次查看：

slow_query_log項已經(jīng)變成ON，說明開啟成功。

上面說過慢SQL默認時間是10秒，我們通過下面的命令就可以看到我們慢SQL的默認時間：

show variables like '%long_query_time%';

我們總不能一直使用這個默認值，可能很多業(yè)務(wù)需要時間更短或更長，所以此時，我們就需要對默認時間進行修改，修改命令如下：

set long_query_time = 3;

修改完了，我們再來看看是否已經(jīng)改成了3秒。

這里需要注意：想要永久的生效，還需要修改MySQL下面的配置文件my.cnf 文件。

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

注意：不同操作系統(tǒng)，配置有些區(qū)別。

Linux操作系統(tǒng)中

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置，可以為空，系統(tǒng)會給一個缺省的文件host_name-slow.log)

long_query_time=2 (記錄超過的時間，默認為10s)

log-queries-not-using-indexes (log下來沒有使用索引的query,可以根據(jù)情況決定是否開啟)

log-long-format (如果設(shè)置了，所有沒有使用索引的查詢也將被記錄)

Windows操作系統(tǒng)中

在my.ini的[mysqld]添加如下語句：

log-slow-queries = E:\web\mysql\log\mysqlslowquery.log

long_query_time = 3(其他參數(shù)如上)

執(zhí)行一條慢SQL，因為我們前面已經(jīng)設(shè)置好了慢SQL時間為3秒，所以，我們只要執(zhí)行一條SQL時間超過3秒即可。

SELECT SLEEP(4);

該SQL耗時4.024秒，下面我們就來查看慢SQL出現(xiàn)了多少條。

使用命令：

show global status like '%Slow_queries%';

查詢SQL歷程

找到慢SQL日志文件，打開后就會出現(xiàn)類似下面這樣的語句；

# Time: 2021-07-20T09:17:49.791767Z
# User@Host: root[root] @ localhost []  Id:   150
# Query_time: 0.002549  Lock_time: 0.000144 Rows_sent: 1  Rows_examined: 4079
SET timestamp=1566292669;
select * from city where Name = 'Salala';

簡單說明：

1.Time 該日志記錄的時間

2.User @Host MySQL登錄的用戶和登錄的主機地址

3.Query_time一行 第一個時間是查詢的時間、第二個是鎖表的時間、第三個是返回的行數(shù)、第四個是掃描的行數(shù)

4.SET timestamp 這一個是MySQL查詢的時間

5.sql語句 這一行就很明顯了，表示的是我們執(zhí)行的sql語句

切記

如果你將long_query_time=0 ，那就意味著，我們所有的查詢SQL語句都會輸出到慢SQL日志文件中。

如何定位慢SQL？

通常我們定位慢SQL有兩種方式：

第一種：定位慢查詢SQL可以通過兩個表象進行判斷

• 系統(tǒng)級表象:
• 使用sar命令和top命令查看當(dāng)前系統(tǒng)的狀態(tài)
• 也可以使用Prometheus和Grafana監(jiān)控工具查看當(dāng)前系統(tǒng)狀態(tài)
• CPU消耗嚴重
• IO等待嚴重
• 頁面響應(yīng)時間過長
• 項目日志出現(xiàn)超時等錯誤
• SQL語句表象:
• SQL語句冗長
• SQL語句執(zhí)行時間過長
• SQL從全表掃描中獲取數(shù)據(jù)
• 執(zhí)行計劃中的rows和cost很大

第二種：根據(jù)不同的數(shù)據(jù)庫使用不同的方式獲取問題SQL

• MySQL:
• 慢查詢?nèi)罩?/section>
• 測試工具loadrunner
• ptquery工具
• Oracle:
• AWR報告
• 測試工具loadrunner
• 相關(guān)內(nèi)部視圖vsession_wait
• GRID CONTROL監(jiān)控工具

熟悉慢SQL日志分析工具嗎？

如果開啟了慢SQL日志后，可能會有大量的慢SQL日志產(chǎn)生，此時再用肉眼看，那是不太現(xiàn)實的，所以大佬們就給我搞了個工具：mysqldumpslow。

mysqldumpslow能將相同的慢SQL歸類，并統(tǒng)計出相同的SQL執(zhí)行的次數(shù)，每次執(zhí)行耗時多久、總耗時，每次返回的行數(shù)、總行數(shù)，以及客戶端連接信息等。

通過命令

mysqldumpslow --help

可以看到相關(guān)參數(shù)的說明：

~# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

比較常用的參數(shù)有這么幾個：

-s 指定輸出的排序方式
   t  : 根據(jù)query time(執(zhí)行時間)進行排序；
   at : 根據(jù)average query time(平均執(zhí)行時間)進行排序；（默認使用的方式）
   l  : 根據(jù)lock time(鎖定時間)進行排序；
   al : 根據(jù)average lock time(平均鎖定時間)進行排序；
   r  : 根據(jù)rows(掃描的行數(shù))進行排序；
   ar : 根據(jù)average rows(掃描的平均行數(shù))進行排序；
   c  : 根據(jù)日志中出現(xiàn)的總次數(shù)進行排序；
-t 指定輸出的sql語句條數(shù)；
-a 不進行抽象顯示(默認會將數(shù)字抽象為N，字符串抽象為S)；
-g 滿足指定條件，與grep相似；
-h 用來指定主機名(指定打開文件，通常慢查詢?nèi)罩久Q為“主機名-slow.log”，用-h exp則表示打開exp-slow.log文件)；

使用方式

mysqldumpslow常用的使用方式如下：

# mysqldumpslow -s c slow.log

如上一條命令，應(yīng)該是mysqldumpslow最簡單的一種形式，其中-s參數(shù)是以什么方式排序的意思，c指代的是以總數(shù)從大到小的方式排序。-s的常用子參數(shù)有：c: 相同查詢以查詢條數(shù)和從大到小排序。t: 以查詢總時間的方式從大到小排序。l: 以查詢鎖的總時間的方式從大到小排序。at: 以查詢平均時間的方式從大到小排序。al: 以查詢鎖平均時間的方式從大到小排序。

同樣的，還可以增加其他參數(shù)，實際使用的時候，按照自己的情況來。

其他常用方式：

# 得到返回記錄集最多的10 個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

# 得到訪問次數(shù)最多的10 個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

# 得到按照時間排序的前10 條里面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

# 另外建議在使用這些命令時結(jié)合| 和more 使用，否則有可能出現(xiàn)爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

接下，我們來個實際操作。

實操

root@yunzongjitest1:~# mysqldumpslow -s t -t 3

Reading mysql slow query log from /var/lib/mysql/exp-slow.log /var/lib/mysql/yunzongjitest1-slow.log
Count: 464  Time=18.35s (8515s)  Lock=0.01s (3s)  Rows=90884.0 (42170176), root[root]@localhost
  select ************

Count: 38  Time=11.22s (426s)  Lock=0.00s (0s)  Rows=1.0 (38), root[root]@localhost
  select *********** not like 'S'

Count: 48  Time=5.07s (243s)  Lock=0.02s (1s)  Rows=1.0 (48), root[root]@localhost
  select ********='S'

這其中的SQL語句因為涉及某些信息，所以我都用*號將主體替換了，如果希望得到具體的值，使用-a參數(shù)。

使用mysqldumpslow查詢出來的摘要信息，包含了這些內(nèi)容：

Count: 464 ：表示慢查詢?nèi)罩究偣灿涗浀竭@條sql語句執(zhí)行的次數(shù)；

Time=18.35s (8515s)：18.35s表示平均執(zhí)行時間（-s at)，8515s表示總的執(zhí)行時間(-s t)；

Lock=0.01s (3s)：與上面的Time相同，第一個表示平均鎖定時間(-s al)，括號內(nèi)的表示總的鎖定時間(-s l)（也有另一種說法，說是表示的等待鎖釋放的時間）；

Rows=90884.0 (42170176): 第一個值表示掃描的平均行數(shù)(-s ar)，括號內(nèi)的值表示掃描的總行數(shù)(-s r)。