数据库创建索引的条件和注意事项

数据库中创建索引是为了提升查询性能，但是建立索引也会降低修改性能。

为什么不对表中的每一列都创建一个索引呢？

建立索引也有不利的一面：

1. 创建索引和维护索引要耗费时间，耗费的时间随着数据量的增加而增加；
2. 索引占据物理空间。除了数据表占据物理空间以外，每一个索引都会占据一定的物理空间。如果建立聚簇索引，那么需要的空间就会更大；如果非聚簇索引很多，一但聚簇索引改变，那么所有非聚簇索引也会跟这变；
3. 当对表中的数据进行增加、删除和修改的视乎，索引也要动态的维护，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变，这样就降低了数据的维护速度；
4. 每个索引都伴随着统计信息，用于SQL优化器执行查询时选择执行路径。索引越多统计信息越过，但过多的索引会导致优化器优化过程需要评估的组合增多。创建索引的时候，应该仔细考虑在哪些列上可以创建索引，哪些列上不能创建索引。

一般来说，应该在下面这些列上创建索引

• 在经常搜索的列上创建索引，能够加快搜索的速度；
• 在作为主键的列上创建索引，需要强制该列的唯一性和组织表中数据的排列结构；
• 在经常被用在连接的列上(主要是外键)建立索引，可以加快连接速度。
• 在经常使用WHERE子句的列上建立索引，加快条件的判断速度。当增加索引时，会提高检索性能，加快条件的判断速度，但是会降低修改性能。

索引可以分为聚簇索引和非聚簇索引。聚簇索引通过树形结构重排表中的数据来提高数据的访问速度，非聚簇索引则通过维护表中的数据指针来提高数据的索引。

创建聚簇索引，应当考虑以下因素

1. 每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个(按序排列表中的数据，只有一种排列方法，笔者注)；
2. 在创建任何非聚簇索引之前都应当先创建聚簇索引。表中行的物理顺序和聚簇索引中行的物理顺序是相同的，创建聚簇索引会改变表中行的物理顺序，数据行按照一定的顺序排列(B+树)，并且自动维护。
3. 关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护。这些标识符是系统自己使用的，用户不能访问。
4. 聚簇索引的平均大小约是数据表的百分之五，但是实际的聚簇索引的大小常常根据索引项的大小变化而变化。
5. 在索引创建的过程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间大小。因此，需要保证有足够的磁盘空间用于创建聚簇索引。

主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统会自动创建一个唯一性的聚簇索引。在逻辑上，主键约束是一种重要的结构；但在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，物理实现上其实不存在主键约束，只存在唯一性的聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比价小。

当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引，那么主键约束或者唯一性键约束创建的索引将会覆盖之前创建的标准索引。这说明了主键约束或者唯一性见约束创建的索引优先级高于CREATE INDEX语句创建的索引。

当创建唯一性索引时，应认真考虑如下规则

• 当在表中创建主键约束或者唯一性键约束时，SQL Server会自动创建一个唯一性索引；
• 如果表中已经包含有数据，那么创建索引时，SQL Server会检查表中已有数据的冗余性；
• 每当使用插入语句或者修改语句时，SQL Server都会检查数据的冗余性；如果有冗余值，那么SQL Server将会取消该语句的执行，并且返回一个错误信息； (确保表中的每一行都有一个唯一值，这样逻辑上可以确保每一个实体都可以唯一确认)
• 只能在可以保证实体完整性的列上创建唯一性索引

索引可以包含一个、两个、甚至更多个列。两个列或者以上列上建立的索引被称作复合索引。

当创建复合索引时，应该考虑以下规则

• 最多可以把16个列合并成一个单独的复合索引，构成符合索引列的总长度不能超过900字节，也就是说复合索引的长度不能太长；
• 在复合索引中，所有的列都必须来自同一个表中，不能跨表建立复合索引；
• 在复合索引中，列的排列顺序是非常重要的(影响查询性能)，因此仔细斟酌列的排列顺序。原则上，应该首先定义最唯一的列。
• 为了是查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中的第一个列。
• 当表中有多个关键列时，复合索引是有用的，这种情况下的符合索引能提高查询性能，同时减少需要在一个表中创建的索引数量。