如何写出性能优良的 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

相关文章

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误