1.9k 字 21 分钟

引言

在开发初期,数据量较小,对于sql查询语句优化并不会那么急迫.但随着日积月累的数据,数据库承担的压力越来越大,数据搜索时间也越来越长.
对查询性能要求也变的迫在眉睫,对于数据库优化,可以有集群,分表分库,读写分离,索引等,但体现在最低端被无数次重复使用的还是程序员们写的sql,一个不成熟的程序员写sql可能极大的浪费数据库的资源,最直观的就是浪费电啊!!!所以为了环保,为了世界,编写出高效的sql是志在必得的.

  1. 尽量不要在 where 子句里面使用 is null 和 is not null,会导致数据库弃用索引.
    低效: (索引失效)

    SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
    高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;

  2. 不要连接列,如: name||’’||id=’ss 222’;

  3. 截取函数不要使用在列上 如 substr(name,2);

  4. 通配符%,不要在词首出现(%SS%),在其他位置时可以利用索引(ss%)

  5. 任何在 Order by 语句的非索引项或者有计算表达式都将降低查询速度,应避免在order by 子句中使用表达式

  6. 尽量不要使用not和 <>关键字,可以由下列改写,可以使用索引提高查询效率,
    select * from employee where salary<>3000;
    对这个查询，可以改写为不使用 NOT：

select * from employee where salary<3000 or salary>3000;

  1. 优先使用not exists 效率比在not in高,原因是not in 无法使用索引,主表大用in,子查询大用exists

  2. ORACLE优化器(没懂):
    ORACLE 的优化器共有 3 种:
    a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)
    设置缺省的优化器,可以通过对 init.ora 文件中 OPTIMIZER_MODE 参数的各种声明,如
    RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在 SQL 句级或是会话(session)级对
    其进行覆盖.
    为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行 analyze 命令,以增
    加数据库中的对象统计信息(object statistics)的准确性.
    如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过
    analyze 命令有关. 如果 table 已经被 analyze 过, 优化器模式将自动成为 CBO , 反之,数据库将采用
    RULE 形式的优化器.
    在缺省情况下,ORACLE 采用 CHOOSE 优化器, 为了避免那些不必要的全表扫描(full table
    scan) , 你必须尽量避免使用 CHOOSE 优化器,而直接采用基于规则或者基于成本的优化器.

  1. 访问数据表,基于全表扫描,通过rowid访问表,rowid 访问表速度更快

  2. SQl共享(存疑),一般没有特殊的访问限制,两个相同的sql可以共享,要求字符串空格都一致(严格)

  3. oracle 表数据比较少的放在FROM子句最后,因为ORACLE处理FROM子句的时候,是从右往左进行查询的,此外多表查询(三张以上),

  4. WHERE子句,表连接条件需要写在其他条件之前,由于ORACLE采取自下而上的顺序解析,过滤数据多的条件应该放在最后面.

  5. 禁止在列中使用,这将是因为 需要替换成表中各列,这个动作需要ORACLE去查询数据字典,所以是低效的

  6. 尽量在一条sql处理尽可能多的业务,当然也要分析具体场景

  7. 尽量使用decode()减少数据库重复的查询

  8. 最高效的删除重复记录方法 ( 因为使用了 ROWID)

    DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

  9. 尽量多使用 COMMIT,可以释放一些资源
    COMMIT 所释放的资源:
    a. 回滚段上用于恢复数据的信息.
    b. 被程序语句获得的锁
    c. redo log buffer 中的空间
    d. ORACLE 为管理上述 3 种资源中的内部花费

  10. 和一般的观点相反, count(*) 比 count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

  11. 尽量避免使用having子句,改用where子句替代,sql 运行顺序,FROM –> WHERE –>GROUP BY –>HAVING –>SELECT –>ORDER BY

  12. 在利用子查询的时候,尽量减少对表的查询次数如:

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

UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

  1. 使用表的别名

  2. 用 EXISTS 替换 DISTINCT,当只需要外部表来过滤信息的时候.

    SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);

  3. 定期的重构索引是有必要的.

    ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

  4. 避免在索引列计算
    低效：

SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;

高效:

SELECT …
FROM DEPT
WHERE SAL > 25000/12;

  1. 用>=替代>
    如果 DEPTNO 上有一个索引,
    高效:
    SELECT *
FROM EMP
WHERE DEPTNO >=4

低效:

SELECT *
FROM EMP
WHERE DEPTNO >3
``` 

主要区别在于第一个直接定位到4,而第二个直接定位到3,再去找比3大的数


26. 索引分为单例索引和复合索引,只有复合索引的第一个列被where子句引用时,优化器才会选择该索引

27. 使用提示
如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留在
SGA 中,你就可以使用 CACHE hint 来告诉优化器把数据保留在 SGA 中. 通常 CACHE hint 和
FULL hint 一起使用.
例如:
```SQL
SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ * FROM WORK;

  1. 用when替代order by
    order by 子句只在两种严格的条件下使用索引
    order by 中所有的列必须包含在相同的索引中并保持在索引的中排列顺序
    order by 中所有的列必须定义为非空
    where 子句使用的索引和order by子句中所使用的索引不能并列

  2. 索引只能告诉你什么在表里,不能告诉你什么不再表里,所以一般!=,<>等不要使用,这将导致索引无法生效.不要在索引列进行运算(重复)

  3. 避免使用耗费资源的操作
    带有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的 SQL 语句会启动 SQL 引擎
    执行耗费资源的排序(SORT)功能. DISTINCT 需要一次排序操作, 而其他的至少需要执行两次
    排序.
    例如,一个 UNION 查询,其中每个查询都带有 GROUP BY 子句, GROUP BY 会触发嵌入排序
    (NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行 UNION 时, 又一个唯一排序
    (SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深
    度会大大影响查询的效率.
    通常, 带有 UNION, MINUS , INTERSECT 的 SQL 语句都可以用其他方式重写

  4. 分离表和索引
    总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于 ORACLE 内
    部系统的对象存放到 SYSTEM 表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上

 目录


买个卤蛋，吃根冰棒