河南企业做网站,具有价值的网站制作,企业网站托管价格,酷站字体文章目录 一、前言二、建议2.1 使用索引2.2 避免使用select *2.3. 使用表连接代替子查询2.4. 优化WHERE子句#xff0c;减少返回结果集的大小2.5 用union all代替union2.6 使用合适的聚合策略2.7 避免在WHERE子句中使用函数2.8 使用EXPLAIN分析查询2.9 小表驱动大表2.10 使用窗… 文章目录 一、前言二、建议2.1 使用索引2.2 避免使用select *2.3. 使用表连接代替子查询2.4. 优化WHERE子句减少返回结果集的大小2.5 用union all代替union2.6 使用合适的聚合策略2.7 避免在WHERE子句中使用函数2.8 使用EXPLAIN分析查询2.9 小表驱动大表2.10 使用窗口函数代替子查询2.11 使用适当的数据类型2.12 优化分页查询2.13 in中值太多 三、总结 一、前言
在系统开发中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 id1)
union
(select * from user where id2);排重的过程需要遍历、排序和比较它更耗时更消耗cpu资源。 建议如果能用union all的时候尽量不用union。 正例
(select * from user where id1)
union all
(select * from user where id2);除非是有些特殊的场景比如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 status1)也可以使用exists关键字实现
select * from order
where exists (select 1 from user where order.user_id user.id and status1)前面提到的这种业务场景使用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(*) 1FROM users u2WHERE 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 ListCategory getCategory(ListLong 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查询的优化都是相对的要根据具体业务和库表数据量的大小选择合适的优化方案。
