当前位置: 首页 > news >正文

探秘MySQL——排查与调优

文章目录

  • 一、问题排查一:SQL执行出错
  • 二、问题排查二:慢查询
    • 0.几个重要参数
    • 1.配置慢查询日志
      • 命令行配置(重启失效)
      • 修改配置文件(永久生效)
    • 2.查看慢查询日志
    • 3.问题排查1:Look_time耗时
    • 4.问题排查2:索引
    • 5.问题排查3:拆解复杂SQL
  • 三、常见优化问题
    • Q1.解决大SQL文件无法导入的问题
    • Q2.大量执行update语句的优化
    • Q3.分页的实现及优化
    • Q4.回表查询优化
  • 参考

一、问题排查一:SQL执行出错

使用工具: Navicat for MySQL

当执行了一条错误的SQL语句,会显示错误信息,包含了错误码、错误详情。
在这里插入图片描述

错误详情中会显示出错的原因和具体位置,方便我们进行位置的定位和排查。

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from admin' at line 1

一般来说,根据错误详情就能直接判断出错原因,但有时候,可能会出现一些我们从来没遇到过的问题,此时,借助互联网,对问题进行一个检索,并且还要甄别网络上提供的解决方式是否靠谱,可以多多尝试。

二、问题排查二:慢查询

使用工具: 慢查询日志

0.几个重要参数

# 是否开启慢查询日志,默认OFF,开启则设置为 ON
slow_query_log # 慢查询日志文件存储位置
slow_query_log_file # 是否把没有使用到索引的SQL记录到日志中,默认OFF,开启则设置为 ON
log_queries_not_using_indexes # 超过多少秒的查询才会记录到日志中,注意单位是秒
long_query_time 

1.配置慢查询日志

命令行配置(重启失效)

在数据库中执行以下语句:

SET GLOBAL slow_query_log_file = 'D:/setup/mysql8.0.27/log/slow.log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL long_query_time = 0.001;

通过查询看看是否配置成功:


SELECT @@global.slow_query_log_file;

修改配置文件(永久生效)

【先留个坑在这】

2.查看慢查询日志

打开慢查询日志,查看里面的一条记录:

# Time: 2023-03-12T09:32:24.818300Z
# User@Host: root[root] @ localhost [::1]  Id:    11
# Query_time: 0.003975  Lock_time: 0.000116 Rows_sent: 393  Rows_examined: 786
SET timestamp=1678613544;
select * from text
order by application_no;
  • 可以看到以下信息:
    查询用户User
    查询耗费时间Query_time
    锁等待时间Lock_time
    结果集行数Rows_sent
    累积扫描行数Rows_examined
    具体的SQL语句

这些信息可以协助我们排查慢查询问题。

3.问题排查1:Look_time耗时

Look_time耗时反映了事务的并发性能,如果慢查询日志中出现了很多这样的记录,说明是事务的并发性能出现了问题。

  • 执行命令查询MySQL整体的锁状态:
show status like 'innodb_row_lock_%';

如果查询出来的值比较大,就意味着你当前MySQL服务器承载的并发压力过高,此时就急需进行系统的高并发优化。

4.问题排查2:索引

如果不是事务并发问题,那么很有可能是SQL本身有问题,比如可能是索引方面需要优化。

这个问题参考我的另一篇博客:这里

5.问题排查3:拆解复杂SQL

如果一条复杂的SQL出现效率问题,为了准确地定位问题所在,可以采取分解SQL子句的方式进行问题排查。

  • 用explain检查每个SQL子句,初步定位问题;
  • SQL子句逐条合并,每合并一次用explain检查一次。

三、常见优化问题

Q1.解决大SQL文件无法导入的问题

修改max_allowed_packet参数设置,可以通过命令行设置,也可以通过修改配置文件设置,二者的生效时间不同。

Q2.大量执行update语句的优化

优化方向:减少commit次数,每次commit会产生两次磁盘同步(写redo log和写bin log)

  • 优化1:拼接多个update语句为一条语句
    通过case when可以从一定程度上将update语句合并成一句。语句的减少会导致commit次数减少。
  • 优化2:尽可能地把多个update语句放在一个事务中
    一个事务仅commit一次。

Q3.分页的实现及优化

需求:某一页需要查询的记录为表中的第300-350条数据。

优化思路:聚集索引

  • 使用limit:
explain select * from text limit 300,350;

用explain分析一下,会发现,type=all表示全表扫描。效率贼低。

在这里插入图片描述

  • 走聚集索引:
explain select * from text where id>300 limit 50;

type=range表示只检索给定范围的行,因为加入了where子句,其中使用了主键,导致走了聚集索引,效率有所提升。

