Sql Server系列：数据库物理模式设计

1、开发数据库时常用命名规范

　　1>、使用不包含数字的单数表名，如果必要的话可以增加模块名前缀。

　　2>、对于主键的命名，使用表名+ID的形式。

　　3>、作为外键的列的名字应该与它们所对应的主键的列名相同，除非该外键维护的是一种自反/递归的关系。

　　4>、避免使用缩写。

2、主键设计原则

　　1>、主键用途：惟一标识一行；作为一个可以被外部键有效引用的对象。

　　2>、主键应当对用户没有意义。

　　3>、主键应该是单列的，以便提高连接和筛选操作的效率。复合主键仅用于描述多对多关系的连接表中用两个外键来作为主键。

　　4>、永远不要更新主键。

　　5>、主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

　　6>、主键应当由计算机自动生成。

　　7>、主键是通过Unique约束（非空约束）以及聚集或非聚集惟一索引来实现的。主键应当是没有实际意义的、单列的、由计算机生成的、不可编辑的，而且对用户来说是不可见的。

2.1、创建主键

　　在表结构设计中使用聚集索引创建索引，并不是最好，由于每个表只能创建一个聚集索引，这样的做法会失去为每个表创建其他更有效的聚集索引的机会。

　　sql语句创建非聚集索引主键：

CREATE TABLE dbo.Product
(
    ProductID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    ProductName VARCHAR(100) NULL
)

2.2、主键使用标识列

　　标识列优点：易于人工识别，占用空间小而且快。

　　将标识列设置为使用聚集索引的主键（这是一种很常见，但并不好的方法），当单个用户检索一行记录的时候，其速度极快。然而，这样的设计方法会在数据库中形成锁争用的热点。

2.3、主键使用GUID

　　GUID是由16个字节的16进制数组成，不会出现重复的数字。与标识列相比，GUID可以在更大的范围保证惟一性。

　　GUID优点：复制使用GUID主键的数据库时不必额外做全面的检查，GUID值的随机性可以减少数据库的热点，可以使用多种方法来生成GUID的值。

　　sql语句创建GUID主键表：

CREATE TABLE dbo.Product
(
    ProductID UNIQUEIDENTIFIER DEFAULT(NEWID()) PRIMARY KEY NONCLUSTERED,
    ProductName VARCHAR(100) NULL
)

3、外键设计原则

　　外键可以引用除了临时表以外的其他表的主键、Unique约束或惟一索引的列。

4、索引

　　SQL Server使用两种基本类型的索引：聚集索引和非聚集索引。一般创建索引的列为：主键、外键、order by列。

　　索引设计原则：

　　◊ 索引并不是越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。当表数据更新时，索引也会进行调整和更新。

　　◊ 避免对经常更新的表进行过多的索引，并且索引的列尽可能少。对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

　　◊ 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

　　◊ 在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如【性别】字段是只有【男】和【女】两个不同值，因此就无须建立索引。如果建立索引，不但不会提高效率，反而会严重降低更新速度。

　　◊ 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引能够确保定义的列的数据完整性，提高查询速度。

　　◊ 在频繁进行排序或分组（ORDER BY或GROUP BY）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

4.1、非聚集索引

　　创建非聚集索引SQL语句：

CREATE NONCLUSTERED INDEX IX_ProductNo ON dbo.Product(ProductNo)

4.2、聚集索引

　　聚集索引将表中的记录行与索引键值以同样的物理顺序存储。由于数据只可能有一种物理存储顺序，因此每个表只能有一个聚集索引。通过聚集索引检索数据只需要更少的逻辑读取的次数，任意非聚集索引都将指向聚集索引的ID而不需要指向数据页。

　　聚集索引将具有同样索引键值的记录行集中到尽可能少的数据页中，因而减少了检索一组记录时需要读取的数据页数。所以，对于那些经常作为条件来查询一组记录的列，如类似于OrderDetail.OrderID这样的从表外键，对其建立聚集索引是一个很好的选择。基于同样的原因，对于只返回一行记录的查询，使用聚集索引不会明显地提高性能。

 

　　对于聚集索引，常见的错误认识有：

　　1>、聚集索引会降低insert操作的速度，因为必须要向后移动一半的数据来为插入的行腾出空间。这种认识是错误的，因为可以利用填充因子控制填充的百分比，从而在索引页上为新插入的数据保留空间。如果索引页填满了，SQL Server将会进行页拆分，在这种情况下只有第一个页才会受到影响。

