MySQL 面试题精选
Java 后端真实面试专题 · MySQL 篇
MySQL 是后端问得最细、追问最狠的一块。每题三段： ① 标准答（讲透：是什么+为什么+怎么做+原理）→ ② 拓展（成体系带出关联点和必追问的，答一题等于答一片）→ ③ 怎么接到你自己的项目。
年限标签：
🟢 3年内🔴 3年+
一、索引
1. 🟢 为什么要用索引？InnoDB 为什么用 B+ 树而不是别的？
标准答：索引是帮助 MySQL 高效查找的有序数据结构，没索引就得全表扫描。InnoDB 选 B+ 树是综合权衡的结果：
- 矮胖：B+ 树非叶子节点只存索引键、不存数据，一个 16KB 的页能放很多键，所以扇出大、树很矮（千万级数据一般只要 3
4 层），查一次只要 34 次磁盘 IO。 - 范围查询友好：所有数据都在叶子节点，且叶子之间用双向链表相连，范围查询、排序顺着链表走就行。
- 稳定：所有查询都要走到叶子，性能稳定。
拓展：面试官常对比着追问：
- "为什么不用 B 树？"——B 树每个节点都存数据，单页能放的键变少、树更高、IO 次数多；且范围查询要中序遍历回溯。
- "为什么不用红黑树/二叉树？"——二叉树太高（千万数据高度几十层），磁盘 IO 爆炸。
- "为什么不用 Hash 索引？"——Hash 只支持等值查询，不支持范围和排序；不过 Memory 引擎和自适应哈希用了它。
- 一次磁盘 IO ≈ 读一个页（16KB），所以"树高 = IO 次数"是核心，矮胖最省 IO。
往项目引 ⭐："我项目订单表上千万行，按用户 id + 时间查历史订单，我建了 (user_id, create_time) 联合索引，查询从全表扫的秒级降到毫秒级。理解 B+ 树让我知道为什么联合索引顺序和范围查询会影响命中。"
2. 🟢 聚簇索引和二级索引的区别？什么是回表和覆盖索引？
标准答：
- 聚簇索引：InnoDB 主键索引就是聚簇索引，它的叶子节点直接存整行数据。一张表只有一个。
- 二级索引（非聚簇）：叶子节点存的是主键值，不是整行。
- 回表：用二级索引查询时，先在二级索引找到主键，再拿主键回到聚簇索引查整行——多查一次树，就叫回表。
- 覆盖索引：如果要查的列正好都在二级索引里，不用回表，直接返回，
EXPLAIN的 Extra 显示Using index。
拓展：
- "怎么减少回表？"——把高频查询要返回的列加进联合索引形成覆盖索引。
- "为什么主键不要太长？"——所有二级索引都存主键值，主键越长，二级索引越占空间。
- "没有主键会怎样？"——InnoDB 会选唯一非空索引，再没有就生成隐藏的 rowid 做聚簇索引。
- 这也是为什么
select *不好——可能本来能覆盖索引，多查了列就被迫回表。
往项目引 ⭐："我项目订单列表只展示订单号、状态、金额几列，我把这几列加进联合索引做成覆盖索引，列表查询不用回表、快了一倍多。这就是'按查询设计索引'。"
3. 🟢 联合索引的最左前缀原则是什么？
标准答：联合索引 (a, b, c) 的本质是先按 a 排、a 相同按 b 排、再按 c 排。所以只有从最左列开始、连续使用才能走索引：a、a+b、a+b+c 能用上，单独 b、c、b+c 用不上。
拓展：
- "
where a=? and c=?能用上吗？"——只能用到 a，因为 b 断了 c 接不上。 - "范围查询会怎样？"——
a=? and b>? and c=?中，b 用了范围后 c 就失效（范围列后面的列用不了索引）。 - "
order by能用联合索引吗？"——能，但要满足最左前缀且方向一致，否则Using filesort。 - 建索引经验：等值、高频、区分度高的列放左边，范围列放右边。
往项目引 ⭐："我项目排查过一个慢查询，就是联合索引列顺序建反了——把范围条件放前面导致后面列用不上。调整顺序后命中索引。所以我现在建联合索引会先想清楚查询模式再定列顺序。"
4. 🟢 索引失效有哪些常见情况？
标准答：
- 列上用函数或运算：
where date(create_time)=?、where id+1=?。 - 隐式类型转换：字段是 varchar 却
where phone=138...（没加引号），等于对列做了转换。 - 违反最左前缀，或范围列后面的列。
like '%x'前导模糊（'x%'可以走）。or连接了非索引列。!=、not in、is not null有时不走。
拓展：
- 这题最好每条举例，面试官就信你真踩过。
- "怎么验证失效？"——
EXPLAIN看key是不是 null、type是不是 ALL。 - "
like '%x'一定不走吗？"——如果是覆盖索引，可能走index（全索引扫描，比全表扫强）。 - 失效本质是"无法利用索引的有序性"。
往项目引 ⭐："我项目线上出过事故：手机号字段是 varchar，代码传了 long 没加引号，隐式转换导致索引失效全表扫，CPU 飙高。加引号后恢复。所以我现在写 SQL 特别注意类型匹配和别在索引列上套函数。"
5. 🟢 EXPLAIN 怎么看？重点看哪几个字段？
标准答：
- type：访问类型，性能从好到差
const > eq_ref > ref > range > index > ALL。出现 ALL（全表扫描） 基本要优化，至少要到 range/ref。 - key：实际用上的索引，null 说明没走索引。
- rows：预估扫描行数，越小越好。
- Extra：出现
Using filesort（没用索引排序）、Using temporary（用了临时表）要警惕；出现Using index（覆盖索引）是好事。
拓展：
- "possible_keys 有但 key 是 null 说明什么？"——有可用索引但优化器没选（可能数据量小、或索引区分度低），可用
force index。 - "key_len 看什么？"——看联合索引用到了几列，判断最左前缀用了多少。
- "Using filesort 一定是磁盘排序吗？"——不一定，是"没用索引完成排序"，数据小也可能在内存排。
往项目引 ⭐："我项目规定：写完复杂查询上线前必须 EXPLAIN 一遍，确认没有 ALL 和 filesort。这是我们组的性能红线，靠它拦住了很多潜在的慢查询。"
二、事务与锁
6. 🟢 事务的 ACID 分别靠什么实现？
标准答：
- 原子性（A）：要么全成功要么全回滚——靠 undo log（记录修改前的值，失败时回滚）。
- 一致性（C）：是最终目的，由其他三个共同保证（数据从一个合法状态到另一个合法状态）。
- 隔离性（I）：事务间互不干扰——靠 锁 + MVCC。
- 持久性（D）：提交后不丢——靠 redo log（先写日志再刷盘，崩溃能恢复）。
拓展：
- 面试官会顺着每个特性往底层追，所以要能说出 undo/redo/锁/MVCC 对应关系。
- "redo 和 binlog 区别？"——redo 是 InnoDB 物理日志（崩溃恢复），binlog 是 Server 层逻辑日志（主从同步），两者靠两阶段提交保证一致。
- "为什么先写日志？"——WAL（Write-Ahead Logging），顺序写日志比随机写数据页快得多。
往项目引 ⭐："我项目下单要同时扣库存、建订单、加积分，用 @Transactional 包一个事务保证原子性，任何一步失败全回滚，绝不会出现'扣了库存没生成订单'的脏数据。"
7. 🟢 事务隔离级别有哪些？分别解决什么问题？MySQL 默认是哪个？
标准答：四个级别，逐级更严：
- 读未提交：能读到别人没提交的数据（脏读）。
- 读已提交（RC）：只能读已提交的，解决脏读；但同一事务两次读同一行可能不同（不可重复读）。
- 可重复读（RR，MySQL 默认）：同一事务多次读结果一致，解决不可重复读。
- 串行化：事务串行执行，解决幻读，性能最差。 脏读=读到未提交；不可重复读=两次读同一行值变了（别人 update）；幻读=两次同样条件查出的行数变了（别人 insert）。
拓展：
- "RR 解决幻读吗？"——InnoDB 在 RR 下用 MVCC（快照读）+ 间隙锁（当前读） 基本解决了幻读，这是它和标准 SQL 不同的地方。
- "为什么大厂有的用 RC 不用默认的 RR？"——RC 锁范围小、并发更高、间隙锁少、死锁概率低。
- 隔离级别越高越安全但并发越差。
往项目引 ⭐："我项目大多用默认的 RR 就够了；只有一个对账场景为了绝对一致，单独对那条查询用了 for update 当前读加锁。知道隔离级别让我清楚什么时候该手动加锁。"
8. 🟢 MVCC 是怎么实现的？
标准答：MVCC（多版本并发控制）让"读不加锁、读写不阻塞"，靠三样东西：
- 每行的隐藏字段：
DB_TRX_ID（最近改它的事务 id）、DB_ROLL_PTR（回滚指针，指向 undo log）。 - undo log 版本链：每次修改都把旧版本串成链表。
- ReadView（一致性视图）：记录生成视图时活跃的事务 id 列表，读取时顺着版本链找到对当前事务"可见"的那个版本。
拓展：
- "RC 和 RR 在 MVCC 上的区别？"——RC 每次查询都生成新的 ReadView（所以能读到别人新提交的），RR 在事务第一次查询时生成一次、之后复用（所以可重复读）。这是两者差异的根本。
- "快照读和当前读？"——普通 select 是快照读（走 MVCC）；
select for update、update、delete 是当前读（读最新并加锁）。 - MVCC 只在 RC 和 RR 下工作。
往项目引 ⭐："理解 MVCC 帮我排查过一个'读到旧数据'的诡异问题——一个长事务里一直读的是事务开始时的快照，没看到中途别的事务提交的更新。后来把长事务拆短解决，并且我明白了为什么 RR 下会这样。"
9. 🔴 MySQL 有哪些锁？什么是间隙锁？行锁会升级成表锁吗？
标准答：
- 按粒度：表锁（开销小、并发低）、行锁（开销大、并发高，InnoDB 支持）。
- 按模式：共享锁 S（读锁）、排他锁 X（写锁）。
- InnoDB 的行锁细分：记录锁（锁某行）、间隙锁（锁一个区间、防止插入）、临键锁（记录+间隙，RR 默认，防幻读）。
拓展：
- "行锁会退化成表锁吗？"——会！行锁是加在索引上的，如果更新条件没走索引，会锁住所有扫描到的行、相当于锁表。这是高频考点也是高频事故。
- "间隙锁解决什么？"——防幻读（锁住区间不让别人插入）。
- "什么时候有间隙锁？"——RR 隔离级别 + 当前读。
- 死锁常和加锁顺序、间隙锁有关。
往项目引 ⭐："我项目出过一次事故：一个 update 的 where 条件列没加索引，导致 InnoDB 锁了全表，其他请求全卡住。定位后给条件列加索引、行锁恢复正常。所以我牢记'行锁一定要走索引'。"
10. 🔴 出现死锁怎么排查、怎么避免？
标准答：排查——SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK 段，里面有两个事务互相等待的 SQL、持有和等待的锁，对照代码找加锁顺序。避免——统一多行/多表的加锁顺序（最常用）、缩短事务、降低隔离级别、让 update/delete 走索引避免锁范围扩大、select for update 控制锁的粒度。
拓展：
- 可开
innodb_print_all_deadlocks=ON把死锁记到错误日志，便于事后分析（线上死锁往往偶发）。 - InnoDB 会自动检测死锁并回滚其中一个事务（代价小的那个），所以业务要能处理"死锁回滚后重试"。
- 死锁四条件之一是"循环等待"，统一加锁顺序就是破坏它。
往项目引 ⭐："我项目转账两个账户互转曾死锁——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 *、避免函数、减少子查询）、避免大事务。 - 架构层：仍不行就加缓存、读写分离、分库分表。
拓展：
- "先优化再加索引还是先加索引？"——先定位再动手，别凭感觉加（索引多了拖慢写入、占空间）。
- 常见优化点：N+1 改批量查、深分页优化、大字段拆表。
- 优化要有"前后对比数据"才有说服力。
往项目引 ⭐："我项目上线前会跑慢查询巡检，超过 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 越大、浪费越多"。
- 业务上也可以限制最大翻页数（如只能翻到 100 页）。
往项目引 ⭐："我项目的数据导出和无限滚动列表都用 where id > ? 游标翻页，避免深分页拖垮数据库；需要跳页的后台列表才用子查询方案。"
13. 🟢 count(*)、count(1)、count(字段) 有什么区别？
标准答：
count(*)和count(1)：统计所有行（包括字段为 null 的），性能基本一样，InnoDB 会选最小的二级索引来扫（不需要回表）。count(字段)：不统计该字段为 null 的行，且如果字段没索引要全表扫。- 推荐用
count(*)，这是 SQL 标准语法、优化器有专门优化。
拓展：
- "为什么 InnoDB 的 count(*) 慢、MyISAM 快？"——MyISAM 把总行数存了下来，直接读；InnoDB 因为有 MVCC、不同事务看到的行数不同，必须实时统计。
- "大表 count 慢怎么办？"——维护一个计数表（增删时同步加减）、用 Redis 计数、或用近似值（
EXPLAIN的 rows、information_schema）。
往项目引 ⭐："我项目分页要返回总数，大表 count 很慢，后来对总数做了缓存（首次查后缓存一段时间）、或用 ES 聚合统计，列表接口快了很多。"
14. 🟢 char 和 varchar 怎么选？
标准答：
- char(n)：定长，不足补空格，读写快但浪费空间。适合长度固定且短的，如手机号、状态码、MD5、性别。
- varchar(n)：变长，按实际长度存（加 1~2 字节长度前缀），省空间，但更新变长可能产生行迁移。适合长度不定的，如昵称、地址、备注。
拓展：
- "varchar(255) 占 255 字节吗？"——不，最多存 255 个字符，实际按内容长度存。255 这个值是因为长度前缀在 255 内只需 1 字节。
- 大文本用 text，但 text 不能设默认值、不便索引，能拆就拆。
- 行迁移会影响性能，频繁更新的变长字段要注意。
往项目引 ⭐："我项目订单状态、性别这种固定值用 char，用户昵称、收货地址用 varchar，长文本评论用 text 单独存——按字段特点选类型，不是一律 varchar(255)。"
15. 🔴 一张大表要加字段/加索引，线上怎么安全操作？
标准答：直接 alter 大表早期会锁表很久、阻塞读写。方案：
- Online DDL（MySQL 5.6+）：部分操作支持 inplace（如加索引），不锁表或只短暂锁。
- 工具（pt-online-schema-change / gh-ost）：建一张影子表、同步原表数据和增量、最后秒级切换，全程几乎不锁表。
- 选业务低峰期操作，并提前评估。
拓展：
- 加索引相对安全（Online DDL 多支持），加字段、改字段类型更危险。
- gh-ost 不依赖触发器、对主库压力更小，是现在主流。
- 要监控复制延迟，避免影响从库。
往项目引 ⭐："我项目给千万级订单表加字段，用的 gh-ost 在线变更，建影子表 + 追增量 + 切换，全程不锁表、业务无感知，避免了直接 alter 锁表的事故。"
四、架构进阶
16. 🔴 什么时候要分库分表？怎么分？带来什么问题？
标准答：单表数据量过大（一般千万级以上、或单表超几十 GB）、单库写入/连接成为瓶颈时考虑。
- 垂直拆：按业务拆库（订单库、商品库）、把大字段/冷字段拆到副表。
- 水平拆：同一张表按规则拆成多张——取模（均匀但扩容麻烦）、范围（易扩容但热点不均）、一致性哈希。 常用中间件 ShardingSphere、MyCat。
拓展：
- 带来的问题——跨分片查询、分布式 id（雪花算法）、跨库事务、分页排序聚合复杂、扩容数据迁移。
- "能不分尽量不分"：先优化索引、加缓存、读写分离，实在不行才分。
- 分片键选择很关键，要让查询尽量落在单片。
往项目引 ⭐："我项目订单表按用户 id 取模分了 16 张表（用户维度查询能落单片），分布式 id 用雪花算法，跨分片的统计报表走 ES 而不是直接查 MySQL。没有为了分而分，是真到了瓶颈才拆。"
17. 🟢 主从复制的原理？读写分离怎么做？主从延迟怎么办？
标准答：
- 原理：主库把变更写入 binlog → 从库 IO 线程拉取 binlog 写到 relay log → 从库 SQL 线程重放 relay log，实现数据同步。
- 读写分离：写走主库、读走从库，用中间件或框架（ShardingSphere、
@DS注解）按 SQL 类型路由，分摊读压力。
拓展：
- "主从延迟怎么办？"——刚写完立刻读可能读到旧数据。方案：关键场景强制读主库、等待同步位点、用半同步复制（主库等至少一个从库确认）。
- 延迟原因：从库单线程重放跟不上（可开并行复制）、大事务、网络。
- 主从只解决读扩展和高可用，不解决单库容量。
往项目引 ⭐："我项目读多写少，用读写分离把查询压到从库；但'下单后立刻查订单详情'这种我强制走主库，避免主从延迟读到空。这是踩过坑才加的策略。"
18. 🟢 redo log、undo log、binlog 的区别？
标准答：
- undo log：InnoDB 的逻辑日志，记录"改之前"的数据，用于事务回滚和 MVCC 读旧版本。
- redo log：InnoDB 的物理日志，记录"在某页做了什么修改"，用于崩溃恢复保证持久性（WAL：先写 redo 再慢慢刷数据页）。
- binlog：Server 层的逻辑日志，记录所有变更（SQL 或行），用于主从复制和数据恢复。
拓展：
- "redo 和 binlog 怎么保证一致？"——两阶段提交：写 redo（prepare）→ 写 binlog → 提交 redo（commit），防止主库崩溃后主从数据不一致。
- redo 是循环写、固定大小；binlog 是追加写。
- 没有 redo 就只能每次提交刷盘，性能差。
往项目引 ⭐："我项目数据同步用的是 canal 监听 binlog，把 MySQL 变更实时同步到 ES 和缓存。所以我对 binlog 的逻辑日志作用印象很深，也理解了为什么它能做增量同步。"
19. 🟢 drop、delete、truncate 的区别？
标准答：
- delete：DML，逐行删除、可加 where、走事务可回滚、会记 undo/binlog，删大表慢。
- truncate：DDL，清空整表、不能加条件、不可回滚、重置自增值、很快（相当于 drop 后重建）。
- drop：DDL，删除表结构 + 数据 + 索引，表都没了。
拓展：
- truncate 不触发触发器、不逐行记日志，所以比 delete 快很多。
- delete 大量数据后表空间不会自动回收，要
optimize table或重建。 - 线上删数据一律用 delete 带条件 + 先备份，绝不轻易 truncate/drop。
往项目引 ⭐："我项目清理测试环境数据用 truncate 快速重置；线上删数据一律 delete 带条件并先备份，操作前还要 DBA 复核——truncate/drop 这种不可回滚的在线上是高危操作。"
20. 🔴 为什么主键推荐用自增 id？用 UUID 有什么问题？分布式下怎么办？
标准答：
- 自增 id 好处：顺序写入，新数据总是追加到 B+ 树最右边，页分裂少、聚簇索引紧凑、IO 顺序。
- UUID 的问题：无序，插入位置随机，导致频繁页分裂和随机 IO；而且字符串长（36 字节），所有二级索引都存主键、会放大存储。
- 分布式下：不能用单库自增，改用雪花算法（Snowflake）——趋势递增、有序、全局唯一、高性能、不依赖中间件。
拓展：
- 雪花算法 64 位：时间戳 + 机器 id + 序列号。
- "雪花算法的坑？"——依赖机器时钟，时钟回拨会生成重复 id，要做回拨处理（等待或抛异常）。
- 其他方案：号段模式（美团 Leaf）、Redis incr。
往项目引 ⭐："我项目单库时主键用自增；分库分表后改雪花算法 id，既全局唯一又趋势递增、对聚簇索引友好，避免了 UUID 那种随机插入导致的页分裂和性能问题。"
你能答到第几层？
- 三段都能答、还能往项目引：MySQL 这块你稳了，冲 18k+ 没问题。
- 标准答 + 拓展能成体系答：知识扎实，差把它接到项目说出来。
- 标准答都磕巴：MySQL 有主线（索引 → 事务锁 → 优化 → 架构），跟着系统学一遍就通。
这是面试专题的「MySQL 篇」，网站上还有并发、Redis、Spring、微服务、项目场景等系统整理。 🌐 更多真实面试专题与资料：smallredtech.com 💬 想系统学 / 简历与辅导咨询，加微信：Ahongbb666（备注「面试题」）