SQL优化37项

大家好，感谢邀请，今天来为大家分享一下SQL优化37项的问题，以及和的一些困惑，大家要是还不太明白的话，也没有关系，因为接下来将为大家分享，希望可以帮助到大家，解决大家的问题，下面就开始吧！

SELECT * FROM EMP（基表）WHERE EMPNO 0

AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND LOC=‘MELB’) B. 效率低

SELECT * FROM EMP（基表）WHERE EMPNO 0

AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC=’MELB’) 16. 识别“执行效率低下”的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 EXECUTIONS0 AND BUFFER_GETS 0 AND ( BUFFER_GETS- DISK_READS)/BUFFER_GETS 0.8 ORDER BY 4 DESC;

17、利用索引提高效率。索引是表的概念部分，用于提高检索数据的效率。 ORACLE使用复杂的自平衡B树结构。一般来说，通过索引查询数据比全表扫描要快。当ORACLE 找出执行查询和更新语句的最佳路径时，ORACLE 优化器会使用索引。同样，在连接多个表时使用索引也可以提高效率。使用索引的另一个好处是它提供主键的唯一性验证。对于那些LONG 或LONG RAW 数据类型，您几乎可以对所有List 建立索引。通常，在大型表中使用索引特别有效。当然，你也会发现在扫描小表时使用索引也能提高效率。虽然使用索引可以提高查询效率，但我们也必须注意它的成本。索引需要空间来存储并且需要定期维护。每当从表中添加或删除记录或修改索引列时，索引本身也会被修改。因此，每条记录的INSERT、DELETE和UPDATE将多花费4或5次磁盘I/O。由于索引需要额外的存储空间和处理，因此不必要的索引会减慢查询响应时间。定期重建索引是必要的。索引重建：ALTER INDEX INDEXNAME REBUILD TABLESPACENAME18。将DISTINCT 替换为EXISTS。当提交包含一对多表信息（例如部门表和员工表）的查询时，请避免在SELECT 子句中使用DISTINCT。一般情况下，可以考虑用EXIST 代替，EXISTS 使得查询速度更快，因为一旦满足子查询的条件，RDBMS 核心模块就会立即返回结果。示例：A. 效率

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

B、效率低下

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO=E.DEPT_NO 19. sql 语句使用大写字母；因为oracle总是先解析sql语句，将小写字母转换成大写字母然后执行20.在java中，代码中尽量少用连接符“+”来连接字符串！ 21. 避免在索引列上使用NOT。避免在索引列上使用NOT。 NOT 与在索引列上使用函数具有相同的影响。当ORACLE“遇到”NOT时，它会停止使用索引，转而执行全表扫描。

22. 如果索引列是函数的一部分，请避免在WHERE 子句中对索引列使用计算。优化器不会使用索引而是使用全表扫描A. 效率低下SELECT … FROM DEPT WHERE SAL * 12 25000; B. 高效选择……来自SAL 25000/12 的部门；

23、用=代替A.高效的SELECT * FROM EMP WHERE DEPTNO=10B。效率低下SELECT * FROM EMP WHERE DEPTNO 9。两者的区别在于，前者DBMS会直接跳转到DEPT等于10的第一条记录，后者会先定位DEPTNO=9的记录，向前扫描到第一条记录其中DEPT大于9。 24、用UNION代替OR（适用于索引列）一般情况下，用UNION代替WHERE子句中的OR会有更好的效果。对索引列使用OR 将导致全表扫描。请注意，上述规则仅适用于多个索引列。如果有未建立索引的列，可能会因为不选择OR而降低查询效率。在以下示例中，索引是基于LOC_ID 和REGION 构建的。 A. 高效：SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID=20 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION=”MELBOURNE” B. 低效SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID=20 OR REG离子=“” MELBOURNE》如果坚持使用OR，那么需要先写入返回记录最少的索引列。 25、用IN代替OR。这是一个简单易记的规则，但实际执行效果需要经测试，在ORACLE8i下面，两者的执行路径似乎是一样的。 A. 效率低下的SELECT.FROM LOCATION。

WHERE LOC_ID=20 OR LOC_ID=30 OR LOC_ID=40 B. 高效选择.FROM LOCATION WHERE LOC_IN IN (20,30,40)26。避免在索引列上使用IS NULL 和IS NOT NULL。避免在索引中使用任何可能的列。如果列为空，ORACLE 将无法使用该索引。对于单列索引，如果某列包含空值，则该记录将不存在于索引中。对于复合索引，如果每列都为空，则该记录也不会存在于索引中。如果至少一列不为空，则该记录存在。在索引中。示例：如果在表的A列和B列上建立唯一索引，并且表中存在A、B值为(12, null)的记录，ORACLE将不接受具有相同A的下一条记录和B值（12，null）记录（插入）。但是，如果所有索引列都为null，ORACLE就会认为整个键值都为null，并且null不等于null。因此，你可以插入1000条具有相同键值的记录，当然它们都是null。由于索引列中不存在空值，因此WHERE子句中索引列的空值比较会导致ORACLE停用索引。 A.效率低下：（索引失败）SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; B、高效：（索引有效）SELECT … FROM DEPARTMENT WHERE DEPT_CODE=0; 27. 始终使用索引的第一列。如果索引建立在多列上，只有它的第一列（前导列）只有在被where子句引用时优化器才会选择使用该索引。这也是一个简单但重要的规则。当仅引用索引的第二列时，优化器使用全表扫描并忽略索引。

