Mysql开启慢SQL并分析原因
作者:seelye 发布时间:2024-01-24 12:03:30
第一步.开启mysql慢查询
方式一:修改配置文件
Windows:Windows 的配置文件为 my.ini,一般在 MySQL 的安装目录下或者 c:\Windows 下。
Linux:Linux 的配置文件为 my.cnf ,一般在 /etc 下
在 my.ini 增加几行:
[mysqlld]
long_query_time=2
#5.0、5.1等版本配置如下选项
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秒
第二句使用来定义慢查询日志的路径(因为是windows,所以不牵涉权限问题)
第三句就是记录下没有使用索引的query
第二步:查看关于慢查询的状态
方式二:通过MySQL数据库开启慢查询
上文的配置需要重启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记录
#注意:设置了slow_query_log全局变量, log_slow_queries也会隐性地跟着改变
mysql>set global slow_query_log=ON
不幸运的是,在MySQL5.0并没有提供类似的全局变量来灵活控制,但是我们可以通过将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';
缺陷与审记
虽然记录了slow query能够帮助你优化产品。但是MySQL目前版本,还有几大蹩足的地方。
1.MySQL5.0版本, long_query_time时间粒度不够细,最小值为1秒。对于高并发性能的网页脚本而言,1秒出现的意义不大。即出现1秒的查询比较少。直到mysql5.1.21才提供更细粒度的long_query_time设定.
2.不能将服务器执行的所有查询记录到慢速日志中。虽然MySQL普通日志记录了所有查询,但是它们是解析查询之前就记录下来了。这意味着普通日志没办法包含诸如执行时间,锁表时间,检查行数等信息。
3.如果开启了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万条数据的海量表,也只需要0.几秒。我们可以通过如下语句代替:
SELECT SLEEP(10);
第四步:查看慢查询的数量
通过如下sql语句,来查看一共执行过几次慢查询:
show global status like '%slow%';
mysql日志的配置:
注意:这些日文件在mysql重启的时候才会生成
#记录所有sql语句
log=E:/mysqllog/mysql.log
#记录数据库启动关闭信息,以及运行过程中产生的错误信息
log-error=E:/mysqllog/myerror.log
# 记录除select语句之外的所有sql语句到日志中,可以用来恢复数据文件
log-bin=E:/mysqllog/bin
#记录查询慢的sql语句
log-slow-queries=E:/mysqllog/slow.log
#慢查询时间
long_query_time=2
第四步:分析慢查询日志
方式一:通过工具分析
MySQL自带了mysqldumpslow工具用来分析slow query日志,除此之外,还有一些好用的开源工具。比如MyProfi(下载地址:http://sourceforge.net/projects/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: 121017 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: 121017 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命令查看慢查询语句,如下图:
问题很明显,解决方式也很明显,建索引了。
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语句,也许你只需简单地增加一个索引,但也有可能你需要调整你应用程序的设 计方案。比如,上面那条语句是很明显,它检查了600多万行数据。不幸的是,并不是每条语句都这么明显。也许还有别的原因,比如:
*锁表了,导致查询处于等态状态。lock_time显示了查询等待锁被翻译的时间
*数据或索引没有被缓存。常见于第一次启动服务器或者服务器没有调优
*备份数据库,I/O变慢
*也许同时运行了其它的查询,减少了当前查询
所以,不要过于紧张日志文件某条记录,而应该理性地审记,找出真正的原因。如果经常出现的slow query需要特别注意。如果个别出现,则做一些常规检查即可。我们建议,统计并且形成基准报告,进行比较排除,比胡乱瞎撞有用。希望大家不要在这部分过于浪费时间与精力。
来源:https://blog.csdn.net/seelye/article/details/50753854
猜你喜欢
- 1.写在前面JS要实现下载功能,一般都是这么几个过程:生成下载的URL,动态创建一个A标签,并将其href指向生成的URL,然后触发A标签的
- 我就废话不多说了,大家还是直接看代码吧~type Animal struct { ID int64 Name string `gorm:&q
- 问题你需要将一个Python对象序列化为一个字节流,以便将它保存到一个文件、存储到数据库或者通过网络传输它。解决方案对于序列化最普遍的做法就
- 目录创建文件夹布局编写命令代码实际应用场景案例1:检查数据库连接是否已就绪案例2:周期性发送邮件每次在启动Django服务之前,我们都会在终
- 最流行的数据交换格式之一是 CSV 格式。是需要通过键盘和控制台以外的方式将信息输入和输出的程序,通过文本文件交换信息是在程序之间共享信息的
- 前言我们在上一期学习了关于Python 迭代器Iterator详情相关的概念,满足迭代器需要符合两个条件实现__iter__()方
- 前言ES5中新增的不少东西,了解之对我们写JavaScript会有不少帮助,下面主要介绍了ES5中的Array对象,需要的朋友们一起来看看吧
- 最近,就“尊重”一词,个人小有感概。也许跟我说“尊重”一词的同事并不是这么想的,但我反思了一下自己,作为一名设计师,确实存在这些疑问(不足之
- 先简单的了解下日期和时间数据类型及工具python标准库包含于日期(date)和时间(time)数据的数据类型,datetime、time以
- 本文实例讲述了php+html5基于websocket实现聊天室的方法。分享给大家供大家参考。具体如下:html5的websocket 实现
- 时间序列预测时间序列是按照时间顺序排列的数据集合,在很多应用中都非常常见。时间序列分析是对这些数据进行分析和预测的过程。时间序列预测是该分析
- 每个PHP脚本都限制了执行时间,所以我们需要通过 set_time_limit 来设置一个脚本的执行时间为无限长;然后使用 flush()
- python标记语句块使用方法,python语言和其它的编程语言有着显著的区别,那就是python对格式的要求非常苛刻,好处就是书写上简易命
- 在数据库testDB中存在架构A及用户A,现将testDB数据库所属的用户由A改为B,同时删除用户A;架构也由A改为B,删除架构A,操作如下
- 大家好,今天分享一个非常有趣的 Python 教程,如何美化一个 matplotlib 折线图,喜欢记得收藏、关注、点赞。1. 导入包imp
- 最近正好在寻求一种Python的数据库ORM (Object Relational Mapper),SQLAlchemy (项目主页)这个开
- 一、变量1.变量•指在程序执行过程中,可变的量;•定义一个变量,就会伴随有3个特征,分别是内存ID、数据类型和变量值。•其他语言运行完之前,
- 前言项目中要实现多选,就想到用插件,选择了bootstrap-select。附上官网api链接,http://silviomoreto.gi
- 这篇文章主要介绍了如何基于python实现脚本加密,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可
- 句柄(handle)是C++程序设计中经常提及的一个术语。它并不是一种具体的、固定不变的数据类型或实体,而是代表了程序设计中的一个广义的概念