MySQL的B+树索引和hash索引的区别

Z时代
2024-01-10
分类：综合

database

简述一下索引：

索引是数据库表中一列或多列的值进行排序的一种数据结构；索引分为聚集索引和非聚集索引，聚集索引查询类似书的目录，快速定位查找的数据，非聚集索引查询一般需要再次回表查询一次，如果不使用索引就会进行全表扫描；还有可以进行多字段组成联合索引，但是要符合最左匹配原则要求。

如果使用覆盖索引就可以不回表扫描。

索引类型：InnoDB引擎，默认B+树（O(logN)）、Hash索引 B树索引 O(1)

1、由于底层是使用hash表，以key-value存储，无法直接通过索引查询，只选择一个数据hash索引更快，但是如果选择N条数据，hash索引的时间复杂度是O(N)，由于B+树索引有序，且叶子节点有链表连接，查询效率比hash索引快
2、索引在硬盘保存，一般不会一次性保存到内存中，B+树可以设计允许数据分批加载，同时树的高度较低，查询速率较快
3、硬盘的I/O速度相比内存来说非常慢，而索引是用于加快查询速度的，需要减少I/O操作，内存和磁盘以页为单位交换数据，为了减少I/O，索引在新建节点的时候，是直接申请一个页的空间，存储分配是按页对齐，就实现了一个节点一次I/O。
4、B+ 树是平衡树，它查找任意节点所耗费的时间都是完全相同的，比较的次数就是 B+ 树的高度

B+ Tree索引和Hash索引区别？

哈希索引适合等值查询，但是无法进行范围查询和模糊查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

索引的种类有哪些？分别的特点是什么？

普通索引：加速查询
唯一索引：加速查询 + 列值唯一 + 可以为null
主键索引：加速查询 + 列值唯一 + 不可为null + 表中只有一个
组合索引：多列值组成一个索引，专用于组合搜索，效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

不适合作为索引

更新频繁的字段不适合创建索引
不会出现在where子句中的字段

聚簇索引和非聚簇索引的区别

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引**

聚簇索引查询会更快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询。通过覆盖索引也可以只查询一次。**

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。**

当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。**

联合索引、最左前缀匹配

创建联合索引时，会选择识别度最高的放在最左边，由于mysql遵循最左前缀匹配原则，从联合索引最左边开始匹配。创建一个联合索引（key1,key2,key3），其实创建了（key1）（key1,key2）（key1,key2,key3）三个索引。

索引下推、查询优化

mysql 5.6版本优化内容：Index Condition Pushdown（索引下推）默认开启，

比如：

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode="95054" AND lastname LIKE "%etrunia%" AND address LIKE "%Main Street%";

如果没有使用索引下推技术，则MySQL会通过zipcode="95054"从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE "%etrunia%"和address LIKE "%Main Street%"来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode="95054"的索引，然后根据lastname LIKE "%etrunia%"和address LIKE "%Main Street%"来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

如果对大家有帮助，请大家多点赞。。。