精华内容
参与话题
问答
  • 聚簇索引和非聚簇索引的区别

    万次阅读 多人点赞 2018-07-25 10:39:04
    参考链接:和刚入门的菜鸟们聊聊--什么是聚簇索引与非聚簇索引 MYSQL索引:对聚簇索引和非聚簇索引的认识 一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。 在《数据库原理》一书中是这么...

    参考链接:和刚入门的菜鸟们聊聊--什么是聚簇索引与非聚簇索引       MYSQL索引:对聚簇索引和非聚簇索引的认识

    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:
    聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    因此,MYSQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。
    下面,我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构。

    如原始数据为:

    MyISAM引擎的数据存储方式如图:

    MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。
    从MYISAM存储的物理文件我们能看出,MYISAM引擎的索引文件(.MYI)和数据文件(.MYD)是相互独立的。

    而InnoDB按聚簇索引的形式存储数据,所以它的数据布局有着很大的不同。它存储数据的结构大致如下:

    注:聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC)和余下的列(如col2)。

    INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值,而不是行指针(row pointers),这减小了移动数据或者数据页面分裂时维护二级索引的开销,因为InnoDB不需要更新索引的行指针。其结构大致如下:

    INNODB和MYISAM的主键索引与二级索引的对比:

    InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此,通过二级索引查询首先查到是主键值,然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合,叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别,主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引,且MYISAM引擎中可以不设主键。

     

    【聚簇索引】
    平时习惯逛图书馆的童鞋可能比较清楚,如果你要去图书馆借一本书,最开始是去电脑里面查书名然后根据书名来定位藏书在那个区,哪个书柜,哪一行,第多少本。。。清晰明确,一目了然,因为藏书的结构与图书室的位置,书架的顺序,书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的,所以很容易找到。比如,你的目标藏书在C区2柜3排5仓,那么你走到B区你就很快知道前面就快到了C区了,你直接奔着2柜区就能找到了。 这就是雷同于聚簇索引的功效了,聚簇索引,实际存储的循序结构与数据存储的物理机构是一致的,所以通常来说物理顺序结构只有一种,那么一个表的聚簇索引也只能有一个,通常默认都是主键,设置了主键,系统默认就为你加上了聚簇索引,当然有人说我不想拿主键作为聚簇索引,我需要用其他字段作为索引,当然这也是可以的,这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引,然后再设置主键,这样就木有问题啦。
    总而言之,聚簇索引是顺序结构与数据存储物理结构一致的一种索引,并且一个表的聚簇索引只能有唯一的一条;

     

    【非聚簇索引】
    同样的,如果你去的不是图书馆,而是某城市的商业性质的图书城,那么你想找的书就摆放比较随意了,由于商业图书城空间比较紧正,藏书通常按照藏书上架的先后顺序来摆放的,所以如果查询到某书籍放在C区2柜3排5仓,但你可能要绕过F区,而不是A.B.C.D...连贯一致的,也可能同在C区的2柜,书柜上第一排是计算机类的书记,也可能最后一排就是医学类书籍;

    那么对照着来看非聚簇索引的概念就比较好理解了,非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;

     

     

    动手试试:看看代码怎么敲的

    建立索引之前选好表对象,假设表明为IndexTestTable此表中包含三个字段Id,Name,UniqueCode

    为了更快的进行姓名查询,我们可以在Name字段上添加非聚簇索引;

    创建索引的格式如下:

    CREATE NONCLUSTERED INDEX [index_name【索引名称】] ON [table_name【表名称】]([column_name1【列名称】],[column_name2【列名称】],...);

    我们给IndexTestTable表的Name字段添加一个非聚簇索引

    CREATE NONCLUSTERED INDEX IndexTestTable_index_name ON IndexTestTable(Name);

    给IndexTestTable表的UniqueCode字段添加一个聚簇索引

    CREATE CLUSTERED INDEX IndexTestTable_index_uniquecode ON IndexTestTable(UniqueCode)

    以上的代码是最简单最直接设置索引的方式,而通常实际应用中,会有多字段联合添加索引的情况,这个就需要你根据实际的应用查询场景,以及在where条件下最常用的查询字段,例如:在 TableX中你最经常查询的条件为:

    SELECT Name,Message 
    FROM TableX 
    WHERE 1=1 
    AND DeptId='003523' 
    AND LimitedCondition='SomeValue' 
    

    这个时候你就可以 添加一个基于 DeptId 和 LimitedCondition 两个字段的非聚簇索引,以便于加速查询速度;

    CREATE NONCLUSTERED INDEX TableX_index_departid_limitedcondition 
    ON TableX(DeptId,LimitedCondition);
    

    简言之,就是需要根据你的实际应用场景,添加有用并且高效的索引;

    展开全文
  • Mysql聚簇索引和非聚簇索引原理(数据库)

    万次阅读 多人点赞 2016-11-02 11:29:35
    一、‘页’操作系统的关系 ①为什么要有内存管理? 我们知道,一个进程完成他的功能,需要访问磁盘加载数据到内存然后等待进入cpu运算,因为数据量大小远远大于内存大小。因此提出虚拟内存概念。虚拟内存就是将...

    一、‘页’和操作系统的关系

    ①为什么要有内存管理?

    我们知道,一个进程完成他的功能,需要访问磁盘加载数据到内存然后等待进入cpu运算,因为数据量大小远远大于内存大小。因此提出虚拟内存概念。虚拟内存就是将程序用到的数据进行划分,暂时用不到的放到磁盘里,用到的放到内存里,操作系统中总是运行着不止一个进程,各个进程有优先级顺序,所以存在进程调度问题,进程的每次调度都会导致内存和磁盘数据置换,段式内存管理页式内存管理都是基于虚拟内存概念的具体内存管理解决方案。

    ②什么是页式内存管理?

    虚拟内存位于程序和物理内存之间,程序只能看见虚拟内存,再也不能直接访问物理内存。每个程序都有自己独立的进程地址空间,这样就做到了进程隔离。这里的进程地址空间是指虚拟地址。顾名思义,既然是虚拟地址,也就是虚的,不是现实存在的地址空间。既然我们在程序和物理地址空间之间增加了虚拟地址,那么就要解决怎么从虚拟地址映射到物理地址,因为程序最终肯定是运行在物理内存中的,主要有分段和分页两种技术。

    分页机制就是把内存地址空间分为若干个很小的固定大小的页,每一页的大小由内存决定,就像Linux中ext文件系统将磁盘分成若干个Block一样,这样做是分别是为了提高内存和磁盘的利用率。

    ③页的大小为什么是4K?

    CPU位数准确地说应该是CPU一次能够并行处理的数据宽度,一般就是指数据总线宽度。(后续补充:参考http://blog.sina.com.cn/s/blog_b4ef897e0102vfw5.html)

    ④mysql索引和页的关系

    B-tree,B是balance,一般用于数据库的索引。使用B-tree结构可以显著减少定位记录时所经历的中间过程,从而加快存取速度。而B+tree是B-tree的一个变种,MySQL就普遍使用B+tree实现其索引结构。  
      一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
      为了达到这个目的,磁盘按需读取,要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。并把B-tree中的m值设的非常大,就会让树的高度降低,有利于一次完全载入。

    二、聚簇索引和非聚簇索引

    ①mysql索引

    B+Tree结构都可以用在MyISAM和InnoDB上mysql中,不同的存储引擎对索引的实现方式不同,大致说下MyISAM和InnoDB两种存储引擎。

    MyISAM的是非聚簇索引,B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,
    这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。

    InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

    B+Tree示意图

    聚集索引和非聚集索引原理图

    ②聚簇索引

    聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多。
    聚集索引这种主+辅索引的好处是,当发生数据行移动或者页分裂时,辅助索引树不需要更新,因为辅助索引树存储的是主索引的主键关键字,而不是数据具体的物理地址。

    ③非聚簇索引

    非聚集索引,类似于图书的附录,那个专业术语出现在哪个章节,这些专业术语是有顺序的,但是出现的位置是没有顺序的。每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚簇索引。


    三、Page结构

    Page是整个InnoDB存储的最基本构件,也是InnoDB磁盘管理的最小单位,与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型,常见的页类型有数据页(B-tree Node)Undo页(Undo Log Page)系统页(System Page) 事务数据页(Transaction System Page)等。单个Page的大小是16K(编译宏UNIV_PAGE_SIZE控制),每个Page使用一个32位的int值来唯一标识,这也正好对应InnoDB最大64TB的存储容量(16Kib * 2^32 = 64Tib)。

    (下章继续解读)

    展开全文
  • 聚簇索引和非聚簇索引

    千次阅读 多人点赞 2018-08-07 15:34:30
    聚簇索引并不是一种单独的索引类型,而是...MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustere...

    参考博客:http://www.admin10000.com/document/5372.html

    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体细节依赖于其实现方式。

    MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。

    这两种索引内部都是B+树,聚集索引的叶子节点存放着一整行的数据。

    Innobd中的主键索引是一种聚簇索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。

    Innodb使用的是聚簇索引,MyISam使用的是非聚簇索引

    聚簇索引(聚集索引)

    聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。

    Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。

    聚簇索引的优缺点

    优点:

    1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快

    1. 聚簇索引对于主键的排序查找和范围查找速度非常快

    缺点:

    1. 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
    2. 更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。

    3.二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

    辅助索引(非聚簇索引)

    在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。

    Innodb辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。

    辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

    Innodb聚簇索引和MyIsam非聚簇索引的比较说明

    参考博客:https://www.cnblogs.com/zlcxbb/p/5757245.html

    InnoDB索引实现

    InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同.

    1)主键索引:

    MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    (图inndb主键索引)是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    2)InnoDB的辅助索引

       InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:

    InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引(Secondary Index, 也就是非主键索引)也会包含主键列,所以,如果主键定义的比较大,其他索引也将很大。如果想在表上定义 、很多索引,则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

    文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

    不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白1、为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,2、用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

    MyISAM索引实现

    MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

    1)主键索引:

    MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图

    这里设表一共有三列,假设我们以Col1为主键,图myisam1是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

    2)辅助索引(Secondary key)

    在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

    同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

    MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

     

     

    MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

    为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

    问题:主键索引是聚集索引还是非聚集索引?

    在Innodb下主键索引是聚集索引,在Myisam下主键索引是非聚集索引

    聚簇索引和非聚簇索引的区别

    聚簇索引的叶子节点存放的是主键值和数据行,支持覆盖索引;二级索引的叶子节点存放的是主键值或指向数据行的指针。

    由于节子节点(数据页)只能按照一颗B+树排序,故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织,所以一张表可以有多个辅助索引

    展开全文
  • 索引的重要性 数据库性能优化中索引绝对是一个重量级的因素,...聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index) 最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引

    本文转自https://my.oschina.net/u/1866821/blog/297673

    索引的重要性
    数据库性能优化中索引绝对是一个重量级的因素,可以说,索引使用不当,其它优化措施将毫无意义。

    聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
    最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一页就是P开头的部分,这就是物理存储顺序(聚簇索引);而不用你到目录,找到汉字“爬”所在的页码,然后根据页码找到这个字(非聚簇索引)。

    下表给出了何时使用聚簇索引与非聚簇索引:

    动作
    使用聚簇索引
    使用非聚簇索引
    列经常被分组排序


    返回某范围内的数据

    不应
    一个或极少不同值
    不应
    不应
    小数目的不同值

    不应
    大数目的不同值
    不应

    频繁更新的列
    不应

    外键列


    主键列


    频繁修改索引列
    不应



    聚簇索引的唯一性
    正式聚簇索引的顺序就是数据的物理存储顺序,所以一个表最多只能有一个聚簇索引,因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引,所以它显得更为珍贵,一个表设置什么为聚簇索引对性能很关键。

    初学者最大的误区:把主键自动设为聚簇索引
    因为这是SQLServer的默认主键行为,你设置了主键,它就把主键设为聚簇索引,而一个表最多只能有一个聚簇索引,所以很多人就把其他索引设置为非聚簇索引。这个是最大的误区。甚至有的主键又是无意义的自动增量字段,那样的话Clustered index对效率的帮助,完全被浪费了。

    刚才说到了,聚簇索引性能最好而且具有唯一性,所以非常珍贵,必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引,这个要看实际情况。

    事实上,建表的时候,先需要设置主键,然后添加我们想要的聚簇索引,最后设置主键,SQLServer就会自动把主键设置为非聚簇索引(会自动根据情况选择)。如果你已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。

    记住我们的最终目的就是在相同结果集情况下,尽可能减少逻辑IO。
    我们先从一个实际使用的简单例子开始。

    一个简单的表:

    1. CREATE TABLE [dbo].[Table1](

    2.   [ID] [int] IDENTITY(1,1) NOT NULL,

    3.   [Da

      ta1] [int] NOT NULL DEFAULT ((0)),

    4.   [Da

      ta2] [int] NOT NULL DEFAULT ((0)),

    5.   [Da

      ta3] [int] NOT NULL DEFAULT ((0)),

    6.   [Name1] [nvarchar](50) NOT NULL DEFAULT (''),

    7.   [Name2] [nvarchar](50) NOT NULL DEFAULT (''),

    8.   [Name3] [nvarchar](50) DEFAULT (''),

    9.   [DTAt] [datetime] NOT NULL DEFAULT (getdate())

    复制代码

     

    来点测试数据(10w条):


    1. declare @i int

    2. set @i = 1 

    3. while @i < 100000 

    4. begin 

    5. insert into Table1 ([Da

      ta1] ,[Da

      ta2] ,[Da

      ta3] ,[Name1],[Name2] ,[Name3]) 

    6. values(@i , 2* @i ,3*@i, CAST(@i AS NVARCHAR(50)), CAST(2*@i AS NVARCHAR(50)), CAST(3*@i AS NVARCHAR(50)))

    7. set @i = @i + 1

    8. end

    9. update table1 set dtat= DateAdd (s, da

      ta1, dtat)

    复制代码

    打开查询分析器的IO统计和时间统计:

    1. SET STATISTICS IO ON;

    2. SET STATISTICS TIME ON;

    复制代码

    显示实际的“执行计划”:


    我们最常用的SQL查询是这样的:

    1. SELECT * FROM Table1 WHERE Da

      ta1 = 2 ORDER BY DTAt DESC;

    复制代码

    先在Table1设主键ID,系统自动为该主键建立了聚簇索引。
    然后执行该语句,结果是:

    1. Table 'Table1'. Scan count 1, logical reads 911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 16 ms, elapsed time = 7 ms.

    复制代码

     

    然后我们在Data1和DTat字段分别建立非聚簇索引:

    1. CREATE NONCLUSTERED INDEX [N_Da

      ta1] ON [dbo].[Table1] 

    2. (

    3. [Da

      ta1] ASC

    4. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

      LINE = OFF) ON [PRIMARY]

    5. CREATE NONCLUSTERED INDEX [N_DTat] ON [dbo].[Table1] 

    6. (

    7. [DTAt] ASC

    8. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

      LINE = OFF) ON [PRIMARY]

    复制代码

    再次执行该语句,结果是:

    1. Table 'Table1'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 0 ms, elapsed time = 39 ms.

    复制代码

     

    可以看到设立了索引反而没有任何性能的提升而且消耗的时间更多了,继续调整。

    然后我们删除所有非聚簇索引,并删除主键,这样所有索引都删除了。建立组合索引Data1和DTAt,最后加上主键

    1. CREATE CLUSTERED INDEX [C_Da

      ta1_DTat] ON [dbo].[Table1] 

    2. (

    3. [Da

      ta1] ASC,

    4. [DTAt] ASC

    5. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

      LINE = OFF) ON [PRIMARY]

    复制代码

    再次执行语句:

    1. Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 0 ms, elapsed time = 1 ms.

    复制代码

     

    可以看到只有聚簇索引seek了,消除了index scan和nested loop,而且执行时间也只有1ms,达到了最初优化的目的。

    组合索引小结
    小结以上的调优实践,要注意聚簇索引的选择。首先我们要找到我们最多用到的SQL查询,像本例就是那句类似的组合条件查询的情况,这种情况最好使用组合聚簇索引,而且最多用到的字段要放在组合聚簇索引的前面,否则的话就索引就不会有好的效果,看下例:

     

    查询条件落在组合索引的第二个字段上,引起了index scan,效果很不好,执行时间是:

    1. Table 'Table1'. Scan count 1, logical reads 238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 16 ms, elapsed time = 22 ms.

    复制代码

    而如果仅查询条件是第一个字段也没有问题,因为组合索引最左前缀原则,实践如下:

    1. Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 0 ms, elapsed time = 1 ms.

    复制代码

    从中可以看出,最多用到的字段要放在组合聚簇索引的前面。

    Index seek 为什么比 Index scan好?
    索引扫描也就是遍历B树,而seek是B树查找直接定位。
    Index scan多半是出现在索引列在表达式中。数据库引擎无法直接确定你要的列的值,所以只能扫描整个整个索引进行计算。index seek就要好很多.数据库引擎只需要扫描几个分支节点就可以定位到你要的记录。回过来,如果聚集索引的叶子节点就是记录,那么Clustered Index Scan就基本等同于full table scan。

    一些优化原则
    1、缺省情况下建立的索引是非聚簇索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说: 
    a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和order by、group by发生的列,可考 
    虑建立群集索引; 
    b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引; 
    c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。 

    2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语,任何一种索引都有助于SELECT的性能提高。 

    3、多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。

    4、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。 

    5、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
    Sql的优化原则2: 
    1、只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT 
    2、尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值。 
    3、尽量少用VARCHAR、TEXT、BLOB类型 
    4、如果你的数据只有你所知的少量的几个。最好使用ENUM类型  

    使用SQLServer Profiler找出数据库中性能最差的SQL
    首先打开SQLServer Profiler:

     


    然后点击工具栏“New Trace”,使用默认的模板,点击RUN。

    也许会有报错:"only TrueType fonts are supported. There id not a TrueType font"。不用怕,点击Tools菜单->Options,重新选择一个字体例如Vendana 即可。(这个是微软的一个bug)

    运行起来以后,SQLServer Profiler会监控数据库的活动,所以最好在你需要监控的数据库上多做些操作。等觉得差不多了,点击停止。然后保存trace结果到文件或者table。
    这里保存到Table:在菜单“File”-“Save as ”-“Trace table”,例如输入一个master数据库的新的table名:profileTrace,保存即可。

    找到最耗时的SQL:

    1. use master

    2. select * from profiletrace order by duration desc;

    复制代码

    找到了性能瓶颈,接下来就可以有针对性的一个个进行调优了。

    对使用SQLServer Profiler的更多信息可以参考:
    http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx

    使用SQLServer Database Engine Tuning Advisor数据库引擎优化顾问
    使用上述的SQLServer Profiler得到了trace还有一个好处就是可以用到这个优化顾问。用它可以偷点懒,得到SQLServer给您的优化顾问,例如这个表需要加个索引什么的…
    首先打开数据库引擎优化顾问:
     

    然后打开刚才profiler的结果(我们存到了master数据库的profileTrace表):

     

    点击“start analysis”,运行完成后查看优化建议(图中最后是建议建立的索引,性能提升72%)

     
    这个方法可以偷点懒,得到SQLServer给您的优化顾问。


    展开全文
  • 在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的: 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 因此,MYSQL中不同的数据存储引擎对聚...
  • 聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会... 总体来说:聚簇索引 比 非聚簇索引 要好 聚簇索引:若根据id查,直接找到数据;若根据name查,先根据name找到对应的id,然后再根据id找到数据; ...
  • 总结:InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引...
  • MYSQL索引:对聚簇索引和非聚簇索引的认识

    万次阅读 多人点赞 2016-07-17 22:03:41
    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的: 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 因此,MYSQL中不同的数据存储引擎对聚
  • 在《数据库原理》里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此,所以一个表最多只能有一个聚簇索引。 不过这个定义太...
  • MySQL中Innodb的聚簇索引和非聚簇索引

    万次阅读 热门讨论 2018-08-29 22:22:15
    数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下...
  • 聚簇索引和非聚簇索引区别的应用

    千次阅读 2014-12-17 10:56:49
     聚簇索引和非聚簇索引的一个标志性区别就是聚簇索引的叶节点对应着数据页,从中间级的索引页的索引行直接对应着数据页。而非聚簇索引的索引B+树叶节点不是直接指向数据页面的。如果表有聚集索引或索引视图上有聚集...
  • 聚簇索引和非聚簇索引区别 MySQL的聚簇索引是指Innodb引擎的特性,MySIAM并没有,如果需要该索引,只要将索引指定为主键(primary key)就可以了。 聚集(clustered)索引,也称聚簇索引。聚簇索引并不是一种单独的...
  • 这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的. 聚簇索引和非聚簇索引的概念 我们先引用官网上的一段话来看看它们是干嘛的 Every InnoDB ...
  • 聚簇索引和非聚簇索引区别对比

    千次阅读 2020-08-27 16:22:48
    非聚簇索引:将数据与索引分开存储,索引结构的叶子节点放的是指向数据的指针(或者说是地址) 在mysql中的应用 innodb的索引都是聚簇索引,其数据直接存放在叶子节点上,但是主键索引(一级索引)叶子节点中存放的...
  • MySQL聚簇索引和非聚簇索引的理解

    千次阅读 2019-07-10 00:41:45
    关于聚簇索引和非聚簇索引的概念很多同学找了很多教程但是仍然很迷糊。 这里给出一篇翻译,并给出我的配图,希望对大家理解有帮助。 英文原文:...
  • 1.聚簇索引和非聚簇索引的概念 1.1聚簇索引 将数据存储与索引放到了一块,找到了索引也就找到了数据,当表有聚簇索引时,它的数据实际上存放在索引的叶子页上,也就是B+树的叶子节点上,因为数据行不能存在两个地方...
  • 今天我们来聊一聊关于 聚簇索引和非聚簇索引的问题;  刚开始学数据库SQL的时候,就知道有主键啊(Primary-key),外键啊(Foreign-key)啥的,连个表查询就已经不清楚是要on 那几个字段了,在数据量不太大的情况下,...
  • 但是书中又把InnoDB作为聚簇索引的代表、MyISAM作为非聚簇索引的代表,两者进行比较,这就导致了矛盾点,如果将二级索引等价于非聚簇索引,那InnoDB就不能完全代表聚簇索引;但是单从二级索引的存储形式来看,又属于...
  • Mysql数据库索引的理解及聚簇索引和非聚簇索引区别 概念 索引是帮助Mysql搞笑获取数据的数据结构 对Mysql数据库来讲,其核心就是存储引擎,而索引就是属于存储引擎级别的概念,不同的存储引擎对索引的实现方式是...
  • 非聚簇索引:数据存储索引分开放,索引结构的叶子节点指向了数据的对应行,myisam通过 key_buffer 把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘...
  • 索引分为聚簇索引和非聚簇索引。以一本英文课本为例,要找第8课,直接翻书,若先翻到第5课,则往后翻,再翻到第10课,则又往前翻。这本书本身就是一个索引,即“聚簇索引”。如果要找"fire”这个单词,会翻到书...
  • mysql聚簇索引和非聚簇索引以及二级索引 innodb的聚簇索引实际上是在同一个结构中保存了btree索引和数据行。 INNODB通过主键列来索引数据。没有定义,INNODB会选择一个唯一的非空索引列代替。如果没有这样的索引,...
  • http://www.cnblogs.com/sunliyuan/p/5826419.html
  • mysql聚簇索引和非聚簇索引区别 mysql聚簇索引和非聚簇索引区别是:对于聚簇索引,表数据按顺序存储,即索引顺序和表记录物理存储顺序一致;对于非聚簇索引,表数据存储顺序与索引顺序无关。 mysql聚簇...
  • 在已创建聚簇索引的表上创建的非聚簇索引,与在未创建聚簇索引的表上创建的非聚簇索引,结构上有何区别? 简单说一下吧。 先说堆表: 没有聚集索引时,表是一个堆表,记录不按任何顺序在数据页中存储,也就是存储...
  • 聚簇索引和非聚簇索引区别是:聚簇索引的叶子节点就是数据节点 而非聚簇索引的叶子节点仍然是索引文件 只是这个索引文件中包含指向对应数据块的指针。 MySQL中不同的数据存储引擎对聚簇索引有不同的支持。 MyISA....

空空如也

1 2 3 4 5 ... 20
收藏数 19,857
精华内容 7,942
关键字:

聚簇索引和非聚簇索引