小红学堂（小红学编程）— Java培训、项目实战与就业辅导

Java 后端真实面试专题 · MySQL 篇

MySQL 是后端问得最细、追问最狠的一块。每题三段： ① 标准答（讲透：是什么+为什么+怎么做+原理）→ ② 拓展（成体系带出关联点和必追问的，答一题等于答一片）→ ③ 怎么接到你自己的项目。

年限标签：🟢 3年内　🔴 3年+

一、索引

1. 🟢 为什么要用索引？InnoDB 为什么用 B+ 树而不是别的？

标准答：索引是帮助 MySQL 高效查找的有序数据结构，没索引就得全表扫描。InnoDB 选 B+ 树是综合权衡的结果：

矮胖：B+ 树非叶子节点只存索引键、不存数据，一个 16KB 的页能放很多键，所以扇出大、树很矮（千万级数据一般只要 3~~4 层），查一次只要 3~~4 次磁盘 IO。
范围查询友好：所有数据都在叶子节点，且叶子之间用双向链表相连，范围查询、排序顺着链表走就行。
稳定：所有查询都要走到叶子，性能稳定。

可以把一次索引查询理解成“从根页定位到叶子页，再顺着叶子扫描”。InnoDB 以 16KB 页为基本读写单位，非叶子页只放键和子指针，扇出很大；真正的行记录在聚簇索引叶子页中。树高增加一层，就可能多一次随机 IO，因此索引设计的核心是减少需要访问的页，而不只是“把字段放进索引”。数据量、页大小、主键长度和选择性都会影响实际层数和收益。

拓展：面试官常对比着追问：

"为什么不用 B 树？"——B 树每个节点都存数据，单页能放的键变少、树更高、IO 次数多；且范围查询要中序遍历回溯。
"为什么不用红黑树/二叉树？"——二叉树太高（千万数据高度几十层），磁盘 IO 爆炸。
"为什么不用 Hash 索引？"——Hash 只支持等值查询，不支持范围和排序；不过 Memory 引擎和自适应哈希用了它。
一次磁盘 IO ≈ 读一个页（16KB），所以"树高 = IO 次数"是核心，矮胖最省 IO。
B+ 树并不是“永远只需要 3 次 IO”：热点页可能已经在 Buffer Pool 中，冷数据还可能因为回表、随机页和并发淘汰产生更多 IO。回答时应说“理论上降低 IO，实际要看缓存命中和扫描行数”。
索引也有成本：占用磁盘和 Buffer Pool，写入/更新要维护叶子顺序，索引越多写放大越明显。应从真实查询模式和 EXPLAIN 出发建立少而有效的索引。
Hash 适合精确匹配，LSM/倒排等结构在日志、搜索场景有优势；MySQL InnoDB 的通用 B+ 树是事务、范围和排序的折中，并非所有场景都唯一正确。

往项目引 ⭐："我项目订单表上千万行，按用户 id + 时间查历史订单，我建了 (user_id, create_time) 联合索引，查询从全表扫的秒级降到毫秒级。理解 B+ 树让我知道为什么联合索引顺序和范围查询会影响命中。"

2. 🟢 聚簇索引和二级索引的区别？什么是回表和覆盖索引？

标准答：

聚簇索引：InnoDB 通常把主键索引作为聚簇索引，叶子节点保存完整行记录，一张表只能有一个这种物理组织方式。主键范围扫描因此能顺便按主键顺序读取数据。
二级索引：叶子节点保存索引列和对应主键值（以及必要的隐藏列），不直接保存整行。先按二级索引定位，再按主键到聚簇索引取行，就是一次回表。
如果查询所需列全部包含在二级索引叶子中，直接从索引返回，不需要回表，称为覆盖索引，EXPLAIN 的 Extra 常见 Using index。覆盖索引减少随机页访问，但会增加索引体积和写入成本。
没有显式主键时，InnoDB 会选择第一个合适的唯一非空索引；都没有时生成隐藏的聚簇索引。主键变更通常代价很高，应保持稳定、短且尽量递增。

拓展：

"怎么减少回表？"——把高频查询要返回的列加进联合索引形成覆盖索引。
"为什么主键不要太长？"——所有二级索引都存主键值，主键越长，二级索引越占空间。
"没有主键会怎样？"——InnoDB 会选唯一非空索引，再没有就生成隐藏的 rowid 做聚簇索引。
这也是为什么 select * 不好——可能本来能覆盖索引，多查了列就被迫回表。
二级索引回表不是“再查一次表锁”，而是按主键再走一次聚簇 B+ 树；回表次数接近命中行数，低选择性查询可能因此比全表扫描更慢。
Using index condition（索引条件下推，ICP）和 Using index 含义不同：ICP 在存储引擎层先过滤部分条件，仍可能回表；确认是否覆盖要看实际返回列。
聚簇索引叶子存整行，所以主键过长会被复制到每个二级索引，放大存储和缓存压力；通常使用紧凑的整数或合理的二进制 ID。

往项目引 ⭐："我项目订单列表只展示订单号、状态、金额几列，我把这几列加进联合索引做成覆盖索引，列表查询不用回表、快了一倍多。这就是'按查询设计索引'。"

