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

《关系数据库的查询优化策略》由会员分享，可在线阅读，更多相关《关系数据库的查询优化策略（15页珍藏版）》请在金锄头文库上搜索。

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

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

3、要按照使用的 频度来确定。 2.1 聚集索引聚集索引聚集索引是指行的物理顺序与行的索引顺序相同的索引。一个表只能有一个聚集 索引。非聚集索引是指定表的逻辑顺序的索引，行的物理顺序与索引顺序不尽相同， 每个表可以有多个非聚集索引。缺省情况下建立的是非聚集索引，但是在一些特定的 情况下建立非聚集索引会极大的缩短查询的时间。有大量重复值、且经常有范围查询 （between,=, 、！= 等负逻辑的条件表达式转化为意思相当的正逻辑。 字段提取要多少，取多少，避免使用“select *”格式，因为在数据量较大的时候， 影响查询性能的最大因素不在与数据的查找，而在于物理 I/O 的操作。 避免使用 LIK

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

5、 salary where work-salary|”| work-dept=$2000 teacher ; 如果把条件分开来写成下面的格式，系统的查询性能可以得到一定的提高。 select work-no from salary where work-salary=$2000 and work-dept= teacher ; 尽量避免使用相关的嵌套查询， 3.1 Where 字句的影响字句的影响 Where 子句说明查询的条件，直接决定查询的性能。因此在 where 子句的书写及应用 中要多加注意。书写 where 子句时尽量避免使用不兼容的数据类型，避免对 where 子句中 的条件参数使

6、用其他的数学操作符，尽可能的把操作转化到式子的左边，这样可以有效的 利用已有的索引技术。对于 where 字句中的多个选择条件，要选取结果集小的先执行。下 面给出一些不规范书写。 select work-id from salary where work-salary4000; select work-id from salary where work-salary*2$4000； 对于第一个查询来说，4000 是整数，而工人的工资时 money 格式的，系统在查询的时 候需要耗费时间来进行格式转化。对于第二个例子，任何在运算符左边的操作都会使 SQL 采用全表扫描，对表中的每个数据项做相应的

7、操作来比较是否满足条件，如果这个字段有 索引，则索引失效。因此上面两个例子最好可以写成下面的格式： select work-no from salary where work-salary$4000; select work-no from salary where work-salary$2000; 4 存储过程的使用存储过程的使用 存储过程由 SQL 语句和 SPL 语言的语句组成,创建后转换为可执行代码,作为数据库的 一个对象存储在数据库中,存储过程的代码驻留在服务器端,因而执行时不需要将应用程序 代码向服务器端传送,可以大大减轻网络负载，加快系统响应时间。同时,由于存储过程已 编译为可

8、执行代码,不需要每次执行时进行分析和优化工作,从而减少了预处理所花费的时 间,提高了系统的效率。 在工程中，我们可以把经常用到的查询动作编写成一个存储过程，并利用参数实现动 态查询过程来响应客户的要求；可以实现在服务器端进行批量数据处理等操作；可以使用 存储过程作为强制安全性工具;还可以利用系统为用户定义的管理级别存储过程实现数据的 管理、配置和监控等。合理使用存储过程可以有效的提高系统效率。 5 视图的应用视图的应用 利用视图不仅可以提高数据的保密性，方便的设置用户的权限，而且也可以提高数据 的精炼性。在 DBMS 中有着许多不同的角色，他们对数据的要求是不同的，针对不同类别的 用户分别建立

9、合适的视图，可以在有效的条件下提高数据的有用性，提高系统对不同用户 的查询响应时间。此外用户访问数据库一般要求得到的是最近的数据，比如查询话费，最 常用的数据是最近三个月的。因此在许多情况下，可以按照时间对数据库中的数据进行水 平分片，把最近一段时间的数据呈现给用户。当用户需要查找“过期”数据时再把相应的 块调进来。由于这种情况极少发生，在一定的情况下，可以有效的减少数据量，缩小数据 查找范围。使用这种方法要注意分区数据的维护，因此一定要在权衡维护和查询代价的基 础上确定是否要使用分片。如果经常要访问全库数据进行综合对比的话，这种方法就不适 用。 6 小结小结 关系数据库的优化是一个和实际数据

