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 name, (SELECT department_name FROM departments WHERE id = employees.department_id) AS department_name
-- FROM user;
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查询的优化都是相对的，要根据具体业务和库表数据量的巨细选择符合的优化方案。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]