溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
筆記內(nèi)容：MySQL慢查詢?nèi)罩?/strong>
筆記日期：2017-12-10
<br>
和大多數(shù)關(guān)系型數(shù)據(jù)庫一樣，日志文件是MySQL數(shù)據(jù)庫的重要組成部分。MySQL有幾種不同的日志文件，通常包括錯(cuò)誤日志文件，二進(jìn)制日志，通用日志，慢查詢?nèi)罩荆鹊?。這些日志可以幫助我們定位mysqld內(nèi)部發(fā)生的事件，數(shù)據(jù)庫性能故障，記錄數(shù)據(jù)的變更歷史，用戶恢復(fù)數(shù)據(jù)庫等等。
<br>
1.錯(cuò)誤日志：記錄啟動(dòng)、運(yùn)行或停止mysqld時(shí)出現(xiàn)的問題。
2.通用查詢?nèi)罩荆河涗浗⒌目蛻舳诉B接和執(zhí)行的語句。
3.更新日志：記錄更改數(shù)據(jù)的語句。該日志在MySQL 5.1中已不再使用。
4.二進(jìn)制日志：記錄所有更改數(shù)據(jù)的語句。還用于主從復(fù)制。
5.慢查詢?nèi)罩荆河涗浰袌?zhí)行時(shí)間超過long_query_time秒的所有查詢或不使用索引的查詢。
6.Innodb日志：innodb redo log
7.中繼日志：從庫從主庫獲取到的要更新的數(shù)據(jù)的日志。
默認(rèn)情況下，所有日志創(chuàng)建于mysqld數(shù)據(jù)目錄中。 可以通過刷新日志，來強(qiáng)制mysqld來關(guān)閉和重新打開日志文件（或者在某些情況下切換到一個(gè)新的日志）。當(dāng)你執(zhí)行一個(gè)FLUSH LOGS語句或執(zhí)行mysqladmin flush-logs或mysqladmin refresh時(shí)，則日志被老化。對(duì)于存在MySQL復(fù)制的情形下，從復(fù)制服務(wù)器將維護(hù)更多日志文件，被稱為接替日志。
<br>
在學(xué)習(xí)通用日志查詢時(shí)，需要知道幾個(gè)數(shù)據(jù)庫中的常用命令：
1.show variables like '%version%';
這個(gè)命令，可以顯示當(dāng)前數(shù)據(jù)庫中與版本號(hào)相關(guān)的信息。示例：
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>以下這個(gè)命令是用于查看當(dāng)前的通用日志查詢是否開啟，如果general_log的值為ON則為開啟，為OFF則為關(guān)閉（默認(rèn)情況下是關(guān)閉的）。
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>以下這個(gè)命令是用于查看當(dāng)前通用查詢?nèi)罩据敵龅母袷?，log_output的值可以是FILE（存儲(chǔ)在數(shù)數(shù)據(jù)庫的數(shù)據(jù)文件中的hostname.log），也可以是TABLE（存儲(chǔ)在數(shù)據(jù)庫中的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> <br>
開啟通用日志查詢： set global general_log=on;
關(guān)閉通用日志查詢： set global general_log=off;
設(shè)置將通用日志記錄到數(shù)據(jù)庫表格中： set globallog_output='TABLE';
設(shè)置將通用日志記錄到本地文件中： set globallog_output='FILE';
示例：
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表中的數(shù)據(jù)如下：
[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 ~]#
注意：上述命令只是臨時(shí)生效，當(dāng)MySQL重啟后則會(huì)失效，如果要永久生效，需要配置my.cnf文件
my.cnf文件需要配置的內(nèi)容如下：
general_log=1 #為1表示開啟通用日志查詢，值為0表示關(guān)閉通用日志查詢
log_output=FILE,TABLE #設(shè)置通用日志的輸出格式為文件和表如果沒有開啟通用日志查詢的話，general_log表是空的：
mysql> select * from mysql.general_log;
Empty set (0.00 sec)
mysql><br>
MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，用來記錄在MySQL中響應(yīng)時(shí)間超過閾值的語句，具體指運(yùn)行時(shí)間超過long_query_time值的SQL語句，就會(huì)被記錄到慢查詢?nèi)罩局校ㄈ罩究梢詫懭胛募蛘邤?shù)據(jù)庫表，如果對(duì)性能要求高的話，建議寫文件）。所以慢查詢?nèi)罩揪褪怯涗沵ysql服務(wù)器中影響數(shù)據(jù)庫性能的相關(guān)SQL語句的，通過對(duì)這些特殊的SQL語句分析，改進(jìn)以達(dá)到提高數(shù)據(jù)庫性能的目的。默認(rèn)情況下，MySQL數(shù)據(jù)庫是不開啟慢查詢?nèi)罩镜?，long_query_time的默認(rèn)值為10（即10秒，通常設(shè)置為1秒），即運(yùn)行10秒以上的語句是慢查詢語句。
一般情況下，慢查詢發(fā)生在數(shù)據(jù)庫比較大的表格中（比如：一個(gè)表的數(shù)據(jù)量有幾百萬），且查詢條件的字段沒有建立索引，此時(shí)，要匹配查詢條件的字段會(huì)進(jìn)行全表掃描，查詢耗時(shí)超過long_query_time所定義的閾值（預(yù)設(shè)值），則為慢查詢語句，這些慢查詢語句就會(huì)記錄到慢查詢?nèi)罩局小?/p>
使用以下命令可以查看當(dāng)前慢查詢?nèi)罩镜拈_啟情況：
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> 需要關(guān)注以下的幾個(gè)參數(shù)：
1.slow_query_log： 的值為ON為開啟慢查詢?nèi)罩荆琌FF則為關(guān)閉慢查詢?nèi)罩尽?/p>
2.slow_query_log_file： 的值是記錄的慢查詢?nèi)罩镜轿募校ㄗ⒁猓耗J(rèn)名為主機(jī)名.log，慢查詢?nèi)罩臼欠駥懭胫付ㄎ募?，需要指定慢查詢的輸出日志格式為文件，相關(guān)命令為：show variables like ‘%log_output%’；去查看輸出的格式）。
3.long_query_time： 指定了慢查詢的閾值，即如果執(zhí)行語句的時(shí)間超過該閾值則為慢查詢語句，默認(rèn)值為10秒。
4.log_queries_not_using_indexes： 如果值設(shè)置為ON，則會(huì)記錄所有沒有利用索引的查詢（注意：如果只是將log_queries_not_using_indexes設(shè)置為ON，而將slow_query_log設(shè)置為OFF，此時(shí)該設(shè)置也不會(huì)生效，即該設(shè)置生效的前提是slow_query_log的值設(shè)置為ON），一般在性能調(diào)優(yōu)的時(shí)候會(huì)暫時(shí)開啟。
5.min_examined_row_limit： 查詢檢查返回少于該參數(shù)指定行的SQL不被記錄到慢查詢?nèi)罩?
6.log_slow_queries： 指定是否開啟慢查詢?nèi)罩?該參數(shù)要被slow_query_log取代，做兼容性保留)
<br>
和通用查詢?nèi)罩疽粯?，慢查詢?nèi)罩疽彩鞘褂?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><br>
設(shè)置記錄日志的方式也是和之前的一樣：
設(shè)置將通用日志記錄到數(shù)據(jù)庫表格中： set globallog_output='TABLE';
設(shè)置將通用日志記錄到本地文件中： set globallog_output='FILE';
<br>
開啟慢查詢?nèi)罩荆?/strong>
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=1; # 將閥值設(shè)置為1秒
Query OK, 0 rows affected (0.00 sec)
mysql> set session long_query_time=1; # 將session級(jí)別的閥值也設(shè)置為1秒
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%long_query_time%'; # 查看閥值，默認(rèn)為10秒
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> show variables like '%slow_query_log%'; # 查看慢查詢?nèi)罩緺顟B(tài)，ON為開啟狀態(tài)，默認(rèn)為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%'; # 查看慢查詢的記錄數(shù)量
+---------------------+-------+
| 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狀態(tài)
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> 如果出現(xiàn)修改之后依舊顯示為默認(rèn)值的情況，重新登錄mysql就好了，或者在show后面加上global關(guān)鍵字也可以。
<br>
關(guān)于慢查詢?nèi)罩镜谋碇械臄?shù)據(jù)個(gè)文本中的數(shù)據(jù)格式分析：
慢查詢的日志記錄myql.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>
慢查詢?nèi)罩竞屯ㄓ貌樵內(nèi)罩径际鞘褂玫耐粋€(gè)本地文件：
[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語句導(dǎo)致慢查詢（sql_text），該慢查詢語句的查詢時(shí)間（query_time），鎖表時(shí)間（Lock_time），以及掃描過的行數(shù)（rows_examined）等信息。
<br>
實(shí)際在學(xué)習(xí)過程中，如何得知設(shè)置的慢查詢是有效的？很簡(jiǎn)單，我們可以手動(dòng)產(chǎn)生一條慢查詢語句，比如，如果我們的慢查詢log_query_time的值設(shè)置為1，則執(zhí)行以下這條語句就可以了：
selectsleep(1);
該條語句即是慢查詢語句，之后，便可以在相應(yīng)的日志輸出文件或表中去查看是否有該條語句。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