MySQL慢查詢日志

發布時間：2020-10-08 22:37:26 來源：網絡閱讀：1158 作者：ZeroOne01 欄目：MySQL數據庫

筆記內容：MySQL慢查詢日志

筆記日期：2017-12-10

MySQL日志文件系統的組成
通用查詢日志
慢查詢日志

和大多數關系型數據庫一樣，日志文件是MySQL數據庫的重要組成部分。MySQL有幾種不同的日志文件，通常包括錯誤日志文件，二進制日志，通用日志，慢查詢日志，等等。這些日志可以幫助我們定位mysqld內部發生的事件，數據庫性能故障，記錄數據的變更歷史，用戶恢復數據庫等等。

MySQL日志文件系統的組成

1.錯誤日志：記錄啟動、運行或停止mysqld時出現的問題。

2.通用查詢日志：記錄建立的客戶端連接和執行的語句。

3.更新日志：記錄更改數據的語句。該日志在MySQL 5.1中已不再使用。

4.二進制日志：記錄所有更改數據的語句。還用于主從復制。

5.慢查詢日志：記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢。

6.Innodb日志：innodb redo log

7.中繼日志：從庫從主庫獲取到的要更新的數據的日志。

默認情況下，所有日志創建于mysqld數據目錄中。可以通過刷新日志，來強制mysqld來關閉和重新打開日志文件（或者在某些情況下切換到一個新的日志）。當你執行一個FLUSH LOGS語句或執行mysqladmin flush-logs或mysqladmin refresh時，則日志被老化。對于存在MySQL復制的情形下，從復制服務器將維護更多日志文件，被稱為接替日志。

通用查詢日志

在學習通用日志查詢時，需要知道幾個數據庫中的常用命令：

1.show variables like '%version%';

這個命令，可以顯示當前數據庫中與版本號相關的信息。示例：

mysql> show variables like '%version%';  
+-------------------------+----------------+
| Variable_name           | Value          |
+-------------------------+----------------+
| innodb_version          | 5.7.14         |
| protocol_version        | 10             |
| slave_type_conversions  |                |
| version                 | 10.2.6-MariaDB |
| version_comment         | MariaDB Server |
| version_compile_machine | x86_64         |
| version_compile_os      | Linux          |
| version_malloc_library  | system         |
| version_ssl_library     | YaSSL 2.4.2    |
| wsrep_patch_version     | wsrep_25.19    |
+-------------------------+----------------+
10 rows in set (0.01 sec)

mysql>

以下這個命令是用于查看當前的通用日志查詢是否開啟，如果general_log的值為ON則為開啟，為OFF則為關閉（默認情況下是關閉的）。

2.show variables like '%general%';

示例：

mysql> show variables like '%general%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | OFF        |
| general_log_file | server.log |
+------------------+------------+
2 rows in set (0.00 sec)

mysql>

以下這個命令是用于查看當前通用查詢日志輸出的格式，log_output的值可以是FILE（存儲在數數據庫的數據文件中的hostname.log），也可以是TABLE（存儲在數據庫中的mysql.general_log）。

3.show variables like '%log_output%';

示例：

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

mysql>

如何開啟MySQL通用查詢日志，以及使用何種方式去記錄這些通用查詢日志？

開啟通用日志查詢： set global general_log=on;
關閉通用日志查詢： set global general_log=off;
設置將通用日志記錄到數據庫表格中： set globallog_output='TABLE';
設置將通用日志記錄到本地文件中： set globallog_output='FILE';
設置將通用日志記錄到數據庫表和本地文件中：set global log_output='FILE,TABLE';

示例：

mysql> set global general_log=on;
Query OK, 0 rows affected (0.01 sec)

mysql> set global log_output='FILE,TABLE';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                        |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2017-12-10 17:47:35.177238 | root[root] @ localhost [] |       189 |         1 | Query        | select * from mysql.general_log |
| 2017-12-10 17:47:52.608628 | root[root] @ localhost [] |       189 |         1 | Query        | select * from mysql.general_log |
| 2017-12-10 17:47:55.138903 | root[root] @ localhost [] |       189 |         1 | Query        | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
3 rows in set (0.00 sec)

mysql>

