MySQL 深入浅出系列03—索引的基础

七七鸽

已于 2024-03-15 21:18:02 修改

阅读量130

点赞数 2

分类专栏： MySQL 深入浅出系列文章标签： mysql 数据库

于 2024-03-15 12:21:22 首次发布

本文链接：https://blog.csdn.net/m0_45322619/article/details/136736253

版权

MySQL 深入浅出系列专栏收录该内容

5 篇文章 0 订阅

订阅专栏

索引的基础

索引的基础
- 索引的类型
索引的优点

索引 (MySQL 中也叫做“键”) 是存储引擎用于快速找到记录的一种数据结构。数据量越大，索引对性能影响越大。索引优化应该是对查询性能优化最有效的手段。

索引的基础

在 MySQL 中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。如果在 actor_id 列上建有索引，则 MySQL 将使用该索引找到 actor_id 为 5 的行，MySQL 先在索引上按值进行查找，然后返回所有包含该值的数据行。索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能高效地使用索引的最左前级列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

索引的类型

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的。并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

B-Tree 索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是 B- Tree 索引，它使用 B-Tree 数据结构来存储数据。大多数 MySQL 引擎都支持这种索引。底层的存储引擎也可能使用不同的存储结构，例如，NDB 集群存储引擎内部实际上使用了 T-Tree 结构存储这种索引，即使其名字是 BTREE ；InnoDB 则使用的是 B+Tree 即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。B-Tree 通常意味着所有的值是按顺序存储的，并且每一个叶子节点到根的距离相同。图展示了 B-Tree 索引的抽象表示，大致反映了 InnoDB 索引是如何工作的。MyISAM 是使用的结构有所不同，但基本思想是类似的。在这里插入图片描述
B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点 (图示并未画出)开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点⻚的值和要查找的值可以找到合适的指针进人下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点⻚ (不同引擎的“指针” 类型不同)。B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。

可以使用 B-Tree 索引的查询类型。B-Tree 索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前级的查找。前面所述的索引对如下类型的查询有效：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引的查询。因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的 ORDER BY 操作 ( 按顺序查找 )。一般来说，如果 B-Tree 可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果 ORDER BY 子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Tree 索引的限制：

一如果不是按照索引的最左列开始查找，则无法使用索引。
二不能跳过索引中的列。
三如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

哈希索引 (bashindex) 基于哈希表实现，只有精确匹配索引所有列的查询才有效生效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码 (hash code) ，哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在 MySQL 中，只有 Memory 引擎显式支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

哈希索引的限制：

一哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
二哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
三哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。如在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。
四哈希索引只支持等值比较，不支持范围比较。
五速度快，除非有哈希冲突，存储引擎会遍历链表中所有行指针，逐个进行比较，直到找到符合条件的行。
六冲突多维护代价也大。

InnoDB “自适应哈希”。当遇到某些索引值被使用的非常频繁，是他会在内存中基于 B-Tree 之上，再创建一个哈希索引，所以让其具有哈希索引的优点，全自动内部行为，无法控制和配置，可以关闭。

创建自定义哈希。当存储引擎不支持哈希索引，可以模拟 InnoDB 创建。思路：不用键值而用哈希值查找的建立在 B-Tree上的伪哈希。需要手动的在查询 Where 子句手动指定使用哈希函数。要避免冲突，必须在 WHERE 条件中带入哈希值和对应列值，只是统计计数可不带。