数据库建立索引的原则

铁律一：天下没有免费的午餐，使用索引是需要付出代价的

 

        索引的优点有目共睹，但是，却很少有人关心过采用索引需要付出的成本，若数据库管理员能够对索引付出的代价能有有一个充分的认识，也不会那么随意的到处建立索引了。

       仔细数数，其实建立索引的代价还是挺大的。如创建索引和维护需要花费时间和精力。特别是在数据库设计的时候，数据库管理员为表中的那些字段需要建立索引，要调研、要协调。如当建有索引的表中的记录又增加、删除修改操作时，数据库要对索引重新进行调整。虽然这个操作数据库会自动完成，但是，需要消耗数据库的资源。当数据库表中的数据越来越多，这个消耗的资源也会越来越多。如索引是数据库中实际存在的对象，所以每个索引都会占用一定的物理空间。若是数据库索引多了，不但会占用大量的物理空间，而且，也会影响到整个数据库的运行性能。

       可见，数据库管理员若要采用索引来提高系统的性能，自身任然需要付出不少代价。数据库管理员现在要考虑的就是如何在这两个之间取得一个平衡。或者说，找到一个汇报和投入的临界点。

 

铁律二：对于查询中很少涉及的列或者重复值比较多的列，不要建立索引

 

       在查询的时候，如果不按某个字段去查询，则在这个字段上建立索引页是浪费。如现在又一张员工信息表，我们可能按照员工编号、员工姓名或者出生地去查询员工信息。但是我们往往不会按照身份证号码去查询。虽然这个身份证号码是唯一的。相反，增加了系统维护时间和占用了系统时间。这简直是搬起石头砸自己的脚。

       另外，如上面的员工信息表，有些字段重复值比较多。如性别字段就只是“男”和“女”；职位字段中也是有限的几个内容。此时，在这些字段上增加索引也不会显著的增加查询速度，减少用户响应时间。相反，需要占用空间，反而会降低数据库的整体性能。

 

铁律三：对于按范围查询的列，最好建立索引

 

       在信息化管理的系统中，很多时候需要按照范围来查询某些交易记录。如在ERP系统中，经常需要查询当月的销售订单和销售出货情况，这就需要按日期范围来查询交易记录。如果有时候发现库不对时，也需要某段时期的库存进出情况，如5月1日到12月3日的库存交易情况等等。此时也是根据日期来进行查询。

       对于这些需要在指定范围内快速或者频繁查询的数据列，需要为其建立索引。因为索引已经排序，其保存的时候指定的范围是连续的，查询可以利用索引的排序，加快查询时间，减少用户等待时间。

       不过，若虽然可能需要按照范围来查询，但是，若这个查询范围利用不多的情况下，最好不要采用索引。如在员工信息表中，可能需要查询，2010年3月份以前入职的员工明细，要为他们增加福利。但是由于表中记录不多，而且也很少进行类似的查询。若为这个字段建立索引，虽然无上大雅，但是很明显，索引所获得的收益要低于其成本支出。对数据库管理员来说，是得不偿失的。

        再者，若采用范围查询的话，最好能利用TOP关键字来限制一次查询的结果。如第一次按顺序只显示前面的 500条记录等等。吧TOP关键词跟范围一起使用，可以大大提高查询的效率。

 

铁律四：表中若有主键或者外键，则一定要为其建立关键词

 

        定义有主键的索引列，一定要为其建立索引。因为主键可以加速定位到表中的某一行。结合索引的所用，可以使得查询速度加倍，如在员工信息表中，我们往往把员工编号设为主键。因为这不仅可以提高查询的速度，而且因为主键要求记录的唯一，还可以保证员工编号的唯一性.此时，若再吧这个员工编号字段设置为索引，则通过员工编号来查询员工信息，其效率要比没有创建索引的时候高出许多。

        另外，若要使得某个字段的值唯一，可以通过两种索引方式实现。一种就是上面所讲的主键索引。还有一种就是唯一索引，利用UNIQUE关键字指定字段内容的唯一性。这两种方式都会在表中的指定列上自动创建唯一索引。这两种方式的结果没有明显的区别。查询优化器不会区分到底是哪种方式建立的唯一性索引，而且他们进行数据查询的方式也是相同的。

       若某张表中的数据列定义有外键，则最好也要为这个字段建立索引。因为外键的主要作用就在于表与表之间的连接查询。若在外键上建立索引，可以加速表与表之间的连接查询。如在员工基本信息表中，有一个字段为员工职位。由于员工职位经常在变化，在这里，存储的其实只是一个员工职位的代码。在另外一张职位信息表中详细记录着该职位的相关信息。此时，这个员工职位字段就是外键。若在这个字段上建立外键，则可以显著提高两张表的连接速度。而且，记录越多，其效果越加明显。

      所以，当表中有外键或者主键的时候，就最好为其建立索引。通过索引，可以强化主键与外键的作用，提高数据库的性能。

 

铁律五：对于一些特殊的数据类型，不要创建索引

      在表中，有些字段比较特殊。如文本字段(TXT)、图像类型字段(IMAGE)等等。如果表中的字段属于这些数据类型，则最好不要为其建立索引。因为这些字段有一些共同的特点。如长度不确定，要么很长，几个字符;要么就是空字符串。如文本数据类型常在应用系统的数据库表中用来做备注的数据类型。有时候备注很长，但有时候又没有数据。若这种类型的字段上建立索引，那根本起不了作用。相反，还增加了系统的负担。

      所以，在一些比较特殊的数据类型上，建立索引要谨慎。在通常情况下，没有必要为其建立索引。但是，也有特殊的情况。如有时候，在ERP系统中，有产品信息这个表，其中有个产品规格这个字段。有时候，其长度可能长达5000个字符。此时，只有文本型的数据类型可以容纳这么大的数据量。而且，在查询的时候，用户又喜欢通过规格这个参数来查询产品信息。此时，若不为这个字段建立索引的话，则查询的速度会很慢。遇到这种情况时，数据库管理员只有牺牲一点系统资源，为其建立索引。

      从这里也可以看出，虽然以上几条说的时铁律，但是，是否需要遵循，还是需要数据库管理员根据企业的实际情况，做出合理的选择。

     

铁律六：索引可以跟where语句融合为一体

 

       用户在查询信息的时候有时候会经常遇到一些限制语句。如在查询订单的时候，经常会用到客户和下订单的日期的条件集合；如在查询库存交易情况的时候，就会利用产品编号和交易日期起止日期的条件集合。

      对于这些经常用在where子句中的数据列，将索引建立在where子句的集合过程中，对于需要加速或者频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，以加快查询的时间。

     

      总之，索引就好像一把双刃剑，即可以提高数据库的性能，也可能对数据库的性能起到反面作用。作为数据库管理员，要有这个能力判断在合适的时间、合适的业务、合适的字段上建立合适的索引。以上六个铁律，只是对建立索引的一些基本要求