第一步.开启 MySQL 慢查询
方式一、修改配置文件
Windows：配置文件为 my.ini，一般在 MySQL 安装目录下或者 c:\Windows 下。
Linux：配置文件为 my.cnf ，一般在 /etc 下。
在 my.ini 增加下面代码:
[mysqld]
long_query_time=2
#5.5以前版本配置如下选项
log-slow-queries="mysql_slow_query.log"
#5.5及以上版本配置如下选项
slow-query-log=On
slow_query_log_file="mysql_slow_query.log"
log-query-not-using-indexes
第一句：定义超过多少秒的查询算是慢查询，这里定义的是2秒
第二句：定义慢查询日志的路径（注意如果是 Linux 或 Mac 系统要考虑权限问题）
第三句：记录下没有使用索引的query
方式二、通过命令行开启慢查询
上面的配置需要重启 mysql server 进程 mysqld 才会生效。但很多时候，尤其是产品生产环境，不希望每次修改都要重新启动mysql服务器，而是希望能在某些特定时间记录。MySQL5.1 之后为我们提供了灵活的运行时控制，使得你不必重新启动 mysql 服务器，也能选择性地记录或不记录某些 slow queries。
MySQL5.1中，提供了全局变量 slow_query_log、slow_query_log_file，可以灵活地控制enable/disable慢查询。同时可以通过 long_query_time 设置慢查询时间
注意:设置了全局变量 slow_query_log ,则变量 log_slow_queries 也会隐性地跟着改变
mysql>set global slow_query_log=ON
不幸的是,在 MySQL5.1 之前并没有提供类似的全局变量来灵活控制，但是我们可以通过将long_query_time 设置得足够大来避免记录某些查询语句。比如
mysql>set global long_query_time = 3600;
mysql>set global log_querise_not_using_indexes = ON;
MySQL5.0 在不关闭服务的情况下，希望不记录日志的办法是让日志文件成为 /dev/null 的符号链接(symbolic link)。注意:你只需要在改变后运行 flush logs 以确定MySQL释放当前的日志文件描述符，重新把日志记录到/dev/null。
和 MySQL5.0 不同, MySQL5.1 后可以在运行时改变日记行为，将日志记录到数据库表中。只要将 MySQL 全局变量 log_output 设置为 table 即可。MySQL 会将日志分别记录到mysql.gengera_log 和 mysql.slow_log 两张表中。但是，推荐的做法是将日志记录在日记文件中。
mysql> show variables like ‘log_output’\G
Variable_name: log_output
Value: FILE
mysql>set global log_output=’table’;
缺陷与审记
如果开启了 log_queries_not_using_indexes 选项，slow query 日志会充满过多的垃圾日志记录，这些快且高效的全表扫描查询(表小)会冲掉真正有用的slow queries记录。比如 select * from category 这样的查询也会被记录下来。
通过microslow-patch补丁可使用更细的时间粒度，和记录所有执行过的sql语句。不过，使用这个补订不得不自己编译MySQL，出于稳定性考滤，我们推荐在开发测试环境，可以打上这个补丁，享受这个补丁带来的便利。在运营环境尽量不要这么做…
第二步.验证慢查询是否开启
执行如下SQL语句来查看 mysql 慢查询的状态，结果会把是否开启慢查询、慢查询的秒数、慢查询日志等信息打印在屏幕上。
/*查看慢查询时间 */
show variables like "long_query_time";默认10s
/*查看慢查询配置情况 */
show status like "%slow_queries%";
/*查看慢查询日志路径 */
show variables like "%slow%";
第三步.执行一次慢查询操作
其实想要执行一次有实际意义的慢查询比较困难，因为在自己测试的时候，就算查询有20万条数据的海量表，也只需要零点几秒。我们可以通过如下语句代替：
SELECT SLEEP(10);
第四步.查看慢查询的数量
通过如下 sql 语句，来查看一共执行过几次慢查询：
show global status like '%slow%';
mysql日志的配置：
注意：这些日文件在mysql重启后才会生效
#记录所有sql语句
log=/usr/local/mysql/data/mysql.log
#记录数据库启动关闭信息，以及运行过程中产生的错误信息
log-error=/usr/local/mysql/data/myerror.log
# 记录除 select 语句之外的所有 sql 语句到日志中，可以用来恢复数据文件
log-bin=/usr/local/mysql/bin
#记录查询慢的sql语句
log-slow-queries=/usr/local/mysql/data/slow.log
#慢查询时间
long_query_time=2
第五步.分析慢查询日志
方式一.通过工具分析
MySQL 自带了 mysqldumpslow 工具用来分析 slow query 日志，除此之外，还有一些好用的开源工具。比如MyProfi、mysql-log-filter，当然还有 mysqlsla。
以下是 mysqldumpslow 常用参数说明，详细内容可用 mysqldumpslow -help 查询。
-s，表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序（从大到小），ac、at、al、ar表示相应的倒序。
-t，是top n的意思，即为返回前面多少条数据。
-g，后边可以写一个正则匹配模式，大小写不敏感。
接下来就是用MySQL 自带的慢查询工具 mysqldumpslow 分析了（该工具位于 MySQL 的bin 目录下），我这里的日志文件名字是host-slow.log。
列出记录次数最多的10个sql语句
mysqldumpslow -s c -t 10 host-slow.log
列出返回记录集最多的10个sql语句
mysqldumpslow -s r -t 10 host-slow.log
按照时间返回前10条里面含有左连接的sql语句
mysqldumpslow -s t -t 10 -g "left join" host-slow.log
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化起到非常大的帮助。
方式二.直接分析mysql慢查询日志
日志部分内容如下:
# Time: 170502 17:38:54
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 3.794217 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 4194304
SET timestamp=1350466734;
select * from wei where text='orange';
# Time: 170502 17:46:22
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 3.819219 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 4194304
SET timestamp=1350467182;
select * from wei where text='xishizhaohua';
其实定位到慢查询语句就已经完成了一大步了，执行 explain 或者 desc 命令查看慢查询语句，如下图：
460f2a9f-b343-333a-ab2d-61e7ec3d89ab.png
问题很明显，解决方式也很明显-------建索引。
mysql> create index text_index on wei(text);
Query OK, 4194304 rows affected (1 min 58.07 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
然后再执行查询操作，用时明显少了很多。
mysql> select * from wei where text='orange';
+---------+--------+
| id | text |
+---------+--------+
| 4103519 | orange |
+---------+--------+
1 row in set (0.33 sec)
Slow Query 日志虽然帮你记录了执行过的SQL语句。但它不是万能的，意义可能没有你想象的那么大。它只告诉了你哪些语句慢，但是为什么慢?具体原因，还是需要你自己去分析，不断的调试。也许你只需要换一条更有效的sql语句，也许你只需简单地增加一个索引，但也有可能你需要调整你应用程序的设计方案。比如上面那条语句很明显，它检查了400多万行数据。不幸的是，并不是每条语句都这么明显。也许还有别的原因，比如:
- 锁表了，导致查询处于等态状态。lock_time显示了查询等待锁被翻译的时间
- 数据或索引没有被缓存。常见于第一次启动服务器或者服务器没有调优
- 备份数据库，I/O变慢
- 同时运行了其它的查询，减少了当前查询
所以,不要过于关注日志文件某条记录，而应该理性地审查，找出真正的原因。如经常出现的 slow query 需要特别注意。如果个别出现，则做一些常规检查即可。推荐大家统计并形成基准报告，进行比较排除，如此肯定比胡乱瞎撞有用。希望大家不要在这部分过于浪费时间与精力。
网友评论