MySQL常见面试题总结

更新2/13/2026

字数0

时长0 分钟

1. 事务的四大特性？

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性（A）：事务要么全成，要么全败，不存 “部分执行”。比如转账，要么双方金额都变，要么都不变。
一致性（C）：事务执行前后，必须满足所有预设的业务规则和限制约束。比如转账总金额不变，余额不能为负。
隔离性（I）：多事务并发时互不干扰，默认级别下，同一事务多次读数据结果一致。
持久性（D）：事务提交后，数据永久保存，就算断电崩溃也不会丢。

2. 事务隔离级别有哪些？

先了解下几个概念：脏读、不可重复读、幻读。

脏读： 是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读： 同一事务内，多次读取同一数据，结果不一致（因中间被其他事务提交的修改干扰）。
幻读： 同一事务内，多次执行相同的范围查询，结果集中的行数不一致（因中间被其他事务提交的插入 / 删除操作干扰）。

不可重复读和脏读的区别： 脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL数据库为我们提供的四种隔离级别：

Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。（整个事务期间只使用第一次创建的Read View 保证同一事务内多次读取同一数据结果一致通过固定的一致性视图实现可重复读）
Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

3. 编码和字符集的关系

我们平时可以在编辑器上输入各种中文英文字母，但这些都是给人读的，不是给计算机读的，其实计算机真正保存和传输数据都是以二进制0101的格式进行的。

那么就需要有一个规则，把中文和英文字母转化为二进制。其中d对应十六进制下的64，它可以转换为01二进制的格式。于是字母和数字就这样一一对应起来了，这就是ASCII编码格式。

它用一个字节，也就是8位来标识字符，基础符号有128个，扩展符号也是128个。也就只能表示下英文字母和数字。

这明显不够用。于是，为了标识中文，出现了GB2312的编码格式。为了标识希腊语，出现了greek编码格式，为了标识俄语，整了cp866编码格式。

为了统一它们，于是出现了Unicode编码格式，它用了2~4个字节来表示字符，这样理论上所有符号都能被收录进去，并且它还完全兼容ASCII的编码，也就是说，同样是字母d，在ASCII用64表示，在Unicode里还是用64来表示。

但不同的地方是ASCII编码用1个字节来表示，而Unicode用则两个字节来表示。

同样都是字母d，unicode比ascii多使用了一个字节，如下：

mysql

D   ASCII:           01100100
D Unicode:  00000000 01100100

可以看到，上面的unicode编码，前面的都是0，其实用不上，但还占了个字节，有点浪费。如果我们能做到该隐藏时隐藏，这样就能省下不少空间，按这个思路，就是就有了UTF-8编码。

总结一下，按照一定规则把符号和二进制码对应起来，这就是编码。而把n多这种已经编码的字符聚在一起，就是我们常说的字符集。

比如utf-8字符集就是所有utf-8编码格式的字符的合集。

想看下mysql支持哪些字符集。可以执行 show charset;

4. utf8和utf8mb4的区别

UTF-8 和 UTF8mb4 都是 Unicode 字符集的编码方式，主要区别在于对 supplementary characters（增补字符） 的支持能力，以及由此带来的存储范围差异。以下是具体区别：

1. 编码范围不同

UTF-8：是最常见的 UTF-8 编码实现，支持 Unicode 基本多文种平面（BMP） 中的字符，即 U+0000 到 U+FFFF 范围内的字符（包括绝大多数常用字符，如中文、英文、日文等）。每个字符的编码长度为 1~3 字节。
UTF8mb4：是 “UTF-8 most bytes 4” 的缩写，支持 全部 Unicode 字符，包括基本多文种平面（U+0000~U+FFFF）和 增补平面（U+10000~U+10FFFF） 中的字符（如 emoji 表情、某些罕见语言字符、特殊符号等）。每个字符的编码长度为 1~4 字节。

2. 核心差异：是否支持 4 字节字符

UTF-8 最多支持 3 字节编码，无法表示 Unicode 中编码值超过 U+FFFF 的字符（即 4 字节字符）。例如：
- Emoji 表情（如 😊 U+1F60A、👍 U+1F44D）；
- 某些罕见语言文字（如古埃及象形文字、数学符号等）；
- 部分特殊符号（如 😂、🤣 等）。若强行存储这些字符，UTF-8 会出现编码错误或截断。
UTF8mb4 支持 4 字节编码，可完整存储上述所有 Unicode 字符，包括 emoji 等增补字符。

3. 应用场景（以 MySQL 为例）

在数据库（如 MySQL）中，UTF-8 和 UTF8mb4 是常见的字符集设置，区别直接影响存储能力：

早期 MySQL 中的 utf8 实际上是 UTF-8 的子集（仅支持 3 字节），并非完整的 UTF-8 标准；
utf8mb4 才是 MySQL 对完整 UTF-8 标准的实现（支持 4 字节）。

若数据库字段设置为 utf8，存储 emoji 等 4 字节字符时会报错（如 Incorrect string value），必须使用 utf8mb4 才能正常存储。

4. 存储与性能影响

存储空间：UTF8mb4 由于支持 4 字节字符，在存储这类字符时会比 UTF-8 多占用 1 字节；但对于普通 1~3 字节字符（如中文、英文），两者存储长度相同，无额外开销。
性能：两者在编码 / 解码效率上差异极小，实际使用中几乎无感知。但需注意：若数据库索引字段使用 UTF8mb4，且字符长度较长，可能会略微增加索引存储开销（需结合实际业务评估）。

总结

特性	UTF-8（狭义，如 MySQL 的 utf8）	UTF8mb4
支持的 Unicode 范围	U+0000 ~ U+FFFF（3 字节内）	U+0000 ~ U+10FFFF（全范围）
4 字节字符支持	❌ 不支持（如 emoji）	✅ 支持
存储长度	1~3 字节	1~4 字节
适用场景	仅需存储常见字符（无 emoji 等）	需要存储 emoji、罕见字符等

建议：新系统开发中，若可能涉及 emoji、特殊符号或国际化罕见字符，优先使用 UTF8mb4，避免编码兼容性问题；若确认仅需处理基本字符，UTF-8 也可满足需求，但灵活性较低。

5. 什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
索引一般存储在磁盘的文件中，它是占用物理空间的。

6. 索引的优缺点？

优点：
- 大幅减少查询的磁盘 IO 次数，提升查询速度；
- 优化排序、分组操作，降低 CPU 开销。
缺点：
- 占用额外磁盘空间（索引本身是独立的数据结构，需存储）；
- 降低写操作效率（新增 / 修改 / 删除数据时，需同步更新对应的索引，避免索引与数据不一致）。

7. 什么情况下需要建索引？

经常用于查询的字段
经常用于连接 (join) 的字段建立索引，可以加快连接的速度
经常需要排序、分组的字段建立索引，因为索引已经排好序

8. 什么情况下不建索引？

更新频繁的字段（索引会拉低写的效率）
区分度很低的字段（唯一性差）如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。
表数据量很少的情况。比如只有几百条数据，没必要加索引。
where 条件中用不到的字段不适合建立索引

9. MySQL 索引的数据结构有哪些？

MySQL 中索引的数据结构根据引擎和应用场景的不同，主要有以下几种：

B + 树索引这是 MySQL 中最核心、最常用的索引结构，InnoDB 和 MyISAM 引擎的默认索引类型，特点是：
- 多路平衡查找树，非叶子节点仅存索引键（作为目录），叶子节点存储实际数据（MyISAM）或主键指向的行数据（InnoDB 聚簇索引）。
- 叶子节点通过双向链表连接，天然支持范围查询（如 BETWEEN、ORDER BY）和排序。
- 适配磁盘 IO 特性，通过控制树高（通常 3-4 层）减少磁盘访问次数，提升查询效率。
哈希索引基于哈希表实现，仅 Memory 引擎默认支持，InnoDB 可通过 “自适应哈希索引”（Adaptive Hash Index）自动为热点数据创建（不可手动干预）。特点是：
- 等值查询极快（O (1) 时间复杂度），但不支持范围查询、排序、模糊查询（如 LIKE）。
- 哈希冲突会影响性能，且无法利用索引完成排序。
R 树索引用于空间数据类型（如 GEOMETRY）的索引，适合存储地理信息（如地图坐标），支持空间范围查询（如 “查找某区域内的点”）。
- MyISAM 引擎支持 R 树索引，InnoDB 对空间索引的支持基于 B + 树优化实现。
Full-Text 全文索引用于全文检索（如文章内容中的关键词匹配），基于 “倒排索引” 原理（记录关键词到文档的映射）。
- 支持 MATCH AGAINST 语法，适合大文本字段（如 TEXT）的模糊查询，替代低效的 LIKE '%关键词%'。
- InnoDB 和 MyISAM 均支持，但 MyISAM 不支持中文分词（需依赖插件）。

总结：B + 树索引是 MySQL 中最主流的结构，覆盖了绝大多数业务场景；其他结构（哈希、R 树、全文索引）仅用于特定场景（如内存表、空间数据、全文检索）。

10. Hash索引和B+树索引的区别？

Hash 索引和 B + 树索引是 MySQL 中两种不同原理的索引结构，核心区别体现在适用场景、查询能力和性能特性上，具体如下：

1. 数据结构本质

Hash 索引：基于哈希表实现，通过哈希函数将索引键映射为哈希值，直接定位数据存储位置（类似字典的键值对）。
B + 树索引：基于多路平衡查找树，索引键按顺序存储，非叶子节点作为 “目录” 指引查找方向，叶子节点存储数据或指针，且通过双向链表连接。

2. 查询能力

场景	Hash 索引	B + 树索引
等值查询（=）	效率极高（O (1)，无哈希冲突时）	效率较高（O (log n)，树高决定）
范围查询（>、<、BETWEEN）	不支持（哈希值无序，无法比较范围）	天然支持（叶子节点有序且链表连接）
排序（ORDER BY）	不支持（哈希值无序，无法直接排序）	支持（可利用索引顺序避免额外排序）
模糊查询（LIKE）	仅支持前缀精确匹配（如`LIKE 'abc%'`，但需哈希值匹配，实际很少用）	支持前缀匹配（利用索引有序性）
联合索引	仅能通过全部索引键的哈希值定位，无法利用部分前缀键	支持 “最左前缀原则”，可利用部分索引键

3. 性能特性

哈希冲突：Hash 索引存在哈希冲突（不同键映射到同一哈希值），需通过链表或开放地址法解决，冲突严重时性能下降明显；B + 树无冲突问题。
索引维护：数据插入 / 删除时，Hash 索引需重新计算哈希值，若冲突多，维护成本高；B + 树需调整树结构（旋转平衡），但复杂度可控。
存储空间：Hash 索引存储哈希值，空间占用较小；B + 树需存储索引键和指针，空间占用更大，但索引键有序性可优化查询。

4. 适用场景

Hash 索引：仅适合等值查询频繁、无范围查询 / 排序的场景，如内存表（Memory 引擎）或 InnoDB 的自适应哈希索引（针对热点数据自动优化）。
B + 树索引：支持绝大多数业务场景，尤其是需要范围查询、排序、联合索引前缀匹配的场景（如订单表按时间范围查询、用户表按 ID + 姓名联合查询等），是 MySQL 的主流索引类型。

总结

Hash 索引是 “专才”，仅在等值查询场景下性能占优，但功能局限大；B + 树索引是 “通才”，支持多样化查询需求，适配磁盘 IO 特性，是 MySQL 中最常用的索引结构。实际业务中，除非明确只有等值查询且性能敏感，否则优先选择 B + 树索引。

11. B树和B+树的区别？

B 树和 B + 树都是多路平衡查找树，常用于磁盘存储系统（如数据库、文件系统），但两者在结构设计和功能上有显著区别，核心差异如下：

