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

大家好，这里是编程Cookbook。本文详细介绍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查询性能问题。然而，在高并发环境下，应根据需求合理开启并定期清理日志，避免对数据库性能造成额外负担。

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【前端基础篇】Day 1

【前端基础篇】Day 1

总结&#xff1a; 1. Web标准的构成 2. 基本标签 目录 1. Web标准的构成 2. 基本标签 2.1快捷键 2.2.1标题标签 2.2.2段落和换行标签 2.2.3文本格式化标签 2.2.4div和span标签 2.3.1 图像标签和路径 2.3.2路径 2.3.3超链接标签 2.4注释标签 2.5特殊字符 1. Web标准…
阅读更多...
【复习】Redis

【复习】Redis

数据结构 Redis常见的数据结构 String&#xff1a;缓存对象Hash&#xff1a;缓存对象、购物车List&#xff1a;消息队列Set&#xff1a;点赞、共同关注ZSet&#xff1a;排序 Zset底层&#xff1f; Zset底层的数据结构是由压缩链表或跳表实现的 如果有序集合的元素 < 12…
阅读更多...
我与Linux的爱恋:了解信号量+共享内存+消息队列的应用

我与Linux的爱恋:了解信号量+共享内存+消息队列的应用

​ ​ &#x1f525;个人主页&#xff1a;guoguoqiang. &#x1f525;专栏&#xff1a;Linux的学习 文章目录 信号量共享内存应用---Server&Client通信client.ccserver.ccnamepipe.hppShm.hpp 消息队列——实现Client&ServerCom.hppClient.ccServer.cc 信号量 信号量…
阅读更多...
跟着李沐老师学习深度学习(十六)

跟着李沐老师学习深度学习(十六)

继续学习深度学习&#xff08;十六&#xff09; 继续理解transformer 对于transformer的理解感觉还是云里雾里的&#xff0c;今天又找了一些视频进行一个梳理。 一个浅解 在B站学习发现评论区真的很不错&#xff0c;在沐神讲transformer论文的评论下&#xff0c;有一个评论…
阅读更多...
DeepSeek-R1本地部署保姆级教程

DeepSeek-R1本地部署保姆级教程

一、DeepSeek-R1本地部署配置要求 &#xff08;一&#xff09;轻量级模型 ▌DeepSeek-R1-1.5B 内存容量&#xff1a;≥8GB 显卡需求&#xff1a;支持CPU推理&#xff08;无需独立GPU&#xff09; 适用场景&#xff1a;本地环境验证测试/Ollama集成调试 &#xff08;二&a…
阅读更多...
hbase集群部署

hbase集群部署

1.hbase集群的搭建&#xff08;以及内部逻辑&#xff09; 虽然Hmaster有多个&#xff0c;但是属于热备&#xff0c;起作用的就active上的这个。 部署流程&#xff1a; 因为我配置的hadoop是一个非HA的&#xff0c;所以修改为以下 如果是HA的hadoop一定要做以下这一步。 在启动…
阅读更多...
2.1 链路层发现协议(LLDP)

2.1 链路层发现协议(LLDP)

LLDP&#xff08;Link Layer Discovery Protocol&#xff0c;链路层发现协议&#xff09;是一种用于网络设备的链路层协议&#xff0c;用于在局域网&#xff08;LAN&#xff09;中自动发现和通告设备的信息。LLDP是一个开放标准协议&#xff0c;定义在IEEE 802.1AB中&#xff0…
阅读更多...
3dtiles平移旋转工具制作

3dtiles平移旋转工具制作

3dtiles平移旋转缩放原理及可视化工具实现 背景 平时工作中&#xff0c;通过cesium平台来搭建一个演示场景是很常见的事情。一般来说&#xff0c;演示场景不需要多完善的功能&#xff0c;但是需要一批三维模型搭建&#xff0c;如厂房、电力设备、园区等。在实际搭建过程中&…
阅读更多...
LeetCode 2506 统计相似字符串对的数目

LeetCode 2506 统计相似字符串对的数目

一、问题描述 我们面对的问题是处理一个下标从 0 开始的字符串数组 words。题目中定义了一种字符串相似的规则&#xff1a;如果两个字符串由相同的字符组成&#xff0c;那么就认为这两个字符串是相似的。例如&#xff0c;"abca" 和 "cba" 是相似的&#xf…
阅读更多...
【Deepseek高级使用教程】Deepseek-R1的5种高级进阶玩法，5分钟教会你Deepseek+行业的形式进行工作重构的保姆级教程

【Deepseek高级使用教程】Deepseek-R1的5种高级进阶玩法，5分钟教会你Deepseek+行业的形式进行工作重构的保姆级教程

AI视频生成&#xff1a;小说文案智能分镜智能识别角色和场景批量Ai绘图自动配音添加音乐一键合成视频https://aitools.jurilu.com/ 最近&#xff0c;有各行各业的小伙伴问我&#xff0c;到底应该怎么将deepseek融入进他们自身的工作流呢&#xff1f;其实这个问题很简单。我就以…
阅读更多...
【LeetCode刷题之路】leetcode155.最小栈

