MySQL数据库——常见慢查询优化方式

本文详细介绍MySQL的慢查询相关概念，分析步骤及其优化方案等。

在这里插入图片描述

文章目录

      • 什么是慢查询日志？
      • 慢查询日志的相关参数
      • 如何启用慢查询日志？
        • 方式一：修改配置文件
        • 方式二：通过命令动态启用
      • 分析慢查询日志
        • 方式一：直接查看日志文件
        • 方式二：使用`EXPLAIN`分析查询
      • 常见的慢查询优化
        • 1. 数据类型优化
        • 2. 索引优化
        • 3. SQL 查询优化
        • 4. 分库分表
      • 慢查询日志的适用场景
      • 慢查询日志的优缺点
      • 总结

什么是慢查询日志？

慢查询日志是MySQL提供的一种日志记录机制，用于记录执行时间超过指定阈值（long_query_time）的SQL语句。通过慢查询日志，可以识别和优化性能较差的SQL查询，是数据库性能调优的重要工具。

  • 关键点：
    • 默认阈值：long_query_time 默认值为 10秒，表示运行时间超过10秒的SQL会被记录。
    • 默认状态：MySQL 默认未开启慢查询日志，需要手动启用。
    • 日志存储方式：支持存储为文件或表。

慢查询日志的相关参数

MySQL慢查询日志的核心参数及其含义如下：

  1. 启用和路径配置：

    • slow_query_log：是否开启慢查询日志，1 表示开启，0 表示关闭。
    • slow-query-log-file：日志文件路径和名称（MySQL 5.6及以上版本）。
    • log-slow-queries：旧版（MySQL 5.6以下）的日志存储路径参数。

  2. 时间阈值：

    • long_query_time：慢查询的时间阈值，单位是秒。运行时间超过该阈值的查询将被记录到慢查询日志中。

  3. 其他参数：

    • log_queries_not_using_indexes：未使用索引的查询也会记录到慢查询日志中，帮助识别潜在的索引问题（可选）。
    • log_output：定义日志的存储方式：
      • 'FILE'：将日志写入文件（默认）。
      • 'TABLE'：将日志记录到 mysql.slow_log 表中。
      • 'FILE,TABLE'：同时使用文件和表存储。

如何启用慢查询日志？

方式一：修改配置文件
  1. 打开 MySQL 配置文件（my.cnf 或 my.ini）。
  2. 添加以下配置：
    slow_query_log = 1
slow_query_log_file = /path/to/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_output = 'FILE'
  3. 重启 MySQL 服务以生效。
方式二：通过命令动态启用

使用 MySQL 提供的全局变量来开启慢查询日志：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL log_output = 'FILE';

注意：动态配置的参数在重启后失效，需将参数写入配置文件以持久化。

分析慢查询日志

方式一：直接查看日志文件

慢查询日志文件以文本格式存储，可以使用 cat、tail 或日志分析工具查看。

方式二：使用EXPLAIN分析查询

EXPLAIN 命令用于模拟优化器的查询执行计划，帮助分析SQL语句的性能问题。
例如：

EXPLAIN SELECT * FROM res_user ORDER BY modifiedtime LIMIT 0,1000;

  • EXPLAIN列说明：

    • table：查询涉及的表。
    • type：访问类型，从高到低依次为：const、eq_ref、ref、range、index、ALL。
    • rows：预计扫描的行数。
    • key：使用的索引。
    • Extra：补充信息，比如是否使用了临时表或文件排序。

  • type 的类型和效率：

    1. ALL：全表扫描，效率最低。
    2. index：全索引扫描。
    3. range：索引范围扫描。
    4. ref：非唯一索引扫描或唯一索引前缀扫描。
    5. eq_ref：唯一索引扫描，效率较高。
    6. const/system：常量查询，效率最高。

常见的慢查询优化

优化 MySQL 的慢查询是提升数据库性能的关键环节。以下是常见的慢查询优化方法，按步骤和具体技术进行详细介绍：

