简介一份面向后端开发与数据相关岗位求职者的 MySQL 面试题知识点总结围绕服务端高频考点展开关系型与非关系型数据库如何选型、一条 SQL 语句从连接器、分析器、优化器到执行器的完整执行流程、索引加快查询的根本原因、哈希表/有序数组/B树等底层数据结构的优劣、主键索引与二级索引的区别、MyISAM 和 InnoDB 的 B 树索引实现差异、InnoDB 选择 B 树的设计考量、普通索引与唯一索引的取舍、覆盖索引和索引下推对回表的优化并整理了对索引使用左/左右模糊匹配、函数或表达式计算、隐式类型转换、OR 条件等导致索引失效的典型场景。资源为单份 docx 文档约 40KB采用“问题解析”的结构组织便于按需检索和打印复习目前已有 251 人学习下载既适合笔试前系统梳理核心概念也适合面试前集中记忆关键原理可当作日常查阅的速查手册。1. MySQL面试题36连问为什么我劝你不要死记答案背过MySQL面试题的人大多有这种经历网上下了一堆题目背得滚瓜烂熟面试官追问一句“为什么”就接不上话。这份MySQL面试题资源恰好相反36个高频考点从“关系型和非关系型的区别”到“主备同步流程”每一个都往原理深处挖了一层。比如讲索引失效时不是让你死记“like %xx会失效”而是告诉你不失效的根本原因——查询优化器不知道从哪个索引值开始比较。对于准备基础岗位的应届生它能快速帮你建立知识骨架对于有一到两年经验的开发更适合当成自检清单逐条对一遍比盲目刷题有效得多。这些题目按“执行链路→索引→日志→主从同步”组织配合我下面要讲的避坑点和验证方法两天能完整过一轮。2. 从一条SQL的执行链路说起连接器、优化器与存储引擎选型2.1 执行链路串讲一条SQL到底走过哪些节点刚拿到这份资料时第一个值得啃透的点就是一条SQL的完整执行链路。标准答案是客户端请求 → 连接器 → 查询缓存 → 分析器 → 优化器 → 执行器。背下来容易但要在面试现场回答好至少还要补两个细节。第一个细节连接器验证用户身份并给予权限这只是在“登录”层面的校验执行器真正调用引擎接口前还会再查一次“这个用户对这张表有没有对应操作权限”。两者粒度不同不少背题的人会漏掉执行器这一步的权限复检被追问时就卡住了。第二个细节查询缓存已经过时。MySQL 5.7里查询缓存默认关闭MySQL 8.0直接移除了这个模块。原因也不难理解只要表上有更新相关缓存全部失效维护代价高而命中率低。背题时不补这个版本差异面试官顺着问一句“如果命中缓存呢”“更新了怎么办”你就容易翻车。第三个容易被忽略的点是Server层和引擎层的分工。分析器和优化器都在Server层前者做词法分析和语法分析后者选择执行计划但真正去数据页里定位一条记录是InnoDB引擎层干的事。资料里把这条链路写得很清楚面试时能把“Server层做什么、引擎层做什么”拆开讲是明显加分项。2.2 存储引擎对比InnoDB、MyISAM与MEMORY怎么选资料对比了三种引擎这张表建议背熟因为面试官常从“你平时用什么引擎”切入特性InnoDBMyISAMMEMORY事务支持ACID不支持不支持锁粒度行锁表锁表锁外键支持不支持不支持持久化数据落盘数据落盘重启后数据丢失索引实现B树叶子节点存数据B树叶子节点存物理地址哈希/索引均在内存适用场景事务型业务、线上核心表读多写少、日志类临时表、中间结果InnoDB从MySQL 5.5.5开始成为默认引擎核心原因就是事务和行锁。MyISAM的插入和查询速度在特定场景下确实快但不支持事务一个更新做到一半崩了整个表的状态很难保证。MEMORY引擎要特别注意重启会丢数据。资料里点出了一个很实际的问题——如果备库用的是内存表备库一重启主备同步线程会直接停掉如果主备是双M架构还可能把主库的内存表数据清掉。生产环境不推荐用内存表这是硬教训。2.3 索引底层数据结构为什么最终选了B树三种常见底层数据结构可以放在一起看哈希表、有序数组、N叉搜索树。哈希表对等值查询是O(1)的Memcached和部分NoSQL引擎都靠它吃饭但完全不支持范围查询一次范围扫描就退化成全表。有序数组等值和范围查询性能都好可更新成本太高——中间插一条记录要移动后续所有数据只适合静态存储引擎。N叉树的读写性能和磁盘访问模式很契合被广泛用在数据库引擎里。InnoDB里一个整数字段索引的N大约是1200。当树高为4时可以存1200的三次方个值这已经是17亿行。考虑到树根数据块常驻内存10亿行表上一个整数字段索引查找一个值最多只需访问3次磁盘第二层大概率也在内存中实际访问次数更少。还有一个高频追问点为什么要用B树而不用B树。B树允许在非叶子节点存储数据看起来没什么但查询连续数据时可能产生更多随机IO而B树的所有叶子节点通过指针相互连接顺序遍历时可以把随机IO变成顺序IO。资料里这题讲得比较细面试时能把“随机IO”和“顺序IO”这两个词说出来比只说“B树更适合磁盘”有说服力得多。3. 索引的最大考点类型、覆盖、失效与字符串索引怎么选3.1 主键索引与二级索引回表是怎么发生的索引类型按叶子节点内容分为主键索引和非主键索引。主键索引的叶子节点存整行数据InnoDB里叫聚簇索引非主键索引的叶子节点存主键值叫二级索引。这个区别直接引出“回表”问题。比如一张表以id为主键并在k字段上建了普通索引那么执行select * from t where k 5时先到k索引树上找到主键值再到主键索引树里取整行数据。二次搜索就是回表。背到这里还不够最好能接一句“覆盖索引可以减少回表次数”正好引到下一题。MyISAM和InnoDB的B树索引实现方式也要分清。InnoDB的索引文件本身就是数据文件叶子节点保存数据本身MyISAM的叶子节点保存的是数据物理地址索引文件和数据文件是分离的。严格来说MyISAM是B树索引实现而InnoDB是B树聚簇索引实现两个词不要混着说。3.2 覆盖索引与索引下推两条性能优化手段覆盖索引指的是某个查询里索引已经覆盖了查询需求不需要回表。举个最简单的例子-- 表user(id, name, age)其中id是主键age上有索引 CREATE TABLE user ( id int NOT NULL, name varchar(16) DEFAULT NULL, age int DEFAULT NULL, PRIMARY KEY (id), KEY idx_age (age) ); -- 覆盖索引查询列age和id都包含在idx_age索引中无需回表 SELECT age, id FROM user WHERE age BETWEEN 20 AND 30;逻辑说明idx_age索引的叶子节点存的是age值和主键id值上面这条SQL只需要age和id两个字段遍历idx_age就能拿到全部结果不需要回主键索引。参数说明age和id是这条查询的全部输出列索引“覆盖”了它们如果再加上name字段就必须回表了。索引下推是MySQL 5.6引入的优化英文叫Index Condition Pushdown。核心思想是在索引遍历过程中先对索引包含的字段做判断过滤掉不满足条件的记录减少回表次数。-- 联合索引idx_name_age(name, age) SELECT * FROM user WHERE name LIKE 陈% AND age 20;没有索引下推时存储引擎先按name LIKE 陈%找到一批主键逐个回表后再过滤age。有了索引下推age20这个条件直接在索引遍历时就判断掉不匹配的记录根本不会回表。逻辑说明索引下推只在索引字段上做判断如果判断条件涉及非索引字段还是得回表后过滤。参数说明name LIKE 陈%是前缀匹配本身能走索引下推的age条件恰好也在联合索引里所以能提前过滤。3.3 哪些操作会导致索引失效不是背结论而是说原因这一组是面试重灾区。资料里列了四类场景我建议按“原因”去记。第一类是左模糊或左右模糊匹配like %xx和like %xx%都会失效。原因是查询结果可能是多个优化器不知道从哪个索引值开始比较只能全表扫描。反过来like xx%有明确的起始位置可以走索引。第二类是对索引做函数操作或表达式计算。索引保存的是字段原始值不是函数计算后的值WHERE DATE(create_time) 2024-01-01这种写法只能放弃索引。正确的做法是把函数挪到右边比如WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。第三类是隐式转换。字符串字段和数字比较时MySQL会隐式调用转换函数相当于对索引字段做了函数操作索引自然失效。比如WHERE mobile 13800138000mobile是varchar类型这就会导致全表扫描。第四类是WHERE子句里出现OR只要其中一个条件列不是索引列整个查询就走不了索引。可以改成UNION或者用索引覆盖来绕过。3.4 字符串加索引四种方案的取舍字符串字段加索引资料里给了四个方案各有边界。一是直接创建完整索引空间占用大。二是创建前缀索引节省空间但会增加扫描次数而且不能使用覆盖索引。三是倒序存储再创建前缀索引适合字符串本身前缀区分度不够的情况比如邮箱前缀都是同一个域名。四是创建hash字段索引查询性能稳定但需要额外的存储和计算消耗而且不支持范围扫描。面试时被问到“前缀索引为什么不能用于覆盖索引”原因在于覆盖索引要求索引叶子节点包含查询的所有列前缀索引只存了部分字节得回表拿完整字段覆盖条件就不成立了。4. 日志与崩溃恢复redo log、binlog与两阶段提交4.1 redo log为什么必须存在写入方式是什么MySQL执行更新操作时为了快速响应不会每次update都立刻把数据写回磁盘而是采用WAL技术先写日志再写磁盘。redo log就是配合WAL机制的日志模块具备crash-safe能力——数据库异常重启后可以通过redo log恢复未落盘的数据。redo log的写入方式要讲详细一点。它包含两部分内存中的redo log buffer和磁盘上的redo log file。MySQL每执行一条DML语句会先把记录写入redo log buffer的用户空间再保存到内核缓冲区OS-buffer后续某个时间点再一次性刷到redo log file。刷盘时机由参数innodb_flush_log_at_trx_commit控制三个取值参数值写入行为安全性性能0延迟写事务提交时不写OS buffer每秒写一次最差可能丢1秒数据最高1实时写、实时刷每次提交都写入并落盘最高不丢已提交事务最低2实时写、延迟刷每次提交写入OS buffer每秒落盘中等操作系统崩溃会丢中等生产环境默认是1因为这是唯一能保证“提交即持久化”的设置。如果为了压测性能临时改成0或2必须清楚自己丢了什么保障。redo log的执行流程可以拿一条具体SQL串一遍假设执行update T set a 1 where id 666。客户端把SQL发给Server层Server层分析、优化并执行生成执行计划后发给InnoDB引擎。InnoDB把内存中的a字段改为1同时在redo log里追加一条记录内容是“哪个数据页、做了什么修改”。随后事务状态设为prepare表示准备提交。Server层处理完事务后把状态改为commitredo log再把内存中的操作记录写入磁盘整个日志记录过程完成。redo log还有一个关键细节它是循环写的。磁盘上由首尾相连的四个文件组成分别是ib_logfile_1到ib_logfile_4。write pos当前记录位置边写边往后移动checkpoint是当前要擦除的位置。write pos和checkpoint之间的空间用来记录新操作。如果write pos追上checkpoint说明日志写满了此时不能再执行新更新必须先推进checkpoint。重启后checkpoint之后的数据通过redo log重放已提交事务不会丢。4.2 binlog归档日志的三个格式与crash-safe的边界binlog是MySQL Server层的归档日志主要做两件事主从复制和数据恢复。当需要恢复数据时取出某个时间范围内的binlog重放即可。但它不能单独做crash-safe。原因是binlog是追加写的保存全量日志但它没有标记告诉InnoDB哪些数据已经刷盘、哪些还没有。crash之前binlog可能没写完整数据库就挂了单靠binlog没法判断恢复边界。redo log则不同刷入磁盘的数据会从redo log里抹掉重启后直接把redo log中剩余的数据恢复到内存就能继续工作所以redo log具备binlog不具备的crash-safe能力。binlog有三种格式资料里说得比较全格式原理优点缺点Statement记录修改数据的SQL语句日志量小IO开销低需要额外记录上下文信息否则备库可能执行出不同结果Row记录被修改行的前后镜像复制准确存储过程/函数/触发器场景不踩坑日志量大Mixed自动判断一般语句用Statement特殊场景用Row折中方案行为不完全可控实际业务里如果要求精确恢复Row格式是首选。生产环境做数据恢复也依赖Row格式后面第5章会展开讲。4.3 两阶段提交为什么redo log和binlog必须保持一致性redo log和binlog是两个独立个体要让它们逻辑上一致必须引两阶段提交。MySQL把redo log的写入拆成prepare和commit两个步骤中间穿插写binlog这就是两阶段提交。为什么必须这样做假设不用两阶段提交会出现两种情况。先写redo log再写binlogredo log写入成功事务提交随后崩溃时binlog还没写备库用binlog做恢复时少了这一次更新主备数据不一致。先写binlog再写redo logbinlog写入成功但崩溃时redo log没写入事务无效。备库恢复时却重放了binlog这条记录主库没有这条数据两边还是不一致。两阶段提交就是为了堵住这两个漏洞。恢复数据时如果redo log状态是commit说明binlog也成功写入直接恢复如果redo log状态是prepare则查一下对应的binlog事务是否完整决定回滚还是继续执行。MySQL判断binlog完整也有标准statement格式的binlog最后有COMMITrow格式的最后有XID event。4.4 change buffer普通索引能用唯一索引为什么不行change buffer解决的是“更新数据页但数据页不在内存中”的问题。当需要更新一个数据页时如果数据页不在内存InnoDB不急着从磁盘读入这个页而是把更新操作缓存在change buffer里。下次查询访问这个数据页时再把页读入内存并执行change buffer中与这个页相关的操作保证逻辑正确。关键限制是唯一索引的更新不能使用change buffer只有普通索引可以。原因在于唯一索引需要先判断索引是否唯一必须把数据页读入内存才能确认没有冲突所以也就谈不上“延迟更新”。change buffer的适用场景是写多读少的业务比如账单类和日志类系统页面在写完后马上被访问的概率很小缓存效果最好。反过来如果写入之后立刻做查询刚缓存的更新马上要触发merge过程随机访问IO次数不但没减少反而增加了维护代价。面试时能说出这个适用边界比只说“唯一索引用不上change buffer”有深度得多。5. 数据安全与避坑实战主从同步、误删恢复与常见翻车点5.1 主从同步原理主库和备库的角色分工主从同步最容易被误读的是“谁主动”的问题。一开始创建主备关系时是由备库指定的。比如基于位点的主备关系备库告诉主库“我要从binlog文件A的位置P开始同步”主库从这个位置往后发日志。但主备关系搭建之后后续日志由主库决定发给备库。实际面试中经常考察备库怎么追数据。主库有新的binlog就继续发给备库备库内部回放这些日志把变更同步到自己的数据文件。这里还隐含一个主备切换场景客户端读写访问主库A备库B持续同步A的更新A出故障后把流量切到BB才升级为新的主库。资料里对这部分讲得不算深但把“备库指定起始位置主库持续推送”这句话说出来就足够撑起一个基础答案。5.2 误删数据恢复DML、DDL和rm三种情况分开处理误删恢复首先要在预防层面下功夫。资料里提到的预防手段包括权限控制与分配、操作规范、定期给开发做培训、搭建延迟备库、SQL审计以及定期备份。备份层面分两种数据量大用物理备份xtrabackup做全量加增量数据量小用mysqldump导出逻辑备份同时定期归档binlog文件。真发生误删时恢复方案的差别很大。DML误操作比如漏加WHERE条件把整表数据更新坏了可以用flashback类工具。原理是解析binlog event然后反转delete反转为insertinsert反转为deleteupdate的前后镜像对调。前提是binlog必须是row格式且参数binlog_row_image设为full。恢复时务必先恢复到临时实例确认数据无误后再写回主库。DDL误操作truncate和drop这种binlog里只记录语句本身不记录行镜像所以flashback思路行不通。只能拿最近一次全量备份配合后续的binlog做重放恢复到误删时间点之前的状态。数据量大时恢复时间会非常长这就是要定期备份binlog文件的意义所在。rm删除的是物理文件光靠备份不够必须有跨机房甚至跨城市的异地备份。5.3 三个面试翻车点现象、原因与解决办法翻车点一把innodb_flush_log_at_trx_commit参数取值记混。现象面试官问“redo log什么时候刷盘”有人把参数值1说成每秒刷写把2说成每次提交落盘。原因三个取值的行为模式太相似孤立记忆很容易错位。解决按“提交时做什么”这个维度拆——0是延迟写、每秒刷1是提交即写即刷2是提交即写但延迟刷。重点记住1是唯一保证提交不丢的配置另外两个都是为了性能牺牲安全性。翻车点二讲change buffer时只说“唯一索引不能用”没提适用场景。现象被追问“那什么时候适合开启change buffer”答不上来。原因只背了结论没有理解它的设计前提。解决补上适用边界——写多读少、写完不会被立即访问的业务账单单、日志类效果最好如果写入后马上查询会立刻触发merge反而增加了随机IO和change buffer维护成本。翻车点三索引失效背了“like %xx不能用”但不理解前缀匹配为什么能用。现象面试官追问“那like xx%呢”犹豫半天说不清楚。原因没从B树索引本身的查找逻辑推导。解决记住一个判断标准——走索引的前提是优化器能从索引树上确定一个起始比较位置。like xx%起始位置明确可以走索引like %xx不知道从哪个索引值开始只能全表扫。用这条标准可以推导所有模糊匹配场景不用死记每一条。6. 把36问串成自己的答案体系一套可复现的面试突击自测流程拿到这份MySQL面试题别急着从头背到尾我给自己定了一套两遍式自测流程效果比来回刷题好很多你也能直接复用。第一遍按主题分组过。把这36个问题按“执行链路、索引、日志、数据安全、主从同步”五个桶装起来每天只过一组。过的时候不追求一字不差而是每道题先给自己30秒用一句话说结论再用两分钟讲原理。比如遇到“InnoDB为什么用B树”我不会直接背“读写性能好、适配磁盘”而是先说哈希表不适合范围查询、B树非叶子节点存数据导致随机IO多再说B树叶子节点通过指针相连把随机IO变成顺序IO。能把推导过程讲出来才算过。第二遍做交叉追问。这轮是找出知识盲区的关键。我会拿两道看似无关的题互相撞一下比如“普通索引还是唯一索引”和“change buffer是什么”撞完发现唯一索引更新无法用change buffer而为什么要用普通索引的答案里正好需要这个前提两条知识就连成一片了。用表格把每个考点的关联题号列出来能连起来的就画一条线最后你会发现36问其实只有大约10个真正的知识单元。这套流程走完大部分题目都能用自己的话重新组织一遍。因为面试题本来就不该成为你面试时的背诵稿而是帮你建立索引结构的工具——就像MySQL索引本身一样查询路径对了答案自然会以更快的速度被检索出来。从那以后我每次准备技术面试都强制先走一遍“分组→交叉→复述”不急着看标准答案反而真正到面试现场时心里更笃定遇到追问也不会慌。这份MySQL面试题36连问的资源包值得你按这个方式过一遍希望帮到你。本文还有配套的精品资源点击获取