如何写出性能优良的 SQL? (如何优化 SQL)

2024-08-31 05:08

本文主要是介绍如何写出性能优良的 SQL? (如何优化 SQL),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

1. 选择最有效率的表名顺序 ( 只在基于规则的优化器中有效 )
       ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名, FROM 子句中写在最后的表(基础表 driving table)将被最先处理,在 FROM 子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有 个以上的表连接查询那就需要选择交叉表(intersection table)作为基础表交叉表是指那个被其他表所引用的表.

2. WHERE 子句中的连接顺序
      ORACLE 采用自下而上的顺序解析 WHERE 子句 , 根据这个原理 , 表之间的连接必须写在其他WHERE 条件之前那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。

3. SELECT 子句中避免使用 *

      ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成这意味着将耗费更多的时间

4. 减少访问数据库的次数
      ORACLE 在内部执行了许多工作 : 解析 SQL 语句,估算索引的利用率,绑定变量,读数据块等;

5. 使用 DECODE 函数来减少处理时间

      使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。

6. 整合简单,无关联的数据库访问

      如果你有几个简单的数据库查询语句 , 你可以把它们整合到一个查询中 ( 即使它们之间没有关系)

7.
删除重复记录
    最高效的删除重复记录方法
( 因为使用了 ROWID) 例子:
    DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X

    WHERE X.EMP_NO = E.EMP_NO);

8. TRUNCATE 替代 DELETE
     当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息如果你没有 COMMIT 事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况而当运用 TRUNCATE 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。 (TRUNCATE 只在删除全表适用,TRUNCATE 是 DDL 不是 DML)

9.
存储过程中尽量多使用 COMMIT
      只要有可能 , 在程序中尽量多使用 COMMIT ,这样程序的性能得到提高,内存需求也会因为 COMMIT 所释放的资源而减少。

10. where 子句替换 HAVING 子句
      避免使用
HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。如果能通过 WHERE 子句限制记录的数目,那就能减少这方面的开销。

11. 减少对表的查询
    在含有子查询的
SQL 语句中 , 要特别注意减少对表的查询 . 例子:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) =

(SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION=604)

12. 通过内部函数提高 SQL 效率
   复杂的
SQL 往往牺牲了执行效率。能够掌握 Oracle 内置函数解决问题的方法在实际工作中是非常有意义的。

13. 使用表的别名 (Alias)
     当在 SQL 语句中连接多个表时,请使用表的别名并把别名前缀于每个 Column 上。这样一来,就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

14. EXISTS 替代 IN 、用 NOT EXISTS 替代 NOT IN
    在许多基于基础表的查询中 , 为了满足一个条件 , 往往需要对另一个表进行联接。在这种情况下,使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。 在子查询中, NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下, NOT IN 都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用 NOT IN ,我们可以把它改写成外连接(Outer Joins)或 NOT EXISTS
例子:
(高效)
SELECT * FROM EMP ( 基础表 ) WHERE EMPNO > 0 AND EXISTS (SELECT
'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
(
低效 )SELECT * FROM EMP ( 基础表 ) WHERE EMPNO > 0 AND DEPTNO IN(SELECT

DEPTNO FROM DEPT WHERE LOC = 'MELB')

15. 识别 ' 低效执行 ' SQL 语句
     虽然目前各种关于
SQL 优化的图形化工具层出不穷 , 但是写出自己的 SQL 工具来解决问题始终是一个最好的方法:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
16.
用索引提高效率

     索引是表的一个概念部分,用来提高检索数据的效率, ORACLE 使用了一个复杂的自平衡B-tree 结构。 通常,通过索引查询数据比全表扫描要快。当 ORACLE 找出执行查询和Update 语句的最佳路径时, ORACLE 优化器将使用索引。 同样在联结多个表时使用索引也可以提高效率。 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。那些 LONG 或 LONG RAW 数据类型,你可以索引几乎所有的列。通常,在大型表中使用索引特别有效。 当然,你也会发现,在扫描小表时,使用索引同样能提高效率。

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。 索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。 这意味着每条记录的 INSERT 、 DELETE 、 UPDATE 将为此多付出 4、 次的磁盘 I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的:

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>


17.
EXISTS 替换 DISTINCT
      当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在 SELECT 子句中使用 DISTINCT. 一般可以考虑用 EXIST 替换, EXISTS 使查询更为迅速,因为 RDBMS 心模块将在子查询的条件一旦满足后,立刻返回结果例子:

(低效):SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE
D.DEPT_NO = E.DEPT_NO
(
高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X'

FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);


18. sql 语句用大写的

       因为 oracle 总是先解析 sql 语句,把小写的字母转换成大写的再执行。

19. 避免在索引列上使用 NOT
      我们要避免在索引列上使用 NOTNOT 会产生在和在索引列上使用函数相同的影响。
ORACLE 遇到 NOT,他就会停止使用索引转而执行全表扫描。

这篇关于如何写出性能优良的 SQL? (如何优化 SQL)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1122967

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能测试介绍

性能测试是一种测试方法,旨在评估系统、应用程序或组件在现实场景中的性能表现和可靠性。它通常用于衡量系统在不同负载条件下的响应时间、吞吐量、资源利用率、稳定性和可扩展性等关键指标。 为什么要进行性能测试 通过性能测试,可以确定系统是否能够满足预期的性能要求,找出性能瓶颈和潜在的问题,并进行优化和调整。 发现性能瓶颈:性能测试可以帮助发现系统的性能瓶颈,即系统在高负载或高并发情况下可能出现的问题

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份