MySQL 日志之--慢查詢日志（slow-query-log）

慢查詢日志：

MySQL慢查詢日志記錄下所有執行超過long_query_time時間的SQL語句，幫你找到執行慢的SQL，友善我們對這些SQL進行優化。

慢查詢日志的配置：

預設情況下，mysql沒有啟用慢查詢日志。

[root@rh64 ~]# mysql -u root -p

<code>Enter password: </code>

<code>Welcome to the MySQL monitor. Commands end </code><code>with</code> <code>; </code><code>or</code> <code>\g.</code>

<code>Your MySQL connection id is </code><code>1</code>

<code>Server version: </code><code>5.6.</code><code>25</code><code>-73.1</code> <code>Percona Server (GPL), Release </code><code>73.1</code><code>, Revision 07b797f</code>

<code>Copyright (c) </code><code>2009</code><code>-2015</code> <code>Percona LLC </code><code>and</code><code>/</code><code>or</code> <code>its affiliates</code>

<code>Oracle is a registered trademark of Oracle Corporation </code><code>and</code><code>/</code><code>or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners。</code>

<code>Type </code><code>'help;'</code> <code>or</code> <code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to clear the current input statement.</code>

mysql> show variables like '%slow%';

+------------------------------------+------------------------------+

| Variable_name | Value |

| log_slow_admin_statements | OFF |

| log_slow_filter | |

| log_slow_rate_limit | 1 |

| log_slow_rate_type | session |

| log_slow_slave_statements | OFF |

| log_slow_sp_statements | ON |

| log_slow_verbosity | |

| max_slowlog_files | 0 |

| max_slowlog_size | 0 |

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_always_write_time | 10.000000 |

| slow_query_log_file | /var/lib/mysql/rh64-slow.log |

| slow_query_log_timestamp_always | OFF |

| slow_query_log_timestamp_precision | second |

| slow_query_log_use_global_control | |

16 rows in set (0.01 sec)

1、可以配置my.cnf檔案，服務啟動時自動配置

[root@rh64 ~]# cat /etc/my.cnf

<code>[mysqld]</code>

<code>datadir=/</code><code>var</code><code>/lib/mysql</code>

<code>socket=/</code><code>var</code><code>/lib/mysql/mysql.sock</code>

<code>user=mysql</code>

<code># Disabling symbolic-links is recommended to prevent assorted security risks</code>

<code>symbolic-links=</code><code>0</code>

<code>innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend</code>

<code>sql_mode=STRICT_TRANS_TABLES ,NO_ENGINE_SUBSTITUTION</code>

<code>slow_query_log=</code><code>true</code>

<code>slow_query_log_file = </code><code>"/var/lib/mysql/rh64-slow.log"</code>

<code>long_query_time=</code><code>1</code>

<code>log-queries-</code><code>not</code><code>-using-indexes=</code><code>true</code>

<code>[mysqld_safe]</code>

<code>log-error=/</code><code>var</code><code>/log/mysqld.log</code>

<code>pid-file=/</code><code>var</code><code>/run/mysqld/mysqld.pid</code>

<code>explicit_defaults_for_timestamp=</code><code>true</code>

<code>innodb_buffer_pool_size = 128M</code>

<code>join_buffer_size = 128M</code>

<code>sort_buffer_size = 2M</code>

<code>read_rnd_buffer_size = 2M</code>

重新開機server後，檢視：

<code>| Variable_name | Value |</code>

<code>| log_slow_admin_statements | OFF |</code>

<code>| log_slow_filter | |</code>

<code>| log_slow_rate_limit | </code><code>1</code> <code>|</code>

<code>| log_slow_rate_type | session |</code>

<code>| log_slow_slave_statements | OFF |</code>

<code>| log_slow_sp_statements | ON |</code>

<code>| log_slow_verbosity | |</code>

<code>| max_slowlog_files | </code><code>0</code> <code>|</code>

<code>| max_slowlog_size | </code><code>0</code> <code>|</code>