1. 数据存储位置

B 树：非叶子节点和叶子节点都存储实际数据（键值 + 数据）。例如，一个 B 树节点可能同时包含索引键和对应的数据记录。
B + 树：仅叶子节点存储实际数据（键值 + 数据），非叶子节点仅存储索引键（作为 “目录”，不存数据）。非叶子节点的作用是指引查找方向，不包含具体业务数据。

2. 叶子节点的连接方式

B 树：叶子节点之间无关联，是独立的节点。范围查询时，需从根节点重新遍历，效率低。
B + 树：所有叶子节点通过双向链表连接，形成一个有序的链表。范围查询（如BETWEEN、ORDER BY）时，找到起始叶子节点后，可直接通过链表顺序遍历，无需回退到非叶子节点，效率极高。

3. 树的高度与 IO 效率

B 树：由于非叶子节点存储数据，导致相同大小的节点能容纳的索引键数量更少（数据占用空间），树的高度更高。而磁盘 IO 次数与树高成正比，因此 B 树的 IO 成本更高。
B + 树：非叶子节点仅存索引键，相同节点可容纳更多索引键，树高更低（通常 3-4 层），IO 次数更少，更适合磁盘存储（磁盘 IO 是数据库性能瓶颈）。

4. 查询性能的稳定性

B 树：查询可能在非叶子节点命中（找到数据），也可能在叶子节点命中，查询效率不稳定（取决于数据所在层级）。
B + 树：所有查询最终都必须到叶子节点才能获取数据，查询路径长度固定（等于树高），因此查询效率更稳定。

5. 适用场景

B 树：适合随机访问为主的场景（如少量数据的内存数据库），但不适合范围查询频繁的场景。（实际数据库中较少用 B 树，更多用于文件系统索引等场景）
B + 树：是数据库索引的首选（如 MySQL 的 InnoDB、MyISAM），原因是：
- 树高更低，IO 效率高；
- 叶子节点有序且链表连接，完美支持范围查询和排序；
- 查询性能稳定，适合磁盘存储的特性。

总结对比表

特性	B 树	B + 树
数据存储位置	非叶子节点 + 叶子节点	仅叶子节点
叶子节点连接	无关联	双向链表连接
树高	较高（相同数据量下）	较低（相同数据量下）
范围查询效率	低（需反复遍历根节点）	高（通过链表顺序遍历）
查询稳定性	不稳定（命中层级不确定）	稳定（必到叶子节点）
典型应用	少量数据的内存索引、文件系统	数据库索引（MySQL、Oracle 等）

B + 树通过优化数据存储位置和叶子节点连接方式，更适配数据库的查询需求（尤其是范围查询）和磁盘 IO 特性，因此成为主流数据库索引的标准结构。

12. 索引有哪些分类？

MySQL 中的索引可以从不同维度进行分类，常见的分类方式及具体类型如下：

一、按「数据结构」分类（核心分类）

B + 树索引最常用的索引类型，InnoDB 和 MyISAM 引擎的默认索引，支持等值查询、范围查询、排序等，是业务中最常使用的索引。
哈希索引基于哈希表实现，仅 Memory 引擎默认支持，InnoDB 有自适应哈希索引（自动为热点数据创建），仅适合等值查询，不支持范围查询和排序。
R 树索引用于空间数据类型（如 GEOMETRY），用于地理信息等空间查询，MyISAM 支持，InnoDB 基于 B + 树优化实现。
Full-Text 全文索引用于大文本字段的全文检索（如文章内容关键词匹配），基于倒排索引原理，支持 MATCH AGAINST 语法。

二、按「物理存储」分类（InnoDB 核心特性）

聚集索引（Clustered Index）
- 索引键与数据行物理存储在一起，叶子节点直接存储完整数据行。
- InnoDB 中，主键索引就是聚簇索引（若未定义主键，会用唯一索引或隐式自增 ID 作为聚簇索引）。
- 特点：查询效率高（直接定位数据），但插入 / 更新可能导致页分裂，影响性能。
非聚集索引（Secondary Index，辅助索引）
- 索引与数据行分开存储，叶子节点存储的是聚簇索引的键（主键），而非实际数据。
- 例如：普通索引、联合索引、唯一索引等都属于非聚簇索引。
- 查询流程：通过非聚簇索引找到主键，再通过聚簇索引定位数据（回表查询）。

三、按「功能逻辑」分类

主键索引（Primary Key）
- 唯一标识一行数据，不允许 NULL，一张表只能有一个主键索引。
- InnoDB 中主键索引是聚簇索引，性能最优，建议优先用自增 ID 作为主键（减少页分裂）。
唯一索引（Unique Index）
- 索引列的值必须唯一，但允许 NULL（多个 NULL 不冲突）。
- 用于保证数据唯一性（如用户名、手机号），查询性能接近主键索引。
普通索引（Normal Index）
- 最基础的索引，无唯一性约束，仅用于加速查询（如商品表的「分类 ID」索引）。
联合索引（Composite Index）
- 由多个列组合而成的索引（如 (a, b, c)），遵循「最左前缀原则」：查询时需从左到右匹配索引列，否则无法完全利用索引。
- 适合多条件查询（如 where a=? and b=?），可减少回表次数（覆盖索引场景）。
前缀索引（Prefix Index）
- 对字符串列的前 N 个字符创建索引（如 index(name(10))），用于缩短索引长度、节省空间。
- 缺点：无法用于排序和分组，且可能降低索引选择性（区分度）。

四、其他特殊索引

空间索引（Spatial Index）：基于 R 树或 B + 树，用于空间数据类型（如经纬度），支持空间关系查询（如「包含」「相交」）。
隐藏索引（Invisible Index）：MySQL 8.0 新增，索引对优化器不可见（不影响查询），用于测试索引删除的影响（避免直接删除风险）。

总结

索引分类的核心逻辑是：数据结构决定底层实现，物理存储决定数据与索引的关联方式，功能逻辑决定适用场景。实际开发中，最常用的是 B + 树结构的聚簇索引（主键）、非聚簇索引（普通索引、联合索引、唯一索引），需根据查询场景选择合适的类型（如多条件查询用联合索引，唯一性约束用唯一索引）。

分类维度	索引类型	核心特性	适用场景
按数据结构	B + 树索引	多路平衡树，仅叶子存数据，叶子双向链表；支持等值 / 范围查询、排序	绝大多数业务场景（如订单查询、用户筛选）
	哈希索引	基于哈希表，O (1) 等值查询；不支持范围 / 排序 / 模糊查询	内存表（Memory 引擎）、热点数据等值查询
	R 树索引	空间数据专用，支持地理范围查询（如包含、相交）	地图坐标、空间位置数据（如门店定位）
	Full-Text 全文索引	基于倒排索引，支持大文本关键词匹配；替代 `LIKE '%关键词%'`	文章内容、商品描述的全文检索
按物理存储	聚集索引（Clustered）	索引与数据物理绑定，叶子存完整数据；InnoDB 中主键即聚集索引	主键查询（如按 ID 查用户信息）
	非聚集索引（Secondary）	索引与数据分离，叶子存主键；需 “回表” 查数据	普通查询（如按手机号查用户、按分类查商品）
按功能逻辑	主键索引	唯一非空，一张表仅一个；InnoDB 中是聚簇索引	唯一标识数据（如用户 ID、订单 ID）
	唯一索引	索引列值唯一，允许 NULL；避免重复数据	唯一字段（如手机号、邮箱、身份证号）
	普通索引	无约束，仅加速查询；最基础的索引类型	高频查询字段（如商品分类、订单状态）
	联合索引	多列组合，遵循 “最左前缀原则”；可减少回表（覆盖索引）	多条件查询（如 `where 用户名=? and 时间=?`）
	前缀索引	对字符串前 N 个字符建索引；节省空间，降低选择性	长字符串字段（如用户名、地址）的模糊查询
其他特殊	空间索引	对应空间数据类型（如 `GEOMETRY`）；基于 R 树或 B + 树优化	地理信息查询（如 “查找某商圈内的门店”）
	隐藏索引（Invisible）	对优化器不可见，不影响查询；用于测试索引删除风险	索引优化测试（如验证某索引是否冗余）

13. 什么是最左匹配原则？

最左匹配原则（Leftmost Prefix Rule）是 MySQL 中联合索引（多列组合索引） 的核心使用规则，指的是：联合索引在查询时，会优先匹配从最左侧开始的连续索引列，只有当左侧列被有效使用时，右侧列才能参与索引匹配。

具体原理

假设创建了一个联合索引 idx(a, b, c)（顺序为 a → b → c），则该索引的 “有效匹配顺序” 必须从最左侧的 a 开始，具体规则如下：

全匹配：查询条件包含 a + b + c（如 where a=? and b=? and c=?），可完整利用整个联合索引。
前缀匹配
- 只使用 a（如 where a=?），可利用索引的 a 部分。
- 使用 a + b（如 where a=? and b=?），可利用索引的 a + b 部分。
中断匹配：如果跳过左侧列，右侧列无法单独使用索引。
例如：where b=? 或 where b=? and c=?，无法利用该联合索引（因为跳过了最左侧的 a）。
范围条件后的列失效：如果左侧列使用了范围查询（如 >、<、between），则其右侧的列无法利用索引。
例如：where a=? and b> ? and c=?，此时 a 和 b 可利用索引，但 c 无法利用（因 b 是范围查询，中断了后续匹配）。

示例说明

假设有表 user，联合索引 idx(name, age, city)，分析以下查询是否能利用索引：

查询语句	是否利用索引	利用的索引部分	原因分析
`where name='张三'`	是	`name`	匹配最左前缀 `name`
`where name='张三' and age=20`	是	`name + age`	连续匹配左侧两列
`where name='张三' and city='北京'`	是	仅 `name`	跳过 `age`，`city` 无法利用
`where age=20 and city='北京'`	否	无	跳过最左 `name`，全失效
`where name='张三' and age>20 and city='北京'`	是	`name + age`	`age` 是范围查询，`city` 失效

为什么有这个原则？

联合索引的底层 B + 树结构是按索引列顺序逐层排序的：

先按 a 排序，a 相同的记录再按 b 排序，b 相同的再按 c 排序。
这种排序特性决定了索引只能从左到右依次匹配，跳过左侧列后，右侧列的排序逻辑就失去了基础（类似查字典必须先按首字母找，再按第二个字母）。

14. 什么是聚集索引和非聚集索引？

聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index）是数据库中两种核心的索引类型，核心区别在于索引与数据的物理存储关系，以下是具体解析：

一、聚集索引（Clustered Index）

核心特点：索引结构与数据行的物理存储直接绑定，索引的叶子节点就是数据本身。

存储结构
- 聚集索引的 B + 树中，非叶子节点存储索引键（如主键），叶子节点直接存储完整的数据行。
- 数据在磁盘上的物理存储顺序，与聚集索引键的逻辑顺序一致（如主键自增，则数据按主键从小到大排列）。
关键特性
- 一张表只能有一个聚集索引（数据物理顺序唯一）。
- InnoDB 中，聚集索引默认是主键索引（若未定义主键，会用唯一索引或隐式自增 ID 替代）。
- 查询时，找到索引键即可直接从叶子节点获取数据，无需回表（效率极高）。
示例假设表 user 以 id 为主键（聚集索引），则其索引结构如下：
- 非叶子节点：存储 id 的范围（如 1-100、101-200）。
- 叶子节点：直接存储 id=1 对应的 name、age 等完整字段数据，且按 id 顺序排列。

二、非聚集索引（Non-Clustered Index）

核心特点：索引与数据物理存储分离，索引叶子节点存储的是 “指向数据的指针”（InnoDB 中是聚集索引的键，如主键）。

