原标题:浅谈数据库设计中的反规范 (ZT)

1. 摘要
　本文从提高数据库性能的角度，介绍了数据库设计中几种常用的反规范方法，并对这些方法的优缺点以及使用时的注意事项做了较为深入的论述。　
关键词：　数据库设计　　反规范　　提高性能
2. 数据库设计简述
数据库设计是把现实世界的商业模型与需求转换成数据库的模型的过程，它是建立数据库应用系统的核心问题。设计的关键是如何使设计的数据库能合理地存储用户的数据，方便用户进行数据处理。　
　数据库设计完全是人的问题，而不是数据库管理系统的问题。系统不管设计是好是坏，照样运行。数据库设计应当由数据库管理员和系统分析员一起和用户一道工作，了解各个用户的要求，共同为整个数据库做出恰当的、完整的设计。　　
数据库及其应用的性能和调优都是建立在良好的数据库设计的基础上，数据库的数据是一切操作的基础，如果数据库设计不好，则其它一切调优方法提高数据库性能的效果都是有限的。　　
数据的规范化

2.1. 范式概述
　　 　　规范化理论是研究如何将一个不好的关系模式转化为好的关系模式的理论，规范化理论是围绕范式而建立的。规范化理论认为，一个关系数据库中所有的关系，都应满足一定的规范(约束条件)。规范化理论把关系应满足的规范要求分为几级，满足最低要求的一级叫做第一范式(1NF)，在第一范式的基础上提出了第二范式(2NF)，在第二范式的基础上又提出了第三范式(3NF)，以后又提出了BCNF范式，4NF，5NF。范式的等级越高，应满足的约束集条件也越严格。规范的每一级别都依赖于它的前一级别，例如若一个关系模式满足2NF，则一定满足1NF。下面我们只介绍1NF，2NF，3NF范式。　　

2.2. 1NF
1NF是关系模型的最低要求，它的规则是： 　　
每一列必须是原子的，不能分成多个子列。
每一行和列的位置只能有一个值。
不能具有多值列。
例：如果要求一个学生一行，一个学生可选多门课，则下面的“学生”表就不满足1NF：
student(s－no,s－name,class－no)
其中：s－no为学号，s－name为学生姓名，class－no为课程号。因为一个学生可选多门课，所以列class－no有多个值，所以空不符合1NF。
　　规范化就是把它分成如下两个表：“学生”表和“选课”表，则这两个表就都满足1NF了。
student(s－no,s－name)
stu－class(s－no,class－no) 　　

2.3. 2NF
　　对于满足2NF的表，除满足1NF外，非主码的列必须依赖于所有的主码，而不是组合主码的一部分。如果满足1NF的表的主码只有一列，则它自动满足2NF。　　例：下面的“选课”表，不符合2NF。
stu－class(s－no,class－no,class－name)
其中：class－name为课程名称。因为词表的主码是：(s－no,class－no),非主码列class－name依赖于组合主码的一部分class－no,所以它不符合2NF。
对该表规范化也是把它分解成两个表：“选课”表和“课程”表，则它们就都满足2NF了。
stu－class(s－no,class－no)
class(class－no,class－name) 　　

2.4. 3NF
　　3NF的规则是除满足2NF外，任一非主码列不能依赖于其它非主码列。 　　例：下面的“课程”表，不符合3NF。
class(class－no,class－name,teacher－no,teacher－name)
其中：teacher－no为任课教师号，teacher－name为任课教师姓名。因为非主码列teacher－name依赖于另一非主码列teacher－no,所以它不符合3NF。　　其解决办法也是把它分解成两个表：“课程”表和“教师”表，则它们就都满足3NF了。 　　
class(class－no,class－name,teacher－no)
teacher(teacher－no,teacher－name) 　

2.5. 小结
　　当一个表是规范的，则其非主码列依赖于主码列。从关系模型的角度来看，表满足3NF最符合标准，这样的设计容易维护。一个完全规范化的设计并不总能生成最优的性能，因此通常是先按照3NF设计，如果有性能问题，再通过反规范来解决。　　
数据库中的数据规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度，但是对完全规范的数据库查询，通常需要更多的连接操作，从而影响查询的速度。因此，有时为了提高某些查询或应用的性能而破坏规范规则，即反规范。

