SQL优化常用的15种方法技巧总结

前言

SQL优化是一个备受关注的热门话题，不论是在面试还是实际工作中，都可能会面临性能问题需要进行优化。当线上接口出现性能问题时，很多时候我们首先考虑的是优化SQL语句，因为相对于代码改造来说，SQL语句的优化成本通常较小。

本文将从15个方面分享SQL优化的一些小技巧，希望对大家在实际工作中的SQL性能优化有所帮助。

1. 避免使用select *

很多时候，为了方便，我们倾向于直接使用select *，一次性查询表中所有列的数据。然而，在实际业务场景中，往往只需要使用其中一两列的数据。使用select *查询会浪费数据库资源，增加网络IO传输时间，并且不会走覆盖索引，导致性能低下。

优化方法： 仅查询需要使用的列，例如 select name, age from user where id=1;。

2. 用union all代替union

在使用union关键字时，会获取排重后的数据。但是，如果可以接受包含重复数据的结果，使用union all效率更高，因为排重的过程会增加时间和CPU资源消耗。

优化方法： 使用 union all 替代 union，除非业务需求明确要求排重。

3. 小表驱动大表

在查询涉及到多张表时，使用in或exists关键字，可以根据业务场景选择合适的方式。一般来说，小表驱动大表，即用小表的数据集驱动大表的数据集，可以提高查询性能。

优化方法： 根据具体情况选择使用 in 或 exists，注意小表在关键位置。

4. 批量操作

对于需要插入大量数据的场景，避免在循环中逐条插入数据，而是使用批量插入的方式，减少数据库请求次数，提高性能。

优化方法： 提供批量插入数据的方法，如 insert into order(id, code, user_id) values (123,'001',100),(124,'002',100),(125,'003',101);。

5. 多用limit

在需要查询某些数据的第一条记录时，使用limit 1可以有效减少数据传输和处理的开销。

优化方法： 使用 limit 1 来获取第一条记录，例如 select id, create_date from order where user_id=123 order by create_date asc limit 1;。

6. in中值太多

对于批量查询接口，使用in关键字过滤数据时，限制每次查询的数据量，以避免接口超时和性能问题。

优化方法： 在SQL中使用limit对数据进行限制，并在业务代码中加入相应的限制逻辑。

7. 增量查询

在需要从远程接口查询数据并同步到另一数据库的场景中，采用增量查询的方式，逐步同步数据，以提高查询效率。

优化方法： 根据上次同步的最大ID和时间，使用limit限制每次同步的数据量。

8. 高效的分页

对于大量数据的分页查询，使用合理的limit关键字结合索引进行分页，避免浪费资源和性能问题。

优化方法： 使用索引并合理设置limit，例如 select id, name, age from user where id > 1000000 limit 20;。

9. 用连接查询代替子查询

在多表查询时，使用连接查询（如inner join）代替子查询，以避免创建临时表和额外性能消耗。

优化方法： 使用连接查询，例如 select o.id, o.code, u.name from order o inner join user u on o.user_id = u.id where u.status=1;。

10. join的表不宜过多

限制join操作涉及的表的数量，防止复杂的索引选择和性能问题。

优化方法： 控制join表的数量，尽量使用联合索引或冗余字段减少join操作。

11. join时要注意

在多表联合查询时，注意使用inner join而不是left join，并确保左表是小表，以避免性能问题。

优化方法： 尽量使用inner join，并确保小表在左边。

12. 控制索引的数量

控制单表索引数量，避免过多索引导致性能损耗。

优化方法： 控制单表索引数量不超过5个，单个索引中字段数不超过5个。

13. 选择合理的字段类型

根据实际情况选择合理的字段类型，减少存储空间浪费和提高查询效率。

优化方法： 使用小的字段类型，注意选择固定长度或可变长度的字符串字段，避免精度丢失问题。

14. 提升group by的效率

在使用group by时，先通过where条件过滤数据，减小数据范围，提高效率。

优化方法： 将where条件放在group by之前，例如 select user_id, user_name from order where user_id <= 200 group by user_id;。

15. 索引优化

通过使用explain命令查看执行计划，检查SQL语句是否使用了索引，并根据需要调整索引。

优化方法： 使用explain命令查看执行计划，优化SQL语句中的索引使用。

浏览量: 247