1. SQL优化的重要性与基本原则作为一名长期与数据库打交道的开发者我深知SQL优化对系统性能的关键影响。在实际项目中一条糟糕的SQL语句可能拖垮整个系统而经过优化的查询则能让性能提升数十倍甚至上百倍。SQL优化不是简单的技巧堆砌而是需要理解数据库工作原理后的系统性思考。SQL优化的核心目标是减少数据库的I/O操作和CPU计算量。数据库执行查询时主要消耗在从磁盘读取数据页到内存在内存中进行排序、连接等计算操作将结果返回给客户端优化SQL就是要尽量减少这三个环节的资源消耗。下面我将分享15个经过实战验证的优化技巧每个技巧都配有具体场景和实现代码。2. 查询语句优化技巧2.1 避免使用SELECT *新手开发者常犯的错误就是习惯性使用SELECT *。这种做法会导致三个问题网络传输开销增大返回不需要的字段会占用更多网络带宽内存消耗增加数据库需要为所有字段分配内存空间索引失效风险当使用覆盖索引时SELECT *会导致回表查询优化方案是只查询需要的字段-- 不推荐 SELECT * FROM user WHERE id1; -- 推荐 SELECT name, age FROM user WHERE id1;注意即使需要大部分字段也建议显式列出字段名。这样当表结构变更时可以避免因字段顺序变化导致的问题。2.2 用UNION ALL代替UNIONUNION和UNION ALL的区别在于前者会去除重复行这个去重操作需要排序和比较消耗大量CPU资源。当确定结果集没有重复或允许重复时应使用UNION ALL。-- 消耗资源的写法 (SELECT * FROM user WHERE id1) UNION (SELECT * FROM user WHERE id2); -- 高效写法 (SELECT * FROM user WHERE id1) UNION ALL (SELECT * FROM user WHERE id2);实测在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 status1); -- 当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.status1);3. 批量操作与查询控制3.1 批量操作减少交互频繁的单条INSERT操作会产生大量网络往返和事务开销。批量操作可以显著提升性能// 不推荐循环单条插入 for(User user : userList) { userMapper.insert(user); } // 推荐批量插入 userMapper.insertBatch(userList);在MySQL中批量插入比单条插入快10-100倍。但要注意单批数据量不宜过大建议500-1000条/批避免内存溢出和长事务。3.2 使用LIMIT限制结果集获取大量数据时应该使用LIMIT分页避免内存溢出// 不推荐一次性获取全部 ListOrder orders orderMapper.getAllOrders(); // 推荐分页获取 ListOrder 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 ListCategory getCategories(ListLong ids) { if(CollectionUtils.isEmpty(ids)) { return Collections.emptyList(); } if(ids.size() 500) { // 可以分批查询或抛出异常 throw new BusinessException(单次查询ID数量不能超过500); } return categoryMapper.getByIds(ids); }对于大量ID查询可以考虑分批查询后合并结果使用临时表JOIN使用专门的搜索引擎4. 高级查询优化技术4.1 增量查询模式对于数据同步或导出场景增量查询比全量查询更高效SELECT id, name, create_time FROM product WHERE id #{lastId} AND update_time #{lastUpdateTime} ORDER BY id LIMIT 1000;这种模式避免了全表扫描利用了id主键索引通过时间条件进一步过滤每次只获取部分数据内存压力小4.2 JOIN优化策略JOIN操作是SQL中最消耗资源的操作之一优化要点包括控制JOIN表数量建议不超过3张表过多JOIN会导致执行计划复杂小表驱动大表将小表放在JOIN左侧避免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.status1; -- 正确写法应把条件放在ON子句 SELECT o.id, o.code, u.name FROM order o LEFT JOIN user u ON o.user_id u.id AND u.status1;4.3 用连接查询代替子查询现代数据库对JOIN的优化通常比对子查询的优化更好-- 不推荐使用子查询 SELECT * FROM product WHERE category_id IN (SELECT id FROM category WHERE type1); -- 推荐使用JOIN SELECT p.* FROM product p JOIN category c ON p.category_id c.id WHERE c.type1;5. 索引优化策略5.1 索引数量控制原则索引不是越多越好需要平衡查询性能和写入性能单表索引数控制在5个以内阿里巴巴开发手册建议单个索引字段不超过5个复合索引字段过多效果递减高并发系统更需严格控制索引会增加INSERT/UPDATE/DELETE开销优化方案使用复合索引代替多个单列索引删除不使用的冗余索引将部分查询迁移到搜索引擎5.2 高效索引设计设计索引时要考虑选择性高的列在前区分度高的列放在复合索引前面常用查询条件优先WHERE和ORDER BY常用字段建索引避免索引失效场景对索引列使用函数隐式类型转换使用!或操作符使用前导通配符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_id123 AND status1 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_id123 AND status1 ORDER BY create_time DESC LIMIT 10;6. 数据类型与表设计优化6.1 合理选择字段类型字段类型选择直接影响存储空间和查询效率数字类型优于字符串整型比较比字符串快固定长度用CHAR如手机号、MD5值变长字符串用VARCHAR节省存储空间金额使用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优化的基础每个表必须有主键避免使用TEXT/BLOB存储频繁查询的数据将大字段拆分到单独表合理使用范式化和反范式化7. 分组与聚合查询优化7.1 GROUP BY优化GROUP BY操作通常需要临时表和排序优化方法WHERE条件先过滤减少参与分组的数据量使用索引覆盖避免回表操作考虑使用物化视图预计算常用聚合结果-- 不推荐全表分组 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 分页查询优化深分页是常见性能瓶颈优化方案使用主键分页WHERE id last_id方式延迟关联先查ID再关联获取完整数据缓存分页结果适合变化不频繁的数据-- 低效的分页 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优化的必备工具关键指标type列从优到差 system const eq_ref ref range index ALLkey列实际使用的索引rows列预估扫描行数Extra列Using filesort、Using temporary等警告EXPLAIN SELECT * FROM user WHERE name LIKE 张%;8.2 常见性能问题排查全表扫描typeALL添加适当索引文件排序Using filesort优化ORDER BY子句临时表Using temporary优化GROUP BY或子查询索引失效检查查询条件是否符合最左前缀原则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_id123 AND status1;9.3 使用MRR优化多范围读取优化MRR可以减少随机IO-- 启用MRR SET optimizer_switchmrron,mrr_cost_basedoff; -- MRR优化的查询 SELECT * FROM user WHERE age BETWEEN 20 AND 30;10. 数据库配置优化10.1 关键参数调整根据服务器配置调整MySQL参数innodb_buffer_pool_size通常设为物理内存的70-80%innodb_log_file_size128M-2G影响写入性能max_connections根据应用需求设置避免过高10.2 连接池配置应用层连接池配置建议初始大小10-20最大大小根据DB负载能力设置空闲检测开启testOnBorrow/testWhileIdle超时设置合理设置连接获取超时11. SQL优化实战经验在实际项目中我总结出以下经验优化是一个持续过程随着数据量增长需要不断调整不要过度优化简单的查询不需要复杂优化关注执行计划理论分析要与实际执行计划结合监控慢查询建立慢查询日志分析机制一个典型的优化案例将某个从1200ms降到50ms的查询主要步骤是分析EXPLAIN发现全表扫描添加复合索引重写查询使用覆盖索引调整JOIN顺序12. 工具与资源推荐12.1 性能分析工具pt-query-digest分析MySQL慢查询日志Percona Toolkit专业的MySQL工具集MySQL Workbench可视化执行计划分析12.2 学习资源《高性能MySQL》必读经典MySQL官方文档最权威的参考Percona博客最新的优化技术分享SQL优化是一门需要理论与实践结合的技能。我建议开发者在实际工作中养成查看执行计划的习惯定期分析慢查询日志持续积累优化经验。每个数据库、每个业务场景都可能需要不同的优化策略没有放之四海而皆准的银弹。 
长河君 · 2026/11/5 4:49:38 · 阅读 1.2k 15个实战验证的SQL优化技巧与性能提升策略
1. SQL优化的重要性与基本原则作为一名长期与数据库打交道的开发者我深知SQL优化对系统性能的关键影响。在实际项目中一条糟糕的SQL语句可能拖垮整个系统而经过优化的查询则能让性能提升数十倍甚至上百倍。SQL优化不是简单的技巧堆砌而是需要理解数据库工作原理后的系统性思考。SQL优化的核心目标是减少数据库的I/O操作和CPU计算量。数据库执行查询时主要消耗在从磁盘读取数据页到内存在内存中进行排序、连接等计算操作将结果返回给客户端优化SQL就是要尽量减少这三个环节的资源消耗。下面我将分享15个经过实战验证的优化技巧每个技巧都配有具体场景和实现代码。2. 查询语句优化技巧2.1 避免使用SELECT *新手开发者常犯的错误就是习惯性使用SELECT *。这种做法会导致三个问题网络传输开销增大返回不需要的字段会占用更多网络带宽内存消耗增加数据库需要为所有字段分配内存空间索引失效风险当使用覆盖索引时SELECT *会导致回表查询优化方案是只查询需要的字段-- 不推荐 SELECT * FROM user WHERE id1; -- 推荐 SELECT name, age FROM user WHERE id1;注意即使需要大部分字段也建议显式列出字段名。这样当表结构变更时可以避免因字段顺序变化导致的问题。2.2 用UNION ALL代替UNIONUNION和UNION ALL的区别在于前者会去除重复行这个去重操作需要排序和比较消耗大量CPU资源。当确定结果集没有重复或允许重复时应使用UNION ALL。-- 消耗资源的写法 (SELECT * FROM user WHERE id1) UNION (SELECT * FROM user WHERE id2); -- 高效写法 (SELECT * FROM user WHERE id1) UNION ALL (SELECT * FROM user WHERE id2);实测在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 status1); -- 当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.status1);3. 批量操作与查询控制3.1 批量操作减少交互频繁的单条INSERT操作会产生大量网络往返和事务开销。批量操作可以显著提升性能// 不推荐循环单条插入 for(User user : userList) { userMapper.insert(user); } // 推荐批量插入 userMapper.insertBatch(userList);在MySQL中批量插入比单条插入快10-100倍。但要注意单批数据量不宜过大建议500-1000条/批避免内存溢出和长事务。3.2 使用LIMIT限制结果集获取大量数据时应该使用LIMIT分页避免内存溢出// 不推荐一次性获取全部 ListOrder orders orderMapper.getAllOrders(); // 推荐分页获取 ListOrder 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 ListCategory getCategories(ListLong ids) { if(CollectionUtils.isEmpty(ids)) { return Collections.emptyList(); } if(ids.size() 500) { // 可以分批查询或抛出异常 throw new BusinessException(单次查询ID数量不能超过500); } return categoryMapper.getByIds(ids); }对于大量ID查询可以考虑分批查询后合并结果使用临时表JOIN使用专门的搜索引擎4. 高级查询优化技术4.1 增量查询模式对于数据同步或导出场景增量查询比全量查询更高效SELECT id, name, create_time FROM product WHERE id #{lastId} AND update_time #{lastUpdateTime} ORDER BY id LIMIT 1000;这种模式避免了全表扫描利用了id主键索引通过时间条件进一步过滤每次只获取部分数据内存压力小4.2 JOIN优化策略JOIN操作是SQL中最消耗资源的操作之一优化要点包括控制JOIN表数量建议不超过3张表过多JOIN会导致执行计划复杂小表驱动大表将小表放在JOIN左侧避免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.status1; -- 正确写法应把条件放在ON子句 SELECT o.id, o.code, u.name FROM order o LEFT JOIN user u ON o.user_id u.id AND u.status1;4.3 用连接查询代替子查询现代数据库对JOIN的优化通常比对子查询的优化更好-- 不推荐使用子查询 SELECT * FROM product WHERE category_id IN (SELECT id FROM category WHERE type1); -- 推荐使用JOIN SELECT p.* FROM product p JOIN category c ON p.category_id c.id WHERE c.type1;5. 索引优化策略5.1 索引数量控制原则索引不是越多越好需要平衡查询性能和写入性能单表索引数控制在5个以内阿里巴巴开发手册建议单个索引字段不超过5个复合索引字段过多效果递减高并发系统更需严格控制索引会增加INSERT/UPDATE/DELETE开销优化方案使用复合索引代替多个单列索引删除不使用的冗余索引将部分查询迁移到搜索引擎5.2 高效索引设计设计索引时要考虑选择性高的列在前区分度高的列放在复合索引前面常用查询条件优先WHERE和ORDER BY常用字段建索引避免索引失效场景对索引列使用函数隐式类型转换使用!或操作符使用前导通配符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_id123 AND status1 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_id123 AND status1 ORDER BY create_time DESC LIMIT 10;6. 数据类型与表设计优化6.1 合理选择字段类型字段类型选择直接影响存储空间和查询效率数字类型优于字符串整型比较比字符串快固定长度用CHAR如手机号、MD5值变长字符串用VARCHAR节省存储空间金额使用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优化的基础每个表必须有主键避免使用TEXT/BLOB存储频繁查询的数据将大字段拆分到单独表合理使用范式化和反范式化7. 分组与聚合查询优化7.1 GROUP BY优化GROUP BY操作通常需要临时表和排序优化方法WHERE条件先过滤减少参与分组的数据量使用索引覆盖避免回表操作考虑使用物化视图预计算常用聚合结果-- 不推荐全表分组 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 分页查询优化深分页是常见性能瓶颈优化方案使用主键分页WHERE id last_id方式延迟关联先查ID再关联获取完整数据缓存分页结果适合变化不频繁的数据-- 低效的分页 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优化的必备工具关键指标type列从优到差 system const eq_ref ref range index ALLkey列实际使用的索引rows列预估扫描行数Extra列Using filesort、Using temporary等警告EXPLAIN SELECT * FROM user WHERE name LIKE 张%;8.2 常见性能问题排查全表扫描typeALL添加适当索引文件排序Using filesort优化ORDER BY子句临时表Using temporary优化GROUP BY或子查询索引失效检查查询条件是否符合最左前缀原则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_id123 AND status1;9.3 使用MRR优化多范围读取优化MRR可以减少随机IO-- 启用MRR SET optimizer_switchmrron,mrr_cost_basedoff; -- MRR优化的查询 SELECT * FROM user WHERE age BETWEEN 20 AND 30;10. 数据库配置优化10.1 关键参数调整根据服务器配置调整MySQL参数innodb_buffer_pool_size通常设为物理内存的70-80%innodb_log_file_size128M-2G影响写入性能max_connections根据应用需求设置避免过高10.2 连接池配置应用层连接池配置建议初始大小10-20最大大小根据DB负载能力设置空闲检测开启testOnBorrow/testWhileIdle超时设置合理设置连接获取超时11. SQL优化实战经验在实际项目中我总结出以下经验优化是一个持续过程随着数据量增长需要不断调整不要过度优化简单的查询不需要复杂优化关注执行计划理论分析要与实际执行计划结合监控慢查询建立慢查询日志分析机制一个典型的优化案例将某个从1200ms降到50ms的查询主要步骤是分析EXPLAIN发现全表扫描添加复合索引重写查询使用覆盖索引调整JOIN顺序12. 工具与资源推荐12.1 性能分析工具pt-query-digest分析MySQL慢查询日志Percona Toolkit专业的MySQL工具集MySQL Workbench可视化执行计划分析12.2 学习资源《高性能MySQL》必读经典MySQL官方文档最权威的参考Percona博客最新的优化技术分享SQL优化是一门需要理论与实践结合的技能。我建议开发者在实际工作中养成查看执行计划的习惯定期分析慢查询日志持续积累优化经验。每个数据库、每个业务场景都可能需要不同的优化策略没有放之四海而皆准的银弹。