慢sql处置惩罚流程和常见案例

 头脑导图:

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

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


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


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

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

复制代码

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

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


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

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

复制代码

3. 日志分析：焦点指标与工具

关键指标解读：

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

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

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

复制代码

可视化工具：推荐使用pt-query-digest（Percona Toolkit 组件），天生包罗执行频率、平均耗时、索引使用情况的具体陈诉：

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

复制代码

示例日志片断：

1. # Time: 2025-05-05T15:00:00+08:00
2. # User@Host: app_user[app_user] @ 192.168.1.100 []
3. # Query_time: 2.865912  Lock_time: 0.000045  Rows_sent: 20  Rows_examined: 500000
4. # 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 格式输出）原查询（未加索引）： 

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

复制代码

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

1. {
2.   "query_block": {
3.     "table": {
4.       "table_name": "users",
5.       "access_type": "ALL",  -- 全表扫描
6.       "rows_examined_per_scan": 100000  -- 扫描10万行
7.     }
8.   }
9. }

复制代码

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

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

复制代码

执行计划：

1. {
2.   "query_block": {
3.     "table": {
4.       "table_name": "users",
5.       "access_type": "ref",  -- 索引引用
6.       "rows_examined_per_scan": 1,
7.       "using_index": true  -- 覆盖索引，无需回表
8.     }
9.   }
10. }

复制代码

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


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



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


陷阱 1：Like'%前缀'导致索引失效
错误写法（全表扫描）：

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

复制代码

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

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

复制代码

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

错误写法（索引失效）：

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

复制代码

优化方案：

• 为address添加索引（适合高频查询）：
  

1. ALTER TABLE orders ADD INDEX idx_address (address(20));  -- 前缀索引（MySQL 8支持）

复制代码

• 改用UNION ALL（减少临时表开销）：
  

1. SELECT * FROM orders WHERE user_id = 100
2. UNION ALL
3. SELECT * FROM orders WHERE address LIKE '%上海';

复制代码

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

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

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

复制代码

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

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

复制代码

2. MySQL 8 专属优化技巧



（1）隐蔽索引（测试索引有用性）

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

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

复制代码

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

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

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

复制代码

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

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

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

优化步骤：

• 创建高频字段表user_basic（存放常用字段）：
  

1. CREATE TABLE user_basic (
2.     id BIGINT PRIMARY KEY AUTO_INCREMENT,
3.     username VARCHAR(50) NOT NULL,
4.     email VARCHAR(100) UNIQUE,
5.     created_at DATETIME DEFAULT CURRENT_TIMESTAMP
6. );

复制代码

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

1. CREATE TABLE user_extend (
2.     id BIGINT PRIMARY KEY,
3.     resume TEXT,
4.     certificate JSON,  -- MySQL 8支持原生JSON类型
5.     FOREIGN KEY (id) REFERENCES user_basic(id)
6. );

复制代码

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

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

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

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

1. CREATE TABLE user_order_stats (
2.     user_id BIGINT PRIMARY KEY,
3.     total_amount DECIMAL(10, 2),
4.     order_count INT,
5.     last_update DATE,
6.     INDEX idx_last_update (last_update)  -- 按时间查询索引
7. );
9. -- 替代复杂关联查询（原需3表JOIN）
10. 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：利用覆盖索引减少回表
  

1. -- 先获取主键（减少扫描字段）
2. SELECT id FROM orders ORDER BY create_time LIMIT 100000, 20;
3. -- 再通过主键批量查询（IN操作比子查询高效）
4. SELECT * FROM orders WHERE id IN (100001, 100002, ..., 100020);

复制代码

• 方案 2：记载前次分页 ID（滚动分页）
  

1. -- 基于上次最大ID分页，避免偏移量累积
2. SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

复制代码

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


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

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

复制代码

优化步骤：

• 查询用户 ID 列表：
  

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

复制代码

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

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

复制代码

    3.查询产品名称：

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

复制代码

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


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

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

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

复制代码

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

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

1. ALTER TABLE users ALTER INDEX idx_email AUTO_RECALCULATE DEFAULT ON;

复制代码

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

• 定期审计流程：
  
  
  
  • 逐日通过SHOW GLOBAL STATUS LIKE 'Slow_queries'监控慢查询数量。
    
  • 每周用pt-query-digest天生陈诉，重点优化Rows_examined > 5万或Query_time > 1秒的语句。
    
  
  
• 索引设计三原则：
  
  
  
  • 为高频查询的WHERE/ORDER BY/GROUP BY字段创建索引。
    
  • 连合索引优先包罗过滤性强的字段（如user_id比status更适合作为第一列）。
    
  • 使用EXPLAIN ANALYZE JSON验证索引有用性，克制过分索引（超过 5 个索引的表需评估）。
    
  
  
• 监控与预警：
  
  
  
  • 通过 Prometheus+Grafana 监控com_select、sort_merge_passes（排序合并次数）等指标。
    
  • 当慢查询数量突然增加 50% 时，触发短信 / 邮件预警，快速定位问题。
    
  
  

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



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。