存储结构
- 非聚集索引的 B + 树中，非叶子节点和叶子节点都存储索引键，但叶子节点不存储完整数据，而是存储 “聚集索引的键”（如主键值）。
- 索引的逻辑顺序与数据的物理存储顺序无关。
关键特性
- 一张表可以有多个非聚集索引（如普通索引、联合索引、唯一索引等）。
- 查询时，需先通过非聚集索引找到主键，再通过聚集索引定位数据（称为 “回表查询”）。
- 若查询字段刚好是索引包含的字段（覆盖索引），则无需回表，直接返回结果。
示例表 user 上有非聚集索引 idx_age(age)，则：
- 索引的叶子节点存储 age 的值和对应的 id（主键）。
- 执行 where age=20 时，先通过 idx_age 找到所有 age=20 的 id，再通过聚集索引（id）查询完整数据。

三、核心区别对比

维度	聚集索引	非聚集索引
数据与索引关系	索引叶子节点 = 数据行	索引与数据分离，叶子存主键
数量限制	一张表仅 1 个	一张表可多个
查询流程	直接获取数据（无需回表）	需先查索引得主键，再回表查数据
物理存储顺序	与索引键顺序一致	与索引键顺序无关
典型应用	主键索引（InnoDB 默认）	普通索引、联合索引、唯一索引

四、使用场景建议

聚集索引：适合高频的主键查询、范围查询（如 where id between 100 and 200），依赖其 “数据有序 + 无需回表” 的特性。
非聚集索引：适合基于非主键字段的查询（如 where age=20），但需注意避免频繁回表（可通过覆盖索引优化）。

理解两者的区别，能帮助设计更合理的索引结构（如用自增 ID 作为聚集索引减少页分裂，用联合索引实现覆盖查询），提升数据库性能。

image-20250823193058691.CJib1r2w

15. 什么是覆盖索引？

覆盖索引（Covering Index）是一种能直接从索引中获取查询所需的全部数据，无需通过索引回表查询数据行的索引类型，核心价值是减少磁盘 IO，大幅提升查询效率。

一、覆盖索引的核心原理

普通非聚簇索引（如普通索引、联合索引）的叶子节点，仅存储 “索引键 + 聚簇索引键（主键）”。当查询需要的字段超出索引包含的范围时，必须通过主键回表（即再次查询聚簇索引）才能获取完整数据，这个过程会增加一次磁盘 IO。

而覆盖索引的关键在于：索引包含了查询语句中所有需要的字段（包括 SELECT 后的字段、WHERE 条件中的字段）。此时查询只需扫描索引，无需回表，直接从索引中提取所有所需数据。

二、覆盖索引的典型场景

覆盖索引通常基于联合索引实现（单一字段的普通索引，仅能覆盖 “查询条件 + 该字段本身” 的场景，适用范围较窄），以下是具体示例：

假设存在表 order，结构如下：

字段名	类型	索引情况
id	int	主键（聚簇索引）
order_no	varchar(50)	普通索引（非聚簇索引）
user_id	int	无索引
create_time	datetime	无索引
status	tinyint	无索引

场景 1：普通索引无法覆盖，需回表

若执行查询：

sql

SELECT id, order_no, status FROM `order` WHERE order_no = '20240501001';

此时使用的索引是 order_no 普通索引，其叶子节点仅存储 order_no 和主键 id。
查询需要的 status 字段不在索引中，必须通过 id 回表查询聚簇索引，才能获取 status，存在额外 IO。

场景 2：联合索引实现覆盖，无需回表

若创建联合索引 idx_order_no_status(order_no, status)，再执行相同查询：

sql

SELECT id, order_no, status FROM `order` WHERE order_no = '20240501001';

联合索引 idx_order_no_status 的叶子节点，存储了 order_no、status 和主键 id。
查询需要的 id（主键，默认包含在非聚簇索引中）、order_no、status 均在索引内，无需回表，直接从索引提取数据。

16. 索引的设计原则？

优先为高频查询字段建索引
区分度高的字段优先作为索引列（区分度 = 不同值数量 / 总记录数）
联合索引遵循 “最左前缀 + 高频字段靠左”
控制索引数量，避免过度索引
利用覆盖索引减少回表

17. 索引什么时候会失效？

MySQL 索引失效是查询性能下降的常见原因，本质是由于查询条件或索引使用方式不符合 B + 树索引的匹配规则，导致优化器放弃使用索引，转而进行全表扫描。以下是常见的索引失效场景及原因分析：

一、索引列参与计算或函数操作

原因：索引存储的是原始值，若对索引列进行计算、函数处理（如 SUBSTR()、DATE_FORMAT() 等），优化器无法直接匹配索引值，只能全表扫描后再处理。

sql

-- 索引：idx_create_time(create_time)
SELECT * FROM order WHERE UNIX_TIMESTAMP(create_time) > 1620000000; -- 失效（函数操作）
SELECT * FROM user WHERE age + 1 = 20; -- 失效（计算操作）

优化：避免对索引列直接操作，改为对常量操作（如 age = 19）。

二、使用 `NOT IN`、`!=`、`<>` 或 `IS NOT NULL`

原因：这些操作会导致索引无法有效筛选数据（返回结果集可能过大），优化器可能选择全表扫描。示例：

sql

-- 索引：idx_status(status)
SELECT * FROM order WHERE status != 1; -- 失效（!=）
SELECT * FROM user WHERE phone NOT IN ('13800138000', '13900139000'); -- 失效（NOT IN）

注意：若结果集占比极小（如 <10%），索引可能仍有效，具体取决于数据分布。

三、`LIKE` 以通配符 `%` 开头（左模糊查询）

原因：LIKE '%xxx' 或 LIKE '%xxx%' 是 “前缀模糊匹配”，索引的有序性无法利用（类似查字典不知道首字母），导致索引失效。

sql

-- 索引：idx_name(name)
SELECT * FROM user WHERE name LIKE '%三'; -- 失效（%开头）
SELECT * FROM user WHERE name LIKE '%张三%'; -- 失效（前后%）

例外：LIKE 'xxx%'（后缀模糊匹配）可利用索引的最左前缀，索引有效。优化：改用全文索引（Full-Text）处理复杂模糊查询。

四、联合索引不满足最左前缀原则

原因：联合索引的匹配必须从最左侧列开始，跳过左侧列或中断顺序会导致后续列索引失效。

示例（联合索引 idx(a, b, c)）：

sql

SELECT * FROM t WHERE b = 1; -- 失效（跳过最左a）
SELECT * FROM t WHERE a = 1 AND c = 3; -- 仅a有效，c失效（跳过b）
SELECT * FROM t WHERE a > 1 AND b = 2; -- a有效，b失效（a是范围查询）

注意：范围查询（>、<、BETWEEN）会导致其右侧的联合索引列失效。

五、索引列与常量类型不匹配（隐式转换、类似与索引列参与函数计算）

原因：若索引列类型与查询条件的常量类型不一致，MySQL 会进行隐式类型转换（如字符串转数字），相当于对索引列做了函数操作，导致索引失效。

sql

-- 索引：idx_phone(phone)，phone是varchar类型
SELECT * FROM user WHERE phone = 13800138000; -- 失效（隐式转换：字符串→数字）

优化：保证查询条件的类型与索引列一致（如 phone = '13800138000'）。

六、`OR` 连接的条件中存在非索引列

原因：OR 两边的条件需都有索引才能生效，若一侧无索引，优化器会认为全表扫描更高效。

sql

-- 索引：idx_name(name)，age无索引
SELECT * FROM user WHERE name = '张三' OR age = 20; -- 失效（age无索引）

优化：将 OR 改为 UNION（需两侧条件都有索引），或为 age 也创建索引。

七、优化器判断全表扫描更快

原因：若查询结果集占表数据量的比例过大（如 >30%），优化器会认为全表扫描比遍历索引更高效（索引需要额外 IO），主动放弃索引。

sql

-- 索引：idx_status(status)，但表中90%的记录status=1
SELECT * FROM order WHERE status = 1; -- 可能失效（结果集过大）

注意：比例阈值由 MySQL 内部统计信息决定，可通过 ANALYZE TABLE 更新统计信息。

如何判断索引是否失效？

使用 EXPLAIN 分析查询计划，若 type 列显示 ALL（全表扫描），且 key 列显示 NULL，说明索引失效。

sql

EXPLAIN SELECT * FROM user WHERE name LIKE '%三';
-- type=ALL，key=NULL → 索引失效

总结

索引失效的核心原因是查询条件破坏了 B + 树的有序性，或优化器认为全表扫描更高效。实际开发中，需通过 EXPLAIN 验证索引使用情况，避免上述场景，同时结合数据分布调整索引设计（如区分度低的字段不建索引）。

18. 什么是前缀索引？

前缀索引（Prefix Index）是针对字符串类型字段的一种索引优化方式，指仅对字符串的前 N 个字符创建索引，而非整个字符串，核心目的是减少索引占用空间、提升索引维护和查询效率。

一、前缀索引的核心作用

字符串字段（如 VARCHAR(255)、TEXT）若直接创建完整索引，会存在两个问题：

索引体积过大：长字符串会导致索引节点（B + 树非叶子节点）存储的索引键数量减少，树高增加，进而增加磁盘 IO 次数。
维护成本高：插入 / 更新时，需处理完整字符串的索引排序和存储，耗时更长。

前缀索引通过截取字符串的前 N 个字符建索引，能有效解决上述问题：

缩短索引键长度，让索引节点容纳更多索引键，降低树高，减少 IO。
减少索引占用的磁盘空间，同时降低写入时的索引维护成本。

19. 索引下推是什么？

索引下推（Index Condition Pushdown，简称 ICP）是 MySQL 5.6 引入的一种索引查询优化技术，核心作用是在索引扫描过程中提前过滤不符合条件的数据，减少回表次数，提升查询效率。

一、索引下推的工作原理

在没有 ICP 的情况下，非聚簇索引（如普通索引、联合索引）的查询流程是：

遍历索引，找到所有满足最左前缀条件的索引项（如联合索引 (a, b) 中，先匹配 a=1）。
无论其他条件（如 b=2）是否满足，都通过主键回表查询完整数据行。
在服务器层（Server Layer）对回表后的数据行进行过滤（如判断 b=2 是否成立）。

ICP 优化后，流程变为：

遍历索引，找到满足最左前缀条件的索引项。
直接在索引层面（存储引擎层，Storage Engine Layer），用其他条件（如 b=2）过滤索引中包含的字段。
仅对过滤后符合条件的索引项，才通过主键回表查询完整数据行。

简单说：ICP 让过滤逻辑 “下沉” 到存储引擎层的索引扫描阶段，减少了需要回表的数据量。

20. 常见的存储引擎有哪些？

MySQL 中常见的存储引擎主要有 InnoDB、MyISAM、Memory 等，不同引擎在事务支持、锁机制、存储结构等核心特性上差异显著，适用场景也不同，以下是主流存储引擎的详细对比：

一、主流存储引擎核心特性对比

特性维度	InnoDB（MySQL 5.5+ 默认）	MyISAM（早期默认）	Memory（内存引擎）
事务支持	支持（ACID 特性）	不支持	不支持
锁机制	行级锁（支持高并发）	表级锁（并发差）	表级锁
外键约束	支持	不支持	不支持
存储结构	聚簇索引（数据 + 索引绑定）	非聚簇索引（分离）	内存中存储（临时）
崩溃恢复	支持（redo/undo 日志）	不支持（易丢失数据）	重启后数据丢失
适用场景	业务表（订单、用户、支付）	只读 / 低频写（日志、报表）	临时表、缓存数据