3. 🟢 联合索引的最左前缀原则是什么？

标准答：联合索引 (a, b, c) 的本质是先按 a 排、a 相同按 b 排、再按 c 排。所以只有从最左列开始、连续使用才能走索引：a、a+b、a+b+c 能用上，单独 b、c、b+c 用不上。

例如索引 (tenant_id, status, created_at) 的叶子顺序是先按租户分组，再按状态，最后按时间。where tenant_id=? and status=? order by created_at 可以定位到一个连续区间；只有 status=? 时，B+ 树无法直接跳到某个 status 的连续区间，只能扫描更多键。AND 条件的书写顺序不影响最左匹配，真正影响的是索引列顺序、等值/范围条件和排序方向。

拓展：

where a=? and c=? 通常只能把 a 用作索引定位，c 可能由 ICP 或回表后过滤；EXPLAIN 的 key_len 能帮助确认实际用了几列。
a=? and b>? and c=? 中，b 的范围会切断后续有序性，c 通常不能继续用于定位，但仍可能被索引条件下推过滤；不要简单说成“c 完全没用”。
order by/group by 也能利用联合索引，但要满足最左前缀、方向和常量条件，混合 ASC/DESC 还受 MySQL 版本和索引定义影响；否则会出现 Using filesort。
建索引应由查询工作负载决定：常见做法是把高频等值、租户/分区等过滤列放左侧，再放范围或排序列，同时评估选择性、写入成本和索引宽度。MySQL 8 的 skip scan 在部分场景可跳过前导列，但不能当作通用替代方案。

往项目引 ⭐："我项目排查过一个慢查询，就是联合索引列顺序建反了——把范围条件放前面导致后面列用不上。调整顺序后命中索引。所以我现在建联合索引会先想清楚查询模式再定列顺序。"

4. 🟢 索引失效有哪些常见情况？

标准答：

列上用函数或运算：where date(create_time)=?、where id+1=?。
隐式类型转换：字段是 varchar 却 where phone=138...（没加引号），等于对列做了转换。
违反最左前缀，或范围列后面的列。
like '%x' 前导模糊（'x%' 可以走）。
or 连接了非索引列。
!=、not in、is not null 有时不走。

索引失效的本质不是“语法不漂亮”，而是优化器无法利用索引的有序性，或者估算后认为扫描成本更低。比如 where date(create_time)=? 需要对每一行先计算函数，无法直接定位时间范围；正确写法通常是 create_time >= ? and create_time < ?。隐式类型转换、字符集/排序规则不一致，也可能让列被转换后无法使用索引。

拓展：

这题最好每条举例并给出改法：函数改范围条件；类型统一；前导 % 改前缀/倒排搜索；OR 拆成 UNION ALL（确认无重复）；对低选择性列评估是否需要索引。
用 EXPLAIN/EXPLAIN ANALYZE 看 key、type、实际行数和过滤比例；key 为 null 不一定是 bug，表很小或选择性低时全表扫描可能更快。
like '%x' 通常不能按 B+ 树前缀定位，但覆盖索引下可能走 index 全索引扫描；这只是减少行宽，不等于真正的范围查找。
可用生成列/函数索引（MySQL 8）把规范化结果预先索引；不要为了“强制走索引”滥用 force index，先确认统计信息和数据分布是否过期。

往项目引 ⭐："我项目线上出过事故：手机号字段是 varchar，代码传了 long 没加引号，隐式转换导致索引失效全表扫，CPU 飙高。加引号后恢复。所以我现在写 SQL 特别注意类型匹配和别在索引列上套函数。"

5. 🟢 EXPLAIN 怎么看？重点看哪几个字段？

标准答：

EXPLAIN 先看执行顺序和访问路径，再看代价。type 大致从好到差是 system/const > eq_ref > ref > range > index > ALL，但不能只凭等级下结论；还要结合实际行数和是否回表。
possible_keys 是候选索引，key 是优化器最终选择的索引，key_len 反映联合索引实际使用长度，ref 显示比较值来源；rows 和 filtered 是估算值，EXPLAIN ANALYZE 才能看到实际耗时/行数。
Extra 中 Using index 表示覆盖索引，Using index condition 表示 ICP；Using filesort 是未按索引顺序完成排序（不一定真的落磁盘），Using temporary 表示使用临时表，需结合数据量判断风险。

拓展：

"possible_keys 有但 key 是 null 说明什么？"——有候选索引但优化器估算全表更便宜，可能是表小、统计信息过期或选择性低；先 ANALYZE TABLE 和核对数据分布，再考虑改写或 force index。
key_len 要结合字段类型、字符集和 NULL 标记解读，不能简单按“字节数=列数”；它只能辅助判断最左前缀，最终以执行计划和实际耗时为准。
Using filesort 不一定是磁盘排序，只表示没有直接利用索引完成排序；排序集小可能在内存，排序集大才会产生明显 IO。
线上诊断可用 EXPLAIN FORMAT=JSON 查看成本、EXPLAIN ANALYZE 查看实际迭代器耗时，并把计划纳入慢查询回归测试，防止数据增长后退化。

