慢查询分析调优工具~mysqldumpslow

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 在日常的业务开发中,MySQL出现慢查询是很常见的,要么说明你家产品的增长性很好,要么就是你的SQL写的太烂了。所以对慢查询SQL进行分析和优化很重要,其中mysqldumpslow是MySQL服务自带的一款很好的分析调优工具。

MySQL慢查询日志

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

查看慢查询设置情况

show variables like '%slow_query_log%';
* slow_query_log //是否开启,默认关闭,建议调优时才开启
* slow_query_log_file //慢查询日志存放目录

慢查询分析调优工具~mysqldumpslow

如何开启慢查询日志记录

3.1 命令开启

set global slow_query_log =1; //只对当前会话生效,重启失效

3.2 配置文件开启

vim my.cnf
在[mysqld]下添加:
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/zdj-slow.log
重启MySQL服务

慢查询分析调优工具~mysqldumpslow

哪些SQL会记录到慢查询日志

show variables like 'long_query_time%';//查看阀值(大于),默认10s

慢查询分析调优工具~mysqldumpslow

如何设置查询阀值

4.1 命令设置

set global long_query_time = 3 //设置慢查询阀值
备注:
另外开一个session或重新连接 才会看到变化

慢查询分析调优工具~mysqldumpslow

4.2 配置文件设置

vim my.cnf
在[mysqld]下添加:
long_query_time = 3
log_output = FILE
重启MySQL服务

如何把未使用索引的SQL记录写入慢查询日志

show variables like 'log_queries_not_using_indexes'; //查看设置,默认关闭
set global log_queries_not_using_indexes = on; //设置

慢查询分析调优工具~mysqldumpslow

模拟数据

select sleep(4);//睡眠4s再执行
show global status like '%Slow_queries%';//查看慢查询条数

慢查询分析调优工具~mysqldumpslow

日志分析工具:mysqldumpslow

mysqldumpslow [ OPTS... ] [ LOGS... ] //命令行格式
mysqldumpslow -h
Option h requires an argument
ERROR: bad option

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
-s 表示按照何种方式排序
    c 访问次数
    l 锁定时间
    r 返回记录
    t 查询时间
    al 平均锁定时间
    ar 平均返回记录数
    at  平均查询时间
-t 返回前面多少条数据
-g 后边搭配一个正则匹配模式,大小写不敏感

几个常用命令

得到返回记录集最多的10条SQL:
mysqldumpslow -s r -t  10 /var/lib/mysql/695f5026f0f6-slow.log
得到访问次数最多的10条SQL:
mysqldumpslow -s r -t  10 /var/lib/mysql/695f5026f0f6-slow.log
得到按照时间排序的前10条里面含有左连接的SQL:
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/695f5026f0f6-slow.log
也支持管道符命令
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/695f5026f0f6-slow.log | more //分页显示

慢查询日志记录内容

cat /var/lib/mysql/695f5026f0f6-slow.log //查看慢查询日志
-- 执行SQL时间
# Time: 2019-12-31T05:54:23.893042Z
-- 执行SQL的主机信息
# User@Host: root[root] @ localhost []  Id:    40
-- SQL的执行信息
# Query_time: 4.013664  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
-- SQL执行时间
SET timestamp=1577771659;
-- SQL内容
select sleep(4);

慢查询分析调优工具~mysqldumpslow

再结合explain关键字进一步分析优化

请阅读此篇:一张图彻底搞定 explain

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
存储 监控 C语言
西门子S7-1200编程实例,关断延迟定时器指令如何使用?
在西门子S7-1200中有四种类型的定时器:TON接通延迟定时器、TONR保持型接通延迟定时器、TOF关断延迟定时器、TP脉冲定时器。
西门子S7-1200编程实例,关断延迟定时器指令如何使用?
|
11月前
|
安全 网络协议 网络安全
Python Socket编程大揭秘:从菜鸟到黑客的进阶之路,你准备好了吗?
【10月更文挑战第4天】在编程领域,Python Socket编程犹如一把开启网络世界的钥匙,带领开发者从简单数据传输迈向复杂应用构建。本文将引导你从零开始,逐步掌握Socket编程的核心技巧,包括基本概念、TCP服务器与客户端的搭建、并发处理及异常管理、SSL/TLS加密通信,直至深入了解网络协议与安全漏洞。通过实战演练与理论学习,助你成为驾驭网络世界的高手。
114 1
|
监控 Android开发 开发者
Android经典面试题之实战经验分享:如何简单实现App的前后台监听判断
本文介绍在Android中判断应用前后台状态的两种方法:`ActivityLifecycleCallbacks`和`ProcessLifecycleOwner`。前者提供精细控制,适用于需针对每个Activity处理的场景;后者简化前后台检测,适用于多数应用。两者各有优劣:`ActivityLifecycleCallbacks`更精确但复杂度高;`ProcessLifecycleOwner`更简便但可能在极端场景下略有差异。根据应用需求选择合适方法。
298 2
|
开发框架 .NET C#
如何判断一个 Dot Net 程序是 32 位还是 64 位?
如何判断一个 Dot Net 程序是 32 位还是 64 位?
|
存储 前端开发 搜索推荐
React——简便获取经纬度信息
React——简便获取经纬度信息
|
并行计算 算法 量子技术
探索未来:量子计算在现代科技中的应用与挑战
随着科技的不断进步,量子计算作为一种新兴技术,其潜力和影响力日益凸显。本文将深入探讨量子计算的核心原理、当前技术发展现状以及面临的主要挑战。通过分析量子计算在特定领域的应用案例,本文旨在提供一个全面的视角,以理解量子计算如何塑造未来的科技格局。
|
编译器 C++
fmt文本格式库的源码下载编译(Win10+VS2022)
fmt文本格式库的源码下载编译(Win10+VS2022)
687 0
|
算法
m基于PSO粒子群优化的LDPC码OMS译码算法最优偏移参数计算和误码率matlab仿真
MATLAB2022a仿真实现了Offset Min-Sum (OMS)译码算法与粒子群优化(PSO)结合,以优化偏移参数,提升LDPC码解码性能。PSO通过迭代寻找最小化误码率(BER)的最佳偏移量。核心程序运用PSO进行参数更新和适应度函数(BER)评估,最终在不同信噪比下展示OMS解码性能,并保存结果。
256 0
|
XML 数据格式 Python
Python生成XML文件
Python生成XML文件
291 0
|
Java 开发工具 git
Spring Cloud Config 分布式配置中心实战详解
Spring Cloud Config 分布式配置中心实战详解
307 0