3. 数据的反规范
3.1. 反规范的好处
是否规范化的程度越高越好?这要根据需要来决定，因为“分离”越深，产生的关系越多，关系过多，连接操作越频繁，而连接操作是最费时间的，特别对以查询为主的数据库应用来说，频繁的连接会影响查询速度。所以，关系有时故意保留成非规范化的，或者规范化以后又反规范了，这样做通常是为了改进性能。例如帐户系统中的“帐户”表B－TB01，它的列busi－balance(企业帐户的总余额)就违反规范，其中的值可以通过下面的查询获得：　
select busi－code,sum(acc－balance) 　
from B－TB06 　　
group by busi－code
　　如果B－TB01中没有该列，若想获得busi－name(企业名称)和企业帐户的总余额，则需要做连接操作：　　
select busi－name,sum(acc－balance) 　　
from B－TB01,B－TB06 　　
where B－TB01.busi－code=B－TB06.busi－code 　　
group by busi－code
如果经常做这种查询，则就有必要在B－TB01中加入列busi－balance，相应的代价则是必须在表B－TB06上创建增、删、改的触发器来维护B－TB01表上busi－balance列的值。类似的情况在决策支持系统中经常发生。 　
反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其它方法经常能够解决性能问题，而不必采用反规范这种方法。　　
3.2. 常用的反规范技术
在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算(例如合计)、数据的物理存储位置等。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。　
3.2.1. 增加冗余列
增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作。例如前面例子中，如果经常检索一门课的任课教师姓名，则需要做class和teacher表的连接查询：
　　select class－name,teacher－name 　　
from class,teacher 　　
where class.teacher－no=teacher.teacher－no 　　
这样的话就可以在class表中增加一列teacher－name就不需要连接操作了。
增加冗余列可以在查询时避免连接操作，但它需要更多的磁盘空间，同时增加表维护的工作量。

3.2.2. 增加派生列
增加派生列指增加的列来自其它表中的数据，由它们计算生成。它的作用是在查询时减少连接操作，避免使用集函数。例如前面所讲的账户系统中的表B－TB01的列busi－balance就是派生列。派生列也具有与冗余列同样的缺点。

3.2.3. 重新组表
重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。例如，用户经常需要同时查看课程号，课程名称，任课教师号，任课教师姓名，则可把表class(class－no,class－name,teacher－no)和表teacher(teacher－no,teacher－name)合并成一个表class(class－no,class－name,teacher－no,teacher－name)。这样可提高性能，但需要更多的磁盘空间，同时也损失了数据在概念上的独立性。　　
3.2.4. 分割表
有时对表做分割可以提高性能。表分割有两种方式：　
1水平分割：根据一列或多列数据的值把数据行放到两个独立的表中。 　　水平分割通常在下面的情况下使用:A 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。B 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。C 需要把数据存放到多个介质上。 　　例如法规表law就可以分成两个表active－law和inactive－law。activea－authors表中的内容是正生效的法规，是经常使用的，而inactive－law表则使已经作废的法规，不常被查询。水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。
　　2垂直分割：把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要join操作。　　

4. 反规范技术需要维护数据的完整性
　　无论使用何种反规范技术，都需要一定的管理来维护数据的完整性，常用的方法是批处理维护、应用逻辑和触发器。批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。另一种方式就是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题的最好的办法。　　

5. 结束语
　 　　数据库的反规范设计可以提高查询性能。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。但反规范技术需要维护数据的完整性。因此在做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点。

(ZT)Rewrite SQL subqueries as outer joins

Because SQL is a declarative language, you can write the same query in many forms, each getting the same result but with vastly different execution plans and performance.

In this example, we select all books that do not have any sales.  Note that this is a non-correlated sub-query, but it could be re-written in several ways.

select    book_key from    book where    book_key NOT IN (select book_key from sales);

There are serious problems with subqueries that may return NULL values. It is a good idea to discourage the use of the NOT IN clause (which invokes a sub-query) and to prefer NOT EXISTS (which invokes a correlated sub-query), since the query returns no rows if any rows returned by the sub-query contain null values.

select    book_key from    book where    NOT EXISTS (select book_key from sales);

Subqueries can often be re-written to use a standard outer join, resulting in faster performance.  As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values.  Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

select    b.book_key from    book  b,    sales s where    b.book_key = s.book_key(+) and    s.book_key IS NULL;

This execution plan will also be faster by eliminating the sub-query.

(ZT)何時該使用object? 何時該使用reference? 何時該使用pointer?

 

對於原來會C#、Java，轉而用C++時，總會對C++同時有object、reference、pointer三種機制感到困擾，因為在C#、Java只有object，一切都很單純，但在C++卻很複雜。
在C#如以下的程式

1Foo foo1;
2Foo foo2 = new Foo();

foo1僅宣告了一個物件，但卻尚未建立。
foo2才是宣告了一個物件，且建立了foo2物件。

在C++卻必須這樣寫

1Foo *foo1; // C# : Foo foo1;
2Foo foo2;  // C# : Foo foo2 = new Foo();
3Foo *foo3 = &Foo();    // object on stack
4Foo *foo4 = new Foo(); // object on heap

foo1為指向Foo型別object的pointer，但此時僅有pointer還沒有object，所以等同C#的Foo foo1。

