关系数据库的查询优化策略

1、关系数据库的查询优化策略1 引言随着计算机应用技术的不断普及和发展，数据库系统正越来越多的走进人们的日常生活。在要求查询结果正确无误的同时，人们越来越关心查询的效率问题。影响查询效率的因素很多，诸如处理器的速度、I/O速度、存储器的容量、操作系统、采取何种的数据库服务系统等。但是对于特定服务器来说查询的效率主要取决于DBA（数据库管理员）所给定的查询语句。 2 合理使用索引 数据库服务器对数据进行访问一般采用下面的两种方式：索引扫描，通过索引访问数据；表扫描，读表中的所有页。当对一个表进行查询时，如果返回的行数占全表总行数的10%到15%时，使用索引可以极大的优化查询的性能。但是如果查询涉及到

2、全表40%以上的行时，表扫描的效率比使用索引扫描的效率高。在具体使用的过程中，要结合实际的数据库和用户的需求来确定要不要索引以及在什么字段上建立什么样的索引。下面给出一些通用的规则： 1. 在经常用作过滤器或者查询频率较高字段上建立索引； 2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；3. 在不同值较少的字段上不必要建立索引，如性别字段； 4. 对于经常存取的列避免建立索引； 5. 用于联接的列（主健/外健）建立索引； 6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定。 2.1 聚集索引聚集索引是指行的物理顺序与行的索引

3、顺序相同的索引。一个表只能有一个聚集索引。非聚集索引是指定表的逻辑顺序的索引，行的物理顺序与索引顺序不尽相同，每个表可以有多个非聚集索引。缺省情况下建立的是非聚集索引，但是在一些特定的情况下建立非聚集索引会极大的缩短查询的时间。有大量重复值、且经常有范围查询（between,=,=）和orderby、groupby发生的列，可考虑建立聚集索引，而对于频繁修改的列、或者返回小数目的不同值的这些情况应该避免建立聚集索引。 使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。比如要返回2004年4月1日到2004年10月1日之间的数据，如果在日期的字段建立了聚集索引，那么数据

4、本来就是按照日期的顺序排列的，只要找到开始和结尾日期的数据就可以了，可以极大的节省时间。而如果使用非聚集索引，必须查到这个时间段中每个日期对应的位置，然后在根据位置存取数据，明显效率很低。显而易见，使用聚集索引的优势很明显。一个表只能按照一个固定的顺序来存储数据，因此，在建立聚集索引的时候一定要和实际查询相结合，看哪个字段对于查询贡献大，而且操作不是很频繁。 索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每添加一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说，我们要合理使用索引体系，特别是对索引的创建，更应精益求精，使数据库的性能得到更好的发挥

5、。 3 书写高效的SQL语句 虽然特定的数据库服务器都会对输入的查询语句进行一定的优化操作，但是查询效率主要取决于DBA所书写的SQL语句的好坏。为确保编写的SQL语句有较好的性能，应考虑以下的优化方法： 尽量减少使用负逻辑的操作符和函数，因为它们会导致全表扫描，而且容易出错。可以把含有NOT、 、！= 等负逻辑的条件表达式转化为意思相当的正逻辑。 字段提取要多少，取多少，避免使用“select *”格式，因为在数据量较大的时候，影响查询性能的最大因素不在与数据的查找，而在于物理I/O的操作。 避免使用LIKE、EXISTS、IN等标准表达式，他们会使字段上的索引无效，引起全表扫描。尽量减少表

6、的联接操作，不可避免的时候要适当增加一些冗余条件，使参与联接的字段集尽量少。 OR会使字段上的索引失效，引起全表扫描。下面的例子中，可以把or子句分开，在把结果做加法和算，也可以编写一个存储过程来避免索引的失效。 Select work-name, work-dept from work where work-id=2 or work-id=3; 尽量减少使用联接字段而把所有的条件分列出来用and来进行连接，可以充分的利用在某些字段上已经存在的索引。 select work-id from salary where work-salary|”| work-dept=$2000 teacher

7、; 如果把条件分开来写成下面的格式，系统的查询性能可以得到一定的提高。 select work-no from salary where work-salary=$2000 and work-dept= teacher ; 尽量避免使用相关的嵌套查询， 3.1 Where字句的影响 Where子句说明查询的条件，直接决定查询的性能。因此在where子句的书写及应用中要多加注意。书写where 子句时尽量避免使用不兼容的数据类型，避免对where 子句中的条件参数使用其他的数学操作符，尽可能的把操作转化到式子的左边，这样可以有效的利用已有的索引技术。对于where字句中的多个选择条件，要选取结果