21. MySQL有哪些锁？

MySQL 的锁可以按粒度和特性两大维度梳理，核心是控制并发、保证数据一致性，以下是精简总结：

一、按锁粒度（最核心）

1. 全局锁（粒度最粗）

锁定范围：整个 MySQL 实例（所有数据库、所有表）。
类型：全局只读锁（排他性，阻塞所有写入）。
加锁方式：FLUSH TABLES WITH READ LOCK;（释放：UNLOCK TABLES;）。
特点：加锁期间仅允许 SELECT，阻塞所有写入、表结构修改和事务提交；影响范围极大。
场景：全库逻辑备份（非 InnoDB 为主的场景），保证备份数据时间点一致性。

2. 行级锁（InnoDB 特有，粒度最细）

类型
- 共享锁（S 锁）：读锁，多个事务可同时持有，互不阻塞。
- 排他锁（X 锁）：写锁，仅一个事务持有，阻塞所有其他锁（包括 S 锁）。
特点：并发性能高（只锁单行），但开销大，可能死锁。
场景：高并发读写（如订单表、用户表的行更新）。

3. 表级锁（粒度较粗）

类型
- 读锁 / 写锁：MyISAM 主要依赖，写锁阻塞所有读写，并发差。
- 意向锁（IS/IX）：InnoDB 特有，行锁的 “声明”，避免表锁与行锁冲突。（仅为声明要加行锁）
- 自增锁：保证自增列唯一性，MySQL 8.0 后优化为轻量锁。
特点：开销小，无死锁，但并发低（锁全表）。
场景：MyISAM 表、全表操作（如 ALTER TABLE）。

4. 页级锁（极少用）

粒度介于行和表之间，仅少数引擎（如 BDB）支持，主流场景不用。

按粒度从粗到细排序：全局锁 → 表级锁 → 页级锁 → 行级锁，锁粒度越粗，并发性能越低但开销越小；粒度越细，并发性能越高但开销越大。全局锁因影响范围极大，实际中需谨慎使用，优先用 InnoDB 的事务快照替代。

二、InnoDB 特有锁（解决事务隔离）

间隙锁：锁定索引间隙（如 id=10 和 id=20 之间），防止插入新数据（解决幻读）。
临键锁：行锁 + 间隙锁的组合，默认锁机制，锁定记录及相邻间隙。
记录锁：仅锁定单条索引记录（如主键等值查询时）。

三、按锁策略（业务视角）

悲观锁：假设冲突多，直接加锁（如 SELECT ... FOR UPDATE），适合写多读少场景（如库存扣减）。
乐观锁：假设冲突少，用版本号控制（如 WHERE version=?），适合读多写少场景（如商品信息更新）。

四、锁的兼容性（核心规则）

不同锁之间的兼容性决定了并发操作是否允许，以下是关键兼容关系（√ 表示兼容，× 表示冲突）：

当前锁 \ 请求锁	共享锁（S）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	√	×	√	×
排他锁（X）	×	×	×	×
意向共享锁（IS）	√	×	√	√
意向排他锁（IX）	×	×	√	√

核心结论

InnoDB 是主流：依赖行级锁（S/X）、意向锁、间隙锁 / 临键锁，支持高并发和事务。
锁的选择原则：粒度细（行锁）优先保证并发，粒度粗（表锁）适合简单场景；根据冲突频率选悲观 / 乐观锁。

22. MVCC 是什么？

MVCC（多版本并发控制）是 InnoDB 实现高并发的核心机制，核心逻辑可简化为：

核心目标

让 读不阻塞写，写不阻塞读，同时保证事务隔离性（避免脏读、不可重复读）。

实现三要素

数据多版本：每行数据保存多个历史版本，通过隐藏字段记录：
- 最新修改的事务 ID（DB_TRX_ID）
- 指向旧版本的指针（DB_ROLL_PTR，形成版本链）。
undo 日志：存储数据的历史版本，供读操作访问（写操作时，旧数据被移到这里）。
读视图（Read View）：事务读数据时生成的 “快照”，用来判断哪个版本的数据可见（基于事务 ID 对比）。

工作流程

写操作：修改数据时，生成新的版本（记录当前事务 ID），旧版本存入 undo 日志，通过指针串联成版本链。
读操作：根据读视图的规则，在版本链中找到 “对当前事务可见” 的版本（无需加锁，直接读历史版本）。

隔离级别差异

读已提交（RC）：每次查询都生成新的读视图，能看到其他事务刚提交的内容。
可重复读（RR，默认）：事务开始时生成一次读视图，后续查询复用，保证多次读结果一致。

简单说：MVCC 通过 “保存历史版本 + 判断可见性”，让读写并行不冲突，兼顾并发和数据一致性。

23. 快照读和当前读？

快照读和当前读是 InnoDB 中两种核心的读取方式，核心区别在于是否读取数据的最新版本，以及是否加锁，直接影响事务隔离性和并发性能：

对比维度	快照读（Snapshot Read）	当前读（Current Read）
读取数据版本	历史版本（基于 MVCC）	最新版本（当前磁盘数据）
是否加锁	不加锁（非阻塞读）	加锁（X 锁 / S 锁，可能阻塞）
触发语句	普通 `SELECT`（无锁语法）	写操作（UPDATE/DELETE/INSERT）、显式加锁查询
核心用途	普通查询（如列表查询、详情查询）	数据修改、需要强一致性的查询（如库存扣减）
并发性能	高（读写不冲突）	低（加锁阻塞）

24. 共享锁和排他锁

共享锁（S 锁，Shared Lock）和排他锁（X 锁，Exclusive Lock）是 InnoDB 行级锁的核心类型，核心区别是是否允许其他事务同时访问同一数据，直接决定了并发读写的规则：

一、共享锁（S 锁）：“读锁”，支持并发读

核心作用：保证事务对数据的 “只读权限”，不允许修改。
加锁方式：通过 SELECT ... LOCK IN SHARE MODE; 显式加锁（事务内有效）。
关键特性
1. 共享性：多个事务可同时对同一行加 S 锁（比如事务 A 和事务 B 都读同一行，互不阻塞）。
2. 阻塞写：加了 S 锁的行，其他事务想加 X 锁（修改 / 删除）会被阻塞，必须等所有 S 锁释放。
适用场景：需要 “读数据并确保数据不被修改” 的场景（如统计报表生成，避免统计过程中数据被改导致结果不准）。

二、排他锁（X 锁）：“写锁”，独占数据

核心作用：保证事务对数据的 “独占修改权限”，不允许其他事务读写。
加锁方式
1. 隐式：UPDATE/DELETE/INSERT 操作会自动为涉及的行加 X 锁（事务提交 / 回滚后释放）。
2. 显式：通过 SELECT ... FOR UPDATE; 主动为查询行加 X 锁（事务内有效）。
关键特性
1. 独占性：同一行只能有一个事务持有 X 锁（其他事务加 S 锁或 X 锁都会被阻塞）。
2. 阻塞所有：加了 X 锁的行，其他事务的读（加 S 锁）和写（加 X 锁）都会被阻塞，直到 X 锁释放。
适用场景：需要 “修改数据并确保数据不被其他事务干扰” 的场景（如库存扣减、订单状态更新，避免并发修改导致超卖或状态混乱）。

三、核心区别与兼容性

对比维度	共享锁（S 锁）	排他锁（X 锁）
核心权限	只读（不可写）	读写（独占）
并发支持	多事务可同时加锁（共享读）	仅一个事务可加锁（独占写）
兼容性	与其他 S 锁兼容，与 X 锁冲突	与所有锁（S/X）都冲突
加锁方式	仅显式（`LOCK IN SHARE MODE`）	隐式（写操作）+ 显式（`FOR UPDATE`）
典型场景	报表统计、数据校验（只读）	库存扣减、状态更新（读写）

一句话总结

共享锁是 “大家一起读，谁也不能改”；排他锁是 “我要改，谁也不能读也不能改”，两者配合实现了 InnoDB 行级锁的并发控制。

25. redo log、undo log、bin log

这三类日志是保证 MySQL 数据一致性和高可用的核心。简单说，redo log 保证事务持久性，undo log 保证事务原子性和隔离性，bin log 保证数据备份和主从同步。

1. Redo Log（重做日志）：防止崩溃丢失数据

Redo Log 是 InnoDB 存储引擎特有的日志，作用是 “重做” 已完成的事务操作，确保即使数据库崩溃，重启后也能恢复数据。

记录内容：不记录具体数据，只记录 “哪个数据页的哪个位置修改成了什么值”。
核心机制：采用 “WAL（Write-Ahead Logging）” 原则，事务提交前，先把修改记录写入 redo log，再写磁盘数据（刷脏页）。
解决问题：避免事务提交后，数据还没刷到磁盘就崩溃，导致数据丢失。

2. Undo Log（回滚日志）：支持事务回滚与 MVCC

Undo Log 同样是 InnoDB 特有，作用是 “撤销” 事务的修改，同时为 MVCC（多版本并发控制）提供历史数据版本。

记录内容：记录事务修改前的数据状态，比如 “把 ID=1 的 name 从‘A’改成‘B’”，undo log 就记 “把 ID=1 的 name 从‘B’改回‘A’”。
核心作用：
1. 事务回滚：当执行ROLLBACK时，MySQL 通过 undo log 恢复到事务开始前的状态。
2. 支持 MVCC：查询时若数据被其他事务修改，可通过 undo log 读取历史版本，避免锁等待。
特点：undo log 是 “逻辑日志”，且会被定期清理（当没有事务需要访问历史版本时）。

3. Binlog（二进制日志）：用于备份与主从同步

Binlog 是 MySQL 服务器层的日志（所有存储引擎都支持），作用是记录所有数据修改操作，用于数据恢复和主从复制。

记录内容：记录完整的 SQL 执行逻辑（Statement 格式）或数据行的修改（Row 格式），只记录已提交事务的操作。
核心作用：
1. 数据备份：通过 binlog 可恢复到指定时间点的数据（结合全量备份）。
2. 主从同步：主库将 binlog 发送给从库，从库执行 binlog 中的操作，实现主从数据一致。
特点：binlog 是 “追加写入”，不会覆盖，需手动配置过期清理策略。

三类日志核心区别对比

对比维度	Redo Log	Undo Log	Binlog
所属层级	InnoDB 存储引擎层	InnoDB 存储引擎层	MySQL 服务器层
核心目标	保证事务持久性（Durability）	保证原子性 + 支持 MVCC	备份 + 主从同步
记录内容	数据页修改（物理日志）	数据修改前状态（逻辑日志）	SQL 逻辑 / 行修改（逻辑日志）
生命周期	循环写入（满了覆盖）	事务结束后可清理	追加写入（过期后删除）

26. bin log和redo log有什么区别？

bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。
bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。
bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

总结：核心区别一句话

redo log 是 InnoDB 的 “物理日志”，负责崩溃后的数据恢复，保证事务持久性；
binlog 是 MySQL 服务器的 “逻辑日志”，负责记录修改历史，用于备份和主从同步。

27. 讲一下MySQL架构？

MySQL 架构可简化为 四层结构，核心特点是 “分层解耦，灵活扩展”：

1. 连接层：管 “接入效率”，优化 “连接成本”

核心功能：客户端连接（如 JDBC 接口）、连接池管理（复用连接）、身份 / 权限校验。
优化点
- 避免频繁创建连接（用连接池控制连接数，防止 TCP 握手 / 挥手开销）；
- 合理设置连接超时（避免空闲连接占用资源）；
- 权限最小化（减少权限校验开销，提高安全性）。

2. 服务层：管 “SQL 逻辑”，优化 “执行效率”

