mysql开启慢查询日志

来自慕课网：

sqlercn:性能优化之MySQL优化

http://www.imooc.com/learn/194

下载mysql官方提供的数据库sakila

官方下载地址：https://dev.mysql.com/doc/index-other.html

官方安装教程：https://dev.mysql.com/doc/sakila/en/sakila-installation.html

安装sakila数据库

（1）登录mysql

mysql -u root -p

（2）创建表结构

下载后的sakila压缩包中包含三个文件，schema是创建表结构的文件，在命令行执行以下语句，source后跟文件所在路径

source /Users/shanmenglu/Downloads/sakila-db/sakila-schema.sql

（3）插入数据

sakila-data.sql是数据文件，执行以下语句

source /Users/shanmenglu/Downloads/sakila-db/sakila-data.sql

（4）查看数据库

show databases;

+--------------------+| Database           |+--------------------+| information_schema || db_test            || db_webdemo         || mysql              || performance_schema || sakila             || sys                |+--------------------+

开启慢查询日志

1.查看慢查询日志是否开启

use sakila;#查看慢查询日期是否开启show variables like 'slow_query_log';

如果为off表示未开启

+----------------+-------+| Variable_name  | Value |+----------------+-------+| slow_query_log | OFF   |+----------------+-------+

2.模糊查询，查询带有log关键字的变量，找到log_queries_not_using_indexes

show variables like '%log%';

| log_queries_not_using_indexes           | OFF                                                           |

输入以下命令，将log_queries_not_using_indexes为on，表示记录未使用索引的查询

set global log_queries_not_using_indexes=on;

3.查看变量long_query_time,表示查询时间超过多久以上的语句记录到慢查询日志中

show variables like 'long_query_time';

+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

为了方面测试，可以将value值设小一些，这里设为0

set long_query_time=0;

4.开启慢查询日志

set global slow_query_log=on;

5.查看慢查询日志文件所在路径

show variables like '%slow%';

+---------------------------+--------------------------------------------------------+| Variable_name             | Value                                                  |+---------------------------+--------------------------------------------------------+| log_slow_admin_statements | OFF                                                    || log_slow_slave_statements | OFF                                                    || slow_launch_time          | 2                                                      || slow_query_log            | ON                                                     || slow_query_log_file       | /usr/local/mysql/data/shanmengludeMacBook-Pro-slow.log |+---------------------------+--------------------------------------------------------+

slow_query_log_file是慢查询日志文件，从上可以看到文件的存储路径
6.测试

从sakila随便输入一条查询语句

show tables; select * from actor limit 0,10;

+----------+------------+--------------+---------------------+| actor_id | first_name | last_name    | last_update         |+----------+------------+--------------+---------------------+|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 ||        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 ||        3 | ED         | CHASE        | 2006-02-15 04:34:33 ||        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 ||        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 ||        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 ||        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 ||        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 ||        9 | JOE        | SWANK        | 2006-02-15 04:34:33 ||       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |+----------+------------+--------------+---------------------+10 rows in set (0.00 sec)

7.退出mysql,查看慢查询日志文件
进入日志文件所在目录，查看日志内容

sudo tail -50 shanmengludeMacBook-Pro-slow.log

日志内容如下：

/usr/local/mysql/bin/mysqld, Version: 5.7.17 (MySQL Community Server (GPL)). started with:Tcp port: 3306  Unix socket: /tmp/mysql.sockTime                 Id Command    Argument# Time: 2017-08-12T13:01:14.877267Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.000062  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0use sakila;SET timestamp=1502542874;set long_query_time=0;# Time: 2017-08-12T13:01:24.957526Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.001101  Lock_time: 0.000118 Rows_sent: 1  Rows_examined: 1012SET timestamp=1502542884;show variables like 'long_query_time';# Time: 2017-08-12T13:02:48.554769Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.001662  Lock_time: 0.000118 Rows_sent: 5  Rows_examined: 1012SET timestamp=1502542968;show variables like '%slow%';# Time: 2017-08-12T13:05:13.819294Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.000313  Lock_time: 0.000081 Rows_sent: 23  Rows_examined: 23SET timestamp=1502543113;show tables;# Time: 2017-08-12T13:05:50.121285Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.000241  Lock_time: 0.000086 Rows_sent: 10  Rows_examined: 10SET timestamp=1502543150;select * from actor limit 0,10;# Time: 2017-08-12T13:07:02.037599Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.000006  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 10SET timestamp=1502543222;# administrator command: Quit;

从中找到刚才执行的语句

# Time: 2017-08-12T13:05:50.121285Z# User@Host: root[root] @ localhost []  Id:     5# Query_time: 0.000241  Lock_time: 0.000086 Rows_sent: 10  Rows_examined: 10SET timestamp=1502543150;select * from actor limit 0,10; #执行内容

user@host：表示主机信息

query_time：SQL的执行信息

timestamp：执行时间

慢日志分析工具：

（1）mysqldumpslow

（2）pt-query-digest

如何发现有问题的SQL？

（1）查询次数多并且每次查询占用时间长的SQL

（2）IO大的SQL(扫描的行数)

（3）未命中索引的SQL