MySQL-聚簇索引和非聚簇索引分析

1、 MySQL 聚簇索引和非聚簇索引 & mysql 索引为啥用b 树 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据 非聚簇索引：数据存储和索引分开放，索引结构的叶子节点指向了数据的对应行，myisam通过 key_buffer 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的原因（磁盘 IO）。澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值.何时使用聚簇索引与非聚簇索引 cluster indexInnoDB 聚簇索引1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用where id = 14这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引

2、B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）聚簇索引的优势看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键 ID 来组织数据，获得数据更快。2. 辅助索引使用主键作为指针而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个指针。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这

3、个主键B+树的节点如何变化，辅助索引树都不受影响。3. 聚簇索引适合用在排序的场合，非聚簇索引不适合4. 取出一定范围数据的时候，使用用聚簇索引5. 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据6. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。聚簇索引的劣势1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片2. 表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键image主键的值是顺序的，所以 InnoDB 把每一条记录都存

4、储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）1. 如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间为什么主键通常建议使用自增id聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）不过，如果涉及到大数据量的排序、全表扫描、c

5、ount之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。mysql中聚簇索引的设定聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。MyISM 非聚簇索引MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。mysql 索引为啥用 b+ 树trie树死在了开始，无疑AVL树在查询方面是最出色的，但是在删除的时候可能会引起噩梦；这样看来好像是红黑树最适合咯，虽然他牺牲了一部分查询性能，但是使删除性能在大部分情况保持了常数的时间复杂度。但是，有一个最重要的问题是，mysql的数据是放在

6、外部存储的，也就是说磁盘IO才是性能瓶颈的关键，所以我们需要的是减少树的深度，所以我们需要更多分叉的树 ,还需要更适合磁盘操作特性的数据结构。B+树是为磁盘或其他直接存取的辅助存储设备而设计的一种数据结构。mysql为什么选取B+树，本质上是因为mysql数据是存放在外部存储的。 B+树 未见其字，先闻其图。（实在是不好定义） （一颗普通的B+树） 性质（m叉B+树）：1. 树中每个结点至多有m个孩子。2. 除根结点和叶子结点外，其它每个结点至少有m/2个孩子。3. 若根结点不是叶子结点，则至少有2个孩子。4. 所有叶子结点都出现在同一层。5. 每个非终端节点中包含n个关键字信息：(A0，K1，A1，K2，A2，.，Kn，An)。其中，Ki (i=1.n)为关键字，且关键字按顺序排序Ki K(i-1)。Ai为指向子树根的接点，且指针A(i-1)指向子树种所有结点的关键字均小于Ki，但都大于K(i-1)。关键字的个数n必须满足： m/2-1 = n = m-1 当然还有一种性质是说n个关键字只有n个孩子，这里就不讨论了。 优势：1. 只有叶子节点才记录数据，非叶子节点只包含索引；所有的非

7、终端节点（内部节点）并不存储数据信息，而是保存其叶子节点的最小值作为索引。 这样，一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。2. 能够提供稳定高效的范围扫描（range-query）功能；这也是为什么数据库和操作系统中的文件系统通常会采用b+树作为数据索引的原因，这个特点主要因为所有叶子节点相互连接，并且叶子节点本身依关键字的大小自小而大顺序链接。B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。所以从Mysql（Inoodb）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。那么Mysql如何衡量查询效率呢？ 磁盘IO次数。B-树/B+树 的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节

8、点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。另一个优点是：B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。B树相对于红黑树的区别AVL 数和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。参考：为什么Mysql用B+树做索引而不用B-树B+Tree在数据库索引上拥有独特优势的原因Kotlin 开发者社区国内第一Kotlin 开发者社区公众号，主要分享、交流 Kotlin 编程语言、Spring Boot、Android、React.js/Node.js、函数式编程、编程思想等相关主题。越是喧嚣的世界，越需要宁静的思考。合抱之木，生于毫末；九层之台，起于垒土；千里之行，始于足下。积土成山，风雨兴焉；积水成渊，蛟龙生焉；积善成德，而神明自得，圣心备焉。故不积跬步，无以至千里；不积小流，无以成江海。骐骥一跃，不能十步；驽马十驾，功在不舍。锲而舍之，朽木不折；锲而不舍，金石可镂。蚓无爪牙之利，筋骨之强，上食埃土，下饮黄泉，用心一也。蟹六跪而二螯，非蛇鳝之穴无可寄托者，用心躁也。 -全文完-

《MySQL-聚簇索引和非聚簇索引分析》由会员Baige****0346分享，可在线阅读，更多相关《MySQL-聚簇索引和非聚簇索引分析》请在金锄头文库上搜索。