8、集小的先执行。下面给出一些不规范书写。 select work-id from salary where work-salary4000; select work-id from salary where work-salary*2$4000； 对于第一个查询来说，4000是整数，而工人的工资时money格式的，系统在查询的时候需要耗费时间来进行格式转化。对于第二个例子，任何在运算符左边的操作都会使SQL采用全表扫描，对表中的每个数据项做相应的操作来比较是否满足条件，如果这个字段有索引，则索引失效。因此上面两个例子最好可以写成下面的格式： select work-no from salary

9、where work-salary$4000; select work-no from salary where work-salary$2000; 4 存储过程的使用 存储过程由SQL语句和SPL语言的语句组成,创建后转换为可执行代码,作为数据库的一个对象存储在数据库中,存储过程的代码驻留在服务器端,因而执行时不需要将应用程序代码向服务器端传送,可以大大减轻网络负载，加快系统响应时间。同时,由于存储过程已编译为可执行代码,不需要每次执行时进行分析和优化工作,从而减少了预处理所花费的时间,提高了系统的效率。 在工程中，我们可以把经常用到的查询动作编写成一个存储过程，并利用参数实现动态查询过程来

10、响应客户的要求；可以实现在服务器端进行批量数据处理等操作；可以使用存储过程作为强制安全性工具;还可以利用系统为用户定义的管理级别存储过程实现数据的管理、配置和监控等。合理使用存储过程可以有效的提高系统效率。 5 视图的应用 利用视图不仅可以提高数据的保密性，方便的设置用户的权限，而且也可以提高数据的精炼性。在DBMS中有着许多不同的角色，他们对数据的要求是不同的，针对不同类别的用户分别建立合适的视图，可以在有效的条件下提高数据的有用性，提高系统对不同用户的查询响应时间。此外用户访问数据库一般要求得到的是最近的数据，比如查询话费，最常用的数据是最近三个月的。因此在许多情况下，可以按照时间对数据库

11、中的数据进行水平分片，把最近一段时间的数据呈现给用户。当用户需要查找“过期”数据时再把相应的块调进来。由于这种情况极少发生，在一定的情况下，可以有效的减少数据量，缩小数据查找范围。使用这种方法要注意分区数据的维护，因此一定要在权衡维护和查询代价的基础上确定是否要使用分片。如果经常要访问全库数据进行综合对比的话，这种方法就不适用。 6 小结 关系数据库的优化是一个和实际数据库结构密切相关的问题，在实际应用中应该结合具体的数据库服务器，深入的理解服务器的运作模式、资源配置，优化服务器的运行环境，选择合适的操作系统，最大限度的发挥服务器的性能。实际使用SQL语句中要注意的地方数据库设计是应用程序设计

12、的基础，其性能直接影响应用程序的性能。数据库性能包括存储空间需求量的大小和查询响应时间的长短两个方面。为了优化数据库性能，需要对数据库中的表进行规范化。规范化的范式可分为第一范式、第二范式、第三范式、BCNF范式、第四范式和第五范式。一般来说，逻辑数据库设计会满足规范化的前3级标准,但由于满足第三范式的表结构容易维护且基本满足实际应用的要求。因此，实际应用中一般都按照第三范式的标准进行规范化。但是，规范化也有缺点：由于将一个表拆分成为多个表，在查询时需要多表连接，降低了查询速度。 由于规范化有可能导致查询速度慢的缺点，考虑到一些应用需要较快的响应速度，在设计表时应同时考虑对某些表进行反规范化。

13、反规范化可以采用以下几种方法： 1. 分割表 分割表包括水平分割和垂直分割。 水平分割是按照行将一个表分割为多个表，这可以提高每个表的查询速度，但查询、更新时要选择不同的表，统计时要汇总多个表，因此应用程序会更复杂。 垂直分割是对于一个列很多的表，若某些列的访问频率远远高于其它列，就可以将主键和这些列作为一个表，将主键和其它列作为另外一个表。通过减少列的宽度，增加了每个数据页的行数，一次I/O就可以扫描更多的行，从而提高了访问每一个表的速度。但是由于造成了多表连接，所以应该在同时查询或更新不同分割表中的列的情况比较少的情况下使用。 2. 保留冗余列 当两个或多个表在查询中经常需要连接时，可以在