往项目引 ⭐："我项目规定：写完复杂查询上线前必须 EXPLAIN 一遍，确认没有 ALL 和 filesort。这是我们组的性能红线，靠它拦住了很多潜在的慢查询。"

二、事务与锁

6. 🟢 事务的 ACID 分别靠什么实现？

标准答：

原子性（Atomicity）：事务内的多条修改要么全部提交、要么全部回滚。InnoDB 用 undo log 保存修改前的逻辑版本，回滚时按相反方向恢复；应用还要正确处理异常和事务边界。
一致性（Consistency）：提交后满足数据库约束和业务不变量，是最终目标，不是某一条日志单独提供的。主键/唯一键、外键、检查约束以及业务代码共同把数据从一个合法状态带到另一个合法状态。
隔离性（Isolation）：并发事务互不产生不允许的中间影响，由锁、MVCC、ReadView 和隔离级别共同实现。隔离越强，锁等待和并发成本通常越高。
持久性（Durability）：提交成功后即使崩溃也能恢复。InnoDB 先把页修改写入 redo log（WAL），提交时按 innodb_flush_log_at_trx_commit 策略刷盘，重启后重放日志恢复数据页。

一次更新通常同时留下三类痕迹：undo 用于回滚/读旧版本，redo 用于恢复数据页，binlog 用于复制和备份。事务提交不是“只写一处”，而是要协调这些日志的顺序。

拓展：

面试官会顺着每个特性往底层追，所以要能说出 undo/redo/锁/MVCC 对应关系。
"redo 和 binlog 区别？"——redo 是 InnoDB 物理日志（崩溃恢复），binlog 是 Server 层逻辑日志（主从同步），两者靠两阶段提交保证一致。
"为什么先写日志？"——WAL（Write-Ahead Logging），顺序写日志比随机写数据页快得多。
ACID 的前提是事务边界正确：Spring 的 @Transactional 只对经过代理调用的方法生效，自调用、异步线程、吞异常都可能让以为存在的事务失效；DDL、隐式提交和跨库调用也要单独确认。
redo/binlog 的刷盘策略会影响性能和丢失窗口；生产调参要结合磁盘、复制和 RPO，而不是只追求最高 TPS。
一致性还包括业务层校验（例如库存不能为负）和幂等设计，数据库事务无法自动覆盖消息队列、远程服务等外部副作用。

往项目引 ⭐："我项目下单要同时扣库存、建订单、加积分，用 @Transactional 包一个事务保证原子性，任何一步失败全回滚，绝不会出现'扣了库存没生成订单'的脏数据。"

7. 🟢 事务隔离级别有哪些？分别解决什么问题？MySQL 默认是哪个？

标准答：四个级别，逐级更严：

读未提交：能读到别人没提交的数据（脏读）。
读已提交（RC）：只能读已提交的，解决脏读；但同一事务两次读同一行可能不同（不可重复读）。
可重复读（RR，MySQL 默认）：同一事务多次读结果一致，解决不可重复读。
串行化：事务串行执行，解决幻读，性能最差。脏读=读到未提交；不可重复读=两次读同一行值变了（别人 update）；幻读=两次同样条件查出的行数变了（别人 insert）。

MySQL InnoDB 默认是 RR（Repeatable Read，可重复读）。普通 SELECT 属于快照读：在 RR 中通常复用事务第一次一致性读建立的 ReadView；SELECT ... FOR UPDATE、UPDATE、DELETE 属于当前读，会读取最新版本并按索引加锁。隔离级别应在连接/事务边界内明确设置，不能只看 ORM 的默认配置。

拓展：

"RR 解决幻读吗？"——InnoDB 在 RR 下用 MVCC（快照读）+ 间隙锁（当前读） 基本解决了幻读，这是它和标准 SQL 不同的地方。
"为什么大厂有的用 RC 不用默认的 RR？"——RC 锁范围小、并发更高、间隙锁少、死锁概率低。
隔离级别越高越安全但并发越差。
“解决问题”要区分快照读和当前读：InnoDB 的 RR 通过 MVCC 让快照读保持一致，通过间隙/临键锁约束当前读插入，因此比标准定义更接近“解决幻读”，但长事务、非索引条件和混用读方式仍可能产生意外。
RC 每次一致性读都创建新的 ReadView，能更快看到已提交更新，锁范围通常更小；高并发写业务有时显式选择 RC，但要评估不可重复读对业务的影响。
串行化不是“加一把全局表锁”这么简单，InnoDB 会把普通读转换为加锁读；吞吐、死锁和超时风险都要通过压测评估。
可用 SELECT @@transaction_isolation、SET TRANSACTION ISOLATION LEVEL ... 验证实际级别；连接池复用时要避免隔离级别泄漏到下一次请求。

往项目引 ⭐："我项目大多用默认的 RR 就够了；只有一个对账场景为了绝对一致，单独对那条查询用了 for update 当前读加锁。知道隔离级别让我清楚什么时候该手动加锁。"

8. 🟢 MVCC 是怎么实现的？

标准答：MVCC（多版本并发控制）让"读不加锁、读写不阻塞"，靠三样东西：

