数据库知识学习，数据库设计优化攻略(一)
1. 基础规范
1.3.1 数据库逻辑设计的规范化
数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：
➢ 第 1 规范：没有重复的组或多值的列，这是数据库设计的最低要求。
➢ 第 2 规范 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大
部分情况下，数据库设计都应该达到第二范式。
➢ 第 3 规范 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，
除非一些特殊作用的表。
更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的
列和较多的表，因而减少了数据冗余，也利于性能的提高。
1.3.2 合理的冗余
➢ 完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。
➢ 冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。
➢ 冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压
力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，
提高效率。
1.3.3 主键的设计
➢ 主键是必要的，SQL SERVER 的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，
所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。
➢ 在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键
的 B 树结构的层次更少。
➢ 主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般
应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
1.3.4 外键的设计
➢ 外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：
➢ 外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK 约束、规则约束、触发器、客
户端程序，一般认为，离数据越近的方法效率越高。
➢ 谨慎使用级联删除和级联更新，级联删除和级联更新作为 SQL SERVER 2000 当年的新功能，在 2005 作 了保留，
应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点
太过强大，使用前必须确定自己已经把握好 其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙
的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。
1.3.5 字段的设计
字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：
➢ 数据类型尽量用数字型，数字型的比较比字符型的快很多。
➢ 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
➢ 尽量不要允许 NULL，除非必要，可以用 NOT NULL+DEFAULT 代替。
➢ 少用 TEXT 和 IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
➢ 自增字段要慎用，不利于数据迁移。
1.3.6 数据库物理存储和环境的设计
➢ 在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比
较多的用户并发和比较大的数据量。
➢ 这里需要注意文件组的作用，适用文件组可以有效把 IO 操作分散到不同的物理硬盘，提高并发能力。
1.3.7 系统设计
➢ 整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的 OLTP 系统，可以选择 CS 结构、三层的 CS 结
构等，不同的系统结构其性能的关键也有所不同。
➢ 系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据
库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。
1.3.8 索引的设计
在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可
能与将来实际使用的时候会有所区别。
关于索引的选择，应改主意：
➢ 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
➢ 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候
选字段。
➢ 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前
面，把重复率低的字段放在前面。
➢ 一个表不要加太多索引，因为索引影响插入和更新的速度。
1.4 编码阶段
编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。
编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算
的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，
而用 SQL 语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。
下面罗列一些编程阶段需要注意的事项：
1.4.1 只返回需要的数据
返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回
不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：
➢ 横向来看，不要写 SELECT 的语句，而是选择你需要的字段。
➢ 纵向来看，合理写 WHERE 子句，不要写没有 WHERE 的 SQL 语句。
➢ 注意 SELECT INTO 后的 WHERE 子句，因为 SELECT INTO 把数据插入到临时表，这个过程会锁定一些系统表，如果这个
WHERE 子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。
➢ 对于聚合查询，可以用 HAVING 子句进一步限定返回的行。
1.4.2 尽量少做重复的工作
这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：
➢ 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。
➢ 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。
➢ 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
➢ 合并对同一表同一条件的多次 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’
➢ UPDATE 操作不要拆成 DELETE 操作+INSERT 操作的形式，虽然功能相同，但是性能差别是很大的。
➢ 不要写一些没有意义的查询，比如 SELECT FROM EMPLOYEE WHERE 1=2
1.4.3 注意事务和锁
事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事
务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：
➢ 事务操作过程要尽量小，能拆分的事务要拆分开来。
➢ 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
➢ 事务操作过程要按同一顺序访问对象。
➢ 提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。
➢ 尽量不要指定锁类型和索引，SQL SERVER 允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER
优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数
据量和数据分布在将来是会变化的。
➢ 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。
1.4.4 注意临时表和表变量的用法
在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：
➢ 如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。
➢ 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。
➢ 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。
➢ 其他情况下，应该控制临时表和表变量的使用。
➢ 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选
择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。
➢ 关于临时表产生使用 SELECT INTO 和 CREATE TABLE + INSERT INTO 的选择，我们做过测试，一般情况下，SELECT INTO
会比 CREATE TABLE + INSERT INTO 的方法快很多，但是 SELECT INTO 会锁定 TEMPDB 的系统表 SYSOBJECTS、SYSINDEXES、
SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用 CREATE TABLE +
INSERT INTO，而大数据量的单个语句使用中，使用 SELECT INTO。
➢ 注意排序规则，用 CREATE TABLE 建立的临时表，如果不指定字段的排序规则，会选择 TEMPDB 的默认排序规则，而不
是当前数据库的排序规则。如果当前数据库的排序规则和 TEMPDB 的排序规则不同，连接的时候就会出现排序规则的冲
突错误。一般可以在 CREATE TABLE 建立临时表时指定字段的排序规则为 DATABASE_DEFAULT 来避免上述问题。
1.4.5 子查询的用法
子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达
式的地方都可以使用子查询。
子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会
形成一个性能瓶颈。
如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用 IN、NOT IN、
EXISTS、NOT EXISTS 引入。
关于相关子查询，应该注意：
➢ NOT IN、NOT EXISTS 的相关子查询可以改用 LEFT JOIN 代替写法。比如：
➢ 如果保证子查询没有重复 ，IN、EXISTS 的相关子查询可以用 INNER JOIN 代替。比如：
➢ IN 的相关子查询用 EXISTS 代替，比如
➢ 不要用 COUNT()的子查询判断是否存在记录，最好用 LEFT JOIN 或者 EXISTS，比如有人写这样的语句：
1.4.6 慎用游标
数据库一般的操作是集合操作，也就是对由 WHERE 子句和选择列确定的结果集作集合操作，游标是提供的一个非集合
操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功
能搬到客户端。
游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非
常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。
另外，我们可以用 SQL SERVER 的一些特性来代替游标，达到提高速度的目的。
➢ 字符串连接的例子
这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把 JOB_ID=10 的
EMPLOYEE 的 FNAME 连接在一起，用逗号连接，可能最容易想到的是用游标：
➢ 用 CASE WHEN 实现转换的例子
很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用 CASE
WHEN 语句进行必要的判断处理，而且 CASE WHEN 是可以嵌套的。
变量参与的 UPDATE 语句的例子
SQL ERVER 的语句比较灵活，变量参与的 UPDATE 语句可以实现一些游标一样的功能，比如：
在 SELECT A,B,C,CAST(NULL AS INT) AS 序号
INTO #T
FROM 表
ORDER BY A ,NEWID()
产生临时表后，已经按照 A 字段排序，但是在 A 相同的情况下是乱序的，这时如果需要更改序号字段为按照 A 字段分
组的记录序号，就只有游标和变量参与的 UPDATE 语句可以实现了，这个变量参与的 UPDATE 语句如下：
DECLARE @A INT
DECLARE @序号 INT
UPDATE #T SET
@序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END,
@A=A,
序号=@序号
➢ 如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项 FAST_FORWARD），一般
只需要静态游标（选项 STATIC）。
➢ 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定
性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。
1.4.7 尽量使用索引
建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在
查询语句中没有强制指定索引，索引的选择和使用方法是 SQLSERVER 的优化器自动作的选择，而它选择的根据是查询语句的
条件以及相关表的统计信息，这就要求我们在写 SQL 语句的时候尽量使得优化器可以使用索引。
为了使得优化器能高效使用索引，写语句的时候应该注意：
➢ 不要对索引字段进行运算，而要想办法做变换，比如
➢ 不要对索引字段进行格式转换
➢ 不要对索引字段使用函数
WHERE LEFT(NAME, 3)=’ABC’ 或者 WHERE SUBSTRING(NAME,1, 3)=’ABC’
➢ 不要对索引字段进行多字段连接
1.4.8 注意连接条件的写法
多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。
➢ 多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。
➢ 连接条件尽量使用聚集索引
➢ 注意 ON 部分条件和 WHERE 部分条件的区别
1.4.9 其他需要注意的地方
经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要
注意：
➢ 程序员注意、关心各表的数据量。
➢ 编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。
➢ 每个 SQL 语句尽量简单
➢ 不要频繁更新有触发器的表的数据
➢ 注意数据库函数的限制以及其性能
1.4.10 学会分辩 SQL 语句的优劣
自己分辨 SQL 语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。
➢ 查看 SQL 语句的执行计划，可以在查询分析其使用 CTRL+L 图形化的显示执行计划，一般应该注意百分比最大的几个图
形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是
CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN 等，其中出现 SCAN 说
明语句有优化的余地。也可以用语句 SET SHOWPLAN_ALL ON 要执行的语句 SET SHOWPLAN_ALL OFF 查看执行计划
的文本详细信息。
➢ 用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU 用量以及 IO 数据，从而分析语句的效
率。
➢ 可以用 WINDOWS 的系统性能检测器，关注 CPU、IO 参数
1.5 测试、运行、维护阶段
测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题
的地方，并进行必要的优化。主要进行语句优化、索引优化等。
试运行和维护阶段是在实际的环境下运行系统，发现的问题范围更广，可能涉及操作系统、网络以及多用户并
发环境出现的问题，其优化也扩展到操作系统、网络以及数据库物理存储的优化。
这个阶段的优花方法在这里不再展开，只说明下索引维护的方法：
➢ 可以用 DBCC DBREINDEX 语句或者 SQL SERVER 维护计划设定定时进行索引重建，索引重建的目的是提高索引的效
能。
➢ 可以用语句 UPDATE STATISTICS 或者 SQL SERVER 维护计划设定定时进行索引统计信息的更新，其目的是使得统
计信息更能反映实际情况，从而使得优化器选择更合适的索引。
➢ 可以用 DBCC CHECKDB 或者 DBCC CHECKTABLE 语句检查数据库表和索引是否有问题，这两个语句也能修复一般的
问题。
2. 设计技巧
2.1 分类拆分数据量大的表
对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。
2.2 索引设计
在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段
建立的顺序进行。
2.3 数据操作的优化
2.4 数据库参数的调整
数据库参数的调整是一个经验不断积累的过程，应由有经验的系统管理员完成。
2.5 必要的工具
2.6 避免长事务。
2.7 通俗地理解三个范式
通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就
必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：
第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运
行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式
，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是
最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间
2.8 提高数据库运行效率的办法
在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
(1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。
(2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面。
(3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，
以该表主键 PK 的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分
割该表，将原来的一个表分解为两个表。
(4) 对数据库管理系统 DBMS 进行系统优化，即优化各种系统参数，如缓冲区个数。
(5) 在使用面向数据的 SQL 语言进行程序设计时，尽量采取优化算法。
总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时
下功夫。
3 大数量性能优化设计
数据库优化包含以下三部分，数据库自身的优化，数据库表优化，程序操作优化
3.1 数据库自身的优化
3.1.1 增加次数据文件，设置文件自动增长（粗略数据分区）
➢ 增加次数据文件
从 SQL SERVER 2005 开始，数据库不默认生成 NDF 数据文件，一般情况下有一个主数据文件（MDF）就够了，但是有
些大型的数据库，由于信息很多，而且查询频繁，所以为了提高查询速度，可以把一些表或者一些表中的部分记录分开存储
在不同的数据文件里
由于 CPU 和内存的速度远大于硬盘的读写速度，所以可以把不同的数据文件放在不同的物理硬盘里，这样执行查询
的时候，就可以让多个硬盘同时进行查询，以充分利用 CPU 和内存的性能，提高查询速度。在这里详细介绍一下其写入的
原理，数据文件（MDF、NDF）和日志文件（LDF）的写入方式是不一样的：
数据文件：SQL Server 按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有
空间的数据文件里，如果有三个数据文件 A.MDF，B.NDF，C.NDF，空闲大小分别为 200mb，100mb，和 50mb，那么写入一个
70mb 的东西，他就会向 ABC 三个文件中一次写入 40、20、10 的数据，如果某个日志文件已满，就不会向其写入
日志文件：日志文件是按照顺序写入的，一个写满，才会写入另外一个
由上可见，如果能增加其数据文件 NDF,有利于大数据量的查询速度，但是增加日志文件却没什么用处。
➢ 设置文件自动增长（大数据量，小数据量无需设置）
在 SQL Server 2005 中，默认 MDF 文件初始大小为 5MB，自增为 1MB，不限增长，LDF 初始为 1MB，增长为 10%，限制文
件增长到一定的数目，一般设计中，使用 SQL 自带的设计即可，但是大型数据库设计中，最好亲自去设计其增长和初始大小，
如果初始值太小，那么很快数据库就会写满，如果写满，在进行插入会是什么情况呢？当数据文件写满，进行某些操作时，
SQL Server 会让操作等待，直到文件自动增长结束了，原先的那个操作才能继续进行。如果自增长用了很长时间，原先的
操作会等不及就超时取消了（一般默认的阈值是 15 秒），不但这个操作会回滚，文件自动增长也会被取消。也就是说，这一
次文件没有得到任何增大，增长的时间根据自动增长的大小确定的，如果太小，可能一次操作需要连续几次增长才能满足，
如果太大，就需要等待很长时间，所以设置自动增长要注意一下几点：
1）要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不必要的麻烦。建议对比
较小的数据库，设置一次增长 50 MB 到 100 MB。对大的数据库，设置一次增长 100 MB 到 200 MB。
2）要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的比例。
3）设置文件最大值，以免 SQL Server 文件自增长用尽磁盘空间，影响操作系统。
4）发生自增长后，要及时检查新的数据文件空间分配情况。避免 SQL Server 总是往个别文件写数据。
因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，
但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。
➢ 数据和日志文件分开存放在不同磁盘上
数据文件和日志文件的操作会产生大量的 I/O。在可能的条件下，日志文件应该存放在一个与数据和索引所在的数据
文件不同的硬盘上以分散 I/O，同时还有利于数据库的灾难恢复。
