| MySQL面试：谈谈你对聚簇索引的理解 | 您所在的位置：网站首页 › 随机索引是什么意思 › MySQL面试：谈谈你对聚簇索引的理解 |
MySQL面试：谈谈你对聚簇索引的理解
|
通俗的讲
聚簇索引：
将数据存储和索引放到了一块，找到了索引也就找到了数据一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。 非聚簇索引：
将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。MyISAM通过key_buffer把索引先缓存到了内存中，当需要访问数据时(通过索引访问数据)，在内存中直接查找索引，然后通过索引找到磁盘相应数据。这也就是为什么索引不在key buffer命中时，速度慢的原因。
聚集索引⼀个表只能有⼀个，⽽⾮聚集索引⼀个表可以存在多个。聚集索引存储记录是物理上连续存在，⽽⾮聚集索引是逻辑上的连续，物理存储并不连续。 澄清一个概念： InnoDB中，在聚集索引上创建的索引叫做辅助索引辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引、辅助引擎叶子节点存储的不再是行的物理位置，而是主键值。一棵树当中没有子结点（即度为0）的结点称为叶子结点，简称“叶子”。 叶子是指出度为0的结点，又称为终端结点 定义 聚簇索引： 聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引 辅助索引（非聚簇索引） 在聚集索引之上创建的索引叫做辅助索引辅助索引访问数据总是需要二次查找，第一次找到主键值，第二次根据主键值找到行数据： 辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。 辅助索引的存储不影响数据在聚簇索引中的组织，所以一张表可以由多个辅助索引。在innodb中有时也称辅助索引为二级索引。
由于聚簇索引是将数据跟索引结构放在一块，所以一个表仅有一个聚簇索引 一个误区：把主键设为聚簇索引 聚簇索引默认是主键。如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一页面中的记录，包含相邻键值的页面可能相聚很远如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，之后恢复设置主键即可此时其他索引只能被定义为非聚簇索引。这里是最大的误区，有的主键还是无意义的自动增量字段，那样的话clusteed index对效率的帮助，完全被浪费了 刚才说了，聚簇索引性能最大而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来就那些选择，某个字段作为聚簇索引，或者组合聚簇索引，这个要看实际情况。 记住我们最终目的就是在相同结果集的情况下，尽可能减少IO逻辑 结合图看
MyISAM使用的是非聚簇索引，非聚簇索引的两颗B+Tree看上去没有什么不同，节点的结构完全一致，支持存储的内容不同耳机，主键索引B+树的节点存储了主键，辅助键索引B+树存储了复制键。表数据存储在独立的地方，这两棵B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有人任何差别。所以索引树是独立的，通过辅助索引无需访问主键的索引 聚簇索引的优势 数据访问更快： 由于行数据和叶子节点存储在一起，同一页中会有多条数据，访问同一数据的不同行记录时，已经把也加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘这样由于主键和行数据时一起被载入磁盘的，找到叶子节点就可以立即将行数据返回了，如果按照主键ID来组织数据，获得数据更快 聚簇索引对主键的排序查找和范围查找速度非常快 聚簇索引适合用在排序的场合，非聚簇索引不适合取出一定范围数据的时候，使用用聚簇索引 聚簇索引的缺点 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID作为主键 主键的值是顺序的，所以InnoDB会将每一条记录都存储在上一天记录的后面。当达到页的最大填充因子（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改）下一条记录就会被写入到新的页中。一旦数据按照这种顺序的方式被加载，主键也就会近乎与被顺序的记录填满（二级索引页可能是不一样的） 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。因此，对于InnoDB表，我们一般定义主键不可更新；另外，建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片如果主键比较大的时候，那辅助索引将会变得更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用更多的物理空间 为什么辅助索引使用主键作为“指针”而不是地址值作为指针减少了当出现行移动或者数据页分裂是辅助索引的维护功能。 使用主键值当做指针会让辅助索引占用更多的空间。换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。 为什么主键通常建议使用自增ID聚簇索引的数据的物理存放顺序是索引顺序是一致的，即使：只要索引是相邻的，那么对应的数据一定也是相邻的存储在物理磁盘上的。如果主键不是自增ID，那么可以想象，它会干些什么，不断地调整数据的物理地址，分页，当然也有一些措施来减少这些操作，但是却无法彻底避免。但是，如果是自增的，那就简单了，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。 因此MyISAM的主索引并非聚簇索引，那么它的物理地址必然是凌乱的，拿到这些物理地址，按照合适的ID算法进行读取，于是开始不同的不停的旋转。聚簇索引则只需一次I/O。 不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。 |
【本文地址】