程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中查詢日志與慢查詢日志的基本學習教程,

MySQL中查詢日志與慢查詢日志的基本學習教程,

編輯:MySQL綜合教程

MySQL中查詢日志與慢查詢日志的基本學習教程,


一、查詢日志

  查詢日志記錄MySQL中所有的query,通過"--log[=file_name]"來打開該功能。由於記錄了所有的query,包括所有的select,體積比較大,開啟後對性能也有比較大的影響,所以請大家慎用該功能。一般只用於跟蹤某些特殊的sql性能問題才會短暫打開該功能。默認的查詢日志文件名為:hostname.log. 
----默認情況下查看是否啟用查詢日志:

[root@node4 mysql5.5]# service mysql start
Starting MySQL....                     [ OK ]
[root@node4 mysql5.5]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show variables like '%log';
+--------------------------------+-------+
| Variable_name         | Value |
+--------------------------------+-------+
| back_log            | 50  |
| general_log          | OFF  |
| innodb_locks_unsafe_for_binlog | OFF  |
| log              | OFF  |
| relay_log           |    |
| slow_query_log         | OFF  |
| sync_binlog          | 0   |
| sync_relay_log         | 0   |
+--------------------------------+-------+
8 rows in set (0.00 sec)

----備注:log和general_log這兩個參數是兼容的。而默認的情況下查詢日志是不開啟的
----使用下面的命令是開啟查詢日志
mysql> set global log=1;
 
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show variables like '%log';

+--------------------------------+-------+
| Variable_name         | Value |
+--------------------------------+-------+
| back_log            | 50  |
| general_log          | ON  |
| innodb_locks_unsafe_for_binlog | OFF  |
| log              | ON  |
| relay_log           |    |
| slow_query_log         | OFF  |
| sync_binlog          | 0   |
| sync_relay_log         | 0   |
+--------------------------------+-------+
8 rows in set (0.00 sec) 
----其中log參數是過時的,在啟動選項中使用log參數的話,會在err日志中顯示出來。
----修改my.cnf文件,添加log的參數設置
[root@node4 mysql5.5]# vi my.cnf
[root@node4 mysql5.5]# cat ./my.cnf |grep '^log='
log=/tmp/mysqlgen.log
----清空err日志
[root@node4 mysql5.5]# cat /dev/null > /tmp/mysql3306.err 
[root@node4 mysql5.5]# ll /tmp/mysql3306.err 
-rw-rw---- 1 mysql root 0 Jul 31 07:50 /tmp/mysql3306.err
[root@node4 mysql5.5]# service mysql start
Starting MySQL...                     [ OK ]
----啟動數據庫後查看err日志的內容
[root@node4 mysql5.5]# cat /tmp/mysql3306.err 
130731 07:51:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data
130731 7:51:32 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
130731 7:51:33 InnoDB: The InnoDB memory heap is disabled
130731 7:51:33 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130731 7:51:33 InnoDB: Compressed tables use zlib 1.2.3
130731 7:51:33 InnoDB: Initializing buffer pool, size = 128.0M
130731 7:51:33 InnoDB: Completed initialization of buffer pool
130731 7:51:33 InnoDB: highest supported file format is Barracuda.
130731 7:51:33 InnoDB: Waiting for the background threads to start
130731 7:51:34 InnoDB: 1.1.8 started; log sequence number 1625855
130731 7:51:34 [Note] Event Scheduler: Loaded 0 events
130731 7:51:34 [Note] /opt/mysql5.5/bin/mysqld: ready for connections.
Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
----使用最新的參數
----general_log和general_log_file。
[root@node4 mysql5.5]# service mysql stop
Shutting down MySQL.                    [ OK ]
[root@node4 mysql5.5]# vi my.cnf
[root@node4 mysql5.5]# cat ./my.cnf |grep '^general'
general_log = 1 
general_log_file = /tmp/mysqlgen.log
[root@node4 mysql5.5]# service mysql start
Starting MySQL...                     [ OK ]
[root@node4 mysql5.5]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show variables like '%log';

+--------------------------------+-------+
| Variable_name         | Value |
+--------------------------------+-------+
| back_log            | 50  |
| general_log          | ON  |
| innodb_locks_unsafe_for_binlog | OFF  |
| log              | ON  |
| relay_log           |    |
| slow_query_log         | OFF  |
| sync_binlog          | 0   |
| sync_relay_log         | 0   |
+--------------------------------+-------+
8 rows in set (0.04 sec)
mysql> show variables like '%file';
+---------------------+-----------------------------------+
| Variable_name    | Value               |
+---------------------+-----------------------------------+
| ft_stopword_file  | (built-in)            |
| general_log_file  | /tmp/mysqlgen.log         |
| init_file      |                  |
| local_infile    | ON                |
| pid_file      | /tmp/mysql3306.pid        |
| relay_log_info_file | relay-log.info          |
| slow_query_log_file | /opt/mysql5.5/data/node4-slow.log |
+---------------------+-----------------------------------+
7 rows in set (0.00 sec)
----在上面的操作中可以看到已經啟用查詢日志,並且文件目錄是/tmp/mysqlgen.log。
----查詢日志記錄了哪些東西?

