1. SQL优化的重要性与基本原则

作为一名长期与数据库打交道的开发者，我深知SQL优化对系统性能的关键影响。在实际项目中，一条糟糕的SQL语句可能拖垮整个系统，而经过优化的查询则能让性能提升数十倍甚至上百倍。SQL优化不是简单的技巧堆砌，而是需要理解数据库工作原理后的系统性思考。

SQL优化的核心目标是减少数据库的I/O操作和CPU计算量。数据库执行查询时主要消耗在：

• 从磁盘读取数据页到内存
• 在内存中进行排序、连接等计算操作
• 将结果返回给客户端

优化SQL就是要尽量减少这三个环节的资源消耗。下面我将分享15个经过实战验证的优化技巧，每个技巧都配有具体场景和实现代码。

2. 查询语句优化技巧

2.1 避免使用SELECT *

新手开发者常犯的错误就是习惯性使用SELECT *。这种做法会导致三个问题：

1. 网络传输开销增大：返回不需要的字段会占用更多网络带宽
2. 内存消耗增加：数据库需要为所有字段分配内存空间
3. 索引失效风险：当使用覆盖索引时，SELECT *会导致回表查询

优化方案是只查询需要的字段：

-- 不推荐 SELECT * FROM user WHERE id=1; -- 推荐 SELECT name, age FROM user WHERE id=1;

注意：即使需要大部分字段，也建议显式列出字段名。这样当表结构变更时，可以避免因字段顺序变化导致的问题。

2.2 用UNION ALL代替UNION

UNION和UNION ALL的区别在于前者会去除重复行，这个去重操作需要排序和比较，消耗大量CPU资源。当确定结果集没有重复或允许重复时，应使用UNION ALL。

-- 消耗资源的写法 (SELECT * FROM user WHERE id=1) UNION (SELECT * FROM user WHERE id=2); -- 高效写法 (SELECT * FROM user WHERE id=1) UNION ALL (SELECT * FROM user WHERE id=2);

实测在100万条数据下，UNION ALL比UNION快3-5倍。只有在确实需要去重时才使用UNION。

2.3 小表驱动大表原则

在子查询和JOIN操作中，应该让小表驱动大表。这是因为数据库会以驱动表为基础循环查询被驱动表，小表作为驱动表可以减少循环次数。

IN和EXISTS的选择：

-- 当user表较小时使用IN SELECT o.field1, o.field2 FROM order o WHERE o.user_id IN (SELECT id FROM user WHERE status=1); -- 当order表较小时使用EXISTS SELECT o.field1, o.field2 FROM order o WHERE EXISTS (SELECT 1 FROM user u WHERE o.user_id = u.id AND u.status=1);

3. 批量操作与查询控制

3.1 批量操作减少交互

频繁的单条INSERT操作会产生大量网络往返和事务开销。批量操作可以显著提升性能：

// 不推荐：循环单条插入 for(User user : userList) { userMapper.insert(user); } // 推荐：批量插入 userMapper.insertBatch(userList);

在MySQL中，批量插入比单条插入快10-100倍。但要注意单批数据量不宜过大（建议500-1000条/批），避免内存溢出和长事务。

3.2 使用LIMIT限制结果集

获取大量数据时，应该使用LIMIT分页，避免内存溢出：

// 不推荐：一次性获取全部 List<Order> orders = orderMapper.getAllOrders(); // 推荐：分页获取 List<Order> orders = orderMapper.getOrdersByPage(0, 100);

对于分页查询，深分页时使用WHERE id > last_id方式比LIMIT offset性能更好：

-- 低效的深分页 SELECT * FROM user ORDER BY id LIMIT 1000000, 20; -- 高效的分页 SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 20;

3.3 控制IN查询的值数量

IN查询中的值过多会导致性能下降和SQL解析开销增大。建议控制在500个以内：

