精华内容
下载资源
问答
  • 为mysql数据库建立索引;mysql索引总结----mysql 索引类型以及创建;mysql_建立索引的优缺点

    因为欣赏所以转载:

    http://www.cnblogs.com/cy163/archive/2008/10/27/1320798.html

    http://www.cnblogs.com/lihuiyong/p/5623191.html

    http://www.cnblogs.com/wb118115/p/6066171.html

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

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

    Code代码如下:
    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"。

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

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引。

    一个简单的对比测试

    以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条数据,平均每条数据源都重复大概10万次,表结构比较简单,仅包含一个自增ID,一个char类型,一个text类型和一个int类型,单表2G大小,使用MyIASM引擎。开始测试未添加任何索引。

    执行下面的SQL语句:

    1 mysql> SELECT id,FROM_UNIXTIME(timeFROM article WHERE a.title='测试标题'

    查询需要的时间非常恐怖的,如果加上联合查询和其他一些约束条件,数据库会疯狂的消耗内存,并且会影响前端程序的执行。这时给title字段添加一个BTREE索引:

    1 mysql> ALTER TABLE article ADD INDEX index_article_title ON title(200);

    再次执行上述查询语句,其对比非常明显:

     

    MySQL索引的概念

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。上述SQL语句,在没有索引的情况下,数据库会遍历全部200条数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”,那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢?上面的两个图片鲜明的用时对比已经给出了答案(注:一般数据库默认都会为主键生成索引)。

    索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

    MySQL索引的类型

    1. 普通索引

    这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。

    01 –直接创建索引
    02 CREATE INDEX index_name ON table(column(length))
    03 –修改表结构的方式添加索引
    04 ALTER TABLE table_name ADD INDEX index_name ON (column(length))
    05 –创建表的时候同时创建索引
    06 CREATE TABLE `table` (
    07 `id` int(11) NOT NULL AUTO_INCREMENT ,
    08 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    09 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    10 `timeint(10) NULL DEFAULT NULL ,
    11 PRIMARY KEY (`id`),
    12 INDEX index_name (title(length))
    13 )
    14 –删除索引
    15 DROP INDEX index_name ON table

    2. 唯一索引

    与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。

    01 –创建唯一索引
    02 CREATE UNIQUE INDEX indexName ON table(column(length))
    03 –修改表结构
    04 ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
    05 –创建表的时候直接指定
    06 CREATE TABLE `table` (
    07 `id` int(11) NOT NULL AUTO_INCREMENT ,
    08 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    09 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    10 `timeint(10) NULL DEFAULT NULL ,
    11 PRIMARY KEY (`id`),
    12 UNIQUE indexName (title(length))
    13 );

    3. 全文索引(FULLTEXT)

    MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

    01 –创建表的适合添加全文索引
    02 CREATE TABLE `table` (
    03 `id` int(11) NOT NULL AUTO_INCREMENT ,
    04 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    05 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    06 `timeint(10) NULL DEFAULT NULL ,
    07 PRIMARY KEY (`id`),
    08 FULLTEXT (content)
    09 );
    10 –修改表结构添加全文索引
    11 ALTER TABLE article ADD FULLTEXT index_content(content)
    12 –直接创建索引
    13 CREATE FULLTEXT INDEX index_content ON article(content)

    4. 单列索引、多列索引

    多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

    5. 组合索引(最左前缀)

    平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:

    –title,time

    –title

    为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:

    1 –使用到上面的索引
    2 SELECT FROM article WHREE title='测试' AND time=1234567890;
    3 SELECT FROM article WHREE utitle='测试';
    4 –不使用上面的索引
    5 SELECT FROM article WHREE time=1234567890;
    MySQL索引的优化

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

    1. 何时使用聚集索引或非聚集索引?

    动作描述 使用聚集索引 使用非聚集索引
    列经常被分组排序 使用 使用
    返回某范围内的数据 使用 不使用
    一个或极少不同值 不使用 不使用
    小数目的不同值 使用 不使用
    大数目的不同值 不使用 使用
    频繁更新的列 不使用 使用
    外键列 使用 使用
    主键列 使用 使用
    频繁修改索引列 不使用 使用

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

    2. 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    3. 使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    4. 索引列排序

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

    5. like语句操作

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

    6. 不要在列上进行运算

    例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MYSQL性能损失。

    最后总结一下,MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的,比如我刚才针对text类型的字段创建索引的时候,系统差点就卡死了。

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

    建立索引的优缺点:

    为什么要创建索引呢?

            这是因为,创建索引可以大大提高系统的性能。 
            第一、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 
            第二、可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 
            第三、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 
            第四、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 
            第五、通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

            也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。

           这是因为,增加索引也有许多不利的一个方面:

            第一、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。 

            第二、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间就会更大。 

            第三、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

     

    什么样的字段适合创建索引:

     索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。

           一般来说,应该在这些列上创建索引,例如:

           第一、在经常需要搜索的列上,可以加快搜索的速度; 

           第二、在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 

           第三、在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度; 

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

           第五、在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 

           第六、在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

         

           建立索引,一般按照select的where条件来建立,比如: select的条件是where f1 and f2,那么如果我们在字段f1或字段f2上简历索引是没有用的,只有在字段f1和f2上同时建立索引才有用等。

     

    什么样的字段不适合创建索引:

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

     

    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,

    并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 
           第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,

    在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。

    增加索引,并不能明显加快检索速度。 
           第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 
            第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。

    当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。

    因此,当修改性能远远大于检索性能时,不应该创建索引。


    创建索引的方法::

    1、创建索引,例如 create index <索引的名字> on table_name (列的列表); 
          2、修改表,例如 alter table table_name add index[索引的名字] (列的列表); 
          3、创建表的时候指定索引,例如create table table_name ( [...], INDEX [索引的名字] (列的列表) );

     

    查看表中索引的方法:

    show index from table_name; 查看索引

     

     

    索引的类型及创建例子::

    1.PRIMARY KEY (主键索引)

    MySQL> alter table table_name add primary key ( `column` )

     

    2.UNIQUE 或 UNIQUE KEY (唯一索引)

    mysql> alter table table_name add unique (`column`)


         3.FULLTEXT (全文索引)
         mysql> alter table table_name add fulltext (`column` )

         4.INDEX (普通索引)
         mysql> alter table table_name add index index_name ( `column` )

    5.多列索引 (聚簇索引)
          mysql> alter table `table_name` add index index_name ( `column1`, `column2`, `column3` )


    展开全文
  • 数据库建立索引的原则

    千次阅读 2014-12-12 11:54:18
    铁律一:天下没有免费的午餐,使用索引是需要付出代价的。 索引的优点有目共睹,但是,却很少有人关心过采用...特别是在数据库设计的时候,数据库管理员为表中的哪些字段需要建立索引,要调研、要协调。如当建有索引的
    
    

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    展开全文
  • 数据库建立索引怎么利用索引查询

    千次阅读 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语句隐藏了这一情况,使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。

    展开全文
  • 数据库建立索引的优缺点

    千次阅读 2019-04-01 14:21:34
    为什么要建立索引,即索引的优点: ① 建立索引的列可以保证行的唯一性,生成唯一的rowId ② 建立索引可以有效缩短数据的检索时间 ③ 建立索引可以加快表与表之间的连接 ④ 为用来排序或者是...

    索引就像是书的目录,是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(BTree)中,使SQL可以快速有效地查找与键值关联的行。

    为什么要建立索引,即索引的优点:
    ① 建立索引的列可以保证行的唯一性,生成唯一的rowId

    ② 建立索引可以有效缩短数据的检索时间

    ③ 建立索引可以加快表与表之间的连接

    ④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

    索引的缺点:
    ① 创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大

    ② 创建索引和维护索引需要空间成本,每一条索引都要占据数据库的物理存储空间,数据量越大,占用空间也越大(数据表占据的是数据库的数据空间)

    ③ 会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间变长

    什么样的表跟列要建立索引:
    ① 总的来说就是数据量大的,经常进行查询操作的表要建立索引

    ② 表中字段建立索引应该遵循几个原则:

    1. 越小的数据类型通常更好:越小的数据类型通常在磁盘、内存中都需要更少的空间,处理起来更快。

    2. 简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂,处理起来也更耗时。

    3. 尽量避免NULL:应该指定列为NOT NULL。含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

    4. 对非唯一的字段,例如“性别”这种大量重复值的字段,增加索引也没有什么意义,所以索引的建立应当更多的选取唯一性更高的字段。

    ③ 表与表连接用于多表联合查询的约束条件的字段应当建立索引

    ④ 用于排序的字段可以添加索引,用于分组的字段应当视情况看是否需要添加索引。

    ⑤ 添加多列索引的时候,对应的多条件查询可以触发该索引的同时,索引最左侧的列的单条件查询也可以触发。

    ⑥ 如果有些表注定只会进行查询所有,也就没必要添加索引,因为查询全部只能进行全量搜索即扫描全表。

    索引的原理:
    索引的原理大致概括为以空间换时间,数据库在未添加索引的时候进行查询默认的是进行全量搜索,也就是进行全局扫描,有多少条数据就要进行多少次查询,然后找到相匹配的数据就把他放到结果集中,直到全表扫描完。而建立索引之后,会将建立索引的KEY值放在一个n叉树上(BTree)。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表,每次以索引进行条件查询时,会去树上根据key值直接进行搜索,次数约为log总条数,底数为页面存储数,例如一个100万数据的表,页面存储数为100,那么有索引的查询次数为3次log1000000100,但是全量搜索为100万次搜索,这种方式类似于二分法,但是这个是n分法。

    索引对增删改的影响实际数据修改测试:

    一个表有字段A、B、C,同时进行插入10000行记录测试

    在没有建索引时平均完成时间是2.9秒

    在对A字段建索引后平均完成时间是6.7秒

    在对A字段和B字段建索引后平均完成时间是10.3秒

    在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

    从以上测试结果可以明显看出索引对数据修改产生的影响

    转自:https://blog.csdn.net/miracleww/article/details/53352738

    展开全文
  • oracle数据库建立索引的原则

    千次阅读 2016-08-08 23:16:02
    数据库建立索引的原则 1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。 3,尝试建立复合索引来...
  • 数据库索引类型

    2018-04-04 11:14:21
    数据库索引类型
  • 首先明白为什么索引会增加速度,DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的...
  • 数据库的五种索引类型

    万次阅读 多人点赞 2019-03-15 21:24:13
    本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方 首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的...
  • 数据库建立索引的好处与坏处

    千次阅读 2018-04-26 13:23:49
    原文出处:原文出处数据库索引的优缺点为什么要创建索引呢?这是因为,创建索引可以大大提高系统的性能。第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。第二,可以大大加快数据的检索速度,这...
  • 用Lucene.net对数据库建立索引及搜索

    千次阅读 2008-07-02 18:33:00
    原文出处:http://blog.csdn.net/zhanghefu/archive/2007/05/08/1600702.aspx用Lucene.net对数据库建立索引及搜索 最近我一直在研究 Lucene.net ,发现Lucene.net对数据库方面建索引的文章在网上很少见,其实它是可以...
  • 建立索引的好处真的很多很多,但是貌似大家都忽视了。。。 <br />索引主要是针对经常用作where条件的字段去建立,这可以有效节省查询时间。 <br />如何建立索引:  就象许多的PHP开发者一样,...
  • 数据库索引的定义:索引数据库表中一列或者多列
  • 数据库索引类型及实现方式

    万次阅读 2017-07-12 18:43:23
    数据库索引类型及实现方式 1、索引定义  数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引是对数据库表中一个或多个列(例如,employee 表的姓氏 (lname) 列)的值进行排序的结构。如果想...
  • 数据库索引

    千次阅读 多人点赞 2019-08-20 22:49:54
    文章目录数据库索引定义优缺点索引类型建立普通索引或组合索引适合建立索引的情况索引失效的sql 定义 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。数据库索引...
  • 数据库索引 建立方法

    千次阅读 2016-04-20 15:08:18
    建立索引之后查找和修改,排序等操作可以省很多时间。 索引是对数据库表中一个或多个列(例如,employee 表的姓名 (name) 列)的值进行排序的结构。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行
  • 关系型数据库索引类型

    千次阅读 2016-09-18 21:46:45
    索引类型分类: 1.主索引:主索引是一种只能在数据库表中建立不能在自由表中建立的索引。在指定的字段或表达式中,主索 引的关键字绝对不允许有重复值。 2.候选索引:和主索引类似,它的值也 不允许在指定的字段或...
  • 各种Oracle索引类型介绍 逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上: Partitioned 分区索引 NonPartitioned ...
  • 3>什么情况下不适合建立索引?  1.对于在查询过程中很少使用或参考的列,不应该创建索引。  2.对于那些只有很少数据值的列,不应该创建索引。  3.对于那些定义为image,text和bit数据类型的列,不应该创建索引...
  • 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 ...
  • 修改Mysql数据库索引

    千次阅读 2019-06-20 11:16:06
    修改Mysql数据库的索引案例描述解决说明建立索引删除索引 案例 描述 原来的数据库索引 UNIQUE KEY `tablename` (`column1`,`column2`) USING BTREE 需要把column1,column2进行调换(以便使用column1进行百万级别...
  • 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 [索引名](字段名1 [(长度)] [ASC |
  • 数据库索引

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

    万次阅读 2017-09-18 13:38:09
    MYSQL数据库索引类型包括普通索引,唯一索引,主键索引与主键索引,这里对这些索引的做一些简单描述: (1)普通索引 这是最基本的MySQL数据库索引,它没有任何限制。它有以下几种创建方式: 创建索引 CREATE ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 229,982
精华内容 91,992
关键字:

数据库建立索引索引类型