慢SQL的优化方法

MySQL 数据库的 SQL 优化是提升性能的核心环节，主要从 减少数据扫描量、降低计算复杂度 和 避免资源竞争 三大方向切入。以下是具体优化方向及实践策略：

一、索引优化（核心方向）

1. 精准添加索引

2. WHERE/JOIN/ORDER BY/GROUP BY 高频过滤列必建索引

3. 联合索引遵循最左前缀原则（(a,b,c) 可支持 a | a,b | a,b,c 查询）

4. 覆盖索引：避免回表（SELECT 字段全部在索引中

5. 避免索引失效场景

6. 禁止对索引列 函数操作：WHERE YEAR(create_time)=2023 换成WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

7. 避免隐式类型转换：WHERE phone=13800138000（若 phone 是字符串类型则失效）

8. LIKE 左模糊禁止：LIKE '%abc'换成LIKE 'abc%'

二、SQL 语句重写优化

1. 避免全表扫描

2. 禁用 SELECT *，只取必要字段

3. 分页深度优化：用 游标分页 替代 OFFSET

-- 传统分页（越后越慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 游标分页（恒定速度）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

4. 子查询优化

5. 关联子查询 → JOIN：减少嵌套查询次数

6. -- 低效：逐行执行子查询
   SELECT * FROM users u 
   WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
   -- 高效：JOIN 一次完成
   SELECT DISTINCT u.* FROM users u 
   JOIN orders o ON u.id = o.user_id;
   

7. join查询注意驱动表，提前筛选出要查询的数据量

8. 避免临时表与文件排序

9. ORDER BY/GROUP BY 字段与索引顺序一致

10. 大结果集分阶段处理（如分批聚合）

三、执行计划深度调优

1. 强制索引选择

2. 当优化器选错索引时，用 FORCE INDEX 干预

3. 拆解复杂 SQL

4. 多表 JOIN 拆分为单表查询 + 程序内存计算

5. 大批量更新分批次执行（减少锁竞争）

6. -- 单次更新100万行 → 锁表风险
   UPDATE logs SET status=1 WHERE create_time < '2023-01-01';
   -- 分批更新（每批1万行）
   WHILE (affected_rows > 0) DO
     UPDATE logs SET status=1 
     WHERE create_time < '2023-01-01' 
     LIMIT 10000;
   END WHILE;
   

四、数据类型与表达式优化

1. 类型精确匹配

• 避免字符串与数字比较：WHERE id = '100' → WHERE id = 100

• 枚举值使用 ENUM 类型（减少存储+加速过滤）

3. 短路计算应用

• 高过滤性条件前置：

• -- 优先用 status 过滤（假设status过滤90%数据）
  SELECT * FROM orders 
  WHERE status = 'shipped' AND total_price < 100;
  

五、高级策略与架构协同

1. 读写分离

• 报表类查询路由到只读副本，减轻主库压力

3. 缓存层拦截

• 热点数据（如商品信息）用 Redis 缓存，避免重复查询

5. 异步处理

• 非实时统计任务写入消息队列（如 Kafka）异步计算