核心功能
- 解析 SQL（检查语法，生成语法树）；
- 优化 SQL（选索引、定连接顺序，生成最优执行计划）；
- 执行 SQL（调用引擎接口干活）；
- 含 binlog（记录修改，用于备份 / 同步）。
优化点
- 让优化器选对索引（避免索引失效，如不做函数操作索引列）；
- 减少全表扫描（通过索引快速定位数据）；
- 优化连接顺序（小表驱动大表，减少中间结果集）；
- 禁用无用查询缓存（8.0 已移除，老版本避免缓存频繁失效）。

3. 引擎层：管 “数据存储”，优化 “读写性能”

核心功能：
- 接收服务层执行器的指令，完成数据的增删改查；
- 管理物理数据（页、行、索引的存储）、锁（行锁 / 表锁）、事务（redo log/undo log）等。

4. 存储层：管 “物理文件”，优化 “磁盘效率”

核心功能：将引擎处理的数据以文件形式存在磁盘（如 InnoDB 的.ibd数据文件、binlog 日志文件）。
优化点
- 用高效文件系统（如 ext4、XFS）；
- 分散磁盘 IO（将数据和日志放不同磁盘，避免竞争）；
- 定期清理过期日志（如 binlog，防止占满磁盘）。

一句话串起来：连接层优化 “少建连接”，服务层优化 “SQL 跑得巧”，引擎层优化 “存得快、锁得少”，存储层优化 “磁盘不卡壳”。这样每层干啥、优化啥就好记了～

28. 分库分表？

分库分表是应对 MySQL 等关系型数据库 数据量过大（如千万 / 亿级）、并发过高 时的核心解决方案，通过 “拆分” 突破单库单表的性能瓶颈。

核心问题：为什么需要分库分表？

单库单表会遇到三个瓶颈：

存储瓶颈：单表数据量过大（如 1 亿行），索引文件庞大，查询时磁盘 IO 耗时剧增。
并发瓶颈：单库连接数有限（MySQL 默认最大连接数约 151），高并发下连接排队，响应变慢。
性能瓶颈：大表的 CRUD 操作（尤其是索引失效时）耗时极长，甚至锁表影响其他操作。

分库分表的两种核心方式

1. 分表（Table Sharding）：把一个大表拆成多个小表

目的：减少单表数据量，提高查询效率。
两种拆分方式
- 水平分表（按行拆分）：将表中不同行的数据拆分到多个表中，表结构完全相同。例：用户表（user）按用户 ID 取模拆分，user_0（ID%2=0）、user_1（ID%2=1）。适用场景：数据行多，且查询常带拆分键（如按 ID 查询）。
- 垂直分表（按列拆分）：将表中不同列的数据拆分到多个表中，表结构不同，共同组成原表的完整信息。例：用户表拆成user_base（存 ID、姓名等常用列）和user_extra（存简介、头像等大字段 / 不常用列）。适用场景：表字段多，或有大字段（如 TEXT），拆分后减少单表 IO。

2. 分库（Database Sharding）：把一个大库拆成多个小库

目的：分散单库的并发压力，突破连接数和存储上限。
两种拆分方式
- 水平分库：将多个表按规则拆分到不同库中，每个库的表结构相同。例：用户表按地区拆分，db_beijing.user、db_shanghai.user 分别存北京、上海用户。
- 垂直分库：按业务模块拆分，不同模块的表放在不同库中。例：电商系统拆成 user_db（用户相关表）、order_db（订单相关表）、goods_db（商品相关表）。适用场景：业务模块清晰，且模块间耦合低，可减少单库压力。

29. 什么是分区表？

简单说，分区表就像一个 “带分类格的抽屉”：整张大表是抽屉，每个分区是抽屉里的小格子，数据按规则放进不同格子，但你打开抽屉时，看到的还是完整的 “一张表”。

1. 核心目的

解决单表数据量过大的问题：

减少单分区数据量，提升查询效率（比如查 2024 年的数据，只扫描 2024 年的分区，不用全表扫描）；
方便批量管理数据（比如直接删除 2023 年的分区，比DELETE删数据快得多）。

2. 常见分区类型（最常用的 2 种）

分区类型	拆分规则	适用场景
范围分区	按数值 / 时间范围拆分（如按年、按月）	时间序列数据（订单、日志）
哈希分区	按字段哈希值均匀拆分	希望数据均匀分布的场景

3. 简单示例（范围分区）

sql