<code> </code><code>slow_launch_time | </code><code>2</code> <code>|</code>

<code>| slow_query_log | ON |</code>

<code>| slow_query_log_always_write_time | </code><code>10.000000</code> <code>|</code>

<code>| slow_query_log_file | /var/lib/mysql/rh64-slow.log |</code>

<code>| slow_query_log_timestamp_always | OFF |</code>

<code>| slow_query_log_timestamp_precision | second |</code>

<code>| slow_query_log_use_global_control | |</code>

2、在系統中配置slow-query-log

mysql> set @@global.slow_query_log = on;

<code>| slow_launch_time | </code><code>2</code> <code>|</code>

<code>| slow_query_log_file | /</code><code>var</code><code>/lib/mysql/rh64-slow.log |</code>

3、檢視慢查詢日志資訊

[root@rh64 mysql]# tail rh64-slow.log

<code>SET timestamp=</code><code>1449476453</code><code>;</code>

<code>insert</code> <code>into</code> <code>emp1 select * </code><code>from</code> <code>emp1;</code>

<code># User@Host: root[root] @ localhost [] Id: </code><code>2</code>

<code># Schema: prod Last_errno: </code><code>0</code> <code>Killed: </code><code>0</code>

<code># Query_time: </code><code>16.748949</code> <code>Lock_time: </code><code>0.000137</code> <code>Rows_sent: </code><code>0</code> <code>Rows_examined: </code><code>1236992</code> <code>Rows_affected: </code><code>618496</code>

<code># Bytes_sent: </code><code>58</code>

<code>SET timestamp=</code><code>1449476471</code><code>;</code>

<code>[root@rh64 mysql]# tail -f rh64-slow.log </code>

<code># Query_time: </code><code>44.036039</code> <code>Lock_time: </code><code>0.000083</code> <code>Rows_sent: </code><code>0</code> <code>Rows_examined: </code><code>2473984</code> <code>Rows_affected: </code><code>1236992</code>

<code># Bytes_sent: </code><code>59</code>

<code>SET timestamp=</code><code>1449476574</code><code>;</code>

<code># Query_time: </code><code>106.674422</code> <code>Lock_time: </code><code>0.000148</code> <code>Rows_sent: </code><code>0</code> <code>Rows_examined: </code><code>4947968</code> <code>Rows_affected: </code><code>2473984</code>

<code>SET timestamp=</code><code>1449476806</code><code>;</code>

記錄沒有使用索引的語句：

mysql> set @@global.log_queries_not_using_indexes=on;

Query OK, 0 rows affected (0.00 sec)

<code>mysql> show variables like </code><code>'%index%'</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| eq_range_index_dive_limit | </code><code>10</code> <code>|</code>

<code>| expand_fast_index_creation | OFF |</code>

<code>| innodb_adaptive_hash_index | ON |</code>

<code>| innodb_adaptive_hash_index_partitions | </code><code>1</code> <code>|</code>

<code>| innodb_cmp_per_index_enabled | OFF |</code>

<code>| log_bin_index | |</code>

<code>| log_queries_not_using_indexes | ON |</code>

<code>| log_throttle_queries_not_using_indexes | </code><code>0</code> <code>|</code>

<code>| relay_log_index | |</code>

測試：

mysql> select count(*) from emp1 where empno=7788;

+----------+

| count(*) |

| 688128 |

1 row in set (4.03 sec)

<code># Query_time: </code><code>4.025612</code> <code>Lock_time: </code><code>0.000098</code> <code>Rows_sent: </code><code>1</code> <code>Rows_examined: </code><code>4947968</code> <code>Rows_affected: </code><code>0</code>

<code># Bytes_sent: </code><code>68</code>

<code>SET timestamp=</code><code>1449477044</code><code>;</code>

<code>select count(*) </code><code>from</code> <code>emp1 </code><code>where</code> <code>empno=</code><code>7788</code><code>;</code>

4、通過mysqldumpslow工具檢視慢查詢日志

