数据库优化

数据库优化

数据库优化的目的：避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。

数据库的优化包括物理上的优化，如良好的平台（硬件平台、网络平台），合理的环境参数（操作系统环境参数、oracle环境参数），及软件上的优化。

一、数据库结构的设计

1、数据库的逻辑设计、包括表与表之间的关系是优化关系型数据库性能的核心。一个好的逻辑数据库设计可以为优化数据库和应用程序打下良好的基础。

标准化的数据库逻辑设计包括用多的、有相互关系的窄表来代替很多列的长数据表。下面是一些使用标准化表的一些好处。

A:由于表窄，因此可以使排序和建立索引更为迅速

B:由于多表，所以多镞的索引成为可能

C:更窄更紧凑的索引

D:每个表中可以有少一些的索引，因此可以提高insert update delete等的速度，因为这些操作在索引多的情况下会对系统性能产生很大的影响

E:更少的空值和更少的多余值，增加了数据库的紧凑性

由于标准化，所以会增加了在获取数据时引用表的数目和其间的连接关系的复杂性。太多的表和复杂的连接关系会降低服务器的性能，因此在这两者之间需要综合考虑。定义具有相关关系的主键和外来键时应该注意的事项主要是：用于连接多表的主键和参考的键要有相同的数据类型。

2、键的设计

基本表设计中，表的主键、外键、索引设计占有非常重要的地位，它们与系统的运行性能密切相关。

（1）主键(Primary Key)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值（不能为Null或缺省值，要有相对稳定性），不含代码信息，易访问。把常用（众所周知）的列作为主键才有意义。短主键最佳（小于25bytes），主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成，自然主键可以是复合性的，在形成复合主键时，主键列不能太多，复合主键使得Join操作复杂化、也增加了外键表的大小。人为主键是，在没有合适的自然属性键、或自然属性复杂或灵敏度高时，人为形成的。人为主键一般是整型值（满足最小化要求），没有实际意义，也略微增加了表的大小；但减少了把它

作为外键的表的大小。

（2）外键（Foreign Key）：外键的作用是建立关系型数据库中表之间的关系（参照完整性），主键只能从独立的实体迁移到非独立的实体，成为后者的一个属性，被称为外键。

（3）索引(Index)：利用索引优化系统性能是显而易见的，对所有常用于查询中的Where

子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间；利用索引可以优化或排除耗时的分类操作；把数据分散到不同的页面上，就分散了插入的数据；主键自动建立了唯一索引，因此唯一索引也能确保数据的唯一性（即实体完整性）；索引码越小，定位就越直接；新建的索引效能最好，因此定期更新索引非常必要。索引也有代价：有空间开销，建立它也要花费时间，在进行Insert、Delete和Update操作时，也有维护代价。

二、索引的设计

尽量避免表扫描

检查你的查询语句的where子句，因为这是优化器重要关注的地方。包含在where里面的每一列（column)都是可能的侯选索引，为能达到最优的性能，考虑在下面给出的例子：对于在where子句中给出了column1这个列。

下面的两个条件可以提高索引的优化查询性能！

第一：在表中的column1列上有一个单索引

第二：在表中有多索引，但是column1是第一个索引的列

避免定义多索引而column1是第二个或后面的索引，这样的索引不能优化服务器性能

例如：下面的例子用了pubs数据库。

SELECT au_id, au_lname, au_fname FROM authors

WHERE au_lname = 'White'

按下面几个列上建立的索引将会是对优化器有用的索引

au_lname

au_lname, au_fname

而在下面几个列上建立的索引将不会对优化器起到好的作用

au_address

au_fname, au_lname

考虑使用窄的索引在一个或两个列上，窄索引比多索引和复合索引更能有效。用窄的索引，在每一页上将会有更多的行和更少的索引级别（相对与多索引和复合索引而言），这将推进系统性能。

对于多列索引，SQL Server维持一个在所有列的索引上的密度统计（用于联合）和在第一个索引上的histogram（柱状图）统计。根据统计结果，如果在复合索引上的第一个索引很少被选择使用，那么优化器对很多查询请求将不会使用索引。

有用的索引会提高select语句的性能，包括insert,uodate,delete。

但是，由于改变一个表的内容，将会影响索引。每一个insert,update,delete语句将会使性能

下降一些。实验表明，不要在一个单表上用大量的索引，不要在共享的列上（指在多表中用了参考约束）使用重叠的索引。

在某一列上检查唯一的数据的个数，比较它与表中数据的行数做一个比较。这就是数据的选择性，这比较结果将会帮助你决定是否将某一列作为侯选的索引列，如果需要，建哪一种索引。

三、查询语句的优化

1、使用解释计划。

2、使用索引。

3、慎重使用NOT IN 、IN子句，因为IN会使系统无法使用索引，而只能直接搜索表中的数据，能用BETWEEN 就不用IN。很多时候使用EXISTS，NOT EXISTS会更好些。

4、尽量避免WHERE条件中使用!= ，<>操作符。

5、尽量避免WHERE条件中使用OR条件，否则将导致引擎放弃索引而进行全表扫描。

6、尽量避免WHERE条件中对字段进行NULL判断，否则将导致引擎放弃索引而进行全表扫描。

7、避免在索引过的数据中，使用非打头字母搜索。如

SELECT * FROM A WHERE NAME LIKE '%L%' ；不使用索引

SELECT * FROM A WHERE SUBSTR(NAME,2,1) = 'L' ；不使用索引

SELECT * FROM A WHERE NAME LIKE 'L%'；使用索引

8、必要时可以强制查询优化器使用索引。

9、避免在WHERE条件中对字段进行表达式操作，如WHERE A/2=100改为A=2*100。

10、避免在WHERE条件中对字段进行函数操作，如SUBSTR等。

11、使用复合索引时，必须是该索引的第一个字段作为条件，否则不会使用索引。

12、能用DISTINCT的就不用GROUP BY 。

13、能用UNION ALL 就不用UNION。

14、尽量使用绑定变量。

Execute immediate 'insert into t values(:x)' using i;

而不是Execute immediate 'insert into t values(' | | i | | ')'

15、可以使用反连接来代替NOT IN。

16、在嵌套查询中使用ROWNUM。

四、使用存储过程

1 存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。

2 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。

3 存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。

4 存储过程主要是在服务器上运行，减少对客户机的压力。

5 存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。

6 存储过程可以在单个存储过程中执行一系列SQL 语句。

7 存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。