sql 优化，提高查询速率

---

一、前言

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

2.1 利用索引

原因：索引可以极大地减少数据库需要扫描的数据量，加快查询速率。
建议：为查询中常常作为WHERE条件、JOIN条件或ORDER BY的列创建索引。
SQL例子：
假设有一个user表，常常按department_id查询

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

复制代码

2.2 避免利用select *

原因：在现实业务场景中，可能我们真正需要利用的只有此中一两列。查了许多数据，但是不用，白白浪费了数据库资源，好比：内存大概cpu。
别的，多查出来的数据，通过网络IO传输的过程中，也会增长数据传输的时间。
还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。
建议：避免利用SELECT *，只选择需要的列。
SQL例子：
– 只需获取用户的姓名和部门ID

1. SELECT name, department_id FROM user;

复制代码

2.3. 利用表毗连代替子查询

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

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

复制代码

替换的子查询版本

1. -- SELECT name, (SELECT department_name FROM departments WHERE id = employees.department_id) AS department_name
2. -- FROM user;

复制代码

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

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

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

复制代码

2.5 用union all代替union

原因：我们都知道sql语句利用union关键字后，可以获取排重后的数据。
而假如利用union all关键字，可以获取全部数据，包罗重复的数据。
反例：

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

复制代码

排重的过程需要遍历、排序和比力，它更耗时，更斲丧cpu资源。
建议：假如能用union all的时间，只管不用union。
正例：

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

复制代码

除非是有些特殊的场景，好比union all之后，结果会合出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以利用union。
2.6 利用符合的聚合策略

原因：公道利用GROUP BY和HAVING可以减少数据处理的复杂性。
建议：仅在须要时利用GROUP BY，并考虑利用HAVING代替WHERE对聚合结果进行过滤。
SQL例子：
对订单按状态分组，并筛选总金额超过一定值的组

1. SELECT status, COUNT(*), SUM(amount)
2. FROM orders
3. GROUP BY status
4. HAVING SUM(amount) > 1000;

复制代码

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

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

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

复制代码

推荐

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

复制代码

2.8 利用EXPLAIN分析查询

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

1. EXPLAIN SELECT * FROM user WHERE department_id = 20;

复制代码

2.9 小表驱动大表

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

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

复制代码

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

1. select * from order
2. 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()等）可以在不改变结果集行数的情况下为每行提供额外的计算列，这通常比利用子查询更高效。
建议：当需要为结果会合的每行添加基于整个结果集的额外信息时，考虑利用窗口函数。
例子：
优化前（利用子查询计算排名）

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

复制代码

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

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

复制代码

2.11 利用适当的数据类型

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

1. CREATE TABLE sales (
2.     id INT AUTO_INCREMENT,
3.     amount DECIMAL(10, 2),
4.     PRIMARY KEY (id)
5. );

复制代码

2.12 优化分页查询

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

1. SELECT * FROM user LIMIT 10 OFFSET 100;

复制代码

优化后（利用上一页的末了一条记载的ID）

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

复制代码

2.13 in中值太多

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

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

复制代码

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

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

复制代码

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

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

复制代码

还有一个方案就是：假如ids超过500条记载，可以分批用多线程去查询数据。每批只查500条记载，末了把查询到的数据汇总到一起返回。
不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但假如返回的数据量太大了，网络传输也是非常斲丧性能的，接口性能始终好不到那里去。
三、总结

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。