MySQL十大慢查询优化实战：从10秒到0.1秒的性能飞跃

反思：为什么你的SQL跑得比蜗牛还慢？

明明只是简单的查询，却要等上10秒？

数据量稍微增长，系统就频繁超时？

慢查询是数据库性能的隐形杀手，而90%的问题可通过优化索引和SQL逻辑解决！

今天小编通过10个真实场景的优化方案，手把手带你从执行计划分析到索引设计，彻底告别慢查询！

一、未命中索引：全表扫描的灾难

问题场景

查询用户表中某手机号用户，但执行耗时2秒：

SELECT * FROM users WHERE phone = '13800138000';  

分析过程

1. 执行EXPLAIN查看执行计划：
   代码语言：javascript

   复制

   EXPLAIN SELECT * FROM users WHERE phone = '13800138000';  

   1. type=ALL（全表扫描），rows=100000（扫描10万行）
2. 原因：phone字段无索引，被迫扫描全表。

优化方案

为phone字段添加索引：

ALTER TABLE users ADD INDEX idx_phone(phone);  

优化效果

• 执行计划变为type=ref（索引查找），rows=1
• 查询时间从2秒降至0.01秒

二、索引失效：隐式类型转换陷阱

问题场景

订单表按字符串类型的订单号查询，但索引未生效：

SELECT * FROM orders WHERE order_no = 10086;  -- order_no是VARCHAR类型  

分析过程

1. EXPLAIN结果显示type=ALL，索引idx_order_no未命中。
2. 原因：order_no是字符串类型，但查询条件使用数字，触发隐式转换导致索引失效。

优化方案

保持字段与参数类型一致：

SELECT * FROM orders WHERE order_no = '10086';  

优化效果

• 索引命中，查询时间从3秒降至0.02秒

三、最左前缀原则：复合索引的正确姿势

问题场景

商品表根据category_id和price查询，但查询依然慢：

SELECT * FROM products WHERE price > 100;  

分析过程

1. 已为(category_id, price)创建复合索引idx_cat_price。
2. 原因：查询未包含category_id，不满足最左前缀原则，索引失效。

优化方案

调整查询条件或索引设计：

-- 方案1：添加category_id条件（如允许业务调整）  
SELECT * FROM products WHERE category_id=1 AND price > 100;  

-- 方案2：单独为price创建索引  
ALTER TABLE products ADD INDEX idx_price(price);  

四、分页优化：避开LIMIT深分页

问题场景

分页查询第100000页数据，耗时8秒：

SELECT * FROM logs ORDER BY id LIMIT 1000000, 10;  

分析过程

1. EXPLAIN显示type=ALL，需扫描前1000010行再丢弃。

优化方案

改用游标分页（基于ID连续递增）：

SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 10;  

优化效果

• 扫描行数从100万降至10行，耗时从8秒降至0.005秒

五、子查询优化：改用JOIN提升效率

问题场景

查询未支付订单的用户信息，子查询耗时6秒：

SELECT * FROM users  
WHERE id IN (SELECT user_id FROM orders WHERE status = 'unpaid');  

分析过程

1. 子查询需全表扫描orders，生成临时表后再关联。

优化方案

改写为JOIN查询：

SELECT u.* FROM users u  
JOIN orders o ON u.id = o.user_id  
WHERE o.status = 'unpaid';  

优化效果

• 避免临时表，查询时间从6秒降至0.3秒

六、避免SELECT *：覆盖索引的魔力

问题场景

查询用户姓名和邮箱，但查询缓慢：

SELECT name, email FROM users WHERE age > 30;  

分析过程

1. 存在索引idx_age(age)，但需回表查询name和email。

优化方案

创建覆盖索引：

ALTER TABLE users ADD INDEX idx_age_cover(age, name, email);  

优化效果

• Extra=Using index，无需回表，耗时从1.2秒降至0.05秒

七、排序优化：利用索引避免Filesort

问题场景

按注册时间倒序查询用户，耗时4秒：

SELECT * FROM users ORDER BY register_time DESC LIMIT 100;  

分析过程

1. EXPLAIN显示Using filesort，内存或磁盘排序成本高。

优化方案

为register_time创建索引：

ALTER TABLE users ADD INDEX idx_register_time(register_time);  

优化效果

• 排序利用索引，耗时从4秒降至0.1秒

八、大事务拆分：减少锁竞争

问题场景

批量更新10万条用户状态，导致锁等待超时：

BEGIN;  
UPDATE users SET status = 'active' WHERE create_time < '2023-01-01';  
COMMIT;  

分析过程

1. 单事务更新数据量过大，长期持有行锁。

优化方案

分批次提交事务：

SET autocommit=0;  
WHILE (需要更新的数据) DO  
  UPDATE users SET status = 'active' WHERE create_time < '2023-01-01' LIMIT 1000;  
  COMMIT;  
  SLEEP(1);  -- 释放锁间隙  
END WHILE;  
SET autocommit=1;  

优化效果

• 锁竞争减少，更新耗时从30秒降至5秒

九、避免函数运算：索引字段的纯洁性

问题场景

查询2023年注册的用户，索引未生效：

SELECT * FROM users WHERE YEAR(register_time) = 2023;  

分析过程

1. 对索引字段register_time使用函数，导致索引失效。

优化方案

改用范围查询：

SELECT * FROM users  
WHERE register_time >= '2023-01-01' AND register_time < '2024-01-01';  

优化效果

• 索引命中，查询时间从3秒降至0.1秒

十、合理使用强制索引：打破优化器误判

问题场景

统计订单金额时优化器选择错误索引：

SELECT SUM(amount) FROM orders WHERE user_id = 100;  

分析过程

1. 存在idx_user_id和idx_amount索引，优化器误选idx_amount。

优化方案

强制指定索引：

SELECT SUM(amount) FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 100;  

优化效果

• 强制使用idx_user_id，扫描行数减少，耗时从2秒降至0.2秒

总结：优化是持续的过程

慢查询优化没有标准答案，需结合执行计划分析、索引设计与业务逻辑调整。

请大家记住三个核心原则：

• 能用索引的不用全表
• 能批量的不单条
• 能拆事务的不长锁

以上是MySQL查询优化的10个技巧，大家如果有更好的MySQL查询优化方案欢迎评论区沟通交流！