聚簇 和 非聚簇索引

mysql聚簇和非聚簇索引的区别是什么?

聚簇

聚簇索引也叫：clustered index 聚集索引 或 一级索引

  • 即是索引目录，也是真实数据（最下层叶子节点）。
  • 因为：B+树：在最下层叶子节点中记录的都是“行”的真实数据
  • 而 非叶子节点，记录行的 key 。作为目录只用来做查询

聚簇索引不用自己创建

每个表会自动创建一个 聚簇索引（有且只有一个）

  • 没主键，则会选择第一个唯一索剔UNIQUE列作为key
  • 如果唯一键，也没有，每一行 创建一个 6字节 递增隐藏列 DB_ROW_ID
  • 然后构建一个 名为：GEN_CLUST_index的索引
非聚簇

非聚簇索引

  • Secondary Indexes

就是 其他索引（除了 聚簇索引外的）

  • 也被称为 辅助索引 或 二级索引
  • 除了构成聚簇索引的索引项之外的其他每个索引，都会构成一颗非聚簇索引树
  • 聚簇索引树，是使用表主键作为key来构造的。
  • 如果主键如果太长的话，非聚簇索引就会使用更多的空间
非聚簇的索引树
  • 这些非 聚簇索引，构成 一颗索引树。
  • 也是 一颗 B+树
  • 树的key 就是这些 索引对应的列
  • 非叶子节点,与聚簇索引一样，只记录这些 行里的索引值，作为目录存在
  • 但是：最底层叶子节点，保存的不是 数据。而是：记录该“行”中聚簇索引的key
索引树的查询
  • 查询：首先在 非聚簇索引 树中 快速找到 叶子节点
  • 叶子节点有 聚簇索引的key
  • 拿到这个 key，在去 聚簇索引 查询一遍。
  • 就可以 拿到真实数据了。

mysql的索引类型 Innodb 和 myisam

  • 索引是 存在 磁盘的

mysql的索引类型跟存储引|擎是相关的,

innodb存储引擎数据文件跟索引文件全部放在ibd文件中，

  • abc.frm 是表结构
  • abc.ibd 是索引和数据。说明是 innodb

而myisam的数据文件放在myd文件中，索引放在myi文件中，

  • abc.MYD 是数据文件
  • abc.MYI 是索引文件。说明是 Myisam 存储引擎。

其实区分架簇索引和非聚簇索引非常简单，只要判断数据跟索引是否存储在一起就可以了.

innodb存储引擎在进行数据插入的时候，数据必须要跟索引放在一起，

  • 如果有主键就使用主键，没有主键就使用唯一键，没有唯一．就使用6字节的rowid，因此跟数据绑定在一起的就是聚簇索引，
  • 而为了避免数据冗余存储，其他的索引的叶子节点中存储的都是聚簇索引的key值，因此innodb中既有聚l索引也有非聚簇索引，
  • 而myisam中只有非聚簇索引.

Hash索引 和 B+树索引

  • hash索引 底层是 hash表
  • 进程查找的时候，调用一次hash函数，
  • 就可以 获取到 响应的 键值
  • 之后 进行 回表 查询，获得 实际的数据。
  • B+树 底层实现是：多路平衡 查找树
  • 对于每一次的 查询都是 从 根节点出发，
  • 查找到 叶子节点，方可 查到 所查询的 键值。
  • 然后 根据查询判断，是否 需要回表 查询数据。
Hash缺点，B+树优点
  • hash查询，进行等值的 查询更快。但是 却 无法进行 范围查询。
  • hash函数后，已经 打散了。
  • B+树的所有节点，遵循 左节点 小于 父节点，右节点 大于 父节点。
  • 天然支持 范围查找。
  • Hash索引 不支持 索引进行排序，也是因为：hash后，数据已经被打散
  • hash索引 不支持 模糊查询，以及多列索引的 最左前缀 匹配。
  • 都是因为 hash函数，不可预测。
  • AAA 和 AAB 索引 没有相关性。
  • Hash索引，任何时候，都避免不了 回表查询数据。
  • 而 B+树，在符合某些条件 如：聚簇索引，索引覆盖等。
  • 可以只 通过 索引，完成查询。
  • Hash索引 虽然在 等值上 查询 比较快，
  • 不稳定，性能不可预测，
  • 当 某个键值，大量存在的 时候，发生hash冲突，
  • 此时效率 可能极差
  • 而：B+树的 效率 比较稳定。
  • 对于 所有的查询，都是 根节点 到 叶子节点。
  • 且：树的高度 较低。
  • 因此 大所属情况下，直接选择 B+树索引，
  • 可以获得 稳定 且：较好的 查询速度。