运维工程师面试题精选：MySQL 原理与日常运维

MySQL 是运维面试中权重最高的数据库题库：面试官既要确认候选人懂原理（索引用 B+ 树、复制为何延迟），也要确认其能干活（慢查询定位、连接打满救急、大表加字段不锁表）。本文精选 12 道高频题，每题先给结论再列要点。

本文题目一览

• InnoDB 和 MyISAM 有什么区别？怎么选
• 为什么 MySQL 索引用 B+ 树
• 最左前缀与覆盖索引是什么？索引何时失效
• 慢查询怎么定位？EXPLAIN 看哪些字段
• 主从复制原理是什么？binlog 有哪几种格式
• 主从延迟有哪些成因？怎么处理
• 读写分离有哪些坑？读到旧数据怎么解决
• 备份怎么做？mysqldump 与物理备份怎么取舍
• 隔离级别与 MVCC 是怎么回事？锁与死锁怎么排查
• 连接数打满（Too many connections）如何应急
• 大表 DDL 有什么风险？pt-osc 与 gh-ost 的思路
• 字符集和时区相关的常见事故有哪些

一、存储引擎与索引

1. InnoDB 和 MyISAM 有什么区别？生产环境怎么选？

结论：现代生产环境几乎一律选 InnoDB。区别主要看四点：事务、锁粒度、崩溃恢复和索引组织方式。

• 事务与恢复：InnoDB 支持事务、外键，靠 redo log 崩溃后可自动恢复；MyISAM 都不支持，崩溃后表易损坏需人工修复。
• 锁粒度：InnoDB 是行锁、并发写友好；MyISAM 是表锁，一次写会阻塞整表读写。
• 索引结构：InnoDB 是聚簇索引，数据与主键索引在一起，二级索引叶子存主键值；MyISAM 索引与数据分离，叶子存行地址。

2. 为什么 MySQL 索引用 B+ 树？B+ 树有什么特点？

结论：磁盘 IO 是数据库瓶颈，B+ 树够"矮胖"且叶子有序相连，兼顾等值查询的低 IO 次数和范围查询的高效扫描。

• 矮：非叶子节点只存键不存数据，一个 16 KB 的页能放上千个键，千万级数据通常 3 到 4 层，查一行只需 3 到 4 次 IO。
• 叶子链表：数据全在叶子层且双向链表相连，范围查询与排序可顺序扫，不用回跳。
• 对比：B 树每节点都存数据，扇出更小、树更高；哈希索引等值快，但不支持范围、排序和最左前缀。追问常考：主键推荐自增整型，避免页分裂与碎片，UUID 做主键两点都吃亏。

3. 什么是最左前缀原则和覆盖索引？常见索引失效场景有哪些？

结论：联合索引按列顺序排序，只能从最左列开始连续使用；查询字段全在索引里就是覆盖索引，不必回表。失效场景则是排障中最常遇到的问题。

• 最左前缀：索引 (a, b, c) 能命中 a、a+b、a+b+c，命中不了单独的 b 或 c；a 用范围查询时，后面的列也无法再精确匹配。
• 覆盖索引：查询字段与条件都在索引中，Extra 显示 Using index，直接出结果，省去回表 IO。
• 失效一：对索引列做函数或运算，如 where year(create_time)=2024；8.0 的函数索引是补救手段。
• 失效二：隐式类型转换，如字符串字段传数字条件，会触发全表扫描，是慢查询高发原因。
• 失效三：like 以 % 开头、or 两侧有一侧无索引；优化器评估走索引不划算时也会主动放弃，属正常。

-- 覆盖索引示例：查询字段全在联合索引 (user_id, status, create_time) 内，无需回表
EXPLAIN SELECT user_id, status FROM orders
WHERE user_id = 1001 AND status = 1;
-- 预期：key 命中该联合索引，Extra 显示 Using index

4. 慢查询怎么定位？EXPLAIN 主要看哪些字段？

结论：先用慢查询日志圈出问题 SQL 并按影响汇总，再用 EXPLAIN 看执行计划，重点盯 type、key、rows、Extra 四项。

• 日志侧：开启 slow_query_log（long_query_time 生产常设 1 秒），用 mysqldumpslow 或 pt-query-digest 按总耗时聚合，先治影响最大的一类 SQL 而非单条。
• type：从好到差是 const、eq_ref、ref、range、index、ALL；大表出现 ALL 基本就是问题所在，至少优化到 range 以上。
• key 与 rows：key 为 NULL 说明没走索引；rows 是预估扫描行数，与实际返回行数差距越大越值得优化。
• Extra：Using filesort、Using temporary 是常见优化信号；Using index 是好事；Using index condition 是索引下推。
• 收尾：优化后复查计划与耗时，并把慢 SQL 来源反馈给开发共同治理。

