Mysql实例mysql中开启慢sql查询(mysql慢查询日志)的方法

《Mysql实例mysql中开启慢sql查询(mysql慢查询日志)的方法》要点：
本文介绍了Mysql实例mysql中开启慢sql查询(mysql慢查询日志)的方法，希望对您有用。如果有疑问，可以联系我们。

MYSQL数据库对于mysql数据库应用程序来说,查看慢SQL以优化数据库操作是最基本的,对于以MySQL为DB的应用程序来说也不例外,本文就是以MySQL为例来介绍如何查看慢SQL的问题.

MYSQL数据库在MySQL中,慢SQL就是指所有执行时间大于long_query_time的SQL语句,知道这些语句后,便可以进行相关优化了,比如：加索引、合并语句等.

MYSQL数据库一、启用慢SQL
1、查看慢SQL是否启用
 

MYSQL数据库上面语句能查看慢SQL是否启用了,即Value为ON表示启用了,为OFF表示禁用了.
 

MYSQL数据库上面语句能查看执行慢于多少秒的SQL算慢SQL即会记录到日志文件中,Value为10表示大于10秒的会记录.

MYSQL数据库2、开启慢SQL
如果你的MySQL是源码编译的,那么慢SQL日志文件信息由编译时指定,比如：一般将慢SQL日志文件放在/data/mysql/目录下,文件名为hostname-slow.log.
如果你的MySQL是RPM安装的,那么慢SQL日志文件信息由MySQL配置文件/etc/my.cnf指定,打开配置文件,找到[mysqld]区段,增加日志配置,具体如下：
 

MYSQL数据库log指定MySQL日志文件存放路径.
log_slow_queries指定慢SQL日志文件存放路径,此目录文件一定要有写权限.
long_query_time指定大于多长时间算慢SQL,单位秒.

MYSQL数据库注意,上述配置一定要加在[mysqld]后而不是[mysqld_safe]下面,否则不会生效.
配置完后,重启MySQL即可：
 

MYSQL数据库上面说明开启了慢SQL,并且时间为2秒.
CD到目录/var/lib/mysql/目录下能看到自动创建了慢SQL日志文件hostname-slow.log.
cat hostname-slow.log会发现里面没有记录,简单测试下：
 

MYSQL数据库上面执行完了一个3秒的SQL语句,这时候打开慢SQL日志文件就能看到了.
当然除了上面的设置方式外,还可以在mysqld进程启动时,通过参数指定选项,比如：--log-slow-queries[=file_name].

MYSQL数据库二、慢SQL分析
慢SQL分析可以通过vi直接打开日志文件来做,但是这种方式还是比较原始,实际上慢SQL分析工具有很多种,常用的有如下几款：
1、mysqldumpslow
这是MySQL官方提供的慢SQL日志分析工具,安装好后,执行如下语句就能分析日志：
#mysqldumpslow /var/lib/mysql/hostname-slow.log
主要功能是统计不同慢SQL的如下指标：
count(出现次数)    Time(执行最长时间/累计总耗费时间)    Lock(等待锁的时间S)    Rows(发送给客户端的总行数)    用户及SQL语句
慢SQL日志文件有时候记录数会比较多,那么mysqldumpslow提供了一些参数用于解决这些问题：
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
上面命令会以查询时间来排序并显示前10条查询,其中,参数s表示排序选项(c:查询次数、r:返回记录行数、t:查询时间),参数t表示只显示top n条查询.

MYSQL数据库2、mysqlsla
这是由hackmysql.com提供的一款日志分析工具,该网站还提供了mysqlreport、mysqlidxchk等实用的mysql工具值得学习.
安装好后,执行如下命令就能分析日志了：
#mysqlsla -lt slow /var/lib/mysql/hostname-slow.log
该工具方便用户分析慢查询的原因,包括执行频率、数据量、查询消耗等,具体包含信息如下：
queries total(总查询次数)     unique(去重后的SQL数量)   
Sorted by(报表按照什么排序)
Gand Totals(慢SQL统计信息)：Time(平均执行时间),Lock(等待锁时间),Rows Sent(总行数),Rows Examined(总扫描行数)
Count(SQL执行次数及占总慢SQL数量的百分比)
Time(执行时间)：包括总时间、最小/最大时间、时间占总慢SQL时间的百分比
Lock Time(等待锁时间)
Rows Sent(行数统计)：包括平均、最小/最大数量
Database(数据库)
Users(用户、IP、占所有用户执行SQL的百分比)
Query abstract(抽象后的SQL语句)
Query sample(SQL语句例子)

MYSQL数据库3、mysql-log-filter
google code上的开源分析工具,提供python和php两种脚本,可以点击这里下载.
执行如下命令就能分析日志：
#python mysql_filter_slow_log.py /var/lib/mysql/hostname-slow.log --no-duplicates --sort-execution-count --top=10
功能上类似于mysqldumpslow,但是多出了很多查询时间的统计信息,包括平均、最大、累计等,此外,还对输出内容排版和格式化.

MYSQL数据库4、myprofi
sourceforge上提供的纯php写的开源分析工具,可以点击这里下载.
执行如下命令就能分析日志：
#php parser.php -slow /var/lib/mysql/hostname-slow.log
执行上面命令后,会列出总的慢查询次数和类型、去重后的SQL语句、执行次数及占总慢SQL数量的百分比.
myprofi的输出比较简洁,适合于只关心慢SQL语句及执行次数的同学.

MYSQL数据库三、慢SQL优化
SQL优化是一门大工程,不敢狂言,在此只是表述我实际中遇到的问题及优化解决方案来以此达到抛砖引玉的效果.
1、索引问题
在一次压力测试中,发现有一个70W记录的流水表,由于没有加索引导致在update时MySQL服务器CPU瞬间飙到170%,语句类似于下面：
update loginlog set LogoutTime=%lu where RoleName=%s and LoginTime=%lu
loginlog表的主键是自增ID.
对字段RoleName和LoginTime建索引后,问题解决了.
因此,对于操作频繁的select、update、带where的delete语句最好对where条件涉及的字段建索引,当然这也会给insert语句带来一定的性能损失.

MYSQL数据库2、联合主键问题
在一次压力测试中,在慢SQL日志中发现一条记录：
 

MYSQL数据库userrelation中FromRoleName与ToRoleName为联合主键,但是上面的where语句中联合主键并没有达到效果.
这是因为,联合主键在索引时,where条件对字段及顺序有要求,比如：
 

MYSQL数据库上述两种情况都有效果.
为字段ToRoleName建立索引后,问题解决了.

MYSQL数据库3、分页问题
有时候会对表格记录有limit操作的需求,这就涉及到分页问题,这里贴一段我的DBA同事曾给我的一段关于分页优化的建议吧：
(1)首次查询的时候缓存结果.这样情况就变得简单了,无论是结果条目的数量,总共的页面数量,还是取出其中的部分条目.

MYSQL数据库(2)不显示总共有多少条目.Google搜索结果的分页显示就用了这个特性.很多时候你可能看了前几页,就够了.那么我可以这样,每次我都把结果限制在500条(这个数据越大 资源消耗越大)然后你每次查询的时候,都查询501条记录,这样,如果结果真有501个,那么我们就显示链接 “显示下500条记录”.

转载请注明本页网址：
http://www.vephp.com/jiaocheng/6176.html