数据库性能调优

一应用程序优化

从数据库设计者的角度来看，应用程序无非是实现对数据的增加、修改、删除、查询和体现数据的结构和关系。设计者在性能方面的考虑因素，总的出发点是：

 （1）把数据库当作奢侈的资源看待，在确保功能的同时，尽可能少地动用数据库资源。

 （2）不要直接执行完整的SQL 语法，尽量通过存储过程来调用SQL Server。

 （3）客户与服务器连接时，建立连接池，让连接尽量得以重用，以避免时间与资源的损耗。

 （4）非到不得已，不要使用游标结构，确实使用时，注意各种游标的特性。

 

二基本表设计优化

 

  （1）表设计遵循第三范式。在基于表驱动的信息管理系统中，基本表的设计规范是第三范式。第三范式的基本特征是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点：一是能消除冗余数据、节省磁盘存储空间；二是有良好的数据完整性限制（基于主外键的参照完整限制和基于主键的实体完整性限制），这使得数据容易维护、移植和更新；三是数据的可逆性好，在做连接查询或者合并表时不遗漏、不重复；四是消除了冗余数据（这里主要指冗余列），使得查询时每个数据页存储的数据行增多，这样就有效地减少了逻辑I/O，同时也减少了物理I/O；五是对大多数事务而言，运行性能好；六是物理设计的机动性较大，能满足日益增长的用户需求。

  （2）分割表。分割表可分为水平分割表和垂直分割表两种：水平分割是按照行将一个表分割为多个表，这可以提高每个表的查询速度，但是由于造成了多表连接，所以应该在同时查询或更新不同分割表中的列的情况比较少的情况下使用。垂直分割是对于一个列很多的表，若某些列的访问频率远远高于其它列，在不破坏第三范式的前提下将主键和这些列作为一个表，将主键和其它列作为另外一个表。一种是当多个过程频繁访问表的不同列时，可将表垂直分成几个表，减少磁盘I/O。通过减少列的宽度，增加了每个数据页的行数，一次I/O就可以扫描更多的行，从而提高了访问每一个表的速度。垂直分割表可以达到最大化利用Cache的目的。分割表的缺点是要在插入或删除数据时要考虑数据的完整性，用存储过程维护。

 （3）存储衍生数据。对一些要做大量重复性计算的过程而言，若重复计算过程得到的结果相同，或计算牵扯多行数据需额外的磁盘I/O开销，或计算复杂需要大量的C P U时间，就考虑存储计算结果：若在一行或多行进行重复性计算，就在表内增加列存储结果，但若参与计算的列被更新时，必须要用触发器或存储过程更新这个新列。总之，存储冗余数据有利于加快访问速度，但违反了第三范式，这会增加维护数据完整性的代价，必须用触发器立即更新、或存储过程更新，以维护数据的完整性。

 （4）引入中间表。在实际单据未进入核心业务流程前，采用“中间表”的技术思路，就是在实际用户操作过程中，实际操作的是一个临时表，在进行数据某个阶段审核（进入下一个环节）后，将临时表的数据写入正式表，并且删除临时表的数据，这样始终保持用户操作表的固定的数据量而且控制增长，可以定期清除。 采用临时表技术首先需将要操作的数据集插入到临时表中，这会给系统带来额外的开销。这里假设临时表中的数据集远小于源数据表中的数据集，因此在进行数据连接操作或对数据集进行频繁读操作时，系统的性能会提高几倍甚至几十倍不等。 并非所有情况都适宜用临时表技术。一般来说，下面两种情况适宜采用临时表技术进行处理： （1）对数据量较大的表进行连接操作，并且连接操作的结果是一个小结果集。 （2）对数据量较大的表进行频繁访问，访问的范围比较固定且比较集中。 合理使用临时表技术，有助于提高应用系统对大数据表的实时处理的性能。

 

三 数据库索引优化

 

索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效率。利用索引优化系统性能是显而易见的，对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样可以减少数据存取时间；利用索引可以优化或排除耗时的分类操作，把数据分散到不同的页面上，这样就分散了插入的数据；主键自动建立了唯一索引，因此唯一索引也能确保数据的唯一性（即实体完整性）。总之，索引可以加快查询速度、减少I/O操作、消除磁盘排序。 优化索引可以避免扫描整个表，减少因查询造成的开销。一般说来建立索引要注意以下几点：

（1）检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中，这是“执行计划”能用到相关索引的必要条件。比较一下列中唯一键的数量和表中记录的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。因此，在查询中经常作为条件表达式且不同值较多的列上建立索引，不同值较少的列上不要建立索引。

 （2）索引的创建也是需要代价的，对于删除、某些更新、插入操作，对于每个索引都要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。 因此频繁进行删除、插入操作的表不要建立过多的索引。

