简介面向准备 MySQL 相关岗位面试的开发者整理的一份知识点问答合集聚焦常见高频考点的串联复习。内容围绕关系型与非关系型数据库的取舍、一条 SQL 从连接器、查询缓存、分析器、优化器到执行器的完整链路展开并延伸至索引为何出现、哈希表/有序数组/N 叉树三类底层结构、聚簇索引与二级索引的差异、MyISAM 与 InnoDB 的 B 树实现区别、覆盖索引与索引下推、索引失效的典型写法以及 change buffer、redo log 与 binlog 等日志机制形成从原理到排查的问答脉络。压缩包内为 1 个 docx 文档约 40KB按题目分条编排便于按考点跳读与临考速览。已有 252 人学习下载适合用于梳理回答思路、补齐概念盲区也可作为面试前的自测清单。1. MySQL 是什么为什么面试官总盯着「一条 SQL 怎么跑」不放把 MySQL 想成一座带账房的大仓库货架上堆着你的数据账房先生负责按你递的条子找货、核对权限、记账、对账。你敲下一条 SQL本质上是递了张条子过去找货的路线由它自己安排。很多人刷 MySQL 面试题时背的是一堆孤立答案——索引是什么、事务是什么、日志是什么可面试官换个问法「这条语句为什么慢」立刻卡壳。缺的不是知识点是那条流水线一张条子进仓库之后在哪一步被拦下、在哪一步绕了远路、在哪一步把结果记进了账本。把流水线走通一遍零散的名词会自己归位。2. 索引为什么像书的目录哈希表、有序数组和 B 树的取舍2.1 没有索引时查一行数据到底亏在哪数据不是一行一行从磁盘搬进内存的而是一页一页搬。InnoDB 的一个数据页被切成七块File Header 占固定 38 字节Page Header 占固定 56 字节两个虚拟的伪记录 Infimum 和 Supremum 占固定 26 字节中间是真正存数据的 User Records、尚未使用的 Free Space、记录相对位置的 Page Directory最后一个 8 字节的 File Trailer 用来校验这一页有没有写完整。这个结构决定了查一行的代价。你要找 id 666 那条记录引擎没法只把那一行捞出来它得先把包含这一行的整页读进内存再在页内一条条比对。而 CPU 把磁盘上的数据加载进内存是整个查询里最慢的一环慢到足以让「多读几页」和「少读几页」变成两种完全不同的体验。没有索引时引擎只能从第一页顺次扫到最后一页这叫全表扫描。索引干的事就是另外维护一份「字段值 → 位置」的对照表像书的目录一样让你直接翻到目标页附近。除了加速查询它顺带还带来几个好处建唯一索引可以保证每一行数据不重复索引能让引擎层避开排序和临时表把随机 IO 变成顺序 IO连表连接也跟着快起来。2.2 三种底层数据结构各自擅长的查询不一样面试里常问「索引的底层数据结构有哪几种」标准答案是三种哈希表、有序数组、搜索树。难的是说清它们各自死在哪种查询上。哈希表把键算成一个位置等值查询一次命中键值型引擎很爱用但它把顺序彻底打散了你要查「id 大于 100 的所有记录」它只能干瞪眼最后退化成全表扫描。有序数组反过来等值能二分、范围能二分性能都好可一旦中间插一条数据后面所有元素都得往后挪更新代价高到只适合静态存储引擎——那种写完就不怎么改的归档数据。结构等值查询范围查询更新代价典型场景哈希表一次定位不支持退化为全表扫描低等值查询为主的键值引擎有序数组二分查找快快高插入要整体挪位静态存储引擎、只读数据N 叉树B 树快快叶子节点相连可控磁盘上的数据库索引真正被数据库引擎广泛采用的是 N 叉树原因很朴素它在读写性能上都不差而且节点大小能对齐磁盘的读取单位一次读盘就能拿走一大批键值。2.3 InnoDB 选 B 树为了让磁盘少转几圈B 树到底能省多少次读盘可以算一算。以 InnoDB 的一个整数字段索引为例一个节点大约能放 1200 个键值也就是所谓的 N 约等于 1200。树高为 4 的时候能容纳的索引项是 1200 的 3 次方已经超过 17 亿。这意味着一个 10 亿行的表在整数字段上建索引找一个值最多只需要访问 3 次磁盘。而且树根的数据块基本常驻内存第二层也有很大概率在内存里实际的平均读盘次数还要更少。# 估算 N 叉树在不同树高下能容纳多少条索引项 N 1200 # InnoDB 整数字段索引单个节点大约能放的键值数量 for height in range(2, 5): # 高度为 h 的 N 叉树最底层最多有 N ** (h - 1) 个节点 capacity N ** (height - 1) print(f树高 {height}可容纳约 {capacity:,} 条索引项)这段代码输出的是三行数字树高 2 约 1200 条树高 3 约 144 万条树高 4 约 17.28 亿条。N换成一个更大的值能撑住的表就更大但单页能放的键值变多也意味着页内比较次数变多所以这个值不是越大越好。那为什么偏偏是 B 树不是哈希也不是 B 树哈希的 O(1) 只在等值时成立碰上范围查询和排序就完全帮不上忙只能全表扫。B 树的非叶子节点也存数据看起来更省层数可查询连续数据时数据散落在各个节点上会带来更多随机 IO。B 树把所有数据都赶到叶子节点叶子节点之间再用指针串起来顺序遍历就变成了顺着链表走随机 IO 被压下去一截。3. 聚簇索引和二级索引一次查询要「回表」几次才拿到数据3.1 主键索引存整行二级索引只存主键按叶子节点存的东西来分索引只有两类。主键索引的叶子节点存的是整行数据在 InnoDB 里叫聚簇索引非主键索引的叶子节点存的是主键的值叫二级索引。这个区别直接决定了「回表」这件事。你按二级索引查一条记录引擎先在二级索引的 B 树上找到主键值再拿着这个主键去聚簇索引的 B 树上找整行。一次查询走了两棵树第二次就叫回表。回表次数越多查询越慢这也是后面所有优化手段的出发点。MyISAM 的玩法完全不同。它的 B 树叶子节点存的是数据记录的物理地址索引文件和数据文件是分开的InnoDB 则是叶子节点直接存数据本身数据文件本身就是索引文件。所以同样是「拿到地址再去取数据」MyISAM 是去另一个文件里按地址取InnoDB 是再走一次 B 树。顺带一个常被忽略的推论既然二级索引的叶子存的是主键值主键越短每个二级索引页能塞下的条目就越多整棵索引树也就越矮。3.2 覆盖索引与索引下推两种少跑一趟的办法覆盖索引解决的是「能不能不回表」。当一条查询需要的字段二级索引上全都有引擎读完索引就能交差不必再回聚簇索引。比如在 k 上建了索引select id from t where k 3就拿得到结果因为二级索引叶子本来就存着 id。反过来select name from t where k 3里 name 不在索引上就必须回表。-- 索引 idx_k 的叶子节点只存了主键 id查询只要 id索引已经覆盖了需求 select id from t where k 3; -- 查询要 name而 idx_k 上没有 name必须拿 id 回聚簇索引再查一次 select name from t where k 3;覆盖索引的价值在于减少树的搜索次数它是个很常用的优化手段。代价是为了覆盖你可能要建一个包含多个字段的联合索引索引占的空间和写入时的维护成本都上去了。所以判断标准不是「能不能覆盖」而是「这条查询是不是高频到值得为它单独建索引」。索引下推是另一条路MySQL 5.6 引入。原本引擎只能把索引里能判断的条件先用上剩下的条件必须回表之后才能判断索引下推把这个判断提前到索引遍历的过程中索引中包含的字段先在索引上过滤一遍不满足条件的记录直接扔掉回表次数自然就少了。3.3 字符串建索引的四种做法各自付什么代价给字符串字段加索引是个典型的「没有最优解只有取舍」的场景常见做法有四种。第一种直接对整个字段建索引。查询最干脆但字段一长索引体积会很夸张。第二种建前缀索引只取前若干个字符。省空间可代价是查询扫描次数会增加而且不能再用覆盖索引——因为索引里根本没有完整的值引擎没法确认你是不是要的那一行。第三种倒序存储之后再建前缀索引专门用来对付「前缀区分度不够」的字符串比如一批后缀相同、前缀雷同的编码。第四种额外加一个 hash 字段并建索引查询性能稳定但要付出额外的存储和计算开销而且和第三种一样不支持范围扫描。-- 前缀索引只取前 10 个字符省空间但可能需要多扫几行也用不上覆盖索引 alter table t add index idx_email_prefix (email(10)); -- hash 字段索引把长字符串压成定长值再建索引 alter table t add column email_crc int unsigned as (crc32(email)) stored; alter table t add index idx_email_crc (email_crc);hash 那条要特别注意碰撞crc32 把任意长度压成 32 位不同字符串算出同一个值很正常。所以查询时得写成where email_crc ? and email ?先用 hash 快速定位再用原值精确比对。少了后半截就可能捞出别人的数据。4. 易混概念拆解这几组「看着差不多」的东西最容易答错4.1 易混一普通索引和唯一索引到底谁更快易混点在于「唯一」两个字自带的心理暗示——听起来更严格、更高级那应该也更快吧。为什么混唯一索引确实能保证数据不重复业务上很诱人而且查询时一旦命中就能停理论上更快。但这个优势只在等值查询里成立写操作上反而会翻车。怎么区分看 change buffer。更新一个数据页时如果页在内存里就直接改不在内存里InnoDB 会先把这次更新记进 change buffer等下次查询用到这一页时再合并省掉一次随机读盘。但唯一索引不行——它每次插入都要判断「这个值是不是已经存在」必须把页读进内存确认change buffer 这条捷径走不通。所以对于写多读少的业务比如账单类、日志类系统普通索引明显更划算。反过来说如果业务是写完立刻就要查change buffer 刚记下就被触发合并随机 IO 一次没少还多付了维护成本这时候它的优势就没了。业务能接受在应用层保证唯一就从性能角度优先考虑非唯一索引。4.2 易混二索引失效的几种写法不是玄学易混点以为「只要建了索引查询就一定会走索引」。为什么混索引失效的表现很诡异同一条 SQL 换个参数可能就走索引了看起来像玄学。其实原因很实在——SQL 的写法让优化器没法用上索引里存的东西。怎么区分记住四类写法。对索引列做左模糊或左右模糊匹配like %xx和like %xx%都会失效因为结果可能是多个引擎不知道从哪个索引值开始比只能全表扫。对索引列做函数运算或表达式计算索引里存的是原始值而不是算完的值用不上。对索引列做隐式类型转换效果等同于套了一层新函数。where里的or只要连了一个非索引列整体就退回全表扫描。-- 一、左模糊索引失效退化成全表扫描 select * from t where name like %张; -- 二、对索引列做函数运算索引存的是原始值函数算完的值对不上 select * from t where month(created_at) 3; -- 三、隐式类型转换phone 是字符串类型这里传了数字等于给列套了函数 select * from t where phone 13800000000; -- 四、OR 连了没有索引的列整体退回全表扫描 select * from t where id 1 or address 某地址;4.3 易混三redo log 和 binlog一个管恢复一个管备份易混点两个都叫日志都记数据变化都跟「数据丢了怎么找回来」有关。为什么混它们确实在同一个事务里配合工作还是两阶段提交绑在一起的很容易被当成同一件事的两种叫法。怎么区分先看归属redo log 是 InnoDB 引擎层的日志binlog 是 MySQL Server 层的归档日志。再看写的方式redo log 是循环写空间固定写完一圈就回头覆盖binlog 是追加写保存的是全量日志一直往后加。然后是能力redo log 具备 crash-safe 能力binlog 不具备——因为 binlog 虽然记了全量却没有一个标志告诉 InnoDB「哪些数据已经刷盘、哪些还没有」崩溃之后没法判断从哪恢复。最后是用途redo log 管异常重启后的数据恢复binlog 管主从复制和按时间点恢复数据。4.4 易混四drop、truncate、delete 删的到底是什么易混点三个都叫「删除」很多人以为只是速度不同。为什么混执行完表里都空了肉眼看不出区别。怎么区分delete 是逐行删每一行的删除操作都会作为事务记录写进日志所以能回滚也会触发与表相关的删除触发器速度最慢。truncate 是一次性把整张表的数据清掉不把单独的删除操作记进日志删掉的行恢复不了也不激活触发器速度快得多但表结构还在。drop 更彻底把表占用的空间整个释放表和结构一起没了。速度排序是 drop 快于 truncatetruncate 快于 delete。按需求选删部分数据用 delete 并带 where 子句删整张表用 drop只想清空数据、保留表结构且与事务无关用 truncate与事务有关或者要触发 trigger还是得用 delete。5. 日志系统在保什么WAL、两阶段提交与崩溃恢复5.1 先写日志再写磁盘WAL 换来了什么WAL 的全称是 Write-Ahead Logging关键动作就一句日志先写内存再写磁盘。MySQL 执行一条更新语句后不会立刻把数据页刷到磁盘而是先把「要在哪个页上做什么修改」记进日志然后返回。为什么要这么绕因为磁盘太慢。如果每次更新都实时写盘一条语句的响应时间会被磁盘拖死而先写日志、把数据页的刷盘推迟到空闲时批量做语句能立刻返回。代价是内存里的数据还没落盘此时宕机就丢了——所以要靠 redo log 在崩溃后重放把没落盘的数据补回来。日志先写、数据后写这就是它能兼顾速度和安全的全部秘密。redo log 在内存里是 redo log buffer落到磁盘是 redo log file中间还要经过操作系统的缓冲区中转。这次中转要不要省由一个参数决定# my.cnf 中的刷盘策略取值 0 / 1 / 2 [mysqld] innodb_flush_log_at_trx_commit 1取 0 是延迟写事务提交时不写每秒把 redo log buffer 写进系统缓冲区并刷到日志文件取 1 是实时写实时刷每次提交都写缓冲区并落盘取 2 是实时写延迟刷每次提交写进系统缓冲区每秒才落盘。1 最安全2 在机器不宕机只重启进程时也够用0 最快但丢数据窗口最大。5.2 redo log 的循环写粉板满了就得停下来redo log buffer 在磁盘上是由首尾相连的四个文件组成的分别是 ib_logfile_1 到 ib_logfile_4。里面有两个指针write pos 是当前记录写到哪了一边写一边后移写到第 3 号文件末尾就回到 0 号文件开头checkpoint 是当前要擦除的位置同样循环后移擦之前必须把这段记录对应的数据更新到数据文件里。两个指针中间那段空着的区域就是还能继续记新操作的地方。如果 write pos 追上了 checkpoint说明粉板写满了这时候不能再执行新的更新得先停下来擦掉一些记录、把 checkpoint 往前推。想明白这个画面就能理解为什么 redo log 有大小上限以及为什么写入突然卡顿可能跟它有关。这套循环写还顺带解释了 crash-safe只要数据刷进了磁盘对应的 redo log 记录就被抹掉所以 redo log 里留下的永远只是「还没落盘」的那部分。数据库重启后把 checkpoint 之后的记录重放一遍就补齐了所有已提交但没落盘的事务。5.3 binlog 的三种格式与两阶段提交binlog 有三种格式。statement 记的是 SQL 语句本身好处是不用记录每一行的变化日志量小、省 IO、性能好坏处是语句要在备库上跑出同样结果就必须额外记录执行时的上下文信息某些函数、存储过程、触发器还可能复制出错。row 格式不记 SQL 上下文只记哪条记录被改成了什么细节非常清楚不会出现复制不一致的问题代价是日志量可能非常大。mixed 是两者的结合普通语句用 statement遇到 statement 搞不定的函数就切 row由 MySQL 按每条语句自己判断。两阶段提交则是让 redo log 和 binlog 保持一致的手段redo log 的写入拆成 prepare 和 commit 两步中间穿插写 binlog。如果先写 redo log 再崩了binlog 备份恢复时会少一次更新数据对不上如果先写 binlog 再崩了redo log 没写、事务无效恢复时同样对不上。拆成两段之后恢复时看 redo log 的状态就能判断状态是 commit说明 binlog 也成功了直接恢复数据状态是 prepare就去查对应的 binlog 事务是否完整再决定回滚还是执行。那怎么判断 binlog 本身是完整的statement 格式的 binlog一个事务最后会有 COMMITrow 格式的最后会有一个 XID event。有了这个收尾标记才知道这个事务的日志有没有写全。5.4 备份与恢复误删之后还有哪些后悔药先把预防说清楚权限控制和分配要收着给操作规范要成文定期给开发做培训搭一个延迟备库兜底所有对线上数据有更改的语句都要过 SQL 审计备份要定期做。数据量大就用物理备份定期全量加增量数据量小就用逻辑导出工具。binlog 文件本身也要定期备份它才是按时间点恢复的关键。真删了分情况。DML 误操作造成的数据不完整可以先用 binlog 反转工具把事件翻过来——delete 转 insert、insert 转 delete、update 把前后镜像对调。前提是 binlog_format 必须是 row且 binlog_row_image 必须是 full否则镜像信息不全反不了。恢复时务必先恢复到临时实例确认无误再回主库。DDL 误操作更麻烦truncate 和 drop 不管 binlog_format 是 row 还是 statementbinlog 里都只记语句、不记数据镜像反转无从下手只能靠全量备份加上应用 binlog 重放数据量一大恢复时间就特别长。至于直接删文件的误操作只能靠跨机房、最好跨城市保存的备份。6. 什么时候该用、什么时候别用一条慢 SQL 该从哪儿开始怀疑「这条 SQL 平时很快今天突然慢得离谱」——这是最常被问到、也最容易答散的一类问题。别急着加索引先按层次往下剥。慢在哪一层典型表现先查什么数据库本身被堵住所有语句一起变慢连接排队系统资源、网络、连接数是否打满语句被锁堵住单条语句卡住不动最后超时表锁、行锁、是不是有长事务占着索引没用对单条语句偶发慢换个参数又好了执行计划里的 key 是不是空的索引用了但回表太多扫描行数看着不大耗时却很长回表次数、能不能改成覆盖索引前两种跟 SQL 写法无关属于「等一等就好」或者「找人处理锁」后两种才是能靠改语句、改索引解决的。对应三种处理办法。一是用 force index 强行指定索引这招见效快但属于硬编码表结构一变就可能反过来拖后腿只适合临时救火。二是改语句引导优化器比如把order by b limit 1改成order by b, a limit 1语义逻辑是一样的但优化器选索引时的判断会变。三是新建一个更合适的索引或者干脆把误用、根本用不上的索引删掉。什么时候别急着加索引写多读少的表每加一个索引就多一份写入维护成本区分度很低的字段比如性别、状态位加了眼看着也用不上——MySQL 在真正执行前并不能精确知道满足条件的记录有多少条只能靠索引的区分度这类统计信息估算估算出来扫描行数占比太高优化器可能直接放弃索引走全表。这类字段该做的是别建单列索引考虑放进联合索引里当后缀。还有个容易被忽略的角度数据量大不等于会爆内存。MySQL 是边读边发的服务端不需要保存完整结果集取数据和发数据靠一个缓冲区来回倒客户端读得慢会堵住查询过程但内存不会被撑爆。InnoDB 内部管 Buffer Pool 用的是改进的 LRU按 5:3 把链表分成 young 和 old 两块全表扫进来的冷数据待在 old 区很快被淘汰不会把热数据挤出去。所以看到「大表查询」先别慌看看是不是客户端那边没及时消费结果。说个自己的血泪经验早些年排查一条时快时慢的语句我第一反应就是加索引加了三个慢的时候照样慢。后来老老实实打开执行计划发现是or里连了一个没索引的列整条语句一直在全表扫跟新加的索引一点关系没有。从那以后我每次写完一条带 where 的 SQL都会先看一眼执行计划里的 key 和扫描行数再决定要不要动索引。这份资料把上面这些点按面试常问的顺序整理成了三十多个问题从索引结构一直串到日志和主备遇到答不上来的再回去翻对应那一条就行。希望帮到你。本文还有配套的精品资源点击获取