# 慢查询日志聚合分析：按总耗时排序看前 10 类
mysqldumpslow -s t -t 10 /data/mysql/slow.log
pt-query-digest /data/mysql/slow.log

二、复制与架构

5. MySQL 主从复制的原理是什么？binlog 有哪几种格式？

结论：复制本质是日志重放：主库把变更写进 binlog，从库拉走写入 relay log 再重放；binlog 格式决定记"语句"还是"行变化"，是复制一致性的关键。

• 流程：主库推送 binlog；从库 IO 线程接收写 relay log；SQL 线程（多为并行 worker）重放追平。
• statement：记原始 SQL，日志量小，但 now()、uuid() 等不确定函数会在从库执行出不同结果，有一致性风险。
• row：记每行变更前后数据，结果确定、最安全，是当前默认；代价是批量更新时日志量大。mixed 是自动切换的折中，已较少新选。
• 补充：异步复制在主库宕机时可能丢未同步事务，半同步可缓解；GTID 让切换时的位点管理大幅简化。

6. 主从延迟有哪些成因？怎么处理？

结论：延迟的本质是从库重放跟不上主库写入。成因分三类：主库写入太猛、从库重放太慢、从库自身压力大。

• 大事务：主库一个删 100 万行的事务瞬间完成，从库要整段重放完位点才推进，延迟突然飙升；治理靠业务侧拆分大事务、分片执行。
• 重放慢：老版本 SQL 线程单线程是经典瓶颈，开启并行复制可缓解；从库表无主键时，row 格式下一次更新可能变全表扫描，是隐蔽元凶，建表应强制主键。
• 监控口径：Seconds_Behind_Master 不完全可信（网络中断时误报），生产常用 pt-heartbeat 测真实延迟。
• 处理原则：先分清偶发尖峰还是持续积压；持续积压要给从库减负或提升配置，硬扛只会越拖越久。

7. 读写分离有哪些坑？主从延迟导致读到旧数据怎么解决？

结论：最大的坑是"写后读一致性"：刚提交的数据从库读不到，表现为刚下单查不到订单、刚改完密码登录失败。

• 强一致路径读主：下单后查订单、支付后查状态、改完资料立即回显等关键链路强制走主库，最常用也最稳妥。
• 会话粘性与等位点：写后一段时间内该会话的读走主库，用时间窗掩盖延迟；或记录 GTID/位点，读从库前先等其重放到该位点。
• 其他坑：事务内查询必须走主库；从库延迟过大要自动摘除；分离中间件自身要做高可用防单点。
• 落点：先承认延迟客观存在，再按业务重要程度分级处理，别给"延迟调到 0"这种不现实答案。

三、备份、事务与锁

8. 备份怎么做？mysqldump 和物理备份怎么取舍？备份如何验证？

结论：小库用 mysqldump 逻辑备份足够，大库必须上物理备份并配合 binlog 做时间点恢复；备份价值只取决于恢复成功率，定期恢复演练是必答项。

• mysqldump：导出 SQL 文本，可读可编辑、跨版本迁移方便；缺点是备份与恢复都慢，大库恢复以小时计。InnoDB 要加 --single-transaction 获得一致性快照且不锁表。
• 物理备份：如 Percona XtraBackup 支持热备与增量，是 TB 级库的主流选择；缺点是不能跨大版本直接恢复、文件不可读。
• 时间点恢复：全备加连续归档的 binlog 可恢复到任意时间点，是应对误删的最后防线，binlog 保留周期要与全备对齐。
• 验证：定期把备份恢复到隔离环境校验行数与核心表，只备不验等于没有备份；周期、异地保存与保留时长要成制度并演练。

# InnoDB 逻辑备份：一致性快照，不锁表
mysqldump --single-transaction --routines --triggers \
  -h db-host -u backup -p appdb > appdb_full.sql

9. 隔离级别与 MVCC 是怎么回事？行锁、间隙锁与死锁怎么排查？

结论：隔离级别是"一致性换并发"的四档取舍，MySQL 默认可重复读（RR）；MVCC 让普通查询不加锁读一致的历史版本。InnoDB 的锁都建在索引之上，没索引就没有行锁。

