铁律一:天下没有免费的午餐,使用索引是需要付出代价的。
索引的优点有目共睹,但是,却很少有人关心过采用索引所需要付出的成本。若数据库管理员能够对索引所需要付出的代价有一个充分的认识,也就不会那么随意到处建立索引了。
仔细数数,其实建立索引的代价还是蛮大的。如创建索引和维护索引都需要花费时间与精力。特别是在数据库设计的时候,数据库管理员为表中的哪些字段需要建立索引,要调研、要协调。如当建有索引的表中的纪录又增加、删除、修改操作时,数据库要对索引进行重新调整。虽然这个工作数据库自动会完成,但是,需要消耗服务器的资源。当表中的数据越多,这个消耗的资源也就越多。如索引是数据库中实际存在的对象,所以,每个索引都会占用一定的物理空间。若索引多了,不但会占用大量的物理空间,而且,也会影响到整个数据库的运行性能。
可见,数据库管理员若要采用索引来提高系统的性能,自身仍然需要付出不少的代价。数据库管理员现在要考虑的就是如何在这两个之间取得一个均衡。或者说,找到一个回报与投入的临界点。
铁律二:对于查询中很少涉及的列或者重复值比较多的列,不要建立索引。
在查询的时候,如果我们不按某个字段去查询,则在这个字段上建立索引也是浪费。如现在有一张员工信息表,我们可能按员工编号、员工姓名、或者出身地去查询员工信息。但是,我们往往不会按照身份证号码去查询。虽然这个身份证号码是唯一的。此时,即使在这个字段上建立索引,也不能够提高查询的速度。相反,增加了系统维护时间和占用了系统空间。这简直就是搬起石头砸自己的脚呀。
另外,如上面的员工信息表,有些字段重复值比较多。如性别字段主要就是“男”、“女”;职位字段中也是有限的几个内容。此时,在这些字段上添加索引也不会显著的增加查询速度,减少用户响应时间。相反,因为需要占用空间,反而会降低数据库的整体性能。
数据库索引管理中的第二条铁律就是,对于查询中很少涉及的列或者重复值比较多的列,不要建立索引。
铁律三:对于按范围查询的列,最好建立索引。
在信息化管理系统中,很多时候需要按范围来查询某些交易记录。如在ERP系统中,经常需要查询当月的销售订单与销售出货情况,这就需要按日期范围来查询交易记录。如有时候发现库存不对时,也需要某段时期的库存进出情况,如5月1日到12月3日的库存交易情况等等。此时,也是根据日期来进行查询。
对于这些需要在指定范围内快速或者频繁查询的数据列,需要为其建立索引。因为索引已经排序,其保存的时候指定的范围是连续的,查询可以利用索引的排序,加快查询时间,减少用户等待时间。
不过,若虽然可能需要按范围来进行查询,但是,若这个范围查询条件利用的不多的情况下,最好不好采用索引。如在员工信息表中,可能需要查询2008年3月份以前入职的员工明细,要为他们增加福利。但是,由于表中记录不多,而且,也很少进行类似的查询。若维这个字段建立索引,虽然无伤大雅,但是很明显,索引所获得的收益要低于其成本支出。对数据库管理员来说,是得不偿失的。
再者,若采用范围查询的话,最好能利用TOP关键字来限制一次查询的结果。如第一次按顺序只显示前面的500条记录等等。把TOP关键字跟范围一起使用,可以大大的提高查询的效率。
铁律四:表中若有主键或者外键,一定要为其建立索引。
定义有主键的索引列,一定要为其建立索引。因为主键可以加速定位到表中的某一行。结合索引的作用,可以使得查询的速度加倍。如在员工信息表中,我们往往把员工编号设置为主键。因为这不但可以提高查询的速度,而且因为主键要求记录的唯一,还可以保证员工编号的唯一性。此时,若再把这个员工编号字段设置为索引,则通过员工编号来查询员工信息,其效率要比没有建立索引高出许多。
另外,若要使得某个字段的值唯一,可以通过两种索引方式实现。一种就是上面所讲的主键索引。还有一种就是唯一索引,利用UNIQUE关键字指定字段内容的唯一性。这两种方式都会在表中的指定列上自动创建唯一索引。这两种方式的结果没有明显的区别。查询优化器不会区分到底是哪种方式建立的唯一性索引,而且他们进行数据查询的方式也是相同的。
若某张表中的数据列定义有外键,则最好也要为这个字段建立索引。因为外键的主要作用就在于表与表之间的连接查询。若在外键上建立索引,可以加速表与表之间的连接查询。如在员工基本信息表中,有一个字段为员工职位。由于员工职位经常在变化,在这里,存储的其实只是一个员工职位的代码。在另外一张职位信息表中详细记录着该职位的相关信息。此时,这个员工职位字段就是外键。若在这个字段上建立外键,则可以显著提高两张表的连接速度。而且,记录越多,其效果越加明显。
所以,当表中有外键或者主键的时候,就最好为其建立索引。通过索引,可以强化主键与外键的作用,提高数据库的性能。
铁律五:对于一些特殊的数据类型,不要建立索引。
在表中,有些字段比较特殊。如文本字段(TXT)、图像类型字段(IMAGE)等等。如果表中的字段属于这些数据类型,则最好不要为其建立索引。因为这些字段有一些共同的特点。如长度不确定,要么很长,几个字符;要么就是空字符串。如文本数据类型常在应用系统的数据库表中用来做备注的数据类型。有时候备注很长,但有时候又没有数据。若这种类型的字段上建立索引,那根本起不了作用。相反,还增加了系统的负担。
所以,在一些比较特殊的数据类型上,建立索引要谨慎。在通常情况下,没有必要为其建立索引。但是,也有特殊的情况。如有时候,在ERP系统中,有产品信息这个表,其中有个产品规格这个字段。有时候,其长度可能长达5000个字符。此时,只有文本型的数据类型可以容纳这么大的数据量。而且,在查询的时候,用户又喜欢通过规格这个参数来查询产品信息。此时,若不为这个字段建立索引的话,则查询的速度会很慢。遇到这种情况时,数据库管理员只有牺牲一点系统资源,为其建立索引。
从这里也可以看出,虽然以上几条说的时铁律,但是,是否需要遵循,还是需要数据库管理员根据企业的实际情况,做出合理的选择。
铁律六:索引可以跟Where语句的集合融为一体。
用户在查询信息的时候,有时会经常会用到一些限制语句。如在查询销售订单的时候,经常会用到客户以及下单日期的条件集合;如在查询某个产品的库存交易情况时,就会利用产品编号与交易日期起止日期的条件集合。
对于这些经常用在Where子句中的数据列,将索引建立在Where子句的集合过程中,对于需要加速或者频繁检索的数据列,可以让这些经常参与查询的数据列按照索引的排序进行查询,以加快查询的时间。
总之,索引就好像一把双刃剑,即可以提高数据库的性能,也可能对数据库的性能起到反面作用。作为数据库管理员,要有这个能力判断在合适的时间、合适的业务、合适的字段上建立合适的索引。以上六个铁律,只是对建立索引的一些基本要求
分享到:
相关推荐
在创建索引的时候,一般遵循以下的一些经验性原则: l 在经常需要搜索的列上建立索引。 l 在主关键字上建立索引。 l 在经常用于连接的列上建立索引,即在外键上建立索引。 l 在经常需要根据范围进行搜索的列上创建...
索引的设计原则5. 创建索引5.1. 自动创建索引5.2. 手动创建索引5.2.1. 创建表时创建索引5.2.2. 在已经存在的表上创建索引5.2.2.1. alter语句创建索引5.2.2.2. create语句创建索引6. 查看索引6.1. explain关键字查看...
6 2.2设置约束 6 2.2.1设置主键约束 6 2.2.2设置外键约束 6 2.2.3设置check约束 6 2.2.4设置默认值 6 2.2.5设置规则 6 2.3建立索引 6 2.4创建视图 6 2.5查询 7 1数据库设计 数据库在一个信息管理系统中占有非常重要...
6.1建立索引 40 6.2建立触发器 40 六、数据库结构的实现 42 6.1数据定义 42 6.1.1创建表格SQL语句 42 6.1.2创建视图SQL语句 45 6.1.3创建索引SQL语句 47 6.3数据查询 47 6.4数据更新 49 6.4.1数据添加 49 6.4.2数据...
c) 编写数据库建数据库、建数据库对象、初始化数据脚本文件 4.3 设计原则 a) 采用多数据文件 b) 禁止使用过大的数据文件,unix系统不大于2GB,window系统不超过500MB c) oracle数据库中必须将索引建立在索引表空间里...
建立索引时设置较小的填充因子,以便在各数 据页中留下较多的自由空间,减少页分割及重新组织的工作。从而提高数据库运行效率 和执行性能。 此外,考虑利用数据库提供的簇表机制、历史数据分离机制、逻辑存储分开...
3.1 设计表间关系:列出父表与子表的关联索引,指出要建立的表间关系的类型。 3.2 完整性设计:列出主要字段完整性的字段名、完整性约束条件;列出记录完整性约束及其约束条件;列出参照完整性表。
如果开发过程中需要建立索引,需要提交书面的更改请求,说明所需索引 的定义(名称、字段列表、顺序、索引类型)以及建立的理由。数据库管理员 统一维护索引并将提交的请求更改。 6. 数据库各表的初始数据(包含各代码...
例如,不要在属性取值较少的列上建立索引。这一条原则背后所隐含的道理其实就是索引的一个技术指标所揭示的。例子中其实反映的就是索引的选择性。实际上,索引的技术指标是为我们设计、评价索引提供了量化的方式,其...
化学分析的要求统一保存在“试验要求数据表”,该表包含指向特定化学分析方法的索引。 2.3.2 机械性能、试验要求及其表达: 机械性能保存在“机械性能汇总表”; 每种试验的试验要求保存在各自的“试验要求数据表” ...
在为表中的主键建立索引以后,系统会自动地在主键上创建聚集索引 。除此之外,我们还可以为一些经常查询的字段上,创建非聚集索引。 索引创建以后会提高查询效率,但也会引起一些问题,如降低数据更新的速度、索引 ...
本文实例总结了MySQL数据库优化技术的索引用法。分享给大家供大家参考,具体如下: 这里紧接上一篇《MySQL数据库优化技术之配置技巧总结》,进一步分析索引优化的技巧: (七)表的优化 ...(八)建立索引原则
数据库查询优化原则 数据库查询优化原则 步骤/方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
优化数据库的方法 1、关键字段建立索引。 2、使用存储过程,它使SQL变得更加灵活和高效。 3、备份数据库和清除垃圾数据。 4、SQL语句语法的优化。(可以用Sybase的SQL Expert,可惜我没找到unexpired的序列号) 5、...
数据库性能调优是知识密集型的学科,需要综合考虑各种复杂的因素:数据库缓冲区的大小、索引的创建、语句改写等等。总之,数据库性能调优的目的在于使系统运行得更快。 调优需要有广泛的知识,这使得它既简单又复杂...
在使用组合索引时,若组合索引为(name,email),单独索引email时不走索引,这称为最左前缀匹配原则,最左匹配原则中,mysql会一直向右匹配知道遇到(< > between like)这一类的范围查询时停止 explain + sql查询语句,...
1、关键字段建立索引。 2、使用存储过程,它使SQL变得更加灵活和高效。 3、备份数据库和清除垃圾数据。 4、SQL语句语法的优化。(可以用Sybase的SQL Expert,可惜我没找到unexpired的序列号) 5、...
充分利用临时数据表,及建立合理的索引、调整优化SQL语句,等可以减少客户访问数据库的次数,减小CPU的占用时间,提高内存的利用率,减小系统响应时间,缩短用户等待时间等都有很重要的意义。 3)调整内存分配。内存...
【强制】所有的MySQL数据库除历史原因外,都必须采用UTF8编码 【建议】一个表的某列与另一表有关联关系的时候, 请在应用程序维护外键关系,如果在数据库建立外键约束请遵循以下几点: 尽量少使用外键,在高并发下...
1.2 合理选择数据库建模工具 当前有很多业务流程比较复杂,很多管理人员都喜欢在数据库建立后追求一些利润上 的报酬,工作人员的自身业务是根据数据库发展情况来定制的。大部分企业在建立数据 库之前必须要建立...