【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 boot整合dubbo+zookeeper的详细过程

《Springboot整合dubbo+zookeeper的详细过程》本文讲解SpringBoot整合Dubbo与Zookeeper实现API、Provider、Consumer模式,包含依赖配置、... 目录Spring boot整合dubbo+zookeeper1.创建父工程2.父工程引入依赖3.创建ap

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步:打开官网下载对应文件第二步:程序安装配置第三部:安装工具SQL Server Manageme

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用:如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念:DATEDIFF 究竟在计算什么?2. 主流数据库中的 DATEDIFF 实现2.1

SpringBoot结合Docker进行容器化处理指南

《SpringBoot结合Docker进行容器化处理指南》在当今快速发展的软件工程领域,SpringBoot和Docker已经成为现代Java开发者的必备工具,本文将深入讲解如何将一个SpringBo... 目录前言一、为什么选择 Spring Bootjavascript + docker1. 快速部署与

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集(如UTF-8)的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1:计算字符串

Spring Boot spring-boot-maven-plugin 参数配置详解(最新推荐)

《SpringBootspring-boot-maven-plugin参数配置详解(最新推荐)》文章介绍了SpringBootMaven插件的5个核心目标(repackage、run、start... 目录一 spring-boot-maven-plugin 插件的5个Goals二 应用场景1 重新打包应用

SpringBoot+EasyExcel实现自定义复杂样式导入导出

《SpringBoot+EasyExcel实现自定义复杂样式导入导出》这篇文章主要为大家详细介绍了SpringBoot如何结果EasyExcel实现自定义复杂样式导入导出功能,文中的示例代码讲解详细,... 目录安装处理自定义导出复杂场景1、列不固定,动态列2、动态下拉3、自定义锁定行/列,添加密码4、合并

Spring Boot集成Druid实现数据源管理与监控的详细步骤

《SpringBoot集成Druid实现数据源管理与监控的详细步骤》本文介绍如何在SpringBoot项目中集成Druid数据库连接池,包括环境搭建、Maven依赖配置、SpringBoot配置文件... 目录1. 引言1.1 环境准备1.2 Druid介绍2. 配置Druid连接池3. 查看Druid监控