什么是慢查詢?nèi)罩?/h4>

MySQL 的慢查詢?nèi)罩?，用來記錄?MySQL 中響應(yīng)時(shí)間超過閥值的語句，具體指運(yùn)行時(shí)間超過 long_query_time? 值的SQL，則會(huì)被記錄到慢查詢?nèi)罩局小ong_query_time 的默認(rèn)值為10，意思是運(yùn)行10秒以上(不含10秒)的語句，認(rèn)為是超出了我們的最大忍耐時(shí)間值。

它的主要作用是，幫助我們發(fā)現(xiàn)那些執(zhí)行時(shí)間特別長的SQL查詢，并且有針對(duì)性地進(jìn)行優(yōu)化，從而提高系統(tǒng)的整體效率。當(dāng)我們的數(shù)據(jù)庫服務(wù)器發(fā)生阻塞、運(yùn)行變慢的時(shí)候，檢查一下慢查詢?nèi)罩?，找到那些慢查詢，?duì)解決問題很有幫助。比如一條sq|執(zhí)行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結(jié)合explain進(jìn)行全面分析。

默認(rèn)情況下，MySQL數(shù)據(jù)庫沒有開啟慢查詢?nèi)罩?，需要我們手?dòng)來設(shè)置這個(gè)參數(shù)。如果不是調(diào)優(yōu)需要的話，一般不建議啟動(dòng)該參數(shù)，因?yàn)殚_啟慢查詢?nèi)罩緯?huì)或多或少帶來一定的性能影響

慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?/p>

如何開啟慢查詢?nèi)罩?/h4>

開啟slow_query_log

mysql> show variables like '%slow_query_log%';
+-----------------------------------+--------------------------------+
| Variable_name                     | Value                          |
+-----------------------------------+--------------------------------+
| slow_query_log                    | OFF                            |
| slow_query_log_always_write_time  | 10.000000                      |
| slow_query_log_file               | /var/lib/mysql/KAiTO-slow.log  |
| slow_query_log_use_global_control |                                |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)

# 開啟慢查詢
mysql > set global slow_query_log='ON';
Query OK, 0 rows affected (0.12 sec)

然后我們?cè)賮聿榭聪侣樵內(nèi)罩臼欠耖_啟，以及慢查詢?nèi)罩疚募奈恢茫?/p>

mysql> show variables like '%slow_query_log%';
+-----------------------------------+--------------------------------+
| Variable_name                     | Value                          |
+-----------------------------------+--------------------------------+
| slow_query_log                    | ON                             |
| slow_query_log_always_write_time  | 10.000000                      |
| slow_query_log_file               | /var/lib/mysql/KAiTO-slow.log  |
| slow_query_log_use_global_control |                                |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)

你能看到這時(shí)慢查詢分析已經(jīng)開啟，同時(shí)文件保存在 /var/lib/mysql/KAiTO-slow.log 文件中。

修改long_query_time閾值

接下來我們來看下慢查詢的時(shí)間閾值設(shè)置，使用如下命令：

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

意思就是超過10秒的SQL語句就會(huì)被記錄慢查詢?nèi)罩局校且绾涡薷倪@個(gè)閾值呢？

mysql> set global long_query_time = 1;
mysql> show global variables like '%long-query_time%';

或修改 my.cnf 文件，[mysqld]下增加或修改參數(shù)long_query_time、slow_query_log和slow_query_log_file后，然后重啟MySQL服務(wù)器。

[mysqld]
slow_query_log=ON #開啟慢查詢?nèi)罩镜拈_關(guān)
slow_query_log_file=/var/lib/mysql/my-slow.log #慢查詢?nèi)罩镜哪夸浐臀募畔?br>long_query_time=3 #設(shè)置慢查詢的閾值為3秒，超出此設(shè)定值的SQL即被記錄到慢查詢?nèi)罩?br>log_output=FILE # 一般有兩種形式，一種是輸出到文件FILE中，一種是寫入數(shù)據(jù)表格table中，會(huì)保存到mysql庫的slow_log表中

如果不指定存儲(chǔ)路徑，慢查詢?nèi)罩緦⒛J(rèn)存儲(chǔ)到 MySQL 數(shù)據(jù)庫的數(shù)據(jù)文件夾下。如果不指定文件名，默認(rèn)文件名為hostname-slow.log。

補(bǔ)充

• min_examined_row_limit

