Mysql索引结构与索引原理及hash索引介绍

柔情只为你懂 2022-10-31 11:00 156阅读 0赞

Mysql索引主要包括四种，Btree索引、Hash索引、full-text全文索引、R-tree索引，因为作为一名PHP开发者，并不是专业的DBA，在这里只需要了解第一种开发相关的BTree索引。

索引的本质：MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据和排序的数据结构。

数据库查询是数据库的主要功能之一，最基本的查询算法是顺序查找（linear search）时间复杂度为O(n)，显然在数据量很大时效率很低。优化的查找算法如二分查找（binary search）、二叉树查找（binary tree search）等，虽然查找效率提高了。但是各自对检索的数据都有要求：二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

![9a99d7f9ef30c4277407bfbf44c8068e.png][]

如上图左边是横列表格，也就是真是的数据，右边是一棵树。数据越来越多，表格增长的越来越高，相反，如果树越来越高，查找的层次越来越多，我们如果能用三次找到，尽量别用四次，尽量减少一次磁盘I/O，也就是这棵树广度越来越广，广度广了同一层就代表枝叶多。

![cae27cfafeba178fa918b8c6545e7ec8.png][]

在数据库里面，在物理存储上，有单位的说法叫段、区、块，就是一种衡量单位。 上图中的磁块也就是相当于存储一段范围的数据。

看上图中，一棵B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3。

我如果要找29这个数字，那么从根找，P1表示小与17的，P2表示大于17小与35的，P3表示大于35的，那么往下走，真实的数据存在于叶子节点，也就是第三层，即3、5、9、10、13...依次往右看。假设我要查找非叶子节点（第二层），不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不存在真实的数据表中，也就是相当于一个参考值。如果查找29，但是我们先给参考项，那么根据图示，29在17和35之间，锁定磁盘块1的P2指针，找到指针P2，内存时间非常短，相比磁盘的IO可以忽略不计，那么下来之后，找到磁盘块3，也就是不见得加载磁盘块2，这里就第二次IO了，那么看图，29在26和30之间，那么又指向指针P2,再往下就加载到了磁盘块8的内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总共三次IO。

看Btree也就是三层楼那么高，也就是尽量把数据横向扩，高度矮比较好，真实的情况是，3层的B+树 可以表示上百万的数据，如果上百万的数据查找只需三次IO，性能提高将是巨大的，如果没有所用，每个数据项都要发生一次IO，那么总共需要上百万次的IO，显然成本非常高。

![1e1d89a183c4122e977413b99350ef9d.png][]

MySQL 中的 Hash 索引  
采用 Hash 进行检索效率非常高，基本上一次检索就可以找到数据，而 B+ 树需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次 I/O 操作，从效率来说 Hash 比 B+ 树更快。

我们来看下 Hash 索引的示意图：

![6de3360ab889a4cf6e51f81fb0f5bdc1.png][]

键值 key 通过 Hash 映射找到桶 bucket。在这里桶（bucket）指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到 Hash 冲突时，会在桶中进行键值的查找。

那么什么是 Hash 冲突呢？

如果桶的空间小于输入的空间，不同的输入可能会映射到同一个桶中，这时就会产生 Hash 冲突，如果 Hash 冲突的量很大，就会影响读取的性能。

通常 Hash 值的字节数比较少，简单的 4 个字节就够了。在 Hash 值相同的情况下，就会进一步比较桶（Bucket）中的键值，从而找到最终的数据行。

Hash 值的字节数多的话可以是 16 位、32 位等，比如采用 MD5 函数就可以得到一个 16 位或者 32 位的数值，32 位的 MD5 已经足够安全，重复率非常低。

我们模拟一下 Hash 索引。关键字如下所示，每个字母的内部编码为字母的序号，比如 A 为 01，Y 为 25。我们统计内部编码平方的第 8-11 位（从前向后）作为 Hash 值：

![8bcec05bdb4f94758e0fb4def83fe12b.png][]

Hash 索引与 B+ 树索引的区别  
我们之前讲到过 B+ 树索引的结构，Hash 索引结构和 B+ 树的不同，因此在索引使用上也会有差别。

Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。  
Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。  
Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。同理，我们也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用。  
对于等值查询来说，通常 Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

[9a99d7f9ef30c4277407bfbf44c8068e.png]: /images/20221024/cf198968de8447339b5ad3aeffb9b334.png
[cae27cfafeba178fa918b8c6545e7ec8.png]: /images/20221024/322bff459cad4ca8805747b1130b9a94.png
[1e1d89a183c4122e977413b99350ef9d.png]: /images/20221024/5e5d443d05b94252a4b14fbeac13192c.png
[6de3360ab889a4cf6e51f81fb0f5bdc1.png]: /images/20221024/f6eceda9a5cf408aa5729a6821c5e5ed.png
[8bcec05bdb4f94758e0fb4def83fe12b.png]: /images/20221024/0e99f50b630241e69053c16a5e441d38.png