每行的隐藏字段：DB_TRX_ID（最近改它的事务 id）、DB_ROLL_PTR（回滚指针，指向 undo log）。
undo log 版本链：每次修改都把旧版本串成链表。
ReadView（一致性视图）：记录生成视图时活跃的事务 id 列表，读取时顺着版本链找到对当前事务"可见"的那个版本。

快照读拿到某行后，会根据 ReadView 判断当前版本的事务是否已提交、是否在视图创建时活跃：不可见就沿 DB_ROLL_PTR 回到 undo 里的旧版本，直到找到可见版本或判定不存在。RC 通常每次语句创建 ReadView，RR 通常在第一次一致性读时创建并复用，所以同一事务两次普通查询的结果不同与否由此决定。MVCC 并没有复制整张表，旧版本在 undo 中按需保存，后台 purge 会在确认没有活跃事务需要它后清理。

拓展：

"RC 和 RR 在 MVCC 上的区别？"——RC 每次查询都生成新的 ReadView（所以能读到别人新提交的），RR 在事务第一次查询时生成一次、之后复用（所以可重复读）。这是两者差异的根本。
"快照读和当前读？"——普通 select 是快照读（走 MVCC）；select for update、update、delete 是当前读（读最新并加锁）。
MVCC 只在 RC 和 RR 下工作。
“读不加锁”只对普通快照读成立，当前读仍会加记录/间隙/临键锁；写写冲突也不会因为 MVCC 消失。
长事务会一直保留旧 ReadView，使 undo 版本无法 purge，导致 undo 表空间膨胀、磁盘增长和查询回溯变慢。线上应监控 information_schema.innodb_trx、历史事务和 undo 使用量。
删除并不是立刻物理擦除，先写删除标记和 undo，等没有事务需要旧版本后才由 purge 回收；这解释了“删了很多数据但空间没马上回来”。
版本可见性依赖事务 id 和提交状态，不是简单按时间戳比较；回答时要避免把 ReadView 说成“复制了一份数据”。

往项目引 ⭐："理解 MVCC 帮我排查过一个'读到旧数据'的诡异问题——一个长事务里一直读的是事务开始时的快照，没看到中途别的事务提交的更新。后来把长事务拆短解决，并且我明白了为什么 RR 下会这样。"

9. 🔴 MySQL 有哪些锁？什么是间隙锁？行锁会升级成表锁吗？

标准答：

按粒度有表锁、页锁和行级锁；InnoDB 的行锁实际加在索引记录/间隙上，而不是直接锁 Java 对象或整行内存。
按模式有共享锁 S 和排他锁 X；InnoDB 还用意向锁（IS/IX）在表级声明事务将要持有的行锁，便于与表锁快速冲突检测。
InnoDB 的行级范围包括记录锁（锁住一条索引记录）、间隙锁（锁住两条记录之间的插入区间）和临键锁（记录锁 + 间隙锁，RR 当前读常见）。唯一等值命中时，间隙锁可能被优化掉；具体范围要看索引和执行计划。

拓展：

“行锁会退化成表锁吗？”严格说 InnoDB 不会把行锁模式自动升级成一把表锁；但更新条件未命中索引时，存储引擎可能扫描并锁住大量记录和间隙，效果上接近锁表。这是高频事故，必须用 EXPLAIN 确认走索引。
间隙锁主要用于 RR 下的当前读，禁止其他事务在范围内插入，从而抑制幻读；它本身不锁住已有记录，临键锁才是记录 + 间隙的组合。
锁范围受唯一/非唯一索引、等值/范围条件、扫描方向影响；辅助索引加锁后，相关主键记录也可能被锁。隔离级别改为 RC 后间隙锁通常减少，但外键检查等场景仍可能出现。
诊断锁等待可结合 performance_schema.data_locks/data_lock_waits、SHOW ENGINE INNODB STATUS 和事务 SQL，不能只看应用线程栈猜测。

往项目引 ⭐："我项目出过一次事故：一个 update 的 where 条件列没加索引，导致 InnoDB 锁了全表，其他请求全卡住。定位后给条件列加索引、行锁恢复正常。所以我牢记'行锁一定要走索引'。"

10. 🔴 出现死锁怎么排查、怎么避免？

标准答：排查——SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK 段，里面有两个事务互相等待的 SQL、持有和等待的锁，对照代码找加锁顺序。避免——统一多行/多表的加锁顺序（最常用）、缩短事务、降低隔离级别、让 update/delete 走索引避免锁范围扩大、select for update 控制锁的粒度。

死锁不是普通的“锁等待超时”：InnoDB 检测到等待图形成环后，会主动选择一个事务回滚并返回 Deadlock found when trying to get lock，让另一个事务继续。线上先保留错误日志、事务 SQL、锁模式和执行计划，再判断是否是同一资源顺序相反、范围锁重叠或事务持锁时间过长。MySQL 8 还可从 performance_schema.data_locks、data_lock_waits 还原当前等待图。

拓展：

可开 innodb_print_all_deadlocks=ON 把历史死锁写入错误日志，并设置采样/告警；不要只把 innodb_lock_wait_timeout 调大来“掩盖”问题。
InnoDB 通常回滚代价较小的事务，但业务必须捕获死锁错误、在事务外按有限次数重试，并保证操作幂等；重试不能复用已标记回滚的连接状态。
统一资源顺序（例如按账户 id 升序）是在破坏“循环等待”；同时缩短事务、拆分批量、补齐索引、避免用户交互期间持锁，可降低死锁概率。
事务中混用不同索引或范围条件会扩大锁集合；改 SQL 前要重新看 EXPLAIN 和实际锁范围，不能只凭代码表面顺序判断。

