数据库的优化方案

数据库的优化

一、数据库优化的重要设计理念

二、数据库的各种优化

三、数据库优化的工具

一、数据库优化的重要设计理念

①：数据库的表越少越好
②：表的字段越少越好
③：字段中的组合主键、组合索引越少越好

这是传说中的‘三少原则’，这里的少是相对的，是减少数据冗余的重要设计理念。

二、数据库的各种优化

数据库三范式

 

实体中的某个属性不能有多个值或不能有重复的属性。 表的每一行包含一个实例的信息。每个表中必须实例一个主键。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。总之第二范式就是非主属性非部分依赖于主关键字。一个数据库表中不包含已在其他表中已包含的非主关键字信息。数据不能包含其他部门的数据，否则变得冗余。 1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
select * from emp where deptno is null;
注意：最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
例如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar2这样的变长字段， null 不占用空间。
3. .in 和 not in 也要慎用，否则会导致全表扫描 select empno,sal from emp where sal in(3000,5000,8000);

对于连续的数值，能用 between 就不要用 in 了
select empno,sal from emp where sal bettween 3000 and 8000;
很多时候用 exists 代替 in 是一个好的选择：

3.下面的查询也将导致全表扫描：%
select empno,ename from emp where ename like '%A%';
若要提高效率，可以考虑全文检索。
5..不要写一些没有意义的查询，如需要生成一个空表结构：
select empno,ename from emp where 1=0;
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)
? Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
? 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
? .在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
? 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
? 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

11.存储过程、视图、函数的适当使用
很多人习惯将复杂操作都放在应用程序层，但如果你要优化数据访问性能，将SQL代码移植到数据库上(使用存储过程，视图，函数和触发器)也是一个很大的改进原因如下：
1、 存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序
2、使用存储过程，视图，函数有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL
3、使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题，同时有助于你集中管理TSQL代码，更好的重构TSQL代码


12、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。


13、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。


14、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。
15、调整服务器内存分配
16、主键和外键的必要性
主键与外键的设计，在全局数据库的设计中，占有重要地位。 因为：主键是实体的抽象，主键与外键的配对，表示实体之间的连接。数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。
17、使用级联删除和级联更新
联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。
18、索引
索引是一个表优化的重要指标，在表优化中占有极其重要的成分，所以将单独写一章”SQL索引一步到位“去告诉我们如何建立和优化索引
19、调整硬盘I/O
20、调整操作系统参数等

三、数据库优化的工具
1、ORACLE数据库在线数据字典，ORACLE在线数据字典能够反映出ORACLE动态运行情况，对于调整数据库性能是很有帮助的。
2、操作系统工具，例如UNIX操作系统的vmstat，iostat等命令可以查看到系统系统级内存和硬盘I/O的使用情况，这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用。
3、SQL语言跟踪工具（SQL TRACE FACILITY），SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用TKPROF工具查看这些文件。
4、ORACLE Enterprise Manager（OEM），这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的ORACLE数据库管理的命令。 管理、诊断和调整多个远程系统。 将远程系统分组组织，便于管理和监视。
5、EXPLAIN PLAN—-—SQL语言优化命令，使用这个命令可以帮助程序员写出高效的SQL语言。实现一个SELECT查询（最好是比较复杂的一个，带joins方式的），在里面添加上你的关键词解释，在这里我们可以使用phpMyAdmin，他会告诉你表中的结果。