news 2026/2/15 4:07:00

慢SQL排查与优化实战：从定位到根治

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
慢SQL排查与优化实战：从定位到根治

线上告警：接口响应超时。一查，又是慢SQL。

这种场景太常见了。数据库是后端最常见的性能瓶颈，而慢SQL是罪魁祸首。这篇把我排查慢SQL的套路整理出来。

第一步：找到慢SQL

1. 开启慢查询日志

-- 查看是否开启SHOWVARIABLESLIKE'slow_query%';SHOWVARIABLESLIKE'long_query_time';-- 动态开启（重启失效）SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;-- 超过1秒算慢SETGLOBALslow_query_log_file='/var/log/mysql/slow.log';

永久生效写my.cnf：

[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1 # 没用索引的也记录

2. 分析慢查询日志

# 用mysqldumpslow分析mysqldumpslow-st-t10/var/log/mysql/slow.log# -s t: 按查询时间排序# -t 10: 显示前10条# 输出示例# Count: 156 Time=3.21s (500s) Lock=0.00s (0s) Rows=1000.0 (156000)# SELECT * FROM orders WHERE user_id = N AND status = N

3. 实时查看正在执行的SQL

-- 查看当前进程SHOWPROCESSLIST;SHOWFULLPROCESSLIST;-- 显示完整SQL-- 看哪个SQL执行时间长SELECT*FROMinformation_schema.processlistWHEREcommand!='Sleep'ORDERBYtimeDESC;-- 杀掉卡住的查询KILL进程ID;

4. performance_schema分析

-- 开启（MySQL 5.6+默认开启）-- 查询最耗时的SQLSELECTDIGEST_TEXT,COUNT_STARASexec_count,SUM_TIMER_WAIT/1000000000000AStotal_time_sec,AVG_TIMER_WAIT/1000000000ASavg_time_ms,SUM_ROWS_EXAMINEDASrows_examinedFROMperformance_schema.events_statements_summary_by_digestORDERBYSUM_TIMER_WAITDESCLIMIT10;

第二步：分析执行计划

找到慢SQL后，用EXPLAIN分析。

EXPLAIN基本用法

EXPLAINSELECT*FROMordersWHEREuser_id=123ANDstatus=1;

输出关键字段：

字段含义关注点
type访问类型ALL全表扫描要优化
key实际用的索引NULL说明没用索引
rows预估扫描行数越小越好
Extra额外信息Using filesort、Using temporary要注意

type类型（从好到差）

system > const > eq_ref > ref > range > index > ALL - const: 主键或唯一索引等值查询，最多一条 - eq_ref: 多表join时，被驱动表用主键/唯一索引 - ref: 普通索引等值查询 - range: 索引范围扫描 - index: 全索引扫描（比ALL好，但也不理想） - ALL: 全表扫描，必须优化

真实案例分析

-- 问题SQLEXPLAINSELECT*FROMordersWHEREcreate_time>'2024-01-01'ANDstatus=1ORDERBYidDESCLIMIT100;-- 输出+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+|1|SIMPLE|orders|ALL|idx_create|NULL|NULL|NULL|500000|Usingwhere;Usingfilesort|+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+

问题：

  1. type=ALL：全表扫描
  2. key=NULL：没用索引
  3. Using filesort：额外排序

第三步：索引优化

1. 联合索引的最左前缀原则

-- 有索引 (a, b, c)-- 能用上索引WHEREa=1WHEREa=1ANDb=2WHEREa=1ANDb=2ANDc=3-- 用不上或部分用上WHEREb=2-- 不能用（跳过了a）WHEREa=1ANDc=3-- 只用到a（跳过了b）WHEREb=2ANDc=3-- 不能用

2. 覆盖索引

-- 原SQL，需要回表SELECT*FROMusersWHEREage>20;-- 优化：只查索引包含的列SELECTid,name,ageFROMusersWHEREage>20;-- 如果有索引 (age, name)，就是覆盖索引，不用回表-- Extra显示 Using index

3. 索引失效的常见原因

-- 1. 对索引列做运算或函数SELECT*FROMordersWHEREYEAR(create_time)=2024;-- ❌SELECT*FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01';-- ✅-- 2. 隐式类型转换-- phone是varchar类型SELECT*FROMusersWHEREphone=13800138000;-- ❌ 数字，会转换SELECT*FROMusersWHEREphone='13800138000';-- ✅ 字符串-- 3. LIKE以%开头SELECT*FROMusersWHEREnameLIKE'%张';-- ❌SELECT*FROMusersWHEREnameLIKE'张%';-- ✅-- 4. OR条件中有非索引列SELECT*FROMusersWHEREid=1ORname='张三';-- name没索引则全表-- 5. 不等于 != 或 <>SELECT*FROMusersWHEREstatus!=0;-- 可能全表扫描-- 6. IS NULL 或 IS NOT NULL（视情况）-- 如果NULL值很多，优化器可能选择全表扫描

4. 索引设计原则

-- 查询条件优先-- 经常出现在WHERE、JOIN、ORDER BY的列建索引-- 区分度高的列优先-- 区分度 = COUNT(DISTINCT col) / COUNT(*)-- 性别区分度低（0.5），手机号区分度高（接近1）-- 联合索引：把区分度高的放前面CREATEINDEXidx_userONorders(user_id,status);-- user_id区分度更高-- 适当冗余，避免回表-- 如果经常查 SELECT id, name FROM users WHERE age > 20-- 可以建 INDEX idx_age_name (age, name)

第四步：SQL改写优化

1. 避免SELECT *

-- 差SELECT*FROMordersWHEREuser_id=123;-- 好：只查需要的列SELECTid,order_no,amount,statusFROMordersWHEREuser_id=123;

2. 小表驱动大表

-- user表1000条，order表100万条-- 差：大表驱动小表SELECT*FROMorders oLEFTJOINusers uONo.user_id=u.id;-- 好：小表驱动大表SELECT*FROMusers uLEFTJOINorders oONu.id=o.user_id;-- 或者用EXISTSSELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.id=o.user_idANDu.status=1);

