精华内容
下载资源
问答
  • 从物理文件也可以看出 InnoDB(聚集索引)的数据文件只有数据结构文件.frm数据文件.idb 其中.idb中存放的是数据和索引信息 是存放在一起的。 2、聚集索引 表数据存储顺序与索引顺序无关。对于聚集索引,叶结点...
  • 在《数据库原理》里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此,所以一个表最多只能有一个聚簇索引
  • 聚簇索引和非聚簇索引

    万次阅读 多人点赞 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+树排序,故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织,所以一张表可以有多个辅助索引

    展开全文
  • 聚簇索引是对磁盘上实际数据重新组织以...在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 ...

    聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

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

    https://blog.csdn.net/alexdamiao/article/details/51934917

     

    聚簇索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据【每个表都有且只有一个聚簇索引】

    非聚簇索引:将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置【每个表都可以没有非聚簇索引,也可以有多个非聚簇索引】

     

    在innodb中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值,辅助索引访问数据总是需要二次查找

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

     

    聚簇索引具有唯一性,由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引。

    表中行的物理顺序和索引中行的物理顺序是相同的在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行 按照一定的顺序排列,并且自动维护这个顺序;

    聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键(类似oracle中的RowId)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。

     

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

    使用聚簇索引的优势:

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

    1.由于行数据和聚簇索引的叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中(缓存器),再次访问时,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

    2.辅助索引的叶子节点,存储主键值,而不是数据的存放地址。好处是当行数据放生变化时,索引树的节点也需要分裂变化;或者是我们需要查找的数据,在上一次IO读写的缓存中没有,需要发生一次新的IO操作时,可以避免对辅助索引的维护工作,只需要维护聚簇索引树就好了。另一个好处是,因为辅助索引存放的是主键值,减少了辅助索引占用的存储空间大小。

    注:我们知道一次io读写,可以获取到16K大小的资源,我们称之为读取到的数据区域为Page。而我们的B树,B+树的索引结构,叶子节点上存放好多个关键字(索引值)和对应的数据,都会在一次IO操作中被读取到缓存中,所以在访问同一个页中的不同记录时,会在内存里操作,而不用再次进行IO操作了。除非发生了页的分裂,即要查询的行数据不在上次IO操作的换村里,才会触发新的IO操作。

    3.因为MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。(强烈的对比)

    4.不过,如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。

     

    聚簇索引需要注意的地方

    当使用主键为聚簇索引时,主键最好不要使用uuid,因为uuid的值太过离散,不适合排序且可能出线新增加记录的uuid,会插入在索引树中间的位置,导致索引树调整复杂度变大,消耗更多的时间和资源。

    建议使用int类型的自增,方便排序并且默认会在索引树的末尾增加主键值,对索引树的结构影响最小。而且,主键值占用的存储空间越大,辅助索引中保存的主键值也会跟着变大,占用存储空间,也会影响到IO操作读取到的数据量。

    为什么主键通常建议使用自增id

    聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id,那么可以想 象,它会干些什么,不断地调整数据的物理地址、分页,当然也有其他一些措施来减少这些操作,但却无法彻底避免。但,如果是自增的,那就简单了,它只需要一 页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。

     

    转自:https://my.oschina.net/xiaoyoung/blog/3046779

    展开全文
  • 这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的. 聚簇索引和非聚簇索引的概念 我们先引用官网上的一段话来看看它们是干嘛的 Every InnoDB ...

    引语

    这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的.

    聚簇索引和非聚簇索引的概念

    我们先引用官网上的一段话来看看它们是干嘛的

    Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.
    
    When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
    
    If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
    
    If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
    

    有耐性的朋友可以自己翻译看看,这里咱们大概翻译了一下,总结出上面这段话的意思:
    每个InnoDB表都有一个特殊的索引,称为聚簇索引,用于存储行数据。
    1.如果创建了一个主键,InnoDB会将其用作聚簇索引(如果主键没有逻辑唯一且非空的列或列集,最好是设置成自动递增的)
    2.如果没有为表创建主键,则MySQL会在所有键列都不为NULL的情况下找到第一个UNIQUE索引,InnoDB会将其用作聚集索引
    3.如果表没有PRIMARY KEY或合适的UNIQUE索引,则InnoDB在包含行ID值的合成列上内部生成一个名为GEN_CLUST_INDEX的隐藏的聚集索引(隐藏的是看不到的,也就是说不会出现在desc table中,行ID是一个6字节的字段,随着插入新行而单调增加)
    从这三种情况来看的话,就是说不管你有没有创建主键,mysql都会给你弄一个聚簇索引给安排上,你创建了就用你设置的主键为聚簇索引,没有创建就给你来个隐藏的.

    聚簇索引(也称为主键索引)就是携带了行数据的索引,非聚簇索引就是除了聚簇索引之外的索引.这样说起来可能有点干巴巴的,咱们画个图来理解一下.
    假设有一张表test

    create table test(
    id int primary key,
    age int not null,
    name varchar(16),
    PRIMARY KEY (`id`),
    KEY `idx_age` (`age`) USING BTREE,
    KEY `idx_name` (`name`) USING BTREE,
    )engine=InnoDB;
    

    主键是id,然后有两个普通索引idx_age,idx_name(btree类型的索引),使用的是innodb引擎.
    我们知道id就是聚簇索引,idx_age,idx_name是非聚簇索引.
    现在有三条数据(1,11,‘甲’),(2,12,‘乙’),(2,13,‘丙’).那么他们在数据库中存储的形式是,如下:
    聚簇索引:

    非聚簇索引:

    可以看到聚簇索引后面是直接跟着的数据,而非聚簇索引指向的是聚簇索引的key值.
    因此非聚簇索引查询数据需要先查到聚簇索引的key,然后用这个key去查询真正的数据(这个过程称为回表).
    也就是说非聚簇索引是需要查询两次
    如图:

    所以能走聚簇索引的尽量走聚簇索引(也可以说是尽量走主键),看起来都是走索引,实际上主键要更快.
    而且主键索引如果是自增的int类型,因为长度比较小,占用的空间也比较小.

    覆盖索引

    我们上面说到如果是非聚簇索引的话会需要回表,查询两次,但是如果要查询得字段,数据直接就在索引上是可以不需要回表的.这种索引称为覆盖索引.
    比如我们要查询上面的test表中的age和name两个字段.

    select id,age,name from test where age = 13;
    

    直接查询的话,会根据age的索引找到id的key,然后再用id去查询出数据.
    但是如果我们创建一个(age,name)的联合索引,情况就不一样了.

    因为要返回的值,id在联合索引指向的key上,age和name共同组成了联合索引,
    因此数据都在(age,name)的联合索引上,并不需要回表在去查询一次,可以大大提高查询得效率.
    当然这个查询要比较频繁,使用率比较高,毕竟创建索引也是要消耗资源的,实际情况要根据查询频率和索引大小来做出判断.
    有联合索引存在的情况下能走覆盖索引当然是最好的,提高了查询效率.
    注:还有在某些count聚合函数使用的时候可以使用覆盖索引来优化count,比如说select count(age) from test.
    因为age是有索引了,直接使用到的也是age,所以覆盖索引了,无需回表.

    总结:

    1.聚簇索引和非聚簇索引,查询得时候使用聚簇索引能提高查询效率,尽量避免走非聚簇索引回表的耗时操作
    2.覆盖索引能提高查询效率,主要是避免了回表的操作,查询得时候根据具体情况建立合适的索引走覆盖索引提高查询速度

    参考资料:

    1.https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html
    2.https://mp.weixin.qq.com/s/y0pjtNUZhOW2ZBOy4m-xsA

    展开全文
  • 1.聚簇索引和非聚簇索引的概念 2.两者详细介绍 3. 两者的区别 3.1 数据存储方式 3.2二级索引查询 1.聚簇索引和非聚簇索引的概念 数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种。“聚簇”的...

     

    目录

    1.聚簇索引和非聚簇索引的概念

    2.两者详细介绍

    2.1 聚簇索引

    2.2 非聚簇索引

    3. 两者的区别

    3.1 数据存储方式

    3.2 二级索引查询


    1.聚簇索引和非聚簇索引的概念

     数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。我们熟悉的InnoDB和MyISAM两大引擎,InnoDB的默认数据结构是聚簇索引,而MyISAM是非聚簇索引。

    聚簇索引(Clustered Index)并不是一种单独的索引类型,而是一种数据存储方式。当表有了聚簇索引的时候,表的数据行都存放在索引树的叶子页中。无法把数据行放到两个不同的地方,所以一张表只允许有一个聚簇索引。InnoDB的聚簇索引实际上是将索引和数据保存中同一个B-Tree中。InnoDB通过主键聚集数据,如果没有定义主键,InnoDB会选择一个唯一的的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

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

    2.两者详细介绍

    2.1 聚簇索引

    因为聚簇和非聚簇索引本质上是数据存储方式,需要依赖于载体,即以InnoDB引起来讲解聚簇索引,以MyISAM来讲解非聚簇索引。下述讲解的图都引用自《高性能MySQL》。

    对于InnoDB引擎来说,是按照聚簇索引的形式存储数据:

    它的每个聚簇索引的叶子节点都包含主键值、事务ID、回滚指针(用于事务和MVCC)以及余下的列。从物理文件也可以看出 InnoDB的数据文件只有数据结构文件.frm和数据文件.ibd 其中.ibd中存放的是数据和索引信息 是存放在一起的。

    InnoDB的二级索引和主键索引也有很大的不同,二级索引存放的是主键值而不是行指针,减少了移动数据或者分裂时维护二级索引的开销,因为不需要更新索引的行指针。

    聚簇索引的特点:

    优点:

    1. 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件,如果没有使用聚集索引,则每封邮件都可能导致一次磁盘IO。
    2. 数据访问更快,聚集索引将索引和数据保存在同一个B-Tree中,因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
    3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

    缺点:

    1. 聚簇数据最大限度地提高了IO密集型应用的性能,但如果数据全部放在内存中,则访问的顺序就没有那么重要了,聚集索引也没有什么优势了。
    2. 插入速度严重依赖于插入顺序,按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式,但如果不是按照主键顺序加载数据,那么在加载完成后最好使用optimize table命令重新组织一下表。
    3. 更新聚集索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
    4. 基于聚集索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题,当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间。
    5. 聚集索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
    6. 二级索引可能比想象的更大,因为在二级索引的叶子节点包含了引用行的主键列。
    7. 二级索引访问需要两次索引查找,而不是一次。

    2.2 非聚簇索引

    对于MyISAM引擎来说,是按照非聚簇索引的形式存储数据:

    原始数据:

     存储方式:

    按照列值和行号来组织索引的,叶子节点中保存的实际上是指向存放数据块的指针。从物理文件中也可以看出MyISAM的索引文件.MYI和数据文件.MYD是分开存储的 是相对独立的。

    举例:执行流程:select * from user where id =1
    1、查看该user表的myi索引文件中有没有以id为索引的索引树
    2、在id索引树上通过id值找到相应节点,从而得到节点的数据(叶子节点存的是索引值和数据地址,数据地址指向当前表myd数据文件具体的哪一行)
    3、根据数据地址去myd文件里找到对应的数据返回。

    3. 两者的区别

    3.1 数据存储方式

    最直观的区别是反映在数据存储方式上,在MySQL数据库中InnoDB(聚簇)和MyISAM(非聚簇)数据存储文件格式如下:

    存储引擎是InnoDB, 在data目录下会看到2类文件:.frm、.ibd
    (1)*.frm--表结构的文件。
    (2)*.ibd--表数据文件
    
    存储引擎是MyISAM, 在data目录下会看到3类文件:.frm、.myi、.myd 
    (1)*.frm--表定义,是描述表结构的文件。
    (2)*.MYD--"D"数据信息文件,是表的数据文件。
    (3)*.MYI--"I"索引信息文件,是表数据文件中任何索引的数据树

    示意图,test1的存储引擎为InnoDB,test2的存储引擎为MyISAM:

    聚簇索引和非聚簇索引的存储方式区别:

    1. 在MyISAM引擎索引和数据是分开存储的,而InnoDB是索引和数据是一起以idb文件的形式进行存储的。
    2. 在访问速度上,聚簇索引比非聚簇索引快。非聚簇索引需要先查询一遍索引文件,得到索引,跟据索引获取数据。而聚簇索引的索引树的叶子节点的直接指向要查找的数据行。

    3.2 二级索引查询

    对于采用聚簇索引的InnoDB引擎的主键索引B+Tree和MyISAM的主键索引树以及MyISAM的二级索引B+Tree都是采用这样的结构。

     但是InnoDB的二级索引B+Tree却是这样的:

    可以得出:
      在使用二级索引进行查询的时候,InnoDB首先通过二级索引B+Tree得到数据行的主键索引,然后再通过主键索引树查询数据。所以在二级索引,InnoDB的性能消耗比较大。
      但是,这种情况在InnoDB中有一定的优化,不是认为控制的,而是引擎实现的,通过二级索引查询多了,InnoDB会生成自适应的哈希索引。

    引用高性能MySQL的图能更加清晰的看到其差异:

    从图中可以看出 InnoDB二级索引的叶子节点存放的是KEY字段+主键值,因此首先通过二级索引查找到的是主键值,再根据主键值在主键索引中查找到相应的数据文件。而MyISAM的二级索引存放的还是列值和行号的组合 叶子节点中保存的是指向物理数据的指针,因此它的主建索引和二级索引的结构并没有任何区别,只是说主键索引的索引值是唯一且非空的,而MyISAM引擎可以不设置主键。InnoDB引擎是必须设置主键的,需要依赖主键生成聚簇索引。 

    展开全文
  • 非聚簇索引:将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置 在innodb中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。辅助索引叶子节点...
  • 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 ...
  • 聚簇索引和非聚簇索引区别 MySQL的聚簇索引是指Innodb引擎的特性,MySIAM并没有,如果需要该索引,只要将索引指定为主键(primary key)就可以了。 聚集(clustered)索引,也称聚簇索引。聚簇索引并不是一种单独的...
  • 目录(1)聚簇索引(2)辅助索引(非聚簇索引)(3)InnoDB索引的实现(3.1)主键索引(3.2)InnoDB的辅助索引(4)MyISAM索引的实现(4.1)主键索引(4.2)辅助索引(Secondary key)(5)总结 (1)聚簇索引 聚簇...
  • 聚簇索引和非聚簇索引(通俗易懂 言简意赅)

    千次阅读 多人点赞 2020-06-23 19:09:36
    总结:InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引...
  • 聚簇索引和非聚簇索引的区别对比

    万次阅读 2020-08-27 16:22:48
    非聚簇索引:将数据与索引分开存储,索引结构的叶子节点放的是指向数据的指针(或者说是地址) 在mysql中的应用 innodb的索引都是聚簇索引,其数据直接存放在叶子节点上,但是主键索引(一级索引)叶子节点中存放的...
  • 今天我们来聊一聊关于 聚簇索引和非聚簇索引的问题;  刚开始学数据库SQL的时候,就知道有主键啊(Primary-key),外键啊(Foreign-key)啥的,连个表查询就已经不清楚是要on 那几个字段了,在数据量不太大的情况下,...
  • 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘...
  • 在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的: 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 因此,MYSQL中不同的数据存储引擎对聚...
  • MySQL中Innodb的聚簇索引和非聚簇索引

    万次阅读 热门讨论 2018-08-29 22:22:15
    数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下...
  • 在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的: 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。 因此,MYSQL中不同的数据存储引擎对聚...
  • Mysql数据库索引的理解及聚簇索引和非聚簇索引的区别 概念 索引是帮助Mysql搞笑获取数据的数据结构 对Mysql数据库来讲,其核心就是存储引擎,而索引就是属于存储引擎级别的概念,不同的存储引擎对索引的实现方式是...
  • MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。这两种索引内部都是B+树,聚集索引...
  • mysql聚簇索引和非聚簇索引的区别 mysql聚簇索引和非聚簇索引的区别是:对于聚簇索引,表数据按顺序存储,即索引顺序和表记录物理存储顺序一致;对于非聚簇索引,表数据存储顺序与索引顺序无关。 mysql聚簇...
  • 1.聚簇索引和非聚簇索引的概念 1.1聚簇索引 将数据存储与索引放到了一块,找到了索引也就找到了数据,当表有聚簇索引时,它的数据实际上存放在索引的叶子页上,也就是B+树的叶子节点上,因为数据行不能存在两个地方...
  • B-Tree介绍 B-Tree是一种多路搜索树(并不是二叉的):  1.定义任意叶子结点最多只有M个儿子;...每个结点存放至少M/2-1(取上整)至多M-1个关键字;(至少2个关键字)  5.叶子结点的关键字个数=...
  • MySQL聚簇索引和非聚簇索引的理解

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

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 26,400
精华内容 10,560
关键字:

聚簇索引和非聚簇索引