[root@rh64 mysql]# mysqldumpslow

<code>Can</code><code>'t determine basedir from '</code><code>my_print_defaults mysqld' output: --datadir=/</code><code>var</code><code>/lib/mysql</code>

<code>--socket=/</code><code>var</code><code>/lib/mysql/mysql.sock</code>

<code>--user=mysql</code>

<code>--symbolic-links=</code><code>0</code>

<code>--innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend</code>

<code>--sql_mode=STRICT_TRANS_TABLES ,NO_ENGINE_SUBSTITUTION</code>

[root@rh64 mysql]# mysqldumpslow --help

<code>Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]</code>

<code>Parse and summarize the MySQL slow query log. Options are</code>

<code> </code><code>--verbose verbose</code>

<code> </code><code>--debug debug</code>

<code> </code><code>--help write </code><code>this</code> <code>text to standard output</code>

<code> </code><code>-v verbose</code>

<code> </code><code>-d debug</code>

<code> </code><code>-s ORDER what to sort by (al, at, ar, c, l, r, t), </code><code>'at'</code> <code>is </code><code>default</code>

<code> </code><code>al: average lock time</code>

<code> </code><code>ar: average rows sent</code>

<code> </code><code>at: average query time</code>

<code> </code><code>c: count</code>

<code> </code><code>t: query time </code>

<code> </code><code>-r reverse the sort order (largest last instead of first)</code>

<code> </code><code>-t NUM just show the top n queries</code>

<code> </code><code>-a don</code><code>'t abstract all numbers to N and strings to '</code><code>S'</code>

<code> </code><code>-n NUM </code><code>abstract</code> <code>numbers with at least n digits within names</code>

<code> </code><code>-g PATTERN grep: only consider stmts that include </code><code>this</code> <code>string</code>

<code> </code><code>-h HOSTNAME hostname of db server </code><code>for</code> <code>*-slow.log filename (can be wildcard),</code>

<code> </code><code>default</code> <code>is </code><code>'*'</code><code>, i.e. match all</code>

<code> </code><code>-i NAME name of server instance (</code><code>if</code> <code>using mysql.server startup script)</code>

<code> </code><code>-l don't subtract lock time from total time</code>

[root@rh64 mysql]# mysqldumpslow rh64-slow.log

<code>Reading mysql slow query log </code><code>from</code> <code>rh64-slow.log</code>

<code>Count: </code><code>3</code> <code>Time=</code><code>0.</code><code>00s (0s) Lock=</code><code>0.</code><code>00s (0s) Rows=</code><code>0.0</code> <code>(</code><code>0</code><code>), root[root]@localhost</code>

<code> </code><code># Schema: prod Last_errno: N Killed: N</code>

<code> </code><code># Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N</code>

<code> </code><code># Bytes_sent: N</code>

<code> </code><code>SET timestamp=N;</code>

<code> </code><code>insert</code> <code>into</code> <code>emp1 select * </code><code>from</code> <code>emp1</code>

<code>Count: </code><code>1</code> <code>Time=</code><code>0.</code><code>00s (0s) Lock=</code><code>0.</code><code>00s (0s) Rows=</code><code>0.0</code> <code>(</code><code>0</code><code>), root[root]@localhost</code>

<code> </code><code>select count(*) </code><code>from</code> <code>emp1 </code><code>where</code> <code>empno=N</code>

按照平均鎖定時間進行排序，查找前10名：

[root@rh64 mysql]# mysqldumpslow -s al -n 10 rh64-slow.log

Reading mysql slow query log from rh64-slow.log

Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost

# Schema: prod Last_errno: N Killed: N

# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N

# Bytes_sent: N

SET timestamp=N;

insert into emp1 select * from emp1

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost

use prod;

select count(*) from emp1 where empno=N

本文轉自客居天涯 51CTO部落格，原文連結：http://blog.51cto.com/tiany/1720789，如需轉載請自行聯系原作者

MySQL 日志之--慢查詢日志（slow-query-log）

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離