foo2真的是一個object了，相當於C#的Foo foo2 = new Foo();

foo3是一個pointer指向Foo object，此時是一個建立在stack的object，不需手動delete刪除。

foo4是一個pointer指向Foo object，此時是一個建立在heap的object，需手動用delete刪除。

foo1若要繼續指定值

foo1 = &Foo();

或

foo1 = new Foo();

皆可
總而言之，若要建立在stack上的object，且要直接用該object，直接Foo foo2即可，若想先宣告一個object variable，等日後看情形使用，則要用pointer的Foo *foo1或Foo *foo3 = &Foo();這種方式。
若要建立在heap上的object，則一律使用pointer的Foo *foo4 = new Foo();這種方式。
那何時要建立在stack?何時要建立在heap呢?

若object屬於local object，其scope僅在function裡，此時應建立在stack。
若object屬於static object，在離開function後，希望該object仍存在，此時應建立在heap，該object會一直等到手動下delete時，才會消失。
這是C++和C#語法很大的差異之處!!我ㄧ開始也很不習慣。

若以sematics(語意)而言，C#和C++何者語意較適當?
回想我們在使用基本型別時，如

int i;

此時i這個int object已經建立了，所以C++才模仿基本型別建立的方式使用

Foo foo;

符合Bjarne Stroustrup所謂『建立一個和基本型別一樣好用的class』想法，所以語法和基本型別一樣。
C#的想法則是，將型別分成value type和reference type，int屬於value type，固用int i語法，而object屬於reference type，一律使用new語法且建立在heap，因為有GC，所以沒有delete問題。
理解後，兩者都有他的道理!!
何時會用reference呢?

將object傳到function裡，且希望使用polymorphism時，會使用reference，當然此時用pointer亦可，不過習慣上大都使用reference，但不可用object，這樣會造成object slicing，就沒有polymorphism了。

/**//*
(C) OOMusou 2006 http://oomusou.cnblogs.com

Filename    :Polymorphism.cpp
Compiler    : Visual C++ 8.0 / ISO C++
Description : Demo how to use Object Decomposition and Polymorphism.
Release     : 01/12/2007 1.0
*/
#include <iostream>
#include <vector>
#include <string>

using namespace std;

class Student {
protected:
// constructor of abstract base class, since student
// can't be initiated, constructor just can be called
// by constructor of derived class, so put it in protected
// level.
  Student(const char* _name) : name(string(_name)) {}

public:
string getName() const { return this->name; }
// pure virtual fuction
virtual string job() const = NULL;

private:
string name;
};

// public inheritance
class Bachelor : public Student {
public:
// call constructor of abc myself.
  Bachelor(const char* name) : Student(name) {};

public:
string job() const { return "study"; };
};

class Master : public Student {
public:
  Master(const char* name) : Student(name) {};

public:
string job() const { return "study, research"; };
};

// new class for further 
/**//*
class Doctor : public Student {
public:
  Doctor(const char* name) : Student(name) {};

public:
  string job() const { return "study, research, teach"; };
};
*/

class Lab {
public:
// pass reference of student 
void add(Student&);
void listAllJob() const;

private:
// put pointer of student in member vector, can't 
// put reference in vector.
  vector<Student *> member;
};

void Lab::add(Student& student) {
// _student is reference of student object
// &_student is pointer of _student reference
this->member.push_back(&student);
}

void Lab::listAllJob() const {
// POWER of Polymorphism !!
// (*iter) automatically refer to derived object, 
// this is called "dynamic binding".
// if you add new object in the future, you don't
// need to maintain this code.
for(vector<Student *>::const_iterator iter = this->member.begin(); iter != this->member.end(); ++iter) {
    cout << (*iter)->getName() << "'s job:" << (*iter)->job() << endl;
  }
}

int main() {
  Bachelor John("John");
  Master   Mary("Mary");
// Doctor   Jack("Jack");

  Lab CSLab;
  CSLab.add(John);
  CSLab.add(Mary);
// CSLab.add(Jack);

  CSLab.listAllJob();
}

執行結果

John's job:study
Mary's job:study, research

73行

void Lab::add(Student& student) {

將物件傳入function，為了要達成polymorphism，所以用了reference。
何時該使用pointer呢?

將object塞進container且要達成polymorphism時，只能使用pointer!!因為object進入container需copy的動作，但reference不支援copy動作，所以不能使用reference，當然也不能使用object，因為會造成object slicing，如此就沒polymorphism了，70行

vector<Student *> member;

就是個例子，vector只能放Student *才能達到polymorphism。
Conclusion
我很早就想寫這一篇了，因為對於我這個C#轉C++的人來說，已經被reference，pointer搞的暈頭轉向，希望藉由這篇經驗的總結，能讓各位在C++的學習上少走些冤枉路。