mysql-索引笔记

索引

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)