精华内容
下载资源
问答
  • 数据库建立索引怎么利用索引查询

    千次阅读 2019-03-05 11:32:04
    数据库建立索引怎么利用索引查询?精选 1.合理使用索引 索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。 索引的使用要恰到好处,其...

    数据库建立索引怎么利用索引查询? 精选

    1.合理使用索引
    索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。
    索引的使用要恰到好处,其使用原则如下:
    在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
    在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
    在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
    如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。
    使用系统工具。如Informix数据库有一个tbcheck工具,可以在可疑的索引上进行检查。在一些数据库服务器上,索引可能失效或者因为频繁操作而 使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,可以试着用tbcheck工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量 数据后,删除并重建索引可以提高查询速度。
    (1)在下面两条select语句中:
    SELECT * FROM table1 WHERE field1<=10000 AND field1>=0;
    SELECT * FROM table1 WHERE field1>=0 AND field1<=10000;
    如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多,因为第二条select语句的第一个条件耗费了大量的系统资源。
    第一个原则:在where子句中应把最具限制性的条件放在最前面。
    (2)在下面的select语句中:
    SELECT * FROM tab WHERE a=… AND b=… AND c=…;
    若有索引index(a,b,c),则where子句中字段的顺序应和索引中字段顺序一致。
    第二个原则:where子句中字段的顺序应和索引中字段顺序一致。
    ——————————————————————————
    以下假设在field1上有唯一索引I1,在field2上有非唯一索引I2。
    ——————————————————————————
    (3) SELECT field3,field4 FROM tb WHERE field1='sdf' 快
    SELECT * FROM tb WHERE field1='sdf' 慢[/cci]
    因为后者在索引扫描后要多一步ROWID表访问。
    (4) SELECT field3,field4 FROM tb WHERE field1>='sdf' 快
    SELECT field3,field4 FROM tb WHERE field1>'sdf' 慢
    因为前者可以迅速定位索引。
    (5) SELECT field3,field4 FROM tb WHERE field2 LIKE 'R%' 快
    SELECT field3,field4 FROM tb WHERE field2 LIKE '%R' 慢,
    因为后者不使用索引。
    (6) 使用函数如:
    SELECT field3,field4 FROM tb WHERE upper(field2)='RMN'不使用索引。
    如果一个表有两万条记录,建议不使用函数;如果一个表有五万条以上记录,严格禁止使用函数!两万条记录以下没有限制。
    (7) 空值不在索引中存储,所以
    SELECT field3,field4 FROM tb WHERE field2 IS[NOT] NULL不使用索引。
    (8) 不等式如
    SELECT field3,field4 FROM tb WHERE field2!='TOM'不使用索引。
    相似地,
    SELECT field3,field4 FROM tb WHERE field2 NOT IN('M','P')不使用索引。
    (9) 多列索引,只有当查询中索引首列被用于条件时,索引才能被使用。
    (10) MAX,MIN等函数,使用索引。
    SELECT max(field2) FROM tb 所以,如果需要对字段取max,min,sum等,应该加索引。
    一次只使用一个聚集函数,如:
    SELECT “min”=min(field1), “max”=max(field1) FROM tb
    不如:SELECT “min”=(SELECT min(field1) FROM tb) , “max”=(SELECT max(field1) FROM tb)
    (11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引,修改该字段值时还要修改索引,所以更新该字段的操作比没有索引更慢。
    (12) 索引值过大(如在一个char(40)的字段上建索引),会造成大量的I/O开销(甚至会超过表扫描的I/O开销)。因此,尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。
    (13) 对于多列索引,ORDER BY的顺序必须和索引的字段顺序一致。
    (14) 在sybase中,如果ORDER BY的字段组成一个簇索引,那么无须做ORDER BY。记录的排列顺序是与簇索引一致的。
    (15) 多表联结(具体查询方案需要通过测试得到)
    where子句中限定条件尽量使用相关联的字段,且尽量把相关联的字段放在前面。
    SELECT a.field1,b.field2 FROM a,b WHERE a.field3=b.field3
    field3上没有索引的情况下:
    对a作全表扫描,结果排序
    对b作全表扫描,结果排序
    结果合并。
    对于很小的表或巨大的表比较合适。
    field3上有索引
    按照表联结的次序,b为驱动表,a为被驱动表
    对b作全表扫描
    对a作索引范围扫描
    如果匹配,通过a的rowid访问
    (16) 避免一对多的join。如:
    SELECT tb1.field3,tb1.field4,tb2.field2 FROM tb1,tb2 WHERE tb1.field2=tb2.field2 AND tb1.field2=‘BU1032’ AND tb2.field2= ‘aaa’
    不如:
    declare @a varchar(80)
    SELECT @a=field2 FROM tb2 WHERE field2=‘aaa’
    SELECT tb1.field3,tb1.field4,@a FROM tb1 WHERE field2= ‘aaa’
    (16) 子查询
    用exists/not exists代替in/not in操作
    比较:
    SELECT a.field1 FROM a WHERE a.field2 IN(SELECT b.field1 FROM b WHERE b.field2=100)
    SELECT a.field1 FROM a WHERE EXISTS( SELECT 1 FROM b WHERE a.field2=b.field1 AND b.field2=100)
    SELECT field1 FROM a WHERE field1 NOT IN( SELECT field2 FROM b)
    SELECT field1 FROM a WHERE NOT EXISTS( SELECT 1 FROM b WHERE b.field2=a.field1)
    (17) 主、外键主要用于数据约束,sybase中创建主键时会自动创建索引,外键与索引无关,提高性能必须再建索引。
    (18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。
    (19) 使用count(*)而不要使用count(column_name),避免使用count(DISTINCT column_name)。
    (20) 等号右边尽量不要使用字段名,如:
    SELECT * FROM tb WHERE field1 = field3
    (21) 避免使用or条件,因为or不使用索引。
    2.避免使用order by和group by字句。
    因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用,可用视图、人工生成临时表的方法来代替。
    如果必须使用,先检查memory、tempdb的大小。
    测试证明,特别要避免一个查询里既使用join又使用group by,速度会非常慢!
    3.尽量少用子查询,特别是相关子查询。因为这样会导致效率下降。
    一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
    4.消除对大型表行数据的顺序存取
    在 嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。
    比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询 10亿行数据。
    避免这种情况的主要方法就是对连接的列进行索引。
    例如,两个表:学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个 表要做连接,就要在“学号”这个连接字段上建立索引。
    还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的where子句强迫优化器使用顺序存取。
    下面的查询将强迫对orders表执行顺序操作:
    SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
    虽然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
    SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
    UNION
    SELECT * FROM orders WHERE order_num=1008
    这样就能利用索引路径处理查询。
    5.避免困难的正规表达式
    MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
    即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode >“98000”,在执行查询时就会利用索引来查询,显然会大大提高速度。
    另外,还要避免非开始的子串。例如语句:SELECT * FROM customer WHERE zipcode[2,3] >“80”,在where子句中采用了非开始子串,因而这个语句也不会使用索引。
    6.使用临时表加速查询
    把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。例如:
    SELECT cust.name,rcvbles.balance,……other COLUMNS
    FROM cust,rcvbles
    WHERE cust.customer_id = rcvlbes.customer_id
    AND rcvblls.balance>0
    AND cust.postcode>“98000”
    ORDER BY cust.name
    如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个临时文件中,并按客户的名字进行排序:
    SELECT cust.name,rcvbles.balance,……other COLUMNS
    FROM cust,rcvbles
    WHERE cust.customer_id = rcvlbes.customer_id
    AND rcvblls.balance>;0
    ORDER BY cust.name
    INTO TEMP cust_with_balance
    然后以下面的方式在临时表中查询:
    SELECT * FROM cust_with_balance
    WHERE postcode>“98000”
    临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
    注意:临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下,注意不要丢失数据。
    7.用排序来取代非顺序存取
    非顺序磁盘存取是最慢的操作,表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况,使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。

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

    使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构,例如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子句的集合过程中,对于需要加速或者频繁检索的数据列,可以让这些经常参与查询的数据列按照索引的排序进行查询,以加快查询的时间。

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


    展开全文
  • 数据库建立索引为什么会加快查询速度 首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就...

    数据库建立索引为什么会加快查询速度
    首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的行数,大大减少遍历匹配的行数,所以能明显增加查询的速度。
    MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构。

    我们知道,数据库查询是数据库最主要的功能之一,我们都希望查询数据的速度尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找,当然这种时间复杂度为O(n)的算法在数据量很大时显然是糟糕的,于是有了二分查找、二叉树查找等。但是二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树,但是数据本身的组织结构不可能完全满足各种数据结构。所以,在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

    B-Tree和B+Tree

    目前大部分数据库系统及文件系统都采用B-Tree和B+Tree作为索引结构。

    索引
    索引的目的:提高查询效率
    原理:通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
    数据结构:B+树
    图解B+树与查找过程:
    这里写图片描述
    如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

    b+树的查找过程
    如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

    b+树性质
    通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

    添加索引的话,首先去索引列表中查询,而我们的索引列表是B类树的数据结构,查询的时间复杂度为O(log2N),定位到特定值得行就会非常快,所以其查询速度就会非常快。

       

    添加索引的话,首先去索引列表中查询,而我们的索引列表是B类树的数据结构,查询的时间复杂度为O(log2N),定位到特定值得行就会非常快,所以其查询速度就会非常快。

    为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引?

    1) B+-tree的磁盘读写代价更低

    B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

    举个例子,假设磁盘中的一个盘块容纳16bytes,而一个关键字2bytes,一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候,B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。
     

    2) B+-tree的查询效率更加稳定

    由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    读者点评
    fanyy1991(csdn用户名)道:个人觉得这两个原因都不是主要原因。数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题,B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作(或者说效率太低)。

    上述那个问题转载自:从B树、B+树、B*树谈到R 树

    那么在任何时候都应该加索引么?这里有几个反例:1、如果每次都需要取到所有表记录,无论如何都必须进行全表扫描了,那么是否加索引也没有意义了。2、对非唯一的字段,例如“性别”这种大量重复值的字段,增加索引也没有什么意义。3、对于记录比较少的表,增加索引不会带来速度的优化反而浪费了存储空间,因为索引是需要存储空间的,而且有个致命缺点是对于update/insert/delete的每次执行,字段的索引都必须重新计算更新。

    那么在什么时候适合加上索引呢?我们看一个Mysql手册中举的例子,这里有一条sql语句:

    SELECT c.companyID, c.companyName FROM Companies c, User u WHERE c.companyID = u.fk_companyID AND c.numEmployees >= 0 AND c.companyName LIKE '%i%' AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = 'Executive')

    这条语句涉及3个表的联接,并且包括了许多搜索条件比如大小比较,Like匹配等。在没有索引的情况下Mysql需要执行的扫描行数是 77721876行。而我们通过在companyID和groupLabel两个字段上加上索引之后,扫描的行数只需要134行。在Mysql中可以通过 Explain Select来查看扫描次数。可以看出来在这种联表和复杂搜索条件的情况下,索引带来的性能提升远比它所占据的磁盘空间要重要得多。

    那么索引是如何实现的呢?大多数DB厂商实现索引都是基于一种数据结构——B树。oracle实现索引的数据结构是B*树。具体关于B树、B+树、B*树的讲解可以查看另一篇博文:树

    可以看到在这棵B树搜索英文字母复杂度只为o(m),在数据量比较大的情况下,这样的结构可以大大增加查询速度。然而有另外一种数据结构查询的虚度比B树更快——散列表。Hash表的定义是这样的:设所有可能出现的关键字集合为u,实际发生存储的关键字记为k,而|k|比|u|小很多。散列方法是通过散列函数h将u映射到表T[0,m-1]的下标上,这样u中的关键字为变量,以h为函数运算结果即为相应结点的存储地址。从而达到可以在o(1)的时间内完成查找。
    然而散列表有一个缺陷,那就是散列冲突,即两个关键字通过散列函数计算出了相同的结果。设m和n分别表示散列表的长度和填满的结点数,n/m为散列表的填装因子,因子越大,表示散列冲突的机会越大。
    因为有这样的缺陷,所以数据库不会使用散列表来做为索引的默认实现,Mysql宣称会根据执行查询格式尝试将基于磁盘的B树索引转变为和合适的散列索引以追求进一步提高搜索速度。我想其它数据库厂商也会有类似的策略,毕竟在数据库战场上,搜索速度和管理安全一样是非常重要的竞争点。

     

    基本概念介绍:

    索引

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

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

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

    注意;并非所有的数据库都以相同的方式使用索引。有关更多信息,请参见数据库服务器注意事项,或者查阅数据库文档。

    作为通用规则,只有当经常查询索引列中的数据时,才需要在表上创建索引。索引占用磁盘空间,并且降低添加、删除和更新行的速度。在多数情况下,索引用于数据检索的速度优势大大超过它的。

    索引列

    可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。

    如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。

    确定索引的有效性:

    检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。
    对新索引进行试验以检查它对运行查询性能的影响。
    考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
    检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
    检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型。
    索引类型

    根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息,请参见数据库文档。

    提示:尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一约束。

    唯一索引
    唯一索引是不允许其中任何两行具有相同索引值的索引。

    当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在 employee 表中职员的姓 (lname) 上创建了唯一索引,则任何两个员工都不能同姓。

    主键索引

    数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。

    在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。

    聚集索引

    在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。

    如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

    建立方式和注意事项

    最普通的情况,是为出现在where子句的字段建一个索引。为方便讲述,我们先建立一个如下的表。

    CREATE TABLE mytable (

    id serial primary key,

    category_id int not null default 0,

    user_id int not null default 0,

    adddate int not null default 0

    );

    如果你在查询时常用类似以下的语句:

    SELECT * FROM mytable WHERE category_id=1;

    最直接的应对之道,是为category_id建立一个简单的索引:

    CREATE INDEX mytable_categoryid

    ON mytable (category_id);

    OK.如果你有不止一个选择条件呢?例如:

    SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

    你的第一反应可能是,再给user_id建立一个索引。不好,这不是一个最佳的方法。你可以建立多重的索引。

    CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

    注意到我在命名时的习惯了吗?我使用”表名字段1名字段2名”的方式。你很快就会知道我为什么这样做了。

    现在你已经为适当的字段建立了索引,不过,还是有点不放心吧,你可能会问,数据库会真正用到这些索引吗?测试一下就OK,对于大多数的数据库来说,这是很容易的,只要使用EXPLAIN命令:

    EXPLAIN

    SELECT * FROM mytable

    WHERE category_id=1 AND user_id=2;

    This is what Postgres 7.1 returns (exactly as I expected)

    NOTICE: QUERY PLAN:

    Index Scan using mytable_categoryid_userid on

    mytable (cost=0.00..2.02 rows=1 width=16)

    EXPLAIN

    以上是postgres的数据,可以看到该数据库在查询的时候使用了一个索引(一个好开始),而且它使用的是我创建的第二个索引。看到我上面命名的好处了吧,你马上知道它使用适当的索引了。

    接着,来个稍微复杂一点的,如果有个ORDER BY字句呢?不管你信不信,大多数的数据库在使用order by的时候,都将会从索引中受益。

    SELECT * FROM mytable

    WHERE category_id=1 AND user_id=2

    ORDER BY adddate DESC;

    很简单,就象为where字句中的字段建立一个索引一样,也为ORDER BY的字句中的字段建立一个索引:

    CREATE INDEX mytable_categoryid_userid_adddate

    ON mytable (category_id,user_id,adddate);

    注意: “mytable_categoryid_userid_adddate” 将会被截短为

    “mytable_categoryid_userid_addda”

    CREATE

    EXPLAIN SELECT * FROM mytable

    WHERE category_id=1 AND user_id=2

    ORDER BY adddate DESC;

    NOTICE: QUERY PLAN:

    Sort (cost=2.03..2.03 rows=1 width=16)

    -> Index Scan using mytable_categoryid_userid_addda

    on mytable (cost=0.00..2.02 rows=1 width=16)

    EXPLAIN

    看看EXPLAIN的输出,数据库多做了一个我们没有要求的排序,这下知道性能如何受损了吧,看来我们对于数据库的自身运作是有点过于乐观了,那么,给数据库多一点提示吧。

    为了跳过排序这一步,我们并不需要其它另外的索引,只要将查询语句稍微改一下。这里用的是postgres,我们将给该数据库一个额外的提示–在 ORDER BY语句中,加入where语句中的字段。这只是一个技术上的处理,并不是必须的,因为实际上在另外两个字段上,并不会有任何的排序操作,不过如果加入,postgres将会知道哪些是它应该做的。

    EXPLAIN SELECT * FROM mytable

    WHERE category_id=1 AND user_id=2

    ORDER BY category_id DESC,user_id DESC,adddate DESC;

    NOTICE: QUERY PLAN:

    Index Scan Backward using

    mytable_categoryid_userid_addda on mytable

    (cost=0.00..2.02 rows=1 width=16)

    EXPLAIN

    现在使用我们料想的索引了,而且它还挺聪明,知道可以从索引后面开始读,从而避免了任何的排序。

    以上说得细了一点,不过如果你的数据库非常巨大,并且每日的页面请求达上百万算,我想你会获益良多的。不过,如果你要做更为复杂的查询呢,例如将多张表结合起来查询,特别是where限制字句中的字段是来自不止一个表格时,应该怎样处理呢?我通常都尽量避免这种做法,因为这样数据库要将各个表中的东西都结合起来,然后再排除那些不合适的行,搞不好开销会很大。

    如果不能避免,你应该查看每张要结合起来的表,并且使用以上的策略来建立索引,然后再用EXPLAIN命令验证一下是否使用了你料想中的索引。如果是的话,就OK。不是的话,你可能要建立临时的表来将他们结合在一起,并且使用适当的索引。

    要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。

    以上介绍的只是一些十分基本的东西,其实里面的学问也不少,单凭EXPLAIN我们是不能判定该方法是否就是最优化的,每个数据库都有自己的一些优化器,虽然可能还不太完善,但是它们都会在查询时对比过哪种方式较快,在某些情况下,建立索引的话也未必会快,例如索引放在一个不连续的存储空间时,这会增加读磁盘的负担,因此,哪个是最优,应该通过实际的使用环境来检验。

    在刚开始的时候,如果表不大,没有必要作索引,我的意见是在需要的时候才作索引,也可用一些命令来优化表,例如MySQL可用”OPTIMIZE TABLE”。

    综上所述,在如何为数据库建立恰当的索引方面,你应该有一些基本的概念了。

    转载自:http://www.jb51.net/article/27315.htm
    ---------------------
    作者:蹲茅坑逗蛆蛆
    来源:CSDN
    原文:https://blog.csdn.net/m0_38128121/article/details/79663261
    版权声明:本文为博主原创文章,转载请附上博文链接!

    展开全文
  • 数据库建立索引 数据库、数据表建立索引的原则 数据库建立索引的原则 1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where...

    数据库建立索引

    数据库、数据表建立索引的原则
    数据库建立索引的原则 
    1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 
    
    2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。 
    
    3,尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时,复合索引也占磁盘空间。 
    
    4,对于小型的表,建立索引可能会影响性能 
    
    5,应该避免对具有较少值的字段进行索引。 
    
    6,避免选择大型数据类型的列作为索引。 
    
    索引建立的原则 
    
    索引查询是数据库中重要的记录查询方法,要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑,下面给出实际中的一些通用的原则: 
    
    1. 在经常用作过滤器的字段上建立索引; 
    
    2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引; 
    
    3. 在不同值较少的字段上不必要建立索引,如性别字段; 
    
    4. 对于经常存取的列避免建立索引; 
    
    5. 用于联接的列(主健/外健)上建立索引; 
    
    6. 在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定; 
    
    7. 缺省情况下建立的是非簇集索引,但在以下情况下最好考虑簇集索引,如:含有有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描I/0的次数,有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中,也取决于DBA的所设计的数据库结构。 
    ------------------------------------------------------------------------------
    这是你的表结构,有三个字段,分别是id,name,cid 
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `cid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_cid_INX` (`name`,`cid`),
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    索引方面:id是主键,(name,cid)是一个多列索引。
    -----------------------------------------------------------------------------
    下面是你有疑问的两个查询:
    
    EXPLAIN SELECT * FROM student WHERE   cid=1;
    
    EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';
    
    
    你的疑问是:sql查询用到索引的条件是必须要遵守最左前缀原则,为什么上面两个查询还能用到索引?
    ---------------------------------------------------------------------------------------------------------------------------
    
    讲上面问题之前,我先补充一些知识,因为我觉得你对索引理解是狭隘的:
    上述你的两个查询的explain结果中显示用到索引的情况类型是不一样的。,可观察explain结果中的type字段。你的查询中分别是:
    1. type: index 
    2. type: ref
    
    解释:
    index:这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。
    
    所以:对于你的第一条语句:
    EXPLAIN SELECT * FROM student WHERE   cid=1;
    判断条件是cid=1,而cid是(name,cid)复合索引的一部分,没有问题,可以进行index类型的索引扫描方式。explain显示结果使用到了索引,是index类型的方式。
    
    ---------------------------------------------------------------------------------------------------------------------------
    
    ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一 一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要满足特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。
    
    
    有些了解的人可能会问,索引不都是一个有序排列的数据结构么。不过答案说的还不够完善,那只是针对单个索引,而复合索引的情况有些同学可能就不太了解了。
    
    下面就说下复合索引:
    以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的:
    
                                                                          
    
    mysql创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。
    所以:第一个name字段是绝对有序的,而第二字段就是无序的了。所以通常情况下,直接使用第二个cid字段进行条件判断是用不到索引的,当然,可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。
    
    那么什么时候才能用到呢?
    当然是cid字段的索引数据也是有序的情况下才能使用咯,什么时候才是有序的呢?观察可知,当然是在name字段是等值匹配的情况下,cid才是有序的。发现没有,观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。
    这也就是mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)。
    ---------------------------------------------------------------------------------------------------------------------------
    所以对于你的这条sql查询:
    EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';
    没有错,而且复合索引中的两个索引字段都能很好的利用到了!因为语句中最左面的name字段进行了等值匹配,所以cid是有序的,也可以利用到索引了。
    
    你可能会问:我建的索引是(name,cid)。而我查询的语句是cid=1 AND name='小红'; 我是先查询cid,再查询name的,不是先从最左面查的呀?
    
    好吧,我再解释一下这个问题:首先可以肯定的是把条件判断反过来变成这样 name='小红' and cid=1; 最后所查询的结果是一样的。
    那么问题产生了?既然结果是一样的,到底以何种顺序的查询方式最好呢?
    
    所以,而此时那就是我们的mysql查询优化器该登场了,mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。
    ---------------------------------------------------------------------------
    索引创建规则:
     1、表的主键、外键必须有索引;
     2、数据量超过300的表应该有索引; 
     3、经常与其他表进行连接的表,在连接字段上应该建立索引
     4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
     5、索引应该建在选择性高的字段上; 
     6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
     7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替: 
     A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
     B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,
    则可以建立复合索引;否则考虑单字段索引; 
     C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
     D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
     E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
     8、频繁进行数据操作的表,不要建立太多的索引;
     9、删除无用的索引,避免对执行计划造成负面影响; 以上是一些普遍的建立索引时的判断依据。
    一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。
    因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,
    索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,
    一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。 
    
    oracle的索引陷阱
    一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 
    oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。 
    下面是一些常见的索引限制问题。
     
    1、使用不等于操作符(<>, !=)
    下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描 
    select * from dept where staff_num <> 1000; 
    但是开发中的确需要这样的查询,难道没有解决问题的办法了吗? 
    有! 
    通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。 
    select * from dept shere staff_num < 1000 or dept_id > 1000; 
     
    2、使用 is null 或 is not null
    使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。 
    解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)
     
    3、使用函数
    如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引: 
    select * from staff where trunc(birthdate) = '01-MAY-82'; 
    但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。 
    select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 
     
    4、比较不匹配的数据类型
    比较不匹配的数据类型也是难于发现的性能问题之一。
    下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。 
    select * from dept where dept_id = 900198; 
    这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。
    把SQL语句改为如下形式就可以使用索引 
    select * from dept where dept_id = '900198';
     
    
     
    
    二,
    
     各种索引使用场合及建议
    
     
    
    (1)B*Tree索引。
    
    常规索引,多用于oltp系统,快速定位行,应建立于高cardinality列(即列的唯一值除以行数为一个很大的值,存在很少的相同值)。
    
     Create index indexname on tablename(columnname[columnname...])
    
    (2)反向索引。
    
    B*Tree的衍生产物,应用于特殊场合,在ops环境加序列增加的列上建立,不适合做区域扫描。
    
     Create index indexname on tablename(columnname[columnname...]) reverse
    
    (3)降序索引。
    
    B*Tree的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。
    
     Create index indexname on tablename(columnname DESC[columnname...])
    
    (4)位图索引。
    
    位图方式管理的索引,适用于OLAP(在线分析)和DSS(决策处理)系统,应建立于低cardinality列,
    适合集中读取,不适合插入和修改,提供比B*Tree索引更节省的空间。
    
     Create BITMAP index indexname on tablename(columnname[columnname...])
    
    在实际应用中,如果某个字段的值需要频繁更新,那么就不适合在它上面创建位图索引。
    在位图索引中,如果你更新或插入其中一条数值为N的记录,
    那么相应表中数值为N的记录(可能成百上千条)全部被Oracle锁定,
    这就意味着其它用户不能同时更新这些数值为N的记录,其它用户必须要等第一个用户提交后,
    才能获得锁,更新或插入数据,bitmap index它主要用于决策支持系统或静态数据。
    
    (5)函数索引。
    
    B*Tree的衍生产物,应用于查询语句条件列上包含函数的情况,
    索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。
    
    索引创建策略 
    1.导入数据后再创建索引 
    2.不需要为很小的表创建索引 
    3.对于取值范围很小的字段(比如性别字段)应当建立位图索引 
    4.限制表中的索引的数目 
    5.为索引设置合适的PCTFREE值 
    6.存储索引的表空间最好单独设定
    
    唯一索引和不唯一索引都只是针对B树索引而言. 
    Oracle最多允许包含32个字段的复合索引 
    由此估计出一个查询如果使用某个索引会需要读入的数据块块数。
    需要读入的数据块越多,则 cost 越大,Oracle 也就越有可能不选择使用 index
    
     
    
    三,
    
    能用唯一索引,一定用唯一索引 
    能加非空,就加非空约束 
    一定要统计表的信息,索引的信息,柱状图的信息。 
    联合索引的顺序不同,影响索引的选择,尽量将值少的放在前面 
    只有做到以上四点,数据库才会正确的选择执行计划。
    
    ====================================================================================================================================
    
     一、 ROWID的概念
    
      存储了row在数据文件中的具体位置:64位编码的数据,A-Z, a-z, 0-9, +, 和 /,
    
      row在数据块中的存储方式
    
      SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;
    
      比如:OOOOOOFFFBBBBBBRRR
    
      OOOOOO:data object number, 对应dba_objects.data_object_id
    
      FFF:file#, 对应v$datafile.file#
    
      BBBBBB:block#
    
      RRR:row#
    
      Dbms_rowid包
    
      SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;
    
      具体到特定的物理文件
    
      二、 索引的概念
    
      1、 类似书的目录结构
    
      2、 Oracle 的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度
    
      3、 索引直接指向包含所查询值的行的位置,减少磁盘I/O
    
      4、 与所索引的表是相互独立的物理结构
    
      5、 Oracle 自动使用并维护索引,插入、删除、更新表后,自动更新索引
    
      6、 语法:CREATE INDEX index ON table (column[, column]...);
    
      7、 B-tree结构(非bitmap):
    
      [一]了解索引的工作原理:
    
      表:emp
    
      
    
      目标:查询Frank的工资salary
    
      建立索引:create index emp_name_idx on emp(name);
     
    
    
     
    
    
     [试验]测试索引的作用:
      1. 运行/rdbms/admin/utlxplan 脚本
    
      2. 建立测试表
    
      create table t as select * from dba_objects;
    
      insert into t select * from t;
    
      create table indextable
    
      as select rownum id,owner,object_name,subobject_name,
    
      object_id,data_object_id,object_type,created
    
      from t;
    
      3. set autotrace trace explain
    
      4. set timing on
    
      5. 分析表,可以得到cost
    
      6. 查询 object_name=’DBA_INDEXES’
    
      7. 在object_name列上建立索引
    
      8. 再查询
    
    三、 唯一索引
    
      1、 何时创建:当某列任意两行的值都不相同
    
      2、 当建立Primary Key(主键)或者Unique constraint(唯一约束)时,唯一索引将被自动建立
    
      3、 语法:CREATE UNIQUE INDEX index ON table (column);
    
      4、 演示
    
      四、 组合索引
    
      1、 何时创建:当两个或多个列经常一起出现在where条件中时,则在这些列上同时创建组合索引
    
      2、 组合索引中列的顺序是任意的,也无需相邻。但是建议将最频繁访问的列放在列表的最前面
    
      3、 演示(组合列,单独列)
    
      五、 位图索引
    
      1、 何时创建:
    
      列中有非常多的重复的值时候。例如某列保存了 “性别”信息。
    
      Where 条件中包含了很多OR操作符。
    
      较少的update操作,因为要相应的跟新所有的bitmap
    
      2、 结构:位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。
    
      3、 优点:位图以一种压缩格式存放,因此占用的磁盘空间比标准索引要小得多
    
      4、 语法:CREATE BITMAP INDEX index ON table (column[, column]...);
    
      5、 掩饰:
    
      create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');
    
      分析,查找,建立索引,查找
    
      六、 基于函数的索引
    
      1、 何时创建:在WHERE条件语句中包含函数或者表达式时
    
      2、 函数包括:算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。
    
      3、 语法:CREATE INDEX index ON table (FUNCTION(column));
    
      4、 演示
    
      必须要分析表,并且query_rewrite_enabled=TRUE
    
      或者使用提示/*+ INDEX(ic_index)*/
    
     七、 反向键索引
    
      目的:比如索引值是一个自动增长的列:
    
    
      多个用户对集中在少数块上的索引行进行修改,容易引起资源的争用,比如对数据块的等待。此时建立反向索引。
    
      性能问题:
    
      语法:
    
      重建为标准索引:反之不行
    
      八、 键压缩索引
    
      比如表landscp的数据如下:
    
      site feature job
    
      Britten Park, Rose Bed 1, Prune
    
      Britten Park, Rose Bed 1, Mulch
    
      Britten Park, Rose Bed 1,Spray
    
      Britten Park, Shrub Bed 1, Mulch
    
      Britten Park, Shrub Bed 1, Weed
    
      Britten Park, Shrub Bed 1, Hoe
    
      ……
    
      查询时,以上3列均在where条件中同时出现,所以建立基于以上3列的组合索引。但是发现重复值很多,所以考虑压缩特性。
    
      Create index zip_idx
    
      on landscp(site, feature, job)
    
      compress 2;
    
      将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。
    
      Prefix 0: Britten Park, Rose Bed 1
    
      Prefix 1: Britten Park, Shrub Bed 1
    
      实际所以的结构为:
    
      0 Prune
    
      0 Mulch
    
      0 Spray
    
      1 Mulch
    
      1 Weed
    
      1 Hoe
    
      特点:组合索引的前缀部分具有非选择性时,考虑使用压缩。减少I/O,增加性能。
    
      九、 索引组织表(IOT)
    
      将表中的数据按照索引的结构存储在索引中,提高查询速度。
    
      牺牲插入更新的性能,换取查询性能。通常用于数据仓库,提供大量的查询,极少的插入修改工作。
    
      必须指定主键。插入数据时,会根据主键列进行B树索引排序,写入磁盘。
    
      十、 分区索引
    
      簇:
    
      A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
    
    
     
    
    ====================================================================================================================================
    
     
    
    为什么要创建索引呢?这是因为,创建索引可以大大提高系统的性能。 
    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 
    第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 
    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 
    第四,在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 
    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。 
    
    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。这是因为,增加索引也有许多不利的一个方面。 
    
    第一,创建索引和维护索引要耗费时间,这种时间随着数据 量的增加而增加。 
    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。 
    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。 
    
    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列 上创建索引,例如: 
    
    在经常需要搜索的列上,可以加快搜索的速度; 
    在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 
    在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度; 
    在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 
    在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 
    在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。 
    
    
    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点: 
    
    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 
    第二,对于那 些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 
    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 
    第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。 
    
    创建索引的方法和索引的特征 
    创建索引的方法 
    创建索引有多种方法,这些方法包括直接创建索引的方法和间接创建索引的方法。直接创建索引,例如使用CREATE INDEX语句或者使用创建索引向导,间接创建索引,例如在表中定义主键约束或者唯一性键约束时,同时也创建了索引。虽然,这两种方法都可以创建索引,但是,它们创建索引的具体内容是有区别的。 
    使用CREATE INDEX语句或者使用创建索引向导来创建索引,这是最基本的索引创建方式,并且这种方法最具有柔性,可以定制创建出符合自己需要的索引。在使用这种方式创建索引时,可以使用许多选项,例如指定数据页的充满度、进行排序、整理统计信息等,这样可以优化索引。使用这种方法,可以指定索引的类型、唯一性和复合性,也就是说,既可以创建聚簇索引,也可以创建非聚簇索引,既可以在一个列上创建索引,也可以在两个或者两个以上的列上创建索引。 
    
    通过定义主键约束或者唯一性键约束,也可以间接创建索引。主键约束是一种保持数据完整性的逻辑,它限制表中的记录有相同的主键记录。在创建主键约束时,系统自动创建了一个唯一性的聚簇索引。虽然,在逻辑上,主键约束是一种重要的结构,但是,在物理结构上,与主键约束相对应的结构是唯一性的聚簇索引。换句话说,在物理实现上,不存在主键约束,而只存在唯一性的聚簇索引。同样,在创建唯一性键约束时,也同时创建了索引,这种索引则是唯一性的非聚簇索引。因此,当使用约束创建索引时,索引的类型和特征基本上都已经确定了,由用户定制的余地比较小。 
    
    当在表上定义主键或者唯一性键约束时,如果表中已经有了使用CREATE INDEX语句创建的标准索引时,那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说,主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。 
    
    索引的特征 
    索引有两个特征,即唯一性索引和复合索引。 
    唯一性索引保证在索引列中的全部数据是唯一的,不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束,那么当创建表或者修改表时,SQL Server自动创建一个唯一性索引。然而,如果必须保证唯一性,那么应该创建主键约束或者唯一性键约束,而不是创建一个唯一性索引。当创建唯一性索引时,应该认真考虑这些规则:当在表中创建主键约束或者唯一性键约束时,SQL Server自动创建一个唯一性索引;如果表中已经包含有数据,那么当创建索引时,SQL Server检查表中已有数据的冗余性;每当使用插入语句插入数据或者使用修改语句修改数据时,SQL Server检查数据的冗余性:如果有冗余值,那么SQL Server取消该语句的执行,并且返回一个错误消息;确保表中的每一行数据都有一个唯一值,这样可以确保每一个实体都可以唯一确认;只能在可以保证实体完整性的列上创建唯一性索引,例如,不能在人事表中的姓名列上创建唯一性索引,因为人们可以有相同的姓名。 
    
    复合索引就是一个索引创建在两个列或者多个列上。在搜索时,当两个或者多个列作为一个关键值时,最好在这些列上创建复合索引。当创建复合索引时,应该考虑这些规则:最多可以把16个列合并成一个单独的复合索引,构成复合索引的列的总长度不能超过900字节,也就是说复合列的长度不能太长;在复合索引中,所有的列必须来自同一个表中,不能跨表建立复合列;在复合索引中,列的排列顺序是非常重要的,因此要认真排列列的顺序,原则上,应该首先定义最唯一的列,例如在(COL1,COL2)上的索引与在(COL2,COL1)上的索引是不相同的,因为两个索引的列的顺序不同;为了使查询优化器使用复合索引,查询语句中的WHERE子句必须参考复合索引中第一个列;当表中有多个关键列时,复合索引是非常有用的;使用复合索引可以提高查询性能,减少在一个表中所创建的索引数量。
    
    
    ---
    
    索引的类型 
    根据索引的顺序与数据表的物理顺序是否相同,可以把索引分成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引,另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。 
    
    聚簇索引的体系结构 
    索引的结构类似于树状结构,树的顶部称为叶级,树的其它部分称为非叶级,树的根部在非叶级中。同样,在聚簇索引中,聚簇索引的叶级和非叶级构成了一个树状结构,索引的最低级是叶级。在聚簇索引中,表中的数据所在的数据页是叶级,在叶级之上的索引页是非叶级,索引数据所在的索引页是非叶级。在聚簇索引中,数据值的顺序总是按照升序排列。 
    
    应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时,应该考虑这些因素:每一个表只能有一个聚簇索引,因为表中数据的物理顺序只能有一个;表中行的物理顺序和索引中行的物理顺序是相同的,在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护这个顺序;关键值的唯一性要么使用UNIQUE关键字明确维护,要么由一个内部的唯一标识符明确维护,这些唯一性标识符是系统自己使用的,用户不能访问;聚簇索引的平均大小大约是数据表的百分之五,但是,实际的聚簇索引的大小常常根据索引列的大小变化而变化;在索引的创建过程中,SQL Server临时使用当前数据库的磁盘空间,当创建聚簇索引时,需要1.2倍的表空间的大小,因此,一定要保证有足够的空间来创建聚簇索引。 
    
    当系统访问表中的数据时,首先确定在相应的列上是否存在有索引和该索引是否对要检索的数据有意义。如果索引存在并且该索引非常有意义,那么系统使用该索引访问表中的记录。系统从索引开始浏览到数据,索引浏览则从树状索引的根部开始。从根部开始,搜索值与每一个关键值相比较,确定搜索值是否大于或者等于关键值。这一步重复进行,直到碰上一个比搜索值大的关键值,或者该搜索值大于或者等于索引页上所有的关键值为止。 
    
    非聚簇索引的体系结构 
    非聚簇索引的结构也是树状结构,与聚簇索引的结构非常类似,但是也有明显的不同。 
    在非聚簇索引中,叶级仅包含关键值,而没有包含数据行。非聚簇索引表示行的逻辑顺序。 非聚簇索引有两种体系结构:一种体系结构是在没有聚簇索引的表上创建非聚簇索引,另一种体系结构是在有聚簇索引的表上创建非聚簇索引。 
    
    如果一个数据表中没有聚簇索引,那么这个数据表也称为数据堆。当非聚簇索引在数据堆的顶部创建时,系统使用索引页中的行标识符指向数据页中的记录。行标识符存储了数据所在位置的信息。数据堆是通过使用索引分配图(IAM)页来维护的。IAM页包含了数据堆所在簇的存储信息。在系统表sysindexes 中,有一个指针指向了与数据堆相关的第一个IAM页。系统使用IAM页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页和在这些数据页中的记录没有任何的顺序并且也没有链接在一起。在这些数据页之间的唯一的连接是IAM中记录的顺序。当在数据堆上创建了非聚簇索引时,叶级中包含了指向数据页的行标识符。行标识符指定记录行的逻辑顺序,由文件ID、页号和行ID组成。这些行的标识符维持唯一性。非聚簇索引的叶级页的顺序不同于表中数据的物理顺序。这些关键值在叶级中以升序维持。 
    
    当非聚簇索引创建在有聚簇索引的表上的时候,系统使用索引页中的指向聚簇索引的聚簇键。聚簇键存储了数据的位置信息。如果某一个表有聚簇索引,那么非聚簇索引的叶级包含了映射到聚簇键的聚簇键值,而不是映射到物理的行标识符。当系统访问有非聚簇索引的表中数据时,并且这种非聚簇索引创建在聚簇索引上,那么它首先从非聚簇索引来找到指向聚簇索引的指针,然后通过使用聚簇索引来找到数据。 
    当需要以多种方式检索数据时,非聚簇索引是非常有用的。当创建非聚簇索引时,要考虑这些情况:在缺省情况下,所创建的索引是非聚簇索引;在每一个表上面,可以创建不多于249个非聚簇索引,而聚簇索引最多只能有一个。 
    系统如何访问表中的数据 
    一般地,系统访问数据库中的数据,可以使用两种方法:表扫描和索引查找。第一种方法是表扫描,就是指系统将指针放置在该表的表头数据所在的数据页上,然后按照数据页的排列顺序,一页一页地从前向后扫描该表数据所占有的全部数据页,直至扫描完表中的全部记录。在扫描时,如果找到符合查询条件的记录,那么就将这条记录挑选出来。最后,将全部挑选出来符合查询语句条件的记录显示出来。第二种方法是使用索引查找。索引是一种树状结构,其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时,系统沿着索引的树状结构,根据索引中关键字和指针,找到符合查询条件的的记录。最后,将全部查找到的符合查询语句条件的记录显示出来。 
    在SQL Server中,当访问数据库中的数据时,由SQL Server确定该表中是否有索引存在。如果没有索引,那么SQL Server使用表扫描的方法访问数据库中的数据。查询处理器根据分布的统计信息生成该查询语句的优化执行规划,以提高访问数据的效率为目标,确定是使用表扫描还是使用索引。 
    索引的选项 
    在创建索引时,可以指定一些选项,通过使用这些选项,可以优化索引的性能。这些选项包括FILLFACTOR选项、PAD_INDEX选项和SORTED_DATA_REORG选项。 
    使用FILLFACTOR选项,可以优化插入语句和修改语句的性能。当某个索引页变满时,SQL Server必须花费时间分解该页,以便为新的记录行腾出空间。使用FILLFACTOR选项,就是在叶级索引页上分配一定百分比的自由空间,以便减少页的分解时间。当在有数据的表中创建索引时,可以使用FILLFACTOR选项指定每一个叶级索引节点的填充的百分比。缺省值是0,该数值等价于100。在创建索引的时候,内部索引节点总是留有了一定的空间,这个空间足够容纳一个或者两个表中的记录。在没有数据的表中,当创建索引的时候,不要使用该选项,因为这时该选项是没有实际意义的。另外,该选项的数值在创建时指定以后,不能动态地得到维护,因此,只应该在有数据的表中创建索引时才使用。 
    PAD_INDEX 选项将FILLFACTOR选项的数值同样也用于内部的索引节点,使内部的索引节点的填充度与叶级索引的节点中的填充度相同。如果没有指定FILLFACTOR选项,那么单独指定PAD_INDEX选项是没有实际意义的,这是因为PAD_INDEX选项的取值是由FILLFACTOR选项的取值确定的。 
    当创建聚簇索引时,SORTED_DATA_REORG选项清除排序,因此可以减少建立聚簇索引所需要的时间。当在一个已经变成碎块的表上创建或者重建聚簇索引时,使用SORTED_DATA_REORG选项可以压缩数据页。当重新需要在索引上应用填充度时,也使用该选项。当使用 SORTED_DATA_REORG选项时,应该考虑这些因素:SQL Server确认每一个关键值是否比前一个关键值高,如果都不高,那么不能创建索引;SQL Server要求1.2倍的表空间来物理地重新组织数据;使用SORTED_DATA_REORG选项,通过清除排序进程而加快索引创建进程;从表中物理地拷贝数据;当某一个行被删除时,其所占的空间可以重新利用;创建全部非聚簇索引;如果希望把叶级页填充到一定的百分比,可以同时使用 FILLFACTOR选项和SORTED_DATA_REORG选项。 
    索引的维护 
    为了维护系统性能,索引在创建之后,由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块,因此,必须对索引进行维护。 
    使用DBCC SHOWCONTIG语句,可以显示表的数据和索引的碎块信息。当执行DBCC SHOWCONTIG语句时,SQL Server浏览叶级上的整个索引页,来确定表或者指定的索引是否严重碎块。DBCC SHOWCONTIG语句还能确定数据页和索引页是否已经满了。当对表进行大量的修改或者增加大量的数据之后,或者表的查询非常慢时,应该在这些表上执行 DBCC SHOWCONTIG语句。当执行DBCC SHOWCONTIG语句时,应该考虑这些因素:当执行DBCC SHOWCONTIG语句时,SQL Server要求指定表的ID号或者索引的ID号,表的ID号或者索引的ID号可以从系统表sysindexes中得到;应该确定多长时间使用一次 DBCC SHOWCONTIG语句,这个时间长度要根据表的活动情况来定,每天、每周或者每月都可以。 
    使用DBCC DBREINDEX语句重建表的一个或者多个索引。当希望重建索引和当表上有主键约束或者唯一性键约束时,执行DBCC DBREINDEX语句。除此之外,执行DBCC DBREINDEX语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。当使用执行DBCC DBREINDEX语句时,应该考虑这些因素:根据指定的填充度,系统重新填充每一个叶级页;使用DBCC DBREINDEX语句重建主键约束或者唯一性键约束的索引;使用SORTED_DATA_REORG选项可以更快地创建聚簇索引,如果没有排列关键值,那么不能使用DBCC DBREINDEX语句;DBCC DBREINDEX语句不支持系统表。另外,还可以使用数据库维护规划向导自动地进行重建索引的进程。 
    统计信息是存储在SQL Server中的列数据的样本。这些数据一般地用于索引列,但是还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息,并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。当表中数据发生变化时,SQL Server周期性地自动修改统计信息。索引统计被自动地修改,索引中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如,如果表中有10000行数据,1000行数据修改了,那么统计信息可能需要修改。然而,如果只有50行记录修改了,那么仍然保持当前的统计信息。除了系统自动修改之外,用户还可以通过执行UPDATE STATISTICS语句或者sp_updatestats系统存储过程来手工修改统计信息。使用UPDATE STATISTICS语句既可以修改表中的全部索引,也可以修改指定的索引。 
    使用SHOWPLAN和STATISTICS IO语句可以分析索引和查询性能。使用这些语句可以更好地调整查询和索引。SHOWPLAN语句显示在连接表中使用的查询优化器的每一步以及表明使用哪一个索引访问数据。使用SHOWPLAN语句可以查看指定查询的查询规划。当使用SHOWPLAN语句时,应该考虑这些因素。SET SHOWPLAN_ALL语句返回的输出结果比SET SHOWPLAN_TEXT语句返回的输出结果详细。然而,应用程序必须能够处理SET SHOWPLAN_ALL语句返回的输出结果。SHOWPLAN语句生成的信息只能针对一个会话。如果重新连接SQL Server,那么必须重新执行SHOWPLAN语句。STATISTICS IO语句表明输入输出的数量,这些输入输出用来返回结果集和显示指定查询的逻辑的和物理的I/O的信息。可以使用这些信息来确定是否应该重写查询语句或者重新设计索引。使用STATISTICS IO语句可以查看用来处理指定查询的I/O信息。 
    就象SHOWPLAN语句一样,优化器隐藏也用来调整查询性能。优化器隐藏可以对查询性能提供较小的改进,并且如果索引策略发生了改变,那么这种优化器隐藏就毫无用处了。因此,限制使用优化器隐藏,这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏时,考虑这些规则:指定索引名称、当 index_id为0时为使用表扫描、当index_id为1时为使用聚簇索引;优化器隐藏覆盖查询优化器,如果数据或者环境发生了变化,那么必须修改优化器隐藏。
    ------------------------------------------------------------------------------
    mysql建立索引的一些小规则
    1、表的主键、外键必须有索引;
    
    2、数据量超过300的表应该有索引;
    
    3、经常与其他表进行连接的表,在连接字段上应该建立索引;
    
    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
    
    5、索引应该建在选择性高的字段上;
    
    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
    
    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    
           A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
           B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
           C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
           D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
           E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
    
    8、频繁进行数据操作的表,不要建立太多的索引;
    
    9、删除无用的索引,避免对执行计划造成负面影响;
    
    以上是一些普遍的建立索引时的判断依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。
    ------------------------------------------------------------------------

    数据表创建规则

    写在前边:
    规范是一种习惯,在Oracle数据库中笔者认为表中的字段命名规范需要引起注意,当考虑使用SSM作为框架的时候,Mybatis 的逆向工程能够根据数据库表名和数据库名字段名自动生成pojo类,其中pojo类中的属性名称就是根据数据库字段名来进行命名的。 
    当然笔者建议例如用户ID 数据库字段应写为“USER_ID”。 
    这样逆向工程生成的pojo属性 是“userId“” 符合java的驼峰命名法。
    
    数据库字段命名	Mybatis逆向工程pojo属性名
    USER_ID	userId
    user_name	userName
    1、编写目的
      使用统一的命名和编码规范,使数据库命名及编码风格标准化,以便于阅读、理解和继承。
    
    2、适用范围
      本规范适用于公司范围内所有以ORACLE作为后台数据库的应用系统和项目开发工作。
    
    3、对象命名规范
    3.1 数据库和SID
      数据库名定义为系统名+模块名
    
      ★ 全局数据库名和例程SID 名要求一致
    
      ★ 因SID 名只能包含字符和数字,所以全局数据库名和SID 名中不能含有“_”等字符
    
    3.2 表相关
    3.2.1 表空间
      ★ 面向用户的专用数据表空间以用户名+_+data命名 ,如Aud 用户专用数据表空间可命名为Aud_data
    
      ★ 面向用户的专用索引表空间以用户名+_+idx命名
    
      ★ 面向用户的专用临时表空间以用户名+_+tmp命名
    
      ★ 面向用户的专用回滚段表空间以用户名+_+rbs 命名
    
      ★ 面向应用的表空间以应用名+_data/应用名+_idx/应用名+_tmp/应用名+_rbs 命名
    
      ★ LOB 段数据专用表空间以其数据表空间+_+lobs 命名,如上例中数据表空间为Aud_data,则LOB 段表空间可命名为Aud_data_lobs
    
    3.2.2 表空间文件
      表空间文件命名以表空间名+两位数序号(序号从01开始)组成,如Aud_data01 等
    
      3.2.3 表
    
      表命名要遵循以下原则:
    
      ★ 一般表采用“系统名+t_+模块名+_+表义名” 格式构成
    
      ★ 若数据库中只含有单个模块,命名可采用“系统名+t_+表义名”格式构成
    
      ★ 模块名或表义名均以其汉语拼音的首字符命名,表义名中汉语拼音均采用小写,且字符间不加分割符;
    
      ★ 表别名命名规则:取表义名的前3 个字符加最后一个字符。如果存在冲突,适当增加字符(如取表义名的前4 个字符加最后一个字符等)
    
      ★ 临时表采用“系统名+t_tmp_+表义名” 格式构成
    
      ★ 表的命名如
    
      dft_gy_cbap:系统名(电费 df)+t_+模块名(高压 gy)++表义名(抄表安排 cbap) dft_cbbj: 系统名(电费 df)+t+表义名(抄表标记 cbbj) dft_tmp_hj: 系统名(电费 df)+tmp+表义名(合计hj)(此处为临时表) ★ 关联表命名为Re_表A_表B,Re 是Relative的缩写,表A 和表B均采用其表义名或缩写形式。
    
    3.2.4 属性(列或字段)
      属性命名遵循以下原则:
    
      ★ 采用有意义的列名,为实际含义的汉语拼音的首字符,且字符间不加任何分割符
    
      ★ 属性名前不要加表名等作为前缀
    
      ★ 属性后不加任何类型标识作为后缀
    
      ★ 不要使用“ID”作为列名
    
      ★ 关联字段命名以 “cd++关联表的表义名(或缩写)++字段名”进行
    
    3.2.5 主键
      ★ 任何表都必须定义主键
    
      ★ 表主键命名为:“pk++表名(或缩写)++主键标识”如“pk_YHXX_IDKH”等
    
    3.2.6 外键
      表外键命名为: “fk++表名(或缩写)+主表名(或缩写)+_+主键标识”如“fk_YHLX_YHXX_SFZH”等
    
    3.2.7 CHECK约束
      CHECK 约束命名为: “chk+_+CHECK约束的列名(或缩写)”
    
    3.2.8 UNIQUE约束
      UNIQUE 约束命名为: “unq+_+UNIQUE约束的列名(或缩写)”
    
    3.2.9 索引
      索引的命名为:“表名(或缩写)+_+列名+_idx”。其中多单词组成的属性列列名取前几个单词首字符再加末单词首字符组成如yd_kh 表khid 上的index: yd_kh_khid_idx
    
    3.2.10 触发器
      ★ AFTER型触发器
    
      系统名+tr_+<表名>_+ +[_row]
    
      ★ BEFORE型触发器
    
      系统名+tr_+<表名>+bef +[_row]
    
      ★ INSTEAD OF型触发器
    
      系统名+ti_+<表名>+_+ +[_row]
    
      ★ 各种类型的触发器中
    
      i,u,d 分别表示insert、update 和delete行级触发器,后加_row 标识,语句级触发器不加,如 yddftr_CSH_i_row
    
    3.2.11 簇
      簇以簇中要存储的各个表(或表别名)及表间加and的组成 命名,即表“A+And+表B…”,如存储GR(工人)和GRJN(工人技能)表的簇命名为GRAndGRJN
    
    3.3 视图
      视图命名以系统名v_+模块名作为前缀,其他命名规则和表的命名类似
    
    3.4 序列
      序列命名以seq_+含义名组成
    
    3.5 同义词
      同义词命名与其基础对象的名称一致,但要去除其用户前缀或含有远程数据库链接的后缀
    
    3.6 存储对象相关
    3.6.1 存储过程
      存储过程命名由“系统名+sp+_+存储过程标识(缩写)”组成存储过程标识要以实际含义的汉语拼音的首字符构成,并用下划线分割各个组成部分。如增加代理商的帐户的存储过程为“sfsp_ZJDLSZH”。
    
    3.6.2 函数
      函数命名由“系统名+f+_+函数标识”组成
    
    3.6.3 包
      包命名由“系统名+pkg+_+包标识”组成
    
    3.6.4 函数文本中的变量采用下列格式命名:
      ★ 参数变量命名采用“i (o或io)+_+名称”形式,前缀i 或o 表输入还是输出参数
    
      ★ 过程变量命名采用“l+_+名称”形式
    
      ★ 全局包变量命名采用“g+_+名称”形式
    
      ★ 游标变量命名采用“名称+_+cur”形式
    
      ★ 常量型变量命名采用“c+_+名称”形式
    
      ★ 变量名采用小写,若属于词组形式,用下划线分隔每个单词
    
      ★ 变量用来存放表中的列或行数据值时,使用%TYPE、%ROWTYPE 方式声明变量,使变量声明的类型与表中的保持同步,随表的变化而变化
    
    3.7 用户及角色
      ★ 用户命名由“系统名称++user++名词(或缩写)或名词短语(或缩写)”组成
    
      ★ 角色命名由“系统名称++role++名词(或缩写)或名词短语(或缩写)”组成
    
    3.8 数据库链接
      ★ 数据库链接命名由“远程服务器名++数据库名++link”组成
    
      ★ 若远程服务器名和数据库名一致,上式“_+数据库名”部分省去
    
    3.9 命名中的其它注意事项
      ★ 命名都不得超过30个字符。
    
      ★ 不要在对象名的字符之间留空格
    
      ★ 小心保留词,要保证你的命名没有和保留词、数据库系统或者常用访问方法冲突 4、 编码规范
    
    4.1 一般性注释
    4.1.1 注释尽可能简洁、详细而全面
      4.1.2 创建每一数据库对象时都要加上COMMENT ON注释,以说明该对象的功能和用途;建表时,对某些数据列也要加上COMMENT ON注释,以说明该列和/或列取值的含义。如:XX 表中有CZZT列属性为NUMBER(10, 0)可加COMMENT ON 注释如下COMMENT ON COLUMN XX.CZZT IS ‘0 = 正常, 1 = 等待, 2 = 超时, 3 = 登出’
    
    4.1.3 注释语法包含两种情况:单行注释、多行注释
      单行注释:注释前有两个连字符(–),一般对变量、条件子句可以采用该类注释。
    
      多行注释:符号/和/之间的内容为注释内容。对某项完整的操作建议使用该类注释。
    
    4.2 函数文本注释
    4.2.1 在每一个块和过程(存储过程、函数、包、触发器、视图等)的开头放置注释
    /*************************************** ************* name : –函数名 *function : –函数功能 *input : –输入参数 *output : –输出参数 *author : –作者 *CreateDate : –创建时间 *UpdateDate : –函数更改信息(包括作者、时间、更改内容等) ***************************************** *************/ CREATE [OR REPLACE] PROCEDURE dfsp_xxx … 4.2.2 传入参数的含义应该有所说明。如果取值范围确定,也应该一并说明。取值有特定含义的变量(如boolean类型变量),应给出每个值的含义。
    
    4.2.3 在每一个变量声明的旁边添加注释。说
      明该变量要用作什么
    
      通常,简单使用单行注释就行了,例如l_sfzh CHAR(11) –身份证号码
    
    4.2.4 在块的每个主要部分之前添加注释
      在块的每个主要部分之前增加注释,解释下—组语句目的,最好是说明该段语句及算法的目的以及要得到的结果,但不要对其细节进行过多的描述
    
    4.2.5 在块和过程的开头注释中还可以增加要访问的数据库等信息
    4.3 常用SQL 语句的编写规范
      4.3.1 CREATE语句 4.3.2 SELECT语句 查询语句采用以下原则编写(可最大化重用共享池中的SQL 语句,提高应用程序性能):
    
      ★ 将SELECT 语句分为5部分:
    
      (1) 由SELECT 开头,后跟一个显示查询结果的列表;
    
      (2) 由FROM 开头,后跟一个或多个获取数据所涉及的表;
    
      (3) 由WHERE 开头,后跟一个或多个确定所需值的条件;
    
      (4) 由GROUP BY开头,后跟一个或多个表列名,通过这些列以对查询结果进行汇总;
    
      (5) 由ORDER BY开头,后跟一个或多个表列名,通过这些列以对查询结果进行排序。
    
      ★ 每个部分分行编写,将每一行的第一个关键字与第一行的SELECT尾部对齐,如 ★ 关键字用大写,列名和表名采用小写
    
      ★ 语句中嵌入逗号时,在逗号后面加一空格,当逗号是最后一个字符时,把它放在本行
    
      ★ 当语句的同一部分要延续到下一行时,按下列格式排列: ★ 将语句中WHERE 和AND 部分格式化,书写布局类似于 ★ 当语句中出现括号时,括号的两边不留空格
    
      ★ 在SQL 语句使用运算符时,操作两边应各留一个空格,如 4.3.3 INSERT语句 4.3.4 UPDATE语句 4.3.5 DELETE语句 4.4 条件执行语句(IF)编写规范
    
      条件执行语句IF…ELSE 按以下格式编写 (1) 在IF…THEN和ELSE(或ELSIF)及ELSE…THEN和END IF间可包含一条或多条PL/SQL
    
      语句,而不需要加BEGIN 和END
    
      (2) IF…ELSE…ENDIF 语句可以嵌套
    
      (3) 注意ELSIF的写法
    
    4.5 循环语句编写规范
    4.5.1 简单循环语句
    4.5.2 FOR循环语句
      FOR 变量 IN [变量取值范围] 4.5.3 WHILE循环语句
    
      WHILE <条件表达式> 4.6 函数文本(存储过程、函数和包等)
    
      ★ 对于存储过程、函数等程序块都要有异常处理部分,在异常部分的最后都要设置OTHERS异常情态处理器,以提高程序的自检能力,格式如下: ★ 对于子程序、触发器、包等带名的程序块,要使用结束标识,如 CREATE OR REPLACE PROCEDURE XXXsp_XXX IS … BEGIN … END XXXsp_XXX;/* 此处的过程名XXXsp_XXX是可选的,规范要求写上,与块开始的CREATE相对应 */
    ------------------------------------------------------------------------
    
    
    

    备用字段 / 保留字段 / 预留字段

    数据库设计误区:备用字段 / 保留字段 / 预留字段
    
    【现象描述】
    在数据表中,不仅设计了当前所需要的字段,而且还在其中留出几个字段作为备用。
    比方说,我设计了一个人员表(Person),其中已经添加了各种必要的字段,包括姓名(Name)、性别(Sex)、出生年月日(birthday)等等。大功告成之后,我忽然想到,将来系统中应该还会有很多其它与人相关的内容吧,比方说毕业院校,比方说工作单位等等,尽管现在根本不需要填写,以后可能还是会用到的吧。拍脑袋一项,那就加入5个varchar2型的字段,分别叫做Text1、Text2……Text5,然后又想,应该还有一些日期型的字段需要备用,就又建立了三个date型的字段,分别起名叫做date1、date2、date3,……
    
    
    【原因分析】
    大家应该已经看出问题了,在这个数据表中存在大量暂时无用的字段,我们可以称之为备用字段,它们的作用是什么呢?就是以防万一,防备可能的情况。
    这似乎可以叫做防患于未然,等到需要的时候,就不需在表中增加新的字段了,而且这样做的话,一个表的数据应该会被存储在相邻的物理空间中,这对于性能也是有好处的。
    另外的原因就是,在古老的数据库中,如果改变数据库的定义(包括增加字段、改变字段的类型、删除字段等等),那么其中所有的数据就会丢失,所以这项工作非常麻烦,我们需要先建立临时表,将数据备份出来,然后创建新表,将数据导入其中,最后再删除原来的表。
    
    
    【问题所在】
    这样的做法对于项目会导致很多问题,而且原先想要解决的问题并不一定能够解决,不信的话,请往下看。
    问题一:增加大量备用字段,必定会浪费很多空间,尽管其中可能都没有具体的数据,但是仅仅是空字段也会占据一定的空间的。
    问题二:由于命名的特点,如果没有完善的文档管理流程,用不了多久(可能也就是两三年),就没有人能够说清楚到底哪个字段代表的是什么意义了。就算有文档管理,这些管理工作也会比较麻烦,而且在每次使用的时候都需要申请,还有可能会出现冲突的情况。
    问题三:增加了这些备用字段就真的会够用吗?不一定,因为我们只是每个类型的字段留出几个备用,如果数量超过,或者要使用特殊的、不常用的类型的时候,还是需要增加新的字段。比方说在上述的Person表中,我们要存储照片,那么可能就要增加一个blob类型的photo字段,这在初期设计的时候可不一定会留出这样的备用字段。而且如果没有完善的管理,谁又能说清楚倒底哪个字段已经被使用,哪个字段还可以使用呢?到时候还不是要增加新的字段。
    
    
    【解决方案】
    其实上面的这种设计方式就是一种“过度设计”,我们应该做的就是“按需设计”,在经过详细有效的分析之后,在数据表中只放置必要的字段,而不要留出大量的备用字段。
    当需要增加相关的信息的时候,就要具体情况具体分析:
    1. 如果数量很少,而且信息的性质与原表密切相关,那么就可以直接在原表上增加字段,并将相关的数据更新进去;
    2. 如果数量较大,或者并非是原表对象至关重要的属性,那么就可以新增一个表,然后通过键值连接起来;
    3. 对于表的数据的存储位置所导致的性能问题,我们可以通过在特定时间对数据库的数据进行重组来解决,而这项工作对于长期运行的数据库来说,也是需要定期进行的。
    --------------------- 
    项目设计数据库表时是否需要在表中加备用预留字段?
    
    背景:以前做项目,有用过SSH框架,或者SSM框架,数据库有Oracle,DB2。在开发过程中,有时因数据库设计者未考虑周到,业务实体有一个属性没有对应的字段,因此需要在数据库表加一个字段,又由于此字段要求不可为空,并且在开发阶段,测试数据不多,有时是drop掉了原来的表,增加了一个字段再重新建了一张表。有时一些表,设计表时会在后面加几个类型为varchar的预留字段。
    
    最近和朋友聊到这个问题,就是:为什么要这么做,好处是什么,怎么权衡这个问题。
    
    在遇到这个问题之后引起我思考:预留字段这个通用的做法是否能减少开发阶段由于考虑不周到,或后续维护阶段因为需求变更或者扩展改造而需要增加字段而造成的麻烦?
    
    就此与一些朋友进行了讨论,根据以往的项目经验和设计原则给出了一些解答,以及怎样的设计能确保数据库健壮,可扩展。大家意见不一,以下是正反方的一些意见和看法。
    
    
    
    ———————————————
    
    正反观点:需要
    原因:
    1. 持久层的设计,数据库表结构不应轻易变更。因此应设置备用字段。启用备用字段后,只修改代码,在代码中增加注释和并文档说明即可,不需要改动数据库结构,更方便。
    2. 如果没有备用字段,如果后期要加字段的话,用add column的方法会改变原先的数据库存储结构,造成数据移动,移动需要时间,而且会移动到其他数据块,add column会影响数据库性能。
    3. 对于反方提到的规范问题,只要代码和文档规范是可以避免这样的问题的,即使遇到这样的问题,也比修改表名带来的危险要小,除了要修改代码、存储过程、配置文件中的表名,还要考虑数据的迁移等问题,如此多的改动难免会出现这样那样的问题,因此保证系统的稳定性来看,携带几个扩展字段为了后续使用也无妨。
    
    
    
    ———————————————
    
    反方观点:不需要
    
    1. 如果要预留字段的话,第一个需要全面考虑的问题,如何评估:
        a. 预留多少个字段;
        b. 预留什么类型的;
        c. 预留的字段不适用怎么办——比如长度/精度不够;
        d. 预留的字段允许不允许空值呢;
    2. 数据库设置备用字段无法在字段名上体现其意义,不规范,后期维护麻烦。在需要增加字段的时候如果直接add column,也不会有太大工作,但能保证数据库字段的规范。虽然在启用备用字段的时候可以文档说明,但在POJO上对应其属性为attribute1,attribute2等,代码的可读性不强。而且,预留字段全部统一为varchar,也不太合适。
    3. 预留字段毕竟是数据库表字段,会占用数据库存储空间。
    
    4. 添加字段出现的性能问题,我之前的项目中一般都是定期对数据库进行数据整理、重组操作。
    
    
    
    
    
    各方案都有不同的侧重点,最终的你会选择选择哪种方案呢?
    
    
    
    ——————————————————————————————
    
    CSDN有另一篇博文,地址是:http://blog.csdn.net/iw1210/article/details/44752771,
    
    分析也很不错,给出了相应的解决方案,详细内容如下:
    
    
    
    数据库设计误区:备用字段 / 保留字段 / 预留字段
    
    【现象描述】
    在数据表中,不仅设计了当前所需要的字段,而且还在其中留出几个字段作为备用。
    比方说,我设计了一个人员表(Person),其中已经添加了各种必要的字段,包括姓名(Name)、性别(Sex)、出生年月日(birthday)等等。大功告成之后,我忽然想到,将来系统中应该还会有很多其它与人相关的内容吧,比方说毕业院校,比方说工作单位等等,尽管现在根本不需要填写,以后可能还是会用到的吧。拍脑袋一项,那就加入5个varchar2型的字段,分别叫做Text1、Text2……Text5,然后又想,应该还有一些日期型的字段需要备用,就又建立了三个date型的字段,分别起名叫做date1、date2、date3,……
    
    
    【原因分析】
    大家应该已经看出问题了,在这个数据表中存在大量暂时无用的字段,我们可以称之为备用字段,它们的作用是什么呢?就是以防万一,防备可能的情况。
    这似乎可以叫做防患于未然,等到需要的时候,就不需在表中增加新的字段了,而且这样做的话,一个表的数据应该会被存储在相邻的物理空间中,这对于性能也是有好处的。
    另外的原因就是,在古老的数据库中,如果改变数据库的定义(包括增加字段、改变字段的类型、删除字段等等),那么其中所有的数据就会丢失,所以这项工作非常麻烦,我们需要先建立临时表,将数据备份出来,然后创建新表,将数据导入其中,最后再删除原来的表。
    
    
    【问题所在】
    这样的做法对于项目会导致很多问题,而且原先想要解决的问题并不一定能够解决,不信的话,请往下看。
    问题一:增加大量备用字段,必定会浪费很多空间,尽管其中可能都没有具体的数据,但是仅仅是空字段也会占据一定的空间的。
    问题二:由于命名的特点,如果没有完善的文档管理流程,用不了多久(可能也就是两三年),就没有人能够说清楚到底哪个字段代表的是什么意义了。就算有文档管理,这些管理工作也会比较麻烦,而且在每次使用的时候都需要申请,还有可能会出现冲突的情况。
    问题三:增加了这些备用字段就真的会够用吗?不一定,因为我们只是每个类型的字段留出几个备用,如果数量超过,或者要使用特殊的、不常用的类型的时候,还是需要增加新的字段。比方说在上述的Person表中,我们要存储照片,那么可能就要增加一个blob类型的photo字段,这在初期设计的时候可不一定会留出这样的备用字段。而且如果没有完善的管理,谁又能说清楚倒底哪个字段已经被使用,哪个字段还可以使用呢?到时候还不是要增加新的字段。
    
    
    【解决方案】
    其实上面的这种设计方式就是一种“过度设计”,我们应该做的就是“按需设计”,在经过详细有效的分析之后,在数据表中只放置必要的字段,而不要留出大量的备用字段。
    当需要增加相关的信息的时候,就要具体情况具体分析:
    1. 如果数量很少,而且信息的性质与原表密切相关,那么就可以直接在原表上增加字段,并将相关的数据更新进去;
    2. 如果数量较大,或者并非是原表对象至关重要的属性,那么就可以新增一个表,然后通过键值连接起来;
    3. 对于表的数据的存储位置所导致的性能问题,我们可以通过在特定时间对数据库的数据进行重组来解决,而这项工作对于长期运行的数据库来说,也是需要定期进行的。
    
    
    --------------------- 
    

     

    展开全文
  • 使用“增量导入”命令对数据库发生的变化的数据导出,并提交给solr建立索引。  配置数据源   将dataSource标签直接添加到dataConfig下面,即成为dataConfig的子元素.  数据源也可以配置在solrconfig.xml中 ...
  • 建立索引目的:加快查询速度 索引类似一本书的目录,通过索引可以快速找到想要的内容。 数据库中的索引是一个表中所含的值的列表,其中注明了各个值所对应的行所在的位置。 索引是关系数据库的内部实现技术,属于...
  • lucene 4.6 为数据库建立增量索引

    千次阅读 2014-01-02 14:09:53
    lucene 4.6 为数据库建立增量索引 首先去官网下载lucene ,地址:http://www.apache.org/dyn/closer.cgi/lucene/java/4.6.0 下载IK分词源码,地址 :  http://code.google.com/p/ik-analyzer/downloads/list ...
  • 数据库索引

    千次阅读 2012-08-23 17:52:55
    数据库建立索引常用的规则如下: 1、表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的...
  • mysql数据库正确建立索引及使用

    千次阅读 2015-09-19 10:00:37
    其中优化mysql的一个重要环节就是为数据库建立正确合理的索引。 如果没有索引,执行查询时mysql必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高...
  • 数据库添加索引

    万次阅读 2017-10-25 09:56:05
    1.MySQL在创建数据表的时候创建索引 在MySQL中创建表的时候,可以直接创建索引。基本的语法格式如下: CREATE TABLE 表名(字段名 数据类型 [完整性约束条件], [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY ...
  • 数据库索引

    千次阅读 2016-07-11 18:13:55
    数据库索引就是加快检索表中数据的方法。数据库索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地
  • 数据库实用索引优化

    千次阅读 2016-04-06 22:20:00
    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引(聚集索引)和非聚簇索引(非聚集索引)两种...
  • MySQL数据库——索引

    千次阅读 2019-04-12 22:26:29
    索引在Mysql中是存储引擎用于快速找到记录的一种数据结构(BTree,平衡树),索引对于数据库的良好性能是非常关键的,尤其是当表中的数据量越来越大的时候,索引对于性能的影响愈发重要。 索引相当于字典的目录,...
  • 转载于:http://www.cnblogs.com/leilei/articles/1152499.html首先,为了建立一个测试环境,我们来往数据库中添加1000万条数据:declare @i intset @i=1while @i(fariqi,neibuyonghu,reader,title) values(‘2004-2...
  • 数据库索引

    千次阅读 2017-08-10 21:53:50
    普通索引:使用字段关键字建立索引,主要是提高查询速度 唯一索引:字段数据是唯一的,数据内容里面能否为null,在一张表里面,是可以添加多个唯一索引。 全文索引:在比较老的版本中,只有myisam引擎支持全文...
  • 数据库索引详解

    千次阅读 2017-04-08 10:43:43
    索引的重要性:当你的数据库的性能出现问题了,那么就重新优化你的索引吧,这能够解决80%的性能问题,由此可见索引的重要性,尤其在数据量越来越大的时候,影响更加的明显,一个最优的索引能够轻易的将查询性能提高...
  • 数据库索引数据库作业

    千次阅读 2018-05-08 10:21:52
    什么是数据库索引数据库索引是对数据库表中一列或多列进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。数据库索引就像书的目录,能加快数据库的查询速度。索引分为聚簇索引(聚集索引)和非聚簇...
  • oracle数据库索引

    千次阅读 2017-10-12 19:24:00
    [什么是索引] 在关系型数据库中,索引是一种和表有关的数据结构,它...数据库索引也是一样,只有先插入好数据,再建立索引。 [为何索引可提高查询效率] 因为DB在执行一条sql语句的时候,默认的方式是根据搜索条
  • 数据库索引原理

    千次阅读 2008-04-21 15:10:00
    [转载 原作者不详 版权归原作者所有]数据库索引原理实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引...
  • 前面两篇文章 《解析B+树比B树更加适合做数据库索引的原因 》 和《从底层解析B+索引提高查询速度的原因》是从数据结构的角度分析了B+索引,并分别介绍了B+索引在两个主流存储引擎InnoDB和MyISAM中的实现。...
  • Java面试之数据库——数据库索引

    万次阅读 2018-05-31 11:01:02
    原文:https://blog.csdn.net/sundacheng1989/article/details/53117172最近使用到Oracle数据库索引比较多,所以就想好好研究一下索引到底是什么。毕竟作为一个Application Developer,而不是DBA,所以这篇文字也...
  • Mysql数据库索引原理

    千次阅读 2013-06-28 14:20:53
    写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个表进行...
  • 数据库索引原理

    千次阅读 2007-05-19 07:53:00
    数据库索引原理 实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集...
  • 数据库添加索引为什么能加快查询速度

    万次阅读 多人点赞 2018-03-23 09:47:06
    数据库建立索引为什么会加快查询速度 首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时...
  • 在数据库存优化设计中往往会提到索引,这编文章就来详细的说明一下在 SQL SERVER 下面的建立索引的技巧和需要注意的一些地方,让您可以更直观的了解数据库的结构。 往往在数据量比较小,查询量也不是很大的时候我们...
  • 加快数据库查询速度方法--建立索引

    千次阅读 2011-08-23 14:26:25
    建立索引目的是加快查询速度。 索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(如B 树)中,使数据库管理系统可以快速...
  • MySQL数据库索引

    万次阅读 多人点赞 2018-09-23 09:31:41
    数据库有哪些索引 唯一索引 聚簇索引与非聚簇索引 全文索引 使用索引一定能提高查询性能吗? 哪些情况下设置了索引但是无法使用 哪些情况下需要设置索引、哪些情况下不需要 什么情况下应该使用组合索引而非...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 105,688
精华内容 42,275
关键字:

数据库建立索引的目的