mysql-索引笔记

发布于:2024-10-09 ⋅ 阅读:(37) ⋅ 点赞:(0)

索引

1、什么是索引

  • 索引是对数据库中数据的一种结构化表示。它像一本书的目录,能够快速定位信息,而无需逐行扫描所有数据。

  • 索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。

2、索引的常见模型

2.1.哈希表

用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。我们只要输入待查找的key,就可以找到其对应的值即 Value。

举例:当我们要根据用户身份证号查用户名时,哈希表索引就是通过哈希函数算出身份证号对应的数组位置,然后得到用户名的。

图源:04 | 深入浅出索引(上)-MySQL 实战 45 讲-极客时间 (geekbang.org)

  • 适用于只有等值查询的场景

  • 做区间查询的速度是很慢的

2.2.有序数组

 图源:04 | 深入浅出索引(上)-MySQL 实战 45 讲-极客时间 (geekbang.org)

  • 适合范围查询(如上图,该有序数组是根据身份证号大小递增存储的,如果我们范围搜索身份证号则速度很快)

  • 更新数据麻烦,因为数组插入一个元素需要移动后面所有的元素。

2.3.搜索树

2.3.1.二叉搜索树
  • 二叉树是搜索效率最高的

  • 因为二叉树较高,读磁盘的次数多,导致查询速度慢

2.3.2.多叉搜索树
  • 读磁盘的次数少,因此查询速度快

3、InnoDB 的索引模型

在 MySQL 中,索引是在存储引擎层实现的,因此不同的存储引擎实现的索引底层的索引模型不同。在这里我们介绍InnoDB实现的索引底层的索引模型是怎样的。

InnoDB 使用了 B+ 树索引模型实现索引。每一个索引在 InnoDB 里面对应一棵 B+ 树。

4、索引类型

索引类型分为主键索引和非主键索引。

  • 主键索引的叶子节点存的是整行数据。

  • 非主键索引的叶子节点内容是主键的值。

基于主键索引和非主键索引的查询有什么区别?

  • 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;

  • 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表

5、索引维护

B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。在维护的过程中可能出现页分裂页合并,会影响性能。

为了避免出现页分裂和页合并,我们希望尽可能使用自增主键。

5.1.什么是自增主键?

  • 主键:主键是一个表中的字段,它的值能够唯一标识表中的每一行数据,

  • 自增:每当新记录被插入到表中时,自增主键会自动加一,生成新的唯一值。

5.2.使用自增主键的好处?

  • 不会触发叶子节点的分裂。每次插入一条新记录,都是追加操作,不涉及到挪动其他记录。

  • 占用存储空间相对较小。每个非主键索引的叶子节点上都是主键的值。主键占用的空间越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

5.3.什么场景适合用业务字段直接做主键?

  • 只有一个索引:由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。

  • 该索引必须是唯一索引:因为主键是唯一标识每行数据的,因此需要是唯一索引。

6、索引使用

6.1.覆盖索引

当数据库执行查询时,如果可以从覆盖索引中获取所需的所有数据,就不需要回表。意味着查询可以直接使用索引中的数据来返回结果,从而提高查询效率。

举例:select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。这里的索引k就是覆盖索引。

6.2.最左前缀原则

最左前缀原则主要用于确定在使用联合索引时,查询条件中必须包含索引的最左侧列(即联合索引的第一列),才能有效地利用该索引。

举例:现在有一个联合索引(name,age),查询条件是"where name like ‘张 %’"。满足最左前缀原则,能有效利用这个联合索引。

建立联合索引的时候,如何安排索引内的字段顺序?

  • 第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

  • 第二考虑的原则就是空间。如果既有联合查询,又有基于 a、b 各自的查询,那么我们可以建立(a,b)、(b)索引或(b,a)、(a)索引。如果b的空间占用更大,那么则考虑建立(b,a)、(a)索引。

6.3.索引下推

索引下推指在索引查找过程中,尽可能多地将过滤条件(如 WHERE 子句中的条件)直接应用于索引,而不是在检索数据后再进行过滤。

举例:现在有联合索引(name, age),执行查询select * from tuser where name like '张%' and age=10;

因为查询条件name like '张%'满足最左前缀原则,因此在联合索引(name,age)上查询。

无索引下推执行流程:

1、在联合索引(name,age)上找到满足查询条件name like '张%'的所有行

2、依次回表查询获得完整数据

3、执行age=10的过滤。

索引下推执行流程:

1、在联合索引(name,age)上找到满足查询条件name like '张%'的所有行,并在这个过程中也检查 age=10 的条件。

2、依次回表查询获得完整数据

可以看出索引下推减少了回表的次数,提高了查询的速度。

参考

 04 | 深入浅出索引(上)-MySQL 实战 45 讲-极客时间 (geekbang.org)