往项目引 ⭐："我项目转账两个账户互转曾死锁——A 转 B 和 B 转 A 加锁顺序相反。后来统一'按账户 id 从小到大加锁'，破坏循环等待根治，这是最经典的死锁避免手段。"

三、性能优化

11. 🟢 一条 SQL 调优的完整思路？

标准答：

定位：开慢查询日志（slow_query_log，设 long_query_time），用 pt-query-digest 找出高频慢 SQL。
分析：对慢 SQL EXPLAIN，看 type/key/rows/Extra 找问题（没走索引？回表多？filesort？）。
优化：加/改索引、做覆盖索引、改写 SQL（避免 select *、避免函数、减少子查询）、避免大事务。
架构层：仍不行就加缓存、读写分离、分库分表。

完整流程还应包括复现和验证：先记录 SQL 模板、参数分布、调用次数、锁等待、CPU/IO 和 p95/p99 延迟；在接近生产数据量的环境用 EXPLAIN ANALYZE 或压测确认瓶颈，再一次只改一个变量。索引上线后要观察写入耗时、Buffer Pool 命中率、锁等待和慢查询回归，而不是只看单次查询变快。

拓展：

“先优化再加索引还是先加索引？”——先定位、看执行计划和数据分布，再决定索引、改写还是拆分；索引过多会拖慢写入、占用缓存，并可能让优化器选错路径。
常见优化点：N+1 改批量查、深分页改游标、避免隐式转换和函数、缩小返回列、拆出大字段、控制事务批次；缓存只能解决部分读热点，不能替代错误 SQL 的修复。
使用参数化 SQL 让计划和缓存更稳定，更新统计信息并关注数据倾斜；优化必须有前后对比（相同参数分布、行数、并发、p95/p99），并保留回滚方案。
架构层（缓存、读写分离、分库分表）会引入一致性和运维复杂度，只有单机索引/SQL 优化无法满足容量或延迟目标时再上。

往项目引 ⭐："我项目上线前会跑慢查询巡检，超过 200ms 的 SQL 都要 EXPLAIN 优化掉，这是我们的性能红线。有条统计 SQL 从 3 秒优化到 200ms，就是靠加覆盖索引 + 改写避免了临时表。"

12. 🔴 limit 深分页（如 limit 100000,10）为什么慢？怎么优化？

标准答：limit 100000,10 会先扫描并回表前 100010 行、再丢弃前 10 万行，越往后越慢（白扫了 10 万行）。优化两种：

覆盖索引 + 子查询：先用覆盖索引定位第 10 万行的主键，再 join 回表取那 10 行——select * from t join (select id from t order by id limit 100000,10) x on t.id=x.id。
游标/记录上次 id：where id > 上次最后一个id limit 10，直接定位、不扫前面的。

深分页慢的根本是 offset 越大，存储引擎需要遍历和丢弃的索引记录越多；如果排序字段不是主键，还可能先 filesort，再回表。更稳的“seek pagination”会把上页最后一条的排序键作为游标，例如 (created_at, id) 联合索引下使用 where (created_at, id) < (?, ?) order by created_at desc, id desc limit 20，用唯一的 id 解决同一时间戳的并列。

拓展：

游标翻页通常最快，但只能按游标向前/向后翻，不能精确跳页；要支持跳页可用覆盖索引子查询或预计算页锚点。游标必须固定排序和方向，否则会重复/漏数据。
子查询方案要确保内外层使用相同 order by、过滤条件和稳定的唯一键；在高并发写入下，普通 offset 也可能因数据插入导致页漂移。
导出任务可按主键/时间分批并记录断点，避免单个大事务；后台接口还可以限制最大页数、设置超时和取消机制。

往项目引 ⭐："我项目的数据导出和无限滚动列表都用 where id > ? 游标翻页，避免深分页拖垮数据库；需要跳页的后台列表才用子查询方案。"

13. 🟢 count(*)、count(1)、count(字段) 有什么区别？

标准答：

count(*) 统计符合 where 条件的行数，包括所有列为 NULL 的行；count(1) 对每行计算常量 1，结果通常相同，现代优化器下性能基本一致。推荐写 count(*)，语义最清晰。
count(字段) 只统计该字段非 NULL 的行，表达式还可能带来额外计算；是否走索引取决于过滤条件、字段索引和成本估算，并不是“写了 count 就一定全表扫”。
InnoDB 要遵守 MVCC，不保存一个对所有事务都正确的总行数，因此无条件 count(*) 也可能扫描最小的二级索引或聚簇索引；大表要关注扫描页数和并发影响。

拓展：

