慢SQL优化实战：我用这5招让查询速度提升100倍！（真实案例）

kernelcraft

于 2025-05-15 18:28:01 发布

阅读量884

点赞数 10

文章标签： sql 数据库其他

本文链接：https://blog.csdn.net/kernelcraft/article/details/147989539

版权

一、先找痛点：如何揪出数据库中的"龟速"查询？

1.1 慢查询日志 - MySQL自带的"测速仪"（必装！！！）

-- 动态开启慢查询（立即生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询
SET GLOBAL slow_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  -- 抓出没走索引的查询（超级重要）

1.2 EXPLAIN命令 - 查询执行的"X光片"

拿到慢SQL后马上执行：

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status=1;

重点看这几个字段：

type列：出现ALL就是全表扫描（立即报警！）
rows列：扫描行数超过1万就要警惕
Extra列：看到Using filesort/Using temporary马上优化

二、最常见的5类慢查询（附真实案例）

2.1 全表扫描型

症状：EXPLAIN的type=ALL，rows数值爆炸
案例：用户表200万数据，查询：

SELECT * FROM users WHERE phone LIKE '%1314%';

优化方案：

添加逆向索引：ALTER TABLE users ADD INDEX idx_phone_reverse(reverse(phone))
改写查询：WHERE REVERSE(phone) LIKE REVERSE('%1314%')

2.2 索引失效型

五大作死写法（血泪教训）：

对索引字段做运算：WHERE YEAR(create_time)=2023
使用前导通配符：WHERE content LIKE '%异常%'
隐式类型转换：WHERE order_no = 10086（order_no是varchar类型）
OR连接非索引字段：WHERE a=1 OR b=2
不符合最左前缀：联合索引(a,b,c)但查询条件只有b和c

2.3 复杂查询型

最近优化的一个真实案例：

SELECT * FROM products 
WHERE id IN (
    SELECT product_id FROM orders 
    WHERE create_time > '2023-01-01'
    GROUP BY product_id 
    HAVING COUNT(*) > 100
)
ORDER BY price DESC 
LIMIT 100;

优化步骤：

用JOIN替代IN子查询
将HAVING条件转移到WHERE
添加复合索引(create_time, product_id)

2.4 锁等待型

某电商系统凌晨出现大量锁超时，排查发现：

UPDATE inventory SET stock=stock-1 
WHERE product_id=123 AND stock>0;

优化方案：

添加索引(product_id, stock)
使用乐观锁版本号控制
事务拆分为：先查询再更新，控制事务粒度

2.5 分页查询型

深度分页的典型问题：

SELECT * FROM logs 
ORDER BY id DESC 
LIMIT 1000000, 10; -- 需要扫描1000010行

优化方案：

使用游标分页：WHERE id < 上次最大ID
延迟关联：

SELECT * FROM logs 
INNER JOIN (
    SELECT id FROM logs 
    ORDER BY id DESC 
    LIMIT 1000000,10
) AS tmp USING(id);

三、我的优化工具箱（5大必杀技）

3.1 索引优化三板斧

覆盖索引：所有查询字段都在索引中
索引下推：5.6+版本默认开启，减少回表
联合索引排序：INDEX(a,b)同时优化WHERE a=1 ORDER BY b

3.2 查询重构技巧

避免SELECT *：某次优化中，字段从23个减到3个，速度提升8倍
用EXISTS代替IN：特别是子查询结果集大时
拆分复杂查询：把一个大查询拆成多个小查询

3.3 分页优化秘籍

最近优化的一个案例：

-- 原始查询（执行2.3秒）：
SELECT * FROM user_actions 
ORDER BY create_time DESC 
LIMIT 800000,20;

-- 优化后（0.02秒）：
SELECT * FROM user_actions 
WHERE create_time < '2023-06-01' 
ORDER BY create_time DESC 
LIMIT 20;

3.4 锁优化策略

尽量使用行锁：InnoDB默认
控制事务粒度：短事务快进快出
热点数据排队：用Redis做并发队列

3.5 定期维护

建立自动化任务：

-- 每周日凌晨3点执行
OPTIMIZE TABLE big_table;
ANALYZE TABLE important_table;

四、实战：某电商系统优化案例

4.1 场景还原

订单查询接口响应时间从200ms暴涨到5s+，QPS从200降到50

4.2 问题定位

慢日志发现大量类似查询：

SELECT * FROM orders 
WHERE user_id=123 
AND status IN(1,2,5) 
ORDER BY create_time DESC 
LIMIT 10;

EXPLAIN显示：using filesort + using temporary

4.3 优化方案

新增联合索引：(user_id, status, create_time)
改写查询为：

SELECT * FROM orders FORCE INDEX(idx_user_status) 
WHERE user_id=123 
AND status=1
UNION ALL
SELECT * FROM orders FORCE INDEX(idx_user_status) 
WHERE user_id=123 
AND status=2
UNION ALL
SELECT * FROM orders FORCE INDEX(idx_user_status) 
WHERE user_id=123 
AND status=5
ORDER BY create_time DESC 
LIMIT 10;