MySQL入门教程（19）慢查询日志的配置与分析
一、什么是慢查询
慢查询是指执行时间较长的SQL，由于慢查询通常会扫描大量数据，带来不必要的I/O和CPU资源消耗，会消耗大量的数据库和服务器资源，当出现大量并发慢查询时，会严重影响业务系统的正常使用。慢查询的产生通常有2个原因，一是查询语句本身优化不足而产生，比如SQL过于复杂或者缺失索引等。二是服务器本身负载过高，当磁盘IO、CPU负载、网络负载等任何一个环节负载异常，都会导致原本正常的查询变慢，所以定期对慢查询日志进行分析，将那些执行次数多、执行时间长、IO过高、未命中索引的SQL及时进行优化是一件很重要的事情。
二、MySQL慢查询相关参数
在MySQL中将执行时间超过设置阈值的查询定义为慢查询并且记录到慢查询日志中，在5.7版本中不会将锁等待导致的慢查询进行记录，而8.0则会把锁等待消耗的时间也算作查询时间。
慢查询相关配置参数：
· slow_query_log：设置为ON表示开启慢查询记录
· slow_query_log_file：慢查询日志保存路径，默认在数据目录下，名为hostname-slow.log
· long_query_time：指定达到多少秒才算慢查询，设为0代表记录所有查询
· log_queries_not_using_indexes：如果语句没有使用索引也会被记录，即便没有达到慢查询阈值
· log_throttle_queries_not_using_indexes：如果没有使用索引的SQL执行特别频繁，每分钟记录的次数
· min_examined_row_limit：至少要扫描达到指定的行数才记录，虽然这样可以避免不必要的记录写入日志，但是实际不建议配置，因为如果一个语句扫描的行数很少却依然成为了慢查询，反而是需要关注的
· log_timestamps：日志时间戳，默认为UTC时间，这样查看日志会存在时区问题，建议修改为SYSTEM，和系统时间一致
· log-slow-admin-statements：记录由ALTER TABLE等语句引发的慢查询
· log-slow-slave-statements：记录从服务器产生的慢查询
[mysqld]
......
slow_query_log = on #开启慢查询日志
slow_query_log_file = /data/mysql/logs/mysql-slow.log #日志保存路径，默认在数据目录下，名为hostname-slow.log
long_query_time = 2 #指定达到多少秒才算慢查询，设为0代表记录所有查询
log_queries_not_using_indexes = 1 #如果语句没有使用索引也会被记录，即便没有达到阈值
log_throttle_queries_not_using_indexes=60 #如果没有使用索引的SQL执行特别频繁，那每分钟最多只记录60次
log_timestamps = system #日志时间戳默认为UTC时间，这样查看日志会存在时区问题，建议修改为SYSTEM，和系统时间一致
# min_examined_row_limit = 1000 #至少要扫描达到指定的行数才记录
# log-slow-admin-statements #记录由ALTER TABLE等语句引发的慢查询
# log-slow-slave-statements #记录从服务器产生的慢查询
执行一个慢查询语句测试
select sleep(3);
四、MySQL慢日志的分析
慢日志是一个普通的文本文件，支持使用vi等工具查看，主要参考指标如下
· rows_examined：可以理解为该SQL扫描了总的行数，该值越高查询必然越慢
· rows_sent：从扫描的行数中获取到需要的行数，如果该值很低代表做了很多无用的扫描
使用第三方工具分析慢查询日志
1、mysqldumpslow工具
mysqldumpslow是MySQL附带的慢日志分析，常用选项如下：
-s：标识按照哪种方式排序，包含：
· al：平均锁等待时间
· at：平均查询时间
· ar：平均返回数据行数
· c：查询执行次数
· l：锁等待时间
· r：返回数据行数
· t：查询时间
-t N：标识返回前 N 条数据
-g：同grep模糊匹配
mysqldumpslow -t 10 mysql-slow.log #显示top10的慢查询语句
mysqldumpslow -s c -t 10 mysql-slow.log #返回10条执行次数最多的SQL，通常也是需要优先优化的地方
2、pt-query-digest工具（推荐）
pt-query-digest 是 Percona Toolkit 中的一个工具，专门用于分析 MySQL 的慢查询日志，并提供详细的查询性能分析报告。
· 安装 pt-query-digest
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-toolkit
· pt-query-digest 命令示例
pt-query-digest mysql-slow.log > slow_log.report #直接分析慢查询日志并将结果输出到文件
pt-query-digest --since=12h mysql-slow.log > slow_log.report #分析最近12个小时的慢查询
pt-query-digest --since '2020-07-07 11:10:00' --until '2020-07-07 11:13:00' mysql.slow #指定时间范围进行分析
· pt-query-digest 分析报告
· overall 部分
统计总览，在这部分中重点关注Overall所展示的数据信息，它包含了指定时间范围内的查询总数、唯一查询数量（也就是有多少个不同的SQL）、QPS、并发数、扫描行数（Rows_examined）和返回行数（Rows_sent）、返回字节数（Bytes_sent）等信息
Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency
Rows sent 200M #这里的200M实际是指行数，1M=100万
Rows affecte 398k #修改的行数，1k=1000
· profile 部分
分组统计结果，即将同类型SQL分组统计的结果，主要有以下指标：
Rank：所有语句的排名，默认按照查询时间从长到短降序排列
Response time：总响应时长与时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象
· 第三部分详细统计结果
列出了上一部分中排行靠前的SQL的执行次数、最大耗时、最小耗时、平均耗时、95%耗时等统计信息
Databases：数据库名
Users：各个用户执行的次数（占比）
Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍
Tables：查询中涉及到的表
Explain：SQL语句
五、MySQL慢查询的优化思路
1、对SQL本身进行优化
从慢日志中获取到具体SQL后，可以结合explain执行计划进行进一步的分析，尤其是没有使用索引的语句，参看《【MySQL运维】使用explain执行计划对SQL进行分析优化》
2、数据拆分、冷热数据优化
如果一张表数据过多，查询性能势必会受到影响，建议对这些大表进行拆分。如果一些大表暂时无法添加有效索引的情况（超大表是因为历史数据不断插入形成的，后面业务需要查询某些特定条件，而这些特定条件区分度又比较低，即便添加索引效率也不会提升太大），比如某系统只需要近一年的数据，但是这个查询条件没办法添加合适的索引，所以可以将之前的数据进行归档，比如按照每月的频率对指定业务数据归档，这样能有效地减少扫描行数，加快 SQL语句的执行时间，也有利于数据管理
3、通过读写分离进行优化
4、提升硬件水平
5、建立应用负责人机制
针对每个库都要有一个相应的负责人，定期将慢日志过滤结果发送给对应研发负责人，促其进行跟踪优化。DBA也要根据慢日志建立追踪机制表，记录每个慢SQL的优化进度、是否可以优化、最终期限等信息。
评论