"为什么 InnoDB 的 count(*) 慢、MyISAM 快？"——MyISAM 把总行数存了下来，直接读；InnoDB 因为有 MVCC、不同事务看到的行数不同，必须实时统计。
"大表 count 慢怎么办？"——维护一个计数表（增删时同步加减）、用 Redis 计数、或用近似值（EXPLAIN 的 rows、information_schema）。
count(*) 与 count(1) 的微小差异通常不值得优化，真正的成本来自过滤、回表和扫描范围；不要为了“快一点”改成 count(1) 却忽略索引。
分页接口可采用“是否有下一页”的 limit pageSize+1 代替精确总数；需要精确数时，可维护汇总表/异步计数并说明一致性延迟。
计数缓存要处理增删失败、重放和修正任务；Redis 计数不是数据库事实来源，不能在事务失败时留下永久错误。

往项目引 ⭐："我项目分页要返回总数，大表 count 很慢，后来对总数做了缓存（首次查后缓存一段时间）、或用 ES 聚合统计，列表接口快了很多。"

14. 🟢 char 和 varchar 怎么选？

标准答：两者都存字符串，但存储布局和更新代价不同，不能只用“固定/不固定”一句话决定：

CHAR(n) 是定长类型，值不足时按字符集规则补齐，读取时通常去掉尾部空格。长度短且稳定时定位简单，适合国家码、状态码、固定格式的哈希/标识（但要确认长度和字符集）。
VARCHAR(n) 保存实际长度并附带 1 或 2 个长度字节，能节省大量空间，适合昵称、地址、标题等长度波动的字段。n 是字符数上限，不是字节数；utf8mb4 下一个字符最多占 4 字节，整行还受行大小限制。

CREATE TABLE user_profile (
  country_code CHAR(2) NOT NULL,
  status       CHAR(1) NOT NULL,
  nickname     VARCHAR(64) NOT NULL,
  address      VARCHAR(255),
  KEY idx_nickname (nickname)
) CHARACTER SET utf8mb4;

VARCHAR 变长字段更新后变长，可能造成页内移动或页分裂；高频更新且长度变化大的字段要评估行迁移、碎片和二级索引成本。CHAR 也不是天然更快，现代 InnoDB 的缓存和页布局常让差异很小。长文本优先考虑 TEXT 或拆表，并明确索引前缀、排序规则和默认值约束；最终以真实数据分布和查询计划验证。

拓展：

"varchar(255) 占 255 字节吗？"——不，最多存 255 个字符，实际按内容长度存。255 这个值是因为长度前缀在 255 内只需 1 字节。
大文本用 text，但 text 不能设默认值、不便索引，能拆就拆。
行迁移会影响性能，频繁更新的变长字段要注意。

往项目引 ⭐："我项目订单状态、性别这种固定值用 char，用户昵称、收货地址用 varchar，长文本评论用 text 单独存——按字段特点选类型，不是一律 varchar(255)。"

15. 🔴 一张大表要加字段/加索引，线上怎么安全操作？

标准答：先确认变更是否支持在线算法、预计扫描量和锁等待，再决定执行方式。直接对千万级表 ALTER TABLE 可能长时间占用 MDL（元数据锁），阻塞读写；即使使用 online DDL，也可能在开始/结束阶段等待已有长事务。

常见做法：

原生 Online DDL：MySQL 5.6+ 部分加索引/加列支持 ALGORITHM=INPLACE 或 MySQL 8 的 ALGORITHM=INSTANT，尽量减少拷贝；先查版本和官方支持矩阵，不能假设所有操作都在线。
影子表工具：gh-ost 或 pt-online-schema-change 创建新表、分批回填、同步增量，最后短暂切换表名。要评估触发器、外键、复制拓扑和主库额外 IO。
发布治理：低峰执行、设置锁等待超时、先在同规模副本演练；变更前后监控 QPS、延迟、复制 lag、磁盘空间和错误率，准备回滚/停止方案。

加索引并不意味着“完全无锁”：DDL 需要短暂元数据锁，若有长事务持有表对象，会在切换点排队。大表改字段类型、重建聚簇索引和删除列尤其谨慎；不要在业务高峰直接执行未经演练的 DDL。

拓展：

加索引相对安全（Online DDL 多支持），加字段、改字段类型更危险。
gh-ost 不依赖触发器、对主库压力更小，是现在主流。
要监控复制延迟，避免影响从库。

往项目引 ⭐："我项目给千万级订单表加字段，用的 gh-ost 在线变更，建影子表 + 追增量 + 切换，全程不锁表、业务无感知，避免了直接 alter 锁表的事故。"

四、架构进阶

16. 🔴 什么时候要分库分表？怎么分？带来什么问题？

标准答：分库分表是容量和并发治理手段，不是看到“千万行”就必须做。先通过索引、SQL、缓存、读写分离和归档确认单库是否真的达到 CPU、IO、连接数、存储或备份恢复瓶颈，再按查询维度拆分。

垂直拆分：按业务边界拆库（订单、商品、账户），或把大字段/低频字段拆到扩展表，降低单表行宽和锁竞争。
水平分表：同一业务表按分片键拆成多张。取模分布均匀但扩容需迁移；范围分片易按时间归档但可能产生热点；一致性哈希可减小扩容迁移但实现复杂。
水平分库：把分片放到多个实例，进一步扩展写入和连接，但带来跨库网络和运维成本。

