MySQL Binlog 三种存储格式 及 一条SQL执行完整流程

一、MySQL Binlog 三种存储格式binlog 一共3 种格式由参数binlog_format控制1.STATEMENT语句级2.ROW行级3.MIXED混合模式1. STATEMENTstatement-based replicationSBR语句模式记录执行的SQL语句本身。特点• 日志体积小一条SQL修改多行只存一条语句• 节约网络IO缺点致命问题• 存在数据不一致风险非确定性函数执行结果主从不一样NOW()、RAND()、UUID()、LIMIT、触发器、存储过程• 无法精准同步基于行的修改例如UPDATE ... LIMITMySQL 5.1 默认早期版本使用现在几乎不推荐生产使用。2. ROWrow-based replicationRBR行模式 ✅生产主流不记录SQL记录每行数据变更前后的值特点• 主从数据一致性最高不存在非确定函数问题• 支持离线数据恢复、闪回binlog2sql、myflash缺点• 更新大量数据时 binlog 文件暴涨例UPDATE t SET namexxx WHERE create_time2025修改10万行会写入10万条行变更日志附加参数binlog_row_image控制每行记录内容•FULL默认记录前镜像后镜像修改前、修改后完整行•MINIMAL只记录修改字段主键体积更小•NOBLOB非blob字段完整记录blob变更只记录变更3. MIXEDmixed-based replicationMBR混合模式自动智能切换•普通SQL → 使用 STATEMENT•存在不确定函数/风险SQL → 自动切换为 ROW优缺点兼顾体积与一致性但存在不可控性现在主流规范直接统一使用 ROW不推荐 Mixed生产环境最佳实践binlog_format ROW binlog_row_image MINIMAL快速对比汇总表格式记录内容一致性日志大小适用场景STATEMENT原始SQL语句差有非确定函数隐患小基本淘汰MIXED自动切换语句/行一般逻辑不可控中等不推荐新项目ROW变更行数据镜像最高偏大生产标准方案、数据恢复、主从同步补充小知识点MySQL8.0默认 binlog_formatROW5.7 默认也是 ROW5.6/5.5 早期版本默认 MIXED。二、使用最左前缀索引a b c 字段是联合索引 where a1 and c2 and b3 索引使用情况联合索引idx(a,b,c)查询where a1 and c2 and b3有效用到索引前缀a bc无法走索引范围过滤。核心原理最左前缀 范围条件阻断规则联合索引中范围条件 between like 前缀模糊后面的列无法使用索引先拆解 SQLWHERE a1 AND c2 AND b3MySQL优化器会自动调整where条件顺序不依赖你书写顺序优化后逻辑等价WHERE a1 AND b3 AND c2索引结构a → b → c1.a1等值匹配 ✅ 使用索引2.b3范围条件❗→b之后所有字段(c)丧失索引检索能力执行流程1. 通过索引快速定位所有a1的索引区间2. 在a1集合里利用索引匹配b33.c2 无法在索引层面过滤MySQL拿到满足 a1 and b3 的索引行回表读取完整数据再过滤c2重点易错区分对比实验场景1当前题目a1 and b3 and c2索引使用idx(a,b)c失效场景2a1 and b3 and c2b是等值c范围 ✅ 使用idx(a,b,c)场景3a1 and b3 and c4a是范围 → b、c全部失效仅用到a补充重要知识点1.等值放前面范围放联合索引最后一列是最优设计良好设计idx(a,b,c)查询a? and b? and c?2. 为什么范围后面列失效联合索引是有序B树a固定 → b有序 → c有序当 b 使用范围查询匹配出来的多条记录中c不再全局有序数据库不能利用索引快速筛选c只能内存过滤。3. 误区纠正❌ 不是“写在后面的条件不走索引”✅ 是联合索引中第一个出现的范围字段阻断后续所有索引列执行验证方式执行 explain 查看 key、key_len• key_len a长度 b长度 → 证明只用到a、b没有用到c优化建议当前SQL如果查询频繁原有索引(a,b,c)不太合适调整索引顺序把范围字段c放到最后索引idx(a,b,c)不变改写SQL尽量保证等值在前如果业务经常a?,b?,c?无法调整条件只能接受c在server层过滤如果数据量巨大可以考虑覆盖索引减少回表开销idx(a,b,c,其他查询字段)覆盖索引避免回表虽然c依然不能索引过滤但减少IO极简总结背诵版联合索引(a,b,c)where a1 and c2 and b3优化器重排条件为 a1 and b3 and c2b是范围条件阻断后面c索引有效利用a、bc在服务层过滤。三、MySQL 一条SQL执行完整流程分为查询SQLSELECT、更新SQLINSERT/UPDATE/DELETE两套流程面试高频先明确架构分层客户端 → 连接器 → 查询缓存(8.0移除) → 分析器 → 优化器 → 执行器 → 存储引擎(InnoDB)默认以InnoDB存储引擎讲解生产主流一、整体通用分层流程SELECT 查询语句1. 连接器建立连接 2. 查询缓存MySQL5.7存在8.0 删除不再讨论 3. 分析器词法分析 → 语法分析生成语法树 4. 优化器生成多种执行计划选出最优执行计划 5. 执行器调用存储引擎API逐行读取数据 6. InnoDB存储引擎访问B树索引、返回数据分步详解1. 连接器客户端发起 TCP 连接mysql -h -u -p• 验证账号、密码• 获取该连接对应的权限• 维护连接长连接/短连接wait_timeout控制空闲断开连接成功后后续所有SQL都复用这条连接权限在连接建立时确定中途修改权限不会立即生效。2. 查询缓存废弃知识点5.7 支持MySQL8.0彻底移除逻辑以SQL字符串为key缓存查询结果缺陷只要表发生更新整张表缓存全部失效实用性极差不推荐使用。3. 分析器作用看懂这条SQL是否合法1.词法分析拆分字符串识别关键字select/from/where/and、字段名、表名、常量2.语法分析按照MySQL语法规则构建抽象语法树AST• 如果语法错误少逗号、关键字写错直接返回语法报错此时不会校验表、字段是否存在表不存在的报错在优化器阶段4. 优化器核心考点拿到语法树生成、选择最优执行计划做两件关键事情1.条件重排自动调整where条件顺序之前联合索引例题用到where a1 and c2 and b3→ 内部调整顺序方便匹配索引2.索引选择、连接顺序选择多条join、多个索引时计算成本选择开销最低方案最终输出确定用哪个索引、先扫描哪张表、执行顺序。explain 看到的内容就是优化器输出的执行计划5. 执行器按照执行计划工作1. 先校验用户是否拥有这张表的查询权限2. 调用存储引擎提供的接口read_row()3. 循环读取引擎返回的数据经过server层过滤无法使用索引的条件在这里过滤4. 组装结果返回客户端⚠️ 重要区分Server层连接器/分析器/优化器/执行器 和 存储引擎层InnoDB分离Server层通用MyISAM/InnoDB共用索引、事务、锁、MVCC由存储引擎实现。6. InnoDB存储引擎层接收执行器指令操作磁盘数据• 根据索引定位数据页B树• 优先访问缓冲池Buffer Pool内存不存在再加载磁盘页• 根据MVCC读取可见版本隔离级别控制• 将行数据返回执行器二、更新SQL流程UPDATE / INSERT / DELETE面试重中之重UPDATE user SET namexx WHERE id1;整体前期链路一样连接器 → 分析器 → 优化器 → 执行器重点区别更新涉及 redo log、undo log、binlog、事务两阶段提交执行步骤1. 执行器调用InnoDB引擎根据索引找到 id1 这一行2.加行锁事务提交前持有锁3. 生成undo log回滚日志用于事务回滚、MVCC4. 修改内存中 Buffer Pool 的数据页内存脏页5. 写入redo log buffer准备持久化redo log6. 告知执行器引擎层执行完成7. 执行器写入binlog cache8.事务提交两阶段提交 2PC• prepare阶段redo log持久化到磁盘打上prepare标记• commit阶段binlog持久化磁盘redo log打上commit标记9. 事务完成释放行锁三大日志简单区分配套考点1.redo log引擎层InnoDB特有崩溃恢复保证事务持久性2.undo log引擎层回滚、MVCC多版本3.binlogserver层主从复制、数据恢复三、高频面试易错题总结1. 语法报错在【分析器】表不存在报错在【优化器】2. 查询缓存8.0已经删除不要再写进答案3. 索引选择是优化器决定不是执行器4. where条件自动调整顺序发生在优化器阶段对应你上一题联合索引5. 索引无法过滤的条件在【执行器Server层】过滤6. SELECT没有redo/binlog写入DML更新语句会生成三大日志7. MVCC、锁、Buffer Pool 属于存储引擎层能力精简版一条查询SQL先经过连接器建立连接接着分析器做词法和语法解析生成语法树然后优化器生成并选出最优执行计划执行器校验权限调用InnoDB引擎接口InnoDB通过索引查找数据借助Buffer Pool读取页面基于MVCC返回可见数据最终执行器把结果返回客户端。更新SQL前期流程一致引擎找到对应数据加行锁记录undo log修改内存数据写入redo log上层执行器记录binlog最后通过两阶段提交保证redo log和binlog数据一致事务完成释放锁。