MySQL入门教程（19）Slow Log慢查询日志的配置与分析教程
一、MySQL慢查询的危害
MySQL中的slow log也被称为慢查询日志，它记录了那些已经执行结束但是执行过慢的DML或DDL语句（DDL语句的记录需要单独配置），如果是因为锁等待导致执行过慢不会被记录。由于慢查询通常会扫描大量非目的的数据，会带来不必要的I/O和CPU资源消耗，有可能因为某一个慢SQL拖慢了整个数据库性能，影响到业务的正常使用。在实际业务场景下通常是程序发起数个SQL请求，通过SHOW PROCESSLIST可以捕捉到同时有多个类似SQL请求在执行操作。除了对系统资源带来消耗之外，慢查询执行过程中(比如全表遍历的select)还会阻塞MDL锁的获取，导致在使用XtraBackup全量备份或者对表DDL操作都有可能被阻塞，一旦DDL被阻塞，针对表的请求就会变成串行，后续业务也就无法执行。
当数据库服务器性能出现问题，大多数情况下都是由高并发慢查询导致CPU资源耗尽，所以定期对慢查询日志进行分析可以有效的对数据库服务器性能起到优化作用。在一个慢查询日志中需要着重关注执行次数多、执行时间长、IO过高、未命中索引的SQL。对于慢查询语句的优化通常从高到低逐步进行，比如一开始定义超过5秒的算慢语句，等把这部分5秒的慢语句处理完了再降低到4秒，以此类推。
二、MySQL慢查询的产生
· 查询语句本身的问题，由于SQL过于复杂或者索引覆盖不全导致查询非常慢，也是最常见的问题
· 服务器负载太高，IO、CPU、网络任何一个环节负载异常都会导致原本正常的查询变慢
· 如果慢查询中出现了大量的commit行为，一般都是磁盘负载过高引发的事务提交慢
三、MySQL慢查询的优化思路
1、对SQL本身进行优化
首先要为没有使用索引的语句进行优化，然后尽量避免大事务的产生。比如一个只查询几条数据的SELECT语句，会因为查询的时候存在DELETE删除大量数据的操作，这个时候MVCC在读取的时候需要扫描purge list，导致性能受到影响
2、通过拆分冷热数据优化慢日志
当出现一些大表暂时无法添加有效索引的情况时（超大表是因为历史数据不断插入形成的，后面业务需要查询某些特定条件，而这些特定条件区分度又比较低，即便添加索引效率也不会提升太大），比如某系统只需要近一年的数据，但是这个查询条件没办法添加合适的索引，所以可以将之前的数据进行归档，比如按照每月的频率对指定业务数据归档，这样能有效地减少扫描行数，加快 SQL语句的执行时间，也有利于数据管理
3、通过读写分离进行优化
4、提升硬件水平
5、建立应用负责人机制
针对每个库都要有一个相应的负责人，定期将慢日志过滤结果发送给对应研发负责人，促其进行跟踪优化。DBA也要根据慢日志建立追踪机制表，记录每个慢SQL的优化进度、是否可以优化、最终期限等信息
四、MySQL慢查询日志配置
1、编辑my.cnf文件，在[mysqld]模块下增加以下内容以开启慢查询日志并记录到日志文件中，如果修改为记录到表中则是写入到mysql.slow_log这张表
[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 # SQL至少要扫描达到指定的行数才记录
# log-slow-admin-statements #记录由ALTER TABLE等语句引发的慢查询
# log-slow-slave-statements #记录从服务器产生的慢查询
2、重启MySQL服务使配置生效
service mysqld restart
3、登陆数据库查询配置生效情况
mysql > show variables like '%slow%';
4、执行一个慢查询语句测试
select sleep(3);
5、慢日志可以用文本工具直接查看
rows_examined：可以理解为该SQL扫描了总的行数，该值越高查询必然越慢
rows_sent：从扫描的行数中获取到需要的行数，如果该值很低代表做了很多无用的扫描
五、MySQL慢日志的分析
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工具（推荐使用）
· 安装PTOSC
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部分——分组统计数据
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语句
· 查看执行计划
从慢日志中获取到具体SQL后，可以结合explain执行计划进行进一步的分析，参看《【MySQL运维】使用explain执行计划对SQL进行分析优化》
评论