MySQL 慢查询优化指南

news/2024/7/22 12:54:58/文章来源:https://blog.csdn.net/weixin_54574094/article/details/139253568

MySQL 慢查询优化指南

在现代数据库管理中,性能优化是一个不可忽视的重要环节。尤其是对于高并发、大数据量的应用,慢查询可能会成为系统的性能瓶颈。本文将介绍如何查看和优化 MySQL 的慢查询,帮助你提高数据库性能。

一、什么是慢查询?

慢查询是指执行时间超过指定阈值的 SQL 查询。在 MySQL 中,可以通过设置 long_query_time 参数来定义这个阈值,默认值为 10 秒。通过启用慢查询日志,我们可以记录这些执行时间过长的查询,以便进行分析和优化。

二、启用慢查询日志

首先,需要确保 MySQL 已启用慢查询日志。可以通过以下命令查看当前配置:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

如果未启用,可以通过修改 MySQL 配置文件(通常为 my.cnfmy.ini)来开启慢查询日志,并设置查询时间阈值:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

然后,重启 MySQL 服务使配置生效:

sudo systemctl restart mysql
三、查看慢查询日志

可以通过登录到数据库服务器,使用文本编辑器(如 vinano)查看慢查询日志文件:

sudo vi /var/log/mysql/slow.log

也可以使用以下 SQL 命令在 MySQL 客户端中直接查看慢查询日志的数量:

SHOW GLOBAL STATUS LIKE 'Slow_queries';
四、分析慢查询日志

假设我们在慢查询日志中发现以下记录:

# Time: 2023-05-16T10:12:34.567890Z
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 12.345678  Lock_time: 0.000123 Rows_sent: 100  Rows_examined: 1000000
SET timestamp=1684230754;
SELECT * FROM orders WHERE status = 'PENDING' AND created_at < '2023-05-01' ORDER BY created_at DESC;
五、优化慢查询

