【MySQL】聚簇索引和非聚簇索引
[数据库系统内幕]这本书中对聚簇索引的:
如果数据记录的顺序遵循搜索键的顺序,则这种索引称为“聚集簇索引”。
书中还详细讲解到:聚簇索引中的数据记录,通常与索引存储于同一个文件中,有时也存放在单独的聚簇文件中,而这些文件均保留了键的顺序。如果数据存储在单独的文件中,且其顺序不遵循键顺序,则索引为非聚簇索引。
链接:https://www.jianshu.com/p/b72c59b61efd
聚簇索引
聚簇索引是一种数据的存储方式,它的数据行只存放在索引(B+树)的叶子中,内部节点不存放数据。
聚簇索引的数据分布
聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引
非聚簇索引
非聚簇索引就是指B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中,如下图:
非聚簇索引
聚簇索引和非聚簇索引的对比
聚簇索引和非聚簇索引的对比
- InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14" 这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。
- 若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
非聚簇索引比聚簇索引多了一次读取数据的IO操作,所以查找性能上会差。
MyisAM索引与InnoDB索引相比较
- MyisAM支持全文索引(FULLTEXT)、压缩索引,InnoDB不支持;
- InnoDB支持事务,MyisAM不支持;
- MyisAM顺序储存数据,索引叶子节点保存对应数据行地址,辅助索引很主键索引相差无几;InnoDB主键节点同时保存数据行,其他辅助索引保存的是主键索引的值;
- MyisAM处理字符串索引时用增量保存的方式,如第一个索引是‘preform’,第二个是‘preformence’,则第二个保存是‘7,ance’,这个明显的好处是缩短索引,但是缺陷就是不支持倒序提取索引,必须顺序遍历获取索引
- innodb引擎,主键使用的是聚簇索引,非主键使用的是非聚簇索引。
- myisam引擎,不管主键还是非主键,使用的都是非聚簇索引。
聚簇索引与非聚簇索引.png
聚簇索引的优势
- 由于行数据和叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中,再次访问的时候,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
- 聚簇索引适合用在排序的场合,非聚簇索引不适合
- 取出一定范围数据的时候,使用用聚簇索引速度快
- 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户 ID 来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘 I/O。
聚簇索引的劣势
- 如果使用UUId(随机ID)作为主键,使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面更慢,
所以建议使用int的auto_increment作为主键
如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值;过长的主键值,会导致非叶子节点占用占用更多的物理空间
为什么主键通常建议使用自增id
- 聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。
- 因为MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取。聚簇索引则只需一次I/O。(强烈的对比)
不过,如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。