進行下面的查詢

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| mysql       |
| performance_schema |
| test        |
| test2       |
+--------------------+
5 rows in set (0.08 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> use test2;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| course     |
| jack      |
| sc       |
| student     |
| t        |
| teacher     |
+-----------------+
6 rows in set (0.07 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from sc;
Empty set (0.04 sec)

----可以看到上面的操作都記錄在了mysqlgen.log裡面。

[root@node4 ~]# tail -f /tmp/mysqlgen.log
/opt/mysql5.5/bin/mysqld, Version: 5.5.22-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time         Id Command  Argument
130731 7:55:41    1 Query  show databases
130731 7:55:56    1 Query  SELECT DATABASE()
      1 Init DB  test
130731 7:55:59    1 Query  show tables
130731 7:56:19    1 Query  SELECT DATABASE()
      1 Init DB  test2
130731 7:56:23    1 Query  show tables
130731 7:56:27    1 Query  drop table t
130731 7:56:39    1 Query  select * from sc

二、慢查詢日志
   顧名思義,慢查詢日志中記錄的是執行時間較長的query,也就是我們常說的slow query,通過設--log-slow-queries[=file_name]來打開該功能並設置記錄位置和文件名,默認文件名為hostname-slow.log,默認目錄也是數據目錄。
    慢查詢日志采用的是簡單的文本格式,可以通過各種文本編輯器查看其中的內容。其中記錄了語句執行的時刻,執行所消耗的時間,執行用戶,連接主機等相關信息。MySQL還提供了專門用來分析滿查詢日志的工具程序mysqlslowdump,用來幫助數據庫管理人員解決可能存在的性能問題。

----使用log_slow_queries參數打開慢查詢,由於該參數已經過時,因此在err日志中將出現提示信息

----修改my.cnf文件,添加log_slow_queries參數
[root@node4 ~]# vi /opt/mysql5.5/my.cnf
[root@node4 ~]# cat /opt/mysql5.5/my.cnf |grep '^log_slow'
log_slow_queries = /tmp/mysqlslow.log
----清空err日志內容:
[root@node4 ~]# cat /dev/null > /tmp/mysql3306.err 
[root@node4 ~]# service mysql start
Starting MySQL....                     [ OK ]
----查看err日志的信息


[root@node4 data]# tail -f /tmp/mysql3306.err 

02:26:28 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data
 2:26:28 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
 2:26:28 [Warning] You need to use --log-bin to make --binlog-format work.
 2:26:28 InnoDB: The InnoDB memory heap is disabled
 2:26:28 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
 2:26:28 InnoDB: Compressed tables use zlib 1.2.3
 2:26:28 InnoDB: Initializing buffer pool, size = 128.0M
 2:26:28 InnoDB: Completed initialization of buffer pool
 2:26:28 InnoDB: highest supported file format is Barracuda.
 2:26:28 InnoDB: Waiting for the background threads to start
 2:26:30 InnoDB: 1.1.8 started; log sequence number 3069452
 2:26:30 [Note] Event Scheduler: Loaded 0 events
 2:26:30 [Note] /opt/mysql5.5/bin/mysqld: ready for connections.
Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution

----使用slow_query_log和slow_query_log_file
[root@node4 ~]# vi /opt/mysql5.5/my.cnf
[root@node4 ~]# cat /opt/mysql5.5/my.cnf |grep '^slow_query'
slow_query_log = 1
slow_query_log_file = /tmp/mysqlslow.log1

[root@node4 ~]# service mysql start
Starting MySQL...                     [ OK ]
[root@node4 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show variables like '%slow%';

+---------------------+---------------------+
| Variable_name    | Value        |
+---------------------+---------------------+
| log_slow_queries  | ON         |
| slow_launch_time  | 10          |
| slow_query_log   | ON         |
| slow_query_log_file | /tmp/mysqlslow.log1 |
+---------------------+---------------------+
rows in set (0.00 sec)

----關於slow_launch_time參數,首先修改一下參數值
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name  | Value  |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
row in set (0.00 sec)

----進行一下相關操作,查看/tmp/mysqlslow.log1的內容

mysql> select database();
+------------+
| database() |
+------------+
| NULL    |
+------------+
row in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t as select * from information_schema.tables;
Query OK, 85 rows affected (0.38 sec)
Records: 85 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 85 rows affected (0.05 sec)
Records: 85 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 170 rows affected (0.03 sec)
Records: 170 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 340 rows affected (0.05 sec)
Records: 340 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 680 rows affected (0.08 sec)
Records: 680 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 1360 rows affected (0.29 sec)
Records: 1360 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 2720 rows affected (1.49 sec)
Records: 2720 Duplicates: 0 Warnings: 0

----在這裡已經超過1s了,查看/tmp/mysqlslow.log1

[root@node4 data]# tail -f /tmp/mysqlslow.log1
# Time: 130801 2:36:25
# User@Host: root[root] @ localhost []
# Query_time: 2.274219 Lock_time: 0.000322 Rows_sent: 0 Rows_examined: 5440
use test;
SET timestamp=1375295785;
insert into t select * from t;

----log_queries_not_using_indexes參數實驗

mysql> show variables like '%indexes%';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF  |
+-------------------------------+-------+
row in set (0.00 sec)

mysql> set log_queries_not_using_indexes = 1;
ERROR 1229 (HY000): Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%indexes%';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+
row in set (0.00 sec)

mysql> desc t;
+-----------------+---------------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG  | varchar(512)    | NO  |   |     |    |
| TABLE_SCHEMA  | varchar(64)     | NO  |   |     |    |
| TABLE_NAME   | varchar(64)     | NO  |   |     |    |
| TABLE_TYPE   | varchar(64)     | NO  |   |     |    |
| ENGINE     | varchar(64)     | YES |   | NULL  |    |
| VERSION     | bigint(21) unsigned | YES |   | NULL  |    |
| ROW_FORMAT   | varchar(10)     | YES |   | NULL  |    |
| TABLE_ROWS   | bigint(21) unsigned | YES |   | NULL  |    |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES |   | NULL  |    |
| DATA_LENGTH   | bigint(21) unsigned | YES |   | NULL  |    |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES |   | NULL  |    |
| INDEX_LENGTH  | bigint(21) unsigned | YES |   | NULL  |    |
| DATA_FREE    | bigint(21) unsigned | YES |   | NULL  |    |
| AUTO_INCREMENT | bigint(21) unsigned | YES |   | NULL  |    |
| CREATE_TIME   | datetime      | YES |   | NULL  |    |
| UPDATE_TIME   | datetime      | YES |   | NULL  |    |
| CHECK_TIME   | datetime      | YES |   | NULL  |    |
| TABLE_COLLATION | varchar(32)     | YES |   | NULL  |    |
| CHECKSUM    | bigint(21) unsigned | YES |   | NULL  |    |
| CREATE_OPTIONS | varchar(255)    | YES |   | NULL  |    |
| TABLE_COMMENT  | varchar(2048)    | NO  |   |     |    |
+-----------------+---------------------+------+-----+---------+-------+
rows in set (0.05 sec)
----下面的命令是查看索引的
mysql> show index from t;
Empty set (0.01 sec)

mysql> select * from t where engine='xxx';
Empty set (0.18 sec)

# Time: 130801 2:43:43
# User@Host: root[root] @ localhost []
# Query_time: 0.185773 Lock_time: 0.148868 Rows_sent: 0 Rows_examined: 5440
SET timestamp=1375296223;
select * from t where engine='xxx';

PS:slow query log相關變量

命令行參數:

    --log-slow-queries

    指定日志文件存放位置,可以為空,系統會給一個缺省的文件host_name-slow.log

系統變量

    log_slow_queries

    指定日志文件存放位置,可以為空,系統會給一個缺省的文件host_name-slow.log

    slow_query_log

    slow quere log的開關,當值為1的時候說明開啟慢查詢。

    slow_query_log_file

    指定日志文件存放位置,可以為空,系統會給一個缺省的文件host_name-slow.log

    long_query_time

    記錄超過的時間,默認為10s

    log_queries_not_using_indexes

    log下來沒有使用索引的query,可以根據情況決定是否開啟

三、Mysqldumpslow

    如果日志內容很多,用眼睛一條一條看會累死,mysql自帶了分析的工具,使用方法如下:

[root@node4 data]# 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

您可能感興趣的文章:

  • MySQL中開啟和使用通用查詢日志的實例教程
  • 對MySQL慢查詢日志進行分析的基本教程
  • MySQL中用通用查詢日志找出查詢次數最多的語句的教程

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved