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

慢sql处理流程和常见案例

news 2025/5/8 5:13:11

 思维导图:

 

  在 MySQL 数据库管理中，慢查询是影响系统性能的常见痛点。随着 MySQL 8 版本的普及，其新增特性（如 CTE、隐藏索引、JSON 格式执行计划等）为慢查询优化提供了更强大的工具。本文结合 MySQL 8 的特性，通过代码示例详解慢查询的定位、分析与优化全流程，从而提高数据库的性能。

一、开启慢查询日志：精准捕获性能瓶颈

1. 配置文件永久开启（推荐生产环境）

修改 MySQL 8 的配置文件my.cnf（Linux）或my.ini（Windows），添加以下核心配置：修改后需重启 MySQL 服务使配置生效。

[mysqld]
slow_query_log = 1                # 启用慢查询日志（8.0+默认关闭）
slow_query_log_file = /var/log/mysql/mysql-slow.log  # 日志文件路径
long_query_time = 1.0             # 慢查询阈值（超过1秒记录，建议根据业务调整）
log_queries_not_using_indexes = 1 # 记录未使用索引的查询（关键优化线索）
log_slow_admin_statements = 1     # 记录慢管理语句（如ALTER TABLE，8.0新增）

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。如图所示：

 

2. 动态配置（临时调试，无需重启）

通过 SQL 命令实时开启慢日志（需SUPER权限）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 阈值设为1秒
SET GLOBAL log_queries_not_using_indexes = ON;

3. 日志分析：核心指标与工具

关键指标解读：

  • Query_time：查询执行时间（精确到微秒），直接反映慢查询严重程度。
  • Rows_examined：扫描的行数，若远大于Rows_sent（返回行数），说明存在大量无效扫描。
  • Full_scan：标记是否全表扫描（Yes表示未使用索引）。

分析工具：
原生工具：使用mysqldumpslow过滤日志（语法与 5.5 兼容）：
 

# 按耗时排序，获取最慢的5条查询 
mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log # 按扫描行数排序，定位扫描行数最多的查询 
mysqldumpslow -s r -t 5 /var/log/mysql/mysql-slow.log

可视化工具：推荐使用pt-query-digest（Percona Toolkit 组件），生成包含执行频率、平均耗时、索引使用情况的详细报告：

pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_analysis.txt

示例日志片段：

# Time: 2025-05-05T15:00:00+08:00
# User@Host: app_user[app_user] @ 192.168.1.100 []
# Query_time: 2.865912  Lock_time: 0.000045  Rows_sent: 20  Rows_examined: 500000
# EXPLAIN for: SELECT * FROM orders WHERE status = 'processing' LIMIT 20;

 问题：扫描 50 万行仅返回 20 行，status字段未使用索引，触发全表扫描。

二、用 EXPLAIN ANALYZE 深度分析执行计划（MySQL 8 增强特性）

MySQL 8 引入EXPLAIN ANALYZE命令，可获取更精确的执行统计信息（需开启optimizer_switch中的derived_merge=off以避免优化器合并子查询）。核心关注字段：

1. type（连接类型，效率优先级）

类型说明优化目标
system单表且仅有一行数据（特殊const）理想状态，无需优化
const主键 / 唯一索引精准匹配优先通过主键 / 唯一索引查询
range索引范围查询（如BETWEEN/IN）合理使用索引，无需强制优化
ALL全表扫描（必须优化！）添加索引或改写查询条件

2. key与key_len

  • key=NULL：未使用索引，需检查WHERE条件是否触发索引失效。
  • key_len：索引使用的字节长度，反映是否完全使用联合索引（如(user_id, order_date)的key_len=8表示仅使用user_id列）。

3. Extra（关键优化信号）

  • Using filesort：文件排序（需通过索引覆盖ORDER BY字段）。
  • Using temporary：临时表（分组 / 排序时产生，尽量通过索引避免）。
  • Using index：覆盖索引（理想状态，无需回表）。


案例：

全表扫描优化（MySQL 8 专属 JSON 格式输出）原查询（未加索引）： 

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

JSON 执行计划片段（简化后）：

{"query_block": {"table": {"table_name": "users","access_type": "ALL",  -- 全表扫描"rows_examined_per_scan": 100000  -- 扫描10万行}}
}

优化后（添加索引并使用覆盖索引）：

ALTER TABLE users ADD INDEX idx_email_cover (email, name, phone);  -- 覆盖索引
EXPLAIN ANALYZE SELECT name, phone FROM users WHERE email = 'user@example.com';

执行计划：

{"query_block": {"table": {"table_name": "users","access_type": "ref",  -- 索引引用"rows_examined_per_scan": 1,"using_index": true  -- 覆盖索引，无需回表}}
}

效果：扫描行数从 10 万降至 1，查询时间从 2.5 秒缩短至 0.01 秒。

三、索引优化：MySQL 8 的新特性与经典规则

1. 避开索引失效的 5 大 “陷阱”

陷阱 1：Like'%前缀'导致索引失效

错误写法（全表扫描）：

SELECT * FROM products WHERE name LIKE '%笔记本';  -- 以%开头，索引失效

优化（前缀匹配，索引有效）：

SELECT * FROM products WHERE name LIKE '笔记本%';  -- 匹配“笔记本”开头的字符串

陷阱 2：OR条件混合无索引列

错误写法（索引失效）：

SELECT * FROM orders WHERE user_id = 100 OR address LIKE '%上海';  -- user_id有索引，address无索引

优化方案：

  • 为address添加索引（适合高频查询）：
ALTER TABLE orders ADD INDEX idx_address (address(20));  -- 前缀索引（MySQL 8支持）

  • 改用UNION ALL（减少临时表开销）：
SELECT * FROM orders WHERE user_id = 100
UNION ALL
SELECT * FROM orders WHERE address LIKE '%上海';

陷阱 3：多列索引未遵循 “最左匹配”

索引定义：ALTER TABLE sales ADD INDEX idx_date_prod (order_date, product_id);
有效查询（使用索引）：

-- 仅用第一列（范围查询）
SELECT * FROM sales WHERE order_date > '2025-01-01';
-- 用前两列（精准匹配）
SELECT * FROM sales WHERE order_date = '2025-01-01' AND product_id = 101;

无效查询（跳过第一列，索引失效）：

SELECT * FROM sales WHERE product_id = 101;  -- 仅用第二列，不使用索引

2. MySQL 8 专属优化技巧

（1）隐藏索引（测试索引有效性）

通过INVISIBLE关键字临时隐藏索引，测试其对查询的影响：

ALTER TABLE users ALTER INDEX idx_email INVISIBLE;  -- 隐藏索引
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';  -- 观察是否走全表扫描
ALTER TABLE users ALTER INDEX idx_email VISIBLE;  -- 恢复索引

（2）降序索引（优化倒序排序）

MySQL 8 支持降序索引，避免DESC排序时的文件排序：

ALTER TABLE orders ADD INDEX idx_order_date_desc (order_date DESC);
-- 优化后无需文件排序
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

四、数据库结构优化：从设计层减少查询压力

1. 拆分大表：分离高频与低频字段

场景：user_info表包含 30 个字段，其中resume（简历）和certificate（证书）字段极少使用，导致全表扫描缓慢。

优化步骤：

  1. 创建高频字段表user_basic（存放常用字段）：
CREATE TABLE user_basic (id BIGINT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

 

    2.创建低频字段表user_extend（通过外键关联）：

CREATE TABLE user_extend (id BIGINT PRIMARY KEY,resume TEXT,certificate JSON,  -- MySQL 8支持原生JSON类型FOREIGN KEY (id) REFERENCES user_basic(id)
);

效果：高频查询（如登录、用户列表）仅访问user_basic，扫描行数减少 60%。

2. 中间表优化复杂关联查询

场景：频繁统计 “用户近 30 天订单金额”，需关联users、orders、order_items三张表，执行时间超过 3 秒。


优化方案：创建统计中间表user_order_stats，每日定时同步数据：

CREATE TABLE user_order_stats (user_id BIGINT PRIMARY KEY,total_amount DECIMAL(10, 2),order_count INT,last_update DATE,INDEX idx_last_update (last_update)  -- 按时间查询索引
);-- 替代复杂关联查询（原需3表JOIN）
SELECT total_amount FROM user_order_stats WHERE user_id = 123 AND last_update = CURDATE();

效果：查询时间从 3 秒降至 0.1 秒，消除多表 JOIN 开销。

五、实战技巧：处理高频慢查询场景

1. 深度分页优化（LIMIT offset, size性能问题）

问题：LIMIT 100000, 20需扫描 100020 行，丢弃前 100000 行，效率极低。

MySQL 8 优化方案：

  • 方案 1：利用覆盖索引减少回表
-- 先获取主键（减少扫描字段）
SELECT id FROM orders ORDER BY create_time LIMIT 100000, 20;
-- 再通过主键批量查询（IN操作比子查询高效）
SELECT * FROM orders WHERE id IN (100001, 100002, ..., 100020);

  • 方案 2：记录上次分页 ID（滚动分页）
-- 基于上次最大ID分页，避免偏移量累积
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

 

2. 分解关联查询：应用层组装结果（适合高并发）

原复杂查询（3 表 JOIN，执行时间 2 秒）：

SELECT u.username, o.order_id, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.country = 'China' AND o.status = 'paid';

优化步骤：

  1. 查询用户 ID 列表：
SELECT id, username FROM users WHERE country = 'China';  -- 单表查询，0.05秒

     2. 查询订单 ID 与产品 ID：

SELECT order_id, product_id FROM orders WHERE user_id IN (1,2,3) AND status = 'paid';  -- 0.1秒

    3.查询产品名称：

SELECT product_id, product_name FROM products WHERE product_id IN (101, 102);  -- 0.03秒

总耗时降至 0.18 秒，相比原查询提升 10 倍以上。

六、MySQL 8 新增特性助力优化

1. CTE（公共表表达式）简化复杂子查询

-- 原嵌套子查询（可读性差，易触发全表扫描）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE score > 80);-- 改用CTE（清晰且便于优化器分析）
WITH high_score_users AS (SELECT id FROM users WHERE score > 80
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM high_score_users);

