2026-03-29 22:30:112026-03-29 14:30:11

🔵 索引

约 3272 个字 17 行代码 1 张图片 预计阅读时间 17 分钟

MySQL 为什么使用 B+ 树来作索引，它的优势什么？

特性和定义

B+ Tree 是一种多叉树， 叶子节点才存放数据，非叶子节点只存放索引，每个节点里的数据是按主键顺序存放的。在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表。B+ Tree 存储千万级的数据只需要 3-4 层高度就可以满足，千万级的表查询目标数据最多需要 3-4 次硬盘 I/O。

B+ 树和 B 树相比：

• B+ 树所有关键码都存放在叶节点中，上层的非叶节点的关键码是其子树中最小关键码的复写
• B+ 树叶节点包含了全部关键码及指向相应数据记录存放地址的指针，且叶节点本身按关键码从小到大顺序连接
• B+ 树在搜索过程中， 如果查询和内部节点的关键字一致，那么搜索过程不停止，而是继续向下搜索这个分支

优势

• 单点查询：B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到。从平均时间代价来看， 会比 B+ 树稍快一些。但是 B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，数据量相同的情况下，B+ 树的非叶子节点可以存放更多的索引，查询底层节点的 硬盘 I/O 次数会更少
• 插入和删除效率：B+ 树有大量的冗余节点，删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，删除非常快。B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂(如果节点饱和)，但是最多只涉及树的一条路径。B 树没有冗余节点，插入或删除节点的时候非常复杂，可能涉及复杂的树的变形
• 范围查询：B+ 树所有叶子节点间有一个链表进行连接，而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询， 范围查询效率不如 B+ 树。B+ 树的插入和删除范围查询效率更高。存在大量范围检索的场景适合使用 B+ 树，比如数据库。而对于大量的单个索引查询的场景可以考虑 B 树，比如 nosql 的 MongoDB

对比

• B+ Tree 对比 B Tree：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的硬盘 I/O 次数下，就能查询更多的节点。B+ Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点
• B+ Tree 对比 二叉树：对于有 N 个叶子节点的 B+ Tree， 其搜索复杂度为 \(O(log_dN)\)，其中 d 表示节点允许的最大子节点个数。在实际的应用当中， d 值是大于 100 的，即使数据达到千万级别时，B+ Tree 的高度依然维持在 3~4 层左右，一次数据查询操作只需要做 3~4 次的硬盘 I/O 操作就能查询到。二叉树的每个父节点的儿子节点个数是 2 个，意味着 其搜索复杂度为 \(O(log_2N)\)，二叉树检索到目标数据所经历的 硬盘 I/O 次数要更多
• B+ Tree 对比 Hash：Hash 在做等值查询的时候效率高，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询

索引有哪些种类？

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
  • 建表时加上 key(列名) 指定
  • 单独创建： create index 索引名 on 表名(列名)
  • 单独创建： alter table 表名 add index 索引名(列名)
• 唯一索引：索引列的值必须唯一，但 允许有 null 且 null 可以出现多次
  • 建表时加上 unique(列名) 指定
  • 单独创建： create unique index idx 表名(列名) on 表名(列名)
  • 单独创建： alter table 表名 add unique 索引名(列名)
• 主键索引：设定为主键后数据库会自动建立索引，innodb 为聚簇索引，值 必须唯一且不能为 null
  • 建表时加上 primary key(列名) 指定
• 复合索引：即一个索引包含多个列
  • 建表时加上 key(列名列表) 指定
  • 单独创建： create index 索引名 on 表名(列名列表)
  • 单独创建： alter table 表名 add index 索引名(列名列表)
• 前缀索引：对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率
  • 单独创建： alter table 表名 add index 索引名(列名(索引长度))

什么是最左匹配原则？

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。使用联合索引进行查询的时候，如果不遵循最左匹配原则，联合索引会失效。

以联合索引(a, b, c)为例

因为 (a, b, c) 联合索引是先按 a 排序；在 a 相同的情况再按 b 排序；在 b 相同的情况再按 c 排序。所以 b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。利用索引的前提是索引里的 key 是有序的

联合索引的最左匹配原则在遇到范围查询(如 >、<)的时候停止匹配，范围查询的字段可以用到联合索引，在范围查询字段的后面的字段无法用到联合索引。

注意：对于 >=、<=、BETWEEN、like 前缀匹配的范围查询并不会停止匹配

索引区分度？

查询优化器发现某个值出现在表的数据行中的百分比(惯用的百分比界线是"30%")很高的时候会忽略索引，进行全表扫描。

联合索引如何进行排序？

给索引列和排序列建立一个联合索引，在查询时查到一个索引之后，还要对 create_time 排序，用到文件排序 filesort。在 SQL 执行计划中，Extra 列会出现 Using filesort。

可以利用索引的有序性，在排序列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

使用索引会有哪些缺陷？

虽然索引大大提高了查询速度，同时却会 降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。

因为更新表时，MySQL 不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

什么时候需要/不需要创建索引？

需要创建索引