分片键要与最主要的查询条件一致，例如按 user_id 让用户订单落到单片；跨分片统计、排序、分页会变成多片并行查询再归并。常用 ShardingSphere 等中间件，但分片路由、分布式 id、事务和治理仍由业务负责。

必须提前设计全局唯一 ID、跨分片唯一约束、事务补偿、分页排序、数据迁移和扩容方案。能不分尽量不分；一旦分片，运维和故障排查复杂度会长期存在。

拓展：

带来的问题——跨分片查询、分布式 id（雪花算法）、跨库事务、分页排序聚合复杂、扩容数据迁移。
"能不分尽量不分"：先优化索引、加缓存、读写分离，实在不行才分。
分片键选择很关键，要让查询尽量落在单片。

往项目引 ⭐："我项目订单表按用户 id 取模分了 16 张表（用户维度查询能落单片），分布式 id 用雪花算法，跨分片的统计报表走 ES 而不是直接查 MySQL。没有为了分而分，是真到了瓶颈才拆。"

17. 🟢 主从复制的原理？读写分离怎么做？主从延迟怎么办？

标准答：经典异步复制链路是：主库事务提交并写入 binlog → 从库 IO/receiver 线程拉取并写入 relay log → SQL/applier 线程按位点重放 relay log。新版本可用并行复制和 GTID 简化位点管理，但本质仍是“日志传输 + 重放”。

读写分离通常在代理、中间件或数据源路由层实现：写、事务中的读、刚写后的强一致读走主库；可接受延迟的列表/报表读走从库。路由不能只按 SQL 字符串判断，还要识别事务边界、存储过程和连接上下文。

主从延迟的原因包括大事务、从库回放能力不足、网络抖动和从库 IO 饱和。治理方式：

关键读强制主库，或把主库 binlog 位点/GTID 传给读请求，等待从库追平后再读；
拆小事务、优化慢 SQL，开启并行复制并提升从库 IO/CPU；
监控 seconds_behind、GTID 位点和业务读延迟，超过阈值自动摘除落后的从库。

主从复制提升读扩展和容灾能力，但不解决单库容量、跨库事务或写瓶颈；故障切换还要处理未同步事务、连接重试和幂等。

拓展：

"主从延迟怎么办？"——刚写完立刻读可能读到旧数据。方案：关键场景强制读主库、等待同步位点、用半同步复制（主库等至少一个从库确认）。
延迟原因：从库单线程重放跟不上（可开并行复制）、大事务、网络。
主从只解决读扩展和高可用，不解决单库容量。

往项目引 ⭐："我项目读多写少，用读写分离把查询压到从库；但'下单后立刻查订单详情'这种我强制走主库，避免主从延迟读到空。这是踩过坑才加的策略。"

18. 🟢 redo log、undo log、binlog 的区别？

标准答：三类日志处在不同层，记录方向和用途也不同：

日志	所属层	记录内容	主要用途	生命周期/写法
undo log	InnoDB	修改前的逻辑版本	回滚、MVCC 快照读	随事务产生，确认无快照需要后 purge
redo log	InnoDB	数据页的物理修改	崩溃恢复、WAL 持久性	固定大小循环写
binlog	MySQL Server	事务逻辑变更（row/statement）	主从复制、增量备份	按文件追加写

一次事务更新通常先生成 undo 和 redo，提交时将 binlog 与 redo 的 prepare/commit 状态协调好；宕机恢复时按 redo 重放已提交页，复制/备份则消费 binlog。redo 不等于“完整 SQL”，不能直接拿来做主从；binlog 也不负责 InnoDB 页级崩溃恢复。

redo 与 binlog 的两阶段提交大致是：写 redo prepare → 写 binlog 并刷盘 → 写 redo commit。两者顺序不一致会导致“本地已恢复但从库没这笔”或“binlog 有但本地未提交”等不一致。刷盘参数（如 innodb_flush_log_at_trx_commit、sync_binlog）决定性能和故障时的丢失窗口，生产要按 RPO 目标配置。

拓展：

"redo 和 binlog 怎么保证一致？"——两阶段提交：写 redo（prepare）→ 写 binlog → 提交 redo（commit），防止主库崩溃后主从数据不一致。
redo 是循环写、固定大小；binlog 是追加写。
没有 redo 就只能每次提交刷盘，性能差。

往项目引 ⭐："我项目数据同步用的是 canal 监听 binlog，把 MySQL 变更实时同步到 ES 和缓存。所以我对 binlog 的逻辑日志作用印象很深，也理解了为什么它能做增量同步。"

19. 🟢 drop、delete、truncate 的区别？

标准答：先看影响范围和事务语义：

语句	类型/范围	`WHERE`	事务与日志	自增/结构
`DELETE`	DML，逐行删除	支持	InnoDB 可回滚，产生 undo/binlog	保留表结构，通常不重置自增
`TRUNCATE`	DDL，清空整表	不支持	触发隐式提交，不能按行回滚；日志量较小	保留结构，通常重置自增
`DROP TABLE`	DDL，删除对象	不支持	隐式提交，表级对象消失	数据、索引、权限依赖一起移除

