【Java面试题】MySQL上篇(索引)

2024-04-09 10:04

本文主要是介绍【Java面试题】MySQL上篇(索引),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 索引
    • 1.索引的分类?
    • 2.B树和B+树的区别?
      • 2.1B树
      • 2.2B+树
    • 3.为什么使用索引会加快查询?
    • 4.创建索引的注意点?
    • 5.索引在哪些情况下会失效?
    • 6.聚簇索引和非聚簇索引的区别?
    • 7.回表查询是什么?
    • 8.什么是最左前缀原则/最左匹配原则?
    • 9.什么是索引下推优化?

索引

1.索引的分类?

  • 三个不同维度划分:
    • 功能分类:主键索引、唯一索引、普通索引、全文索引
    • 数据结构:B+树索引、哈希索引
    • 存储位置:聚簇索引、非聚簇索引

2.B树和B+树的区别?

2.1B树

  1. 平衡二叉树:节点最多有两个子树;
  2. B树:一棵多路平衡查找树,每个节点可以有多个子树(M 阶 B 树表示该树每个节点最多有 M 个子树
  3. 平衡二叉树每个节点只有一个数据两个指向孩子的指针
  4. B 树每个中间节点有 k-1 个关键字(可以理解为数据)和 k 个子树( k介于阶数 M 和 M/2 之间,M/2 ⬆️向上取整)

在这里插入图片描述

  1. 应用场景:文件系统和数据库系统中常用的B/B+ 树,他通过对每个节点存储个数的扩展,使得对连续的数据能够进行较快的定位和访问,能够有效减少查找时间,提高存储的空间局部性从而减少IO操作。他广泛用于文件系统及数据库中,如:

2.2B+树

  1. 特点:

    1. 节点的子树数和关键字数相同(B 树是关键字数比子树数少一);
    2. 叶子节点包含了全部数据,同时符合左小右大的顺序;
    3. 非叶子节点仅用作索引,它的关键字和子节点有重复元素

    在这里插入图片描述

  2. 优点:

    1. 层级更低,IO 次数更少
    2. 每次都需要查询到叶子节点,查询性能稳定
    3. 叶子节点形成有序链表,范围查询方便
  3. 分类:

    • B+树索引又可分为聚簇索引和非聚簇索引
    • 聚簇索引的叶子节点存放的是整张表的行记录数据、非聚簇索引的叶子节点存放的是相遇行数据的指针地址

img

3.为什么使用索引会加快查询?

  1. 有了索引,就可以直接跳到索引指示的数据位置,而不必扫描整张表,从而大大减少了磁盘 I/O 操作的次数。
  2. MySQL 的 InnoDB 存储引擎默认使用 B+ 树来作为索引的数据结构,而 B+ 树的查询效率非常高,时间复杂度为 O(logN)
  3. 索引文件相较于数据库文件,体积小得多,查到索引之后再映射到数据库记录,查询效率就会高很多。

4.创建索引的注意点?

  1. 选择合适的列作为索引

    • 经常作为 查询条件(WHERE 子句)、排序条件(ORDER BY 子句)、分组条件(GROUP BY 子句) 的列是建立索引的好候选。

    • 区分度低的字段,例如性别,不要建索引

    • 频繁更新的字段,不要作为主键或者索引

    • 不建议用无序的值(例如身份证、UUID )作为索引,当主键具有不确定性,会造成叶子节点频繁分裂,出现磁盘存储的碎片化

  2. 避免过多的索引

    • 每个索引都需要占用额外的磁盘空间。

    • 更新表(INSERT、UPDATE、DELETE 操作)时,所有的索引都需要被更新。

    • 维护索引文件需要成本;还会导致页分裂,IO 次数增多。

  3. 利用前缀索引和索引列的顺序

    • 对于字符串类型的列,可以考虑使用前缀索引来减少索引大小。

    • 在创建复合索引时,应该根据查询条件将最常用作过滤条件的列放在前面。

5.索引在哪些情况下会失效?

  1. 运算:对索引列运算(如,+、-、*、/),索引失效。
  2. 函数:在索引列上使用 mysql 的内置函数,索引失效。
  3. 范围查询:索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效。
  4. 使用%XXX左模糊查询失效,因为mysql是最左原则,使用XXX%右模糊查询是可以使用索引的,但是左模糊违背了最左原则所以不行
  5. 字段类型不匹配导致的索引失效:where 条件字段类型与实际表中字段类型不匹配的时候,Mysql 会进行隐式的数据类型转换,而类型转换会使用到内置函数,导致在进行数据查询的时候并没有使用索引。
  6. 查询条件包含 or,可能会导致索引失效:or 分割的条件,如果 or 左边的条件存在索引,而右边的条件没有索引,不走索引
  7. 联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。

6.聚簇索引和非聚簇索引的区别?

  1. 聚簇索引直接将数据存储在 B+树的叶子节点中,而非聚簇索引的叶子节点存储的是指向数据行的指针。
  2. 一个表只能有一个聚簇索引,但可以有多个非聚簇索引。
  3. 聚簇索引改善了顺序访问的性能,但更新主键的成本较高;非聚簇索引适合快速插入和更新操作,但检索数据可能需要更多的磁盘 I/O。

7.回表查询是什么?

  1. 在 InnoDB 存储引擎里,利用辅助索引查询,先通过辅助索引找到主键索引的键值
  2. 再通过主键值查出主键索引里面没有符合要求的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程就叫回表。

8.什么是最左前缀原则/最左匹配原则?

  1. 最左匹配原则:在 InnoDB 的联合索引中,查询的时候只有匹配了前一个/左边的值之后,才能匹配下一个。

  2. 根据最左匹配原则,我们创建了一个组合索引,如 (a1,a2,a3),相当于创建了(a1)、(a1,a2)和 (a1,a2,a3) 三个索引。

  3. 为什么不从最左开始查,就无法匹配呢?

    比如有一个 user 表,我们给 name 和 age 建立了一个组合索引。

    ALTER TABLE user add INDEX comidx_name_phone (name,age);
    

    组合索引

从这张图可以看出来,name 是有序的,age 是无序的。当 name 相等的时候, age 才是有序的。

这个时候我们使用 where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候, B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。如果 name 相同的时候再比较 age。但是如果查询条件没有 name,就不知道下一步应该查哪个 节点,因为建立搜索树的时候 name 是第一个比较因子,所以就没用上索引

9.什么是索引下推优化?

  1. 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。
  2. 如图为MySQL的大体架构:

image-20240408202240143

  1. MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。

  2. 索引下推下推其实就是指将部分上层(服务层)负责的事情交给了下层(引擎层)去处理

  3. 在没有使用ICP的情况下,MySQL的查询:

    • 存储引擎读取索引记录;
    • 根据索引中的主键值,定位并读取完整的行记录;
    • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件
  4. 使用ICP的情况下,查询过程:

    • 存储引擎读取索引记录 (不是完整的行记录)
    • 判断 WHERE条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;
    • 条件满足,使用索引中的主键去定位并读取完整的行记录(就是所谓的回表);
    • 存储引擎把记录交给Server层,Server层检测该记录是否满足WHERE条件的其余部分。
  5. 例子:例如一张表,建了一个联合索引(name, age),查询语句:select * from t_user where name like '张%' and age=10;,由于name使用了范围查询,根据最左匹配原则:

没有使用 ICP

但是,使用了索引下推优化,把 where 的条件放到了引擎层执行,直接根据name like '张%' and age=10的条件进行过滤,减少了回表的次数。

使用 ICP

在这里插入图片描述

这篇关于【Java面试题】MySQL上篇(索引)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/887855

相关文章

Spring Security常见问题及解决方案

《SpringSecurity常见问题及解决方案》SpringSecurity是Spring生态的安全框架,提供认证、授权及攻击防护,支持JWT、OAuth2集成,适用于保护Spring应用,需配置... 目录Spring Security 简介Spring Security 核心概念1. ​Securit

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

SpringBoot+EasyPOI轻松实现Excel和Word导出PDF

《SpringBoot+EasyPOI轻松实现Excel和Word导出PDF》在企业级开发中,将Excel和Word文档导出为PDF是常见需求,本文将结合​​EasyPOI和​​Aspose系列工具实... 目录一、环境准备与依赖配置1.1 方案选型1.2 依赖配置(商业库方案)二、Excel 导出 PDF

SpringBoot改造MCP服务器的详细说明(StreamableHTTP 类型)

《SpringBoot改造MCP服务器的详细说明(StreamableHTTP类型)》本文介绍了SpringBoot如何实现MCPStreamableHTTP服务器,并且使用CherryStudio... 目录SpringBoot改造MCP服务器(StreamableHTTP)1 项目说明2 使用说明2.1

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

spring中的@MapperScan注解属性解析

《spring中的@MapperScan注解属性解析》@MapperScan是Spring集成MyBatis时自动扫描Mapper接口的注解,简化配置并支持多数据源,通过属性控制扫描路径和过滤条件,利... 目录一、核心功能与作用二、注解属性解析三、底层实现原理四、使用场景与最佳实践五、注意事项与常见问题六

Spring的RedisTemplate的json反序列泛型丢失问题解决

《Spring的RedisTemplate的json反序列泛型丢失问题解决》本文主要介绍了SpringRedisTemplate中使用JSON序列化时泛型信息丢失的问题及其提出三种解决方案,可以根据性... 目录背景解决方案方案一方案二方案三总结背景在使用RedisTemplate操作redis时我们针对

Java中Arrays类和Collections类常用方法示例详解

《Java中Arrays类和Collections类常用方法示例详解》本文总结了Java中Arrays和Collections类的常用方法,涵盖数组填充、排序、搜索、复制、列表转换等操作,帮助开发者高... 目录Arrays.fill()相关用法Arrays.toString()Arrays.sort()A

Spring Boot Maven 插件如何构建可执行 JAR 的核心配置

《SpringBootMaven插件如何构建可执行JAR的核心配置》SpringBoot核心Maven插件,用于生成可执行JAR/WAR,内置服务器简化部署,支持热部署、多环境配置及依赖管理... 目录前言一、插件的核心功能与目标1.1 插件的定位1.2 插件的 Goals(目标)1.3 插件定位1.4 核

如何使用Lombok进行spring 注入

《如何使用Lombok进行spring注入》本文介绍如何用Lombok简化Spring注入,推荐优先使用setter注入,通过注解自动生成getter/setter及构造器,减少冗余代码,提升开发效... Lombok为了开发环境简化代码,好处不用多说。spring 注入方式为2种,构造器注入和setter