1. 数据类型优化
  • 使用占用空间更小的字段类型：
    • 优先使用 TINYINT、SMALLINT，而非 INT。
    • 固定长度的字符串使用 CHAR，而非 VARCHAR。
    • 使用 TIMESTAMP 而非 DATETIME，减少存储空间。
      • TIMESTAMP 占用 4 字节，而 DATETIME 占用 8 字节。TIMESTAMP 的时间范围为 1970-2038，而 DATETIME 为 1000-9999，TIMESTAMP 更节省空间并且在 UTC 时间格式下自动处理时区转换。
    • 精度要求较高时使用 DECIMAL 或 BIGINT：
      • 如果需要精确的数字存储，特别是涉及到小数的场景，使用 DECIMAL 类型而非 FLOAT 或 DOUBLE。例如，对于要求两位小数的金额字段，可以将值乘以 100 保存为 BIGINT。
2. 索引优化

索引是优化慢查询最常见和高效的方法。以下是索引优化的几种方式：

  • 创建适合的索引：

    • 对 WHERE 子句中频繁使用的列建立索引。
    • 对 GROUP BY、ORDER BY 和 JOIN 操作中涉及的列建立索引。
    CREATE INDEX idx_column_name ON table_name(column_name);

  • 联合索引：
    如果查询中涉及多个条件，可以创建联合索引。注意最左前缀原则。

    CREATE INDEX idx_multi_columns ON table_name(column1, column2);

  • 覆盖索引：
    通过索引覆盖查询的所有字段，减少回表操作。

    SELECT col1, col2 FROM table_name WHERE col1 = 1;

  • 避免冗余索引：
    合理设计索引，避免不必要的重复索引。例如 (a, b) 的索引已经可以覆盖 a 的查询，没必要再单独为 a 创建索引。

3. SQL 查询优化

优化 SQL 查询语句本身是提高性能的重要手段。

  • 避免 SELECT *：
    只查询必要的字段，减少数据传输量。

    SELECT col1, col2 FROM table_name WHERE condition;

  • 避免子查询，改用 JOIN：
    子查询在某些情况下会导致性能下降，特别是嵌套子查询。

    -- 子查询
SELECT * FROM table_name WHERE col1 IN (SELECT col1 FROM other_table);-- 改为 JOIN
SELECT t1.* FROM table_name t1 JOIN other_table t2 ON t1.col1 = t2.col1;

  • 合理使用 LIMIT：
    对分页查询，尽量使用 LIMIT + 游标（id > n）的方法，减少使用LIMIT + OFFSET 的方式，尤其是当 偏移量（OFFSET）非常大时。

    LIMIT + OFFSET 的性能瓶颈：

    • 数据库需要从头开始扫描，跳过 OFFSET 指定的记录。
    • 偏移量越大，查询耗时越长。
    • 即使只返回少量数据，数据库仍需加载并跳过大量无关记录。
      示例：
      -- 查询第 1000000 页，每页 10 条记录
SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 10;
      • 数据库会先找到前 1000000 条记录，跳过它们，然后再返回第 1000000 条后的 10 条记录。
      • 随着 OFFSET 增大，性能会急剧下降。

    优化方案：使用 LIMIT + 游标（id > n）：

    • 通过游标条件 id > n，可以直接定位到需要的记录，避免跳过大量无关记录。

      示例：
      假设表 orders 中的主键是 id，查询从第 1000000 条开始的 10 条记录：

      -- 优化后的查询
SELECT * FROM orders WHERE id > 1000000 ORDER BY id ASC LIMIT 10;
      • 通过 id > 1000000 确定游标位置，直接从符合条件的记录开始扫描。
      • 查询性能与 OFFSET 无关，扫描范围大大缩小。

  • 避免函数操作：
    不要在 WHERE 子句中对列使用函数，会导致索引失效。

        SELECT * FROM table_name WHERE DATE(column_name) = '2023-01-01'; -- 慢SELECT * FROM table_name WHERE column_name >= '2023-01-01' AND column_name < '2023-01-02'; -- 快

  • 减少 OR 的使用：
    OR 通常会导致全表扫描，可以用 UNION 或 IN 代替。

    -- 原始查询：使用 OR，可能导致全表扫描
