【后端面经】【数据库与MySQL】为什么MySQL用B+树而不用B树?-02

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【4月更文挑战第11天】数据库索引使用规则:`AND`用`OR`不用,正用反不用,范围中断。索引带来空间和内存代价,包括额外磁盘空间、内存占用和数据修改时的维护成本。面试中可能涉及B+树、聚簇索引、覆盖索引等知识点。MySQL采用B+树,因其利于范围查询和内存效率。数据库不使用索引可能因`!=`、`LIKE`、字段区分度低、特殊表达式或全表扫描更快。索引与NULL值处理在不同数据库中有差异,MySQL允许NULL在索引中的使用。
  • 如果查询条件是where A=a1 or B=b1,数据库不会使用这个索引
for a in A {
   
  if a == a1 {
   
      as = append(as, a)
  }
}
for b in B {
   
  if b == b1 {
   
      bs = append(bs, b)
  }
}
// as 和 bs 的并集就是你要的结果
  • 如果查询条件是WHERE A=a1 AND B > b1 AND C = c1,查询只会使用索引的A和B两列
for a in A {
   
  if a == a1 {
   
      for b in B {
   
        if b > b1 {
   
          // C 是无序的,所以用不了。你可以从前面的表格里面看出来
          // 比如说 b > 23 之后,对应的 C 是乱序的
          // 这就是你要的结果,去磁盘里面读取
        }
      }
  }
}
  • 如果查询条件是 WHERE A !=a1,那么这个查询也不会使用索引。

判断会不会使用索引,需要按照组成索引的列的顺序,从左往右:AND 用 OR 不用,正用反不用,范围则中断。这里只是联合索引的规则,有一些意外的情况也不符合这个口诀描述的规律,比如M=1 OR N=2,如果单列M上有一个索引,单列N上也有一个索引,可能也会使用M/N上的两个索引。可以看EXPLAIN命令的输出。

索引的代价

索引是一个以空间换时间的设计思想,会消耗很多的系统资源,比如:

  1. 额外的磁盘空间:索引需要存储在磁盘里
  2. 额外的内存空间:运行的时候,索引会被加载到内存里
  3. 额外的计算:在增删改数据的时候,数据库还需要同步维护索引

常见面试问题

索引和索引优化,强调一般情况下,忽略数据库有别的索引、或者查询条件过滤效果不好导致数据库不使用索引的情况

  • 性能优化 -> 索引优化
  • 是否了解B树、B+树
  • 聚簇索引、覆盖索引
  • 数据一定会索引吗
  • 使用索引的性能一定好吗?综合考虑索引本身的开销以及数据库不用索引的情况

  • 基本介绍索引的思路是按照B+树、索引分类和最左分配原则来介绍。

数据结构方面,MySQL里的索引主要是B+树,它的查询性能更好,适合范围查询,也适合放在内存里。

MySQL的索引可以从不同的角度进行分类,比如根据叶子节点是否包含数据分为聚簇索引和非聚簇索引、包含某个查询的全部列的覆盖索引等。

数据库使用索引时遵循最左匹配原则,但是数据库最终会不会使用索引,跟查询/数据量有关。

  • 为什么MySQL使用B+树,而没有使用B树/二叉树/红黑树/跳表?

一个数据结构是否适合数据库索引,取决于数据结构的增删改查性能,而且还要去范围查询友好,减少内存消耗和磁盘IO次数。

  1. 与B+树相比,B树的数据存储在全部节点里,而且叶子节点没有用链表进行连接,对范围查询不友好。非叶子节点里存储了数据,导致内存里难以放下全部非叶子节点,意味着查询非叶子节点的时候就需要磁盘IO。
  2. 与B+树相比,平衡二叉树、红黑树在同等数据量下,高度更高,磁盘IO次数更多,性能更差。而且它们会频繁执行再平衡过程,来保证树形结构平衡。
  3. 与B+树相比,跳表在极端情况下会退化为链表,平衡性差。而且跳表的查询时间无法预估,需要更多的内存
  • 为什么数据库不使用索引

    比如前面的索引<A,B,C>里,假设A的所有值都是正数,使用查询条件where A > -1的时候,数据库会觉得还不如全表扫描,所以实际并不会用索引。

    一般来说,数据库可能不使用索引的原因有以下几种:

    • 使用了!=,LIKE之类的查询
    • 字段区分度不大,比如A列只有0,1两个值
    • 使用特殊表达式,比如数学运算和函数调用
    • 数据量太小,或是觉得全表扫描更快的时候

    上述的原因只是可能的情况,LIKE查询的时候,如果只是Like ab%这种前缀查询,可能也会使用索引。

    还有一些SQL提示,比如FORCE INDEX(强迫使用索引)、USE INDEX(使用索引)或者 IGNORE INDEX(忽略索引),关键是取决于数据库。

    还有一种说法是,含有NULL的列上的索引会失效。不过这个说法并不准确,实际上MySQL还是会尽可能的使用索引。

  • 索引和NULL值

    不同的数据库可能会有差异。

    MySQL本身会尽可能的使用索引,即使索引的某个列有零值,而且IS NULLIS NOT NULL都可以使用索引。而且MySQL的唯一索引里允许多行的值都是NULL。

    但是不管从什么角度,使用NULL都是比较差的写法

其他思考

有没有遇到过索引设计不合理引发的线上事故?如何定位问题+解决的?

有没有因为NULL遇到过问题?

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
11天前
|
JSON 前端开发 Java
管理系统总结(前端:Vue-cli, 后端Jdbc连接mysql数据库,项目部署tomcat里)
管理系统总结(前端:Vue-cli, 后端Jdbc连接mysql数据库,项目部署tomcat里)
|
11天前
|
安全 关系型数据库 MySQL
node实战——后端koa结合jwt连接mysql实现权限登录(node后端就业储备知识)
node实战——后端koa结合jwt连接mysql实现权限登录(node后端就业储备知识)
28 3
|
11天前
|
关系型数据库 MySQL 数据库
【后端面经】【数据库与MySQL】12|数据库锁:明明有行锁,怎么突然就加了表锁?
【4月更文挑战第15天】在MySQL的InnoDB引擎中,锁通过索引实现,主要锁定叶子节点。查询使用哪个索引,就锁哪个;无索引时锁全表。若查询值不存在,InnoDB会构造临键锁阻止插入。锁在事务结束(Rollback或Commit)时释放。乐观锁在尝试更新时检查数据变化,适合读多写少场景,悲观锁一开始就加锁,适用于写多读少。InnoDB支持行锁和表锁,行锁依赖索引。共享锁允许多个线程加同类型锁,排它锁则独占。
45 1
|
11天前
|
SQL NoSQL 关系型数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?-02
【4月更文挑战第13天】该文介绍了几个数据库查询优化技巧。首先,创建覆盖索引如&lt;A,B,C&gt;能加速`select A,B,C from student where A=? and B=? and C=?`的执行。其次,为常用于排序的列建立索引,如在`id,update_time`上建索引,可避免数据排序,显著提高查询速度。优化`count(*)`可通过预估值或使用Redis记录总数,但需注意数据一致性问题。使用索引提示如FORCE INDEX可强制使用特定索引,但应谨慎。将`having`的非聚合条件移到`where`里可提升效率。最后,处理深度分页时
26 3
|
9天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
88 0
|
11天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
68 0
|
4天前
|
存储 SQL 关系型数据库
【MySQL】数据库基础 -- 详解
【MySQL】数据库基础 -- 详解
|
11天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
52 0
|
11天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
57 0
|
4天前
|
存储 数据可视化 关系型数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库