（3）查询经常用到的列上建立非聚簇索引，在频繁进行范围查询、排序、分组的列上建立聚簇索引。

 （4）对于不存在重复值的列，创建唯一索引优于创建非唯一索引。

（5）当数据库表更新大数据后， 删除并重新建立索引来提高查询速度。

（6）当对一个表的update操作远远多于select操作时， 不应创建索引。

 （7）如果索引列是函数的参数，则索引在查询时用不上，该列也不适合索引。

 （8）Hash Join （HJ）由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。

（9）在主键上建立索引，尤其当经常用它作为连接的时候；在经常用于连接而又未指定为外键的列上建立索引。

（10）经常同时存取多列，且每列都含有重复值，可以考虑建立复合索引来覆盖一个或一组查询，并且把查询引用最频繁的列作为前导列。

（11）尽使用较窄的索引，这样数据页每页上能因存放较多的索引行而减少操作。

（12）并行查询将不会用到索引。

（13）索引中存储值不能为全空。

（14）查询中较少用到的列、数据量较大的列均不应建立索引。

 

下面的表总结了何时使用聚集索引或非聚集索引（很重要）。
动作描述            使用聚集索引    使用非聚集索引

列经常被分组排序    应                应
返回某范围内的数据  应                不应
一个或极少不同值    不应              不应
小数目的不同值     应                不应
大数目的不同值     不应              应
频繁更新的列       不应              应
外键列            应                应
主键列            应                应
频繁修改索引列     不应              应 

 

聚集索引

　　一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 
　　聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。 
　　　　 
　　　　 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。 
　　　　 

　　　　 当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束

非聚集索引

　　一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

 

 

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

四 SQL语句优化

 

在完成了系统设计、索引设计等工作以后， 就要考虑在使用过程中对语句的设计了。影响数据库应用程序性能的一个重要因素是SQL语句，按其影响严重程度，依次可分为：无谓的SQL，拙劣的SQL，复杂的SQL。 无谓的SQL：它们对数据库的访问，并不存在技术、技能上的问题，但却不是必要的，超出了实际业务需求。其结果是浪费了宝贵的主机资源、占用了网络流量，降低了系统性能。 拙劣的SQL：它们对数据库的访问并不是多余的，所体现的业务逻辑或结果是正确的，但是“写法”不够好，导致数据库处理起来不够优化。 复杂的SQL：数据库中多表（或视图）关联，条件复杂、冗长，计算复杂，使用冷僻的SQL技术等。 其中，无谓的SQL和拙劣的SQL属于开发技能方面的问题；复杂的SQL属于设计技能方面的问题，设计到数据库的结构。 在使用结构化查询语言来执行查询时，推荐以下举措：

（1）选择运算应尽可能先做，并在对同一个表进行多个选择运算时， 选择影响较大的语句放在前面；较弱的选择条件写在后面， 这样就可以先根据较严格的条件得出数据较小的信息， 再在这些信息中根据后面较弱的条件得到满足条件的信息。

 （2）应避免使用相关子查询。把子查询转换成联结来实现。对于主查询的每一条记录子查询都要执行一次， 嵌套的层次越多效率越低。避免对子句使用数学运算符。即不要对数据表的属性列进行操作。SQL概念上将位于WHERE子句中的相关子查询，处理成获取参数并且返回一个单独的值或值的集合的函数。因为子查询要对应位于外层查询的每一个元组进行单独的计算。从而导致大量的随机磁盘I/O操作。所以在实际应用中若可以用连接代替的子查询， 则用连接实现。例如，有以下相关子查询语句： SELECT ProductName FROM Products WHERE EXISTS (SELECT * FROM OrderDetails WHERE Discount >= 25 AND Products.ProductID= OrderDetails.ProjectID)； 用连接查询实现如下： SELECT ProductName FROM Products， OrderDetails WHERE Discount >= 25 AND Products.ProductID= OrderDetails.ProjectID

（3）字段提取按照“ 需多少， 提多少” 的原则， 避免“SELECT *”。“SELECT *”需要数据库返回相应表的所有列信息， 这对于一个列较多的表无疑是一项费时的操作。

（4）避免使用!=(或<>)、IS NULL或IS NOT NULL、IN、NOT IN 等这样的操作符，避免在WHERE子句中使用非聚合表达式。这些操作符会使系统无法使用索引， 而只能直接搜索表中的数据。例如， SELECT id， name FROM employee WHERE id!=B% 优化器将无法通过索引来确定将要命中的行数， 因此需要搜索该表的所有行。

