sql 优化，提高查询速度

一、前言

在系统开发中，SQL查询的优化是提高应用性能和响应速度的关键。以下是SQL语句优化的建议，希望对您有帮助：

二、建议

2.1 使用索引

原因：索引可以极大地减少数据库需要扫描的数据量，加快查询速度。
建议：为查询中经常作为WHERE条件、JOIN条件或ORDER BY的列创建索引。

SQL例子：
假设有一个user表，经常按department_id查询

CREATE INDEX idx_department_id ON user(department_id);
SELECT * FROM user WHERE department_id = 20;

2.2 避免使用select *

原因：在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。
此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。
还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。
建议：避免使用SELECT *，只选择需要的列。

SQL例子：
– 只需获取用户的姓名和部门ID

SELECT name, department_id FROM user;

2.3. 使用表连接代替子查询

原因：在某些情况下，JOIN操作比子查询更高效，因为JOIN允许数据库优化器更有效地执行查询计划。
建议：当可能时，使用JOIN代替子查询。

SQL例子：
使用JOIN

SELECT e.name, d.department_name
FROM user e
JOIN departments d ON e.department_id = d.id;

替代的子查询版本

SELECT e.name, (SELECT d.department_name FROM departments d WHERE d.id = e.department_id) AS department_name
FROM user e;

2.4. 优化WHERE子句，减少返回结果集的大小

原理：减少返回结果集的大小可以加快查询速度。
建议：在WHERE子句中过滤掉尽可能多的行。
SQL例子：
假设有大量的数据，但只对特定条件的记录感兴趣

SELECT * FROM orders WHERE status = 'inventory' AND order_date > '2024-01-01';

2.5 用union all代替union

原因：我们都知道sql语句使用union关键字后，可以获取排重后的数据。
而如果使用union all关键字，可以获取所有数据，包含重复的数据。
反例：

(select * from user where id=1) 
union 
(select * from user where id=2);

排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。
建议：如果能用union all的时候，尽量不用union。
正例：

(select * from user where id=1) 
union all
(select * from user where id=2);

除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。

2.6 使用合适的聚合策略

原因：合理使用GROUP BY和HAVING可以减少数据处理的复杂性。
建议：仅在必要时使用GROUP BY，并考虑使用HAVING代替WHERE对聚合结果进行过滤。

SQL例子：
对订单按状态分组，并筛选总金额超过一定值的组

SELECT status, COUNT(*), SUM(amount)
FROM orders
GROUP BY status
HAVING SUM(amount) > 1000;

2.7 避免在WHERE子句中使用函数

原因：在WHERE子句中对列使用函数会阻止索引的使用。
建议：尽可能避免在WHERE子句中对列使用函数。
SQL例子：
不推荐（可能无法利用索引）

SELECT * FROM user WHERE YEAR(hire_date) = 2020;

推荐

SELECT * FROM user WHERE hire_date >= '2020-01-01' AND hire_date < '2024-01-01';

2.8 使用EXPLAIN分析查询

原因：了解查询的执行计划和性能瓶颈。
建议：使用EXPLAIN或类似工具分析查询，并根据结果调整索引或查询结构。
SQL例子：
大多数数据库管理系统都支持EXPLAIN命令

EXPLAIN SELECT * FROM user WHERE department_id = 20;

2.9 小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。
假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。
这时如果想查一下，所有有效的用户下过的订单列表。
可以使用in关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。
为什么呢？
因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。
而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。
这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。
总结一下：
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。
不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

2.10 使用窗口函数代替子查询

原因：窗口函数（如ROW_NUMBER()、RANK()等）可以在不改变结果集行数的情况下为每行提供额外的计算列，这通常比使用子查询更高效。
建议：当需要为结果集中的每行添加基于整个结果集的额外信息时，考虑使用窗口函数。
例子：
优化前（使用子查询计算排名）

SELECT id, name,
       (SELECT COUNT(*) + 1
        FROM users u2
        WHERE u2.score > u.score) AS rank
FROM users u;

优化后（使用窗口函数计算排名）

SELECT id, name,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM users;

2.11 使用适当的数据类型

原因：选择合适的数据类型可以减少存储空间和查询时间。
建议：避免使用过大的数据类型，如使用INT代替VARCHAR存储数字。
SQL例子：
创建表时选择合适的数据类型

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id)
);

2.12 优化分页查询

原因：当使用LIMIT和OFFSET进行分页时，随着页码的增加，查询性能会逐渐下降，因为数据库需要扫描越来越多的行来找到所需的起始点。
建议：使用基于索引的查询来优化分页，特别是当表很大时。例如，可以记录上一页最后一条记录的某个唯一标识符（如ID），并使用它作为下一页查询的起点。
例子：
优化前（随着页码增加性能下降）

SELECT * FROM user LIMIT 10 OFFSET 100;

优化后（使用上一页的最后一条记录的ID）

SELECT * FROM user WHERE id > LAST_SEEN_ID ORDER BY id LIMIT 10;

2.13 in中值太多

对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。
sql语句如下：

select id,name from category
where id in (1,2,3...100000000);

如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。
这时该怎么办呢？

select id,name from category
where id in (1,2,3...100)
limit 500;

可以在sql中对数据用limit做限制。
不过我们更多的是要在业务代码中加限制，伪代码如下：

public List<Category> getCategory(List<Long> ids) {
   if(CollectionUtils.isEmpty(ids)) {
      return null;
   }
   if(ids.size() > 500) {
      throw new BusinessException("一次最多允许查询500条记录")
   }
   return mapper.getCategoryList(ids);
}

还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。

不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。

三、总结

SQL查询的优化都是相对的，要根据具体业务和库表数据量的大小选择合适的优化方案。