MySql 索引类型 BTree 和 Hash

索引是帮助mysql获取数据的数据结构。最常见的索引是Btree索引和Hash索引。主键索引叶子节点存储的就是 MySql 的整个数据行，普通索引的叶子节点存储的是索引列和主键值。

不同的引擎对于索引有不同的支持：Innodb 和 MyISAM 默认的索引是 Btree 索引；而 Mermory 默认的索引是 Hash 索引。

一、BTree

BTree 索引是最常用的 mysql 数据库索引算法，因为它不仅可以被用在 =, >, >=, <, <= 和 between 这些比较操作符上，而且还可以用于 like 操作符，只要它的查询条件是一个不以通配符开头的常量，例如：

  • select * from user where name like ‘jack%’;
  • select * from user where name like ‘jac%k%’;

如果一通配符开头，或者没有使用常量，则不会使用索引，例如：

  • select * from user where name like ‘%jack’;
  • select * from user where name like simply_name;

二、Hash

Hash 索引只能用于对等比较，例如 = ,<=>（相当于 = ）操作符。由于是一次定位数据，不像 BTree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次 IO 访问，所以检索效率远高于 BTree 索引。

但为什么我们使用 BTree 比使用 Hash 多呢？主要 Hash 本身由于其特殊性，也带来了很多限制和弊端：

  1. Hash索引仅仅能满足=, IN, <=> 查询，不能使用范围查询。
  2. 联合索引中，Hash索引不能利用部分索引键查询。对于联合索引中的多个列，Hash 是要么全部使用，要么全部不使用，并不支持 BTree 支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash 索引无法被利用。
  3. Hash 索引无法避免数据的排序操作
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
  4. Hash 索引任何时候都不能避免表扫描
    Hash 索引是将索引键通过 Hash 运算之后，将 Hash 运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。
  5. Hash 索引遇到大量 Hash 值相等的情况后性能并不一定会比 BTree 高
    对于选择性比较低的索引键，如果创建 Hash索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

三、聚簇、非聚簇索引

聚簇、非聚簇说白了就是是索引的存储方式

  • 聚簇索引的叶子节点就是数据节点（主键一定是聚簇索引）
  • 非聚簇索引的叶子节点是保存了对应数据块指针的索引节点

MyISAM 没有聚簇索引

对于如下的 sql 语句，直接根据主键查询有所字段信息，此时主键是聚簇索引，因为主键对应的索引叶子节点上保存了所有的数据：

1
select * from students where id = 1;

对于如下的 sql 语句，根据 no 查询编号和姓名，编号 no 本身是一个索引，当初查询命中索引时，由于查询的字段包含姓名。而 no 索引的叶子节点中保存的是主键 id，还要根据主键 id 再去查询姓名字段，所以此时的 no 索引不是聚簇索引

1
select no,name from students where no = 1001;

对于如下的 sql 语句，根据 no 查询编号，编号 no 本身是一个索引，当初查询命中索引时，直接返回编号，不需要回表查询，这种场景下 no 是聚簇索引

1
select no from students where no = 1002;

总结

  1. hash 索引查找数据基本上能一次定位数据，当然有大量碰撞的话性能也会下降。而 btree 索引就得在节点上挨着查找了，很明显在数据精确查找方面 hash 索引的效率是要高于 btree 的；
  2. 那么不精确查找呢，也很明显，因为 hash 算法是基于等值计算的，所以对于 like 等范围查找 hash 索引无效，不支持；
  3. 对于 btree 支持的联合索引的最优前缀，has 也是无法支持的，联合索引中的字段要么全用要么全不用。
  4. hash 不支持索引排序，索引值和计算出来的 hash 值大小并不一定一致。

转载整理自：https://www.cnblogs.com/baizhanshi/p/11084539.html