• 数据库开发规范

  1. 数据库命名规范

  前缀

  • 对象前缀命名: 前缀命名一般用小写
  • 表的前缀: 业务模块组名前缀
  • 存储过程前缀: udp ，系统存储过程（sp）
  • 自定义函数前缀: udf(User define function)
  • 视图前缀: udv(User Define View)表示用户自定义视图
  • 自定义约束前缀: uck(User Checker)用户自定义约束
  • 索引前缀: idx(Index)表示索引
  • 主键前缀: pk(primary keys)表示主键
  • 主键前缀: fk(foreign keys)表示主键

  常见命名约定

  • 表名使用单数名
    例如：对存储客人信息的表（Customer）不使用Customers

  • 字段名不要存在无用前缀
    例如表‘WeiXinConfig’，既然我已经知道这张表是关于微信的表，里面的名称字段可以可以使用Name,不需要添加无用的前缀类似‘WeiXinName’，‘WeiXinGuanZhuMsg’，‘WeiXinUpImgMsg’等

  • 避免无谓的表名后缀

    • 表是用来存储数据信息的,表是行的集合。那么如果表名已经能够很好地说明其包含的数据信 息，就不需要再添加体现上面两点的后缀了。
    • GuestInfo(存储客户信息)应写成Guest，FlightList（存储航班信息的表）应写成Flight

  • 所有表示时间的字段，统一以 Date 来作为结尾（而不是有的使用Date,有的使用Time）

    以大家都熟悉的论坛来说，需要记录会员最后一次登录的时间，这时候一般人都会把这个字段命名为LoginTime 或者 LoginDate。这时候，已经产生了一个歧义;如果仅看表的字段名称，不去看表的内容，很容易将LoginTime理解成登录的次数，因为，Time还有一个很常用的意思，就是次数

  • 所有表示数目的字段，都应该以Count作为结尾

  • 所有代表链接的字段，均为Url结尾

  • 所有名称的字符范围为：A-Z, a-z, 0-9 和_(下划线)。不允许使用其他字符作为名称

  • 采用英文单词或英文短语（包括缩写）作为名称，不能使用无意义的字符或汉语拼音

  • 名称应该清晰明了，能够准确表达事物的含义，最好可读，遵循“见名知意”的原则

  • 字段名不能使用保留关键字

  2. 数据库设计规范

  范式设计与反范式设计

  • 关于范式 Normal Form

    范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式，才能设计出高效率、优雅的数据库，否则可能会设计出错误的数据库。
    目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，还又称完美范式）。满足最低要求的叫第一范式，简称1NF。在第一范式基础上进一步满足一些要求的为第二范式，简称2NF。其余依此类推。各种范式呈递次规范，越高的范式数据库冗余越小。通常所用到的只是前三个范式，即：第一范式（1NF），第二范式（2NF），第三范式（3NF）。

    • 第一范式（1NF）： 强调的是列的原子性，即列不能够再分成其他几列。简而言之，第一范式就是无重复的列。
    • 第二范式（2NF）： 首先要满足它是1NF，另外还需要包含两部分内容：一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
    • 第三范式（3NF） 在1NF基础上，任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]。第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。

  • 关于范式的讨论

    • 第二范式和第三范式如何区别？

      第二范式：非主键列是否依赖主键（包括一列通过某一列间接依赖主键），要是有依赖关系的就是第二范式；</br>
      第三范式：非主键列是否是直接依赖主键，不能是那种通过传递关系的依赖的。要是符合这种就是第三范式

    • 使用范式有哪些优点和缺点？

      范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。
      范式再给我们带来的上面的好处时，同时也伴随着一些不好的地方：按照范式的规范设计出来的表，等级越高的范式设计出来的表越多。
      
      如第一范式可能设计出来的表可能只有一张表而已，再按照第二范式去设计这张表时就可能出来两张或更多张表，如果再按第三范式或更高的范式去设计这张表会出现更多比第二范式多的表。表的数量越多，当我们去查询一些数据，必然要去多表中去查询数据，这样查询的时间要比在一张表中查询中所用的时间要高很多。也就是说我们所用的范式越高，对数据操作的性能越低。所以我们在利用范式设计表的时候，要根据具体的需求再去权衡是否使用更高范式去设计表。在一般的项目中，我们用的最多也就是第三范式，第三范式也就可以满足我们的项目需求，性能好而且方便管理数据；当我们的业务所涉及的表非常多，经常会有多表发生关系，并且我们对表的操作要时间上要尽量的快，这时可以考虑我们使用“反范式”。

  • 关于反范式

    不满足范式的模型，就是反范式模型
    反范式跟范式所要求的正好相反，在反范式的设计模式，我们可以允许适当的数据的冗余，用这个冗余去取操作数据时间的缩短。本质上就是用空间来换取时间，把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联；

  • 范式和反范式的对比

    模型	优点	缺点
    范式化模型	数据无冗余，便于更新	不利于查询操作
    反范式化模型	便于查询	数据冗余，不利于更新

  RDBMS模型设计过程中，常常使用范式约束我们的模型，但在NOSQL模型中则大量采用反范式，在设计的时候 结合读写的业务场景类分析 对比各项指标 进行综合考虑实施。