記錄到mysql.general_log表中的數據如下：
MySQL慢查詢日志

默認情況下記錄在本地文件中的通用查詢日志文件名稱的前綴為主機名，后綴為.log，可以使用find命令尋找，示例：

[root@server ~]# find / -name "server.log"
/data/mariadb/server.log
[root@server ~]# tail /data/mariadb/server.log
Time                 Id Command    Argument
171210 17:46:39   189 Query set global log_output='FILE,TABLE'
171210 17:47:35   189 Query select * from mysql.general_log
171210 17:47:52   189 Query select * from mysql.general_log
171210 17:47:55   189 Query select * from mysql.general_log
171210 17:50:57   189 Quit  
171210 17:52:01   190 Connect   root@localhost as anonymous on 
          190 Query select @@version_comment limit 1
171210 17:52:09   190 Query select * from mysql.general_log
171210 17:52:59   190 Quit  
[root@server ~]#

注意：上述命令只是臨時生效，當MySQL重啟后則會失效，如果要永久生效，需要配置my.cnf文件

my.cnf文件需要配置的內容如下：

general_log=1   #為1表示開啟通用日志查詢，值為0表示關閉通用日志查詢
log_output=FILE,TABLE   #設置通用日志的輸出格式為文件和表

如果沒有開啟通用日志查詢的話，general_log表是空的：

mysql> select * from mysql.general_log;
Empty set (0.00 sec)

mysql>

慢查詢日志

MySQL的慢查詢日志是MySQL提供的一種日志記錄，用來記錄在MySQL中響應時間超過閾值的語句，具體指運行時間超過long_query_time值的SQL語句，就會被記錄到慢查詢日志中（日志可以寫入文件或者數據庫表，如果對性能要求高的話，建議寫文件）。所以慢查詢日志就是記錄mysql服務器中影響數據庫性能的相關SQL語句的，通過對這些特殊的SQL語句分析，改進以達到提高數據庫性能的目的。默認情況下，MySQL數據庫是不開啟慢查詢日志的，long_query_time的默認值為10（即10秒，通常設置為1秒），即運行10秒以上的語句是慢查詢語句。
一般情況下，慢查詢發生在數據庫比較大的表格中（比如：一個表的數據量有幾百萬），且查詢條件的字段沒有建立索引，此時，要匹配查詢條件的字段會進行全表掃描，查詢耗時超過long_query_time所定義的閾值（預設值），則為慢查詢語句，這些慢查詢語句就會記錄到慢查詢日志中。

使用以下命令可以查看當前慢查詢日志的開啟情況：

show variables like '%quer%';

示例：

mysql> show variables like '%quer%';
+---------------------------------+-----------------+
| Variable_name                   | Value           |
+---------------------------------+-----------------+
| expensive_subquery_limit        | 100             |
| ft_query_expansion_limit        | 20              |
| have_query_cache                | YES             |
| log_queries_not_using_indexes   | OFF             |
| long_query_time                 | 10.000000       |
| query_alloc_block_size          | 16384           |
| query_cache_limit               | 1048576         |
| query_cache_min_res_unit        | 4096            |
| query_cache_size                | 1048576         |
| query_cache_strip_comments      | OFF             |
| query_cache_type                | OFF             |
| query_cache_wlock_invalidate    | OFF             |
| query_prealloc_size             | 24576           |
| slow_query_log                  | OFF             |
| slow_query_log_file             | server-slow.log |
| wsrep_sst_donor_rejects_queries | OFF             |
+---------------------------------+-----------------+
16 rows in set (0.00 sec)

mysql>

需要關注以下的幾個參數：
1.slow_query_log： 的值為ON為開啟慢查詢日志，OFF則為關閉慢查詢日志。

2.slow_query_log_file： 的值是記錄的慢查詢日志到文件中（注意：默認名為主機名.log，慢查詢日志是否寫入指定文件中，需要指定慢查詢的輸出日志格式為文件，相關命令為：show variables like ‘%log_output%’；去查看輸出的格式）。

3.long_query_time： 指定了慢查詢的閾值，即如果執行語句的時間超過該閾值則為慢查詢語句，默認值為10秒。

