精华内容
参与话题
问答
  • MySQL中Innodb的聚簇索引和非聚簇索引

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

    聚簇索引

    数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引,因为在一个表中数据的存放方式只有一种。

    一般来说,将通过主键作为聚簇索引的索引列,也就是通过主键聚集数据。下图展示了Innodb中聚簇索引的结构(图片来自《高性能MySQL(第三版)》):

    聚簇索引的结构

    聚簇索引的结构

    这里要特别注意的概念,一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的,因此逻辑上键值相邻的页在物理上可能相隔很远。

    在中间的某个节点页中,主键<11的叶子页和11<主键<21的叶子页分别被两个指针所指向,且主键<11的叶子页也有一个指针指向了11<主键<21的叶子页,其余页之间的关系也是一样。

    聚簇索引的优点

    1. 聚簇索引将索引和数据行保存在同一个B-Tree中,查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。
    2. 聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的,

    聚簇索引的缺点

    1. 聚簇索引的更新代价比较高,如果更新了行的聚簇索引列,就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
    2. 插入速度严重依赖于插入顺序,按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入,最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
    3. 聚簇索引在插入新行和更新主键时,可能导致“页分裂”问题。
    4. 聚簇索引可能导致全表扫描速度变慢,因为可能需要加载物理上相隔较远的页到内存中(需要耗时的磁盘寻道操作)。

    非聚簇索引

    非聚簇索引,又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过二级索引查找行,存储引擎需要在二级索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。

    总结

    下面是Innodb聚簇索引和非聚簇索引的示意图(图片来自《高性能MySQL(第三版)》:

    Innodb聚簇索引和非聚簇索引

    Innodb聚簇索引和非聚簇索引

    展开全文
  • 可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引,只要将关键字存储到树的节点即可。当数据库一条...

            可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引,只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时,一棵B+树就只能存储主键,如果检索的是非主键字段,则主键索引失去作用,又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。  这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)。这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说,主键B+树在叶子节点存储指向真正数据行的指针,而非主键。

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

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

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

      我们重点关注聚簇索引,看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次B+树查找,这不是多此一举吗?聚簇索引的优势在哪?

      1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

      2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。

     

     

    注意:上图作为B+树不够精确,只是大致展示了搜索的过程,因为B+树可以使得非叶子节点中包含父节点中的值,这就可以使得索引值可以全部放到叶子节点中,B树就做不到,如果用B树存储索引值,那一些索引值只能在非叶子节点中。下图即为B+树,第一级父节点中有5,28,65,第二级非叶节点中也可以包含5,28,65,第三级也一样,最终到叶节点,对应聚簇索引就存放着索引值和数据。

     

    图片.png

     

    *回表*

    所谓回表就是在使用二级索引时,因为二级索引之存储了部分数据,如果根据键值查找到的数据不能包括全部目标数据,就需要二级索引指针,也就是键值对中的值,来找到聚簇索引的所有数据,然后根据完整的数据取出所需要列的过程。 
    覆盖索引不需要回表

    转载:https://blog.51cto.com/2839840/2057806?utm_source=oschina-app

    展开全文
  • mysql聚簇索引和非聚簇索引以及二级索引 innodb的聚簇索引实际上是在同一个结构中保存了btree索引和数据行。 INNODB通过主键列来索引数据。没有定义,INNODB会选择一个唯一的非空索引列代替。如果没有这样的索引,...

    聚簇索引:  ORANCLE 中的索引组织表;   



    如何区分聚簇索引和非聚簇索引?这里有一个比喻,来自网络: 

    举例来说明一下聚集索引和非聚集索引的区别: 

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

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

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

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







      聚簇:数据行和数据紧密的存储在一起     叶子页 vs 节点页     叶子页存数据,节点页存索引。

    innodb的聚簇索引实际上是在同一个结构中保存了btree索引和数据行。

    INNODB通过主键列来索引数据。没有定义,INNODB会选择一个唯一的非空索引列代替。如果没有这样的索引,会选择第一个非空的唯一索引代替,如果没有非空唯一索引,Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。innodb只聚集在同一个页面中的记录,包含相邻键值的页面可能会相距甚远。(物理位置)


    聚簇索引:

      ORANCLE 中的索引组织表;


    他的数据实际上存放的索引的叶子页中。    聚簇:数据行和数据紧密的存储在一起。                         叶子页 vs 节点页     叶子页存数据,节点页存索引。

    innodb的聚簇索引实际上是在同一个结构中保存了btree索引和数据行。

    INNODB通过主键列来索引数据。没有定义,INNODB会选择一个唯一的非空索引列代替。如果没有这样的索引,InnoDB会隐式定义一个主键。


    聚集索引的缺点:

      A:聚簇数据最大限度地提高了IO密集型应用的性能,但如果数据全部放在内存中,则访问的顺序就没有那么重要了,聚集索引也没有什么优势了

      B:插入速度严重依赖于插入顺序,按照主键的顺序插入是加载数据到innodb表中速度最快的方式,但如果不是按照主键顺序加载数据,那么在加载完成后最好使用optimize table命令重新组织一下表 。                                     optimize table account;

      C:更新聚集索引列的代价很高,因为会强制innodb将每个被更新的行移动到新的位置

      D:基于聚集索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题,当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间

      E:聚集索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候

      F:二级索引可能比想象的更大,因为在二级索引的叶子节点包含了引用行的主键列。

      G:二级索引访问需要两次索引查找,而不是一次


    因为二级索引叶子节点中保存的不是指向行的物理位置的指针,而是行的主键值。这意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个主键值去聚集索引中查找对应的行,这里做了重复的工作,两次btree查找而不是一次,对于innodb,自适应哈希索引能减少这样的重复工作。

     

    innodb和myisam物理存储的数据分布对比:

      myisam:

      是按照数据插入的顺序存储在磁盘上的,myisam中的主键索引和二级索引在结构上并没有什么不同,主键索引就是一个名为primary的唯一非空索引。MyISAM的非聚餐索引:数据和索引是分开存放的 

    这里写图片描述

      innodb:

      因为innodb支持聚集索引,所以使用非常不同的方式存储同样的数据,innodb聚集索引包含了整个表的数据,而不是只有索引,因为在Innodb中,聚集索引就是表,所以不像myisam那样需要独立的行存储。聚集索引的每一个叶子节点都包含了主键值,事务ID,用于事务和MVCC的回滚指针以及所有剩余列的值,如果主键是一个列前缀索引,innodb也会包含完整的主键列和剩下的列的值。

      还有一点和myisam不同的是,innodb的二级索引和聚集索引很不同,innodb二级索引的叶子节点中存储的不是行指针,而是主键值,并以此作为指向行的指针,这样的策略减少了当出现行移动或者数据页的分裂时二级索引的维护工作,使用主键值当做指针会让二级索引占用更多的空间,换来的好处是,innodb在移动行时无须更新二级索引中的这个指针。

    InnoDB的聚簇索引: 数据和主键索引是存放在一起的,其他索引叶子结点存放的主键id。 

     这里写图片描述


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

     

    InnoDb 使用自增主键和非自增主键乱序值做主键区别

    eg :用UUID做主键,因为新航的主键值不一定比之前插入的大,所以Innodb无法简单的总是把新行插入到索引最后,而是需要为新行寻找合适的位置,并且分配空间,这样会增加很多额外的工作,并导致数据分布不够优化。这将导致 大量的随机I/O。因为写入是乱序的,IoonDB不得不频繁的做页分裂操作,页分裂会导致移动大量的数据,一次插入最好啊需要修改三个页。 并且页会变的稀疏并且被不规则的填充,所以最终数据会有碎片。


    顺序主键也可能会造成       ①主键上届热点 ②AUTON_INCREMENT锁机制

    可以修改 innodb_autoinc_lock_mode 参数







    网上关于二级索引的解释:

    二级索引也是有两个字段的有序文件:

    第一个字段是索引字段,有相同的数据类型,并且是数据文件中的非排序字段,

    第二个字段可以是一个块指针也可以是记录指针。二级索引(也称为非聚簇索引)用于在二级键上搜索文件,二级索引的搜索键指定了一个顺序,这个顺序与文件的排序顺序不同。例如,对于图3.16所示的EMPLOYEE薪水册文件,可能用雇员标识符(EMP.ID)作为构建主索引的主键,而雇员的姓和名(EMP.LNAME和EMP.FNAME)可能用于构建二级索引。因此,用户产生的搜索操作可以通过雇员标识符(EMP.ID)或者雇员的名字(EMP.FNAME和EMP.LNAME)来访问记录。


    网上关于二级索引的解释:

    二级索引也是有两个字段的有序文件:

    第一个字段是索引字段,有相同的数据类型,并且是数据文件中的非排序字段,

    第二个字段可以是一个块指针也可以是记录指针。二级索引(也称为非聚簇索引)用于在二级键上搜索文件,二级索引的搜索键指定了一个顺序,这个顺序与文件的排序顺序不同。例如,对于图3.16所示的EMPLOYEE薪水册文件,可能用雇员标识符(EMP.ID)作为构建主索引的主键,而雇员的姓和名(EMP.LNAME和EMP.FNAME)可能用于构建二级索引。因此,用户产生的搜索操作可以通过雇员标识符(EMP.ID)或者雇员的名字(EMP.FNAME和EMP.LNAME)来访问记录。


    选择索引

      这里我们要引入两个比较难理解但很重要的概念:聚簇索引和非聚簇索引。这是索引的两种类型。在聚簇索引中,索引树的叶级页包含实际的数据:记录的索引顺序与物理顺序相同。在非聚簇索引中,叶级页指向表中的记录:记录的物理顺序与逻辑顺序没有必然的联系。

      聚簇索引非常象目录表,目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表,索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如,它也许同时有主题索引和作者索引。同样,一个表可以有多个非聚簇索引。通常情况下,你使用的是聚簇索引,但是你应该对两种类型索引的优缺点都有所理解。

      从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时,用聚簇索引也比用非聚簇索引好。例如,假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息,你应该对这个表的DATETIME(日期时间)型字段建立聚簇索引。假如你不仅想根据日期,而且想根据用户名从你的网点活动日志中取数据。在这种情况下,同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引,对用户名字段建立非聚簇索引。如果你发现你需要更多的索引方式,你可以增加更多的非聚簇索引。

      但非聚簇索引需要大量的硬盘空间和内存。另外,虽然非聚簇索引可以提高从表中取数据的速度,它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时,必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据,那么不要对它建立太多非聚簇索引。另外,如果硬盘和内存空间有限,也应该限制使用非聚簇索引的数量。



    展开全文
  • 聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会... 总体来说:聚簇索引 比 非聚簇索引 要好 聚簇索引:若根据id查,直接找到数据;若根据name查,先根据name找到对应的id,然后再根据id找到数据; ...
    • 聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一且非空的索引代替(类似oracle中的RowId);
    • 主键建议使用自增id,因为主键是相邻的,那么对应的数据一定存在相邻的硬盘上,方便查找;
    • 表中行的物理顺序和索引中行的物理顺序是相同的
    • InnoDB使用的是聚簇索引;MyISAM使用的是非聚簇索引;
    • 总体来说:聚簇索引  比  非聚簇索引  要好

     

    聚簇索引:若根据id查,直接找到数据;若根据name查,先根据name找到对应的id,然后再根据id找到数据;

    非聚簇索引:主键id叶子节点对应的是独立空间的数据地址,辅助键name叶子节点对应的也是独立空间的数据地址,所以根据主键查和根据辅助键查区别不大;

     

     

    展开全文
  • 聚簇索引和非聚簇索引的区别

    万次阅读 多人点赞 2018-07-25 10:39:04
    参考链接:和刚入门的菜鸟们聊聊--什么是聚簇索引与非聚簇索引 MYSQL索引:对聚簇索引和非聚簇索引的认识 一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。 在《数据库原理》一书中是这么...
  • innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用 myisam中, 主索引和次索引,都指向物理行(磁盘位置). myisam 示意图 innodb 示意图 注意: innodb来说, 1: 主键索引 既存储索引值...
  • 聚簇索引&非聚簇索引

    2020-01-02 17:49:47
    非聚簇索引:将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置 在innodb中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。辅助索引叶子节点...
  • 索引的重要性 数据库性能优化中索引绝对是一个重量级的因素,可以说,索引使用不当,其它优化措施将毫无意义。...最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引
  • MYSQL索引:对聚簇索引和非聚簇索引的认识

    万次阅读 多人点赞 2016-07-17 22:03:41
    聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值...聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 因此,MYSQL中不同的数据存储引擎对聚
  • 但是书中又把InnoDB作为聚簇索引的代表、MyISAM作为非聚簇索引的代表,两者进行比较,这就导致了矛盾点,如果将二级索引等价于非聚簇索引,那InnoDB就不能完全代表聚簇索引;但是单从二级索引的存储形式来看,又属于...
  • Mysql聚簇索引和非聚簇索引原理(数据库)

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

    千次阅读 多人点赞 2018-08-07 15:34:30
    聚簇索引并不是一种单独的索引类型,而是...MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustere...
  • 这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的. 聚簇索引和非聚簇索引的概念 我们先引用官网上的一段话来看看它们是干嘛的 Every InnoDB ...
  • 非聚簇索引 为什么要建索引 怎么创建索引 总结 什么是索引. 我们来看看比较大众的定义,OK,那就直接百度百科吧:"索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息...
  • 索引分为聚簇索引和非聚簇索引。以一本英文课本为例,要找第8课,直接翻书,若先翻到第5课,则往后翻,再翻到第10课,则又往前翻。这本书本身就是一个索引,即“聚簇索引”。如果要找"fire”这个单词,会翻到书...
  • MySQL聚簇索引和非聚簇索引的理解

    千次阅读 2019-07-10 00:41:45
    关于聚簇索引和非聚簇索引的概念很多同学找了很多教程但是仍然很迷糊。 这里给出一篇翻译,并给出我的配图,希望对大家理解有帮助。 英文原文:...
  • 聚簇索引和非聚簇索引区别的应用

    千次阅读 2014-12-17 10:56:49
     聚簇索引和非聚簇索引的一个标志性区别就是聚簇索引的叶节点对应着数据页,从中间级的索引页的索引行直接对应着数据页。而非聚簇索引的索引B+树叶节点不是直接指向数据页面的。如果表有聚集索引或索引视图上有聚集...
  • sql聚簇索引和非聚簇索引 The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A ...
  • 聚簇索引和非聚簇索引的区别

    千次阅读 2017-05-18 07:30:45
    聚簇索引和非聚簇索引 (1)定义 聚簇索引的顺序就是数据的物理存储顺序; 非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。 (2)索引底层实现结构是B+树,二叉树的一种;  聚簇索引:索引的叶节点就是...
  • 聚簇索引是对磁盘上实际数据重新组织以按指定的一个...聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 因此,MYSQL中不同的数据存储引擎对聚簇索引的支持...

空空如也

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

非聚簇索引