3. 分页优化

-- 深分页问题：OFFSET很大时很慢SELECT*FROMordersORDERBYidLIMIT100000,20;-- 要扫描100020行，丢弃前100000行-- 优化1：用上一页的IDSELECT*FROMordersWHEREid>上一页最后一条的IDORDERBYidLIMIT20;-- 优化2：延迟关联SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT100000,20)tONo.id=t.id;-- 子查询只查主键，速度快

4. 避免在循环中查询

// 差：N+1查询users:=getUsers()for_,user:=rangeusers{orders:=getOrdersByUserID(user.ID)// 循环里查询}// 好：批量查询users:=getUsers()userIDs:=extractUserIDs(users)orders:=getOrdersByUserIDs(userIDs)// 一次查出来orderMap:=groupByUserID(orders)

5. UNION ALL 替代 UNION

-- UNION会去重，需要排序SELECTidFROMordersWHEREstatus=1UNIONSELECTidFROMordersWHEREstatus=2;-- UNION ALL不去重，更快SELECTidFROMordersWHEREstatus=1UNIONALLSELECTidFROMordersWHEREstatus=2;

第五步：表结构优化

1. 选择合适的数据类型

-- 整数类型TINYINT-- 1字节，-128~127SMALLINT-- 2字节INT-- 4字节BIGINT-- 8字节-- 状态字段用TINYINT足够，别用INTstatusTINYINTNOTNULLDEFAULT0-- 时间戳用INT存储（4字节）比DATETIME（8字节）省空间-- 但DATETIME可读性好，看业务需求-- 金额用DECIMAL，别用FLOAT/DOUBLE（精度问题）amountDECIMAL(10,2)NOTNULLDEFAULT0.00

2. 大表拆分

-- 垂直拆分：把大字段拆出去-- orders表id,user_id,order_no,amount,status,create_time-- order_details表（大字段）id,order_id,goods_info,remark-- 水平拆分：按时间或ID分表orders_2024,orders_2025 orders_0,orders_1,orders_2...orders_15-- 按user_id取模

3. 归档历史数据

-- 定期把老数据移到归档表INSERTINTOorders_archiveSELECT*FROMordersWHEREcreate_time<'2023-01-01';DELETEFROMordersWHEREcreate_time<'2023-01-01'LIMIT10000;-- 小批量删除，避免锁表太久

真实案例：一次慢查询排查

现象：订单列表接口偶尔超时

排查过程：

  1. 查慢查询日志，找到问题SQL：
SELECT*FROMordersWHEREuser_id=123ANDstatusIN(1,2,3)ANDcreate_time>'2024-01-01'ORDERBYcreate_timeDESCLIMIT20;
  1. EXPLAIN分析：
type: ref key: idx_user_id rows: 50000 Extra: Using where; Using filesort

虽然用了索引，但rows很大，还有filesort。

  1. 分析：

    • 这个用户订单很多（大客户）
    • idx_user_id单列索引，筛选后还要回表过滤status和时间
    • ORDER BY create_time和索引顺序不一致，要filesort

  2. 优化方案：

