MySQL—聚簇/非聚簇索引

区别

Posted by Kingtous on March 21, 2019

MySQL—聚簇索引

聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index) 最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一页就是P开头的部分,这就是物理存储顺序(聚簇索引);而不用你到目录,找到汉字“爬”所在的页码,然后根据页码找到这个字(非聚簇索引)。

聚簇索引的叶节点就是数据节点,而非聚簇索引的叶节点仍然是索引节点,并保留一个链接指向对应数据块

  • 聚簇索引
    • 频繁更新的表不适合
      • 磁盘频繁的排序等操作
    • 学号什么的不适合
      • 无意义的自动增量字段
  • 非聚簇索引
    • 主键插入速度快
  • 主码+Unique会自动被DBMS建立索引

  • 一个基本表中最多只能建立一个聚簇索引
动作 使用聚簇索引 使用非聚簇索引
列经常被分组排序
返回某范围内的数据 不应
一个或极少不同值 不应 不应
小数目的不同值 不应
大数目的不同值 不应
频繁更新的列 不应
外键列
主键列
频繁修改索引列 不应

###聚族索引的优点

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

###聚族索引的缺点

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