14、其中一个表上增加若干冗余的列，以避免表之间的连接过于频繁。由于对冗余列的更新操作必须对多个表同步进行，所以一般在冗余列的数据不经常变动的情况下使用。 3. 增加派生列 派生列是由表中的其它多个列计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间。 应用程序性能的优化 应用程序的优化通常可分为两个方面：源代码和SQL语句。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高，而对数据库系统性能的提升收效有限，因此应用程序的优化应着重在SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能

15、就行，而是要写出高质量的SQL语句，提高系统的可用性。 下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。 1. IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not

16、 null的语句优化器是不允许使用索引的。 2. 联接列 对于有联接的列，即使最后的联接值为一个静态值，优化器不会使用索引的。例如，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫乔治布什（George Bush）的职工。 下面是一个采用联接查询的SQL语句： select * from employee where first_name|last_name =George Bush;上面这条语句完全可以查询出是否有George Bush这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没

17、有使用。 当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引： Select * From employee where first_name =George and last_name =Bush;遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着George Bush这个员工的姓名，对于这种情况我们又如何避免全程遍历使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本： select * from employee where first_name =

18、SUBSTR(&name,1,INSTR(&name, )-1) and last_name = SUBSTR(&name,INSTR(&name, )+1) ;3. 带通配符（%）的like语句 同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含Bush的人。可以采用如下的查询SQL语句： select * from employee where last_name like %Bush%;这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而

19、当通配符出现在字符串其他位置时，优化器就能利用索引。例如，在下面的查询中索引得到了使用： select * from employee where last_name like c%;4. Order by语句 Order by语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个

20、索引，同时应绝对避免在order by子句中使用表达式。 5. NOT 我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子： . where not (status =VALID)如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例： . where status INVAL

21、ID;再看下面这个例子： select * from employee wheresalary3000;对这个查询，可以改写为不使用NOT的语句： select * from employee wheresalary3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。 6. IN和EXISTS 有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。 第一种格式是使用IN操作符： . where column in(sel

22、ect * from . where .);第二种格式是使用EXIST操作符： . where exists (select X from .where .);绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以将几乎所有的IN操作符子查询改写为使用EXISTS的子查询。 第二种格式中，子查询以select X开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造

23、起来要比IN子查询困难一些。 通过使用EXISTS，Oracle系统会首先检查主查询，然后运行子查询直到找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。 同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），但NOT EXISTS要比NOT IN查询效率更高。 其他相关资料：ORACLE SQL性能优化系列 (一)

24、作者：Black_Snail 1. 选用适合的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 stat

25、istics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2. 访问Table的方式 ORACLE 采用两种访问表中记录的方式: a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读

26、入多个数据块(database block)的方式优化全表扫描. b. 通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3. 共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer po

27、ol)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它 和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找

28、相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等). 共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP;bruce_lee2005-02-23, 14:334. 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子

29、句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表

30、 (不佳的方法) select count(*) from tab2,tab1 执行时间26.09秒 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. 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 将比下列SQL更有效率 SELECT

31、* 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 2000bruce_lee2005-02-23, 15:575，WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.。bruce_lee2005-02-23, 21:587. 减少访问数据库的次数 当执行每条SQL语句时, O

32、RACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.bruce_lee2005-02-23, 21:590bruce_lee2005-02-23, 22:036，避免在select 语句中使用*这是一个非常低效的方法. 实际上，ORACLE在解析的过程中，会将 * 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。bruce_lee2005-02-23, 22:118. 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫

33、描相同记录或重复连接相同的表.bruce_lee2005-02-23, 22:1410， 删除重复记录 最高效的删除重复记录方法 ( 因为使用了ROWID) DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)；bruce_lee2005-02-23, 22:1511. 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复

34、到删除之前的状态(准确地说是 恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短bruce_lee2005-02-23, 22:16只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 (译者按: 在使用COMMIT时必须要注意到事务的

35、完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)bruce_lee2005-02-23, 22:1713. 计算记录条数 和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO) (译者按: 在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)bruce_lee2005-02-23, 22:1814. 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.

36、 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中bruce_lee2005-02-23, 22:2015. 减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询。低效: 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 = 0020bruce_lee2