DELETE 适合按条件、分批、可审计地清理；大批量删除会产生大量 undo、锁和 binlog，可按主键范围分批并控制事务大小。TRUNCATE 速度快但需要更强的元数据权限和锁，且受外键、触发器等约束影响；不同 MySQL/存储引擎对回滚和自增细节应以版本文档为准。DROP 是不可逆的结构性操作，线上必须备份、审批并确认依赖。

不要把“truncate 一定不可回滚”理解成所有数据库/版本的绝对规律，但在 MySQL 线上应按高危 DDL 处理。执行前先确认目标库、表名和备份恢复路径，避免把测试环境脚本带到生产。

拓展：

truncate 不触发触发器、不逐行记日志，所以比 delete 快很多。
delete 大量数据后表空间不会自动回收，要 optimize table 或重建。
线上删数据一律用 delete 带条件 + 先备份，绝不轻易 truncate/drop。

往项目引 ⭐："我项目清理测试环境数据用 truncate 快速重置；线上删数据一律 delete 带条件并先备份，操作前还要 DBA 复核——truncate/drop 这种不可回滚的在线上是高危操作。"

20. 🔴 为什么主键推荐用自增 id？用 UUID 有什么问题？分布式下怎么办？

标准答：InnoDB 主键是聚簇索引，主键选择会影响每个二级索引的叶子宽度和数据页写入方式。

自增整数单调递增，新记录通常追加到 B+ 树右侧，页分裂和随机 IO 少；值短，二级索引占用也小。缺点是容易暴露规模、跨库合并时会冲突，且单点发号可能成为瓶颈。
随机 UUID（尤其 36 字符文本）插入位置分散，容易造成页分裂、缓存命中下降；长度大还会复制到每个二级索引。若确实需要 UUID，优先考虑紧凑 BINARY(16)、时间有序 UUID/ULID，并评估索引顺序。
分布式场景常用 Snowflake、号段、数据库 segment 或 Redis 发号。Snowflake 典型 64 位结构是时间戳 + 机器/数据中心标识 + 序列号，趋势递增且本地生成，不需要每次访问中心服务。

Snowflake 要处理时钟回拨（等待、切换 worker 或拒绝生成）、序列号耗尽、机器号分配和重启持久化；“趋势递增”不等于严格全局有序。分片后还要决定是否需要可猜测性、防止 ID 泄漏，并为迁移/重试保证发号幂等。无论选哪种方案，都应保持主键短、稳定、不频繁修改。

拓展：

雪花算法 64 位：时间戳 + 机器 id + 序列号。
"雪花算法的坑？"——依赖机器时钟，时钟回拨会生成重复 id，要做回拨处理（等待或抛异常）。
其他方案：号段模式（美团 Leaf）、Redis incr。

往项目引 ⭐："我项目单库时主键用自增；分库分表后改雪花算法 id，既全局唯一又趋势递增、对聚簇索引友好，避免了 UUID 那种随机插入导致的页分裂和性能问题。"

你能答到第几层？

三段都能答、还能往项目引：MySQL 这块你稳了，冲 18k+ 没问题。
标准答 + 拓展能成体系答：知识扎实，差把它接到项目说出来。
标准答都磕巴：MySQL 有主线（索引 → 事务锁 → 优化 → 架构），跟着系统学一遍就通。

这是面试专题的「MySQL 篇」，网站上还有并发、Redis、Spring、微服务、项目场景等系统整理。 🌐 更多真实面试专题与资料：smallredtech.com 💬 想系统学 / 简历与辅导咨询，加微信：Ahongbb666（备注「面试题」）

Java 真实面试专题

MySQL 面试题精选

Java 后端真实面试专题 · MySQL 篇

一、索引

1. 🟢 为什么要用索引？InnoDB 为什么用 B+ 树而不是别的？

2. 🟢 聚簇索引和二级索引的区别？什么是回表和覆盖索引？

3. 🟢 联合索引的最左前缀原则是什么？

4. 🟢 索引失效有哪些常见情况？

5. 🟢 EXPLAIN 怎么看？重点看哪几个字段？

二、事务与锁

6. 🟢 事务的 ACID 分别靠什么实现？

7. 🟢 事务隔离级别有哪些？分别解决什么问题？MySQL 默认是哪个？

8. 🟢 MVCC 是怎么实现的？

9. 🔴 MySQL 有哪些锁？什么是间隙锁？行锁会升级成表锁吗？

10. 🔴 出现死锁怎么排查、怎么避免？

三、性能优化

11. 🟢 一条 SQL 调优的完整思路？

12. 🔴 limit 深分页（如 limit 100000,10）为什么慢？怎么优化？

13. 🟢 count(*)、count(1)、count(字段) 有什么区别？

14. 🟢 char 和 varchar 怎么选？

15. 🔴 一张大表要加字段/加索引，线上怎么安全操作？

四、架构进阶

16. 🔴 什么时候要分库分表？怎么分？带来什么问题？

17. 🟢 主从复制的原理？读写分离怎么做？主从延迟怎么办？

18. 🟢 redo log、undo log、binlog 的区别？

19. 🟢 drop、delete、truncate 的区别？

20. 🔴 为什么主键推荐用自增 id？用 UUID 有什么问题？分布式下怎么办？

你能答到第几层？