主键设计原则

• 主键的数据类型

  最常见的主键数据类型是数字类型、固定长度的字符类型和GUID类型。通常情况下，RDBMS会在主键上建立聚集索引（SQL Server默认都这么做），由于我们使用B-Tree的数据结构来存储索引数据，所以一般对主键有以下两个要求：

  • 越短越好——越短在一个Page中存储的节点越多，检索速度就越快。
  • 顺序增长——如果每一条插入的数据的主键都比前面的主键大，那么B-Tree上的节点也是顺序增长的，不会造成频繁的B-Tree分割。

  越短越好是为了查询的速度快，顺序增长是为了插入速度快。

  有了这两个要求，我们再来分析下各个数据类型：

  • 数字类型：根据数据量决定是用Int16还是Int32或者Int64，能用Int32的就不需要使用Int64。
  • 字符类型：基本不满足前面提到的2点要求，字符类型一般不会很短，而且也很可能不是顺序增长的，所以不是特别推荐的主键类型。当然如果确实业务需求使用字符类型，那么也尽量使用char(XX)而不要使用varchar(XX)，因为在RDBMS中，对于定长字符串和变成字符串的数据结构和处理是不一样的，varchar的性能更差。
  • GUID类型：这个类型并不是所有数据库都有对应的数据类型，SQL Server有uniqueidentifier，MySQL没有。GUID类型在SQL Server中是16个字节，不算短，比4个字节的Int32长多了。在插入新数据时，GUID一般都是使用NewId()这样的生成随机GUID的方式生成的，所以也不是顺序增长的，在插入速度上不会很快。
    通过上面的比较，我们知道使用数字类型是更好的方式，那么我们为什么还会有人使用GUID和字符串来当主键呢？那是因为：

  相对于数字类型，字符类型更易读易记，在检索关联的数据时，更方便直接。

  GUID的优势是全球唯一，也就是说同样的系统，如果部署了多套环境，那么里面的数据的主键仍然是唯一的，这样有助于数据的集成。典型的例子就是一个系统在全国每个省份都部署一套，每个省份的数据各种录入，互不干扰，然后再把每个省的数据集成起来为总部做分析。

  • 数据库主键与业务主键

  前面说到一个表可能有很多个唯一标识的候选键，那么这么多候选键中，哪个应该拿来做主键呢？一种方案是再新建一个独立的字段作为主键，该字段并没有业务含义，只是一个自增列或者流水号，用于唯一标识每一行数据，这是数据库主键。另外一种方案是选择其中较短较常用的属性作为主键，这是业务主键。个人建议是不要使用任何有业务含义的字段作主键，而是使用一个自增的（或者系统生成的）没有实际业务意义的字段作为主键。为什么呢？主要是出于以下考虑：

  具有业务意义的字段很可能是用户从系统录入的，不要信任用户的任何输入，只要是用户自己录入的，那么就很有可能录错了，如果发现录入错误，这个时候再对主键进行修改，将会涉及到大量关联的外键表的修改，是很麻烦的一件事情。比如在做人员表的时候，就不要使用员工号或者身份证号做主键。

  具有业务意义的字段虽然在当前阶段是唯一的，是不变的，但是并不能保证随着公司政策变动、业务调整等原因，导致该业务字段需要修改，以满足新的业务要求，这个时候要修改主键也是很麻烦的事情。比如部门表，我们以部门Code作为主键，但是后来部门变动，Code修改，则系统部门表的主键也得更改。

  还有一个原因是业务主键在数据录入的时候不一定是明确知道的，有时我们会在不知道业务主键的情况下，就录入其他相关信息，这个时候，如果使用业务主键做数据库的主键，那么数据将无法录入。比如员工表把员工号作为主键，那么员工还没有入职，没有员工号的时候，HR需要先维护一些该预入职员工的信息是不可能的。

  • 联合主键

  联合主键就是以多个字段来唯一标识每一行数据。前面已经说到主键应该越短越好，而且是建议是一个没有意义的自增列，那么是不是就不会再需要联合主键呢？答案是否定的，我们仍然可能会使用到联合主键。联合主键主要使用在多对多的关系时，中间表就需要使用联合主键。在简单的多对多关系中，我们不需要为中间的关联建立实体，所以中间表可能就只需要两列，分别是两个实体表的主键。