28、将UNION替换为UNION-ALL（前提查询没有去重或者数据本身没有重复）。当SQL语句需要两个UNION查询结果集时，会以UNION-ALL的方式将两个结果集进行合并，然后进行排序，然后输出最终结果。如果使用UNION ALL 代替UNION，则不需要排序。效率将会提高。需要注意的是，UNION ALL会重复输出两个结果集中相同的记录。所以你还是有必要根据业务需求来分析使用UNION ALL的可行性。 UNION会对结果集进行排序，该操作将使用SORT_AREA_SIZE内存。这个内存的优化也非常重要。可以使用如下SQL查询排序Amount A的消耗情况，效率较低： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE=’31-DEC-95′ UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE=’31-DEC-95′ B. 高效： SELECT ACCT_NUM, BALANCE_AMT FROM DEB IT_TRANSACTIONS WHERE TRAN_DATE=’31-DEC-95′ UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE=’31-DEC-95′ 29. 使用WHERE 而不是ORDER BY。 ORDER BY 子句仅在两个严格条件下使用索引。 ORDER BY 中的所有列必须包含在同一个索引中，并保持索引中的排序顺序。 ORDER BY 中的所有列必须定义为非空。 WHERE 子句中使用的索引和ORDER BY 子句中使用的索引不能同时使用。例如：表DEPT 包含以下列： DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL 低效：（不使用索引） SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 高效：（使用索引） SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE 030。更改索引列的类型当比较不同数据类型的数据时，ORACLE 会自动对列进行简单的类型转换。假设EMPNO是数字类型索引列。 SELECT … FROM EMP WHERE EMPNO=’1234′ 事实上，ORACLE 类型转换后，该语句被转换为： SELECT … FROM EMP WHERE EMPNO=TO_NUMBER(‘1234′) 幸运的是，索引列上没有发生类型转换，并且索引的目的没有改变。现在，假设EMP_TYPE是字符类型的索引列。 SELECT … FROM EMP WHERE EMP_TYPE=1234 该语句被ORACLE 转换为： SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=1234 由于内部发生类型转换，因此不会使用该索引！为了避免ORACLE隐藏你的SQL，最好显式地表达类型转换。注意，在比较字符和数值时，ORACLE会优先将数值类型转换为字符类型。 31、需要注意的WHERE子句：某些SELECT语句中的WHERE子句不使用索引在下面的例子中，(1)’!=’不会使用索引。请记住，索引只能告诉你表中存在什么，而不能告诉你表中不存在什么。 (2)“||”是一个字符连接函数。与其他功能一样，索引被禁用。 (3)“+”是一个数学函数。与其他数学函数一样，索引被禁用。 (4)相同索引列不能互斥Compare，这样会启用全表扫描。 32.正确使用索引

一个。如果检索的数据量超过表中记录数的30%，使用索引不会有明显的效率提升。

b.在某些情况下，使用索引可能比全表扫描慢，但这是同一个数量级的差异。一般来说，使用索引比全表扫描快几倍甚至几千倍。 34.避免使用消耗资源的操作带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句将启动SQL引擎来执行消耗资源的排序（SORT）功能。 DISTINCT 需要一次排序操作，而其他则需要至少两次排序操作。通常，带有UNION、MINUS 和INTERSECT 的SQL 语句可以用其他方式重写。如果你的数据库的SORT_AREA_SIZE配置得好，使用UNION、MINUS、INTERSECT也可以考虑，毕竟它们的可读性很强。 35、优化GROUP BY，提高GROUP BY语句的效率。您可以在GROUP BY 之前过滤掉不需要的记录。以下两个查询返回相同的结果，但第二个查询显然要快得多。 A. 效率低下SELECT JOB, AVG(SAL) FROM EMP GROUP 购买JOB HAVING JOB=’PRESIDENT’ OR JOB=’MANAGER’ B. 高效SELECT JOB , AVG(SAL) FROM EMP WHERE JOB=’PRESIDENT’ OR JOB=’MANAGER ‘ 团购工作

36. Like 带有通配符的语句(%)

A、高效

select * from emp where name like ‘A%’

B、效率低下

select * from emp where name like ‘%A%’

37.正确使用提示

Hint是Oracle提供的一种SQL语法，它允许用户在SQL语句中插入相关语法，从而影响SQL的执行方式。由于Hint 的特殊作用，开发人员不应该在代码中使用它。 Hint更像是Oracle提供给DBA的一个分析问题的工具。在SQL代码中使用Hint可能会导致非常严重的后果，因为数据库数据发生了变化。在某一时刻使用这个执行计划是最优的，但在另一时刻，它可能很差。这就是CBO取代RBO的原因。原因之一是规则是死的，数据一直在变化。为了得到最正确的执行计划，只有了解表中数据的实际情况，计算各种执行计划的成本才是最优的。最科学的是，这也是CBO的工作机制。在SQL代码中添加Hints是非常危险的，尤其是与性能相关的Hints。使用Hint 时需要注意的一件事是，Hint 并非始终有效。 HINT失败的原因有两个：

A. 如果CBO认为使用Hint会导致错误的结果，Hint将被忽略。如果索引中的记录由于空值而与表中的记录不一致，则结果将是错误的，并且提示将被忽略。

B、如果表中指定了别名，则Hint中也必须使用该别名，否则Hint也会忽略它。