SQL性能调优 密级:普通/秘密/绝密 所属项目： <请在此填写本文档所属项目> 日期：2004/5/27 作者： xuji 关键字： 人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中中表现得尤为明显。不良的SQL往往来自于不恰当的数据库设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！当然，某些情况下提升了SQL语句的性能，同时也使SQL语句的可读性变得很差。关键我们要清楚，在程序中哪些地方存在性能瓶颈，哪些地方的性能必须提升，以及哪些因素对性能造成影响。本文档中的SQL语名主要针对Oracel数据库。 本文档中的实例没有经过严格的测试，大家有兴趣可以写一些相关的测试程序，对文中的结论进行验证。如有错误请即时更正，并通知开发组中人员。关于SQL性能问题，如大家有新的见解，欢迎大家进行补充。 1 索引对查询性能的影响 1.1 簇索引与非簇索引对性能的影响 SQL语句 簇索引(Date) 非簇索引(Date) select count(*) from record where date>'19991201'and date<'19991214' and amount> 2000 14秒 25秒 Select date,sum(amount) from record group by date 28秒 55秒 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com select count(*) from record where date>'19990901' and place in ('BJ','SH') 14秒 27秒 分析：date上有大量的重复值，在非簇索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。在簇索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。 1.2 组合索引对查询性能的影响 SQL语句 组合索引(place、date、amount) 组合索引(date、place amount) Select count(*) from record where date>'19991201' and date<'19991214' and amount>2000 26秒 <1秒 Select date,sum(amount) from record group by date 27秒 11秒 Select count(*) from record where date > '19990901' and place in ('BJ,'SH') <1秒 <1秒 分析：第一种组合索引不合理，因为它的第一个列是place，第一和第二条SQL没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的；第二种组合索引是一个合理的组合索引。它将date作为第一列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 1.3 小结 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说： l 有大量重复值、且经常有范围查询（between，>，<，>=，<=）和order by、group by发生的列，可考虑建立簇索引 l 经常同时存取多列，且每列都含有重复值可考虑建立组合索引； l 组合索引要尽量使关键查询形成索引覆盖，其最频繁的列放在前面。 2 关于连接 2.1 选择最有效率的表名顺序 TAB1 100000条记录 TAB2 1条记录 EMP表描述了LOCATION表和CATEGORY表的交集 SQL1 SQL2 普通连接 select count(*) from tab1,tab2(快) select count(*) from tab2,tab1（慢） 交叉表连接 SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN（快） SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000（慢） 分析：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 注：基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, SQL语句中基础表的选择是不一样的. 如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径. 如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的连接条件都有索引对应, 在这种情况下, 基础表就是FROM 子句中列在最后的那个表。 3 Where子句 3.1 Where子句的顺序 SQL语句 处理时间 SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) 快 SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER' 慢 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 3.2 在Where子句中对字段逐行计算的影响 SQL语句1 SQL语句2 Select * from record where substring(card_no,1,4)='5378'(13秒) Select * from record where card_no like '5378%'（<1秒） Select * from record where amount/30< 1000（11秒） Select * from record where amount <1000*30（<1秒） Select * from record where convert(char(10),date,112)='19991201'（10秒） Select * from record where date='1999/12/01'（<1秒） 分析：在语句1中where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索。 3.3 Where子句中的or与in SQL语句 运行时间 Select count(*) from stuff where id_no in('0','1') 23秒或小于23秒 Select count(*) from stuff Where id_no=’0’ or id_no=’1’ 23秒 分析：where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in('0','1')转化为id_no='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上，它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。但关于此问题，具体情况还要视数据库（SQL编译器）而定。并不是所有的SQL编译器都向上述那样将in转换为or。有些数据库先将in中的字段值保存在临时表中，然后将表与临时表联接，这样就可以利用该字段的索引，从而提高查询性能。对于采用 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com “Or策略”的数据库而言，一种有效的办法是创建一个存储过程中，在存储过程中分别查询结果再相加，但这样方法对于in中的值是动态时不是很方便。 3.4 In与Exists SQL语句 处理时间 SELECT col1,col2,col3 FROM table1 a WHERE a.col1 not in (SELECT col1 FROM table2) 20秒 SELECT col1,col2,col3 FROM table1 a WHERE not exists (SELECT 'x' FROM table2 b WHERE a.col1=b.col1) <1秒 分析：我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。 第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。 通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。 同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。 在做优化sql时，经常碰到使用in的语句，这时我们一定要用exists把它给换掉，因为Oracle在处理In时是按Or的方式做的，即使使用了索引也会很慢。使用Exists的相关子查询，可以提高查询速度。 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 3.5 IS NULL 与 NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 3.6 带％的Like语句 SQL语句 处理时间 select * from employee where last_name like '%cliton%' 慢 select * from employee where last_name like 'c%' 快 分析：由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在第二个SQL语句中，%只是出现的字符串的后面，所以优化器就可以利用索引。 3.7 Order By 子句 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 3.8 Not、<> SQL语句 处理时间 select * from employee where salary<>3000 慢 select * from employee where salary<3000 or salary>3000 快 分析：虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。 3.9 NOT IN 与MINUS SQL语句 处理时间 select staff_name from staff_member where staff_id not in (select staff_id from staff_func where func_id like ‘81%’) 慢 select staff_name from staff_member where staff_id in (select staff_id from staff_member MINUS select staff_id from staff_func where func_id like ‘81%’) 快 分析：由于NOT IN必须进行全表搜索，所以尽量不要使用NOT IN子句。使用MINUS 子句都比NOT IN 子句快，虽然使用MINUS子句要进行两次查询。 3.10 Where子句替换Having子句 Having子句与group by一起使用，对返回的记录集进行过滤。避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 。 SQL语句 处理时间 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH' 慢 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH' GROUP BY REGION 快 3.11 用>=替代> SQL语句 处理时间 SELECT * FROM EMP WHERE DEPTNO >3 慢 SELECT * FROM EMP WHERE DEPTNO >=4 快 4 其他 4.1 在Oracle中查询性能最差的SQL语句 SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE, DISK_READS,sql_text FROM v$sqlarea ORDER BY disk_reads DESC ) WHERE ROWNUM<10 ; 4.2 字符级的比较 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 l SELECT * from EMP; l Select * From Emp; 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com l SELECT * FROM EMP; 4.3 绑定变量的SQL语句 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如： 第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值) SQL1 SQL2 第一组 select pin , name from people where pin = :blk1.pin; select pin , name from people where pin = :blk1.pin; 第二组 select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind; 4.4 避免使用“select *” 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。 4.5 减少访问数据库次数 以下有三种方法可以检索出雇员号等于0342或0291的职员 SQL语句 处理时间 SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 低效 DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO; 次效 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com BEGIN OPEN C1(342); FETCH C1 INTO …,..,.. ; ….. OPEN C1(291); FETCH C1 INTO …,..,.. ; CLOSE C1; END; SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291; 高效 分析：当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。 4.6 DECODE函数 SQL语句 处理时间 SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%'; SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%'; 慢 SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%'; 快 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 4.7 最高效的删除重复记录的方法 DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 4.8 最快的清除记录方法 truncate table tablename 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是 恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.Truncate是DDL，不是DML。另外，当表中有字段被其他表引用为外键时，无法使用该方法。 4.9 减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询 低效SQL 高效SQL Select语句 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER=(SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) Update语句 UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020 UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com 4.10 使用表的别名 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。(注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属) 上海格尔软件股份有限公司 上海市余姚路288号A楼4层 Tel: (86-021) 62327010 Fax: (86-021) 62327015 URL: http://www.koal.com