mysql性能优化系列6-慢查询

1.慢查询

慢查询日志是mysql用来记录执行时间超过long_query_time参数设定的时间阈值的SQL语句。默认慢查询日志是关闭的。

1.1 慢查询配置

• slow_query_log：打开、关闭慢查询日志
• slow_query_log_file：慢查询日志的存储路径及文件（默认和数据文件放一起）
• long_query_time：慢查询SQL执行时间得伐值，默认10秒
• log_queries_not_using_indexes：是否记录未使用索引的SQL
• log_output：日志存放的地方，默认是FILE，还可以是TABLE或者是【FILE,TABLE】
  上述配置可通过以下命令查看：
• show VARIABLES like ‘%slow_query_log%’
• show VARIABLES like ‘%slow_query_log_file%’
• show VARIABLES like ‘%long_query_time%’
• show VARIABLES like ‘%log_queries_not_using_indexes%’
• show VARIABLES like ‘log_output’

1.2 慢查询文件格式

从慢查询日志里面，找一个一个慢查询日志看下，这里我把慢查询时间设为了0，也就是所有SQL都记录。

//用户名 、用户的IP信息、线程ID号
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    25
//执行花费的时间
# Query_time: 0.001000  
//执行获得锁的时间
#Lock_time: 0.000000 
//获得的结果行数
#Rows_sent: 1  
//扫描的数据行数
#Rows_examined: 1
//SQL执行的具体时间
SET timestamp=1558275429;
//具体的SQL语句
SELECT id  from test where id>1

1.3 慢查询工具

（1）Mysqldumpslow
语法：
mysqldumpslow -s 参数 -t 参数 日志名

• -s：后面加c（总次数）、t（总时间）、l（锁的时间）、r（返回结果行数）、at,al,ar （平均数）
• -t top 指定取符合条件的前面几条作为结果输出

（2）pt_query_digest
与mysqldumpshow工具相比，py-query_digest分析结果更具体，更完善。
命令：pt-query-digest [OPTIONS] [FILES] [DSN]

• –history：将分析结果保存到表中，下次再使用–history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中
• –review：将分析结果保存到表中，一个类型的查询一条记录，下次再有相同语句不再记录
• –create-review-table：当使用–review参数把分析结果输出到表中时，如果没有表就自动创建
• –create-history-table：当使用–history参数把分析结果输出到表中时，如果没有表就自动创建
• –filter：对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
• –limit：限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出
• –host：服务器地址
• –user：用户名
• –password：密码
• –output：分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json等
• –since 从什么时间开始分析
• –until 截止时间，和since一起可以指定分析一段时间内的慢查询

分析结果：
第一部分：总体统计结果

• Overall：总共有多少条查询
• Time range：查询执行的时间范围
• unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
• total：总计 min：最小 max：最大 avg：平均
• 95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值

第二部分：查询分组统计结果

• Rank：所有语句的排名，默认按查询时间降序排列，通过–order-by指定
• Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
• Response：总的响应时间
• time：该查询在本次分析中总的时间占比
• calls：执行次数，即本次分析总共有多少条这种类型的查询语句
• R/Call：平均每次执行的响应时间
• V/M：响应时间Variance-to-mean的比率
• Item：查询对象

第三部分：每一种查询的详细统计结果

• ID：查询的ID号，和Query ID对应
• Databases：数据库名
• Users：各个用户执行的次数（占比）
• Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
• Tables：查询中涉及到的表
• Explain：SQL语句