SELECT * FROM table_name WHERE col1 = 1 OR col1 = 2;-- 优化方式 1：使用 IN，能够高效利用单列索引
SELECT * FROM table_name WHERE col1 IN (1, 2);-- 优化方式 2：使用 UNION，将查询拆分成两个独立的部分
(SELECT * FROM table_name WHERE col1 = 1)
UNION
(SELECT * FROM table_name WHERE col1 = 2);

  • 优化 LIKE 查询：
    LIKE 查询如果以 % 开头会导致全表扫描，因为无法使用索引。可以优化为前缀匹配或使用全文索引。

    示例：

    -- 非优化：前缀为 %，无法使用索引
SELECT * FROM table_name WHERE col1 LIKE '%keyword%';-- 优化：前缀匹配，能够使用索引
SELECT * FROM table_name WHERE col1 LIKE 'keyword%';-- 使用全文索引（适用于大文本字段）
ALTER TABLE table_name ADD FULLTEXT(col1);
SELECT * FROM table_name WHERE MATCH(col1) AGAINST('keyword');
4. 分库分表

分库分表是一种应对大规模数据存储和高并发访问的解决方案。

  • 何时分库分表：
    根据《阿里巴巴 Java 开发手册》的建议，单表行数超过 500 万行或单表容量超过 2GB 时，考虑分库分表。

  • 分库分表的好处：

    • 提升查询效率：通过拆分单表或数据库，将数据分散到多个存储节点上，减少单节点的存储和查询压力。
    • 提升并发性能：多个节点可以同时处理查询或写入操作，分担压力。
    • 减少锁冲突：分库分表后，每个表的并发操作减少，减少锁等待和冲突。

  • 分库分表的方式：

    1. 垂直拆分（按功能分库）：
      按业务模块划分数据库，将不同的业务表存储在不同的库中。

      库1：用户数据（users, profiles）
库2：订单数据（orders, order_items）
库3：商品数据（products, categories）

    2. 水平拆分（按数据分片分库分表）：
      将单表数据按照一定规则（如用户 ID、订单 ID 等）拆分到多个表或库中。

      • 范围分片：根据 ID 范围分配数据。
        orders_0: ID 1-10000
orders_1: ID 10001-20000
      • 哈希分片：对分片键取模，将数据分散到多个库或表中。
        -- 按订单 ID 取模分表
SELECT * FROM orders_hash WHERE MOD(order_id, 4) = 0;

  • 分库分表的注意事项：

    • 尽量在当前架构下优化数据库性能，例如升级硬件、迁移历史数据。
    • 分片键的选择要能有效分散数据，同时能支持大部分查询需求。
    • 使用分布式中间件（如 ShardingSphere、MyCAT）来管理分库分表后的复杂性。

慢查询日志的适用场景

  1. 数据库性能调优
    • 找出执行较慢的查询，优化索引设计或SQL语句。
  2. 排查系统瓶颈
    • 通过 log_queries_not_using_indexes 找出未使用索引的查询，优化数据访问路径。
  3. 数据模型优化
    • 分析慢查询日志，可以评估表设计、字段类型是否合理。

慢查询日志的优缺点

  • 优点：

    • 帮助识别性能瓶颈。
    • 提供查询优化的方向。
    • 支持将日志存储为表，便于后续分析。

  • 缺点：

    • 开启后可能对性能产生一定影响，尤其是高并发场景。
    • 日志文件可能过大，需要定期清理。

总结

慢查询日志是性能调优的重要工具，通过合理的日志配置和日志分析，可以有效发现并优化SQL查询性能问题。然而，在高并发环境下，应根据需求合理开启并定期清理日志，避免对数据库性能造成额外负担。

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

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

相关文章

Qt天气预报系统设计界面布局第四部分左边

Qt天气预报系统设计界面布局第四部分左边

Qt天气预报系统设计 1、第四部分左边的第一部分1.1添加控件1.2修改控件名字 2、第四部分左边的第二部分2.1添加控件2.2修改控件名字 3、第四部分左边的第三部分3.1添加控件3.2修改控件名字 4、对整个widget04l调整 1、第四部分左边的第一部分 1.1添加控件 拖入一个widget&…
阅读更多...
【嵌入式硬件】嵌入式显示屏接口

【嵌入式硬件】嵌入式显示屏接口

