当系统数据量突破百万级时，SQL性能直接决定业务生死。本文通过10个高频踩坑案例，结合真实场景代码，手把手带你提升查询效率300%+。每个技巧都经过生产环境验证，建议收藏！

一、索引使用：避免全表扫描的生死线

场景：电商订单表orders（300万数据）按用户ID查询
错误写法：

SELECT * FROM orders WHERE user_id = 10086; -- 未建立索引

优化方案：

-- 创建覆盖索引（MySQL示例）
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

原理：未加索引时扫描300万行，添加B+树索引后只需3次磁盘IO，耗时从2.1s降至12ms

二、SELECT * 是性能杀手

场景：用户表users查询联系方式
错误写法：

SELECT * FROM users WHERE status = 1;

优化方案：

SELECT user_id, name, phone FROM users WHERE status = 1;

原理：当单行数据量达1KB时，查询10万条数据可减少953MB网络传输量

三、JOIN优化：小表驱动原则

场景：部门表departments（50行）关联员工表employees（10万行）
错误写法：

SELECT * FROM employees 
LEFT JOIN departments ON employees.dept_id = departments.id;

优化方案：

SELECT * FROM departments 
LEFT JOIN employees ON departments.id = employees.dept_id;

原理：MySQL的Nested-Loop Join算法中，小表作为驱动表可减少60%循环次数

四、分页查询：拒绝OFFSET陷阱

场景：百万级数据分页
错误写法：

SELECT * FROM logs LIMIT 1000000, 20; -- 扫描前100万条

优化方案：

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

原理：偏移量10万时，优化方案从2.3s降至28ms（基于自增主键）

五、避免字段函数操作

场景：按注册年份统计用户量
错误写法：

SELECT COUNT(*) FROM users WHERE YEAR(create_time) = 2023;

优化方案：

SELECT COUNT(*) FROM users 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

原理：函数操作导致索引失效，范围查询利用索引扫描效率提升18倍

六、EXISTS替代IN子查询

场景：查询有订单的用户
错误写法：

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

优化方案：

SELECT * FROM users u 
WHERE EXISTS (
  SELECT 1 FROM orders WHERE user_id = u.id LIMIT 1
);

原理：IN子查询导致全表扫描，EXISTS使用半连接策略效率提升40%

七、批量插入：拒绝逐条提交

场景：导入10万条商品数据
错误写法：

for(Product p : list){
   execute("INSERT INTO products VALUES(...)");
}

优化方案：

INSERT INTO products VALUES
(v1,v2),(v3,v4)...; -- 单次插入500条

原理：批量插入减少99%的客户端-服务端交互次数，10万数据从6分钟降至8秒

八、避免多余排序

场景：已索引字段排序
错误写法：

SELECT * FROM orders 
WHERE user_id = 10086 ORDER BY create_time DESC;

优化方案：

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);

原理：联合索引天然有序，避免Using filesort额外排序

九、UNION ALL替代UNION

场景：合并两个查询结果
错误写法：

SELECT id FROM A UNION SELECT id FROM B; -- 默认去重

优化方案：

SELECT id FROM A UNION ALL SELECT id FROM B;

原理：去重操作消耗额外30%性能，确认无需去重时必用UNION ALL

十、执行计划：优化器的眼睛

诊断步骤：

1. 使用EXPLAIN查看执行计划
2. 关注type列（目标至少达到range级别）
3. 检查Extra列是否出现Using filesort、Using temporary

经典案例：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 ORDER BY amount;

当出现Using filesort时，需创建(user_id, amount)联合索引

终极总结：

1. 索引策略决定查询生死
2. 数据搬运量是性能核心指标
3. 执行计划分析必须成为本能反应

行动建议：
立即用EXPLAIN检查你本周写的SQL，至少找出3个优化点。将本文案例存入代码片段库，开发时随时比对！

转发提醒：转发本文到技术群，@你常写SQL的同事，共同避免生产事故！