• 表的主关键字：自动建立唯一索引
• 表的字段唯一约束：利用索引来保证数据的完整
• 直接条件查询的字段：经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度
• 查询中与其它表关联的字段：例如字段建立了外键关系
• 查询中排序的字段：排序的字段如果通过索引去访问将大大提高排序速度
• 查询中统计或分组统计的字段：经常用于 GROUP BY 和 ORDER BY 的字段，可以创建联合索引

不需要创建索引

• 表记录太少：表数据太少的时候，不需要创建索引
• 经常插入、删除、修改的字段：经常更新的字段不用创建索引，索引字段频繁修改，由于要维护 B+ Tree 的有序性，那么就需要频繁的重建索引，会影响数据库性能
• 数据重复且分布平均的表字段：假如一个表有10万行记录，性别只有男和女两种值，且每个值的分布概率大约为50%，那么对这种字段建索引一般不会提高数据库的查询速度
• 经常和主字段一块查询但主字段索引值比较多的表字段

索引的优化(使用索引的注意事项)？

• like 语句的前导模糊查询不能使用索引
  SQL

    select * from doc where title like '%XX';   --不能使用索引
    select * from doc where title like 'XX%';   --非前导模糊查询，可以使用索引
  

  union、in、or 都能够命中索引，建议使用 in，因为 in 的综合效率最高。
• 负向条件查询不能使用索引 负向条件有：!=、<>、not in、not exists、not like 等，优化案例：
  SQL

  select * from doc where status != 1 and status != 2;  --优化前
  select * from doc where status in (0,3,4);            --优化后
  

• 联合索引最左前缀原则 如果在 (a, b, c) 三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c) 组索引。
  • 建立联合索引的时候，区分度最高的字段在最左边
  • 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列
  • 最左前缀查询时，并不是指 SQL 语句的 where 顺序要和联合索引一致
• 不能使用索引中范围条件右边的列(范围列可以用到索引)，范围列之后列的索引全失效
  • 范围条件有：<、<=、>、>=、between 等
  • 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引
  • 假如有联合索引 (emp_no、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 则使用不到索引
    SQL

      select * from employees.titles 
      where emp_no < 10010' and title='Senior Engineer' and 
      from_date between '1986-01-01' and '1986-12-31';
    

• 不要在索引列上面做任何操作(计算、函数)，否则会导致索引失效而转向全表扫描
  SQL

    select * from doc where YEAR(create_time) <= '2016'; --优化前`
    select * from doc where create_time <= '2016-01-01'; --优化后
  

• 强制类型转换会全表扫描
  SQL

    select * from doc where YEAR(create_time) <= '2016'; --优化前
    select * from doc where create_time <= '2016-01-01'; --优化后
  

• 更新十分频繁、数据区分度不高的列不宜建立索引
  • 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能
  • "性别"这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
  • 一般区分度在 80% 以上的时候就可以建立索引，区分度可以使用 count(distinct(列名)) / count(*) 来计算
• 利用覆盖索引来进行查询操作，避免回表，减少 select * 的使用
  • 覆盖索引：查询的列和所建立的索引的列个数相同，字段相同
  • 被查询的列：数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度
  • 可以建立 (login_name, passwd, login_time) 的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询
    SQL

      select uid, login_time 
      from user 
      where login_name=? and passwd=?;
    

• 索引不会包含有 NULL 值的列，IS NULL，IS NOT NULL 无法使用索引
  • 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时，尽量使用 NOT NULL 约束以及默认值
• 如果有 order by、group by 的场景，利用索引的有序性
  • order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能
• 使用短索引(前缀索引)
  • 对列进行索引，如果可能应该 指定一个前缀长度。例如，如果有一个 CHAR(255) 的列，如果该列在前 10 个或 20 个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就 不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省硬盘空间和 I/O 操作，减少索引文件的维护开销。可以使用 count(distinct leftIndex(列名, 索引长度)) / count(*) 来计算前缀索引的区分度
  • 缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引
  • 很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可
• 利用延迟关联或者子查询优化超多分页场景
  • MySQL 并不是跳过 offset 行，而是取 offset + N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写
• 如果明确知道只有一条结果返回，limit 1 能够提高效率
  SQL

    select * from user where login_name=?;
    // 可以优化为
    select * from user where login_name=? limit 1
  

• 超过三个表最好不要 join
  • 需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引
  • 例如：left join 是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用 right join
• 单表索引建议控制在 5 个以内
• SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好
  • consts：单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据
  • ref：使用普通的索引(Normal Index)
  • range：对索引进行范围检索
  • 当 type = index 时：索引物理文件全扫，速度非常慢
• 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
  • 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的

WHERE 语句索引使用的注意事项？

• where 子句使用的所有字段，都必须建立索引
• 确保 MySQL 版本 5.0 以上，且查询优化器 开启了 index_merge_union = on，也就是变量 optimizer_switch 里存在 index_merge_union 且为 on

索引什么时候会失效？

• 查询条件中 带有 or，除非所有的查询条件都建有索引，
• like 查询是以 % 开头，索引会失效
• 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则索引失效
• 索引列上参与计算，索引失效
• 违背最左匹配原则，索引失效
• 如果 MySQL 估计全表扫描要比使用索引要快，索引失效

评论