当前位置: 首页 > 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;但是想想我大学记了好久的笔记本…...

Spark 之 入门讲解详细版(1)

1、简介 1.1 Spark简介 Spark是加州大学伯克利分校AMP实验室&#xff08;Algorithms, Machines, and People Lab&#xff09;开发通用内存并行计算框架。Spark在2013年6月进入Apache成为孵化项目&#xff0c;8个月后成为Apache顶级项目&#xff0c;速度之快足见过人之处&…...

解锁数据库简洁之道:FastAPI与SQLModel实战指南

在构建现代Web应用程序时&#xff0c;与数据库的交互无疑是核心环节。虽然传统的数据库操作方式&#xff08;如直接编写SQL语句与psycopg2交互&#xff09;赋予了我们精细的控制权&#xff0c;但在面对日益复杂的业务逻辑和快速迭代的需求时&#xff0c;这种方式的开发效率和可…...

智能在线客服平台:数字化时代企业连接用户的 AI 中枢

随着互联网技术的飞速发展&#xff0c;消费者期望能够随时随地与企业进行交流。在线客服平台作为连接企业与客户的重要桥梁&#xff0c;不仅优化了客户体验&#xff0c;还提升了企业的服务效率和市场竞争力。本文将探讨在线客服平台的重要性、技术进展、实际应用&#xff0c;并…...

el-switch文字内置

el-switch文字内置 效果 vue <div style"color:#ffffff;font-size:14px;float:left;margin-bottom:5px;margin-right:5px;">自动加载</div> <el-switch v-model"value" active-color"#3E99FB" inactive-color"#DCDFE6"…...

页面渲染流程与性能优化

页面渲染流程与性能优化详解&#xff08;完整版&#xff09; 一、现代浏览器渲染流程&#xff08;详细说明&#xff09; 1. 构建DOM树 浏览器接收到HTML文档后&#xff0c;会逐步解析并构建DOM&#xff08;Document Object Model&#xff09;树。具体过程如下&#xff1a; (…...

3-11单元格区域边界定位(End属性)学习笔记

返回一个Range 对象&#xff0c;只读。该对象代表包含源区域的区域上端下端左端右端的最后一个单元格。等同于按键 End 向上键(End(xlUp))、End向下键(End(xlDown))、End向左键(End(xlToLeft)End向右键(End(xlToRight)) 注意&#xff1a;它移动的位置必须是相连的有内容的单元格…...

Device Mapper 机制

Device Mapper 机制详解 Device Mapper&#xff08;简称 DM&#xff09;是 Linux 内核中的一套通用块设备映射框架&#xff0c;为 LVM、加密磁盘、RAID 等提供底层支持。本文将详细介绍 Device Mapper 的原理、实现、内核配置、常用工具、操作测试流程&#xff0c;并配以详细的…...

MySQL JOIN 表过多的优化思路

当 MySQL 查询涉及大量表 JOIN 时&#xff0c;性能会显著下降。以下是优化思路和简易实现方法&#xff1a; 一、核心优化思路 减少 JOIN 数量 数据冗余&#xff1a;添加必要的冗余字段&#xff08;如订单表直接存储用户名&#xff09;合并表&#xff1a;将频繁关联的小表合并成…...

破解路内监管盲区:免布线低位视频桩重塑停车管理新标准

城市路内停车管理常因行道树遮挡、高位设备盲区等问题&#xff0c;导致车牌识别率低、逃费率高&#xff0c;传统模式在复杂路段束手无策。免布线低位视频桩凭借超低视角部署与智能算法&#xff0c;正成为破局关键。该设备安装于车位侧方0.5-0.7米高度&#xff0c;直接规避树枝遮…...

使用SSE解决获取状态不一致问题

使用SSE解决获取状态不一致问题 1. 问题描述2. SSE介绍2.1 SSE 的工作原理2.2 SSE 的事件格式规范2.3 SSE与其他技术对比2.4 SSE 的优缺点 3. 实战代码 1. 问题描述 目前做的一个功能是上传多个文件&#xff0c;这个上传文件是整体功能的一部分&#xff0c;文件在上传的过程中…...