（5）避免使用OR， 用UNION代替。OR语句的执行原理并不是利用列上的索引根据每个语句分别查找再将结果求并集， 而是先取出满足每个OR子句的行， 存入临时数据库的工作表中， 再建立唯一索引以去掉重复行， 最后从这个临时表中计算结果。这样使用可能造成索引失效， 导致顺序扫描整个表， 大大降低查询效率。

（6）在执行连接前对关系作适当的预处理， 预处理的方法有两种， 在连接属性上建立索引和对关系进行排序。

 （7）将一个大的查询拆成多步执行查询。

 （8）如果应用程序使用循环,可考虑在查询内放入循环。

 

五 事务处理调优

 

数据库的日常运行过程中可能面临多个用户同时对数据库的并发操作带来的数据不一致的问题，如：丢失更新、脏读和不可重复读等。并发控制的主要方法是封锁，锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致。 数据库应用程序将其工作分成若干个事务进行处理。当一个事务执行时，它访问数据库并执行一些本地计算。开发人员可以假设每一个事务都会被隔离地执行---没有任何并发动作。因为隔离的概念提供了透明性，这种对事务处理方式的保证有时被称为原子性保证。但是，如果把应用程序中的事务序列作为一个整体来看，则并没有上面所说的那种保证。在一个应用程序执行的两个事务之间，可能会执行另外一个应用程序的事务，而且第二个应用程序的执行可能修改了第一个应用程序中的两个事务（或其中的一个）需要访问的数据项。因此，事务的长度对保证正确性有着重要影响。 尽管将事务切分成较小粒度可以提高执行效率，但会因此破坏执行的正确性。这种性能和正确性之间的矛盾充斥并发控制的整个调优过程。考虑事务的性能我们要考虑到：事务使用的锁的个数（在所有其他条件相同的情况下，使用的锁个数越少，性能越好）；锁的类型（读锁对性能更有利）；事务持有锁的时间长短（持有时间越短，性能越好）。关于锁的调优有以下建议：

（1）使用特殊的系统程序来处理长的读操作。对于一个只读的事务R来说，它“看到”的数据库的状态一直是事务R开始时的状态。只读查询可以不需要封锁开销，在不造成阻塞和死锁的情况下，只读的查询可以与其他对同一数据进行更新的较小的事务并行地执行。

 （2）消除不必要的封锁。只有一个事务执行时，或所有事务都是只读事务时，用户应利用配置选项减少锁的个数，从而减小锁管理模块的内存开销和执行封锁操作的处理时间开销。

（3）根据事务的内容将事务切分成较小的事务。事务所要求的锁越多，它需要等待其他事务释放某个锁的可能就越大。事务T执行的时间越长，被T阻塞的事务等待的时间可能就越长。因此，在可能发生阻塞的情况下，利用较短的事务较好。

 （4）在应用程序允许的情况下，适当降低隔离级别。

 （5）选择适当的封锁粒度。页级封锁阻止并发事务访问或修改该页面上所有记录，表级封锁阻止并发事务访问或修改表内所有的页面；记录级封锁（行级锁）比页级封锁粒度好，页级封锁比表级封锁粒度好。长事务（指要访问表内几乎所有页面的事务）应该尽可能使用表级封锁来防止死锁，而短事务应该使用记录级封锁来提高并发度。

（6）只在数据库很少被访问时才修改有关数据定义的数据（系统目录或元数据）。每个能够编译、添加或删除表、添加或删除索引、改变属性定义的事务都必须访问目录数据，因此，目录很容易成为热点，也因而成为瓶颈。

（7）减少访问热点（大量事务访问和更新的数据）。只有在更新某热点的事务完成滞后，其他的事务才能获得这个热点上的锁，因此热点可能成为瓶颈。

（8）死锁检测周期的调优。 以上每个建议都可以独立于其他建议来运用，但是在调优时必须检测是否能体现合适的隔离性保证。

 

六 总结

 

数据库性能优化的基本原则就是通过尽可能少的磁盘访问获得所需要的数据。本文从计算机硬件、关系系统与应用程序、数据库索引、SQL 语句、事务处理几个比较共性的方面分析了数据库性能优化的问题, 提出了若干数据库性能优化的策略。当然实现优化的方法还有很多, 要根据具体情况而定。对于不同的应用情况,我们应该具体情况具体分析, 各方面优化措施综合运用, 以使数据库性能得到提高。数据库应用系统的性能是一项全民工程，开发团队的所有人都有责任为性能做贡献，树立性能意识，使之成为日常工作的习惯而不是单独成为某一阶段的工作，要未雨绸缪，不要寄希望于某一个环节的工作。