MySQL常见面试题总结

前言 这是 JavaGuide 面试突击版本 ，只保留最常问的面试题，并对重点内容进行了 ⭐️ 标注。提供亮色和暗色两个主题，需要打印的朋友请选择亮色版本。 时间充裕的朋友，推荐使用 JavaGuide 网站系统学习，内容更全面深入。 如果你想要付费支持/面试辅导（比如简历优化、一对一提问、高频考点突击资料等）的话，欢迎了解我的知识星球。已经坚持维护六年，内容持续更新，虽白菜价（0.4元/天）但质量很高，主打一个良心！ 面试突击最新版可在公众号回复「 PDF 」获取（知识星球会提前同步最新版）。 重要说明 本站所有面试题保持 年度系统性优化完善 ，严格同步 Java 技术生态与招聘市场的最新动态， 确保内容时效性与前瞻性 。 这部分内容摘自 JavaGuide 下面几篇文章中的重点： 1. MySQL 常见面试题总结（MySQL 基础、存储引擎、事务、索引、锁、性能优化等） 2. MySQL 索引详解 3. MySQL 三大日志(binlog、redo log 和 undo log)详解 4. MySQL 事务隔离级别详解 5. InnoDB 存储引擎对 MVCC 的实现 6. SQL 语句在 MySQL 中的执行过程 MySQL 基础 什么是关系型数据库？ 顾名思义，关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。 关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。 大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。 有哪些常见的关系型数据库呢？ MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） ……。 什么是 SQL？ SQL 是一种结构化查询语言(Structured Query Language)，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。 几乎所有的主流关系数据库都支持 SQL ，适用性非常强。并且，一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。 SQL 可以帮助我们： 新建数据库、数据表、字段； 在数据库中增加，删除，修改，查询数据； 新建视图、函数、存储过程； 对数据库中的数据进行简单的数据分析； 搭配 Hive，Spark SQL 做大数据； 搭配 SQLFlow 做机器学习； …… 什么是 MySQL？ MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。 由于 MySQL 是开源免费并且比较成熟的数据库，因此，MySQL 被大量使用在各种系统中。任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL 的默认端口号是 3306 。 ⭐️MySQL 有什么优点？ 这个问题本质上是在问 MySQL 如此流行的原因。 MySQL 成功可以归功于在 生态、功能和运维 这三个层面上的综合优势。 第一，从生态和成本角度看，它的护城河非常深。 开源免费： 这是它得以广泛普及的基石。任何公司和个人都可以免费使用，极大地降低了技术门槛和初期成本。 社区庞大，生态完善： 经过几十年的发展，MySQL 拥有极其活跃的社区和丰富的生态系统。这意味着无论你遇到什么问题，几乎都能在网上找到解决方案；同时，市面上所有的主流编程语言、框架、ORM 工具、监控系统都对 MySQL 有完美的支持。它的文档也非常丰富，学习资源唾手可得。 第二，从核心技术功能上看，它非常强大且均衡。 强大的事务支持： 这是它作为关系型数据库的立身之本。值得一提的是，InnoDB 默认的可重复读（REPEATABLE READ）隔离级别，通过 MVCC 和 Next Key Lock 机制，很大程度上避免了幻读问题，这在很多其他数据库中都需要更高的隔离级别才能做到，兼顾了性能和一致性。详细介绍可以阅读笔者写的这篇文章：MySQL 事务隔离级别详解。 优秀的性能和可扩展性： MySQL 本身经过了海量互联网业务的严酷考验，单机性能非常出色。更重要的是，它围绕着水平扩展，形成了一套非常成熟的架构方案，比如主从复制、读写分离、以及通过中间件实现的分库分表。这让它能够支撑从初创公司到大型互联网平台的各种规模的业务。 第三，从运维和使用角度看，它非常‘亲民’。 开箱即用，上手简单： 相比于 Oracle 等大型商业数据库，MySQL 的安装、配置和日常使用都非常简单直观，学习曲线平缓，对于开发者和初级 DBA 非常友好。 维护成本低： 由于其简单性和庞大的社区，找到相关的运维人才和解决方案都相对容易，整体的维护成本也更低。 值得一提的是最近几年，PostgreSQL 的势头很猛，甚至压过了 MySQL。网上出现了很多抨击诋毁 MySQL 的文章，笔者认为任何无脑抨击其中一方或者吹捧另外一方的行为都是不可取的。 笔者也写过一篇文章分享对这两个关系型数据库代表的看法，感兴趣的可以看看：MySQL 被干成老二了？。 MySQL 字段类型 ⭐️整数类型的 UNSIGNED 属性有什么用？ MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。 例如， TINYINT UNSIGNED 类型的取值范围是 0 255，而普通的 TINYINT 类型的值范围是 128 127。INT UNSIGNED 类型的取值范围是 0 4,294,967,295，而普通的 INT 类型的值范围是 2,147,483,648 2,147,483,647。 对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。 CHAR 和 VARCHAR 的区别是什么？ CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于： CHAR 是定长字符串，VARCHAR 是变长字符串。 CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。 CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。 CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。 VARCHAR(100)和 VARCHAR(10)的区别是什么？ VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。 虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。 不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。 为什么不推荐使用 TEXT 和 BLOB？ TEXT 类型类似于 CHAR（0 255 字节）和 VARCHAR（0 65,535 字节），但可以存储更长的字符串，即长文本数据，例如博客内容。 | 类型 | 可存储大小 | 用途 | | | | | | TINYTEXT | 0 255 字节 | 一般文本字符串 | | TEXT | 0 65,535 字节 | 长文本字符串 | | MEDIUMTEXT | 0 16,772,150 字节 | 较大文本数据 | | LONGTEXT | 0 4,294,967,295 字节 | 极大文本数据 | BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。 | 类型 | 可存储大小 | 用途 | | | | | | TINYBLOB | 0 255 字节 | 短文本二进制字符串 | | BLOB | 0 65KB | 二进制字符串 | | MEDIUMBLOB | 0 16MB | 二进制形式的长文本数据 | | LONGBLOB | 0 4GB | 二进制形式的极大文本数据 | 在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。 数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如： 不能有默认值。 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。 检索效率较低。 不能直接创建索引，需要指定前缀长度。 可能会消耗大量的网络和 IO 带宽。 可能导致表上的 DML 操作变慢。 …… ⭐️DATETIME 和 TIMESTAMP 的区别是什么？如何选择？ DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。 TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。 DATETIME：'1000 01 01 00:00:00.000000' 到 '9999 12 31 23:59:59.999999' Timestamp：'1970 01 01 00:00:01.000000' UTC 到 '2038 01 19 03:14:07.999999' UTC 的核心优势在于其内建的时区处理能力。数据库负责 UTC 存储和基于会话时区的自动转换，简化了需要处理多时区应用的开发。如果应用需要处理多时区，或者希望数据库能自动管理时区转换， 是自然的选择（注意其时间范围限制，也就是 2038 年问题）。 如果应用场景不涉及时区转换，或者希望应用程序完全控制时区逻辑，并且需要表示 2038 年之后的时间， 是更稳妥的选择。 关于两者的详细对比以及日期存储类型选择建议，请参考我写的这篇文章： MySQL 时间类型数据存储建议。 NULL 和 '' 的区别是什么？ 和 (空字符串) 是两个完全不同的值，它们分别表示不同的含义，并在数据库中有着不同的行为。 代表缺失或未知的数据，而 表示一个已知存在的空字符串。它们的主要区别如下： 1. 含义 : 代表一个不确定的值，它不等于任何值，包括它自身。因此， 的结果是 ，而不是 或 。 意味着缺失或未知的信息。虽然 不等于任何值，但在某些操作中，数据库系统会将 值视为相同的类别进行处理，例如： , , 。需要注意的是，这些操作将 值视为相同的类别进行处理，并不意味着 值之间是相等的。 它们只是在特定操作中被特殊处理，以保证结果的正确性和一致性。 这种处理方式是为了方便数据操作，而不是改变了 的语义。 表示一个空字符串，它是一个已知的值。 2. 存储空间 : 的存储空间占用取决于数据库的实现，通常需要一些空间来标记该值为空。 的存储空间占用通常较小，因为它只存储一个空字符串的标志，不需要存储实际的字符。 3. 比较运算 : 任何值与 进行比较（例如 , , , 等）的结果都是 ，表示结果不确定。要判断一个值是否为 ，必须使用 或 。 可以像其他字符串一样进行比较运算。例如， 的结果是 。 4. 聚合函数 : 大多数聚合函数（例如 , , , ）会忽略 值。 会统计所有行数，包括包含 值的行。 会统计指定列中非 值的行数。 空字符串 会被聚合函数计算在内。例如， 会将其视为 0， 和 会将其视为一个空字符串。 看了上面的介绍之后，相信你对另外一个高频面试题：“为什么 MySQL 不建议使用 作为列默认值？”也有了答案。 ⭐️Boolean 类型如何表示？ MySQL 中没有专门的布尔类型，而是用 类型来表示布尔值。 类型可以存储 0 或 1，分别对应 false 或 true。 ⭐️手机号存储用 INT 还是 VARCHAR？ 存储手机号， 强烈推荐使用 VARCHAR 类型 ，而不是 INT 或 BIGINT。主要原因如下： 1. 格式兼容性与完整性： 手机号可能包含前导零（如某些地区的固话区号）、国家代码前缀（'+'），甚至可能带有分隔符（' ' 或空格）。INT 或 BIGINT 这种数字类型会自动丢失这些重要的格式信息（比如前导零会被去掉，'+' 和 ' ' 无法存储）。 VARCHAR 可以原样存储各种格式的号码，无论是国内的 11 位手机号，还是带有国家代码的国际号码，都能完美兼容。 2. 非算术性： 手机号虽然看起来是数字，但我们从不对它进行数学运算（比如求和、平均值）。它本质上是一个标识符，更像是一个字符串。用 VARCHAR 更符合其数据性质。 3. 查询灵活性： 业务中常常需要根据号段（前缀）进行查询，例如查找所有 "138" 开头的用户。使用 VARCHAR 类型配合 这样的 SQL 查询既直观又高效。 如果使用数字类型，进行类似的前缀匹配通常需要复杂的函数转换（如 CAST 或 SUBSTRING），或者使用范围查询（如 ），这不仅写法繁琐，而且可能无法有效利用索引，导致性能下降。 4. 加密存储的要求（非常关键）： 出于数据安全和隐私合规的要求，手机号这类敏感个人信息通常必须加密存储在数据库中。 加密后的数据（密文）是一长串字符串（通常由字母、数字、符号组成，或经过 Base64/Hex 编码），INT 或 BIGINT 类型根本无法存储这种密文。只有 VARCHAR、TEXT 或 BLOB 等类型可以。 关于 VARCHAR 长度的选择： 如果不加密存储（强烈不推荐！）： 考虑到国际号码和可能的格式符，VARCHAR(20) 到 VARCHAR(32) 通常是一个比较安全的范围，足以覆盖全球绝大多数手机号格式。VARCHAR(15) 可能对某些带国家码和格式符的号码来说不够用。 如果进行加密存储（推荐的标准做法）： 长度必须根据所选加密算法产生的密文最大长度，以及可能的编码方式（如 Base64 会使长度增加约 1/3）来精确计算和设定。通常会需要更长的 VARCHAR 长度，例如 VARCHAR(128), VARCHAR(256) 甚至更长。 最后，来一张表格总结一下： | 对比维度 | VARCHAR 类型（推荐） | INT/BIGINT 类型（不推荐） | 说明/备注 | | | | | | | 格式兼容性 | ✔ 能存前导零、"+"、" "、空格等 | ✘ 自动丢失前导零，不能存符号 | VARCHAR 能原样存储各种手机号格式，INT/BIGINT 只支持单纯数字，且前导零会消失 | | 完整性 | ✔ 不丢失任何格式信息 | ✘ 丢失格式信息 | 例如 "013800012345" 存进 INT 会变成 13800012345，"+" 也无法存储 | | 非算术性 | ✔ 适合存储“标识符” | ✘ 只适合做数值运算 | 手机号本质是字符串标识符，不做数学运算，VARCHAR 更贴合实际用途 | | 查询灵活性 | ✔ 支持 等 | ✘ 查询前缀不方便或性能差 | 使用 VARCHAR 可高效按号段/前缀查询，数字类型需转为字符串或其他复杂处理 | | 加密存储支持 | ✔ 可存储加密密文（字母、符号等） | ✘ 无法存储密文 | 加密手机号后密文是字符串/二进制，只有 VARCHAR、TEXT、BLOB 等能兼容 | | 长度设置建议 | 15 20（未加密），加密视情况而定 | 无意义 | 不加密时 VARCHAR(15 20) 通用，加密后长度取决于算法和编码方式 | MySQL 存储引擎 MySQL 核心在于存储引擎，想要深入学习 MySQL，必定要深入研究 MySQL 存储引擎。 MySQL 支持哪些存储引擎？默认使用哪个？ MySQL 支持多种存储引擎，你可以通过 命令来查看 MySQL 支持的所有存储引擎。 从上图我们可以查看出， MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。 我这里使用的 MySQL 版本是 8.x，不同的 MySQL 版本之间可能会有差别。 MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。 你可以通过 命令查看你的 MySQL 版本。 你也可以通过 命令直接查看 MySQL 当前默认的存储引擎。 如果你想要深入了解每个存储引擎以及它们之间的区别，推荐你去阅读以下 MySQL 官方文档对应的介绍(面试不会问这么细，了解即可)： InnoDB 存储引擎详细介绍：<https://dev.mysql.com/doc/refman/8.0/en/innodb storage engine.html 。 其他存储引擎详细介绍：<https://dev.mysql.com/doc/refman/8.0/en/storage engines.html 。 MySQL 存储引擎架构了解吗？ MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。 存储引擎是基于表的，而不是数据库。 下图展示了具有可插拔存储引擎的 MySQL 架构： 你还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎，区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。 MySQL 官方文档也有介绍到如何编写一个自定义存储引擎，地址：<https://dev.mysql.com/doc/internals/en/custom engine.html 。 ⭐️MyISAM 和 InnoDB 有什么区别？ MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，可谓是风光一时。 虽然，MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。 MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。 言归正传！咱们下面还是来简单对比一下两者： 1、是否支持行级锁 MyISAM 只有表级锁(table level locking)，而 InnoDB 支持行级锁(row level locking)和表级锁,默认为行级锁。 也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！ 2、是否支持事务 MyISAM 不提供事务支持。 InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next Key Lock）。 关于 MySQL 事务的详细介绍，可以看看我写的这篇文章：MySQL 事务隔离级别详解。 3、是否支持外键 MyISAM 不支持，而 InnoDB 支持。 外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！ 阿里的《Java 开发手册》也是明确规定禁止使用外键的。 不过，在代码中进行约束的话，对程序员的能力要求更高，具体是否要采用外键还是要根据你的项目实际情况而定。 总结：一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。 4、是否支持数据库异常崩溃后的安全恢复 MyISAM 不支持，而 InnoDB 支持。 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 。 5、是否支持 MVCC MyISAM 不支持，而 InnoDB 支持。 讲真，这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。 6、索引实现不一样。 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。 InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。 详细区别，推荐你看看我写的这篇文章：MySQL 索引详解。 7、性能有差别。 InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。 8、数据缓存策略和机制实现不同。 InnoDB 使用缓冲池（Buffer Pool）缓存数据页和索引页，MyISAM 使用键缓存（Key Cache）仅缓存索引页而不缓存数据页。 总结 ： InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。 MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。 MyISAM 不支持外键，而 InnoDB 支持。 MyISAM 不支持 MVCC，而 InnoDB 支持。 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。 MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。 InnoDB 的性能比 MyISAM 更强大。 最后，再分享一张图片给你，这张图片详细对比了常见的几种 MySQL 存储引擎。 MyISAM 和 InnoDB 如何选择？ 大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是 我们一般都会介意啊）。 《MySQL 高性能》上面有一句话这样写到: 不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。 因此，对于咱们日常开发的业务系统来说，你几乎找不到什么理由使用 MyISAM 了，老老实实用默认的 InnoDB 就可以了！ ⭐️MySQL 索引 索引是什么？ 索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。 索引的作用就相当于书的目录。打个比方：我们在查字典的时候，如果没有目录，那我们就只能一页一页地去找我们需要查的那个字，速度很慢；如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。 索引底层数据结构存在很多种类型，常见的索引结构有：B 树、 B+ 树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyISAM，都使用了 B+ 树作为索引结构。 索引的优点： 1. 查询速度起飞 (主要目的) ：通过索引，数据库可以 大幅减少需要扫描的数据量 ，直接定位到符合条件的记录，从而显著加快数据检索速度，减少磁盘 I/O 次数。 2. 保证数据唯一性 ：通过创建 唯一索引 (Unique Index) ,可以确保表中的某一列（或几列组合）的值是独一无二的，比如用户 ID、邮箱等。主键本身就是一种唯一索引。 3. 加速排序和分组 ：如果查询中的 ORDER BY 或 GROUP BY 子句涉及的列建有索引，数据库往往可以直接利用索引已经排好序的特性，避免额外的排序操作，从而提升性能。 索引的缺点： 1. 创建和维护耗时 ：创建索引本身需要时间，特别是对大表操作时。更重要的是，当对表中的数据进行 增、删、改 (DML 操作) 时，不仅要操作数据本身，相关的索引也必须动态更新和维护，这会 降低这些 DML 操作的执行效率 。 2. 占用存储空间 ：索引本质上也是一种数据结构，需要以物理文件（或内存结构）的形式存储，因此会 额外占用一定的磁盘空间 。索引越多、越大，占用的空间也就越多。 3. 可能被误用或失效 ：如果索引设计不当，或者查询语句写得不好，数据库优化器可能不会选择使用索引（或者选错索引），反而导致性能下降。 那么，用了索引就一定能提高查询性能吗？ 不一定。 大多数情况下，合理使用索引确实比全表扫描快得多。但也有例外： 数据量太小 ：如果表里的数据非常少（比如就几百条），全表扫描可能比通过索引查找更快，因为走索引本身也有开销。 查询结果集占比过大 ：如果要查询的数据占了整张表的大部分（比如超过 20% 30%），优化器可能会认为全表扫描更划算，因为通过索引多次回表（随机 I/O）的成本可能高于一次顺序的全表扫描。 索引维护不当或统计信息过时 ：导致优化器做出错误判断。 索引为什么快？ 索引之所以快，核心原因是它 大大减少了磁盘 I/O 的次数 。 它的本质是一种 排好序的数据结构 ，就像书的目录，让我们不用一页一页地翻（全表扫描）。 在 MySQL 中，这个数据结构是 B+树 。B+树结构主要从两方面做了优化： 1. B+树的特点是“矮胖”，一个千万数据的表，索引树的高度可能只有 3 4 层。这意味着，最多只需要 3 4 次磁盘 I/O ，就能精确定位到我想要的数据，而全表扫描可能需要成千上万次，所以速度极快。 2. B+树的叶子节点是 用链表连起来的 。找到开头后，就能顺着链表 顺序读 下去，这对磁盘非常友好，还能触发预读。 MySQL 索引底层数据结构是什么？ 在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，详细介绍可以参考笔者写的这篇文章：MySQL 索引详解。 为什么 InnoDB 没有使用哈希作为索引的数据结构？ 我发现很多求职者甚至是面试官对这个问题都有误解，他们相当然的认为 MySQL 底层并没有使用哈希或者 B 树作为索引的数据结构。 实际上，不论是提问还是回答这个问题都要区分好存储引擎。像 MEMORY 引擎就同时支持哈希和 B 树。 哈希索引的底层是哈希表。它的优点是，在进行 精确的等值查询 时，理论上时间复杂度是 O(1) ，速度极快。比如 。 但是，它有几个对于通用数据库来说是致命的缺点： 1. 不支持范围查询: 这是最主要的原因。哈希函数的一个特点是它会把相邻的输入值（比如 和 ）映射到哈希表中完全不相邻的位置。这种顺序的破坏，使得我们无法处理像 或 这样的范围查询。要完成这种查询，哈希索引只能退化为全表扫描。 2. 不支持排序: 同理，因为哈希值是无序的，所以我们无法利用哈希索引来优化 子句。 3. 不支持部分索引键查询: 对于联合索引，比如 ，哈希索引必须使用所有索引列进行查询，它无法单独利用 来加速查询。 4. 哈希冲突问题: 当不同的键产生相同的哈希值时，需要额外的链表或开放寻址来解决，这会降低性能。 鉴于数据库查询中范围查询和排序是极其常见的操作，一个不支持这些功能的索引结构，显然不能作为默认的、通用的索引类型。 为什么 InnoDB 没有使用 B 树作为索引的数据结构？ B 树和 B+树都是优秀的多路平衡搜索树，非常适合磁盘存储，因为它们都很“矮胖”，能最大化地利用每一次磁盘 I/O。 但 B+树是 B 树的一个增强版，它针对数据库场景做了几个关键优化： 1. I/O 效率更高: 在 B+树中，只有叶子节点才存储数据（或数据指针），而非叶子节点只存储索引键。因为非叶子节点不存数据，所以它们可以容纳更多的索引键。这意味着 B+树的“扇出”更大，在同样的数据量下，B+树通常会比 B 树更矮，也就意味着查找数据所需的磁盘 I/O 次数更少。 2. 查询性能更稳定: 在 B+树中，任何一次查询都必须从根节点走到叶子节点才能找到数据，所以查询路径的长度是固定的。而在 B 树中，如果运气好，可能在非叶子节点就找到了数据，但运气不好也得走到叶子，这导致查询性能不稳定。 3. 对范围查询极其友好: 这是 B+树最核心的优势。它的所有叶子节点之间通过一个双向链表连接。当我们执行一个范围查询（比如 ）时，只需要通过树形结构找到 的叶子节点，然后就可以沿着链表向后顺序扫描，而无需再回溯到上层节点。这使得范围查询的效率大大提高。 什么是覆盖索引？ 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index） 。 在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当使用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。 请解释一下 MySQL 的联合索引及其最左前缀原则 使用表中的多个字段创建索引，就是 联合索引 ，也叫 组合索引 或 复合索引 。 以 和 两个字段建立联合索引： 最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。 最左匹配原则会一直向右匹配，直到遇到范围查询（如 、<）为止。对于 =、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配（相关阅读：联合索引的最左匹配原则全网都在说的一个错误结论）。 假设有一个联合索引 ，其从左到右的所有前缀为 、 、 （创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。 我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。 我们这里简单演示一下最左前缀匹配的效果。 1、创建一个名为 的表，这张表只有 、 、 这 3 个字段。 2、下面我们分别测试三条不同的 SQL 语句。 再来看一个常见的面试题：如果有索引 ，查询 会走索引么？ 呢？ 呢？ 呢？ 先不要往下看答案，给自己 3 分钟时间想一想。 1. 查询 ：根据最左前缀匹配原则，查询可以使用索引的前缀部分。因此，该查询仅在 上使用索引，然后对结果进行 的过滤。 2. 查询 ：由于查询中不包含最左列 ，根据最左前缀匹配原则，整个索引都无法被使用。 3. 查询 ：和第二种一样的情况，整个索引都不会使用。 4. 查询 ：这个查询是可以用到索引的。查询优化器分析 SQL 语句时，对于联合索引，会对查询条件进行重排序，以便用到索引。会将 和 的条件进行重排序，变成 。 MySQL 8.0.13 版本引入了索引跳跃扫描（Index Skip Scan，简称 ISS），它可以在某些索引查询场景下提高查询效率。在没有 ISS 之前，不满足最左前缀匹配原则的联合索引查询中会执行全表扫描。而 ISS 允许 MySQL 在某些情况下避免全表扫描，即使查询条件不符合最左前缀。不过，这个功能比较鸡肋， 和 Oracle 中的没法比，MySQL 8.0.31 还报告了一个 bug：Bug 109145 Using index for skip scan cause incorrect result（后续版本已经修复）。个人建议知道有这个东西就好，不需要深究，实际项目也不一定能用上。 SELECT \ 会导致索引失效吗？ 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖。 哪些字段适合创建索引？ 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。 索引失效的原因有哪些？ 1. 创建了组合索引，但查询条件未遵守最左匹配原则; 2. 在索引列上进行计算、函数、类型转换等操作; 3. 以 % 开头的 LIKE 查询比如 ; 4. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到; 5. IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同); 6. 发生隐式转换; ⭐️MySQL 日志 MySQL 日志常见的面试题有： MySQL 中常见的日志有哪些？ 慢查询日志有什么用？ binlog 主要记录了什么？ redo log 如何保证事务的持久性？ 页修改之后为什么不直接刷盘呢？ binlog 和 redolog 有什么区别？ undo log 如何保证事务的原子性？ …… 上诉问题的答案可以在《Java 面试指北》(付费，点击链接领取优惠卷) 的 「技术面试题篇」 中找到。 文章地址：<https://www.yuque.com/snailclimb/mf2z3k/zr4kfk （密码获取：<https://t.zsxq.com/avfM0 ）。 ⭐️MySQL 事务 什么是事务？ 我们设想一个场景，这个场景中我们需要插入多条相关联的数据到数据库，不幸的是，这个过程可能会遇到下面这些问题： 数据库中途突然因为某些原因挂掉了。 客户端突然因为网络原因连接不上数据库了。 并发访问数据库时，多个线程同时写入数据库，覆盖了彼此的更改。 …… 上面的任何一个问题都可能会导致数据的不一致性。为了保证数据的一致性，系统必须能够处理这些问题。事务就是我们抽象出来简化这些问题的首选机制。事务的概念起源于数据库，目前，已经成为一个比较广泛的概念。 何为事务？ 一言蔽之， 事务是逻辑上的一组操作，要么都执行，要么都不执行。 事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作，这两个操作必须都成功或者都失败。 1. 将小明的余额减少 1000 元 2. 将小红的余额增加 1000 元