10、库结构密切相关的问题，在实际应用中应该结合 具体的数据库服务器，深入的理解服务器的运作模式、资源配置，优化服务器的运行环境， 选择合适的操作系统，最大限度的发挥服务器的性能。实际使用实际使用 SQL 语句中要注意的地方语句中要注意的地方数据库设计是应用程序设计的基础，其性能直接影响应用程序的性能。数据库性能包 括存储空间需求量的大小和查询响应时间的长短两个方面。为了优化数据库性能，需要对 数据库中的表进行规范化。规范化的范式可分为第一范式、第二范式、第三范式、BCNF范式、第四范式和第五范式。一般来说，逻辑数据库设计会满足规范化的前 3 级标准,但由 于满足第三范式的表结构容易维护且基本满足实

11、际应用的要求。因此，实际应用中一般都 按照第三范式的标准进行规范化。但是，规范化也有缺点：由于将一个表拆分成为多个表， 在查询时需要多表连接，降低了查询速度。 由于规范化有可能导致查询速度慢的缺点，考虑到一些应用需要较快的响应速度，在 设计表时应同时考虑对某些表进行反规范化。反规范化可以采用以下几种方法： 1. 分割表 分割表包括水平分割和垂直分割。 水平分割是按照行将一个表分割为多个表，这可以提高每个表的查询速度，但查询、 更新时要选择不同的表，统计时要汇总多个表，因此应用程序会更复杂。 垂直分割是对于一个列很多的表，若某些列的访问频率远远高于其它列，就可以将主 键和这些列作为一个表，将主键

12、和其它列作为另外一个表。通过减少列的宽度，增加了每 个数据页的行数，一次 I/O 就可以扫描更多的行，从而提高了访问每一个表的速度。但是 由于造成了多表连接，所以应该在同时查询或更新不同分割表中的列的情况比较少的情况 下使用。 2. 保留冗余列 当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的列， 以避免表之间的连接过于频繁。由于对冗余列的更新操作必须对多个表同步进行，所以一 般在冗余列的数据不经常变动的情况下使用。 3. 增加派生列 派生列是由表中的其它多个列计算所得，增加派生列可以减少统计运算，在数据汇总 时可以大大缩短运算时间。 应用程序性能的优化应用程序性能的优化

13、 应用程序的优化通常可分为两个方面：源代码和 SQL 语句。由于涉及到对程序逻辑 的改变，源代码的优化在时间成本和风险上代价很高，而对数据库系统性能的提升收效有 限，因此应用程序的优化应着重在 SQL 语句的优化。对于海量数据，劣质 SQL 语句和优 质 SQL 语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能 就行，而是要写出高质量的 SQL 语句，提高系统的可用性。 下面就某些 SQL 语句的 where 子句编写中需要注意的问题作详细介绍。在这些 where 子句中，即使某些列存在索引，但是由于编写了劣质的 SQL，系统在运行该 SQL 语句时也 不能使用该索引，

14、而同样使用全表扫描，这就造成了响应速度的极大降低。 1. IS NULL 与 IS NOT NULL 不能用 null 作索引，任何包含 null 值的列都将不会被包含在索引中。即使索引有多列 的情况下，只要这些列中有一列含有 null，该列就会从索引中排除。也就是说如果某列存 在空值，即使对该列建索引也不会提高性能。 任何在 where 子句中使用 is null 或 is not null 的语句优化器是不允许使用索引的。 2. 联接列 对于有联接的列，即使最后的联接值为一个静态值，优化器不会使用索引的。例如， 假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIR

15、ST_NAME 和 LAST_NAME），现在要查询一个叫乔治布什（George Bush）的职工。 下面是一个采 用联接查询的 SQL 语句： select * from employee where first_name|last_name =George Bush;上面这条语句完全可以查询出是否有 George Bush 这个员工，但是这里需要注意，系 统优化器对基于 last_name 创建的索引没有使用。 当采用下面这种 SQL 语句的编写，Oracle 系统就可以采用基于 last_name 创建的索引：Select * From employee where first_name

16、 =George and last_name =Bush;遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着 George Bush 这个 员工的姓名，对于这种情况我们又如何避免全程遍历使用索引呢？可以使用一个函数，将 变量 name 中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引 列上。下面是 SQL 查询脚本： select * from employee where first_name = SUBSTR(3. 带通配符（%）的 like 语句 同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中 包含 Bush 的人。可以采用如下的查询 SQL 语句： select * from employee where last_name like %Bush%;这里