sql 优化，提高查询速度

news/2024/9/18 20:58:57/ 标签: sql, 数据库, oracle

文章目录

  • 一、前言
  • 二、建议
    • 2.1 使用索引
    • 2.2 避免使用select *
    • 2.3. 使用表连接代替子查询
    • 2.4. 优化WHERE子句，减少返回结果集的大小
    • 2.5 用union all代替union
    • 2.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查询

sql">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

sql">SELECT name, department_id FROM user;

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

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

SQL例子：
使用JOIN

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

替代的子查询版本

sql">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例子：
假设有大量的数据，但只对特定条件的记录感兴趣

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

2.5 用union all代替union

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

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

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

sql">(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例子：
对订单按状态分组，并筛选总金额超过一定值的组

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

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

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

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

推荐

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

2.8 使用EXPLAIN分析查询

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

sql">EXPLAIN SELECT * FROM user WHERE department_id = 20;

2.9 小表驱动大表

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

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

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

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

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

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

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

2.11 使用适当的数据类型

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

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

2.12 优化分页查询

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

sql">SELECT * FROM user LIMIT 10 OFFSET 100;

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

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

2.13 in中值太多

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

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

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

sql">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查询的优化都是相对的，要根据具体业务和库表数据量的大小选择合适的优化方案。


http://www.ppmy.cn/news/1518744.html

相关文章

PHP程序设计教案

PHP程序设计教案

文章目录&#xff1a; 一&#xff1a;前言 1.什么是PHP 2.环境安装 3. 语法规范 3.1 注释 3.2 分隔符 3.3 其他规范 二&#xff1a;基础语法 1.输出 1.1 echo 1.2 print 1.3 var_dump类型和值 1.4 print_r()易读 2.常量变量 2.1 常量 2.1.1 define()/const…
阅读更多...
vue前端实现登录页面的验证码(新手版)

vue前端实现登录页面的验证码(新手版)

一、搭建vue前端登录页面 <template><div style"width: 800px; margin: 5px auto; background-color: #17ecf3"><div align"center"><h2>用户登录</h2></div><div style"width: 60%; margin: 1px auto"…
阅读更多...
如何解决`.gitignore`规则不生效或已提交相关文件的问题

如何解决`.gitignore`规则不生效或已提交相关文件的问题

前言 在使用Git进行版本控制时&#xff0c;.gitignore文件是一个非常有用的工具&#xff0c;它可以帮助我们排除不需要跟踪的文件或目录。然而&#xff0c;在实际开发过程中&#xff0c;有时我们会遇到.gitignore规则不生效的情况&#xff0c;或者是不小心将不应提交的文件提交…
阅读更多...
RabbitMQ 入门教程

RabbitMQ 入门教程

RabbitMQ 入门教程 1. 引言 RabbitMQ 是一个开源的消息代理和队列服务器&#xff0c;实现高级消息队列协议 (AMQP)。它能帮助开发者实现应用程序间的解耦、异步处理、流量削峰等需求。 2. 安装与配置 2.1 安装RabbitMQ 2.1.1 Ubuntu bash sudo apt-get update sudo apt…
阅读更多...
动态IP池在数据抓取中的应用与优势

动态IP池在数据抓取中的应用与优势

随着互联网技术的快速发展&#xff0c;数据抓取&#xff08;Web Scraping&#xff09;已经成为获取互联网信息的重要手段。然而&#xff0c;在进行大规模数据抓取时&#xff0c;往往会遇到反爬虫机制、IP封禁等问题。动态IP池作为一种解决方案&#xff0c;可以有效地绕过这些障…
阅读更多...
告别手动记录，音频转文字软件助力会议记录新高度

告别手动记录，音频转文字软件助力会议记录新高度

如果你突然被领导指派去参与一场会议&#xff0c;身边没有纸笔要怎么记录转达会议内容呢&#xff1f;我往往会采用手机的录音功能来记录会议内容会后再进行整理。这次我们就来探索音频转文字工具怎么提升我们的工作效率。 1.365在线转文字 链接传送&#xff1a;https://www.p…
阅读更多...
微服务优缺点以及如何拆分

微服务优缺点以及如何拆分

微服务优点 1,降低代码逻辑复杂度。 单个微服务模块相当于一个项目&#xff0c;开发人员只用关心这个模块的逻辑即可。 2&#xff0c;技术栈更加灵活 不同的微服务可以使用合适的语言架构实现&#xff0c;然后把服务注册到一个注册中心即可相互调用。 3&#xff0c;按需伸缩 当…
阅读更多...
人工智能工作级开发者认证 HCCDP – AI 真题2 答案

人工智能工作级开发者认证 HCCDP – AI 真题2 答案

1.GBDT通过bagging的防范可以对样本和特征都进行采集。答案:FALSE 原因:GBDT可以对样本采集，不能对特征采集 2.深度学习是机器学习的一个分支。答案:true 3.softmax激活函数的作用是减少及时量和防止梯度消失。答案false 4.在建筑施工现场，基于定制化的图像识别目标检测系统，…
阅读更多...
Node.js 安装与使用及连接 MongoDB 的详细教程

Node.js 安装与使用及连接 MongoDB 的详细教程

