mysql-性能调优2--开启慢查询记录日志功能

注：原创作品，转载请注明出处

在我们的应用常有查询较慢的sql语句，开启慢查询日志记录功能，能把这些查询较慢的sql一览无余。

在my.ini配置文件中添加如下配置（这个功能mysql默认是没有开启的）

#开启记录慢查询日志，可以记录查询最慢的sql语句，long_query_time制定慢的时间，单#位为秒，log-queries-not-using-indexes是记录那些没有使用索引查询的sql语句。两个条件满足一个就会记录到慢查询日志中。

log-slow-queries = C:\Program Files\MySQL\MySQL Server 5.5\customLog\log_slow.txt
long-query-time = 1
log-queries-not-using-indexes

利用本人博客 mysql-性能调优1 中的例子，往表中插入一百万条数据，之后，执行查询语句，

mysql> select 10*count(*) from supan;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+-------------+
| 10*count(*) |
+-------------+
|     9999990 |
+-------------+
1 row in set (2.31 sec)

可见用了2.31秒，这样慢查询日志文件中就有了这个乌龟sql。
如下：
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 150117 16:39:34
# User@Host: root @ localhost [127.0.0.1]
# Query_time: 1.255072 Lock_time: 0.211012 Rows_sent: 1 Rows_examined: 999999
use test;
SET timestamp=1421483974;
select 10*count(*) from supan;


之后测试log-queries-not-using-indexes指标：
之前是没有在supan表name中建立索引，所以把时间调到很大8，添加log-queries-not-using-indexes 重启mysql，执行查询：

mysql> select * from supan where name = '李四';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Empty set (2.50 sec)

结果日志中存放了这个没有使用索引的记录。可以看一下日志：
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 150117 17:11:10
# User@Host: root @ localhost [127.0.0.1]
# Query_time: 1.435082 Lock_time: 0.121007 Rows_sent: 0 Rows_examined: 999999
use test;
SET timestamp=1421485870;
[color=red]select * from supan where name = '李四';[/color]

我们在supan的name字段值添加索引：
CREATE INDEX index_supan_name ON supan (name);

在此执行查询：

mysql> select * from supan where name = '李四';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Empty set, 2 warnings (1.29 sec)

结果日志中没有了这个sql，因为这次查询用到了索引，可以看一下日志：
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument

呵呵呵。完毕