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

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七