数据库 设计与优化

三、设计技巧

１．分类拆分数据量大的表。对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。
２．索引设计。在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段建立的顺序进行。 
３．数据操作的优化。
４．数据库参数的调整。数据库参数的调整是一个经验不断积累的过程，应由有经验的系统管理员完成。
５．必要的工具。
６．避免长事务。

　　

7. 通俗地理解三个范式
　　通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就

必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：
　　第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
　　第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
　　第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
　　没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运

行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式

，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是

最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间

 

 

8. 提高数据库运行效率的办法
　　在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
　　 (1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。
　　 (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面。
　　 (3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，

以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。
　　 (4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
　　 (5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
　　总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。

 

SQL 语句优化

 

5. WHERE子句中的连接顺序．

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

 

 

6. SELECT子句中避免使用 ‘ * ‘
　　当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

 

11. 用TRUNCATE替代DELETE
　　当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是
　　恢复到执行删除命令之前的状况)
　　而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

 

 

14. 用Where子句替换HAVING子句
　　 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

 

 

17. 使用表的别名(Alias)
　　当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

 

 

18. 用EXISTS替代IN
　　在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

 

20. 用表连接替换EXISTS

21. 用EXISTS替换DISTINCT

 

SQL语句优化技术分析

操作符优化 

IN 操作符 
 　　　推荐方案：在业务密集的SQL当中尽量不采用IN操作符。 

NOT IN操作符 
 　　　此操作是强列推荐不使用的，因为它不能应用表的索引。 
 　　　推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替 

<> 操作符（不等于） 
 　　　不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 
       推荐方案：用其它相同功能的操作运算代替，如 

IS NULL 或IS NOT NULL操作（判断字段是否为空） 
 　　　推荐方案： 用其它相同功能的操作运算代替，如 
 　　　a is not null 改为 a>0 或a>’’等。 

> 及 < 操作符（大于或小于操作符） 
 　　　大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。 

LIKE操作符 

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。 



UNION操作符 

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。 

SQL书写的影响 

同一功能同一性能不同写法SQL的影响 
如一个SQL在A程序员写的为 
 　　　Select * from zl_yhjbqk 
B程序员写的为 
 　　　Select * from dlyx.zl_yhjbqk（带表所有者的前缀） 
C程序员写的为 
 　　　Select * from DLYX.ZLYHJBQK（大写表名） 
D程序员写的为 
 　　　Select *　from DLYX.ZLYHJBQK（中间多了空格） 

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



WHERE后面的条件顺序影响 
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如 
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1 
Select * from zl_yhjbqk where xh_bz=1　and dy_dj = '1KV以下' 
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。 



查询表顺序的影响 

在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）

SQL语句索引的利用 

对操作符的优化（见上节） 

对条件字段的一些优化 

采用函数处理的字段不能利用索引，如： 

substr(hbs_bh,1,4)=’ 5400’ ，优化处理：hbs_bh like ‘5400%’ 

trunc(sk_rq)=trunc(sysdate)， 优化处理： 

sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1) 

进行了显式或隐式的运算的字段不能进行索引，如： 

ss_df+20>50，优化处理：ss_df>30 

‘X’||hbs_bh>’X 5400021452’ ，优化处理：hbs_bh>’ 5400021542’  

sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5 

hbs_bh=5401002554，优化处理：hbs_bh=’  5401002554’ ，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。 

条件内包括了多个本表的字段运算时不能进行索引，如： 

ys_df>cx_df，无法进行优化 

qc_bh||kh_bh=’ 5400250000’ ，优化处理：qc_bh=’ 5400’  and kh_bh=’ 250000’