尧图网页设计 
简介这是一份面向后端开发求职者与在校学生的 MySQL 面试知识点总结文档围绕数据库原理与索引机制展开适合准备初中级后端岗位面试、需要系统梳理 MySQL 核心考点的读者。资源共 1 个 docx 文件压缩包约 40KB内容以问答形式组织便于按主题快速检索与背诵。文档覆盖关系型与非关系型数据库的区别、一条 SQL 语句从连接器到执行器的完整执行流程、索引的使用原因与哈希表、有序数组、搜索树三种底层数据结构并深入对比 MyISAM 与 InnoDB 的 B 树索引实现差异、InnoDB 选择 B 树的原因、普通索引与唯一索引的取舍、覆盖索引与索引下推优化以及模糊匹配、函数计算、隐式转换、OR 条件等导致索引失效的常见场景。此外还涉及 change buffer、redo log 与 binlog 区别、扫描行数判断等日志与优化知识点。目前已有 251 人学习适合作为面试前查漏补缺的速查笔记。1. 从一道“为什么唯一索引反而更慢”的面试题说起很多人背 MySQL 面试题时把“普通索引还是唯一索引”当成一道记忆题答案写“优先非唯一索引”就完事。但真到线上一个账单系统把唯一索引改成普通索引后写入吞吐翻了近一倍这时候你才会意识到面试官问的从来不是结论而是你懂不懂 change buffer 的适用边界。这份 MySQL 面试题资料把 37 个高频问题按索引、日志、数据、主从四条线串起来从“一条 SQL 怎么执行”讲到“crash 后怎么恢复”覆盖了后端面试里最容易被追问到底的存储引擎细节。它适合正在准备 Java 后端、DBA、运维岗面试的人也适合工作两三年、想把这些概念从“背过”变成“讲得清”的工程师。下面我不按题号念答案而是把它拆成能复现、能验证、能踩坑的实战路径。2. 一条 SQL 的执行链路从连接器到引擎层到底发生了什么2.1 先把 Server 层五个组件的位置摆正面试题里问“一条 MySQL 语句执行的步骤”标准答案是连接器、查询缓存、分析器、优化器、执行器。但光背顺序没用你得知道每个组件在什么情况下会成为瓶颈。连接器负责验证身份和权限连接建立后权限就固定了后续改权限不影响已存在的连接——这一点在排查“为什么改了权限还是报错”时特别关键。查询缓存从 MySQL 8.0 起已经默认关闭原因是任何更新都会让整张表的缓存失效命中率极低。分析器做词法和语法分析你写的selct拼错就是它报的。优化器决定走哪个索引、要不要 join reorder。执行器拿着执行计划去引擎层取数据取到一行就发给客户端一行。理解这条链路的意义在于当一条 SQL 变慢时你能判断是卡在连接数打满、优化器选错索引还是引擎层锁等待。常见做法是用SHOW PROCESSLIST看线程状态用EXPLAIN看优化器的选择再结合performance_schema定位执行器阶段。2.2 用 EXPLAIN 和 optimizer_trace 验证优化器的选择光看理论不够得动手验证。下面这段操作可以让你看到优化器为什么放弃某个索引-- 建一张测试表模拟订单场景 CREATE TABLE t_order ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_user (user_id), KEY idx_status (status) ) ENGINEInnoDB; -- 插入一批数据后看优化器选了哪个索引 EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1; -- 打开优化器追踪看它比较了哪些方案 SET optimizer_trace enabledon; SELECT * FROM t_order WHERE user_id 100 AND status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;EXPLAIN输出里的key列告诉你最终选了哪个索引rows列是估算扫描行数。OPTIMIZER_TRACE则把优化器比较候选索引、计算代价的全过程摊开给你看里面considered_execution_plans段落会列出每个索引的估算成本。参数上rows是估算值不是精确值它来自索引的区分度统计这也是面试题第 12 问“MySQL 如何判断一行扫描数”的落点。如果发现优化器选错了索引可以用FORCE INDEX强行纠正但更稳妥的做法是ANALYZE TABLE更新统计信息或者新建一个更合适的联合索引。2.3 索引失效的四种写法用执行计划当场验证面试题第 9 问列了索引失效的几种情况但很多人记不住“为什么”。核心原因只有一个索引 B 树里存的是字段的原始值任何让原始值无法直接比较的写法都会导致无法定位。左模糊like %xx不知道从哪个值开始找函数计算where id110改变了比较对象隐式转换where varchar_col123相当于对列做了类型转换OR连接非索引列则直接退化成全表扫描。-- 逐条验证对比 type 列从 ref 变成 ALL EXPLAIN SELECT * FROM t_order WHERE user_id LIKE %100; -- 左模糊全表 EXPLAIN SELECT * FROM t_order WHERE user_id 1 101; -- 函数计算全表 EXPLAIN SELECT * FROM t_order WHERE status 1; -- 隐式转换视字段类型 EXPLAIN SELECT * FROM t_order WHERE user_id 100 OR amount 50; -- OR 非索引列看type列ref或range说明走了索引ALL就是全表扫描。这里有个容易翻车的点——status是 TINYINT你传字符串1时 MySQL 会做隐式转换但转换方向取决于字段类型和值类型不一定每次都失效得用EXPLAIN实测而不是背结论。字符串加索引的四种方案完整索引、前缀索引、倒序存储、hash 字段也建议在这张表上各建一次对比EXPLAIN的key_len和实际扫描行数比看文字描述直观得多。3. redo log 与 binlog两阶段提交和 crash-safe 的验证方法3.1 为什么 redo log 能 crash-safe 而 binlog 不能这是面试里区分度最高的一类问题。redo log 是 InnoDB 引擎层的物理日志循环写记录的是“在哪个数据页做了什么修改”已经刷盘的数据会从 redo log 里抹掉所以它天然知道哪些数据还没落盘。binlog 是 Server 层的逻辑日志追加写保存全量变更但它没有“已刷盘”的标志位crash 后无法判断哪些数据已经持久化。这就是为什么恢复时必须靠 redo log 判断状态binlog 只负责补全和主从复制。两阶段提交把 redo log 的写入拆成 prepare 和 commit中间穿插写 binlog。恢复时的判断逻辑是redo log 处于 commit 状态说明 binlog 也写成功了直接恢复redo log 处于 prepare 状态就去查对应的 binlog 事务是否完整完整就提交不完整就回滚。这个逻辑保证了两个日志在崩溃恢复后逻辑一致。3.2 用 innodb_flush_log_at_trx_commit 控制刷盘时机面试题第 17 问提到 redo log 的写入经过 redo log buffer、OS buffer、redo log file 三层innodb_flush_log_at_trx_commit控制的就是这个过程中的刷盘行为参数值行为数据安全性性能0每秒写入 OS buffer 并刷盘崩溃可能丢 1 秒数据最高1每次提交都刷盘不丢数据最低2每次提交写 OS buffer每秒刷盘操作系统崩溃才丢数据中等-- 查看当前配置 SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE sync_binlog; -- 生产环境推荐组合双 1 保证不丢 SET GLOBAL innodb_flush_log_at_trx_commit 1; SET GLOBAL sync_binlog 1;sync_binlog1表示每次事务提交都刷 binlog 到磁盘和innodb_flush_log_at_trx_commit1配合才能保证 crash-safe。如果业务能容忍极端情况下丢少量数据把这两个值调大能明显提升写入吞吐但这是拿数据安全换性能得根据业务场景权衡。我一般会在压测环境把三个值都试一遍用sysbench跑写入对比 TPS 差异再决定线上配置。3.3 binlog 三种格式的选择与验证binlog 的 Statement、Row、Mixed 三种格式面试常问区别但实际选型要看场景。Statement 记录 SQL 语句日志量小但某些函数如NOW()在主从上执行结果可能不一致。Row 记录每行数据的变更前后镜像日志量大但绝对准确是当前主流选择。Mixed 让 MySQL 自己判断一般语句用 Statement可能不一致的用 Row。-- 查看当前 binlog 格式 SHOW VARIABLES LIKE binlog_format; -- 临时切换为 Row 格式需要重启或动态设置 SET GLOBAL binlog_format ROW; -- 查看 binlog 内容验证格式 SHOW BINARY LOGS; SHOW BINLOG EVENTS IN mysql-bin.000001 LIMIT 20;用mysqlbinlog工具解析 binlog 文件时Row 格式会显示### UPDATE和具体的列值变化Statement 格式则直接显示 SQL 文本。误删数据恢复时binlog_formatROW配合binlog_row_imageFULL是前提因为 flashback 类工具需要完整的前后镜像才能把 delete 反转成 insert。这一点在面试题第 28 问“误删数据怎么办”里是隐含条件很多人答了用 binlog 恢复却没说格式要求面试官一追问就露馅。4. 索引结构与存储引擎B 树、聚簇索引和覆盖索引的边界4.1 InnoDB 和 MyISAM 的索引实现差异InnoDB 的 B 树叶子节点直接存整行数据数据文件本身就是索引文件这叫聚簇索引。MyISAM 的叶子节点存的是数据记录的物理地址索引文件和数据文件分离所以 MyISAM 的主键索引和二级索引在结构上没有本质区别都是指向数据的指针。这个差异导致 InnoDB 的主键查询比 MyISAM 快少一次寻址但二级索引查询需要回表而 MyISAM 的二级索引也要回表。面试题第 7 问“InnoDB 为什么设计 B 树”的答案里两个关键点是范围查询和随机 IO。哈希索引 O(1) 但不支持范围B 树非叶子节点存数据导致范围查询时随机 IO 多B 树所有数据在叶子节点且用指针串联范围扫描时顺序 IO 效率高。以整数字段索引为例N 约等于 1200树高 4 就能存 1200 的三次方约 17 亿条记录根节点常驻内存10 亿行的表查一个值最多访问 3 次磁盘。4.2 覆盖索引和索引下推的实测对比覆盖索引是指查询需要的字段全部在索引里不需要回表。索引下推是 MySQL 5.6 引入的优化在索引遍历时就过滤掉不满足条件的记录减少回表次数。-- 建联合索引 ALTER TABLE t_order ADD KEY idx_user_status (user_id, status); -- 覆盖索引只查索引里有的字段Extra 显示 Using index EXPLAIN SELECT user_id, status FROM t_order WHERE user_id 100; -- 需要回表查了索引里没有的 amountExtra 无 Using index EXPLAIN SELECT user_id, status, amount FROM t_order WHERE user_id 100; -- 索引下推status 在索引里但 like 条件在索引遍历时过滤 EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status LIKE 1%;看Extra列出现Using index就是覆盖索引出现Using index condition就是索引下推生效。覆盖索引的代价是索引变宽占用更多空间更新时维护成本也高。我一般只在核心查询上建覆盖索引比如列表页只查 id 和几个状态字段详情页再回表查全量。索引下推则要注意它只对联合索引中后续字段的条件生效且条件必须是索引列上的。4.3 change buffer 的适用场景与唯一索引的取舍change buffer 是 InnoDB 在更新非唯一索引时如果数据页不在内存中先把更新缓存在 change buffer 里等下次查询该页时再 merge。唯一索引不能用 change buffer因为唯一性约束要求每次写入都必须读磁盘确认是否冲突。这就是面试题第 7 问“普通索引还是唯一索引”的底层原因。适用场景是写多读少比如账单、日志系统页面写完不会马上被访问change buffer 能攒一批再 merge减少随机 IO。反过来写完马上查的业务change buffer 还没来得及 merge 就被触发反而增加了维护成本。我一般会先看业务的读写比写多读少且能接受非唯一约束的优先普通索引如果业务逻辑强依赖唯一性那就老老实实用唯一索引别为了性能牺牲正确性。5. 避坑与排查那些面试答对但线上翻车的点5.1 现象改了权限但连接仍然报错原因连接器在建立连接时就固定了权限后续GRANT不影响已存在的连接。解决让应用重连或KILL掉旧连接新连接才会加载新权限。这个坑在面试里不会问但线上改权限后不生效时很多人第一反应是权限没改对其实是连接没刷新。5.2 现象EXPLAIN 显示走了索引但查询依然慢原因走了索引但回表次数庞大或者索引区分度太低。面试题第 25 问专门讲了这种情况。解决用EXPLAIN看rows估算值如果接近全表行数说明索引没起到过滤作用考虑建覆盖索引或者用FORCE INDEX换一个区分度更高的索引。我遇到过status字段只有三个值建了索引但优化器算下来还不如全表扫这种情况删掉索引反而更快。5.3 现象误删数据后用 binlog 恢复失败原因binlog_format不是 ROW或者binlog_row_image不是 FULL导致 flashback 工具拿不到完整的前后镜像。解决恢复前先确认SHOW VARIABLES LIKE binlog_format和binlog_row_image并且恢复要在一个临时实例上做验证无误后再导回主库。面试题第 28 问提到的 myflash 工具就依赖这两个参数。5.4 现象kill 命令执行了但 SQL 还在跑原因面试题第 30 问列了三种情况——kill 命令被堵、到位了没触发、触发了但执行需要时间。解决先用SHOW PROCESSLIST确认线程状态如果是Sending data说明在执行器阶段kill 后要等当前行处理完如果是Waiting for table metadata lock得先找到持锁的线程。我一般会先KILL QUERY尝试终止语句不行再KILL CONNECTION断连接。5.5 现象内存表重启后备库同步中断原因MEMORY 引擎的数据在重启后丢失备库重启会导致主备同步线程停止双 M 架构下还可能把主库的内存表数据删掉。解决生产环境避免使用 MEMORY 引擎面试题第 35 问明确不建议。如果确实需要内存级性能用 Redis 或把数据放到 InnoDB 的 buffer pool 里。6. 用两阶段提交状态判断恢复策略一个可以背下来的决策表面试题第 16 问“数据库 crash 后如何恢复未刷盘数据”是整份资料里最容易被追问的题因为它把 redo log、binlog、change buffer 三个概念串在一起。我把它的判断逻辑整理成一张决策表面试时按这个顺序讲基本不会乱redo log 状态binlog 状态恢复动作fsync 但未 commit未 fsync数据丢失change buffer 中的更新不回放fsync 未 commit已 fsync先从 binlog 恢复 redo log再从 redo log 恢复 change buffer已 commit已 fsync直接从 redo log 恢复这张表的底层逻辑是redo log 的 commit 状态代表事务是否真正提交binlog 的完整性决定 prepare 状态下的事务是提交还是回滚。恢复时先看 redo logcommit 的直接恢复prepare 的去查 binlogbinlog 完整就提交不完整就回滚。change buffer 的恢复依附于 redo log因为 change buffer 的变更也记录在 redo log 里。验证这个流程的方法是在测试环境模拟 crash用sysbench跑写入中途kill -9掉 mysqld 进程重启后对比数据行数和 binlog 解析结果。我一般会跑三组正常关闭、kill -9、断电模拟虚拟机强制关机观察不同innodb_flush_log_at_trx_commit配置下的数据丢失情况。跑过一遍之后这些参数和状态就不再是纸上的概念了。从那以后我每次面试前都会把这张表默写一遍并且在实际环境里模拟一次 crash 恢复因为背下来的答案和亲手验证过的理解在面试官追问时的表现完全不一样。希望帮到你。本文还有配套的精品资源点击获取 MySQL面试题实战：从索引优化到crash-safe恢复的37个高频问题
简介这是一份面向后端开发求职者与在校学生的 MySQL 面试知识点总结文档围绕数据库原理与索引机制展开适合准备初中级后端岗位面试、需要系统梳理 MySQL 核心考点的读者。资源共 1 个 docx 文件压缩包约 40KB内容以问答形式组织便于按主题快速检索与背诵。文档覆盖关系型与非关系型数据库的区别、一条 SQL 语句从连接器到执行器的完整执行流程、索引的使用原因与哈希表、有序数组、搜索树三种底层数据结构并深入对比 MyISAM 与 InnoDB 的 B 树索引实现差异、InnoDB 选择 B 树的原因、普通索引与唯一索引的取舍、覆盖索引与索引下推优化以及模糊匹配、函数计算、隐式转换、OR 条件等导致索引失效的常见场景。此外还涉及 change buffer、redo log 与 binlog 区别、扫描行数判断等日志与优化知识点。目前已有 251 人学习适合作为面试前查漏补缺的速查笔记。1. 从一道“为什么唯一索引反而更慢”的面试题说起很多人背 MySQL 面试题时把“普通索引还是唯一索引”当成一道记忆题答案写“优先非唯一索引”就完事。但真到线上一个账单系统把唯一索引改成普通索引后写入吞吐翻了近一倍这时候你才会意识到面试官问的从来不是结论而是你懂不懂 change buffer 的适用边界。这份 MySQL 面试题资料把 37 个高频问题按索引、日志、数据、主从四条线串起来从“一条 SQL 怎么执行”讲到“crash 后怎么恢复”覆盖了后端面试里最容易被追问到底的存储引擎细节。它适合正在准备 Java 后端、DBA、运维岗面试的人也适合工作两三年、想把这些概念从“背过”变成“讲得清”的工程师。下面我不按题号念答案而是把它拆成能复现、能验证、能踩坑的实战路径。2. 一条 SQL 的执行链路从连接器到引擎层到底发生了什么2.1 先把 Server 层五个组件的位置摆正面试题里问“一条 MySQL 语句执行的步骤”标准答案是连接器、查询缓存、分析器、优化器、执行器。但光背顺序没用你得知道每个组件在什么情况下会成为瓶颈。连接器负责验证身份和权限连接建立后权限就固定了后续改权限不影响已存在的连接——这一点在排查“为什么改了权限还是报错”时特别关键。查询缓存从 MySQL 8.0 起已经默认关闭原因是任何更新都会让整张表的缓存失效命中率极低。分析器做词法和语法分析你写的selct拼错就是它报的。优化器决定走哪个索引、要不要 join reorder。执行器拿着执行计划去引擎层取数据取到一行就发给客户端一行。理解这条链路的意义在于当一条 SQL 变慢时你能判断是卡在连接数打满、优化器选错索引还是引擎层锁等待。常见做法是用SHOW PROCESSLIST看线程状态用EXPLAIN看优化器的选择再结合performance_schema定位执行器阶段。2.2 用 EXPLAIN 和 optimizer_trace 验证优化器的选择光看理论不够得动手验证。下面这段操作可以让你看到优化器为什么放弃某个索引-- 建一张测试表模拟订单场景 CREATE TABLE t_order ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_user (user_id), KEY idx_status (status) ) ENGINEInnoDB; -- 插入一批数据后看优化器选了哪个索引 EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1; -- 打开优化器追踪看它比较了哪些方案 SET optimizer_trace enabledon; SELECT * FROM t_order WHERE user_id 100 AND status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;EXPLAIN输出里的key列告诉你最终选了哪个索引rows列是估算扫描行数。OPTIMIZER_TRACE则把优化器比较候选索引、计算代价的全过程摊开给你看里面considered_execution_plans段落会列出每个索引的估算成本。参数上rows是估算值不是精确值它来自索引的区分度统计这也是面试题第 12 问“MySQL 如何判断一行扫描数”的落点。如果发现优化器选错了索引可以用FORCE INDEX强行纠正但更稳妥的做法是ANALYZE TABLE更新统计信息或者新建一个更合适的联合索引。2.3 索引失效的四种写法用执行计划当场验证面试题第 9 问列了索引失效的几种情况但很多人记不住“为什么”。核心原因只有一个索引 B 树里存的是字段的原始值任何让原始值无法直接比较的写法都会导致无法定位。左模糊like %xx不知道从哪个值开始找函数计算where id110改变了比较对象隐式转换where varchar_col123相当于对列做了类型转换OR连接非索引列则直接退化成全表扫描。-- 逐条验证对比 type 列从 ref 变成 ALL EXPLAIN SELECT * FROM t_order WHERE user_id LIKE %100; -- 左模糊全表 EXPLAIN SELECT * FROM t_order WHERE user_id 1 101; -- 函数计算全表 EXPLAIN SELECT * FROM t_order WHERE status 1; -- 隐式转换视字段类型 EXPLAIN SELECT * FROM t_order WHERE user_id 100 OR amount 50; -- OR 非索引列看type列ref或range说明走了索引ALL就是全表扫描。这里有个容易翻车的点——status是 TINYINT你传字符串1时 MySQL 会做隐式转换但转换方向取决于字段类型和值类型不一定每次都失效得用EXPLAIN实测而不是背结论。字符串加索引的四种方案完整索引、前缀索引、倒序存储、hash 字段也建议在这张表上各建一次对比EXPLAIN的key_len和实际扫描行数比看文字描述直观得多。3. redo log 与 binlog两阶段提交和 crash-safe 的验证方法3.1 为什么 redo log 能 crash-safe 而 binlog 不能这是面试里区分度最高的一类问题。redo log 是 InnoDB 引擎层的物理日志循环写记录的是“在哪个数据页做了什么修改”已经刷盘的数据会从 redo log 里抹掉所以它天然知道哪些数据还没落盘。binlog 是 Server 层的逻辑日志追加写保存全量变更但它没有“已刷盘”的标志位crash 后无法判断哪些数据已经持久化。这就是为什么恢复时必须靠 redo log 判断状态binlog 只负责补全和主从复制。两阶段提交把 redo log 的写入拆成 prepare 和 commit中间穿插写 binlog。恢复时的判断逻辑是redo log 处于 commit 状态说明 binlog 也写成功了直接恢复redo log 处于 prepare 状态就去查对应的 binlog 事务是否完整完整就提交不完整就回滚。这个逻辑保证了两个日志在崩溃恢复后逻辑一致。3.2 用 innodb_flush_log_at_trx_commit 控制刷盘时机面试题第 17 问提到 redo log 的写入经过 redo log buffer、OS buffer、redo log file 三层innodb_flush_log_at_trx_commit控制的就是这个过程中的刷盘行为参数值行为数据安全性性能0每秒写入 OS buffer 并刷盘崩溃可能丢 1 秒数据最高1每次提交都刷盘不丢数据最低2每次提交写 OS buffer每秒刷盘操作系统崩溃才丢数据中等-- 查看当前配置 SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE sync_binlog; -- 生产环境推荐组合双 1 保证不丢 SET GLOBAL innodb_flush_log_at_trx_commit 1; SET GLOBAL sync_binlog 1;sync_binlog1表示每次事务提交都刷 binlog 到磁盘和innodb_flush_log_at_trx_commit1配合才能保证 crash-safe。如果业务能容忍极端情况下丢少量数据把这两个值调大能明显提升写入吞吐但这是拿数据安全换性能得根据业务场景权衡。我一般会在压测环境把三个值都试一遍用sysbench跑写入对比 TPS 差异再决定线上配置。3.3 binlog 三种格式的选择与验证binlog 的 Statement、Row、Mixed 三种格式面试常问区别但实际选型要看场景。Statement 记录 SQL 语句日志量小但某些函数如NOW()在主从上执行结果可能不一致。Row 记录每行数据的变更前后镜像日志量大但绝对准确是当前主流选择。Mixed 让 MySQL 自己判断一般语句用 Statement可能不一致的用 Row。-- 查看当前 binlog 格式 SHOW VARIABLES LIKE binlog_format; -- 临时切换为 Row 格式需要重启或动态设置 SET GLOBAL binlog_format ROW; -- 查看 binlog 内容验证格式 SHOW BINARY LOGS; SHOW BINLOG EVENTS IN mysql-bin.000001 LIMIT 20;用mysqlbinlog工具解析 binlog 文件时Row 格式会显示### UPDATE和具体的列值变化Statement 格式则直接显示 SQL 文本。误删数据恢复时binlog_formatROW配合binlog_row_imageFULL是前提因为 flashback 类工具需要完整的前后镜像才能把 delete 反转成 insert。这一点在面试题第 28 问“误删数据怎么办”里是隐含条件很多人答了用 binlog 恢复却没说格式要求面试官一追问就露馅。4. 索引结构与存储引擎B 树、聚簇索引和覆盖索引的边界4.1 InnoDB 和 MyISAM 的索引实现差异InnoDB 的 B 树叶子节点直接存整行数据数据文件本身就是索引文件这叫聚簇索引。MyISAM 的叶子节点存的是数据记录的物理地址索引文件和数据文件分离所以 MyISAM 的主键索引和二级索引在结构上没有本质区别都是指向数据的指针。这个差异导致 InnoDB 的主键查询比 MyISAM 快少一次寻址但二级索引查询需要回表而 MyISAM 的二级索引也要回表。面试题第 7 问“InnoDB 为什么设计 B 树”的答案里两个关键点是范围查询和随机 IO。哈希索引 O(1) 但不支持范围B 树非叶子节点存数据导致范围查询时随机 IO 多B 树所有数据在叶子节点且用指针串联范围扫描时顺序 IO 效率高。以整数字段索引为例N 约等于 1200树高 4 就能存 1200 的三次方约 17 亿条记录根节点常驻内存10 亿行的表查一个值最多访问 3 次磁盘。4.2 覆盖索引和索引下推的实测对比覆盖索引是指查询需要的字段全部在索引里不需要回表。索引下推是 MySQL 5.6 引入的优化在索引遍历时就过滤掉不满足条件的记录减少回表次数。-- 建联合索引 ALTER TABLE t_order ADD KEY idx_user_status (user_id, status); -- 覆盖索引只查索引里有的字段Extra 显示 Using index EXPLAIN SELECT user_id, status FROM t_order WHERE user_id 100; -- 需要回表查了索引里没有的 amountExtra 无 Using index EXPLAIN SELECT user_id, status, amount FROM t_order WHERE user_id 100; -- 索引下推status 在索引里但 like 条件在索引遍历时过滤 EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status LIKE 1%;看Extra列出现Using index就是覆盖索引出现Using index condition就是索引下推生效。覆盖索引的代价是索引变宽占用更多空间更新时维护成本也高。我一般只在核心查询上建覆盖索引比如列表页只查 id 和几个状态字段详情页再回表查全量。索引下推则要注意它只对联合索引中后续字段的条件生效且条件必须是索引列上的。4.3 change buffer 的适用场景与唯一索引的取舍change buffer 是 InnoDB 在更新非唯一索引时如果数据页不在内存中先把更新缓存在 change buffer 里等下次查询该页时再 merge。唯一索引不能用 change buffer因为唯一性约束要求每次写入都必须读磁盘确认是否冲突。这就是面试题第 7 问“普通索引还是唯一索引”的底层原因。适用场景是写多读少比如账单、日志系统页面写完不会马上被访问change buffer 能攒一批再 merge减少随机 IO。反过来写完马上查的业务change buffer 还没来得及 merge 就被触发反而增加了维护成本。我一般会先看业务的读写比写多读少且能接受非唯一约束的优先普通索引如果业务逻辑强依赖唯一性那就老老实实用唯一索引别为了性能牺牲正确性。5. 避坑与排查那些面试答对但线上翻车的点5.1 现象改了权限但连接仍然报错原因连接器在建立连接时就固定了权限后续GRANT不影响已存在的连接。解决让应用重连或KILL掉旧连接新连接才会加载新权限。这个坑在面试里不会问但线上改权限后不生效时很多人第一反应是权限没改对其实是连接没刷新。5.2 现象EXPLAIN 显示走了索引但查询依然慢原因走了索引但回表次数庞大或者索引区分度太低。面试题第 25 问专门讲了这种情况。解决用EXPLAIN看rows估算值如果接近全表行数说明索引没起到过滤作用考虑建覆盖索引或者用FORCE INDEX换一个区分度更高的索引。我遇到过status字段只有三个值建了索引但优化器算下来还不如全表扫这种情况删掉索引反而更快。5.3 现象误删数据后用 binlog 恢复失败原因binlog_format不是 ROW或者binlog_row_image不是 FULL导致 flashback 工具拿不到完整的前后镜像。解决恢复前先确认SHOW VARIABLES LIKE binlog_format和binlog_row_image并且恢复要在一个临时实例上做验证无误后再导回主库。面试题第 28 问提到的 myflash 工具就依赖这两个参数。5.4 现象kill 命令执行了但 SQL 还在跑原因面试题第 30 问列了三种情况——kill 命令被堵、到位了没触发、触发了但执行需要时间。解决先用SHOW PROCESSLIST确认线程状态如果是Sending data说明在执行器阶段kill 后要等当前行处理完如果是Waiting for table metadata lock得先找到持锁的线程。我一般会先KILL QUERY尝试终止语句不行再KILL CONNECTION断连接。5.5 现象内存表重启后备库同步中断原因MEMORY 引擎的数据在重启后丢失备库重启会导致主备同步线程停止双 M 架构下还可能把主库的内存表数据删掉。解决生产环境避免使用 MEMORY 引擎面试题第 35 问明确不建议。如果确实需要内存级性能用 Redis 或把数据放到 InnoDB 的 buffer pool 里。6. 用两阶段提交状态判断恢复策略一个可以背下来的决策表面试题第 16 问“数据库 crash 后如何恢复未刷盘数据”是整份资料里最容易被追问的题因为它把 redo log、binlog、change buffer 三个概念串在一起。我把它的判断逻辑整理成一张决策表面试时按这个顺序讲基本不会乱redo log 状态binlog 状态恢复动作fsync 但未 commit未 fsync数据丢失change buffer 中的更新不回放fsync 未 commit已 fsync先从 binlog 恢复 redo log再从 redo log 恢复 change buffer已 commit已 fsync直接从 redo log 恢复这张表的底层逻辑是redo log 的 commit 状态代表事务是否真正提交binlog 的完整性决定 prepare 状态下的事务是提交还是回滚。恢复时先看 redo logcommit 的直接恢复prepare 的去查 binlogbinlog 完整就提交不完整就回滚。change buffer 的恢复依附于 redo log因为 change buffer 的变更也记录在 redo log 里。验证这个流程的方法是在测试环境模拟 crash用sysbench跑写入中途kill -9掉 mysqld 进程重启后对比数据行数和 binlog 解析结果。我一般会跑三组正常关闭、kill -9、断电模拟虚拟机强制关机观察不同innodb_flush_log_at_trx_commit配置下的数据丢失情况。跑过一遍之后这些参数和状态就不再是纸上的概念了。从那以后我每次面试前都会把这张表默写一遍并且在实际环境里模拟一次 crash 恢复因为背下来的答案和亲手验证过的理解在面试官追问时的表现完全不一样。希望帮到你。本文还有配套的精品资源点击获取