除了上述變量，控制慢查詢?nèi)罩镜倪€有一個(gè)系統(tǒng)變量: min_examined_row_limit。這個(gè)變量的意思是，查詢掃描過的最少記錄數(shù)。這個(gè)變量和查詢執(zhí)行時(shí)間，共同組成了判別一個(gè)查詢是否是慢查詢的條件。如果查詢掃描過的記錄數(shù)大于等于這個(gè)變量的值，并且查詢執(zhí)行時(shí)間超過?long_query_time的值，那么，這個(gè)查詢就被記錄到慢查詢?nèi)罩局? 反之，則不被記錄到慢查詢?nèi)罩局小?/p>

mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

你也可以根據(jù)需要，通過修改 my.cnf 文件，來修改min_examined_row_limit的值。

除了記錄普通的慢查詢之外，MySQL 還提供了兩個(gè)參數(shù)來讓我們記錄未使用索引的查詢，它們分別是：log-queries-not-using-indexes? 和 log_throttle_queries_not_using_indexes

• log-queries-not-using-indexes

系統(tǒng)變量log-queries-not-using-indexes作用是未使用索引的查詢也被記錄到慢查詢?nèi)罩局小?/p>

• log_throttle_queries_not_using_indexes

可通過設(shè)置 log_throttle_queries_not_using_indexes? 來限制每分鐘寫入慢日志中的不走索引的SQL語句個(gè)數(shù)，該參數(shù)默認(rèn)為 0，表示不開啟，也就是說不對(duì)寫入SQL語句條數(shù)進(jìn)行控制。

在生產(chǎn)環(huán)境下，如果沒有使用索引，那么此類 SQL 語句會(huì)頻繁地被記錄到 slow log，從而導(dǎo)致 slow log 文件大小不斷增加，我們可以通過調(diào)整此參數(shù)進(jìn)行配置。

• log_slow_extra

如果啟用 log_slow_extra 系統(tǒng)變量（從 MySQL 8.0.14 開始提供），服務(wù)器會(huì)在日志寫入幾個(gè)額外字段。若要記錄bytes_received? 與 bytes_sent這兩個(gè)字段則需要開啟

• percona slow log

GreatSQL是源于Percona Server的分支版本，除了Percona Server已有的穩(wěn)定可靠、高效、管理更方便等優(yōu)勢(shì)外，特別是進(jìn)一步提升了MGR(MySQL Group Replication)的性能及可靠性，以及眾多bug修復(fù)。這就是為什么在使用GreatSQL查看慢查詢?nèi)罩緯r(shí)，會(huì)有Query_time、Lock_time等信息，這些都是我們GreatSQL源于Percona Server的原因，使查詢內(nèi)容更加豐富，更多的數(shù)據(jù)可以使得我們更好的排查錯(cuò)誤。

通過一個(gè)簡(jiǎn)單的案例來展示：我們先把慢查詢?nèi)罩敬蜷_且設(shè)置時(shí)間閾值大于1秒就記錄：

#開啟慢查詢?nèi)罩?br>mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
#時(shí)間閾值超過1秒就記錄
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
#查看已經(jīng)被記錄的慢查詢數(shù)量
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.01 sec)

寫一條SQL語句使得使用時(shí)間大于1秒

mysql> SELECT * FROM `student` WHERE id>1000 AND `name`='Yunxi';
+---------+-------+-------+------+---------+
| 9999715 |   707 | Yunxi |  863 |      71 |
.......省略
| 9999999 |   418 | Yunxi |  793 |     734 |
+---------+-------+-------+------+---------+
166949 rows in set (3.94 sec)
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 4     |
+---------------+-------+
1 row in set (0.00 sec)

可以看到此條SQL已經(jīng)被記錄，接下來我們?nèi)ゲ榭绰樵內(nèi)罩荆?/p>

# Time: 2022-12-14T15:01:34.892085Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 3.985637  Lock_time: 0.000138 Rows_sent: 165346  Rows_examined: 9900000 Thread_id: 8 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 4848540 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9900000 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-12-14T15:01:30.906448Z End: 2022-12-14T15:01:34.892085Z Schema: slow Rows_affected: 0
# Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 0
# Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 27606  InnoDB_IO_r_bytes: 452296704  InnoDB_IO_r_wait: 0.220474
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 8191
use slow;
SET timestamp=1671030090;
SELECT * FROM `student` WHERE id>100000 AND `name`='Yunxi';

可以看到慢查詢?nèi)罩居涗浀姆浅Ｔ敿?xì)，從上述日志中能看到幾個(gè)信息：

1.這個(gè)SQL的耗時(shí)3.985637秒。

2.返回結(jié)果有165346行，總共需要掃描9900000行數(shù)據(jù)。如果掃描行數(shù)很多，但返回行數(shù)很少，說明該SQL效率很低，可能索引不當(dāng)。