【LeetCode刷题之路】leetcode155.最小栈

LeetCode刷题记录 &#x1f310; 我的博客主页&#xff1a;iiiiiankor&#x1f3af; 如果你觉得我的内容对你有帮助&#xff0c;不妨点个赞&#x1f44d;、留个评论✍&#xff0c;或者收藏⭐&#xff0c;让我们一起进步&#xff01;&#x1f4dd; 专栏系列&#xff1a;LeetCode…
阅读更多...
Linux版本控制器Git【Ubuntu系统】

Linux版本控制器Git【Ubuntu系统】

文章目录 **前言**一、版本控制器二、Git 简史三、安装 Git四、 在 Gitee/Github 创建项目五、三板斧1、git add 命令2、git commit 命令3、git push 命令 六、其他1、git pull 命令2、git log 命令3、git reflog 命令4、git stash 命令 七、.ignore 文件1、为什么使用 .gitign…
阅读更多...
2025年信息科学与工程学院科协机器学习介绍——机器学习基本模型介绍

2025年信息科学与工程学院科协机器学习介绍——机器学习基本模型介绍

机器学习 目录 机器学习一.安装基本环境conda/miniconda环境 二.数据操作数据预处理一维数组二维数组以及多维数组的认识访问元素的方法torch中tenson的应用张量的运算张量的广播 三.线性代数相关知识四.线性回归SoftMax回归问题&#xff08;分类问题&#xff09;什么是分类问题…
阅读更多...
pyecharts介绍

pyecharts介绍

文章目录 介绍安装pyecharts基本使用全局配置选项 折线图相关配置地图模块使用柱状图使用 介绍 echarts虑是个由百度开源的数据可视化&#xff0c;凭借着良好的交互性&#xff0c;精巧的图表设计&#xff0c;得到了众多开发者的认可&#xff0c;而Pyhon是门富有表达力的语言&a…
阅读更多...
蓝桥杯——lcd显示

蓝桥杯——lcd显示

一&#xff1a;复制文件 从官方参考文件中复制相关文件&#xff0c;Src中的lcd.c&#xff0c;Inc中的lcd.h&#xff0c;fonts.h复制到自己创建的文件中 二&#xff1a;lcd初始化 在lcd.h中找到四个初始化函数&#xff0c;将其写到main文件中 三&#xff1a;写lcd显示函数 在…
阅读更多...
ligerUI在前端层面对于数据的验证拦截

ligerUI在前端层面对于数据的验证拦截

当你的系统框架采用ligerUI的时候&#xff0c;对于常见的数据验证&#xff0c;我们可以采取ligerUI本身的一些API调用来进行前端的数据验证&#xff0c;例如当你想遍历验证每行数据的时候&#xff1a; var rows liger.get("edit_Mtl").getData(); for (var i 0; i…
阅读更多...
ubuntu-24.04.1-desktop 中的 QT6.7 QtCreator 调试程序

ubuntu-24.04.1-desktop 中的 QT6.7 QtCreator 调试程序

ubuntu-24.04.1-desktop 中的 QT6.7 QtCreator 中调试程序 &#xff11; 启动调试时提示&#xff1a;The kit does not have a debugger set.&#xff12; CDB配置问题&#xff12;.1 选择 工具 -> 外部 -> 配置&#xff12;.2 配置 CDB 路径 &#xff11; 启动调试时提示…
阅读更多...
VSCode ssh远程连接内网服务器（不能上网的内网环境的Linux服务器）的终极解决方案

VSCode ssh远程连接内网服务器（不能上网的内网环境的Linux服务器）的终极解决方案

VSCode ssh远程连接内网服务器&#xff08;不能上网的内网环境的Linux服务器&#xff09; 离线下载vscode-server并安装: 如果远程端不能联网可以下载包离线安装,下载 vscode-server 的 url 需要和 vscode 客户端版本的 commit-id 对应.通过 vscode 面板的帮助->关于可以获…
阅读更多...
【C语言基础】基本数据类型和常量介绍

【C语言基础】基本数据类型和常量介绍

博主未授权任何人或组织机构转载博主任何原创文章&#xff0c;感谢各位对原创的支持&#xff01; 博主链接 博客内容主要围绕&#xff1a; 5G/6G协议讲解 高级C语言讲解 Rust语言讲解 文章目录 基本数据类型和常量介绍一、整数类型 int二、浮点数值类型 f…
阅读更多...
【Deepseek】AnythingLLM + Ollama

【Deepseek】AnythingLLM + Ollama

1. 下载安装 anythingllm 下载地址&#xff1a;https://anythingllm.com/desktop 2. 启动anything 点击 Get started 3.创建工作空间 4.选择Ollama大语言模型 聊天设置 当前只有一个1.5b的模型 下载完成7b模型后 选择后记得点击更新到工作空间&#xff01;&…
阅读更多...
最新文章

Copyright @ 2022~2023