　　2>、在使用标识列的主键上创建聚集索引是一种好的设计方法，它可以使对表的操作达到最快速度。这种认识是错误的，它浪费了创建其他更有效的聚集索引的机会。并且，使用这种方法把每个新插入的记录行都存储到表尾部的同一个数据页中，这将导致数据库的热点与锁争用。虽然行锁可以部分地缓解这个热点问题，但是，根据要锁定的行的数量、页上的行的数量、用户的数量，锁管理器有可能会把行锁升级为页锁，此时又会出现热点问题。

　　3>、聚集索引是具有魔力的。如果哪个查询的速度不够快，那么久在该列上创建聚集索引，对于表的操作速度一定会得到提高。这种认识是错误的，聚集索引只是比非聚集索引稍微快一点点。因为在每个表上只能创建一个聚集索引，索引它也是一种宝贵的性能资源，只有在那些经常作为条件查询一组记录行的列上才应该创建聚集索引。

　　SQL语句创建聚集索引：

CREATE CLUSTERED INDEX IX_OrderID ON dbo.OrderDetail(OrderID)

　　SQL语句删除索引：

DROP INDEX OrderDetail.IX_OrderID

4.3、组合索引

 　　组合索引是拥有多个索引列的聚集或非聚集索引。因为组合索引包含多个索引列，所以只有在创建表之后，才能使用create index DDL命令来创建。

　　SQL语句创建组合索引：

CREATE NONCLUSTERED INDEX IX_ProductNoProductName ON dbo.Product(ProductNo,ProductName)

　　在组合索引中，索引排列的先后顺序非常重要。因为只有这样的一些查询才能够使用组合索引：它们在查询条件中所包含的索引列在组合索引中是从第一个索引列开始按照从左到右的顺序依次排列的。上例的组合索引中，索引列的顺序是ProductNo、ProductName，只以ProductName为条件的查询将不会使用这个索引，只有以ProductNo或者ProductNo和ProductName为条件的查询才能使用这个索引。

4.4、索引选项

　　1>、Unique索引

　　SQL Server的索引具有几个选项，包括惟一性、空间分配和性能选项。

　　unique索引选项并非只是一个具有惟一约束的索引；对于unique索引可以进行索引优化。在创建主键或者unique约束时，系统都会自动地为它们创建unique索引。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo

 　　2>、索引填充因子（FillFactor）和填充索引（PAD_INDEX）

　　索引需要在其索引树上保留一点空间，这样，在插入新的记录时就不必对索引进行重构。填充因子指定了在索引上填有数据空间所占的百分比。

　　填充因子应该设置为多少才最合适，取决于数据库的用途。如果数据库主要用于数据检索，就应该将填充因子设为较大的值以尽可能填满一个索引页的空间。如果需要对表进行大量插入操作，在索引页上保留一些空间就可以提供更新操作的性能。如果对于表插入操作的数量变化幅度较大，将填充因子设置为一个中间的值，并选择填充索引选项就比较合适。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo WITH FILLFACTOR=85 PAD_INDEX

 　　3>、索引排序选项（ASC|DESC）

　　尽管SQL Server可以创建降序索引，但不建议改变默认的升序索引顺序。这样做并不能改善性能，但却容易在今后造成一些混乱。除非明确地在Order by字句中使用desc选项，否则，所有使用order by字句的查询仍将按照升序排序。

　　创建升序或者降序索引的方法是：在create index DDL命令中的列名之后使用asc或者desc选项。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo DESC WITH FILLFACTOR=85 PAD_INDEX

　　4>、忽略重复键选项（IGNORE_DUP_KEY）

　　忽略重复键选项不是作用于索引的，它指定了索引对今后的数据修改操作的处理方式。

　　通常，事务都是原子的，这意味着整个事务中的操作作为一个逻辑单元要么全部成功，要么全部失败。如果设置了忽略重复键选项，允许insert事务只提交那些为unique索引所接收的记录行，而忽略那些违反了unique索引的惟一性的记录行。

　　这样选项不会破坏unique索引。在表中仍然不会插入重复的值，所以数据库的一致性是完整无损的，但事务的原子性却被违反了。

　　利用这个选项可以方便地向数据库中导入大量可能存在问题的记录行，但是消弱了数据库的ACID属性。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo WITH IGNORE_DUP_KEY