public List<Category> getCategories(List<Long> ids) { if(CollectionUtils.isEmpty(ids)) { return Collections.emptyList(); } if(ids.size() > 500) { // 可以分批查询或抛出异常 throw new BusinessException("单次查询ID数量不能超过500"); } return categoryMapper.getByIds(ids); }

对于大量ID查询，可以考虑：

1. 分批查询后合并结果
2. 使用临时表JOIN
3. 使用专门的搜索引擎

4. 高级查询优化技术

4.1 增量查询模式

对于数据同步或导出场景，增量查询比全量查询更高效：

SELECT id, name, create_time FROM product WHERE id > #{lastId} AND update_time >= #{lastUpdateTime} ORDER BY id LIMIT 1000;

这种模式：

1. 避免了全表扫描
2. 利用了id主键索引
3. 通过时间条件进一步过滤
4. 每次只获取部分数据，内存压力小

4.2 JOIN优化策略

JOIN操作是SQL中最消耗资源的操作之一，优化要点包括：

1. 控制JOIN表数量：建议不超过3张表，过多JOIN会导致执行计划复杂
2. 小表驱动大表：将小表放在JOIN左侧
3. 避免WHERE条件导致JOIN类型变化：

-- 这个查询实际上变成了INNER JOIN SELECT o.id, o.code, u.name FROM order o LEFT JOIN user u ON o.user_id = u.id WHERE u.status=1; -- 正确写法应把条件放在ON子句 SELECT o.id, o.code, u.name FROM order o LEFT JOIN user u ON o.user_id = u.id AND u.status=1;

4.3 用连接查询代替子查询

现代数据库对JOIN的优化通常比对子查询的优化更好：

-- 不推荐：使用子查询 SELECT * FROM product WHERE category_id IN (SELECT id FROM category WHERE type=1); -- 推荐：使用JOIN SELECT p.* FROM product p JOIN category c ON p.category_id = c.id WHERE c.type=1;

5. 索引优化策略

5.1 索引数量控制原则

索引不是越多越好，需要平衡查询性能和写入性能：

1. 单表索引数控制在5个以内：阿里巴巴开发手册建议
2. 单个索引字段不超过5个：复合索引字段过多效果递减
3. 高并发系统更需严格控制：索引会增加INSERT/UPDATE/DELETE开销

优化方案：

• 使用复合索引代替多个单列索引
• 删除不使用的冗余索引
• 将部分查询迁移到搜索引擎

5.2 高效索引设计

设计索引时要考虑：

1. 选择性高的列在前：区分度高的列放在复合索引前面
2. 常用查询条件优先：WHERE和ORDER BY常用字段建索引
3. 避免索引失效场景：
   • 对索引列使用函数
   • 隐式类型转换
   • 使用!=或<>操作符
   • 使用前导通配符LIKE '%xxx'

-- 创建合适的复合索引 ALTER TABLE order ADD INDEX idx_user_status (user_id, status); -- 索引失效的例子 SELECT * FROM user WHERE DATE(create_time) = '2023-01-01';

5.3 索引优化实战案例

分析一个订单查询优化案例：

-- 原始查询（耗时1200ms） SELECT * FROM orders WHERE user_id=123 AND status=1 ORDER BY create_time DESC LIMIT 10; -- 优化步骤： -- 1. 创建复合索引 ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); -- 2. 优化后查询（耗时15ms） SELECT id, user_id, amount, create_time FROM orders WHERE user_id=123 AND status=1 ORDER BY create_time DESC LIMIT 10;

6. 数据类型与表设计优化

6.1 合理选择字段类型

字段类型选择直接影响存储空间和查询效率：

1. 数字类型优于字符串：整型比较比字符串快
2. 固定长度用CHAR：如手机号、MD5值
3. 变长字符串用VARCHAR：节省存储空间
4. 金额使用DECIMAL：避免浮点精度问题

-- 不推荐 ALTER TABLE user ADD COLUMN phone VARCHAR(20); -- 推荐 ALTER TABLE user ADD COLUMN phone CHAR(11); -- 金额字段 ALTER TABLE order ADD COLUMN amount DECIMAL(12,2);

6.2 表设计规范

良好的表设计是SQL优化的基础：

1. 每个表必须有主键
2. 避免使用TEXT/BLOB存储频繁查询的数据
3. 将大字段拆分到单独表
4. 合理使用范式化和反范式化

7. 分组与聚合查询优化

7.1 GROUP BY优化

GROUP BY操作通常需要临时表和排序，优化方法：

1. WHERE条件先过滤：减少参与分组的数据量
2. 使用索引覆盖：避免回表操作
3. 考虑使用物化视图：预计算常用聚合结果

-- 不推荐：全表分组 SELECT user_id, COUNT(*) FROM order GROUP BY user_id; -- 推荐：先过滤 SELECT user_id, COUNT(*) FROM order WHERE create_time > '2023-01-01' GROUP BY user_id;

7.2 分页查询优化

深分页是常见性能瓶颈，优化方案：

1. 使用主键分页：WHERE id > last_id方式
2. 延迟关联：先查ID再关联获取完整数据
3. 缓存分页结果：适合变化不频繁的数据

-- 低效的分页 SELECT * FROM user ORDER BY name LIMIT 100000, 20; -- 高效分页（假设name有索引） SELECT * FROM user WHERE name > 'last_name_value' ORDER BY name LIMIT 20;

8. 执行计划分析与实战调优

8.1 使用EXPLAIN分析查询

EXPLAIN是SQL优化的必备工具，关键指标：

1. type列：从优到差 system > const > eq_ref > ref > range > index > ALL
2. key列：实际使用的索引
3. rows列：预估扫描行数
4. Extra列：Using filesort、Using temporary等警告

EXPLAIN SELECT * FROM user WHERE name LIKE '张%';

8.2 常见性能问题排查

1. 全表扫描（type=ALL）：添加适当索引
2. 文件排序（Using filesort）：优化ORDER BY子句
3. 临时表（Using temporary）：优化GROUP BY或子查询
4. 索引失效：检查查询条件是否符合最左前缀原则

9. 高级优化技巧

9.1 使用覆盖索引

覆盖索引是指查询只需要通过索引就能获取全部所需数据，避免回表：

-- 创建覆盖索引 ALTER TABLE user ADD INDEX idx_name_age (name, age); -- 使用覆盖索引的查询 SELECT name, age FROM user WHERE name LIKE '张%';

9.2 使用索引下推

MySQL 5.6+支持索引下推（ICP），可以在存储引擎层过滤数据：

-- 在没有ICP时，存储引擎只根据user_id检索，server层过滤status -- 有ICP时，存储引擎会同时过滤user_id和status SELECT * FROM order WHERE user_id=123 AND status=1;

9.3 使用MRR优化

多范围读取优化（MRR）可以减少随机IO：

-- 启用MRR SET optimizer_switch='mrr=on,mrr_cost_based=off'; -- MRR优化的查询 SELECT * FROM user WHERE age BETWEEN 20 AND 30;

10. 数据库配置优化

10.1 关键参数调整

根据服务器配置调整MySQL参数：

1. innodb_buffer_pool_size：通常设为物理内存的70-80%
2. innodb_log_file_size：128M-2G，影响写入性能
3. max_connections：根据应用需求设置，避免过高

10.2 连接池配置

应用层连接池配置建议：

1. 初始大小：10-20
2. 最大大小：根据DB负载能力设置
3. 空闲检测：开启testOnBorrow/testWhileIdle
4. 超时设置：合理设置连接获取超时

11. SQL优化实战经验

在实际项目中，我总结出以下经验：

1. 优化是一个持续过程：随着数据量增长，需要不断调整
2. 不要过度优化：简单的查询不需要复杂优化
3. 关注执行计划：理论分析要与实际执行计划结合
4. 监控慢查询：建立慢查询日志分析机制

一个典型的优化案例：将某个从1200ms降到50ms的查询，主要步骤是：

1. 分析EXPLAIN发现全表扫描
2. 添加复合索引
3. 重写查询使用覆盖索引
4. 调整JOIN顺序

12. 工具与资源推荐

12.1 性能分析工具

1. pt-query-digest：分析MySQL慢查询日志
2. Percona Toolkit：专业的MySQL工具集
3. MySQL Workbench：可视化执行计划分析

12.2 学习资源

1. 《高性能MySQL》：必读经典
2. MySQL官方文档：最权威的参考
3. Percona博客：最新的优化技术分享

SQL优化是一门需要理论与实践结合的技能。我建议开发者在实际工作中养成查看执行计划的习惯，定期分析慢查询日志，持续积累优化经验。每个数据库、每个业务场景都可能需要不同的优化策略，没有放之四海而皆准的银弹。