4.log_queries_not_using_indexes： 如果值設置為ON，則會記錄所有沒有利用索引的查詢（注意：如果只是將log_queries_not_using_indexes設置為ON，而將slow_query_log設置為OFF，此時該設置也不會生效，即該設置生效的前提是slow_query_log的值設置為ON），一般在性能調優的時候會暫時開啟。

5.min_examined_row_limit： 查詢檢查返回少于該參數指定行的SQL不被記錄到慢查詢日志

6.log_slow_queries： 指定是否開啟慢查詢日志(該參數要被slow_query_log取代，做兼容性保留)

和通用查詢日志一樣，慢查詢日志也是使用 show variables like '%log_output%'; 語句來查看日志的記錄方式：

mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

mysql>

設置記錄日志的方式也是和之前的一樣：

設置將通用日志記錄到數據庫表格中： set globallog_output='TABLE';
設置將通用日志記錄到本地文件中： set globallog_output='FILE';
設置將通用日志記錄到數據庫表和本地文件中：set global log_output='FILE,TABLE';

開啟慢查詢日志：

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=1;  # 將閥值設置為1秒
Query OK, 0 rows affected (0.00 sec)

mysql> set session long_query_time=1; # 將session級別的閥值也設置為1秒
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';  # 查看閥值，默認為10秒
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%slow_query_log%';  # 查看慢查詢日志狀態，ON為開啟狀態，默認為OFF
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log      | ON              |
| slow_query_log_file | server-slow.log |
+---------------------+-----------------+
2 rows in set (0.01 sec)

mysql> show global status like '%slow%';  # 查看慢查詢的記錄數量
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
2 rows in set (0.06 sec)

mysql> show variables like 'log_queries_not_using_indexes';   # 查看log_queries_not_using_indexes狀態
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql>

如果出現修改之后依舊顯示為默認值的情況，重新登錄mysql就好了，或者在show后面加上global關鍵字也可以。

關于慢查詢日志的表中的數據個文本中的數據格式分析：

慢查詢的日志記錄myql.slow_log表中，格式如下：

MySQL慢查詢日志

查詢語句：

select * from mysql.slow_log;

此時再查看一下慢查詢的記錄數量：

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 374   |
+---------------------+-------+
2 rows in set (0.00 sec)

mysql>

慢查詢日志和通用查詢日志都是使用的同一個本地文件：

[root@server ~]# find / -name 'server.log'
/data/mariadb/server.log
[root@server ~]# tail /data/mariadb/server.log
          197 Field List    INNODB_SYS_INDEXES 
          197 Field List    INNODB_SYS_VIRTUAL 
          197 Field List    INNODB_MUTEXES 
          197 Field List    INNODB_SYS_SEMAPHORE_WAITS 
171210 21:43:19   197 Query show tables
171210 21:43:36   197 Query select * from ALL_PLUGINS
171210 21:44:09   197 Query select * from myql.slow_log
171210 21:44:15   197 Query select * from mysql.slow_log
171210 21:44:23   197 Query select * from mysql.slow_log
171210 22:00:12   197 Quit  
[root@server ~]#

可以看到，不管是表還是文件，都具體記錄了：是哪條sql語句導致慢查詢（sql_text），該慢查詢語句的查詢時間（query_time），鎖表時間（Lock_time），以及掃描過的行數（rows_examined）等信息。

如何手動產生慢查詢語句

實際在學習過程中，如何得知設置的慢查詢是有效的？很簡單，我們可以手動產生一條慢查詢語句，比如，如果我們的慢查詢log_query_time的值設置為1，則執行以下這條語句就可以了：

selectsleep(1);

該條語句即是慢查詢語句，之后，便可以在相應的日志輸出文件或表中去查看是否有該條語句。

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

MySQL慢查詢日志

MySQL日志文件系統的組成

通用查詢日志

如何開啟MySQL通用查詢日志，以及使用何種方式去記錄這些通用查詢日志？

慢查詢日志

如何手動產生慢查詢語句

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

MySQL慢查詢日志

MySQL日志文件系統的組成

通用查詢日志

如何開啟MySQL通用查詢日志，以及使用何種方式去記錄這些通用查詢日志？

慢查詢日志

如何手動產生慢查詢語句

猜你喜歡

最新資訊

相關推薦

相關標簽