数字显示串行接口&#xff08;Digital Display Serial Interface&#xff09; SPI 不过多赘述。 I2C-bus interface 不过多赘述 MIPI DSI MIPI (Mobile Industry Processor Interface) Alliance, DSI (Display Serial Interface) 一般用于移动设备&#xff0c;下面是接口…
阅读更多...
一个在ios当中采用ObjectC和opencv来显示图片的实例

一个在ios当中采用ObjectC和opencv来显示图片的实例

前言 在ios中采用ObjectC编程利用opencv来显示一张图片&#xff0c;并简单绘图。听上去似乎不难&#xff0c;但是实际操作下来&#xff0c;却不是非常的容易的。本文较为详细的描述了这个过程&#xff0c;供后续参考。 一、创建ios工程 1.1、选择ios工程类型 1.2、选择接口模…
阅读更多...
el-input输入框需要支持多输入，最后传输给后台的字段值以逗号分割

el-input输入框需要支持多输入，最后传输给后台的字段值以逗号分割

需求&#xff1a;一个输入框字段需要支持多次输入&#xff0c;最后传输给后台的字段值以逗号分割 解决方案&#xff1a;结合了el-tag组件的动态编辑标签 那块的代码 //子组件 <template><div class"input-multiple-box" idinputMultipleBox><div>…
阅读更多...
[2474].第04节：Activiti官方画流程图方式

[2474].第04节：Activiti官方画流程图方式

我的后端学习大纲 Activiti大纲 1.安装位置&#xff1a; 2.启动&#xff1a;
阅读更多...
用再生龙备份和还原操作系统（三）

用再生龙备份和还原操作系统（三）

续上篇《用再生龙备份和还原操作系统&#xff08;二&#xff09;》 三&#xff0c;用再生龙将镜像文件还原到硬盘 将再生龙工具盘、待还原系统的硬盘&#xff08;与源盘一样大或更大&#xff09;、镜像文件所在磁盘&#xff08;如果是U盘&#xff0c;也可以后插&#xff09;安…
阅读更多...
2025：OpenAI的“七十二变”？

2025：OpenAI的“七十二变”？

朋友们&#xff0c;准备好迎接AI的狂欢了吗&#xff1f;&#x1f680; 是不是跟我一样&#xff0c;每天醒来的第一件事就是看看AI领域又有什么新动向&#xff1f; 尤其是那个名字如雷贯耳的 OpenAI&#xff0c;简直就是AI界的弄潮儿&#xff0c;一举一动都牵动着我们这些“AI发…
阅读更多...
谷粒商城项目125-spring整合high-level-client

谷粒商城项目125-spring整合high-level-client

新年快乐! 致2025年还在努力学习的你! 你已经很努力了&#xff0c;今晚就让自己好好休息一晚吧! 在后端中选用哪种elasticsearch客户端&#xff1f; elasticsearch可以通过9200或者9300端口进行操作 1&#xff09;9300&#xff1a;TCP spring-data-elasticsearch:transport-…
阅读更多...
9.若依-自定义表单构建

9.若依-自定义表单构建

表单构建工具&#xff0c;开发者通过图形界面和拖拽等操作&#xff0c;可以快速构建复杂的表单。 需求&#xff1a;做一些复杂的功能提交&#xff0c;涉及到多张标的数据提交。 自定义一个特殊表单页面 1.拖拉一个布局组件中行容器&#xff0c;然后在组件属性中设置表单删格在…
阅读更多...
Matlab Hessian矩阵计算

Matlab Hessian矩阵计算

文章目录 一、简介二、实现代码三、实现效果一、简介 图像的Hessian矩阵用于描述图像灰度值的二阶导数，可以用来分析图像的局部曲率和变化。例如，在图像边缘检测、特征点检测等任务中，Hessian矩阵能帮助我们识别图像的结构。 Hessian矩阵定义 对于二维图像，Hessian矩阵是由…
阅读更多...
java重装小结

java重装小结

一、Java安装 安装路径 https://www.oracle.com/java/technologies/javase/javas e8-archive-downloads.html 具体类型可参考&#xff1a; Java安装配置-CSDN博客 我在这一步主要碰到的问题就是访问官网报404错误&#xff0c;可参考&#xff1a; 在oracle官网下载资源显示…
阅读更多...
4G报警器WT2003H-16S低功耗语音芯片方案开发-实时音频上传