3.Read_* 等幾個(gè)指標(biāo)表示這個(gè)SQL讀記錄的方式，是否順序讀、隨機(jī)讀等。

4.Sort_* 等幾個(gè)指標(biāo)表示該SQL是否產(chǎn)生了排序，及其代價(jià)。如果有且代價(jià)較大，需要想辦法優(yōu)化。

5.tmp 等幾個(gè)指標(biāo)表示該SQL是否產(chǎn)生臨時(shí)表，及其代價(jià)。如果有且代價(jià)較大，需要想辦法優(yōu)化。

6.Full_scan/Full_join表示是否產(chǎn)生了全表掃描或全表JOIN，如果有且SQL耗時(shí)較大，需要想辦法優(yōu)化。

7.InnoDB_IO_* 等幾個(gè)指標(biāo)表示InnoDB邏輯讀相關(guān)數(shù)據(jù)。

8.InnoDB_rec_lock_wait 表示是否有行鎖等待。

9.InnoDB_queue_wait 表示是否有排隊(duì)等待。

10.InnoDB_pages_distinct 表示該SQL總共讀取了多少個(gè)InnoDB page，是個(gè)非常重要的指標(biāo)。

GreatSQL可以作為MySQL或Percona Server的可選替代方案，用于線上生產(chǎn)環(huán)境。完全免費(fèi)并兼容MySQL或Percona Server。綜上，如果在生產(chǎn)環(huán)境中已經(jīng)用上Percona Server的話，那么也可以放心使用GreatSQL。詳情可見：（https://greatsql.cn/doc/#!&v=47_6_0）了解更多GreatSQL內(nèi)容

查看慢查詢數(shù)目

查詢當(dāng)前系統(tǒng)中有多少條慢查詢記錄

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

慢查詢?nèi)罩痉治龉ぞ?/strong>

在生產(chǎn)環(huán)境中，如果要手工分析日志，查找、分析SQL，顯然是個(gè)體力活，MySQL提供了日志分析工具 mysqldumpslow? ，或者是可以使用另一個(gè)工具pt-query-digest。它可以從?logs、processlist?、和 tcpdump? 來分析 MySQL 的狀況，logs包括 slow log、general log、binlog。也可以把分析結(jié)果輸出到文件中，或則把文件寫到表中。分析過程是先對(duì)查詢語句的條件進(jìn)行參數(shù)化，然后對(duì)參數(shù)化以后的查詢進(jìn)行分組統(tǒng)計(jì)，統(tǒng)計(jì)出各查詢的執(zhí)行時(shí)間、次數(shù)、占比等，可以借助分析結(jié)果找出問題進(jìn)行優(yōu)化。

關(guān)閉慢查詢?nèi)罩?/strong>

作者建議除了調(diào)優(yōu)需要開，正常還是不要開了

MySQL服務(wù)器停止慢查詢?nèi)罩竟δ艿姆椒ǎ?/p>

• 方式1

[mysqld]
slow_query_log=OFF

• 方式2

SET GLOBAL slow_query_log=off;

刪除慢查詢?nèi)罩?/strong>

mysql> show variables like '%slow_query_log%';
+-----------------------------------+--------------------------------+
| Variable_name                     | Value                          |
+-----------------------------------+--------------------------------+
| slow_query_log                    | ON                             |
| slow_query_log_always_write_time  | 10.000000                      |
| slow_query_log_file               | /var/lib/mysql/zhyno1-slow.log |
| slow_query_log_use_global_control |                                |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)

通過以上查詢可以看到慢查詢?nèi)罩镜哪夸?，在該目錄下手?dòng)刪除慢查詢?nèi)罩疚募纯?。或使用命?nbsp;mysqladmin? 來刪除,mysqladmin 命令的語法如下：mysqladmin -uroot -p flush-logs執(zhí)行該命令后，命令行會(huì)提示輸入密碼。輸入正確密碼后，將執(zhí)行刪除操作。新的慢查詢?nèi)罩緯?huì)直接覆蓋舊的查詢?nèi)罩荆恍枰偈謩?dòng)刪除。

注意慢查詢?nèi)罩径际鞘褂?mysqladmin flush-logs命令來刪除重建的。使用時(shí)一定要注意，一旦執(zhí)行了這個(gè)命令，慢查詢?nèi)罩径贾淮嬖谛碌娜罩疚募?，如果需要舊的查詢?nèi)罩荆捅仨毷孪葌浞荨?/p>

參考文章

《MySQL是怎樣運(yùn)行的--從根兒上理解MySQL》—小孩子

4919（https://juejin.cn/book/6844733769996304392）