-- 创建按年月分区的订单表（逻辑上是一张表，物理拆成多个分区）
CREATE TABLE orders (
    id INT,
    order_time DATETIME,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (TO_DAYS(order_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01'))
);

-- 插入数据：会自动落入对应分区
INSERT INTO orders VALUES (1, '2024-01-15', 100); -- 落入p202401
INSERT INTO orders VALUES (2, '2024-02-20', 200); -- 落入p202402

-- 查询2024年1月数据：只扫描p202401分区，效率高
SELECT * FROM orders WHERE order_time BETWEEN '2024-01-01' AND '2024-01-31';

-- 直接删除2024年1月数据：秒级完成（只需删除p202401分区）
ALTER TABLE orders DROP PARTITION p202401;

4. 关键注意点

分区表对外仍是一张表：你执行SELECT * FROM orders能看到所有数据，无需手动指定分区；
分区键必须是主键 / 唯一索引的一部分（InnoDB）：比如上面的order_time若不是主键，分区会受限；
分区≠分表：分表是拆成多张独立的表（如 orders_202401、orders_202402），用户需手动指定表名；分区表是 “逻辑单表、物理多分区”，对用户透明。

总结

分区表是 “逻辑单表、物理多分区”，用户操作无感知；
核心价值是提升大数据量表的查询 / 删除效率，按规则拆分数据；
最常用范围分区（按时间），适合订单、日志等时间类数据。

30. 查询语句执行流程？

连接校验：客户端与 MySQL 建立连接，验证账号、密码合法性，同时校验当前用户对目标库 / 表的操作权限（如 SELECT 权限）。
- 关键：权限校验失败直接拒绝请求，流程终止。
解析 SQL（语法校验）：MySQL 解析器检查 SQL 语法是否合法（如关键字拼写、括号匹配、字段 / 表名格式等），并将合法 SQL 拆解为抽象语法树（识别表名、字段、查询条件等）。
- 关键：语法错误会直接抛出异常，流程终止，不会进入后续阶段。
查询缓存：（MySQL 5.7 及之前版本）
- 命中规则：以 “完整 SQL 语句（字节级一致，含空格、大小写）” 为键，匹配缓存中已存储的查询结果，命中则直接返回结果；
- 未命中 / 失效：表数据（INSERT/UPDATE/DELETE）修改会导致缓存失效，未命中则进入下一步。
生成执行计划：优化器基于语法树选择最优执行策略（如用哪个索引、表连接顺序、是否全表扫描等）。
执行查询：执行器按计划调用存储引擎（如 InnoDB）读数据，优先从引擎缓存（Buffer Pool）取，无缓存则读磁盘。
返回结果：处理数据（排序、分页等）后，将结果返回给客户端。

31. 更新语句执行过程？

连接校验：客户端建立 MySQL 连接，验证账号 / 密码合法性，同时校验当前用户对目标表的 UPDATE 权限（无权限直接终止流程）。
解析优化：解析器校验 SQL 语法（如字段 / 表名、语法格式），优化器生成最优执行计划（比如通过主键 / 索引快速定位待更新行，避免全表扫描）。
定位加锁：执行器调用 InnoDB 引擎，按执行计划定位目标行；InnoDB 对目标行加排他行锁（X 锁），阻塞其他事务的修改 / 加锁操作，保证并发安全。
- 关键：行锁在 “定位到行” 时加，事务提交 / 回滚后才释放。
日志预写（双日志打底）：
- 先写 undo log：记录数据旧版本（用于事务回滚、MVCC 多版本查询，保障原子性）；
- 再写 redo log（prepare 阶段）：记录数据修改的物理操作（如 “页 123 偏移 456 的值从 100 改 200”），写入 redo log buffer 并刷到磁盘（防崩溃丢数据，保障持久性）。
修改内存数据：直接修改 InnoDB 缓冲池（Buffer Pool）中的目标数据，修改后的数据称为 “脏页”（不立即刷磁盘，提升性能）。
事务提交（最终确认）：
- 写 binlog：将更新操作记录到二进制日志（用于主从同步、数据恢复），并刷到磁盘；
- 标记 redo log（commit 阶段）：更新 redo log 状态为 “提交完成”，事务正式提交；
- 释放行锁：事务提交后释放行锁，允许其他事务操作目标行。
- 后续：后台线程异步将缓冲池中的脏页刷到磁盘（ibd 数据文件），完成物理持久化。

关键核心细节

3 类日志的核心作用：
- undo log：负责回滚、MVCC（保障原子性）；
- redo log：崩溃恢复（保障持久性）；
- binlog：主从同步、数据恢复（保障集群一致性）。
两阶段提交：redo log 分 “prepare” 和 “commit” 两步，与 binlog 配合，避免 “redo log 提交但 binlog 未写” 导致的数据不一致。
性能关键：修改内存而非直接刷磁盘，脏页异步刷盘减少磁盘 IO；行锁而非表锁，降低并发阻塞。

32. exist和in的区别？

以 “查询有订单的用户” 为例（用户表user，订单表order，关联字段user.id = order.user_id）：

1. IN 的执行逻辑

sql

-- IN：先查订单表的所有user_id（子查询），再查用户表匹配这些id
SELECT * FROM user 
WHERE id IN (SELECT user_id FROM `order`);

执行步骤：
1. 先执行子查询SELECT user_id FROM order，得到一个 “用户 ID 集合”（如{1,2,3}）；
2. 再执行外层查询：SELECT * FROM user WHERE id IN {1,2,3}；
特点：子查询只执行 1 次，结果集加载到内存后匹配，小结果集快，大结果集（如 10 万行）会占内存、匹配慢。

2. EXISTS 的执行逻辑

sql

-- EXISTS：逐行查用户表，代入子查询判断该用户是否有订单
SELECT * FROM user u
WHERE EXISTS (SELECT 1 FROM `order` o WHERE o.user_id = u.id);

执行步骤：

先取外层user表的一行数据（如 id=1）；
将u.id=1代入子查询，判断order表是否有user_id=1的行；
有则保留该行，无则舍弃，继续处理下一行；

特点：子查询执行次数 = 外层行数，但只需判断 “是否存在”，无需返回所有结果，大子查询表（如订单表有 100 万行）时更高效。

3. 关键补充

性能选择原则
- 当子查询表小、外层表大：用IN（如 “查属于某几个部门的员工”，部门表只有 10 行）；
- 当子查询表大、外层表小：用EXISTS（如 “查有订单的用户”，订单表 100 万行，用户表 1 万行）；
- MySQL 新版本（8.0+）会优化部分IN/EXISTS的执行计划，但若结果集差异大，仍需手动选择。

空值处理差异

sql

-- IN遇到NULL：结果为空（因为NULL无法匹配）
SELECT 1 WHERE 1 IN (NULL); -- 无结果
-- EXISTS遇到NULL：只要子查询有行就返回TRUE
SELECT 1 WHERE EXISTS (SELECT NULL); -- 返回1

简化写法
- EXISTS的子查询无需返回实际字段，通常写SELECT 1（比SELECT *更高效，因为无需读取字段数据）。

4. 总结

核心逻辑：IN是 “集合匹配”，EXISTS是 “逐行判断存在性”；
性能选择：小结果集用IN，大结果集（子查询表）用EXISTS；
空值处理：对 NULL 敏感选EXISTS，需集合匹配选IN。

33. MySQL中int(10)和char(10)的区别？

int(10) 和 char(10) 核心区别：

存什么：
- int(10)：只能存整数（如 123），括号里的 10 是 “显示宽度”（仅当设置 ZEROFILL 时生效，如 INT(10) ZEROFILL 会在数字前补 0列如：0000000123）（不影响实际存储）。
- char(10)：只能存字符串（如 '123'、'abc'），括号里的 10 是 “固定长度”（必须存 10 个字符，不够补空格）。
占多少空间：
- int(10)：固定 4 字节（和 10 无关）。
- char(10)：按字符算（如 utf8 下 10 个汉字占 30 字节）。
怎么用：
- 存数字（ID、数量）用 int(10)，运算快、省空间。
- 存固定长度字符串（如手机号前 10 位）用 char(10)，查询快。

34. truncate、delete与drop区别？

TRUNCATE、DELETE、DROP 都是 MySQL 中删除数据或对象的操作，但适用场景、执行原理和影响差异很大，核心区别如下：

1. 操作对象与效果

DELETE：
- 对象：表中的 数据行（不删表结构）。
- 效果：删除满足 WHERE 条件的行（若不加 WHERE，则删除全表数据，但表结构、索引、约束等保留）。
TRUNCATE：
- 对象：表中的 所有数据（不删表结构，但会重置自增 ID）。
- 效果：清空全表数据，表结构、索引等保留，但自增列会重置为初始值（如 AUTO_INCREMENT=1）。
DROP：
- 对象：整个表（或数据库），包括数据、结构、索引、约束等。
- 效果：删除表本身（或库），后续无法再对该表执行查询 / 插入，除非重建。

2. 适用场景

操作	适用场景	典型示例
`DELETE`	需删除部分数据（带 `WHERE`），或需回滚	`DELETE FROM user WHERE age < 18;`
`TRUNCATE`	需清空全表数据（保留表结构），追求效率	`TRUNCATE TABLE user;`
`DROP`	需彻底删除表（或库），不再使用	`DROP TABLE user;` 或 `DROP DATABASE db;`

35. having和where区别？

HAVING 和 WHERE 都是 MySQL 中用于过滤数据的条件，但 作用时机、适用对象 完全不同，核心区别如下：

1. 作用时机：“分组前” vs “分组后”

WHERE：在 数据分组（GROUP BY）之前 过滤行。先根据 WHERE 条件筛选出符合要求的行，再对剩下的行进行分组。例：SELECT dept FROM emp WHERE salary > 5000 GROUP BY dept逻辑：先筛出工资 > 5000 的员工，再按部门分组。
HAVING：在 数据分组（GROUP BY）之后 过滤组。先分组，再根据 HAVING 条件筛选出符合要求的组（对分组后的结果再过滤）。例：SELECT dept, AVG(salary) avg_sal FROM emp GROUP BY dept HAVING avg_sal > 5000逻辑：先按部门分组并计算平均工资，再筛出平均工资 > 5000 的部门。

2. 适用对象：“原始列” vs “聚合函数 / 分组列”

WHERE：只能使用 原始表中的列（不能用聚合函数，如 SUM()、AVG()）。错误示例：SELECT dept FROM emp WHERE AVG(salary) > 5000 GROUP BY dept（WHERE 中用了 AVG()，报错）。
HAVING：可以使用 聚合函数 或 GROUP BY 中的分组列。正确示例：SELECT dept, COUNT(*) cnt FROM emp GROUP BY dept HAVING cnt > 10（用分组后的 cnt 过滤）。

3. 总结：关键区别表

区别	`WHERE`	`HAVING`
作用阶段	分组前过滤行	分组后过滤组
支持的条件	原始列，不能用聚合函数	聚合函数、分组列
配合 `GROUP BY`	必须在 `GROUP BY` 之前	必须在 `GROUP BY` 之后

一句话记区别

WHERE 是 “分组前筛行，不用聚合函数”；
HAVING 是 “分组后筛组，可用聚合函数”。

只有用到 GROUP BY 分组时，才需要区分两者；没分组时，只用 WHERE 即可。

36. 什么是MySQL主从同步？

MySQL 主从同步（Master-Slave Replication）是一种 数据备份与读写分离的解决方案，通过将主数据库（Master）的变更同步到从数据库（Slave），实现数据多副本存储和负载分担。

核心目的

数据备份：从库作为主库的副本，避免主库单点故障导致数据丢失。
读写分离：主库负责 “写操作”（INSERT/UPDATE/DELETE），从库负责 “读操作”（SELECT），分散单库压力。

同步原理（3 步核心流程）

主库记录变更到 binlog主库执行写操作时，会将操作逻辑记录到 binlog（二进制日志） 中（binlog 是 MySQL 服务器层日志，记录所有数据修改）。
从库读取主库的 binlog从库启动一个 IO 线程，连接主库并请求读取 binlog；主库启动一个 dump 线程，将 binlog 内容发送给从库，从库将其写入本地的 relay log（中继日志）。
从库执行中继日志中的操作从库启动一个 SQL 线程，读取 relay log 中的内容，逐条执行相同的操作，从而让从库数据与主库保持一致。

主从同步的关键特点

异步同步：默认情况下，主库写完 binlog 就返回，不等待从库同步完成（性能高，但极端情况可能有数据延迟）。
单向同步：只有主库的数据会同步到从库，从库的修改不会影响主库（通常从库设为只读，避免数据不一致）。
可扩展为一主多从：一个主库可以连接多个从库，分担更多读压力（如主库写，3 个从库分别处理不同业务的读请求）。

一句话总结

主从同步就是 “主库写数据记日志，从库抄日志跟着改”，既保证数据有备份，又能让读写分开干活，提升系统稳定性和性能。

37. 乐观锁和悲观锁是什么？

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁： 假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。
- 实现方式：使用数据库中的锁机制。
乐观锁： 假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。
- 实现方式：乐观锁一般使用版本号机制或CAS算法实现。

38. 用过 processlist 吗？

show processlist 是 MySQL 中用于 查看当前数据库连接和执行中的线程 的重要命令，常用于排查连接数过高、慢查询阻塞等问题。

一、核心作用

查看当前所有客户端与 MySQL 的连接信息（谁在连、从哪连、执行什么操作）；
定位慢查询 / 卡死的 SQL（执行时间过长的进程）；
排查锁阻塞（比如某个进程长时间处于 “Waiting for table lock” 状态）；
清理异常连接（杀死卡死 / 空闲的进程）。

二、使用方式（两种常用形式）

基础版：SHOW PROCESSLIST

sql

-- 查看当前所有进程（仅显示前100行，Info字段只显示前100个字符）
SHOW PROCESSLIST;

-- 查看全量进程（无行数限制，Info字段显示完整SQL）
SHOW FULL PROCESSLIST;

进阶版：查询系统表

sql

-- 从information_schema库查询，支持筛选、排序（更灵活）
SELECT * FROM information_schema.processlist 
WHERE COMMAND != 'Sleep' -- 排除空闲连接
  AND TIME > 10 -- 筛选执行时间>10秒的进程
ORDER BY TIME DESC;

三、关键字段含义：

processlist的关键字段决定了排查方向，核心字段如下：

字段名	含义与核心解读
Id	进程 ID（杀死进程用`KILL Id;`）
User	发起连接的 MySQL 用户（如 root、app_user）
Host	客户端 IP + 端口（如`192.168.1.100:54321`，定位哪个客户端的连接）
db	该进程操作的数据库名（NULL 表示未指定库）
Command	进程当前状态（核心！）：- Sleep：空闲连接（客户端未发指令）- Query：正在执行 SQL- Connect：正在建立连接- Locked：被锁阻塞- Update/Insert/Delete：执行写操作
Time	进程处于当前状态的时长（单位：秒）：- Sleep>300：闲置过久的连接- Query>10：慢查询嫌疑
State	更细粒度的执行状态（如`Sending data`：正在返回数据、`Waiting for row lock`：等行锁）
Info	进程正在执行的 SQL 语句（NULL 表示无 SQL），`SHOW FULL PROCESSLIST`显示完整 SQL

四、实战场景示例

排查慢查询（执行时间过长的 SQL）

sql

-- 找出执行时间>30秒的非空闲进程，显示完整SQL
SELECT Id, User, Host, Time, Info 
FROM information_schema.processlist 
WHERE Command != 'Sleep' AND Time > 30
ORDER BY Time DESC;

杀死卡死的进程

sql

-- 先查进程ID（比如Id=123）
SHOW FULL PROCESSLIST;
-- 杀死该进程
KILL 123;

排查锁阻塞（找被锁的进程）

sql

-- 筛选处于Locked状态的进程
SELECT Id, User, Info, State 
FROM information_schema.processlist 
WHERE State LIKE '%lock%';

统计空闲连接数（优化连接池）

sql

-- 统计Sleep状态且空闲>60秒的连接数
SELECT COUNT(*) AS idle_conn 
FROM information_schema.processlist 
WHERE Command = 'Sleep' AND Time > 60;

五、关键注意事项

SHOW PROCESSLIST需要PROCESS权限（普通用户默认只有查看自己的进程，root 可看所有）；
Time字段对 Sleep 进程：表示空闲时长；对 Query 进程：表示 SQL 执行时长；
不要随意 kill 系统进程（如system user的复制进程），仅清理业务异常进程；
若大量 Sleep 进程，需优化应用连接池（如缩短空闲超时时间wait_timeout）。

总结

processlist是 MySQL 的 “任务管理器”，核心用于排查连接、慢查询、锁阻塞问题；
重点关注Command（进程状态）、Time（执行 / 空闲时长）、Info（执行的 SQL）三个字段；
实战中优先用SHOW FULL PROCESSLIST看完整 SQL，或用系统表做精准筛选，异常进程用KILL Id清理。

39. MySQL查询 limit 1000,10 和limit 10 速度一样快吗？

MySQL 中 LIMIT 1000,10 和 LIMIT 10 的执行速度 不一样，通常 LIMIT 10 更快，核心原因在于两者的 扫描范围和数据定位方式不同：

LIMIT 10：只需要从表中读取 前 10 条数据，找到后立即停止扫描（如果有索引优化，会更快定位）。例：若表有主键索引，直接按顺序取前 10 条，几乎无额外开销。
LIMIT 1000,10：需先扫描并跳过 前 1000 条数据，再读取接下来的 10 条。关键问题：即使只需要最后 10 条，MySQL 也必须先处理前面的 1000 条（无论是全表扫描还是走索引，都要定位到第 1000 条的位置），扫描范围更大。

关键问题：即使只需要最后 10 条，MySQL 也必须先处理前面的 1000 条（无论是全表扫描还是走索引，都要定位到第 1000 条的位置），扫描范围更大。

40. 深分页怎么优化？

深分页（如 LIMIT 100000, 10）的性能问题源于 MySQL 需扫描并跳过大量前置数据（即使最终只取 10 条），优化的核心思路是 避免全量扫描偏移量，直接定位到目标数据。以下是几种常用方案：

方案 1：用 “索引定位” 替代偏移量（推荐）

原理： 利用上一页的 “最大主键 / 唯一索引值” 作为条件，直接从该位置开始查询，跳过前面的所有数据。

适用场景

有自增主键（如 id）或唯一有序字段（如 create_time + id 组合）。
分页按索引字段排序（如 ORDER BY id 或 ORDER BY create_time）。

示例

假设分页按 id 升序排列，第 10000 页（LIMIT 100000, 10）：

sql

-- 传统深分页（慢）
SELECT * FROM user ORDER BY id LIMIT 100000, 10;

-- 优化后（快）：用上一页最后一条id=100000作为条件
SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 10;

优势：通过索引直接定位到 id > 100000 的位置，无需扫描前 100000 条，效率接近单页查询。

方案 2：“延迟关联” 减少扫描字段

**原理：**先通过索引查询目标数据的主键，再关联原表获取完整字段，避免扫描大量无用字段。

适用场景

表字段多（如大表有几十列），SELECT * 会加载过多数据。
有合适的索引（如排序字段 + 主键的联合索引）。

示例

sql

-- 传统方式（扫描全字段，慢）
SELECT * FROM user ORDER BY create_time LIMIT 100000, 10;

-- 延迟关联（先查主键，再关联，快）
SELECT u.* 
FROM user u
INNER JOIN (
  -- 子查询用索引查主键，扫描成本低
  SELECT id FROM user ORDER BY create_time LIMIT 100000, 10
) t ON u.id = t.id;

优势：子查询仅扫描索引字段（如 create_time + id 联合索引），获取主键后再回表取完整数据，减少无效字段的 IO 开销。

41. 高度为3的B+树，可以存放多少数据？

高度为 3 的 B + 树能存放的数据量并非固定值，核心取决于数据库的页大小、索引键值与指针大小、单条数据记录大小，以常用的 MySQL InnoDB 引擎为例，其默认参数下的估算结果约为2000 万 - 4300 万条，具体计算过程如下：

确定基础参数
InnoDB 默认数据页大小为 16KB（16384 字节）；非叶子节点存储索引键值和指针，若索引键为 BIGINT 类型（8 字节），InnoDB 中指针为 6 字节，那么单个索引条目大小为 8+6=14 字节；叶子节点存储实际数据记录，需假设单条记录大小，常见假设为 1KB 或 500 字节。
计算每层节点的承载量
- 非叶子节点（根节点 + 中间层）：每个非叶子节点以 16KB 为单位存储索引条目，单个节点可存储的条目数约为 16384÷14≈1170 个。这意味着根节点可指向 1170 个中间层节点，每个中间层节点又可指向 1170 个叶子节点，中间层总共能关联 1170×1170=1368900 个叶子节点。
- 叶子节点：若单条记录为 1KB，单个 16KB 的叶子节点可存储 16384÷1024≈16 条记录；若单条记录为 500 字节，单个叶子节点可存储 16384÷500≈32 条记录。
计算总数据量
- 当单条记录 1KB 时，总数据量 = 1170×1170×16=21902400 条（约 2190 万条）；
- 当单条记录 500 字节时，总数据量 = 1170×1170×32=43804800 条（约 4380 万条）。

此外，若调整参数，数据量会大幅变化。比如主键改用 4 字节的 INT 类型，单个非叶子节点条目数会增至 16384÷（4+6）≈1638 个；若数据页调整为 32KB，非叶子节点和叶子节点的承载量会翻倍，高度为 3 的 B + 树存储量可突破亿级。

42. MySQL单表多大进行分库分表？

目前主流的有两种说法：

MySQL 单表数据量大于 2000 万行，性能会明显下降，考虑进行分库分表。
阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，这个数值和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为MySQL为了提高性能，会将表的索引装载到内存中。在InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。

因此，对于分库分表，需要结合实际需求，不宜过度设计，在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。对此，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

至于MySQL单表多大进行分库分表，应当根据机器资源进行评估。

43. 大表查询慢怎么优化？

某个表有近千万数据，查询比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
利用缓存。利用Redis等缓存热点数据，提高查询效率
限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
读写分离。经典的数据库拆分方案，主库负责写，从库负责读
通过分库分表的方式进行优化，主要有垂直拆分和水平拆分

44. 说说count(1)、count(*)和count(字段名)的区别

在 MySQL 中，count(1)、count(*) 和 count(字段名) 都用于统计行数，但它们的统计逻辑、执行效率和适用场景存在显著区别，主要差异体现在 统计范围 和 对 NULL 值的处理 上。

1. 核心区别：统计范围与 NULL 处理

函数	统计范围	是否忽略 NULL 值
`count(*)`	统计所有行数（包括所有列，无论字段是否为 NULL）	不忽略，所有行都计入统计
`count(1)`	统计所有行数（用常量 “1” 代表每行，与字段无关）	不忽略，所有行都计入统计
`count(字段名)`	统计指定字段非 NULL 值的行数	忽略，仅统计字段值不为 NULL 的行

2. 执行效率对比

在大多数场景下，效率从高到低排序为：count(*) ≈ count(1) > count(非空索引字段) > count(允许 NULL 的字段)

count(*) 和 count(1) 效率接近：MySQL 对 count(*) 有特殊优化，而 count(1) 无需解析字段，两者在有索引时都会优先使用索引统计。
count(字段名) 效率较低：需判断字段是否为 NULL，若字段无索引，会触发全表扫描，大表中性能差异明显。

3. 适用场景

统计 表的总行数（包括所有行，无论字段是否为 NULL）：优先用 count(*)（语义最清晰，优化最好）。
需用常量替代统计逻辑（如兼容其他数据库语法）：可用 count(1)，结果与 count(*) 一致。
统计 指定字段非 NULL 值的行数（如 “有效手机号的用户数”）：必须用 count(字段名)（需确保字段可能为 NULL，否则意义与 count(*) 重复）。

总结

count(*)：统计所有行，包括 NULL，优化最好，推荐用于 “总记录数统计”。
count(1)：功能与 count(*) 一致，效率接近，语义稍弱，较少优先使用。
count(字段名)：仅统计字段非 NULL 的行，效率较低，用于特定业务场景（需排除 NULL 值时）。

实际开发中，除非明确需要统计 “非 NULL 字段行数”，否则优先使用 count(*)。

45. MySQL中 datetime 和 timestamp 有什么区别？

MySQL 中 DATETIME 和 TIMESTAMP 的核心区别可简化为三点：

范围：
- DATETIME：1000-01-01 到 9999-12-31（范围极广）。
- TIMESTAMP：1970-01-01 到 2038-01-19（受限于 Unix 时间戳）。
时区：
- DATETIME：存固定字符串，不随时区变化。
- TIMESTAMP：存 UTC 时间戳，查询时自动按时区转换。
存储：
- DATETIME 占 8 字节，TIMESTAMP 占 4 字节（更省空间）。

场景建议：跨时区、时间在 1970-2038 年用 TIMESTAMP；需超范围时间或固定本地时间用 DATETIME。

⚠️ 注意：2038 年将面临 “2038 问题”（超过 32 位整数最大值），需提前规划升级（如改用 DATETIME 或 MySQL 8.0 支持的 TIMESTAMP(6) 扩展范围）。

46. 说说为什么不建议用外键？

在 MySQL 等关系型数据库中，外键（Foreign key）用于强制束两个表的关联关系（如订单表的 user_id 关联用户表的 id），保证数据一致性。但实际开发中，不建议使用外键，主要原因如下：

降低写入性能，增加数据库负担
增加表结构耦合，不利于维护和扩展
故障影响范围扩大
可通过代码更灵活地替代

总结

外键的 “强约束” 特性在小型、低并发系统中可能简化开发，但在中大型系统中，会成为性能瓶颈和维护负担。

替代方案：用代码逻辑保证数据关联的合法性，配合唯一索引、业务日志等机制，既能避免外键的弊端，又能满足数据一致性需求，同时更适应高并发、可扩展的架构。

47. 使用自增主键有什么好处？

使用自增主键的核心好处：

唯一且省心：数据库自动生成，不重复，不用需手动维护。
性能好：InnoDB 中作为聚集索引，连续递增减少存储碎片，查询更快，且占用空间小（比 UUID 等更省）。
关联 / 排序方便：小字段关联查询高效，按主键排序天然利用存储顺序。
利于扩展：分库分表时，可按主键范围分片，规则简单。

48. 自增主键保存在什么地方？

这个问题很基础但能考察对 MySQL 存储细节的理解，自增主键的当前值主要保存在内存中，同时会通过特定机制持久化到磁盘以防止数据丢失。

1. 核心存储位置：内存

MySQL 启动时，会从存储引擎（如 InnoDB）的数据文件中读取自增主键的当前最大值，将其加载到内存中的自增计数器（Auto-Increment Counter） 里。

每次执行INSERT语句生成新记录时，直接从内存计数器中获取下一个自增 ID，同时将计数器值加 1。
这种内存存储方式能保证自增 ID 的分配效率，避免频繁磁盘 IO。

2. 持久化机制：防止重启丢失

仅靠内存存储会导致 MySQL 重启后计数器重置，因此需要持久化策略，不同存储引擎的实现不同：

InnoDB 引擎：自增主键的当前值会持久化到redo log中。每次修改计数器后，MySQL 会将新值写入 redo log；重启时，会从 redo log 中恢复最新的计数器值，避免 ID 回滚。
MyISAM 引擎：自增当前值直接保存在数据文件（.MYD） 的头部。重启时读取数据文件头部即可恢复计数器，无需依赖日志。

49. 自增主键一定是连续的吗？

自增主键不连续的 4 种核心场景总结

唯一键冲突：插入数据违反唯一约束导致失败，但自增 ID 已完成分配且不会回收，下次插入将使用新 ID，造成断层。
事务回滚：显式事务中插入数据时已分配自增 ID，若事务最终回滚，已分配的 ID 不会归还，后续插入需重新申请新 ID。
批量插入预分配：执行批量插入语句（如INSERT ... SELECT）时，MySQL 会按 “1→2→4→8...” 的倍数预分配自增 ID，未用完的预分配 ID 会被浪费，导致后续 ID 断层。
自增步长非 1：若通过参数auto_increment_increment将自增步长设置为 2 或更大数值，生成的自增 ID 本身就会天然不连续（如 1、3、5...）。

50. InnoDB的自增值为什么不能回收利用？

InnoDB 自增值不能回收利用，核心目的是在保证自增主键唯一性的前提下，最大化插入效率与系统并发度，其设计逻辑可从冲突风险、性能代价两方面总结：

一、核心矛盾：回收自增值会导致主键冲突

当多个事务并行申请自增值时，若允许已分配的自增值因事务失败（如唯一键冲突、回滚）回收，会直接破坏主键唯一性：

并行事务场景示例：事务 A 申请到 id=2、事务 B 申请到 id=3，此时表的自增值已更新为 4。若事务 A 因唯一键冲突失败，若将自增值回退为 2，后续新事务会重新申请到 id=2；但事务 B 已提交 id=3 的记录，新事务插入 id=3 时会触发 “主键冲突” 报错。
本质问题：自增值一旦分配给事务，即使事务失败，该值也已脱离 “未使用” 状态 —— 其他事务可能已基于 “自增值已递增” 的结果分配到更大的 id 并提交，回收旧值必然导致后续 id 重复。

二、解决方案的性能代价不可接受

若强行实现自增值回收，需解决主键冲突问题，但两种可行方案均会严重牺牲性能：

方案 1：申请 id 前校验唯一性
每次分配自增值前，先查询主键索引树，判断该 id 是否已存在（若存在则跳过）。但此操作会额外增加一次索引树查询的 I/O 开销，将 “快速分配 id” 的轻量操作变为耗时的校验流程，大幅降低插入效率。
方案 2：扩大自增锁粒度
要求事务执行完成并提交后，才释放自增锁允许下一个事务申请 id。这会将 “细粒度的自增锁” 升级为 “事务级锁”，完全阻塞并行插入，系统并发能力骤降，无法应对高吞吐场景。

三、最终设计：放弃回收，优先保证效率与唯一性

为规避上述冲突风险与性能问题，InnoDB 采用 “自增值分配后不回收” 的设计：无论事务是否成功（提交 / 回滚 / 冲突），已分配的自增值均不会回退，后续插入直接使用当前自增值（已递增后的数值）。这种设计虽可能导致自增值不连续（如事务失败后跳过部分 id），但相比 “回收带来的冲突与性能损耗”，是更优的权衡 —— 毕竟自增值的核心诉求是 “唯一标识”，而非 “连续序列”。

51. MySQL数据如何同步到Redis缓存？

MySQL 数据同步 Redis 主要有两类方案，核心是平衡实时性和复杂度，简化如下：

一、主动同步（实时性高，适合写多 / 需实时场景）

1. 业务代码层同步

逻辑：改 MySQL 后，直接在代码里加 Redis 操作（如 MySQL 更新后调用 Redis 的 SET/DEL）
优点：简单直接，延迟低；缺点：代码耦合，易漏写

2. binlog + 中间件同步（主流）

逻辑：MySQL 开 binlog 记录变更，用 Canal/MaxWell 解析 binlog，自动同步到 Redis
优点：解耦业务和 MySQL，高可用；缺点：需搭中间件，维护成本稍高

二、被动加载（实时性低，适合读多 / 实时要求低场景）

逻辑：查数据时先查 Redis，没有再查 MySQL，查完写 Redis（设过期时间）
优点：无需额外同步组件，省存储；缺点：首次查 / 缓存过期有延迟，需防 “缓存穿透”（如查不到时存空值标记）

选择建议

实时场景（如订单、库存）：用 binlog + 中间件
读多场景（如商品详情）：用被动加载

52. 为什么阿里Java手册禁止使用存储过程？

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程主要有以下几个缺点。

存储过程难以调试。存储过程的开发一直缺少有效的 IDE 环境。SQL 本身经常很长，调试式要把句子拆开分别独立执行，非常麻烦。
移植性差。存储过程的移植困难，一般业务系统总会不可避免地用到数据库独有的特性和语法，更换数据库时这部分代码就需要重写，成本较高。
管理困难。存储过程的目录是扁平的，而不是文件系统那样的树形结构，脚本少的时候还好办，一旦多起来，目录就会陷入混乱。
存储过程是只优化一次，有的时候随着数据量的增加或者数据结构的变化，原来存储过程选择的执行计划也许并不是最优的了，所以这个时候需要手动干预或者重新编译了。

53. MySQL update 是锁行还是锁表？

MySQL 的 UPDATE 语句是锁行还是锁表，取决于 存储引擎、索引使用情况 和 隔离级别，核心规则如下：

核心前提：仅 InnoDB 支持行锁，MyISAM 等引擎只锁表

MyISAM、MEMORY 等引擎：无论如何执行 UPDATE，都会对整个表加锁（表级锁），此时其他事务无法读写该表，并发性能差。
InnoDB 引擎：默认支持行级锁，但行锁的生效依赖索引，若未使用索引或索引失效，会退化为表级锁。

54. select...for update会锁表还是锁行？

核心前提：仅 InnoDB 支持行锁，依赖索引

MyISAM 等引擎：不支持行锁，select ... for update 会直接锁表（表级锁）。
InnoDB 引擎：默认尝试加行锁，但行锁生效的关键是 WHERE 条件能否利用有效索引定位到具体行。

55. MySQL的binlog有几种格式？分别有什么区别？

MySQL 的 binlog（二进制日志）有三种格式，分别是statement、row和mixed，核心区别在于记录数据变更的方式和适用场景：

1. statement（记 SQL）

只记录执行的 SQL 语句（比如update user set name='a' where id=1）。
优点：日志体积小，省空间；缺点：含now()等函数时，主从同步可能不一致。

2. row（记行变更）

记录每行数据的具体变化（比如 “id=1 的行，name 从 'b' 改成 'a'”）。
优点：主从同步绝对一致；缺点：日志体积大，占空间多。

3. mixed（混合）

平时用 statement，检测到可能不一致的 SQL（如含now()）时，自动切为 row。
优点：平衡体积和一致性；缺点：切换逻辑复杂，极端情况仍有风险。

选择建议

要稳定、高一致性（如主从、数据同步）：优先用row。
简单场景、想省空间：可用statement。
不确定需求：用mixed过渡。

56. 阿里手册为什么禁止使用 count(列名)或 count(常量)来替代 count(*)

阿里手册禁止用count(列名)或count(常量)替代count(*)，核心原因是三者语义和行为不同，易出错：

语义差异导致结果错
- count(*)：明确统计所有行数（包括NULL值行），是 SQL 标准的 “总行数” 语法。
- count(列名)：只统计列非 NULL 的行数，若列有 NULL，结果会少算。
- count(常量)（如count(1)）：结果虽和count(*)一致，但语义不直观，容易让别人误解为 “统计某列值”。
性能和可读性问题
- count(列名)需判断 NULL，性能比count(*)差（数据库对count(*)有专门优化）。
- 用count(*)更规范，团队协作时减少理解成本，避免混淆。

简言之，count(*)是统计总行数的 “标准答案”，替代方案要么结果错，要么不直观，所以被禁止。

57. 存储MD5值应该用VARCHAR还是用CHAR？

存储 MD5 值建议用CHAR(32)，而非 VARCHAR，核心原因是 MD5 值的长度固定且语义明确：

MD5 值特性决定MD5 加密后始终生成32 位十六进制字符串（长度固定，无 NULL 或变长情况），CHAR (32) 能精准匹配其长度，而 VARCHAR 用于存储变长字符串，此处无需 “动态调整长度”。
性能与空间优势
- CHAR (32) 存储时会固定占用 32 字节，查询时无需计算长度，效率略高于 VARCHAR（VARCHAR 需额外存储长度信息）。
- 若用 VARCHAR (32)，虽功能可用，但会浪费 1-2 字节的长度标识空间（MySQL 中 VARCHAR 需额外存储长度信息），且语义上不如 CHAR 清晰（CHAR 更直观体现 “固定长度” 特性）。
规范性用 CHAR (32) 能明确表结构中该字段的用途（MD5 值），避免他人误解为 “可能变长的字符串”，提升代码可读性。

综上，存储 MD5 值优先选择CHAR(32)，匹配其固定长度特性，兼顾性能与规范性。

MySQL常见面试题总结 ​

1. 事务的四大特性？ ​

2. 事务隔离级别有哪些？ ​

3. 编码和字符集的关系 ​

4. utf8和utf8mb4的区别 ​

1. 编码范围不同 ​

2. 核心差异：是否支持 4 字节字符 ​

3. 应用场景（以 MySQL 为例） ​

4. 存储与性能影响 ​

总结 ​

5. 什么是索引？ ​

6. 索引的优缺点？ ​

7. 什么情况下需要建索引？ ​

8. 什么情况下不建索引？ ​

9. MySQL 索引的数据结构有哪些？ ​

10. Hash索引和B+树索引的区别？ ​

1. 数据结构本质 ​

2. 查询能力 ​

3. 性能特性 ​

4. 适用场景 ​

总结 ​

11. B树和B+树的区别？ ​

1. 数据存储位置 ​

2. 叶子节点的连接方式 ​

3. 树的高度与 IO 效率 ​

4. 查询性能的稳定性 ​

5. 适用场景 ​

总结对比表 ​

12. 索引有哪些分类？ ​

一、按「数据结构」分类（核心分类） ​

二、按「物理存储」分类（InnoDB 核心特性） ​

三、按「功能逻辑」分类 ​

四、其他特殊索引 ​

总结 ​

13. 什么是最左匹配原则？ ​

具体原理 ​

示例说明 ​

为什么有这个原则？ ​

14. 什么是聚集索引和非聚集索引？ ​

一、聚集索引（Clustered Index） ​

二、非聚集索引（Non-Clustered Index） ​

三、核心区别对比 ​

四、使用场景建议 ​

15. 什么是覆盖索引？ ​

一、覆盖索引的核心原理 ​

二、覆盖索引的典型场景 ​

16. 索引的设计原则？ ​

17. 索引什么时候会失效？ ​

一、索引列参与计算或函数操作 ​

二、使用 NOT IN、!=、<> 或 IS NOT NULL ​

三、LIKE 以通配符 % 开头（左模糊查询） ​

四、联合索引不满足最左前缀原则 ​

五、索引列与常量类型不匹配（隐式转换、类似与索引列参与函数计算） ​

六、OR 连接的条件中存在非索引列 ​

七、优化器判断全表扫描更快 ​

如何判断索引是否失效？ ​

总结 ​

18. 什么是前缀索引？ ​

一、前缀索引的核心作用 ​

19. 索引下推是什么？ ​

一、索引下推的工作原理 ​

20. 常见的存储引擎有哪些？ ​

一、主流存储引擎核心特性对比 ​

21. MySQL有哪些锁？ ​

一、按锁粒度（最核心） ​

二、InnoDB 特有锁（解决事务隔离） ​

三、按锁策略（业务视角） ​

四、锁的兼容性（核心规则） ​

核心结论 ​

22. MVCC 是什么？ ​

核心目标 ​

实现三要素 ​

工作流程 ​

隔离级别差异 ​

23. 快照读和当前读？ ​

24. 共享锁和排他锁 ​

一、共享锁（S 锁）：“读锁”，支持并发读 ​

二、排他锁（X 锁）：“写锁”，独占数据 ​

三、核心区别与兼容性 ​

一句话总结 ​

MySQL常见面试题总结

1. 事务的四大特性？

2. 事务隔离级别有哪些？

3. 编码和字符集的关系

4. utf8和utf8mb4的区别

1. 编码范围不同

2. 核心差异：是否支持 4 字节字符

3. 应用场景（以 MySQL 为例）

4. 存储与性能影响

总结

5. 什么是索引？

6. 索引的优缺点？

7. 什么情况下需要建索引？

8. 什么情况下不建索引？

9. MySQL 索引的数据结构有哪些？

10. Hash索引和B+树索引的区别？

1. 数据结构本质

2. 查询能力

3. 性能特性

4. 适用场景

总结

11. B树和B+树的区别？

1. 数据存储位置

2. 叶子节点的连接方式

3. 树的高度与 IO 效率

4. 查询性能的稳定性

5. 适用场景

总结对比表

12. 索引有哪些分类？

一、按「数据结构」分类（核心分类）

二、按「物理存储」分类（InnoDB 核心特性）

三、按「功能逻辑」分类

四、其他特殊索引

总结

13. 什么是最左匹配原则？

具体原理

示例说明

为什么有这个原则？

14. 什么是聚集索引和非聚集索引？

一、聚集索引（Clustered Index）

二、非聚集索引（Non-Clustered Index）

三、核心区别对比

四、使用场景建议

15. 什么是覆盖索引？

一、覆盖索引的核心原理

二、覆盖索引的典型场景

16. 索引的设计原则？

17. 索引什么时候会失效？

一、索引列参与计算或函数操作

二、使用 `NOT IN`、`!=`、`<>` 或 `IS NOT NULL`

三、`LIKE` 以通配符 `%` 开头（左模糊查询）

四、联合索引不满足最左前缀原则

五、索引列与常量类型不匹配（隐式转换、类似与索引列参与函数计算）

六、`OR` 连接的条件中存在非索引列

七、优化器判断全表扫描更快

如何判断索引是否失效？

总结

18. 什么是前缀索引？

一、前缀索引的核心作用

19. 索引下推是什么？

一、索引下推的工作原理

20. 常见的存储引擎有哪些？

一、主流存储引擎核心特性对比

21. MySQL有哪些锁？

一、按锁粒度（最核心）

二、InnoDB 特有锁（解决事务隔离）

三、按锁策略（业务视角）

四、锁的兼容性（核心规则）

核心结论

22. MVCC 是什么？

核心目标

实现三要素

工作流程

隔离级别差异

23. 快照读和当前读？

24. 共享锁和排他锁

一、共享锁（S 锁）：“读锁”，支持并发读

二、排他锁（X 锁）：“写锁”，独占数据

三、核心区别与兼容性

一句话总结