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

所以，我決定把這個(gè)慢SQL技巧點(diǎn)，好好跟你分享分享。希望你下次在遇到類似的面試，能順順利利輕輕鬆鬆的斬獲自己想要的offer。

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

什麼是慢SQL?

#MySQL的慢查詢?nèi)照I是MySQL提供的一種日誌記錄，它用來記錄MySQL中查詢時(shí)間超過（大於）設(shè)定閾值（long_query_time）的語句，記錄到慢查詢?nèi)照I中。

其中，long_query_time的預(yù)設(shè)值是10，單位是秒，也就是說預(yù)設(shè)情況下，你的SQL查詢時(shí)間超過10秒就算慢SQL了。

如何開啟慢SQL日誌？

在MySQL中，慢SQL日誌預(yù)設(shè)是未開啟的，也就說就算出現(xiàn)了慢SQL，也不會(huì)告訴你的，如果需要知道哪些SQL是慢SQL，需要我們手動(dòng)開啟慢SQL日誌的。

關(guān)於慢SQL是否開啟，我們可以透過下面這個(gè)指令來查看：

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

透過指令，我們就可以看到slow_query_log項(xiàng)目為OFF，表示我們的慢SQL日誌並未開啟。另外我們也可以看到我們慢SQL日誌存放在哪個(gè)目錄下和日誌檔名。

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

set global slow_query_log = 1;

這裡要注意，這裡開啟的是我們目前的資料庫，並且，我們重新啟動(dòng)資料庫後會(huì)失效的。

開啟慢SQL日誌後，再檢視：

#slow_query_log項(xiàng)目變成ON，說明開啟成功。

上面說過慢SQL預(yù)設(shè)時(shí)間是10秒，我們透過下面的指令就可以看到我們慢SQL的預(yù)設(shè)時(shí)間：

show variables like '%long_query_time%';

我們總不能一直使用這個(gè)預(yù)設(shè)值，可能很多業(yè)務(wù)需要時(shí)間更短或更長(zhǎng)，所以此時(shí)，我們就需要對(duì)預(yù)設(shè)時(shí)間進(jìn)行修改，修改指令如下：

set long_query_time = 3;

修改完了，我們?cè)賮砜纯词欠褚呀?jīng)改成了3秒。

這裡要注意：想要永久的生效，還需要修改MySQL下面的設(shè)定檔my. cnf 檔。

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

注意：不同作業(yè)系統(tǒng)，設(shè)定有些差別。

Linux作業(yè)系統(tǒng)中

在mysql設(shè)定檔my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (指定日誌檔案存放位置，可以為空，系統(tǒng)會(huì)給一個(gè)缺省的檔案host_name-slow.log)

long_query_time=2 (記錄超過的時(shí)間，預(yù)設(shè)為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，因?yàn)槲覀兦懊嬉呀?jīng)設(shè)置好了慢SQL時(shí)間為3秒，所以，我們只要執(zhí)行一條SQL時(shí)間超過3秒即可。

SELECT SLEEP(4);

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

使用命令：

show global status like '%Slow_queries%';

查詢SQL歷程

找到慢SQL日志文件，打開后就會(huì)出現(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';

簡(jiǎn)單說明：

1.Time 該日志記錄的時(shí)間

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

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

4.SET timestamp 這一個(gè)是MySQL查詢的時(shí)間

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

切記

如果你將long_query_time=0 ，那就意味著，我們所有的查詢SQL語句都會(huì)輸出到慢SQL日誌檔。

如何定位慢SQL？

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

第一種：定位慢查詢SQL可以透過兩個(gè)表象進(jìn)行判斷

• 系統(tǒng)級(jí)表象:
• #使用sar##指令和top指令查看目前系統(tǒng)的狀態(tài)
• 也可以使用Prometheus和Grafana監(jiān)控工具來檢視目前系統(tǒng)狀態(tài)
• CPU消耗嚴(yán)重
• #IO等待嚴(yán)重
• ##IO
#頁面回應(yīng)時(shí)間過長(zhǎng)
• 專案日誌出現(xiàn)逾時(shí)等錯(cuò)誤
• SQL語句表象:
• SQL
  語句冗長(zhǎng)
• SQL##語句執(zhí)行時(shí)間過長(zhǎng)
• SQL從全表掃描中取得資料
#執(zhí)行計(jì)劃中的
rows

cost很大

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

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

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

mysqldumpslow能將相同的慢SQL歸類，并統(tǒng)計(jì)出相同的SQL執(zhí)行的次數(shù)，每次執(zhí)行耗時(shí)多久、總耗時(shí)，每次返回的行數(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ù)有這么幾個(gè)：

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

使用方式

mysqldumpslow常用的使用方式如下：

# mysqldumpslow -s c slow.log

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

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

其他常用方式：

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

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

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

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

接下，我們來個(gè)實(shí)際操作。

實(shí)操

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語句因?yàn)樯婕澳承┬畔?，所以我都?號(hào)將主體替換了，如果希望得到具體的值，使用-a參數(shù)。

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

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

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

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

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