精华内容
下载资源
问答
  • 要建立几个字段建立索引怎么样建立, 建立好了如何使用 SQL语句又该怎写? ----------------------------------------------------------------------------------------------------------------------------------...

    我现在有一个SQL语句 
    SELECT FAQID,
    FAQNAME,
    TYPE,
    CREATOR,
    TOQUESTION,
    SUPERCODE
    FROM T_KBS_FAQ t
    where TYPE = '1' 
    <[AND FAQID = :faqId]>
    <[AND FAQNAME like '%' ||:faqName || '%']>
    <[AND CREATOR = :creator]>
    <[AND SUPERCODE=:superCode]>
    <[AND TOQUESTION=:toQuestion]>
    <%:toKngType%>
    现在数据过大 导致查询很慢,如何优化? 要建立几个字段建立索引怎么样建立, 建立好了如何使用 SQL语句又该怎写?

    -------------------------------------------------------------------------------------------------------------------------------------------------

    name id rowid     rowid id
    王一            1 x001     x001                1
    王二            2 x002     x002                2
    王三            3 x003     x003                3
            索引
    select * from 表 where id = 3
    索引是由where条件触发的 ,此查询语句中指定id=3,因为有索引直接从索引段中找到id=3对应的rowid,然后就可以快速用rowid快速找到表中的记录,如果没有索引,就是一行一行的查找id=3的记录

    下面写几种常见索引的创建吧:

    (1) create index 索引名 on 表(字段名);     //创建B树索引,一般用的OLTP中

    (2) create bitmap index 索引名 on 表(字段名);   //创建位图索引,一般用的OLAP中

    (3) create index 索引名 on 表名 (substr(字段,1,10))  

    //创建函数索引,有些时候呢,咱们的搜索条件要加上函数,这种情况呢,普通索引就不能解发了,就要创建函数索引

    (4) create index 索引名 on 表名 (字段1,字段2......字段n);

    //复合索引,当条件中,经常去查询多个条件时,可以把多个条件放在一个索引中

    (5)  create index 索引名 on 表(字段 desc);   //排序后创建索引

    (6) create index 索引名 on 表名 (字段) reverse; //反转索引,消除热点块

    (7) create index 索引名 on 表名(字段) local;   //创建分区本地索引

    (8) create index 索引名 on 表名 (字段) global;   //创建分区全局索引

    扩展资料:


    1、如果有两个或者以上的索引,其中有一个唯一性索引,而其他是非唯一,这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
    2、至少要包含组合索引的第一列(即如果索引建立在多个列上,只有它的第一个列被where子句引用时,优化器才会使用该索引
    3、小表不要简历索引
    4、对于基数大的列适合建立B树索引,对于基数小的列适合简历位图索引
    5、列中有很多空值,但经常查询该列上非空记录时应该建立索引
    6、经常进行连接查询的列应该创建索引
    7、使用create index时要将最常查询的列放在最前面
    8、LONG(可变长字符串数据,最长2G)和LONG RAW(可变长二进制数据,最长2G)列不能创建索引
    9、限制表中索引的数量(创建索引耗费时间,并且随数据量的增大而增大;索引会占用物理空间;当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度)


    附加维护索引基本操作手段:


    查看指定表上现有索引详情
    select t.INDEX_NAME,t.COLUMN_NAME,i.index_type  from user_ind_columns t,user_indexes i where t.index_name = i.index_name and  t.table_name='表名';


    查看索引及列

    select t.index_name,t.status from user_indexes t where t.INDEX_NAME in ();

    查看指定分区表的所有索引


    select t.INDEX_NAME,t.COLUMN_NAME,i.index_type from 

    user_ind_columns t,user_indexes i where t.index_name = i.index_name and  

    t.table_name='表名';
    查询分区索引状态

    select PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where INDEX_NAME='';

    valid:当前索引有效

    N/A :分区索引 有效

    unusable:索引失效

    重新建立索引

    ALTER INDEX 用户.索引 REBUILD;

    展开全文
  • <br />如何建立索引:  就象许多的PHP开发者一样,在刚开始建立动态网站的时候,我都是使用相对简单的数据结构。PHP在连接数据库方面的确实是十分方便(译者注:有些人认为 PHP在连接不同数据库时没有一...

    建立索引的好处真的很多很多,但是貌似大家都忽视了。。。

    索引主要是针对经常用作where条件的字段去建立,这可以有效节省查询时间。

    如何建立索引:
      就象许多的PHP开发者一样,在刚开始建立动态网站的时候,我都是使用相对简单的数据结构。PHP在连接数据库方面的确实是十分方便(译者注:有些人认为 PHP在连接不同数据库时没有一个统一的接口,不太方便,其实这可以通过一些扩展库来做到这一点),你无需看大量的设计文档就可以建立和使用数据库,这也是PHP获得成功的主要原因之一。

      前些时候,一位颇高级的程序员居然问我什么叫做索引,令我感到十分的惊奇,我想这绝不会是沧海一粟,因为有成千上万的开发者(可能大部分是使用MySQL的)都没有受过有关数据库的正规培训,尽管他们都为客户做过一些开发,但却对如何为数据库建立适当的索引所知较少,因此我起了写一篇相关文章的念头。

      最普通的情况,是为出现在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"。

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


    ----------------------分割线-------------------------------

    建立索引的原则:


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    展开全文
  • 建立索引) 首先,为了建立一个测试环境,我们来往数据库中添加1000万条数据: declare @i int set @i=1 while @i begin insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-...
    
    
    首先,为了建立一个测试环境,我们来往数据库中添加1000万条数据:

    declare @i int

    set @i=1

    while @i<=250000

    begin

    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,办公室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,外事科','这是最先的25万条记录')

    set @i=@i+1

    end

    GO



    declare @i int

    set @i=1

    while @i<=250000

    begin

    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','办公室','办公室,通信科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支 队,户政科,外事科','这是中间的25万条记录')

    set @i=@i+1

    end

    GO



    declare @h int

    set @h=1

    while @h<=100

    begin

    declare @i int

    set @i=2002

    while @i<=2003

    begin

    declare @j int

    set @j=0

    while @j<50

    begin

    declare @k int

    set @k=0

    while @k<50

    begin

    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'通信科','办公室,通信科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事 科','这是最后的50万条记录')

    set @k=@k+1

    end

    set @j=@j+1

    end

    set @i=@i+1

    end

    set @h=@h+1

    end

    GO



    declare @i int

    set @i=1

    while @i<=9000000

    begin

    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,办公室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,外事科','这是最后添加的900万条记录')

    set @i=@i+1000000

    end

    GO

    通 过以上语句,我们创建了25万条由于2004年2月5日发布的记录,25万条由办公室于2004年9月6日发布的记录,2002年和2003年各100个2500条相同日期、不同分秒的记录(共50万条),还有由通信科于2004年5月5日发布的900万条记录,合计1000万条。

    一、因情制宜,建立“适当”的索引

    建立“适当”的索引是实现查询优化的首要前提。

    索引(index)是除表之外另一重要的、用户定义的存储在物理介质上的数据结构。当根据索引码的值搜索数据时,索引提供了对数据的快速访问。事实上, 没有索引,数据库也能根据Select语句成功地检索到结果,但随着表变得越来越大,使用“适当”的索引的效果就越来越明显。注意,在这句话中,我们用了“适当”这个词,这是因为,如果使用索引时不认真考虑其实现过程,索引既可以提高也会破坏数据库的工作性能。

    (一)深入浅出理解索引结构

    实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

    其实,我们的汉语字典的正文本身 就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开 头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样 的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其 他目录来找到您需要找的内容。

    我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    如果您 认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去 根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的 正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在 非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻 到您所需要的页码。

    我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

    通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。

    进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

    (二)何时使用聚集索引或非聚集索引

    下面的表总结了何时使用聚集索引或非聚集索引(很重要)。

    动作描述
    使用聚集索引
    使用非聚集索引

    列经常被分组排序



    返回某范围内的数据

    不应

    一个或极少不同值
    不应
    不应

    小数目的不同值

    不应

    大数目的不同值
    不应


    频繁更新的列
    不应


    外键列



    主键列



    频繁修改索引列
    不应



    事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建 立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚 类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

    (三)结合实际,谈索引使用的误区

    理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。

    1、主键就是聚集索引

    这种想法笔者认为是极端错误的,是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。

    通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如 此。此时,如果我们将这个列设为主键,SQL SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。

    显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。

    从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次, 让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引 项的时候会负作用,但对于查询速度并没有影响。

    在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。

    通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很 长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样 做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表 扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。

    在这里之所以 提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚 合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

    (1)仅在主键上建立聚集索引,并且不划分时间段:

    Select gid,fariqi,neibuyonghu,title from tgongwen

    用时:128470毫秒(即:128秒)

    (2)在主键上建立聚集索引,在fariq上建立非聚集索引:

    select gid,fariqi,neibuyonghu,title from Tgongwen

    where fariqi> dateadd(day,-90,getdate())

    用时:53763毫秒(54秒)

    (3)将聚合索引建立在日期列(fariqi)上:

    select gid,fariqi,neibuyonghu,title from Tgongwen

    where fariqi> dateadd(day,-90,getdate())

    用时:2423毫秒(2秒)

    虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000万 容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因 素。

    得出以上速度的方法是:在各个select语句前加:declare @d datetime

    set @d=getdate()

    并在select语句后加:

    select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

    2、只要建立索引就能显著提高查询速度

    事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。

    从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中, 我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看 来,我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

    3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度

    上面已经谈到:在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。

    很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题, 我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列)

    (1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

    查询速度:2513毫秒

    (2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'

    查询速度:2516毫秒

    (3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'

    查询速度:60280毫秒

    从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列 还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度 一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您 是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

    (四)其他书上没有的索引使用经验总结

    1、用聚合索引比用不是聚合索引的主键速度快

    下面是实例语句:(都是提取25万条数据)

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

    使用时间:3326毫秒

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

    使用时间:4470毫秒

    这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。

    2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

    用时:12936

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

    用时:18843

    这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。

    3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

    用时:6343毫秒(提取100万条)

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

    用时:3170毫秒(提取50万条)

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

    用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

    用时:3280毫秒

    4 、日期列不会因为有分秒的输入而减慢查询速度

    下面的例子中,共有100万条数据,2004年1月1日以后的数据有50万条,但只有两个不同的日期,日期精确到日;之前有数据50万条,有5000个不同的日期,日期精确到秒。

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

    用时:6390毫秒

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

    用时:6453毫秒

    (五)其他注意事项

    “水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

    所以说,我们要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使您的数据库能得到高性能的发挥。

    当然,在实践中,作为一个尽职的数据库管理员,您还要多测试一些方案,找出哪种方案效率最高、最为有效。
    展开全文
  • oracle数据库建立索引

    2011-09-28 23:59:06
    oracle数据库如何建立索引,建立索引有什么优势,索引该如何使用
  • 数据库建立索引

    2020-12-14 23:53:05
    象许多的PHP开发者一样,在刚开始建立动态网站的时候,我都是使用相对简单的数据结构。PHP在连接数据库方面的确实是十分方便(译者注:有些人认为 PHP...但却对如何数据库建立适 当的索引所知较少,因此我起了写一篇
  • 文章目录1. 什么是索引2. 索引的特点3. 索引的分类4. 索引的设计原则5. 创建索引5.1. 自动创建索引5.2. 手动创建索引5.2.1. 创建表时创建索引5.2.2. 在已经存在的表上创建...在数据库中,索引用来加速对表的查询,索引
  • 如何数据库建立索引 SQL2010-12-15 16:46:54阅读41评论0 字号:大中小 订阅 最普通的情况,是为出现在where子句的字段建一个索引。为方便讲述,我们先建立一个如下的表。 CREATE TABLE mytable ...

    如何为数据库建立索引

    SQL 2010-12-15 16:46:54 阅读41 评论0   字号: 订阅

    最普通的情况,是为出现在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://database.51cto.com/art/200512/12970.htm

    展开全文
  • 达梦数据库如何索引和使用

    千次阅读 2020-02-17 22:36:11
    建立索引的准则 1.1在表中插入数据后创建索引 一般情况下,在插入或装载了数据后,为表创建索引会更加有效率。如果在装载数据之前创建了一个或多个索引,那么在插入每行时DM数据库都必须更改和维护每个索引,使得...
  • 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段...
  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性 可以加速表和表之间的连接 二、 如何索引 1. 场景介绍 引入一个场景,以下面的表为例,这个表有5个字段,分别是id,name,time,subject和grade,...
  • 首先,为了建立一个测试环境,我们来往数据库中添加1000万条数据:declare @i intset @i=1while @i<=250000begininsert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,...
  • mysql数据库建立索引

    2017-12-11 18:33:11
    令我感到十分的惊奇,我想这绝不会是沧海一粟,因为有成千上万的开发者(可能大部分是使用MySQL的)都没有受过有关数据库的正规培训,尽管他们都为客户做过一些开发,但却对如何数据库建立适当的索引所知较少,...
  • 数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录,其中有一条:...
  • 为mysql数据库建立索引 前些时候,一位颇高级的程序员居然问我什么叫做索引,令我感到十分的惊奇,我想这绝不会是沧海一粟,因为有成千上万的开发者(可能大部分是使用MySQL的)都没有受过有关数据库的正规培训...
  • 达梦数据库、oracle数据库如何判断指定表有没有建立索引?对应的表有没有索引查询方法 sm_appmenuitem 这个演示表有 5 个索引。 我在不知道的情况下想知道这个表的索引有没有建成功,或者说我现在想知道这个表的索引...
  • 令我感到十分的惊奇,我想这绝不会是沧海一粟,因为有成千上万的开发者(可能大部分是使用MySQL的)都没有受过有关数据库的正规培训,尽管他们都为客户做过一些开发,但却对如何数据库建立适当的索引所知较少,...

空空如也

空空如也

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

数据库如何建立索引