精华内容
下载资源
问答
  • [摘要]索引是数据库中建立记录间有规律排序的主要方式,它可以显著提高文件的操作速度。当数据库 中记录的数目和数据量很大的时候,顺序查找速度会明显下降。为了提高查找速度,必须对文件建立索 引。数据库索引的设计...
  • 数据库索引

    2020-08-30 17:04:10
    一、什么索引及其分类 索引对数据库表中一个或多个列(例如,employee 表的姓名 (name) 列...在数据库,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: CREATETABLEmytable( IDI...

    一、什么是索引及其分类

    索引是对数据库表中一个或多个列(例如,employee 表的姓名 (name) 列)的值进行排序的结构。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。“索引”这个词其实和我们以前接触的书本目录其实是一个概念,其最主要作用就是为了加快数据的查询速度。

    下面介绍几种常见的MySQL索引类型。在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL 
     
    );  

    我们随机向里面插入了10000条记录,其中有一条:5555, admin。

    在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

    索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

    MySQL索引类型包括:

    (1)普通索引

    这是最基本的索引,它没有任何限制。它有以下几种创建方式:

    1)创建索引 

    CREATE INDEX indexName ON mytable(username(length));

    如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

    2)修改表结构

    ALTER mytable ADD INDEX [indexName] ON (username(length));

    3)创建表的时候直接指定

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    INDEX [indexName] (username(length))  
     
    );

    删除索引的语法:

    DROP INDEX [indexName] ON mytable;

    (2)唯一索引

    它与前面的普通索引类似,不同点是:索引列的值必须唯一(不允许具有索引值相同的行),但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

    1)创建索引

    CREATE UNIQUE INDEX indexName ON mytable(username(length));

    2)修改表结构 

    ALTER mytable ADD UNIQUE [indexName] ON (username(length));

    3)创建表的时候直接指定

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    UNIQUE [indexName] (username(length))  
     
    );

     

    (3)主键索引

    它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    PRIMARY KEY(ID)  
     
    );

    当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

    (4)组合索引

    为了形象地对比单列索引和组合索引,为表添加多个字段:

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    city VARCHAR(50) NOT NULL,  
     
    age INT NOT NULL 
     
    );  
    为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:
    
    ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

    建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

    如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

    建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

    usernname,city,age  
    usernname,city  
    usernname  
    

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

    SELECT * FROM mytable WHREE username="admin" AND city="郑州";
    SELECT * FROM mytable WHREE username="admin";

    而下面几个则不会用到:

    SELECT * FROM mytable WHREE age=20 AND city="郑州";
    SELECT * FROM mytable WHREE city="郑州";
    
    (5)建立索引的时机

    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

    SELECT t.Name 
     
    FROM mytable t LEFT JOIN mytable m   
     
    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 

    此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

    SELECT * FROM mytable WHERE username like'admin%';
    而下句就不会使用:
    
    
    SELECT * FROM mytable WHEREt Name like'%admin';
    
    因此,在使用LIKE时应注意以上的区别。

    (6)索引的缺点:

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    1)虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

    2)建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

    索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    (7)使用索引的注意事项

    使用索引时,有以下一些技巧和注意事项:

    1)索引不会包含有NULL值的列

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

    2)使用短索引

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

    3)索引列排序

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

    4)like语句操作

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

    5)不要在列上进行运算

    select * from users where YEAR(adddate)<2007;

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate<‘2007-01-01’;
    6)不使用NOT IN和<>操作

    二、索引原理

            想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b+ tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

    1、首先来聊聊聚集索引

            我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。

    上图就是带有主键的表(聚集索引)的结构图。其中树的所有结点(底部除外)的数据都是由主键字段中的数据构成,也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句:

    select * from table where id = 1256;

    首先根据索引定位到1256这个值所在的叶结点,然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节, 但是从上图能看出,树一共有三层, 从根节点至叶节点只需要经过三次查找就能得到结果。如下图

    假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n)最坏时间复杂度,这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,以现在磁盘的IO能力和CPU的运算能力, 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。换言之,查找次数是以树的分叉数为底,记录总数的对数,用公式来表示就是

    用程序来表示就是Math.Log(100000000,10),100000000是记录数,10是树的分叉数(真实环境下分叉数远不止10), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

    然而, 事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

    2、常规索引

    讲完聚集索引 , 接下来聊一下非聚集索引, 也就是我们平时经常提起和使用的常规索引。

    非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段,假如给user表的name字段加上索引 ,那么索引就是由name字段中的值构成。在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ,那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。如下图

     

    每次给字段建一个新索引,字段中的数据就会被复制一份出来用于生成索引。 因此,给表添加索引会增加表的体积,占用磁盘存储空间。

    聚集索引和聚集索引的区别在于,通过聚集索引可以查到需要查找的数据,而通过非聚集索引可以查到记录对应的主键值 ,再使用主键的值通过聚集索引查找到需要的数据。如下图

    不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。

    然而, 有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

    先看下面这个SQL语句

    //建立索引

    create index index_birthday on user_info(birthday);

    //查询生日在1991年11月1日出生用户的用户名

    select user_name from user_info where birthday = '1991-11-1'

    这句SQL语句的执行过程如下

    首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

    然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

    最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

    我们把birthday字段上的索引改成双字段的覆盖索引

    create index index_birthday_and_user_name on user_info(birthday, user_name);

    这句SQL语句的执行过程就会变为

    通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

    数据库索引的大致工作原理就是像文中所述, 然而细节方面可能会略有偏差,这但并不会对概念阐述的结果产生影响 。

     

    https://zhuanlan.zhihu.com/p/23624390

    https://blog.csdn.net/weixin_42181824/article/details/82261988

    展开全文
  • 数据库索引和约束

    千次阅读 2015-01-04 22:14:15
    SQL Server引入索引主要是为了提高查询的效率,它能够加速ORDER BY和GROUP BY子句的操作,保证数据的唯一性,并加快表连接的速度。 索引的概念和创建原则 索引就是一个指向表数据的指针,如果我们把书比作数据...

    索引特征及概念

    索引是最为常用的改善数据库性能的技术。SQL Server引入索引主要是为了提高查询的效率,它能够加速ORDER BYGROUP BY子句的操作,保证数据的唯一性,并加快表连接的速度

    索引的概念和创建原则

    索引就是一个指向表中数据的指针,如果我们把书比作数据,那么各个章节就相对于数据库中的表,而书的目录就是索引了。

    索引是在基本表的列上建立的一种数据库对象,它和基本表分开存储,它的建立或撤销对数据的内容毫无影响。索引一经创建,就完全由系统自动选择和维护,不需要用户指定使用索引,也不需要用户执行打开索引或进行重新索引等操作。

    在创建索引时,务必注意下列问题

    1)基本表的记录数量越多,记录越长,越有必要创建索引,这对加快查询速度十分有效。

    2)索引是为了加快查询速度而创建的,但同时会影响基本表的插入、删除或修改等更新活动的速度。因此只有那些对数据量大、查询频度高、实时性要求强的基本表才创建索引,且一张基本表上不要创建过多的索引。

    3)索引是建立在基本表列上的对象,对哪个列进行索引,通常根据列在WHEREORDER BYGROUP BY子句中出现的频率决定。

    不适合建索引的列的情况

    1包含太多重复值的列

    (2)查询中很少被引用的列

    (3)值特别长的列

    (4)具有很多null值的列

    (5)需要经常插入、删除、修改的列

    (6)记录较少的基本表

    (7)需要进行频繁的大批量数据更新的基本表

    索引的类型

    从两个方面分类

    1、从列的使用角度可将索引分为单列索引、唯一索引、复合索引三类。
    2、从是否改变基本表记录的物理位置角度可分为聚集索引和非聚集索引两类。

    分类
    1、单列索引

          是对基本表的某一单独的列进行索引,通常应对每个基本表的主关键字建立单列索引。

    2、唯一索引

          一旦在一个或多个列上建立了唯一索引,则不允许在表中相应的列上插入任何相同的取值。

    3、复合索引

          是针对基本表中两个或两个以上列建立的索引。

    4、聚集索引

          采用聚集索引会改变基本表中记录的物理存储顺序。

    5、非聚集索引

          采用非聚集索引,表中记录的物理顺序与索引顺序不同,即表中的记录仍按实际插入的先后顺序排列,不按索引列排序。

    聚集和非聚集索引的对比

    1、存取速度

          从建立了聚集索引的表中取出数据要比建立了非聚集索引的表快,但相对而言,聚集索引会降低向表中插入、删除和修改数据的速度。

    2、索引的数据         

          对聚索引的主要限制是每个表只能建立一个聚集索引,但是一个表可以有不止一个非聚集索引。

    3、所需空间
          非聚集索引需要较多的硬盘空间和内存

    创建和删除索引

    1、使用企业管理器方法

             1)通过点击“钥匙”图标创建,这种方式将在所选定的一个或多个列上创建一个聚集索引,是表的主关键字索引。

             2)即使用索引管理菜单创建、修改、撤销索引。

                         鼠标右击——〉所有任务——〉管理索引

    2、使用Transact SQL创建索引

         Transact SQL使用create index命令创建索引,常用的语法结构如下:

         create [unique]  [clustered|nonclusteredindex <索引名

                     on   <表名> (<列名1> [次序] [<列名2>[次序]]···)

         说明:

              unique:为表或视图创建唯一索引(不允许存在索引值相同的两行)。

              clustered:聚集索引。

              nonclustered:非聚集索引,默认值

              索引名:在实际使用时,,用户并不需要知道索引名,但在创建阶段,索引名应符合SQL Server的命名规则,并且在整个数据库中,索引名不能重复。

         例如:create unique clusteredindex myindex1

                   on  成绩表(学号,课程号)

    3、使用Transact SQL删除索引

          语法结构:drop indextable_name.index_name [,···n]

    约束

    设计数据库完整性的目的是为了防止数据库存在不符合语义的数据,防止错误信息的输入和输出。SQL Server提供的用来实施数据完整性的途径主要是约束(Constraint)、标识列(Identity Column)、默认(Default)、规则(Rule)、触发器(Trigger)、数据类型(Data Type)、索引(Index)和存储过程(Stored Procedure)

    使用约束实施数据的完整性

    约束的用途是限制用户输入到表中的数据的值的范围,一般分为列级约束和表级约束两种。要浏览某张表格上所有约束的信息,可以使用存储过程:

               sp_helpconstraint 表名

    (1)  primary key  约束

         特征:

           a、创建primary key约束时,SQL Server会自动创建一个唯一的聚集索引;

           b、定义了primary key约束的字段的取值不能重复,并且不能取null值;

           c、每个表只能定义一个primary key约束;

           d、如果表中已经有了聚集索引,那么在创建primary key约束之前,要么指定所创建的是非聚集索引,要么删除已有的聚集索引


    创建表级primary key约束。

          create table 学生表(

             学号  char(8)  not null,

             姓名 char(8) null,

            constraint  pk_学生表  primary key  (学号));

    创建列级primary key 约束

          create table 学生表(

        学号 char(8)  not null,

        constraint pk_学号 primary keynonclustered(学号),

         姓名  char(8) null);

    (2) Foreign  Key约束

        特征:

         a、一旦Foreign Key约束定义了某个字段,则该字段的取值必须参照(Reference)同一表或另一表中的Primary Key约束或Unique约束。

         bForeign Key约束不能自动建立索引。

     

     create table  成绩表1(

           学号 char(8) not null constraintpk_no references 学生表(学号),

           成绩 int);

    (3) default 约束

      特征:

          a.每一个字段只能有一个Default约束;

          b.default约束不能放在IDENTITY字段上或者timestamp字段上。

    alter  table 成绩表

           add constraint df_成绩 default 0 for 成绩

    5  create  table 成绩表2(

             学号 char(8) not null,

              课程代码 char(7) not null,

              成绩 float constraint  df_score default 0.00);

    (4)Unique约束(用于不是主键但又要求不能有重复值出现的字段)   特征:

        a.一个表可以有多个Unique 约束;

        b. 按照Unique约束的要求,在一个表中不允许受约束列的字段上有相同的null值,因此最好将被定义了Unique约束的列定义为非空(允许被约束列的值为空)

        c.创建Unique约束时,系统自动创建了非聚集索引。

    alter table 学生表

             add constraint un_name unique(姓名)

    create table abc(

              学号 char(8) not null  constraint un_no unique,

              姓名 char(8));

    create table cbd(

                      学号 char(8) not null,

                      姓名 char(8),

                      constraint  un_xh unique(学号));

    (5)Check约束

         特征:

         a.限制了向特定的字段列输入数据的类型;

         b.表级定义的Check约束可以对多个字段列进行核查。

    create table efg(

                学号 char(8)not null,

                姓名 char(8) not null,

                年龄 int,

               constraint  chk_age check (年龄>17 and 年龄<25));

    10  create table  efg1(

               学号 char(8) not null,

               姓名 char(8) not null,

              年龄 int constraint chk_age1 check(年龄>17 and 年龄<25));

    (6)删除约束

    如果需要将某个约束删除,可以使用alter table约束,其语法格式是:

         alter table 表名

          drop constraint 约束名

    展开全文
  • 索引是一种特殊的文件,包含着对数据表所有的记录的引用指针,数据库索引好比一本书前面的目录,能加快数据库的查询速度,数据库索引就是为了提高表的搜索效率而对某些字段的值建立的目录。 2.索引的作用: ...

    1.索引的概念:
    索引是一种特殊的文件,包含着对数据表中所有的记录的引用指针,数据库索引好比是一本书前面的目录,能加快数据库的查询速度,数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

    2.索引的作用:
    建立索引的目的是加快对表中记录的查找或排序,为表设置索引要付出代价;一是增加了数据库的存储空间,二是在插入和修改数据时要花费更多的时间(因为索引也会随之改变)。
    (1),设置合适的索引之后,数据库利用各种快速的定位技术,可以大大加快数据的查询速度,这也是创建索引的最主要的原因。
    (2)当表很大的时候,或者查询涉及到多个表时,使用索引可以使查询速度快上成千倍。
    (3)可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本。
    (4)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
    (5)在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间。
    3.索引的分类
    MySQL的索引分为以下几类:
    (1)普通索引,这是最基本的索引类型,而且它没有唯一性之类的限制。
    (2)唯一索引,这种索引和前面的‘普通索引’基本相同,但是有一个区别;索引列表的所有值只能出现一次,即必须唯一,当现有数据中存在重复的键值时,大多数数据库都不允许新创建的唯一索引与表一起保存,数据库还可以防止添加将再表中创建重复键值的新数据。
    (3)主键索引,主键索引时一种唯一性索引,但它必须指定为‘PRIMARY KEY(字段)’在数据库中为表定义主键将自动创建主键索引,主键索引是唯一的特定类型,该索引要求主键中的每一个值都唯一。
    (4)全文索引,索引类型为(FULLTEXT),全文索引可以在CHAR VARCHAR 或者TEXT类型的列上创建。
    (5)单列索引与多列索引,索引可以是单列上创建索引,也可以是在多列上创建的索引,多列索引可以区分其中一列可能有相同的行。如果经常同时搜索两列或者多列排序时,索引也时很有帮助的。
    4.创建索引的原则依据。
    索引可以提升数据库查询的速度,因为索引会占用数据库的系统资源不是任何情况下都需要建立索引的,数据库查询会先查询索引查询。
    (1)表的主键、外建必须有索引,主键具有唯一性,索引值也是唯一性,查询时可以快速定位到数据行,外键一般关联的是另一个表的主键,所有在多表查询时也可以快速定位。
    (2)数据量过300行的表应该有索引,数据量较大的时候,如果没有索引,需要把表遍历一遍,严重影响数据库的性能。
    (3)经常与其他表进行表连接的表,在连接字段上应该建立索引。
    (4)唯一性太差的字段不适合建立索引,如果索引字段的数据唯一性太差,是不适合创建索引。
    (5)更新太频繁的字段不合适创建索引,在表中进行增加、删除、修改操作时,索引也有相应操作产生、字段更新得过于频繁,对于系统资源占用也会更多。
    (6)经常出现在where(条件判断)字句中的字段,特别是大表的字段,应该建立索引。
    (7)索引应该建立在选择性高的字段上,如果很少的字段拥有相同值,即有很多独特值,则选择性很高。
    (8)索引应该建在小字段上,对于大的文本段甚至超长字段,不要建索引。

    5.创建索引的方法;
    (1)创建普通索引,命令格式是:
    CREATE INDEX (索引名字)ON tablename(列的列表)

    mysql> create index salary_index (索引名)on sys.IT_salary(name)(那个库的表里的字段);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    mysql> show index from IT_salary;查看索引方法

    在这里插入图片描述

    explain select * from IT_salary;
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | IT_salary | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

    各属性含义:
    id: 查询的序列号
    select_type: 查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询
    SIMPLE:查询中不包含子查询或者UNION
    查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY
    在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERY
    table: 输出的行所引用的表
    type: 访问类型

    从左至右,性能由差到好
    

    1.ALL: 扫描全表
    2.index: 扫描全部索引树
    3.range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
    4.ref: 使用非唯一索引或非唯一索引前缀进行的查找
    (eq_ref和const的区别:)
    5.eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    6.const, system: 单表中最多有一个匹配行,查询起来非常迅速,例如根据主键或唯一索引查询。system是const类型的特例,当查询的表只有一行的情况下, 使用system。
    7.NULL: 不用访问表或者索引,直接就能得到结果,如select 1 from test where 1
    possible_keys: 表示查询时可能使用的索引。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引
    key: 显示MySQL实际决定使用的索引。如果没有索引被选择,是NULL
    key_len: 使用到索引字段的长度
    注:key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
    ref: 显示哪个字段或常数与key一起被使用
    rows: 这个数表示mysql要遍历多少数据才能找到,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的
    Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
    1.
    .Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,这个叫覆盖索引。
    .Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where。
    Key_name对应的是索引名字,显示名字是salary_index的索引已经存在,NOnunique对应值是1,表示不是唯一性索引。

    (2)创建唯一索引,命令格式:

    CREATE UNIQUE INDEX(索引名字) ON tablename(表名)(列的列表,字段);
    

    该索引比普通索引多了一个关键字UNIQUE关键字。

    mysql> create unique index salary (索引名字)on IT_salary(表)(id);//将这个表中的ID号设置为唯一索引名字为salary
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    它的NOn_unique值为0表示唯一索引’
    (3)创建主机索引两种方式
    1.1:一种是在创建表的同时创建主键,主键索引会自动创建,命令格式:

    CREATE TABLE tablename(表名)([...],PRIMARY KEY(列的列表))

    1.2:已经创建了表,没有指定主键,然后修改表加入主键,主键索引会自动创建命令如下:

    ALTER TABLE (表名) ADD PRIMARY KEY(列的类表,字段)

    在这里插入图片描述

    (4)在mysql中使用全文索引(FULLTEXT index),目前只有使用myiSAM类型表的时候有效(msISAM是默认的表类型)全文索引建立在TEXT、CHAR或者VARVHAR类型的字段或者字段组合上,创建表时指定或修改表时指定全文搜索,命令如下:

    CREATE TABLE 表名(列名 TEXT,FULLLTEXT(列名)) enginde=MyISAM;
    
    ALTER TABLE 表名 ADD FULLTEXT(列名);
    
    CREATE TABLE 表名(列名字1 TEXT,FULLTEXT(列名1)) engine=MyISAM;
    

    (5)多列索引只需要在创建索引时指定多少列即可;

    mysql> create index salary_age_index (索引名字)on IT_salary(age列1,type列2);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    (6)不使用索引时,应该删除索引

    1.DROP INDEX 索引名 ON 表名;删除索引
    2.ALTER TABLE  表名 DROP INDEX l列;
    3.ALTER TABLE 变名 DROP PRIMARY KEY;
    

    第一条命令是直接删除索引,第二条命令是修改表时删除索引,第三条是删除主键索引。
    1.第一种直接删除索引。
    在这里插入图片描述
    2.修改表时删除索引。
    在这里插入图片描述
    3.删除主键索引。
    在这里插入图片描述
    (7)查看索引方法:

    SHOW INDEX FROM 表名;或者SHOW KEYS FROM 表名。
    
    

    在这里插入图片描述

    展开全文
  • 建立索引的目的加快对表记录的查找或排序。为表设置索引要付出代价的:一增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。数据库索引就是为了提高表的搜索效率而对...

    基本特点

    建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。
    创建索引可以大大提高系统的性能。第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
    因为,增加索引也有许多不利的方面。第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    基础语法

    • 创建索引语法
    db.集合名.createIndex(待创建索引的列 [,额外选项])
    

    参数:

    待创建的列:{键:1…键:-1}
    说明:1升序,-1降序,例如{age:1}表示创建age索引并按照升序的方式存储
    额外选项:设置索引的名称或者唯一索引等等

    • 删除索引语法
    1. 全部删除
    db.集合名.dropIndexes()
    
    1. 删除指定
    db.集合名.dropIndex(索引名)
    
    • 查看索引语法
    db.集合名.getIndexes()
    

    简单练习

    准备数据

    向数据库中增加十万条数据

     use test5
     for(var i=0;i<100000;i++){
     	db.c1.insert({name:"aaa"+i,age:i});
     }
    

    在这里插入图片描述

    创建普通索引

    1 . 给name列创建普通索引并查看所有索引

    //创建普通索引
    db.c1.createIndex({name:1})
    //查看所有索引
    db.c1.getIndexes()
    

    在这里插入图片描述

    2.删除name索引并查看

    重点:先查看所有索引获取要删除的索引名
    在这里插入图片描述

    //删除name列的索引
     db.c1.dropIndex('name_1')
    //查看所有索引
     db.c1.getIndexes()
    

    在这里插入图片描述
    3.给name列创建索引并命名为name_czy(自定义)

    //给name列创建索引并命名为name_czy
     db.c1.createIndex({name:1},{name:"name_czy"})
    //查看所有索引
     db.c1.getIndexes()
    

    在这里插入图片描述

    创建复合索引

    一次性给多个字段添加索引

    db.集合名.createIndex({键1:存储方式,键2:存储方式...})
    

    给name和age两个字段添加索引

    db.c1.createIndex({name:1,age:1})
    db.c1.getIndexes()
    

    在这里插入图片描述

    创建唯一索引

    语法

    db.集合名.createIndex(待添加的索引的列, [unique:列名])
    

    给name添加普通索引

     //删除所有索引
     db.c1.dropIndexes()
     //创建唯一索引
     db.c1.createIndex({name:1},{unique:"name"})
    

    在这里插入图片描述

    分析索引

    语法

    db.集合名.find().explain('executionStats')
    

    扫描方式
    COLLSCAN 全表扫描
    IXSCAN 索引扫描
    FETCH 根据索引去检索指定document

    1. age列未添加索引扫描
    db.c1.find({age:18}).explain('executionStats')
    

    在这里插入图片描述

    1. age列添加索引扫描
    //添加普通索引
    db.c1.createIndex({age:1})
    //扫描
    db.c1.find({age:18}).explain('executionStats')
    

    在这里插入图片描述

    选择规则

    • 为常做条件、排序、分组的字段建立索引
    • 选择唯一性索引
    • 选择较小的数据列,为较长的字符串使用前缀索引
    展开全文
  • 索引Index

    2017-11-21 17:35:00
    索引是数据库一列或多列的值进行排序的一种结构为了提高查询的效率索引一般建立在需要经常查询的地方 优点 创建索引可以大大提高系统的性能第一,通过创建唯一性索引,可以保证数据库每一行数据的唯一性...
  • 内容主要集中大多数企业常见的问题之上,如安装和升级到oracle database 11g数据库软件、创建数据库、导出和导入数据、数据库的备份与恢复、性能调优,等等。  本书还提供了dba完成本职工作必备的基本的uniix...
  • 可以实现单表不同的数据库中移动。 # 4.空间可以回收(除drop table操作处,表空不能自已回收) # 缺点: # 单表增加过大,如超过100G # 结论: # 共享表空间Insert操作上少有优势。其它都没独立表空间表现好。...
  • 为了提高查询排序的速度 适合添加索引的情况: 索引需要单独文件来保存维护。 表数据发生变化需要维护索引表。 适合添加索引的情况: 表数据量足够大; 增删改操作较少; 高基数列 建立索引的优点: 1. 可以大大加快...
  • 有两部分,请分别下载。 基本信息 作者: 赵振平 [作译者介绍] 出版社:电子工业出版社 ISBN:9787121061899 上架时间:2008-5-30 出版日期:2008 年5月 ... 耗时3年,作者曾为...实务15 linux(unix)环境...
  • 有两部分,请分别下载。 基本信息 作者: 赵振平 [作译者介绍] 出版社:电子工业出版社 ISBN:9787121061899 上架时间:2008-5-30 出版日期:2008 年5月 ... 耗时3年,作者曾为...实务15 linux(unix)环境...
  • 数据库课程设计(基于B/S)

    热门讨论 2008-12-28 15:28:06
    课程设计任务书 题目名称 数据库原理及设计课程设计 学生学院 经济管理学院 专业班级 06级电子商务5班 ...4.1设计索引:我们可以最经常查询的列上建立索引提高查询效率。 而这个系统,我们经常要按...
  • 3.4.2 Oracle数据库中的存储层次体系 87 3.4.3 字典管理和本地管理的表空间 90 3.5 临时文件 92 3.6 控制文件 94 3.7 重做日志文件 94 3.7.1 在线重做日志 95 3.7.2 归档重做日志 97 3.8 密码文件 99 3.9 ...
  • 注意:ReceivedDate列指的订单录入系统(即保存到数据库中)的日期,而不是消费者接收到其所订购书籍的日期。 TITLE4(Name, ISBN, CallNumber, Year, AuthorFName, AuthorLName); PK(CallNumber) MEMBER4(LibId,...
  • 6.5.5 备用数据库中的块更改跟踪支持 248 第7章 Data Pump 249 7.1 API增强 249 7.2 压缩增强 250 7.3 加密 253 7.3.1 指定加密 254 7.3.2 选择加密方式 254 7.4 重用转储文件 256 7.5 重映射数据 257 7.6...
  • 3.4.2 Oracle数据库中的存储层次体系 87 3.4.3 字典管理和本地管理的表空间 91 3.5 临时文件 93 3.6 控制文件 95 3.7 重做日志文件 95 3.7.1 在线重做日志 96 3.7.2 归档重做日志 98 3.8 密码文件 100 3.9 ...
  • 5Java性能优化五.zip

    2019-12-24 15:06:30
    四。数据库优化 对绝大部分应用系统而言。数据库是不可缺少的一部分。JAVA程序能够使用JDBC的方式连接数据库。...能够使用行数据来水平分割,为了提高数据库的查询效率,能够建立有效且事宜的索引
  • 找出网站管理员的名字和邮件地址并把它添加到数据库中。通过这个方法,仔细查看竞争者的所有链接,找出那些想与之交换链接的网站,并建立自己的数据库。  完成了这项工作后,站点上建立一个链接网页,把这些网站...
  • 物联网仪表板 这项工作是在沃州管理与工程学院(HEIG-VD-沃顿州立高等工业学院)取得的学士... 性能一直一个重要的问题,并通过服务器和客户端之间进行聚合,建立索引和分配负载来提高性能。 用户可以使用交叉过
  • 最后一方面根据相似度算法,计算用户提问与索引数据库中记录的相似度大小,3提取出满足阈值的记录作为结果,按照相似度降序的方式输出。 为了进一步提高检索的准确性,许多系统结合相关反馈技术来收集用户对检索...
  • lucene4.2自带demo

    2017-04-01 14:06:07
    因为lucene全文搜索引擎,所以它比较擅长从一个词语反过来找到那个词哪篇文章反着的,假如用数据,从2000个字like那个字段效率很低,而lucene通过生成索引反过来的方式,这样可以提高查询的效率。...
  • 用来建立数据库连接的唯一服务名。如果要没有调度程序的情况下仍能连接到数据库, 请将该值设置为与例程名相同。此参数自 8.1.3 版起已废弃。 值范围: 根据操作系统而定。 默认值 :0 mts_sessions: 说明 : 指定...
  • 最后,第8章介绍如何Java中建立多线程程序。  第二部分:应用标准Java类  下一部分挖掘Java库(用Java的话来说就是软件包)的内涵。第9章简单概述不同Java软件包及其零件;第10章介绍Java的基本动画功能;第11...
  •  CONNECT, RESOURCE, DBA这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划,而不要简单的使用这些预定角色。将来的版本这些角色可能不会作为预定...
  • sql总结.doc

    2019-08-27 22:08:51
    存储过程(Stored Procedure)一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 (2)存储过程的基本写法...
  • 数据库初期设计时一定要谨慎,把所有可能的情况都考虑进去,即使当时没有用到,也要将它留在数据库中作为备用字段以便将来扩充。 程序一旦开始编码,就应该尽量避免再修改数据库。因为如果数据库结构一旦改变,所有...
  • Oracle专家高级编程--详细书签版

    热门讨论 2012-08-21 11:57:09
    当然,所有问题都存储Oacle数据库中,Oracle站点上都能得到.我把时间主要都花费帮助用户成功地使用Oracle数据库上.我的空闲时间里,我Oracle公司内部构建应用程序,开发软件.  本书我每天工作的结晶.本书的...
  • 5.2.3 在索引创建过程允许用户表访问 176 5.3 索引选项 177 5.3.1 使用索引INCLUDE 177 5.3.2 使用PAD_INDEX和FILLFACTOR 178 5.3.3 禁用页和/或行索引锁定 178 5.4 管理超大型索引 179 5.4.1 ...
  • (39) 关系数据库中,用来表示实体之间联系的(D) A. 树结构 B. 网结构 C. 线性表 D. 二维表 (40) 将E-R图转换到关系模式时,实体与联系都可以表示成(B) A. 属性 B. 关系 C. 键 D. 域 (41) 下列选项中,哪个不是...

空空如也

空空如也

1 2 3 4
收藏数 65
精华内容 26
关键字:

在数据库中建立索引主要是为了提高