数据库建立索引的原则

数据库建立索引的原则

 

       使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的 值进行排序的一种结构，例如 employee 表的姓（lname）列。如果要按姓查找 特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。

 

 

       索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集 合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

 

       索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序 顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相 似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。

 

       在数据库关系图中，您可以在选定表的“索引/键”属性页中创建、编辑或删除 每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将 保存在数据库中。

 

       建立索引的优点

       1.大大加快数据的检索速度;

       2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

       3.加速表和表之间的连接;

       4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序 的时间。

       索引的缺点

       1.索引需要占物理空间。

       2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降 低了数据的维护速度。

 

       根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引 和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。 提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键 或唯一约束。有关这些约束的更多信息，请参见主键约束和唯一约束。 唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引 与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如， 如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工 都不能同姓。 有关唯一索引的更多信息，请参见创建唯一索引。 主键索引 数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的 主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引 的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时， 它还允许对数据的快速访问。有关主键的更多信息，请参见定义主键。 聚集索引 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表 只能包含一个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。 与非聚集索引相比，聚集索引通常提供更快的数据访问速度。 一、索引 1. 概念：索引是揭示文献内容出处，提供文献查考线索的工具书。 2. 类型：种类很多，从不同的角度可以划分出不同的类型。按文种分，可 以分为中文索引的外文索引；按收录范围分，可以分为综合性索引和专题性索引； 按收录文献的时间分，可以分为近期索引和回溯性索引；按索引款目的标目分， 可以分为题名索引、著者索引、语词索引、主题索引、分类索引等。 3. 功能：揭示文献的内容和指引读者查找信息 4. 作用：索引揭示了一书、一刊的基本情况，如篇目、文句。可以深入、 完整、详细、系统地为读者提所需文献的具体线索。

 

       铁律一：天下没有免费的午餐，使用索引是需要付出代价的。 索引的优点有目共睹，但是，却很少有人关心过采用索引所需要付出的成本。若 数据库管理员能够对索引所需要付出的代价有一个充分的认识，也就不会那么随 意到处建立索引了。 仔细数数，其实建立索引的代价还是蛮大的。如创建索引和维护索引都需要花费 时间与精力。特别是在数据库设计的时候，数据库管理员为表中的哪些字段需要 建立索引，要调研、要协调。如当建有索引的表中的纪录又增加、删除、修改操 作时，数据库要对索引进行重新调整。虽然这个工作数据库自动会完成，但是， 需要消耗服务器的资源。当表中的数据越多，这个消耗的资源也就越多。如索引 是数据库中实际存在的对象，所以，每个索引都会占用一定的物理空间。若索引 多了，不但会占用大量的物理空间，而且，也会影响到整个数据库的运行性能。 可见，数据库管理员若要采用索引来提高系统的性能，自身仍然需要付出不少的 代价。数据库管理员现在要考虑的就是如何在这两个之间取得一个均衡。或者说， 找到一个回报与投入的临界点。

 

        铁律二：对于查询中很少涉及的列或者重复值比较多的列，不要建立索引。 在查询的时候，如果我们不按某个字段去查询，则在这个字段上建立索引也是浪 费。如现在有一张员工信息表，我们可能按员工编号、员工姓名、或者出身地去 查询员工信息。但是，我们往往不会按照身份证号码去查询。虽然这个身份证号 码是唯一的。此时，即使在这个字段上建立索引，也不能够提高查询的速度。相 反，增加了系统维护时间和占用了系统空间。这简直就是搬起石头砸自己的脚呀。 另外，如上面的员工信息表，有些字段重复值比较多。如性别字段主要就是“男”、 “女”;职位字段中也是有限的几个内容。此时，在这些字段上添加索引也不会显著 的增加查询速度，减少用户响应时间。相反，因为需要占用空间，反而会降低数 据库的整体性能。 数据库索引管理中的第二条铁律就是，对于查询中很少涉及的列或者重复值比较 多的列，不要建立索引。

 

       铁律三：对于按范围查询的列，最好建立索引。 在信息化管理系统中，很多时候需要按范围来查询某些交易记录。如在 ERP 系 统中，经常需要查询当月的销售订单与销售出货情况，这就需要按日期范围来查 询交易记录。如有时候发现库存不对时，也需要某段时期的库存进出情况，如 5 月 1 日到 12 月 3 日的库存交易情况等等。此时，也是根据日期来进行查询。 对于这些需要在指定范围内快速或者频繁查询的数据列，需要为其建立索引。因 为索引已经排序，其保存的时候指定的范围是连续的，查询可以利用索引的排序， 加快查询时间，减少用户等待时间。 不过，若虽然可能需要按范围来进行查询，但是，若这个范围查询条件利用的不 多的情况下，最好不好采用索引。如在员工信息表中，可能需要查询 2008 年 3 月份以前入职的员工明细，要为他们增加福利。但是，由于表中记录不多，而且， 也很少进行类似的查询。若维这个字段建立索引，虽然无伤大雅，但是很明显， 索引所获得的收益要低于其成本支出。对数据库管理员来说，是得不偿失的。 再者，若采用范围查询的话，最好能利用 TOP 关键字来限制一次查询的结果。 如第一次按顺序只显示前面的 500 条记录等等。把 TOP 关键字跟范围一起使用， 可以大大的提高查询的效率。

 

       铁律四：表中若有主键或者外键，一定要为其建立索引。 定义有主键的索引列，一定要为其建立索引。因为主键可以加速定位到表中的某 一行。结合索引的作用，可以使得查询的速度加倍。如在员工信息表中，我们往 往把员工编号设置为主键。因为这不但可以提高查询的速度，而且因为主键要求 记录的唯一，还可以保证员工编号的唯一性。此时，若再把这个员工编号字段设 置为索引，则通过员工编号来查询员工信息，其效率要比没有建立索引高出许多。 另外，若要使得某个字段的值唯一，可以通过两种索引方式实现。一种就是上面 所讲的主键索引。还有一种就是唯一索引，利用 UNIQUE 关键字指定字段内容 的唯一性。这两种方式都会在表中的指定列上自动创建唯一索引。这两种方式的 结果没有明显的区别。查询优化器不会区分到底是哪种方式建立的唯一性索引， 而且他们进行数据查询的方式也是相同的。 若某张表中的数据列定义有外键，则最好也要为这个字段建立索引。因为外键的 主要作用就在于表与表之间的连接查询。若在外键上建立索引，可以加速表与表 之间的连接查询。如在员工基本信息表中，有一个字段为员工职位。由于员工职 位经常在变化，在这里，存储的其实只是一个员工职位的代码。在另外一张职位 信息表中详细记录着该职位的相关信息。此时，这个员工职位字段就是外键。若 在这个字段上建立外键，则可以显著提高两张表的连接速度。而且，记录越多， 其效果越加明显。 所以，当表中有外键或者主键的时候，就最好为其建立索引。通过索引，可以强 化主键与外键的作用，提高数据库的性能。

 

       铁律五：对于一些特殊的数据类型，不要建立索引。 在表中，有些字段比较特殊。如文本字段(TXT)、图像类型字段(IMAGE)等等。 如果表中的字段属于这些数据类型，则最好不要为其建立索引。因为这些字段有 一些共同的特点。如长度不确定，要么很长，几个字符;要么就是空字符串。如 文本数据类型常在应用系统的数据库表中用来做备注的数据类型。有时候备注很 长，但有时候又没有数据。若这种类型的字段上建立索引，那根本起不了作用。 相反，还增加了系统的负担。 所以，在一些比较特殊的数据类型上，建立索引要谨慎。在通常情况下，没有必 要为其建立索引。但是，也有特殊的情况。如有时候，在 ERP 系统中，有产品 信息这个表，其中有个产品规格这个字段。有时候，其长度可能长达 5000 个字 符。此时，只有文本型的数据类型可以容纳这么大的数据量。而且，在查询的时 候，用户又喜欢通过规格这个参数来查询产品信息。此时，若不为这个字段建立 索引的话，则查询的速度会很慢。遇到这种情况时，数据库管理员只有牺牲一点 系统资源，为其建立索引。 从这里也可以看出，虽然以上几条说的时铁律，但是，是否需要遵循，还是需要 数据库管理员根据企业的实际情况，做出合理的选择。

 

       铁律六：索引可以跟 Where 语句的集合融为一体。 用户在查询信息的时候，有时会经常会用到一些限制语句。如在查询销售订单的 时候，经常会用到客户以及下单日期的条件集合;如在查询某个产品的库存交易 情况时，就会利用产品编号与交易日期起止日期的条件集合。 对于这些经常用在 Where 子句中的数据列，将索引建立在 Where 子句的集合过 程中，对于需要加速或者频繁检索的数据列，可以让这些经常参与查询的数据列 按照索引的排序进行查询，以加快查询的时间。

 

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