实际应用中的索引设计准则

实际应用中的索引设计准则

索引在关系数据库系统中极其重要，如果一个数据库系统的索引没有使用索引或者有索引但是设计得不好，那么系统的性能是很差的。因为如果不使用索引的话，系统需要扫描整个table，这个时间代价极大。我以前对此没有什么感性认识，后来在实际项目中才体会到这点。

索引最主要的功能有两项：第一是确保数据的唯一性，第二是提高数据的访问速度。其中“确保数据的唯一性”是通过创建唯一性索引(Unique Index)来实现的，对此我们不作过多探讨。我们下面主要讨论索引对数据访问速度的影响，在此基础上总结设计索引的经验性准则。

到底哪些数据访问操作能利用索引而提高处理速度呢？常见的，创建索引时，只要选取适当的字段，将能大幅度提高下列操作的速度。

1）  查询操作中的WHERE语句的数据提取

这是最显而易见的了，唯一需要注意的是在WHERE语句中使用LIKE运算符以便要求字段内容符合特定格式的时候，其模式以常数字符串开头者才会使用索引（例如：‘abc%’），如果其模式以通配符开头将不会使用索引，如下两个例子所示：即使已经为name字段创建索引，但是只有第一条查询命令会使用索引，而第二条查询命令并不会使用索引。

 SELECT * FROM employee WHERE name LIKE ‘宋%’

 SELECT * FROM employee WHERE name LIKE ‘%东风’

2）  询操作中ORDER BY语句的数据排序和GROUP BY语句的数据分组

      当以特定的次序显示数据记录时，若为排序的基准字段创建索引，将能提高其速度。与之类似，当进行分组小计时，若为分组的基准字段创建索引，也能提高速度。

3）  连接表

    从各个相关表中提取所需信息是很平常的事情，比如说，客户数据存放在表Customers，订单数据存放在Orders，两者通过字段CustomerID中的客户编号连接，这种情况下，只要为两个表的CustomerID分别创建索引，将大幅度提高数据的访问速度。

4）  更新和删除数据记录

    索引也能提高更新和删除数据记录的速度，因为在更新或者删除数据记录前，必须先找到它。

对于上面提到的索引提高数据的访问速度的4个方面，都是比较单一的，其实他们的效率是可以叠加的，也就是，同一个查询，可以利用多个索引加快其中各项操作的访问速度，这就看我们如何正确的定义和使用索引了。

    上面列举了索引的优点，但是并不是因为它存在这些优点就可以任意使用它的，所谓有利必有弊，如果滥用索引也会带来麻烦。根据前人的经验，我们在设计索引时要参考下面的准则。

Note：只能为下列字段创建索引

Ø         经常被用来搜索数据记录的字段

   经常被用来搜索数据记录的字段最好是识别度较高的字段（也就是数据内容重复性低的字段），例如使用员工编号或者身份证号码来寻找员工数据是非常恰当的，因为每一位员工的编号和身份证号码都不可能相同。使用姓名来搜索员工数据也不错，因为此举符合便利性和操作习惯，而且同名同姓的员工毕竟不多。但是使用家庭住址或薪资来寻找员工数据就显然不恰当了，就家庭住址而言它操作不便（因为要输入很多字符，而且会使得索引过于庞大），就薪资而言它识别性太低。

   另外，SQL Server允许为单一字段或多个字段的组合创建索引，如果为多个字段的组合创建索引，而某个查询又只引用该索引的组合键中的某个字段，这个查询就是覆盖查询，索引对于覆盖查询所提高的速度最显著，比如说，给字段A、B于C的组合创建一个索引，而查询中又只提取B和C的内容，则该查询是覆盖查询。覆盖查询之所以能将访问速度提高到最高点，主要是因为查询要提取的所有数据均位于索引本身的索引页（Index Page）中，而不需引用表所在的数据页，因此能有效降低整体I/O，从而提高访问速度。

Ø         PRIMARY KEY约束所定义的作为主键的字段

Ø         应用UNIQUE约束的字段

Ø         FOREIGN KEY约束所定义的作为外键的字段

Ø         在查询中用来连接表的字段

Ø         经常用来作为排序基准的字段

除了这些以外，一般都不允许为其他字段创建索引，此外，SQL Server中，也不允许为bit,text,ntext,image数据类型的字段创建索引。

之所以要慎选用来创建索引的字段，而不能随意创建，主要有以下的原因：

 索引会占用磁盘空间，创建不必要的索引只会形成浪费。最好不要为数据内容超过20个字节的字段创建索引，因为该索引将会占用太多的磁盘空间，而且较大的索引也需要更多的磁盘输入输出操作，对执行效率也有不良影响。

 索引必须与表中的数据记录确实对应才能够发挥作用，为使索引实时反应表的更新动态，以避免索引的内容过期，每当添加和删除数据记录，或是修改了已创建索引字段的数据内容，SQL Server就必须更新所有相关的索引，由此可见，如果索引过多，将因为必须更新相关内容的索引而使得添加，修改和删除数据记录的速度减慢。

   如果只是用SELECT命令查询数据而并未更新任何数据，则较多的索引确实能有效加快查询速度，这是因为SQL Server能从中挑选出有利者来使用并决定出一个提取数据的最快速方式，因此，如果一个表属于引用性的，其内容很少变动，但是经常被查询，则为它多创建几个索引倒也无所谓，但是对于一个经常性、例行性变动的表而言，则要谨慎地创建确实必要的索引，否则只会拖垮应用程序的执行速度。

 索引创建之后是需要维护的，而这些都需要时间和资源，因此不要创建使用频率很低的索引，耗用过多维护成本。

 尽量不要为数据内容重复率很高的字段创建索引，毕竟带来的收益很少。

 数据记录越多，索引提高数据访问效率的幅度就越明显，反过来说，如果表内含的数据记录很少，索引有时不但提高不了访问速度，反而会降低访问速度，因为当数据记录很少时，SQL Server沿着索引树状结构寻找索引中的数据反而要比表扫描所需的时间还长。

    那么，到底多少条才算多或少呢？可以在SQL查询分析器中执行命令语句，并查看执行计划和执行时间以便了解索引到底有没有帮助。当然要完全设计出好的索引，并不是全部靠条条框框就可以的，要通过实践慢慢摸索，在不断的尝试中修正错误获得进步，一点点的把数据库的设计趋向完善。

    索引设计好后，还要对其进行维护，这已经在上面提到过，只有正确的维护他们，才能够使他们拥有最佳的效率。比如说，表经过一段时间使用后，数据会变成片段，该状况会影响系统执行效率，我们必须改善它，再者，基于操作上的需求和更改，可能必须要改变所引得相关设置，这些都属于索引维护的范畴。    索引创建后，可以随需要更改其设置，如可以更改其名称、改变键列、在聚集和非聚集间切换、更改存放的文件组等。但是如果表拥有极多条数据记录，改变索引的某些设置可能必须耗费极长的时间，若用可视化操作更改索引的相关设置，可以采用“属性”对话框或者“管理索引对话框”，也可以以程序控制方式来更改索引的相关设置，可以视情况而定。