DBA必备数据库设计优化完美攻略

codedba

2753

19页

379次

2021-03-30

免费下载

第 1 页共 19 页

1 引言

1.1 编写目的

一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个

软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关

的注意事项。

1.2 分析阶段

一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引

了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、

响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。

另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是 OLTP（联机事务处理系统）和 OLAP

（联机分析处理系统）。

1.3 设计阶段

设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。

在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的

性能打下良好的基础。

以下是性能要求设计阶段需要注意的：

1.3.1 数据库逻辑设计的规范化

数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

➢ 第 1 规范：没有重复的组或多值的列，这是数据库设计的最低要求。

➢ 第 2 规范每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大

部分情况下，数据库设计都应该达到第二范式。

➢ 第 3 规范一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，

除非一些特殊作用的表。

更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的

列和较多的表，因而减少了数据冗余，也利于性能的提高。

1.3.2 合理的冗余

➢ 完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

➢ 冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

➢ 冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压

力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，

提高效率。

1.3.3 主键的设计

➢ 主键是必要的，SQL SERVER 的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，

第 2 页共 19 页

所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。

➢ 在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键

的 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 系统设计

第 3 页共 19 页

➢ 整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的 OLTP 系统，可以选择 CS 结构、三层的 CS 结

构等，不同的系统结构其性能的关键也有所不同。

➢ 系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据

库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

1.3.8 索引的设计

在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可

能与将来实际使用的时候会有所区别。

关于索引的选择，应改主意：

➢ 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

➢ 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候

选字段。

➢ 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前

面，把重复率低的字段放在前面。

➢ 一个表不要加太多索引，因为索引影响插入和更新的速度。

1.4 编码阶段

编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。

编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算

的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，

而用 SQL 语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。

下面罗列一些编程阶段需要注意的事项：

1.4.1 只返回需要的数据

返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回

不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：

➢ 横向来看，不要写 SELECT 的语句，而是选择你需要的字段。

➢ 纵向来看，合理写 WHERE 子句，不要写没有 WHERE 的 SQL 语句。

➢ 注意 SELECT INTO 后的 WHERE 子句，因为 SELECT INTO 把数据插入到临时表，这个过程会锁定一些系统表，如果这个

WHERE 子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

➢ 对于聚合查询，可以用 HAVING 子句进一步限定返回的行。

of 19

免费下载

相关文档

评论