# Regarding slow SQL, I chatted with the interviewer for a long time. The interviewer was also very humble and always nodded. I thought my answer was okay. Finally, I said, "Go back and wait for the notification!".

#So, I decided to share this slow SQL technology with you. I hope that next time you encounter a similar interview, you can get the offer you want smoothly and easily.

The greatest joy in life is that everyone says you can’t do it, but you complete it!

What is Slow SQL?

MySQL's slow query log is a log record provided by MySQL. It is used to record statements in MySQL whose query time exceeds (greater than) the set threshold (long_query_time) and records them in the slow query log. middle.

Among them, the default value of long_query_time is 10, and the unit is seconds. That is to say, by default, if your SQL query time exceeds 10 seconds, it is regarded as slow SQL.

How to enable slow SQL log?

In MySQL, the slow SQL log is not turned on by default, which means that even if a slow SQL occurs, it will not tell you. If you need to know which SQL is Slow SQL requires us to manually enable the slow SQL log.

Regarding whether slow SQL is enabled, we can check it through the following command:

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

Through the command, we can see that the slow_query_log item is OFF, indicating that our slow SQL log is not turned on. In addition, we can also see the directory where our slow SQL logs are stored and the name of the log file.

Let's enable the slow SQL log and execute the following command:

set global slow_query_log = 1;

It should be noted here that what is enabled here is our current database, and it will be invalid after we restart the database.

After turning on the slow SQL log, check again:

slow_query_log item has become ON means the activation is successful.

As mentioned above, the default time of slow SQL is 10 seconds. We can see the default time of our slow SQL through the following command:

show variables like '%long_query_time%';

We cannot always use this default value. Many businesses may require shorter or longer time, so at this time, we need to modify the default time. The modification command is as follows:

set long_query_time = 3;

The modification is completed, let’s see if it has been changed to 3 seconds.

Note here: If you want it to take effect permanently, you also need to modify the configuration file my under MySQL. cnf file.

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

Note: Different operating systems have different configurations.

In Linux operating system

Add

log-slow-queries=/var/lib/mysql/slowquery.log in the mysql configuration file my.cnf (Specify the log file storage location, which can be empty. The system will give a default file host_name-slow.log)

long_query_time=2 (record the time exceeded, the default is 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日志文件，打開后就會出現(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 該日志記錄的時(shí)間

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

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

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

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

切記

If you set long_query_time=0, it means that all our query SQL statements will be output to the slow SQL log file.

How to locate slow SQL?

Usually we have two ways to locate slow SQL:

The first way: locate slow querySQL can be through two representations Determine

• System-level symptoms:
• Use the sar command and topCommand to view the current system status
• You can also use Prometheus and Grafanamonitoring tools to view the current system status
• CPUConsumption is serious
• IOWaiting is serious
• The page response time is too long
• There are timeout and other errors in the project log
• SQL statement representation:
• SQLLong statement
• SQLThe statement execution time is too long
• SQLGet data from full table scan
• rows and ## in the execution plan #costVery big

SQL

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

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

如果開啟了慢SQL日志后，可能會有大量的慢SQL日志產(chǎn)生，此時(shí)再用肉眼看，那是不太現(xiàn)實(shí)的，所以大佬們就給我搞了個工具：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ù)有這么幾個：

-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)會將數(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最簡單的一種形式，其中-s參數(shù)是以什么方式排序的意思，c指代的是以總數(shù)從大到小的方式排序。-s的常用子參數(shù)有：c: 相同查詢以查詢條數(shù)和從大到小排序。t: 以查詢總時(shí)間的方式從大到小排序。l: 以查詢鎖的總時(shí)間的方式從大到小排序。at: 以查詢平均時(shí)間的方式從大到小排序。al: 以查詢鎖平均時(shí)間的方式從大到小排序。

同樣的，還可以增加其他參數(shù)，實(shí)際使用的時(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

# 得到按照時(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

接下，我們來個實(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)樯婕澳承┬畔ⅲ晕叶加?號將主體替換了，如果希望得到具體的值，使用-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相同，第一個表示平均鎖定時(shí)間(-s al)，括號內(nèi)的表示總的鎖定時(shí)間(-s l)（也有另一種說法，說是表示的等待鎖釋放的時(shí)間）；

Rows=90884.0 (42170176): The first value indicates the average number of rows scanned (-s ar), and the value in brackets indicates the total number of rows scanned (-s r).