AlbertYang Blog

主题

Albert Yang2025-08-20
1845
6.9m
Get...

索引 ​

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准。

InnoDB的索引模型 ​

B+树 ​

B+树的每个叶子节点存放元素有限，每个叶子节点为一个 page，针对元素的数量会产生页分裂、页合并等现象。

什么是B+树？_攻城狮百里的博客-CSDN博客_b+树

聚簇索引和二级索引 ​

  • 主键索引的叶子结点存的是整行记录。InnoDB 引擎中主键索引又称为聚簇索引。
  • 非主键索引的叶子结点存的是行记录的ID。在 InnoDB 引擎中非主键索引又称为二级索引。

搜索方式 ​

  • 根据主键搜索

    sql
    select * from id = 500

    根据 id = 500 从主键索引这个 B+树中查找对应记录。

    • 首先找到500对应的叶子节点所在的 page。
    • 在 page 上通过二分法进行查找（page 内部维护了有序数组）数据所在分组。
    • 分组单向链表，遍历查询链表获取数据。

  • 根据非主键搜索

    sql
    select * from k = 5

    根据 k =5 从非主键索引这个 B+树中查找对应记录，获取主键 id = 500。再根据 id =500 从主键索引获取对应行记录。这个过程叫做 回表。

基于非主键字段进行搜索的时候，会进行回表操作。多扫描一次索引树。

索引维护 ​

B+树为了维护索引有序性，在插入新数据的时候要对顺序进行维护，调整插入数据的位置。

比如新插入的值为700的时候，需要在 R5 记录后面插入一条新纪录。

如果插入值为400，就需要在逻辑上挪动 R4、R5的位置，空出位置让400插入。

页分裂 ​

如果在插入值 700 时，R5所在数据页满了，根据 B+树的算法，需要申请一个新的数据页面。并且挪动部分数据过去，这个过程称为页分裂。

页分裂的过程会影响到性能和页面利用率。

申请新的页面后，原本一页的数据，分到两个页面，会使页面的利用率降低到50%左右。

页合并 ​

当相邻两个页面，由于数据删除导致利用率很低的时候，会将数据页合并。

为什么推荐使用自增Id作为主键 ​

从插入方式考虑：

在使用自增主键的场景下，新增数据时，会获取当前ID最大值+1，作为新增数据的主键。

此时，B+树中会递增插入，减少中间插入导致挪动的情况发生。

若选择业务字段作为主键，则无法保证插入的有序性。

使用自增ID可以保证插入的连续性，减少叶子节点的挪动。

从存储空间考虑：

非主键字段的索引的叶子节点，存储的都是主键。

假如用一个业务字段（身份证号）作为主键，占用约为20个字节。而用自增ID，整型类作为主键，则只占用4个字节。若为 bigint，则占用 8个字节。

主键长度越小，非主键索引叶子节点越小，非主键索引占用空间越小。

从插入性能和存储的情况考虑，主键使用自增ID比较合理。

适合业务字段作为主键的场景 ​

  1. 只有一个索引。
  2. 该索引必须为唯一索引。

将单独的业务字段作为主键，避免根据该字段查询的时候进行回表操作。只需要查一次索引即可。

问题记录 ​

覆盖索引 ​

在使用非主键索引时，找到了索引叶子节点上的主键后，要二次搜素主键索引获取记录，这个过程叫回表。

有些情况可以避免回表，比如只查询主键，查找非主键索引就能获取主键信息，不需要回表操作。

即非主键索引已经覆盖了查询需求，称为覆盖索引。

sql
select * from T where k between 3 and 5

执行该语句时。

  • 在 k 索引树找到 3，进行回表操作找到 R3。
  • 按顺序继续找到 5，进行回表操作找到 R4。
  • 按顺序找到 6，此时发现不匹配，结束查询。

整个过程查询了3次 k 索引，进行了2次回表。将两条记录返回给 Server 层。

sql
select ID from T where k between 3 and 5

根据 k 查找 k索引的时候，between 3 and 5 会按顺序匹配 3、5、6。6发现不匹配，结束查找。将3、5返回给 Server 层的执行器。

整个过程查询了3次 k 索引，没有回表操作。将两条记录返回给 Server 层。

覆盖索引，可以降低查找索引的次数，减少回表，显著提高查询性能。所以使用覆盖索引是一种查询SQL语句的优化方式。

联合索引 ​

假如存在 (name，age）联合索引。

索引会先按照 name 字段排序，相同 name 字段按照 age 字段排序。

联合索引的覆盖索引情况 ​

sql
select age from user where name = '张三'

根据 name 查找 age 时，索引叶子节点包含了查询的结果，无需进行回表。

但是根据 age 查找 name 的话，并不会利用到该索引。

联合索引有效情况 ​

  1. 根据 name 查找。
  2. 根据 name like ‘张%’ 进行模糊查找。

因为索引是按照前面的字段进行排序的，只有保证前面部分能匹配，就能保证有序性。进而利用到索引。

最左前缀原则 ​

在联合索引（name,age,address）情况下，满足以下规则，索引会生效。

  1. 匹配索引最左的 N 个字段。

    name = ’张三‘ and age = 10

  2. 匹配最左边字符串索引的最左 M 个字符。

    name like ‘张%’

最左前缀原则，要求我们在建立联合索引的时候，要适当选择最左边的字段。

索引下推 ​

联合索引（name，age）的情况下。

查询 name 时满足最左前缀原则，可以利用索引快速查询。但是根据 name 去查询 age 时，情况有所不同。

sql
select * from tuser where name like '张 %' and age=10 and ismale=1;

比如查询 name 姓张并且年龄10岁的男孩。

  • 在 MySQL 5.6 之前，在联合索引中根据 name like '张 %' 获取主键后，只能通过回表操作从主键索引中获取记录，再进行条件匹配。

  • 在 MySQL 5.6之后，引入了索引下推优化。在非主键索引遍历后，对索引包含的字段进行遍历，直接过滤掉不满足的记录行，减少回表的次数。

    比如查询 ID3 时，age=30，直接过滤掉，不会进行回表操作。