数据库的优化

数据库优化的目标无非就是避免磁盘I/O瓶颈、减少磁盘利用率和减少资源竞争。本文章从基本的表设计、扩展设计以及应用系统中SQL语句的优化来讨论如何避免磁盘I/O瓶颈和减少资源竞争。

1、在表的设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重满足用户需求，而没有从系统优化的高度来认识和重视他们。实际上，他们与系统的性能关系密切。现在从系统数据库优化的角度来讨论这些基本概念及其重要意义。

2、主键(Primary key)：主键被用于复杂的SQL语句时，频繁的在数据访问中被用到。一个表只能存在一个主键，而且主键必须有固定的值(不能有null或者缺省值)，把常用的列作为主键才有意义，短主键(小于25bytes)最佳，主键的长短影响索引的大小，索引的大小影响了索引页的大小，从而影响了磁盘的I/O。

3、外键(Foreign key)外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是： 外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

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

(1)、数据类型要尽量使用数字型，因为数字类型比字符型的比较要快的多。

(2)、数据类型要尽量小，这里的尽量小是在满足可以预见的未来需求的前提下的。

(3)、尽量不要允许NULL，除非必要，可以使用not null + default来代替。

(4)、少用Text和image，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

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

5、数据库的物理存储和环境设计：在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

6、索引：在设计阶段，可以根据功能和性能的需求进行初步的索引设计，需要根据预计的数据量和查询来设计索引。关于索引的选择应该注意：

(1)、根据数据量来决定哪个表需要添加索引，如果数据量小，可以只有主键。

(2)、根据使用频率来决定哪些字段需要添加索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段，可以避免整表扫描和访问。

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

(4)、一个表中不要存在太多索引，因为索引影响插入和更新速度。

7、查询优化规则：在访问数据库表的数据时，要尽量避免使用排序(Sort)、连接(Join)和相关子查询操作。经验告诉我们，在优化查询时，必须坐到：

(1)、尽可能少的行；

(2)、避免排序和尽可能的减少的行排序，若要进行大量数据的排序，最好将相关数据放在临时表中操作，用最简单的键排序，如整型或字符串排序；

(3)、避免表内的相关子查询。

(4)、避免在where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接；

(5)、在where子句中，多用"与"(and)连接，少用"或"or连接;

(6)、利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时，可以建临时表(索引)以减少I/O。但缺点是增加了空间的开销。

8、编码阶段：编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。编码阶段首先是需要程序员有性能意识，也就是在实现功能的同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量运用这个工具，所谓集合运算，实际上是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或用存储过程代替。

(1)、只返回需要的数据，返回数据到客户端至少需要从数据库中提取数据、网络数据传输、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效功劳，其害处是显而易见的，避免这种事情的发生：

A、横向来看，不要写Select * 语句，而是选择你需要的字段。

B、纵向来看，合理写Where子句，不要写没有where的SQL语句。

C、对于聚合函数，要使用having进行进一步限制。

(2)、 尽量少做重复的工作，这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：
A、 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。
B、 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。
C、 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
D、 合并对同一表同一条件的多次UPDATE，比如
UPDATE EMPLOYEE SET FNAME='HAIWER' WHERE EMP_ID=' VPA30890F'
UPDATE EMPLOYEE SET LNAME='YANG' WHERE EMP_ID=' VPA30890F'
这两个语句应该合并成以下一个语句
UPDATE EMPLOYEE SET FNAME='HAIWER',LNAME='YANG'
WHERE EMP_ID=' VPA30890F'
E、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。
F、 不要写一些没有意义的查询，比如 SELECT * FROM EMPLOYEE WHERE 1=2