侧边栏壁纸
  • 累计撰写 122 篇文章
  • 累计创建 68 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录

MySQL查询截取分析

刘少锋
2022-03-21 / 0 评论 / 0 点赞 / 25 阅读 / 1,464 字

实际情况问题解决流程

  1. 收到监控系统报告,系统变慢,假如是sql变慢
  2. 开启慢查询日志,设置阙值,比如超过5秒钟的就是慢SQL,并将它抓取出来。
  3. explain+慢SQL分析
  4. show profile,查询SQL在Mysql服务器里面的执行细节和生命周期情况
  5. 运维经理 or DBA,进行SQL数据库服务器的参数调优。

慢查询日志

是什么

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。

具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合之前explain进行全面分析。

怎么用

默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。

当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

命令行临时修改

# 默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的
SHOW VARIABLES LIKE '%slow_query_log%';
# 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效
set global slow_query_log = 1;

那么开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢?

这个是由参数long_query_time控制,默认情况下long_query_time的值为10秒,假如运行时间正好等于long_query_time的情况,并不会被记录下来。也就是说,在mysql源码里是判断大于long_query_time,而非大于等于

# 查看配置时间
SHOW VARIABLES LIKE 'long_query_time%';

怎么修改慢查询的标准时间?

# 设置慢的阙值时间
set global long_query_time=3;
# 设置后无效需要重新连接或新开一个会话才能看到修改值。
SHOW VARIABLES LIKE 'long_query_time%';
# 查询当前系统中有多少条慢查询记录
show global variables like'long_query_time';

配置文件永久修改

如果要永久生效,就必须修改配置文件my.cnf(其它系统变量也是如此)

修改my.cnf文件,[mysqld]下增加或修改参数后,然后重启MySQL服务器。

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/query-slow.log
# 关于慢查询的参数slow_query_log_file,它指定慢查询日志文件的存放路径,系统默认会给一个缺省的文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)
long_query_time = 3
log_output = FILE

日志分析工具mysqldumpslow

在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具,mysqldumpslow。

# 得到返回记录集最多的10个SQL
mysqldumpslow-s r -t 10 /var/fib/mysql/query-slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow-s c -t 10 /var/lib/mysql/query-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow-s t -t 10 -g "left join" /var/lib/mysql/query-slow.log
# 另外建议在使用这些命令时结合|和more使用,否则有可能出现爆屏情况
mysqldumpslow-s r -t 10 /var/lib/mysql/query-slow.log | more

Show Profile

是什么:是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

官网: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

默认情况下,参数处于关闭状态,并保存最近15次的运行结果

分析步骤

是否支持,看看当前的mysql版本是否支持

Show variables like 'profiling';
# 默认是关闭,使用前需要开启
Show variables like "profiling%";

开启功能,默认是关闭,使用前需要开启

set profiling = on;

运行SQL,假如存在运行速度慢的SQL语句

select * from emp group by id%10 limit 150000;

查看结果

show profiles;

诊断SQL

show profile cpu,block io for query Query_ID; # Query_ID即上一步前面的问题SQL数字号码

日常开发需要注意的结论

# 有以下任一情况,说明SQL语句有问题,必须优化
converting HEAP to MyISAM # 查询结果太大,内存都不够用了往磁盘上搬了。
Creating tmp table # 创建临时表:拷贝数据到临时表,用完再删除
Copying to tmp table on disk # 把内存中临时表复制到磁盘,危险!!!
locked

全局查询日志

配置启用

在mysql的my.cnf中,设置如下:

#开启
general_log = 1
#记录日志文件的路径
general_log_file = /path/logfile
#输出格式
log_output = FILE

命令启用

set global general_log = 1;
set global log_output = 'TABLE';
# 此后,你所编写的sql语句,将会记录到mysql库里的general_log表,可以用下面的命令查看
select * from mysql.general_log;

会抓取所有的SQL语句,永远不要在生产环境开启这个功能

0

评论区