• 主键值的生成

  主键值的生成 主要有这么几种生成方式：

  • 自增，这是SQL Server常用的主键生成方式，完全由数据库管理主键的值。
  • Sequence对象，这是Oracle常用的主键生成方式，现在SQL Server已支持。主要是在数据库中有一个Sequence对象，通过该对象生成主键。
  • GUID，这是用于GUID类型的主键，可以使用newid()这种数据库提供的函数，或者使用程序生成Guid并赋值。
  • 其他程序赋值，完全由程序根据自己的算法生成并赋值。
  • 主键与索引

  在概念和作用上，主键与索引是完全两个不同的东西，但是由于我们大部分情况下都是使用主键检索数据，所以大部分数据库的默认实现，在建立主键时会自动建立对应的索引。

  以SQL Server为例，默认情况下，建立主键的列，就会建立聚集索引，但是实际上，我们可以在建立主键时不使用聚集索引。另外还有一个唯一约束（索引）的概念，该索引中的数据必须是唯一不能重复的，感觉和主键的意义一样，但是还是有一点点区别。

  主键是只能由一个，而唯一约束（索引）在一个表中可以有多个。

  主键不能为空，而唯一约束（索引）是可以为空的。

外键设计原则

>数据库外键的主要作用是 保持数据的一致性 完整性，但对于外键的使用主要一直是矛盾的焦点，主要有两个问题：一个是如何保证数据库数据的完整性和一致性；二是数据库性能问题

• 观点一
  • 由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。
  • 有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。
  • 外键在一定程度上说明的业务逻辑，会使设计周到具体全面。
• 观点二
  • 可以用应用程序保证数据的完整性
  • 过分强调或者说使用主键／外键会平添开发难度，导致表过多等问题
  • 不用外键时数据管理简单，操作方便，性能高
• 总结:
  • 在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。
  • 用外键要适当，不能过分追求
  • 不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。

索引设计原则

>  基于合理的数据库设计，经过深思熟虑后为表建立索引，是获得高性能数据库系统的基础。而未经合理分析便添加索引，则会降低系统的总体性能。索引虽然说提高了数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。<br/>
是否要为表增加索引、索引建立在那些字段上，是创建索引前必须要考虑的问题。解决此问题的一个比较好的方法，就是分析应用程序的业务处理、数据使用，为经常被用作查询条件、或者被要求排序的字段建立索引。基于优化器对SQL语句的优化处理。

我们在创建索引时可以遵循下面的一般性原则：

• (1)为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
  在这些字段上建立索引，可以有效地避免排序操作。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
• （2）在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
• （3）为经常用作查询选择的字段，建立索引。
• （4）在经常用作表连接的属性上，建立索引。
• （5）考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

除了以上原则，在创建索引时，我们还应当注意以下的限制：

• （1）限制表上的索引数目。
  对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
• （2）不要在有大量相同取值的字段上，建立索引。
  在这样的字段（例如：性别）上建立索引，字段作为选择条件时将返回大量满足条件的记录，优化器不会使用该索引作为访问路径。
• （3）避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。
  由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。
• （4）对复合索引，按照字段在查询条件中出现的频度建立索引。
  在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。
  因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
• （5）删除不再使用，或者很少被使用的索引。
  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

SqlServer数据类型