下面我将详细讲解如何安装 Node.js、介绍 Node.js 的脚手架工具、使用 Express 脚手架创建项目&#xff0c;以及如何安装和连接 MongoDB。 一、Node.js 安装 下载 Node.js&#xff1a; 访问 Node.js 官方网站。 根据你的操作系统选择最新的 LTS&#xff08;长期支持版&#x…
阅读更多...
从自动驾驶看无人驾驶叉车的技术落地和应用

从自动驾驶看无人驾驶叉车的技术落地和应用

摘 要 &#xff5c; 介绍无人驾驶叉车在自动驾驶技术中的应用&#xff0c;分析其关键技术&#xff0c;如环境感知、定位、路径规划等&#xff0c;并讨论机器学习算法和强化学习算法的应用以提高无人叉车的运行效率和准确性。无人叉车在封闭结构化环境、机器学习、有效数据集等方…
阅读更多...
参加 帆软 BI 上海城市 课堂（08-30培训）

参加 帆软 BI 上海城市 课堂（08-30培训）

参加 帆软 BI 城市 课堂&#xff08;0830&#xff09;&#xff1a; 由于目前是自由职业&#xff0c;也想学习一下新的知识 。所以参加本次的培训&#xff0c;总的来说还是比较专业。 培训在 上海 帆软的总部 环球港进行。时间是 13:30~17&#xff1a;00 老师很专业。学习中 课…
阅读更多...
关于前端布局的基础知识

关于前端布局的基础知识

float 横向布局 float 实现横向布局&#xff0c;需要向横着布局的元素添加float 其值left right 存在问题 如果使用float 所在父级五高度&#xff0c;会导致下方的元素上移 top的高度被吞了 解决方法&#xff1a; 给父级元素设置高度&#xff1a;不推荐&#xff0c;需要给父级…
阅读更多...
LeetCode第65题 有效数字 结合设计模式：状态模式

LeetCode第65题 有效数字 结合设计模式：状态模式

思路&#xff1a;有限状态机&#xff0c;结合Java的设计模式&#xff1a;状态模式。 单纯用状态机会有大量的if-else&#xff0c;非常不好看&#xff0c;思路不清晰 用设计模式则非常清楚。但是设计模式是为了给人理解的&#xff0c;对机器而言也可能稍微影响性能&#xff1b; …
阅读更多...
IO练习--随机点名

IO练习--随机点名

随机点名器1 需求: 有一个文件里面存储了班级同学的信息&#xff0c;每一个信息占一行。 格式为:张三-男-23 要求通过程序实现随机点名器。 运行效果: 第一次运行程序:随机同学姓名1(只显示名字) 第二次运行程序:随机同学姓名2(只显示名字) 第三次运行程序:随机同学姓名3(只显…
阅读更多...
【精选】基于Hadoop的用户网站浏览分析的设计与实现（全网最新定制，独一无二）

【精选】基于Hadoop的用户网站浏览分析的设计与实现（全网最新定制，独一无二）

博主介绍&#xff1a; ✌我是阿龙&#xff0c;一名专注于Java技术领域的程序员&#xff0c;全网拥有10W粉丝。作为CSDN特邀作者、博客专家、新星计划导师&#xff0c;我在计算机毕业设计开发方面积累了丰富的经验。同时&#xff0c;我也是掘金、华为云、阿里云、InfoQ等平台…
阅读更多...
2.10鼠标事件

2.10鼠标事件

目录 实验原理 实验代码 运行结果 文章参考 实验原理 在 OpenCV 中存在鼠标的操作&#xff0c;比如左键单击、双击等。对于 OpenCV 来讲&#xff0c;用户的鼠标操作被认为发生了一个鼠标事件&#xff0c;需要对这个鼠标事件进行处理&#xff0c;这就是事件的响应。下面我们…
阅读更多...
软件设计原则之接口隔离原则

软件设计原则之接口隔离原则

接口隔离原则&#xff08;Interface Segregation Principle, ISP&#xff09;是面向对象设计中的一个重要原则&#xff0c;它属于SOLID原则之一。这个原则强调客户端&#xff08;即接口的调用者&#xff09;不应该被迫依赖于它们不使用的方法。换句话说&#xff0c;一个类对另一…
阅读更多...
centos安装docker并配置加速器

centos安装docker并配置加速器

docker安装与卸载&#xff1a; 1、检查当前是否安装docker yum list installed | grep docker2、卸载docker 根据yum list installed | grep docker查询出来的内容&#xff0c;逐个进行删除 yum remove docker.x86 64 -y3、启动与关闭docker 4、删除/etc/docker文件夹 如果…
阅读更多...
理解HTTP请求方法：GET、POST、PUT 等

理解HTTP请求方法：GET、POST、PUT 等

在现代Web开发中&#xff0c;理解不同HTTP请求方法的用途及其特点是至关重要的。每种请求方法都承担着特定的角色&#xff0c;在客户端和服务器之间的通信中发挥着关键作用。包括GET、POST、PUT&#xff0c;以及一些不太常用的方法&#xff0c;如HEAD、DELETE、OPTIONS、TRACE和…
阅读更多...
MyBatis之XML配置文件（二）

MyBatis之XML配置文件（二）

六、动态SQL拼接 &#xff2d;yBatis提供了if 、foreach、choose等标签动态拼接sql语句&#xff0c;下面介绍这些标签的使用 1、if标签 if标签通常用于WHERE语句中&#xff0c;通过判断参数值决定是否使用某个查询条件。 <select id"selectStudentListLikeName"…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站