2. 索引统计信息自动更新（8.0.17+）

通过AUTO_RECALCULATE参数自动更新索引统计信息，避免因统计信息陈旧导致的执行计划偏差：

ALTER TABLE users ALTER INDEX idx_email AUTO_RECALCULATE DEFAULT ON;

七、最佳实践：建立慢查询优化闭环

  1. 定期审计流程：

    • 每日通过SHOW GLOBAL STATUS LIKE 'Slow_queries'监控慢查询数量。
    • 每周用pt-query-digest生成报告，重点优化Rows_examined > 5万或Query_time > 1秒的语句。

  2. 索引设计三原则：

    • 为高频查询的WHERE/ORDER BY/GROUP BY字段创建索引。
    • 联合索引优先包含过滤性强的字段（如user_id比status更适合作为第一列）。
    • 使用EXPLAIN ANALYZE JSON验证索引有效性，避免过度索引（超过 5 个索引的表需评估）。

  3. 监控与预警：

    • 通过 Prometheus+Grafana 监控com_select、sort_merge_passes（排序合并次数）等指标。
    • 当慢查询数量突然增加 50% 时，触发短信 / 邮件预警，快速定位问题。

总结

MySQL 8 的慢查询优化需要结合版本特性与经典优化理论，从 “日志分析→执行计划诊断→索引优化→结构调整” 四个层面逐层深入。通过EXPLAIN ANALYZE的精准分析、覆盖索引的合理设计、以及大表拆分等架构优化，多数慢查询问题可迎刃而解。记住：优化的核心是让数据库 “少干活”—— 减少扫描行数、避免临时表与文件排序、利用索引覆盖查询。持续迭代优化策略并结合监控体系，才能确保 MySQL 在高并发场景下稳定高效运行。

http://www.xdnf.cn/news/286561.html

相关文章：

  • Webug4.0靶场通关笔记16- 第20关文件上传(截断上传)
  • 数据结构——算法复杂度
  • 部署GM DC Monitor 一体化监控预警平台
  • Python 整理3种查看神经网络结构的方法
  • 3DGS-slam:splatam公式
  • 开源模型应用落地-qwen模型小试-Qwen3-8B-推理加速-vLLM（一）
  • Git 标签管理
  • 【STM32 学习笔记】GPIO输入与输出
  • Scrapy分布式爬虫实战：高效抓取的进阶之旅
  • 【NLP】30. 深入理解 In-Context Learning 的核心机制与策略
  • PrivKV: Key-Value Data Collection with Local Differential Privacy论文阅读
  • vue+element 导航 实现例子
  • HarmonyOS Device Connector（hdc）
  • linux 中inotify与inode的关系是什么？
  • PandasAI：对话式数据分析新时代
  • 实战设计模式之中介者模式
  • 基于Boost库、Jsoncpp、cppjieba、cpp-httplib等构建Boost搜索引擎
  • Lua 元表和元方法
  • 重名导致does not name a type
  • 青少年编程与数学 02-018 C++数据结构与算法 25课题、图像处理算法
  • 科学养生，开启健康生活新篇章
  • [machine learning] Transformer - Attention (四)
  • ModBus协议详解：从基础概念到C#实现RTU与TCP通讯
  • Spring Boot 集成 Solr 的详细步骤及示例
  • C# WPF 布局
  • 算法笔记.约数个数
  • 基于muduo库实现高并发服务器
  • Nginx核心功能2
  • Linux：权限的理解
  • 健康养生：从生活点滴启航