精华内容
下载资源
问答
  • 数据库建立索引的原则 建立索引的原则: 定义主键的数据列一定要建立索引。 定义有外键的数据列一定要建立索引。 对于经常查询的数据列最好建立索引。 对于需要在指定范围内的快速或频繁查询的数据列; 经常用在...

    数据库建立索引的原则


    建立索引的原则:

    1. 定义主键的数据列一定要建立索引。
    2. 定义有外键的数据列一定要建立索引。
    3. 对于经常查询的数据列最好建立索引。
    4. 对于需要在指定范围内的快速或频繁查询的数据列;
    5. 经常用在WHERE子句中的数据列。
    6. 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
    7. 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
    8. 对于定义为text、image和bit的数据类型的列不要建立索引。
    9. 对于经常存取的列避免建立索引
    10. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
    11. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

    组合多个索引
      一个单独的索引扫描只能用于这样的条件子句:使用被索引字段和索引操作符类中的操作符, 并且这些条件以AND连接。假设在(a, b)上有一个索引, 那么类似WHERE a = 5 AND b = 6的条件可以使用索引,但是像WHERE a = 5 OR b = 6的条件就不能直接使用索引。
    一个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独立扫描,每个扫描使用一个条件, 最后将这些扫描的结果OR 在一起,生成最终结果。另外一个例子是,如果我们在x 和y上有独立的索引,一个类似WHERE x = 5 AND y = 6 这样的查询可以分解为几个使用独立索引的子句,然后把这几个结果AND 在一起,生成最终结果。

    在大多数最简单的应用里,可能有多种索引组合都是有用的,数据库开发人员必须在使用哪个索引之间作出平衡。有时候多字段索引是最好的,有时候创建一个独立索引并依靠索引组合是最好的。比如,假如你的查询有时候只涉及字段x,有时候只涉及字段y,有时候两个字段都涉及, 那么你可能会选择在x和y上创建两个独立的索引, 然后依靠索引组合来处理同时使用两个字段的查询。你也可以在(x, y)上创建一个多字段索引, 它在同时使用两个字段的查询通常比索引组合更高效,但是对那些只包含y的查询几乎没有用,因此它不能是唯一一个索引。一个多字段索引和y上的独立索引可能会更好。因为对那些只涉及x的查询, 可以使用多字段索引,但是它会更大,因此也比只在x上的索引更慢。最后一个选择是创建三个索引, 但是这种方法只有在表的更新远比查询少,并且所有三种查询都很普遍的情况下才是合理的。如果其中一种查询比其它的很多,那么你可能更愿意仅仅创建两种匹配更常见查询的索引。

    备注:设定某个列为主键时,是否默认为其创建索引?
    答:一般创建主键时,大多数数据库会自动创建一个唯一索引,一般来说外键肯定是会被经常做连接查询的字段,所以数据库管理系统会自动加上索引。mysql / oralce / sql server 等都会自动添加。

    展开全文
  • ...铁律一:天下没有免费的午餐,使用索引是需要付出代价的。...索引的优点有目共睹,但是,却很少有人关心过采用...若数据库管理员能够对索引所需要付出的代价有一个充分的认识,也就不会那么随意到处建立索引了...

         本文摘自csdn(http://blog.csdn.net/yanshouke/archive/2008/12/18/3547518.aspx)


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

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

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

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

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

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

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

    数据库索引管理中的第二条铁律就是,对于查询中很少涉及的列或者重复值比较多的列,不要建立索引。

    铁律三:对于按范围查询的列,最好建立索引。

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

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

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

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

    铁律四:表中若有主键或者外键,一定要为其建立索引。

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

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

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

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

    铁律五:对于一些特殊的数据类型,不要建立索引。

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

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

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

    铁律六:索引可以跟Where语句的集合融为一体。

    用户在查询信息的时候,有时会经常会用到一些限制语句。如在查询销售订单的时候,经常会用到客户以及下单日期的条件集合;如在查询某个产品的库存交易情况时,就会利用产品编号与交易日期起止日期的条件集合。

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

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

     

    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,068
精华内容 427
关键字:

数据库建立索引的原则