• 隔离级别：读未提交、读已提交（RC，防脏读）、可重复读（RR，防不可重复读）、串行化；要能把脏读、不可重复读、幻读与级别一一对应。
• MVCC：靠 undo log 版本链加 ReadView 判断可见性；普通 select 是快照读不加锁，update/delete 等是当前读加锁。RC 与 RR 的差别在 ReadView 生成时机：RC 每次快照读生成，RR 首次生成后复用。长事务会占住历史版本使 undo 无法回收，要重点盯。
• 行锁与间隙锁：行锁锁的是索引记录，更新条件无索引时退化为逐行扫描加锁、等同表锁；间隙锁锁记录间的空隙，与行锁组成临键锁，在 RR 下防幻读，代价是并发插入易互相等待。
• 死锁排查：看 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段，或查 performance_schema.data_lock_waits 看谁堵了谁；治理靠固定顺序访问、缩短事务、补齐索引，应用对死锁回滚（错误码 1213）做有限重试。

-- 查看最近一次死锁详情与当前锁等待关系
SHOW ENGINE INNODB STATUS;
SELECT * FROM performance_schema.data_lock_waits;

四、应急与变更

10. 数据库报 Too many connections，线上如何应急？

结论：先保命再查因：用预留连接登入止损，再从连接来源、慢查询、连接池找根因；盲目调大 max_connections 只是拖延，还可能压垮内存。

• 应急登入：MySQL 为 SUPER 用户预留了 1 个连接，DBA 用它登入看 processlist，分清是慢查询堆积、长事务未提交还是某应用连接泄漏。
• 止损：kill 掉异常的长事务与空闲连接（先确认业务归属）、暂停批量任务、必要时摘掉异常应用实例。
• 根因：应用连接池过大且多实例叠加、慢 SQL 占满连接、连接用完不释放；结合连接数历史曲线判断突发还是慢性爬升。wait_timeout 过长使空闲连接不释放，也是常见推手。
• 根治：统一连接池规范、接入连接治理中间件、上线连接数水位告警，别让这道题在同一系统上演两次。

11. 给大表加字段有什么风险？pt-osc 和 gh-ost 的思路是什么？

结论：传统 ALTER 要复制整表并长时间持锁，大表直接执行可能锁表数小时、打爆磁盘并拖垮主从；在线 DDL 工具的核心思路都是"建新表、同步增量、原子切换"。

• 直接 ALTER 的风险：部分操作支持 Online DDL 的 inplace 算法，但改字段类型等仍走整表拷贝；执行期 MDL 锁还会堵住后续所有访问，是经典的"一条 DDL 堵全站"模型。
• pt-osc：建同构新表，在原表上建触发器同步增量，拷完存量后交换表名；简单可靠，但触发器对原表写入有额外开销，外键处理麻烦。
• gh-ost：GitHub 出品，不用触发器，改为订阅 binlog 同步增量，对主库压力更小、可暂停限速，是目前更常用的方案。
• 执行纪律：先评估表大小与磁盘余量（至少容纳一份新表）、低峰执行、全程盯主从延迟、备好中止与回滚路径；小表才直接 ALTER，大表一律走工具。

12. 字符集和时区相关的常见事故有哪些？

结论：这类事故的特点是"上线不报错、数据悄悄出错"，以 utf8 与 utf8mb4 混淆、时区口径不一致最典型，根治办法是全链路统一。

• utf8 是阉割版：MySQL 的 utf8（utf8mb3）每字符最多 3 字节，存不下 emoji 与部分生僻字，插入会报错或被截断；新系统一律 utf8mb4，老库迁移要库、表、列、连接字符集整体改，漏一处就乱码。
• 排序规则连带影响：utf8mb4 下 general_ci、unicode_ci、bin 的比较规则不同，会影响唯一索引判重（如大小写、尾空格）与排序结果，迁移时不能只看字符集不看 collation。
• 时区问题：TIMESTAMP 按时区转换存储、DATETIME 只存字面值，再叠加服务器、驱动、系统三处时区不一致，典型表现是报表数据整体错位 8 小时。
• 防范：字符集与时区要写进建库规范与上线清单，交付时逐项核对实际配置；这类问题常在迁移、换驱动时集中爆发。

面试官视角

MySQL 题的区分度，在于候选人能否把原理落到生产动作上：

• 原理看锚点：讲 B+ 树能提页大小与树高、讲索引能提回表与覆盖索引、讲 MVCC 能说清 RC 与 RR 的 ReadView 差异——这些细节装不出来。
• 应急看顺序：连接数打满先保命还是先调参、主从延迟先分类还是先加机器，顺序感来自真实值班经验。
• 变更与备份看底线：谈大表 DDL 主动提低峰、盯延迟、备回滚路径的，可放心托付变更；答备份不提恢复验证的，灾备意识要打问号，建议作为硬性扣分点。

Mr丶点心

打赏 海报 链接