在这里插入图片描述

  • 放弃limit,全部依靠聚集索引:
explain select * from text where id>300 and id<=350;

这种实现方式有一个问题就是,由于被删除了一部分记录,导致自增主键不连续,这时候就会出现页与页间有记录重叠的情况。
在这里插入图片描述

Q4.回表查询优化

优化思路:联合索引、索引覆盖

看下面一个语句:

explain SELECT date FROM text where application_no>'EP2567834';

在没有设置联合索引的时候,好家伙,直接全表扫描。

在这里插入图片描述

  • 设置非聚集索引再执行:
ALTER TABLE text ADD INDEX(`application_no`);

可以用非聚集索引,为啥没用呢?因为 要回表查询 啊,比全表扫描还慢,要这索引有何用?
在这里插入图片描述

  • 添加联合索引,成功解决回表问题:
ALTER TABLE text ADD INDEX union_index(application_no,date);

这次终于命中了,既不用全表扫描,也不用回表查询,嘿嘿。

在这里插入图片描述

参考

博客
慢查询
mysql配置文件
博客
update优化
分页优化

相关文章:

探秘MySQL——排查与调优

文章目录一、问题排查一&#xff1a;SQL执行出错二、问题排查二&#xff1a;慢查询0.几个重要参数1.配置慢查询日志命令行配置&#xff08;重启失效&#xff09;修改配置文件&#xff08;永久生效&#xff09;2.查看慢查询日志3.问题排查1&#xff1a;Look_time耗时4.问题排查2…...

【9.数据页结构】

概述 InnoDB 的数据是按「数据页」为单位来读写的&#xff0c;也就是说&#xff0c;当需要读一条记录的时候&#xff0c;并不是将这个记录本身从磁盘读出来&#xff0c;而是以页为单位&#xff0c;将其整体读入内存。数据库的 I/O 操作的最小单位是页&#xff0c;InnoDB 数据页…...

演唱会总是抢不到票?教你用Python制作一个自动抢票脚本

人生苦短 我用python 这个大家应该都知道吧&#xff1f; 是中国综合类现场娱乐票务营销平台&#xff0c; 业务覆盖演唱会、 话剧、音乐剧、体育赛事等领域。 如何快速抢票&#xff1f; 那么&#xff0c; 今天带大家用Python来制作一个自动抢票的脚本小程序 本文源码python安…...

【系统开发】WebSocket + SpringBoot + Vue 搭建简易网页聊天室

文章目录一、数据库搭建二、后端搭建2.1 引入关键依赖2.2 WebSocket配置类2.3 配置跨域2.4 发送消息的控制类三、前端搭建3.1 自定义文件websocket.js3.2 main.js中全局引入websocket3.3 App.vue中声明websocket对象3.4 聊天室界面.vue3.5 最终效果一、数据库搭建 很简单的一个…...

Learning C++ No.14【STL No.4】

引言&#xff1a; 北京时间&#xff1a;2023/3/9/12:58&#xff0c;下午两点有课&#xff0c;现在先把引言给搞定&#xff0c;这样就能激励我更早的把这篇博客给写完了&#xff0c;万事开头难这句话还是很有道理的&#xff0c;刚好利用现在昏昏欲睡的时候&#xff0c;把这个没…...

高速PCB设计指南(八)

七、产品内部的电磁兼容性设计 1 印刷电路板设计中的电磁兼容性 1.1 印刷线路板中的公共阻抗耦合问题 数字地与模拟地分开&#xff0c;地线加宽。 1.2 印刷线路板的布局 ※对高速、中速和低速混用时&#xff0c;注意不同的布局区域。 ※对低模拟电路和数字逻辑要分离。…...

什么是腾讯云关系型数据库(MySQL/SQL Server/MariaDB/PostgreSQL详解)

什么是腾讯云关系型数据库&#xff1f;腾讯云关系型数据库提供 MySQL、SQL Server、MariaDB、PostgreSQL详细介绍。腾讯云关系型数据库让您在云中轻松部署、管理和扩展的关系型数据库&#xff0c;提供安全可靠、伸缩灵活的按需云数据库服务。腾讯云关系型数据库提供 MySQL、SQL…...

进程通信相关概念

一、概念 1.1 通信方式有哪些 管道&#xff1a;水管&#xff0c;男纸条放入水管&#xff0c;女看了拿走不回复 消息队列&#xff1a;大盒子&#xff0c;男放入纸条&#xff0c;女看了不拿走&#xff0c;男女都可放 共享内存&#xff1a;直接桌子&#xff0c;男放桌上&#…...

05.Java的运算符

