SQL语句查询性能优化.docVIP

SQl语句查询性能优化 【摘要】本文从DBMS的查询优化器对SQL查询语句进行性能优化的角度出发，结合数据库理论，从查询表达式及其多种查询条件组合对数据库查询性能优化进行分析，总结出多种提高数据库查询性能优化策略，介绍索引的合理建立和使用以及高质量SQL查询语句的书写原则，从而实现高效的查询，提高系统的可用性。 【关键词】SQL查询语句，索引，性能优化 1.引言 在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，索引的运用与复杂视图的编写等体会不出SQL语句各种写法的性能优劣，但是应用系统实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。 (1) 当插入的数据为数据表中的记录数量10%以上时, 首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。 (2) 避免在索引列上使用函数或计算，在WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例: 低效： select * from table where salary * 12 25000; 高效： select * from table where salary 25000/12; 低效： select * from table1 where name=zhangsan and tID 10000 高效： select * from table1 where tID 10000 and name=zhangsan 如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name=zhangsan的，而后再根据限制条件条件tID10000来提出查询结果。 (3) 避免在索引列上使用NOT和”!=”或 ， 索引只能告诉什么存在于表中， 而不能告诉什么不存在于表中，当数据库遇到NOT和”!=”时，就会停止使用索引转而执行全表扫描。 (4) 索引列上用=替代 高效： select * from table where Deptno =4 低效： select * from table where Deptno 3 两者的区别在于， 前者table将直接跳到第一个Deptno等于4的记录而后者将首先定位到Deptno=3的记录并且向前扫描到第一个Deptno大于3的记录。 (5) 函数的列启用索引方法，如果一定要对使用函数的列启用索引，Oracle9i以上版本新的功能：基于函数的索引(Function-Based Index)是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。 create index EMP_I ON EMP (upper( ename)); /*建立基于函数的索引*/ select * from EMP where upper( ename) = ‘BLACKSNAIL’; /*将使用索引*/ 3. SQL语句性能优化 3.1 WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其它where条件之前，那些可以过滤掉最大数量记录的条件必须写在where子句的末尾。 低效：Select * from table where Salary 50000 and Job = ‘MANAGER’ and 25 (Select count(*) from table where Mgr=table.Empno); 高效：Select * from table where 25 (select count(*) from table where Mgr=table.Empno) and Salary 50000 and Job = ‘MANAGER’; 3.2 用EXISTS替代IN 在许多基于基础表的查询中，为了满足一个条件往往需要对另一个表进行联接，例如在ETL过程写数据到模型时经常需要关联10个左右的维表，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程, 在这种情况下，使用EXISTS而不用I