-- 新建联合索引CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);-- 改SQL，把IN改成多个条件SELECT*FROMordersWHEREuser_id=123ANDstatus=1ANDcreate_time>'2024-01-01'ORDERBYcreate_timeDESCLIMIT20UNIONALLSELECT*FROMordersWHEREuser_id=123ANDstatus=2ANDcreate_time>'2024-01-01'ORDERBYcreate_timeDESCLIMIT20...

或者接受现状，在应用层做缓存。

  1. 结果：查询时间从2s降到50ms。

工具推荐

  1. pt-query-digest：分析慢查询日志，比mysqldumpslow强大
  2. Percona Toolkit：一套MySQL工具集
  3. MySQL Workbench：可视化执行计划
  4. Explain Analyze（MySQL 8.0+）：真实执行统计
-- MySQL 8.0+ 用EXPLAIN ANALYZEEXPLAINANALYZESELECT*FROMordersWHEREuser_id=123;-- 显示真实执行时间，而不是预估

总结

慢SQL排查的套路：

  1. 定位：慢查询日志、SHOW PROCESSLIST、performance_schema
  2. 分析：EXPLAIN看执行计划，重点关注type、key、rows、Extra
  3. 优化：
    • 索引优化：联合索引、覆盖索引、避免索引失效
    • SQL改写：避免SELECT *、分页优化、小表驱动大表
    • 表结构优化：合适的数据类型、大表拆分

记住一点：看数据量，看场景。100条数据的表，怎么写都快；上亿数据的表，细节决定成败。

有问题评论区聊。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/2/3 19:47:19

OpenCore配置自动化：智能工具如何彻底改变Hackintosh搭建体验

OpenCore配置自动化&#xff1a;智能工具如何彻底改变Hackintosh搭建体验 【免费下载链接】OpCore-Simplify A tool designed to simplify the creation of OpenCore EFI 项目地址: https://gitcode.com/GitHub_Trending/op/OpCore-Simplify 传统的OpenCore配置过程常常…

作者头像 李华
网站建设 2026/2/12 19:48:13

运动控制算法十年演进（2015–2025）

运动控制算法十年演进&#xff08;2015–2025&#xff09; 一句话总论&#xff1a; 2015年运动控制算法还是“PID三环固定参数手工调参”的刚性工业时代&#xff0c;2025年已进化成“端到端VLA大模型力矩直出自适应无感控制量子级扰动自愈具身意图级柔顺自进化”的生物级智能时…

作者头像 李华
网站建设 2026/2/5 20:53:53

Web产品后台开发难？XinServer 零代码解决方案

Web产品后台开发难&#xff1f;试试这个零代码后端方案 兄弟们&#xff0c;最近是不是又被后台需求搞到头大&#xff1f;产品经理一拍脑袋&#xff1a;“咱们这个用户管理&#xff0c;得加个标签功能&#xff0c;还要能按部门筛选&#xff0c;后天能上线吗&#xff1f;” 你心里…

作者头像 李华
网站建设 2026/2/12 3:39:30

Chart.js插件系统完整教程：从入门到精通

Chart.js插件系统完整教程&#xff1a;从入门到精通 【免费下载链接】Chart.js Simple HTML5 Charts using the canvas tag 项目地址: https://gitcode.com/gh_mirrors/ch/Chart.js 引言&#xff1a;为什么选择插件开发&#xff1f; 在当今数据可视化需求日益复杂的场景…

作者头像 李华
网站建设 2026/2/13 22:19:40

如何5步精通CosId：分布式ID生成难题的终极解决方案

在分布式系统开发中&#xff0c;如何生成全局唯一、高性能、有序递增的ID一直是困扰开发者的核心难题。CosId作为一款通用、灵活且高性能的分布式ID生成器&#xff0c;通过创新的算法设计和丰富的生态集成&#xff0c;为开发者提供了完美的解决方案。本文将带你从零开始&#x…

作者头像 李华
网站建设 2026/2/9 16:25:18

终极指南：10分钟掌握Python自动化Anki卡片制作

终极指南&#xff1a;10分钟掌握Python自动化Anki卡片制作 【免费下载链接】genanki A Python 3 library for generating Anki decks 项目地址: https://gitcode.com/gh_mirrors/ge/genanki 你是否曾经花费数小时手动制作Anki卡片&#xff0c;只为记住那些重要知识点&am…

作者头像 李华