数据库优化2

 设计阶段

 

设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。

 

在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。

 

以下是性能要求设计阶段需要注意的：

 

1、数据库逻辑设计的规范化

 

数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

 

第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。（无重复的列）

 

第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。（属性完全依赖于主键）

 

第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。（属性不依赖于其它非主属性）

 

2、合理的冗余

 

完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

 

冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

 

冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

 

3、主键的设计

 

主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的。

 

在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

 

主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

 

4、外键的设计

 

外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：

 

外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

 

         5、字段的设计

 

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

 

A、数据类型尽量用数字型，数字型的比较比字符型的快很多。尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

 

B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

 

C、 尽量不要允许NULL，除非必要，可以用NOTNULL+DEFAULT代替。(设置NOT NULL约束的字段，如果给定一个DEFAULT约束，即使INSERT没有给字段赋值也不会出错。)

 

D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，

大部分情况下最好不用。

 

E、自增字段要慎用，不利于数据迁移。

 

　　　8、索引的设计

 

在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

 

关于索引的选择，应改主意：

 

A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

 

B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

 

C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

 

D、一个表不要加太多索引，因为索引影响插入和更新的速度

 

 

 

 

 

编码阶段

 

在语句中多写注释，注释不影响SQL语句的执行效率。增加代码的可读性。

 

尽量使事务处理达到最短，如果事务太长最好按功能将事务分开执行(如：可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞，用户操作速度变慢或死机情况。

 

尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作，最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理。

 

 

只返回需要的数据

 

返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：

 

     1.不要写SELECT *的语句，而是选择你需要的字段。数据分页处理

 

2.合理写WHERE子句，不要写没有WHERE的SQL语句。用Where子句替换HAVING子句

 

3.没有必要时不要用DISTINCT和ORDER BY, 这些动作可以改在客户端执行，它们增加了额外的开销。用EXISTS替换DISTINCT

(低效):

select distinctdeptno,dname from dept d , emp e where d.deptno=e.deptno

(高效):

select distinctdeptno,dname from dept d , where exists ( select ‘x’ from emp ewhered.deptno=e.deptno);

 

 4.用IN来替换OR

低效:

Select * fromemp where job=’CLERK’ or job = ‘MANAGER’ or job=’SALESMAN’;

高效

Select * fromemp where job in (’CLERK’,‘MANAGER’,’SALESMAN’);

 

5.Between能够更快地根据索引找到范围。

select * from empwhere deptno in (10,20)

Select * from empwhere between 10 and 20 是一样的。由于in会在比较多次，所以有时会慢些。

 

6.注意union和 union all 的区别 , UNIONall执行效率高。

 

7.如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度;如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

注意SELECT INTO后的WHERE子句，因为SELECTINTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

 

 

8.用>=替代>

高效:

Select * fromemp where deptno >=4;

低效:

Select * fromemp where deptno >3;

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

 

9.对于聚合查询，可以用HAVING子句进一步限定返回的行。

selectdeptno,count(1) from emp group by deptno having count(1)>1;

select count(*)from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

 

优化GROUP BY：

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUPBY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

低效:

Selectjob,avg(sal) from emp group by job having job = ‘PRESIDENT’ or job = ‘MANAGER’;

高效:

Selectjob,avg(sal) from emp where job = ‘PRESIDENT’ or job = ‘MANAGER’ group by job;

 

 

尽量少做重复的工作

 

A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

 

B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

 

C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

 

D、合并对同一表同一条件的多次UPDATE，比如

update emp setsal=6000 where ename = 'SMITH'

　　update emp set comm=6000 where ename = 'SMITH'

 

这两个语句应该合并成以下一个语句

update emp setsal=6000,comm=6000 where ename = 'SMITH'

 

E、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

 

F、不要写一些没有意义的查询，比如

select * fromemp where 1 = 1;

 

 

SQL书写的影响

 

同一功能同一性能不同写法SQL的影响。

 

如一个SQL在

A程序员写的为 select * fromzl_yhjbqk

B程序员写的为 select * fromdlyx.zl_yhjbqk（带表所有者的前缀）

C程序员写的为 select * fromDLYX.ZLYHJBQK（大写表名）

D程序员写的为 select *  from DLYX.ZLYHJBQK（中间多了空格）

 

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。

 

WHERE后面的条件顺序影响

 

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：

Select * from emp where sal > 2000 andjob=’MANAGER’;

Select * from emp where job=’MANAGER’ andsal > 2000;

以上两个SQL中sal 及job两个字段都没进行索引，所以执行的时候都是全表扫描，第二条SQL的CPU占用率比第一条低。

 

 

注意连接条件的写法

 

多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。

 

A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。

 

B、连接条件尽量使用聚集索引

 

C、 注意ON部分条件和WHERE部分条件的区别

 

其他需要注意的地方

 

　　经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：

　　A、程序员注意、关心各表的数据量。

　　B、编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。

　　C、每个SQL语句尽量简单

　　D、不要频繁更新有触发器的表的数据

　　E、注意数据库函数的限制以及其性能