数据类型	类型	描述
bit	整型	bit 数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或Fa lse 、On 或Off
int	整型	int 数据类型可以存储从- 231(-2147483648)到231 (2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节
smallint	整型	smallint 数据类型可以存储从- 215(-32768)到215(32767)之间的整数。这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。这种数据类型在数据库里占用2 字节空间
tinyint	整型	tinyint 数据类型能存储从0到255 之间的整数。它在你只打算存储有限数目的数值时很有用。这种数据类型在数据库中占用1 个字节
numeric	精确数值型	numeric数据类型与decimal 型相同
decimal	精确数值型	decimal 数据类型能用来存储从-1038-1到1038-1的固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。范围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数
money	货币型	money 数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220 亿之间的数据，精确到货币单位的万分之一
smallmoney	货币型	smallmoney 数据类型用来表示钱和货币值。这种数据类型能存储从-214748.3648 到214748.3647 之间的数据，精确到货币单位的万分之一
float	近似数值型	float 数据类型是一种近似数值类型，供浮点数使用。说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。浮点数可以是从-1.79E+308到1.79E+308 之间的任意数
real	近似数值型	real 数据类型像浮点数一样，是近似数值类型。它可以表示数值在-3.40E+38到3.40E+38之间的浮点数
datetime	日期时间型	datetime数据类型用来表示日期和时间。这种数据类型存储从1753年1月1日到9999年12月3 1日间所有的日期和时间数据， 精确到三百分之一秒或3.33毫秒
Smalldatetime	日期时间型	smalldatetime 数据类型用来表示从1900年1月1日到2079年6月6日间的日期和时间，精确到一分钟
cursor	特殊数据型	cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用
timestamp	特殊数据型	timestamp 数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有“time”，但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的
uniqueidentifier	特殊数据型	uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列
char	字符型	char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000 个字符
varchar	字符型	varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char 型不一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度
text	字符型	text 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符
nchar	统一编码字符型	nchar 数据类型用来存储定长统一编码字符型数据。统一编码用双字节结构来存储每个字符，而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍
nvarchar	统一编码字符型	nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍
ntext	统一编码字符型	ntext 数据类型用来存储大量的统一编码字符型数据。这种数据类型能存储230 -1或将近10亿个字符，且使用的字节空间增加了一倍
binary	二进制数据类型	binary数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的内容接近相同的长度时，你应该使用这种数据类型
varbinary	二进制数据类型	varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的内容大小可变时，你应该使用这种数据类型
image	二进制数据类型	image 数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节

使用原则

• 1.字符类型建议采用varchar/nvarchar数据类型
• 2.金额货币建议采用money数据类型
• 3.科学计数建议采用numeric数据类型
• 4.自增长标识建议采用bigint数据类型 (数据量一大，用int类型就装不下，那以后改造就麻烦了)
• 5.时间类型建议采用为datetime数据类型
• 6.禁止使用text、ntext、image老的数据类型
• 7.禁止使用xml数据类型、varchar(max)、nvarchar(max)

3. 数据库查询规范

禁止在数据库做复杂运算

禁止使用SELECT *

减少内存消耗和网络带宽

给查询优化器有机会从索引读取所需要的列

表结构变化时容易引起查询出错

禁止在索引列上使用函数或计算

禁止在索引列上使用函数或计算

在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描

禁止使用游标

禁止使用触发器

禁止使用存储过程

禁止在查询里指定索引

With(index=XXX)（ 在查询里我们指定索引一般都用With(index=XXX) ）

随着数据的变化查询语句指定的索引性能可能并不最佳

索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障

新建的索引无法被应用立即使用，必须通过发布代码才能生效

变量/参数/关联字段类型必须与字段类型一致

避免类型转换额外消耗的CPU，引起的大表scan尤为严重

参数化查询

限制JOIN个数

单个SQL语句的表JOIN个数不能超过5个

过多的JOIN个数会导致查询分析器走错执行计划

过多JOIN在编译执行计划时消耗很大

限制SQL语句长度及IN子句个数

在 IN 子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误 8623 或 8632，要求IN子句中条件个数限制在100个以内

尽量避免大事务操作

只在数据需要更新时开始事务，减少资源锁持有时间

增加事务异常捕获预处理机制

禁止使用数据库上的分布式事务

除非必要SELECT语句都必须加上NOLOCK

指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设 置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录

使用UNION ALL替换UNION

UNION会对SQL结果集去重排序，增加CPU、内存等消耗

查询大量数据使用分页或TOP

合理限制记录返回数，避免IO、网络带宽出现瓶颈

递归查询层级限制

使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环

EXISTS替代IN

尽量避免使用OR运算符

对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集

增加事务异常处理机制

输出列使用二段式命名格式

多表查询 使用别名 查询结果 别名.字段

4. 数据库架构

读写分离

• 设计之初就考虑读写分离，哪怕读写同一个库，有利于快速扩容
• 按照读特征把读分为实时读和可延迟读分别对应到写库和读库
• 读写分离应该考虑在读不可用情况下自动切换到写端

Schema解耦

• 禁止跨库Join

数据生命周期

• 根据数据的使用频繁度，对大表定期分库归档
• 主库/归档库物理分离

日志类型的表应分区或分表

• 对于大的表格要进行分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，大幅减少IO资源消耗

频繁写入的表，需要分区或分表