精华内容
下载资源
问答
  • 数据库建立索引的原则 铁律一:天下没有免费的午餐,使用索引是需要付出代价的 索引的优点有目共睹,但是,却很少有人关心过采用索引所需要付出的成本。若数据库管理员能够对索引所需要付出的代价有一个充分...

    数据库建立索引的原则

     

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

     

      索引的优点有目共睹,但是,却很少有人关心过采用索引所需要付出的成本。若数据库管理员能够对索引所需要付出的代价有一个充分的认识,也就不会那么随意到处建立索引了。仔细数数,其实建立索引的代价还是蛮大的。如创建索引和维护索引都需要花费时间与精力。特别是在数据库设计的时候,数据库管理员为表中的哪些字段需要建立索引,要调研、要协调。如当建有索引的表中的纪录又增加、删除、修改操作时,数据库要对索引进行重新调整。

    虽然这个工作数据库自动会完成,但是,需要消耗服务器的资源。当表中的数据越多,这个消耗的资源也就越多。如索引是数据库中实际存在的对象,所以,每个索引都会占用一定的物理空间。若索引多了,不但会占用大量的物理空间,而且,也会影响到整个数据库的运行性能。可见,数据库管理员若要采用索引来提高系统的性能,自身仍然需要付出不少的代价。数据库管理员现在要考虑的就是如何在这两个之间取得一个均衡。或者说,找到一个回报与投入的临界点。

     

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

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

    铁律三:对于按范围查询的列,最好建立索引在信息化管理系统中,很多时候需要按范围来查询某些交易记录。如在ERP系统中,经常需要查询当月的销售订单与销售出货情况,这就需要按日期范围来查询交易记录。如有时候发现库存不对时,也需要某段时期的库存进出情况,如5月1日到12月3日的库存交易情况等等。此时,也是根据日期来进行查询。对于这些需要在指定范围内快速或者频繁查询的数据列,需要为其建立索引。因为索引已经排序,其保存的时候指定的范围是连续的,查询可以利用索引的排序,加快查询时间,减少用户等待时间。

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

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

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

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

    另外,若要使得某个字段的值唯一,可以通过两种索引方式实现。一种就是上面所讲的主键索引。还有一种就是唯一索引,利用UNIQUE关键字指定字段内容的唯一性。这两种方式都会在表中的指定列上自动创建唯一索引。这两种方式的结果没有明显的区别。

    查询优化器不会区分到底是哪种方式建立的唯一性索引,而且他们进行数据查询的方式也是相同的。若某张表中的数据列定义有外键,则最好也要为这个字段建立索引。因为外键的主要作用就在于表与表之间的连接查询。若在外键上建立索引,可以加速表与表之间的连接查询。如在员工基本信息表中,有一个字段为员工职位。由于员工职位经常在变化,在这里,存储的其实只是一个员工职位的代码。在另外一张职位信息表中详细记录着该职位的相关信息。

    此时,这个员工职位字段就是外键。若在这个字段上建立外键,则可以显著提高两张表的连接速度。而且,记录越多,其效果越加明显。

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

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

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

     

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

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

     

    铁律六:索引可以跟

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

     

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

     

    转载于:https://www.cnblogs.com/nwj-0613/p/4768317.html

    展开全文
  • 说说所理解的吧,首先要明白对于索引的定义...创建了索引的列几乎是立即响应,而不创建索引的列则需要较长时间的等待。在数据库系统中创建索引主要有以下作用:① 加快数据检索;② 保证数据的唯一性;③ 实现表与表...

    说说所理解的吧,首先要明白对于索引的定义描述。MySQL/Oracle中的的索引是对数据库表中一个或多个列的值进行排序的结构。索引有助于更快的获取信息。是用来定位的。

    对表中的列是否创建索引,以及创建何种索引,对于查询的响应速度会有很大差别。创建了索引的列几乎是立即响应,而不创建索引的列则需要较长时间的等待。

    在数据库系统中创建索引主要有以下作用:

    ① 加快数据检索;

    ② 保证数据的唯一性;

    ③ 实现表与表之间的参照完整性;

    ④ 在使用GROUP BY、ORDER BY子句进行查询时,利用索引可以减少排序和分组的时间。

    索引是否必须?

    1、创建索引需要占用数据空间并花费一定的时间

    2、建立索引会减慢数据修改速度

    创建索引要耗一定的系统性能,因此要考察对某列创建索引的必要性。

    选择创建索引的数据列:

    a.定义有主键和外键的列

    b.在指定范围中快速或频繁查询的列

    c.连接中频繁使用的列

    d.需要按排序顺序快速或频繁检索的列

    设计索引时,应考虑以下数据库准则。

    (1)一个表如果建有大量索引,会影响INSERT、UPDATE和DELETE语句的性能。

    (2)避免对经常更新的表进行过多的索引,并且索引应保持较窄。

    (3)使用多个索引可以提高更新少而数据量大的查询的性能。

    (4)对小表进行索引可能不会产生优化效果。

    展开全文
  • 索引可以提高数据的访问速度,但同时也增加了插入、更新和删除操作的处理时间。...创建索引的建议 1、primary key 字段, 系统自动创建主键的索引;  unique kye 字段,系统自动创建对应的索引; for...

    索引可以提高数据的访问速度,但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引、索引建立在那些字段上,是创建索引前必须要考虑的问题。解决此问题就是分析应用程序的业务处理、数据使用,为经常被用作查询条件、或者被要求排序的字段建立索引。

    创建索引的建议

    1、primary key 字段, 系统自动创建主键的索引;

     unique kye 字段,系统自动创建对应的索引;

    foreign key 约束所定义的作为外键的字段应该有索引

    作为Primary Key的域/域组不能为null。而Unique Key可以.Primary Key一般在逻辑设计中用作记录标识,这也是设置
    Primary Key的本来用意, 而Unique Key只是为了保证域/域组的唯一性。

    2、数据量超过300的表应该有索引

    3、经常与其他表进行连接的表,在连接字段上应该建立索引

    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引,可以加快搜索的速度

    5、经常出现在关键字order by、group by、distinct后面的字段,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间

    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    6、对于那些定义为text, image和bit数据类型的列不应该增加索引,对于大的文本字段甚至超长字段,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引,使用短索引

    7、复合索引是指多字段联合索引,查询时经常需要这几个字段组合一起为条件再查询 

    A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
    C、索引不会包含有NULL值的列。复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。;
    D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
    E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

    8、频繁进行数据操作的表,不要建立太多的索引;这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
    9、删除无用的索引,避免对执行计划造成负面影响;

    10、一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    11. 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的

    建索引的几大原则 

    1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。 

    2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式 

    3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,增加索引,并不能明显加 快检索速度。使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 

    4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 

    5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

    6. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    7. 避免在取值朝一个方向增长的字段(例如:日期类型的字段)上,建立索引;对复合索引,避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长,新记录总是存放在索引的最后一个叶页中,从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外,如果所建索引是聚集索引,表中数据按照索引的排列顺序存放,所有的插入操作都集中在最后一个数据页上进行,从而引起插入“热点”。

    8. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用。因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

    9. 删除不再使用,或者很少被使用的索引。表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

     

    展开全文
  • 数据库创建索引的一些原则

    万次阅读 2009-03-09 21:18:00
    一,经常被用来过滤记录的字段。1. primary key 字段, 系统自动创建主键的索引;2. unique kye 字段,系统自动创建对应的索引;3. foreign key 约束所定义的作为外键的字段;4. 在查询中用来连接表的字段;... 索引的

    一,经常被用来过滤记录的字段。

    1. primary key 字段, 系统自动创建主键的索引;

    2. unique kye 字段,系统自动创建对应的索引;

    3. foreign key 约束所定义的作为外键的字段;

    4. 在查询中用来连接表的字段;

    5. 经常用来作为排序(order by 的字段)基准的字段;


    二. 索引会占用磁盘空间,创建不必要的索引只会形成浪费.

    三. 索引的创建必须考虑数据的操作方式。

    1.内容很少变动,经常被查询,为它多创建几个索引無所谓;

    2.经常性,例行性变动的表而言,则需要谨慎地创建确实必要的索引;


    四. primary key 和 unique key的区别
    1. 作为Primary Key的域/域组不能为null。而Unique Key可以。
    2. 在一个表中只能有一个Primary Key,而多个Unique Key可以同时存在。
    更大的区别在逻辑设计上, Primary Key一般在逻辑设计中用作记录标识,这也是设置
    Primary Key的本来用意, 而Unique Key只是为了保证域/域组的唯一性。


    五,复合索引和单一索引

      复合索引是指多字段联合索引,查询时经常需要这几个字段组合一起为条件再查询
      唯一索引主要是用主键ID索引,存储结构顺序与物理结构一致

    如:create  index  idx  on  tbl(a,b) 
     先按a排序, a相同的按b排序,所以当你查a或ab的时候,
    能够利用到这个索引.但当你只查b的时候,索引对你的帮助不大.可能可以跳跃查找.

    展开全文
  • SQL中索引的创建原则
  • 一,经常被用来过滤...1. primary key 字段, 系统自动创建主键的索引; 2. unique kye 字段,系统自动创建对应的索引; 3. foreign key 约束所定义作为外键字段; 4. 在查询中用来连接表字段; 5. 经常...
  • 大多数情况下,业务SQL十分复杂,没法优化。所以就要建立索引了。...对经常用于查询的字段创建索引,避免添加不必要的索引 数据量少的表尽量不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间...
  • 是否要为表增加索引、索引建立在那些字段上,是创建索引前必须要考虑问题。解决此问题一个比较好方法,就是分析应用程序业务处理、数据使用,为经常被用作查询条件、或者被要求排序字段建
  • 创建索引和维护索引都需要花费时间与精力。特别是在数据库设计的时候,数据库管理员为表中的哪些字段需要建立索引,要调研、要协调。如当建有索引的表中的记录又增加、删除、修改操作时,数据库要对索引进行重新...
  • 铁律一:天下没有免费的午餐,使用索引是需要付出...如创建索引和维护索引都需要花费时间与精力。特别是在数据库设计的时候,数据库管理员为表中的哪些字段需要建立索引,要调研、要协调。如当建有索引的表中的纪...
  • 数据库索引创建原则

    2018-04-11 21:22:17
    所以创建索引最好是在where条件内作为查询条件。当然也要注意一些细节:1、比如复合索引前缀原则,当我们创建A、B、C三个复合索引时,当我们使用AB或者A或者ABC时,索引会被用到,但是使用BC、AC或B或C时就用不到...
  • 下面作者就为数据库创建索引的一些原则和方法和大家做一些分享。一. 索引是建立在数据库表中的某些列上面的。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。具有下面几点...
  • 为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。1.选择唯一性索引唯一性索引的值是唯一的,可以更快速的通过该索引来确定某...
  • 1.索引的创建与使用 2.创建索引的原则 3.索引的分类 4.创建索引的多种方法 5.管理索引 6.索引优化 7.查看、修改索引属性 8.修改索引名 9.删除索引
  • 3)查询较频繁字段创建索引(对于查询涉及较少、重复值比较多列不建立索引) 4)最左前缀匹配原则 5)更新频繁字段不适合创建索引 6)区分度低列不适合做索引列(如性别,男、女、未知,最多也就三种) 8)...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 963
精华内容 385
热门标签
关键字:

数据库创建索引的原则