我们需要从以下几个方面来优化这条慢查询:

  1. 检查表结构和索引: 查看 orders 表的结构,尤其是索引情况:

    SHOW CREATE TABLE orders;
    

    输出结果:

    CREATE TABLE `orders` (
    `id` int NOT NULL AUTO_INCREMENT,
    `user_id` int NOT NULL,
    `status` varchar(50) NOT NULL,
    `created_at` datetime NOT NULL,
    `total_amount` decimal(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_created_at` (`created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
  2. 添加适当的索引: 通过分析查询条件和排序需求,添加复合索引以提高查询效率:

    ALTER TABLE orders ADD INDEX idx_status_created_at (status, created_at);
    
  3. 使用 EXPLAIN 命令: 执行优化后的查询,查看执行计划:

    EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND created_at < '2023-05-01' ORDER BY created_at DESC;
    

    假设 EXPLAIN 输出如下:

    +----+-------------+--------+------------+-------+---------------+------------------------+---------+------+-------+-------------+
    | id | select_type | table  | partitions | type  | possible_keys | key                    | key_len | ref  | rows  | Extra       |
    +----+-------------+--------+------------+-------+---------------+------------------------+---------+------+-------+-------------+
    |  1 | SIMPLE      | orders | NULL       | ref   | idx_status_created_at | idx_status_created_at | 767     | const| 1000  | Using where |
    +----+-------------+--------+------------+-------+---------------+------------------------+---------+------+-------+-------------+
    

    通过 EXPLAIN 输出,可以看到查询计划使用了我们新添加的索引 idx_status_created_at,并且 typeref,说明索引查找相对高效。

以下是 EXPLAIN 命令输出中 type 列的各个等级及其含义,以表格的形式展示:

Type含义性能示例
system表仅有一行(系统表)。最理想SELECT * FROM dual;
const表中最多有一行匹配查询条件,通常是通过主键或唯一索引来查找。非常快速SELECT * FROM table WHERE primary_key = 1;
eq_ref对每个从表的记录,主表中只有一条匹配记录。良好SELECT * FROM table1 JOIN table2 ON table1.primary_key = table2.foreign_key;
ref对于从表的每一行,从主表中匹配到多行。较好SELECT * FROM table WHERE indexed_column = 'value';
ref_or_null类似于 ref,但还包括了对 NULL 值的检查。较好SELECT * FROM table WHERE indexed_column = 'value' OR indexed_column IS NULL;
index_merge查询使用了多个索引的合并。SELECT * FROM table WHERE indexed_column1 = 'value1' OR indexed_column2 = 'value2';
range只检索表中给定范围的行,使用索引查找。一般SELECT * FROM table WHERE indexed_column BETWEEN 10 AND 20;
index全索引扫描,类似全表扫描,但只扫描索引树。一般SELECT indexed_column FROM table;
all全表扫描。最差SELECT * FROM table;

在实际应用中,通过理解和优化 EXPLAIN 输出中的 type 类型,能够显著提高查询性能。对于性能要求高的查询,应该尽量避免使用 typeallindex,并尽量使用索引以提高查询效率。

  1. 检查查询性能: 再次执行查询,检查执行时间是否明显减少。如果查询性能仍然不理想,可以考虑进一步优化查询逻辑或重新设计表结构。
六、进一步优化建议
  • 优化查询逻辑: 重新评估查询逻辑,确保没有不必要的复杂性。例如,避免在 WHERE 子句中使用不必要的函数调用或复杂表达式。
  • 拆分查询: 如果数据量非常大,可以考虑将查询拆分成多个小查询,分批处理。例如,使用分页技术(LIMIT 和 OFFSET)分批读取数据。
  • 优化表设计: 重新设计表结构,避免过多的复杂性。确保数据存储和访问的高效性。例如,规范化表设计,避免冗余数据。
七、总结

通过启用和分析慢查询日志,我们可以有效地识别和解决 MySQL 慢查询问题。优化慢查询通常需要检查表结构、添加适当的索引、优化查询逻辑,以及必要时重新设计表结构。通过这些步骤,可以显著提高数据库性能,确保系统的高效运行。

希望这篇博客能帮助你理解和优化 MySQL 慢查询。如果你有任何问题或建议,欢迎在评论区留言。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_1054060.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

向npm发布自己写的vue组件,使用vite创建项目

向npm发布自己写的vue组件&#xff0c;使用vite创建项目 创建项目 pnpm create vite输入项目名称 由于我的组件是基于 ant-design-vue和vue的&#xff0c;需要解析.vue文件&#xff0c;我又安装了下面4个。 然后执行 pnpm i安装依赖 vite.config.ts import { defineC…

常见排序算法之插入排序

目录 一、直接插入排序 1.1 什么是插入排序 1.2 代码思路 1.3 C语言源码 二、希尔排序 2.0 插入排序的弊端 2.1 什么是希尔排序&#xff1f; 2.2 排序思路 2.3 C语言源码 一、直接插入排序 1.1 什么是插入排序 插入排序是一种简单直观的排序算法&#xff0c;它通过构…

成都爱尔眼科蔡裕主任解说什么是近视性黄斑病变

近视性黄斑病变&#xff0c;属于黄斑病变的其中一种。 黄斑是眼内一个部位&#xff0c;它位于眼底的后极部&#xff0c;视网膜的中心部&#xff0c;管理着光、形、色。黄斑变性是指由于年龄、遗传、不良环境、慢性光损伤等各种因素的影响&#xff0c;使眼部视网膜处的黄斑发生…

Go 和 Delphi 定义可变参数函数的对比

使用可变参数函数具有灵活性、重用性、简化调用等优点&#xff0c;各个语言有各自定义可变参数函数的方法&#xff0c;也有通用的处理方法&#xff0c;比如使用数组、定义参数结构体、使用泛型等。 这里总结记录一下 go、delphi 的常用的定义可变参数函数的方式&#xff01; 一…

【机器学习系列】使用高斯贝叶斯模型进行数据分类的完整流程

目录 一、导入数据 二、选择特征 三、十折交叉验证 四、划分训练集和测试集 五、训练高斯贝叶斯模型 六、预测测试集 七、查看训练集和测试集上的分数 八、查看混合矩阵 九、输出评估指标 一、导入数据 # 根据商户数据预测其是否续约案例 import pandas #读取数据到 da…

如何部署一套高可用性的医院信息管理系统?基于华为云、SpringBoot、Vue及Jenkins、Gitlab的CI/CD流程

目录 一、项目背景 二、项目架构 三、项目部署流程 1、前端部署 2、后端部署 3、监控与运维 四、项目过程 一、项目背景 随着医疗信息化程度的不断加深&#xff0c;医院信息管理系统的稳定性和可用性成为了医疗机构日常运营的关键。在这个数字化时代&am…

ChatGPT魔法,定制个性化提示词!

扮演Prompt创作者的角色 我想让你成为我的Prompt创作者。你的目标是帮助我创建最佳的Prompt&#xff0c;这个Prompt将由 你ChatGPT使用。 你将遵循以下过程&#xff1a; 1.首先&#xff0c;你会问我Prompt是关于什么的。我会告诉你&#xff0c;但我们需要通过不断的重复来改进…

一些关于深度聚类以及部分对比学习的论文阅读笔记

目录 资料SwAV问题方法方法的创新点为什么有效有什么可以借鉴的地方聚类Multi-crop 代码 PCL代码 Feature Alignment and Uniformity for Test Time Adaptation代码 SimSiam 资料 深度聚类算法研究综述(很赞&#xff0c;从聚类方法和深度学习方法两个方面进行了总结&#xff0…

Linux查看设备信息命令

dmidecode | grep Product Name 查看grub版本号&#xff1a;rpm -qa | grep -i "grub" 客户端操作系统版本&#xff1a; cat /etc/issue cat /etc/redhat-release 处理器品牌及型号&#xff1a; less /proc/cpuinfo |grep model

【Unity入门】认识Unity编辑器

Unity 是一个广泛应用于游戏开发的强大引擎&#xff0c;从 1.0 版本开始到现在&#xff0c;其编辑器的基本框架一直保持稳定。其基于组件架构的设计&#xff0c;使得界面使用起来直观且高效。为了更好地理解 Unity 的界面&#xff0c;我们可以将其比喻为搭建一个舞台。以下是对…

uni微信小程序input框过滤中文字节以及规定以外的符号

问题描述 需求是输入账号只能为手机号、邮箱、字母和数字组成的字符串&#xff0c;那么就是所有大小写字母、数字、以及符号 - _ . 四种。 条件限制 微信小程序无法直接通过type属性实现&#xff0c;type属性中没有专门为只允许英文字母的输入类型。详情见input | uni-ap…

【windows】Total Uninstall:一款功能强大的完全卸载软件

软件介绍 Total Uninstall是一款专业的软件卸载工具&#xff0c;旨在帮助用户彻底地清除计算机上的应用程序&#xff0c;包括与应用程序相关的所有文件和注册表项。以下是Total Uninstall的一些主要功能和特点&#xff1a; 完全卸载&#xff1a;软件可以监视应用程序的安装过程…

使用Django实现WebSocket

文章目录 安装依赖编写Consumer配置路由在模板中使用WebSocket运行应用 WebSocket是一种在单个TCP连接上进行全双工通信的协议&#xff0c;在Web开发中被广泛应用于实时通信和数据推送。本文将介绍如何在Django中使用WebSocket来实现实时通信功能。 安装依赖 首先&#xff0…

基于Java实现震中附近风景区预警可视化分析实践

目录 前言 一、空间数据说明 1、表结构信息展示 2、空间范围查询 二、Java后台开发实现 1、模型层设计与实现 2、控制层设计与实现 三、Leaflet地图开发 1、地震震中位置展示 2、百公里风景区列表展示 3、风景区列表展示 4、附近风景区展示 四、总结 前言 地震这类…

prompt提示词:如何让AI帮你提一个好问题

我们看完一篇文章的时候&#xff0c;有时候发给AI后&#xff0c;不知道如何问AI&#xff0c;不知道问哪些问题&#xff0c;你使用这个提示词&#xff0c;就可以让AI帮你想一个好问题&#xff0c;然后你用AI想好的问题再去问AI 能提出一个好的问题是非常难的 提示词 结合文章…

免费插件集-illustrator插件-Ai插件-文本对象分行

文章目录 1.介绍2.安装3.通过窗口>扩展>知了插件4.功能解释5.总结 1.介绍 本文介绍一款免费插件&#xff0c;加强illustrator使用人员工作效率&#xff0c;进行文本对象分行。首先从下载网址下载这款插件 https://download.csdn.net/download/m0_67316550/87890501&…

【头歌】计算机网络DHCP服务器配置第二关access口配置答案

头歌计算机网络DHCP服务器配置第二关access口配置操作步骤 任务描述 本关任务&#xff1a;创建 vlan &#xff0c;并且将与 pc 机相连接口划分 vlan 。 操作要求 在第一关的拓扑图的基础上&#xff0c;配置交换机&#xff0c;具体要求如下&#xff1a; 1、在特权模式下进入 vla…

Day06-Mybatis

1. Mybatis介绍 2. Mybatis连接数据库并返回数据事例 连接oracle数据的设置方式 spring.application.namespringboot-mybatis spring.datasource.driver-class-nameoracle.jdbc.OracleDriver spring.datasource.urljdbc:oracle:thin:192.168.100.66:1521:orcl spring.datasour…

IDEA升级web项目为maven项目乱码

今天将一个java web项目改造为maven项目。 首先&#xff0c;创建一个新的maven项目&#xff0c;将文件拷贝到新项目中。 其次&#xff0c;将旧项目的jar包&#xff0c;在maven的pom.xml做成依赖 接着&#xff0c;把没有maven坐标的jar包在编译的时候也包含进来 <build>…

redis数据类型之string,list

华子目录 key操作说明SCAN cursor [MATCH pattern] [COUNT count]dump与restorekeys 通配符 示例演示 string说明setbit key offset valuegetbit key offsetsetrange key offset value List结构图相关命令lrem key count valueltrim key count value示例&#xff1a;使用 LTRIM…