MySQL慢查询排查，专治各种“卡到崩溃”

最新推荐文章于 2025-04-03 11:40:23 发布

码里看花‌

最新推荐文章于 2025-04-03 11:40:23 发布

阅读量324

点赞数 7

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/hellojava6666/article/details/146976895

版权

前言：做后端开发这些年，最怕的就是半夜收到监控报警：“数据库慢查询激增！”。经历过几次生产事故后，我算是把MySQL慢查询的毛病摸透了。今天就把这些实战经验掰开了揉碎了分享给大家，专治SQL性能的各种不服！

一、慢查询到底“慢”在哪？——揪出元凶

1. 开启慢查询日志，让慢SQL无处遁形

# 查看慢查询配置（默认关闭） 
show variables like '%slow_query_log%';  
# 临时开启慢查询日志（重启失效） 
set global slow_query_log = 'ON';  
# 设置慢查询阈值（单位：秒） 
set global long_query_time = 1;  
# 记录未使用索引的查询 
set global log_queries_not_using_indexes = 'ON';

关键点：

日志路径别用默认的，小心磁盘被撑爆！
阈值建议从1秒开始，逐步调整。

用mysqldumpslow工具分析日志，比如：

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log  # 按耗时排序取前10

2. EXPLAIN执行计划——看懂SQL的“体检报告”

EXPLAIN SELECT * FROM user WHERE name LIKE '%张%' AND age > 20;

重点看这几列：

type：ALL（全表扫描）直接拉黑！至少range级别。
key：实际使用的索引，NULL就是没走索引。
rows：扫描行数，越大越危险。
Extra：出现Using filesort或Using temporary，赶紧优化！

二、索引失效的六大“作死”场景（附解法）

场景1：数据类型隐式转换——坑哭新手

-- 电话字段是varchar，但查询用数字 
SELECT * FROM user WHERE phone = 13800138000; -- 索引失效！ 
-- 正确写法 
SELECT * FROM user WHERE phone = '13800138000';

场景2：对索引列使用函数或运算

-- 生日字段有索引 
SELECT * FROM user WHERE YEAR(birthday) = 2000; -- 失效！ 
-- 改写为范围查询 
SELECT * FROM user WHERE birthday BETWEEN '2000-01-01' AND '2000-12-31';

场景3：最左前缀原则——组合索引的命门
索引idx(name, age)：

SELECT * FROM user WHERE age = 20; -- 失效，没带头大哥name 
SELECT * FROM user WHERE name LIKE '王%' AND age = 20; -- 有效

场景4：OR连接非索引字段

-- age有索引，address无索引 
SELECT * FROM user WHERE age = 20 OR address = '北京'; -- 全表扫描！ 
-- 优化方案：拆成UNION 
SELECT * FROM user WHERE age = 20  
UNION  
SELECT * FROM user WHERE address = '北京';

场景5：!= 或 <> 操作符

SELECT * FROM user WHERE status != 1; -- 即使status有索引也失效 
-- 改写方案： 
SELECT * FROM user WHERE status IN (0,2,3); -- 注意区分度

场景6：LIKE以通配符开头

SELECT * FROM user WHERE name LIKE '%小明%'; -- 全表扫 
-- 必须左边%怎么办？考虑全文索引或ES

三、慢SQL优化三板斧

1. 重写SQL语句

3. 参数调优——针对高并发场景

**消灭SELECT ***：只查需要的字段，特别是TEXT/BLOB类型。

子查询转JOIN：

-- 慢查询 
SELECT * FROM orders WHERE user_id IN (SELECT id FROM user WHERE age > 18); 
-- 优化后 
SELECT o.* FROM orders o JOIN user u ON o.user_id = u.id WHERE u.age > 18;

分页优化：

-- 深度分页卡死 
SELECT * FROM user LIMIT 1000000, 10; 
-- 优化方案：记录上一页最大ID 
SELECT * FROM user WHERE id > 1000000 LIMIT 10;

2. 索引设计黄金法则

单表索引不超过5个
优先考虑区分度高的字段（比如手机号 > 性别）
避免冗余索引，比如已有(a,b)，再建(a)就是多余

# my.cnf 核心参数 
join_buffer_size = 256M  # 关联查询缓存 
sort_buffer_size = 256M  # 排序缓存 
innodb_buffer_pool_size = 物理内存的70%  # 缓存数据和索引

四、防患于未然——长效机制

每周定时检查慢查询日志，重点关注执行时间突增的SQL。
关键业务表增加监控：扫描行数、锁等待时间、索引使用率。
新功能上线前，强制EXPLAIN审核。
定期清理无用索引：用sys.schema_unused_indexes视图（MySQL 5.7+）。

结语：SQL优化是个持续的过程，别指望一劳永逸。遇到性能问题，冷静分析执行计划，先确认索引是否有效，再考虑重写SQL。如果这篇文章帮你解决了问题，欢迎关注我的优快云，后续继续手撕各种数据库疑难杂症！