使用MySQL Slow Log来解决MySQL CPU占用高的问题

但是怎么找到是哪个SQL语句的执行时间过长呢？可以通过MySQL

Slow

Log来找，详解如下。

首先找到MySQL的配置文件my.cnf，根据不同版本的mysql开启慢查询的配置也不一样

mysql

5.0

[mysqld]

long_query_time

=

1

log-slow-queries

=

/var/log/mysql/slow.log

mysql

5.1

[mysqld]

long_query_time

=

1

slow_query_log=1

slow_query_log_file

=

/var/log/mysql/slow.log

long_query_time

是指执行超过多久的sql会被log下来，这里是1秒。

log-slow-queries和slow_query_log_file

设置把日志写在哪里

把上述参数打开，运行一段时间，就可以关掉了，省得影响生产环境

接下来就是分析了，我这里的文件名字叫

/var/log/mysql/slow.log。

先mysqldumpslow

–help下，主要用的是

-s

ORDER

what

to

sort

by

(t,

at,

l,

al,

r,

ar

etc),

‘at'

is

default

-t

NUM

just

show

the

top

n

queries

-g

PATTERN

grep:

only

consider

stmts

that

include

this

string

-s，是order的顺序，说明写的不够详细，主要有

c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序

-t，是top

n的意思，即为返回前面多少条的数据

-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow

-s

c

-t

20

/var/log/mysql/slow.log

mysqldumpslow

-s

r

-t

20

/var/log/mysql/slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow

-t

10

-s

t

-g

“left

join”

/var/log/mysql/slow.log

这个是按照时间返回前10条里面含有左连接的sql语句。

用了这个工具就可以查询出来那些sql语句是性能的瓶颈，进行优化，比如加索引，该应用的实现方式等。

首先换数据库，MySQL处理这个数量级数据比较吃力。推荐你用DB2 或ORACLE

如果不能换，检查一下存储引擎用InnoDB，如果是，检查

innodb_flush_log_at_trx_commit 这个选项，是否是1

如果是1 用SET AUTOCOMMIT = 0 ，提高数据修改速度

PHP优化需要

MySQL Slow Log 分析工具分析日志：mysqldumpslow或mysqlsla比较不错。

Explain/ DESC 分析SQL 的执行情况和SHOW PROCESSLIST

使用SHOW PROCESSLIST 看是否有锁表情况，

设置 my.cnf 中的long-query-time 和log-slow-queries 记录服务器那些SQL执行速度比较慢

根据上述情况查看对对应的SQL语句进行优化

优化服务器性能，用RAID5（SAN），加内存本身的升级，提高硬盘I/O性能。

数据库总体性能优化：

数据表最好能拆成小表。

数据库切片，分到不用的服务器上，

数据库访问性能优化

修改my.cnf, 下面是影响比较大的：

innodb_flush_log_at_trx_commit 设置为0

如果比下面值大就不用调整了：

query_cache_size 设置为16M

sort_buffer_size 设置为16M

record_buffer 设置为16M

key_buffer_size 设置为8M

innodb_buffer_pool_size 设置为32M

下面是建议设置的

table_cache 设置为512

read_buffer_size 设置为16M

myisam_sort_buffer_size设置为16M

innodb_additional_mem_pool_size 设置为128M

innodb_log_file_size 设置为256M

innodb_log_buffer_size设置为8M

数据库表优化，

1 建立相应的INDEX

2 统一编码，MySQL的默认编码是Latin1，不支持中文，需要把数据库的默认编码修改为gbk或者utf8

show variables like 'character%' 查看

另外表的编码也要与数据库统一

一个普通WEB站点的页面常常需要查询N条SQL语句后才能得出页面结果，当网站访问速度慢而前端做了大量优化工作以后，数据库瓶颈的查找也是WEB优化的一个重要部分。

MySQL中提供了一个慢查询的日志记录功能，可以把查询SQL语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。

开启慢查询功能

log-slow-queries

慢查询日志文件路径

long_query_time

超过多少秒的查询就写入日志

打开my.cnf配置文件，加入以下代码：

log-slow-queries

=

/tmp/mysql-slow.log

long_query_time

=

2

如果是windows则在my.ini中加入

my.ini

复制代码

代码如下:

log_slow_queries

long_query_time

=

2

保存退出，重启MySQL即可。

关于long_query_time设置

通常我们设置long_query_time的值为2，表示查询SQL语句超过两秒的就记录，通常2秒就够了，默认是10秒。然而，对于许多WEB程序来说，2秒的查询还是太长了。的确在许多站点中，一个SQL语句超过1秒的执行时间都算慢的了。

mysql5.1.21以后才提供更细粒度的long_query_time设定，之前的版本只能以秒做单位。

查看日志

复制代码

代码如下:

[root@lizhong

tmp]#

tail

-f

/tmp/mysql_slow.log

Time:

120815

23:22:11

User@Host:

root[root]

@

localhost

[]

Query_time:

9.869362

Lock_time:

0.000035

Rows_sent:

1

Rows_examined:

6261774

SET

timestamp=1294388531

select

count(*)

from

blog

第一行：执行时间

第二行：执行用户

第三行（重要）：

Query_time

SQL执行的时间,越长则越慢

Lock_time

在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间

Rows_sent

查询返回的行数

Rows_examined

查询检查的行数

最后

1、日志不能说明一切问题，知识表象，可能跟锁表、系统繁忙的偶发性有关，当然，如果某条SQL语句经常查询慢那基本可以判断是可以再次优化的。

2、不要开启log-queries-not-using-indexes没有索引查询记录功能，这个功能实际用处不大。就是记录SQL查询的时候，没有索引的通通记录。虽然索引对查询的速度有影响，但要看数据量大小。因为开启了这个功能以后，select

*

from

tab这样的查询也会被记录在日志中，很快日志文件就会被垃圾信息给充满，从而影响主要的查询慢日志记录的查看。

3、MySQL自带了mysqldumpslow工具用来分析slow

query日志，或者其它工具也可以，通过工具配合可以更好的分析。

---