4G报警器WT2003H-16S低功耗语音芯片方案开发-实时音频上传

一、引言 在当今社会&#xff0c;安全问题始终是人们关注的重中之重。无论是家庭、企业还是公共场所&#xff0c;都需要一套可靠的安全防护系统来保障人员和财产的安全。随着科技的飞速发展&#xff0c;4G 报警器应运而生&#xff0c;为安全防范领域带来了全新的解决方案。…
阅读更多...
工业5G路由器让无人机数据传输 “飞” 起来

工业5G路由器让无人机数据传输 “飞” 起来

无人机上搭载5G通信模块&#xff0c;该模块与工业5G路由器通过5G网络建立连接。无人机的飞控系统、传感器以及摄像头等设备采集到的数据&#xff0c;如飞行姿态、高度、速度、环境图像、温度湿度等&#xff0c;经过编码、加密、调制等处理后转换为适合5G网络传输的信号形式。 …
阅读更多...
Spring Certified Professional 2024 (2V0-72.22)

Spring Certified Professional 2024 (2V0-72.22)

关于认证 Spring Certified Professional (2V0-72.22) 认证可证明您在 Spring Framework 方面的专业知识&#xff0c;Spring Framework 是构建企业级 Java 应用程序的领先平台。此认证在全球范围内得到认可&#xff0c;并证明您在 Spring 的各个方面都具有熟练程度&#xff0c;…
阅读更多...
深信服云桌面系统的终端安全准入设置

深信服云桌面系统的终端安全准入设置

深信服的云桌面系统在默认状态下没有终端的安全准入设置&#xff0c;这也意味着同样的虚拟机&#xff0c;使用云桌面终端或者桌面套件都可以登录&#xff0c;但这也给系统带来了一些安全隐患&#xff0c;所以&#xff0c;一般情况下需要设置终端的安全准入策略&#xff0c;防止…
阅读更多...
通过 4 种方法将数据从 OnePlus 传输到Android

通过 4 种方法将数据从 OnePlus 传输到Android

概括 由于它们是不同的品牌&#xff0c;因此将数据从 OnePlus 传输到Android是否很困难&#xff1f;也许您可以从这篇介绍 OnePlus 到Coolmuster Android数据传输的 4 个实用解决方案的文章中获得帮助。学习完它们后&#xff0c;您将有一个顺利的转移过程&#xff0c;所以为什…
阅读更多...
springboot534售楼管理系统(论文+源码)_kaic

springboot534售楼管理系统(论文+源码)_kaic

摘 要 现代经济快节奏发展以及不断完善升级的信息化技术&#xff0c;让传统数据信息的管理升级为软件存储&#xff0c;归纳&#xff0c;集中处理数据信息的管理方式。本售楼管理系统就是在这样的大环境下诞生&#xff0c;其可以帮助管理者在短时间内处理完毕庞大的数据信息&am…
阅读更多...
自学记录：鸿蒙5使用ArkTS和ArkUI实现Live View功能

自学记录：鸿蒙5使用ArkTS和ArkUI实现Live View功能

这次&#xff0c;我决定挑战一个全新的模块——Live View Kit&#xff0c;它提供了实况窗的创建、更新和管理功能。作为API 13的全新特性&#xff0c;我想用它开发一个智能餐厅的点餐和取餐提醒功能。 这篇文章不仅是我的学习记录&#xff0c;也是我探索HarmonyOS Next API 13的…
阅读更多...
git的全通路线介绍

git的全通路线介绍

一、关系 1.git是代码版本管理工具&#xff0c;即可将项目切换到任意版本。 2.github与gitee是基于git技术构建的远程仓库网站。github是国外建立的&#xff0c;资源更丰富&#xff1b;gitee是国内建立的&#xff0c;免费功能更多。 3.gitlab与github类似&#xff0c;只不过…
阅读更多...
新年快乐

新年快乐

今天是元旦&#xff0c;也是2025年的第一天&#xff0c;是旧的一年的结束&#xff0c;是新的一年的开始。 回顾2024年这一年&#xff0c;认认真真&#xff0c;勤勤恳恳&#xff0c;做出了一些成绩。 罗列一下2024年一年的里程表&#xff1a; 一月份&#xff0c;一边上班&…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023