1.运算符计算机的最基本的用途之一就是执行数学运算&#xff0c;比如&#xff1a;int a 10;int b 20;a b;a < b;上述 和 < 等就是运算符&#xff0c;即&#xff1a;对操作数进行操作时的符号&#xff0c;不同运算符操作的含义不同。作为一门计算机语言&#xff0c;Ja…...

轮转数组(力扣189)

轮转数组 题目描述&#xff1a; 给定一个整数数组 nums&#xff0c;将数组中的元素向右轮转 k 个位置&#xff0c;其中 k 是非负数。 示例1&#xff1a; 输入: nums [1,2,3,4,5,6,7], k 3 输出: [5,6,7,1,2,3,4] 解释: 向右轮转 1 步: [7,1,2,3,4,5,6] 向右轮转 2 步: [6,7…...

主流的“对象转换工具”使用示例大全以及性能的对比

目录 前言 源码地址 代码示例 引入依赖 先定两个实体用于转换 定义一个接口让所有转换器都集成 Apache BeanUtils BeanCopier bean-mapping bean-mapping-asm Dozer 自己写get/set JMapper json2json MapStruct&#xff08;推荐&#xff09; ModelMapper OriK…...

分享10个不错的C语言开源项目

今天跟大家分享10个重量级的C语言开源项目&#xff0c;C语言确实经得住考验&#xff1a; Redis&#xff1a;Redis是一个开源的高性能的键值对数据库。它以C语言编写&#xff0c;具有极高的性能和可靠性。 Nginx&#xff1a;Nginx是一个高性能的HTTP和反向代理服务器&#xff0…...

【阅读笔记】JavaScript设计模式与开发实践2--闭包与单例、策略模式

目录闭包与高阶函数Function 扩展函数柯里化函数单例模式透明的单例模式惰性单例策略模式策略模式发展策略模式实现闭包与高阶函数 Array.prototype.sort 接受一个函数当作参数&#xff0c;用户可以自行在该函数内指定排序方式 // 由小到大排序 let res [1, 4, 2].sort((a, …...

设计模式(二十)----行为型模式之责任链模式

1、概述 在现实生活中&#xff0c;常常会出现这样的事例&#xff1a;一个请求有多个对象可以处理&#xff0c;但每个对象的处理条件或权限不同。例如&#xff0c;公司员工请假&#xff0c;可批假的领导有部门负责人、副总经理、总经理等&#xff0c;但每个领导能批准的天数不同…...

数据持久化层--冷热分离

业务场景 有一个系统的主要功能是这样的:它会对接客户的邮件服务器,自动收取发到几个特定客服邮箱的邮件,每收到一封客服邮件,就自动生成一个工单。之后系统就会根据一些规则将工单分派给不同的客服专员处理。 这家媒体集团客户两年多产生了近2000万的工单,工单的操作记…...

Ubuntu16.04系统 VSCode中python开发插件的安装

VSCode中python开发插件的安装 1. python python插件提供了代码分析&#xff0c;高亮&#xff0c;规范化等很多基本功能 2. Python for vscode 3. Python Preview 实时可视化你的代码结果。如果你Leedcode等题时&#xff0c;可以安装这个插件。能为VSCode切换各种主题皮肤…...

buuctf-pwn write-ups (12)

文章目录buu093-wustctf2020_easyfastbuu094-ciscn_2019_es_1buu095-wdb2018_guessbuu096-gyctf_2020_some_thing_excetingbuu097-axb_2019_heapbuu098-oneshot_tjctf_2016buu099-护网杯_2018_gettingstartbuu100-wustctf2020_number_gamebuu101-zctf2016_note2buu093-wustctf2…...

Linux- 系统随你玩之--网络上的黑客帝国

文章目录1、前言2、TCPDump介绍2.1、问题来了&#xff1a; 所有用户都可以采用该命令吗&#xff1f;2.2、抓包原理2.3、特点2.3.1、参数化支持2.2.2、 TCP功能3、 服务器安装Tcpdump3.1、安装3.2、检查安装是否正常。4、tcpdump 命令4.1、常用功能选项4.2、输出内容5、实操5.1、…...

Python每日一练(20230312)

目录 1. 提示用户输入的简单菜单 ★ 2. 字母异位词分组 ★★ 3. 俄罗斯套娃信封问题 ★★★ &#x1f31f; 每日一练刷题专栏 C/C 每日一练 ​专栏 Python 每日一练 专栏 1. 提示用户输入的简单菜单 如果用户选择菜单选项1&#xff0c;提示用户输入1到10之间的整数&a…...

人生又有几个四年

机缘 不知不觉&#xff0c;已经来 csdn 创作四周年啦~ 我是在刚工作不到一年的时候接触 csdn 的&#xff0c;当时在学习 node&#xff0c;对 node 的文件相关的几个 api 总是搞混&#xff0c;本来还想着在传统的纸质笔记本上记一下&#xff0c;但是想想我大学记了好久的笔记本…...

visual studio 2022更改主题为深色

visual studio 2022更改主题为深色 点击visual studio 上方的 工具-> 选项 在选项窗口中&#xff0c;选择 环境 -> 常规 &#xff0c;将其中的颜色主题改成深色 点击确定&#xff0c;更改完成...

镜像里切换为普通用户

如果你登录远程虚拟机默认就是 root 用户&#xff0c;但你不希望用 root 权限运行 ns-3&#xff08;这是对的&#xff0c;ns3 工具会拒绝 root&#xff09;&#xff0c;你可以按以下方法创建一个 非 root 用户账号 并切换到它运行 ns-3。 一次性解决方案&#xff1a;创建非 roo…...

【JavaSE】绘图与事件入门学习笔记

-Java绘图坐标体系 坐标体系-介绍 坐标原点位于左上角&#xff0c;以像素为单位。 在Java坐标系中,第一个是x坐标,表示当前位置为水平方向&#xff0c;距离坐标原点x个像素;第二个是y坐标&#xff0c;表示当前位置为垂直方向&#xff0c;距离坐标原点y个像素。 坐标体系-像素 …...

DeepSeek 技术赋能无人农场协同作业:用 AI 重构农田管理 “神经网”

目录 一、引言二、DeepSeek 技术大揭秘2.1 核心架构解析2.2 关键技术剖析 三、智能农业无人农场协同作业现状3.1 发展现状概述3.2 协同作业模式介绍 四、DeepSeek 的 “农场奇妙游”4.1 数据处理与分析4.2 作物生长监测与预测4.3 病虫害防治4.4 农机协同作业调度 五、实际案例大…...

【数据分析】R版IntelliGenes用于生物标志物发现的可解释机器学习

禁止商业或二改转载&#xff0c;仅供自学使用&#xff0c;侵权必究&#xff0c;如需截取部分内容请后台联系作者! 文章目录 介绍流程步骤1. 输入数据2. 特征选择3. 模型训练4. I-Genes 评分计算5. 输出结果 IntelliGenesR 安装包1. 特征选择2. 模型训练和评估3. I-Genes 评分计…...

听写流程自动化实践,轻量级教育辅助

随着智能教育工具的发展&#xff0c;越来越多的传统学习方式正在被数字化、自动化所优化。听写作为语文、英语等学科中重要的基础训练形式&#xff0c;也迎来了更高效的解决方案。 这是一款轻量但功能强大的听写辅助工具。它是基于本地词库与可选在线语音引擎构建&#xff0c;…...

安宝特案例丨Vuzix AR智能眼镜集成专业软件,助力卢森堡医院药房转型,赢得辉瑞创新奖

在Vuzix M400 AR智能眼镜的助力下&#xff0c;卢森堡罗伯特舒曼医院&#xff08;the Robert Schuman Hospitals, HRS&#xff09;凭借在无菌制剂生产流程中引入增强现实技术&#xff08;AR&#xff09;创新项目&#xff0c;荣获了2024年6月7日由卢森堡医院药剂师协会&#xff0…...

C/C++ 中附加包含目录、附加库目录与附加依赖项详解

在 C/C 编程的编译和链接过程中&#xff0c;附加包含目录、附加库目录和附加依赖项是三个至关重要的设置&#xff0c;它们相互配合&#xff0c;确保程序能够正确引用外部资源并顺利构建。虽然在学习过程中&#xff0c;这些概念容易让人混淆&#xff0c;但深入理解它们的作用和联…...

保姆级【快数学会Android端“动画“】+ 实现补间动画和逐帧动画!!!

目录 补间动画 1.创建资源文件夹 2.设置文件夹类型 3.创建.xml文件 4.样式设计 5.动画设置 6.动画的实现 内容拓展 7.在原基础上继续添加.xml文件 8.xml代码编写 (1)rotate_anim (2)scale_anim (3)translate_anim 9.MainActivity.java代码汇总 10.效果展示 逐帧…...

第一篇:Liunx环境下搭建PaddlePaddle 3.0基础环境(Liunx Centos8.5安装Python3.10+pip3.10)

第一篇&#xff1a;Liunx环境下搭建PaddlePaddle 3.0基础环境&#xff08;Liunx Centos8.5安装Python3.10pip3.10&#xff09; 一&#xff1a;前言二&#xff1a;安装编译依赖二&#xff1a;安装Python3.10三&#xff1a;安装PIP3.10四&#xff1a;安装Paddlepaddle基础框架4.1…...