MySQL-必问

1.一条 UPDATE 语句是如何执行的请结合 redo log、undo log、binlog 和 MVCC多版本并发控制 讲清楚整个流程并说明为什么需要两阶段提交2PC✅ 深度解析redo log重做日志保证不丢数据物理日志记录“某个数据页做了什么修改”例如“将 page_id100 的偏移量 200 处的值从 10 改为 20”undo log回滚日志支持回滚和 MVCC逻辑日志记录“如何撤销一个事务的操作”例如“如果这个 insert 成功了请执行 delete如果是 update请恢复旧值”binlog二进制日志备份/同步用的日志给外部看的逻辑日志记录“整个 SQL 语句或行变更事件”用于复制和审计例如UPDATE users SET age30 WHERE id1; 执行流程以 InnoDB 引擎为例客户端发送UPDATE users SET age 30 WHERE id 1;Server 层解析 SQL → 查询缓存已废弃→ 分析器 → 优化器 → 执行器执行器调用存储引擎接口获取记录InnoDB 引擎查找聚簇索引中id1的行加上行锁X Lock开始事务生成undo log用于回滚和 MVCC修改内存中的数据页Buffer Pool写入redo logprepare 状态返回结果给执行器Server 层写入binlog逻辑日志InnoDB 提交事务将 redo log 改为commit 状态释放锁返回客户端 “OK”⚠️ 这就是所谓的“两阶段提交”Two-Phase Commit, 2PC确保 redo log 与 binlog 数据一致。 为什么要两阶段提交假设没有 prepare 阶段场景后果先写 redo log 成功宕机前未写 binlog主库有数据从库无数据 → 不一致先写 binlog 成功redo log 失败从库应用了变更主库没变 → 更严重✅ 两阶段提交解决这个问题如果是 prepare redo log 已写 → 崩溃恢复时检查 binlog 是否完整 → 决定是否提交如果 binlog 没写完 → 事务回滚如果 binlog 写完了 → 事务提交 这是 MySQL 实现主从复制数据强一致性的基石。 企业级使用场景 最佳实践场景实践建议数据库崩溃恢复依赖 redo log binlog 自动恢复RTO 30s主从延迟排查检查 binlog 写入时间 vs slave apply 时间GTID 复制配置使用gtid_modeON避免位点错乱半同步复制设置rpl_semi_sync_master_enabledON保证至少一个从库收到 binlog-- 查看当前事务状态 SHOW ENGINE INNODB STATUS; -- 查看 binlog 写入情况 SHOW MASTER STATUS; 高级提示MySQL 8.0.29 支持组提交优化group commit和binlog transaction compression进一步提升性能。2.什么情况下索引会失效联合索引的最左匹配原则本质是什么B树结构如何影响查询性能✅ 深度解析❌ 常见导致索引失效的情况情况示例原因对字段做函数操作WHERE YEAR(create_time) 2024无法使用索引需全表扫描类型隐式转换VARCHAR(id)vsINT查询字符集转换导致索引失效使用%开头的模糊查询LIKE %张三无法利用有序性OR 条件中部分无索引WHERE a1 OR b2b 无索引可能走全表联合索引未遵循最左前缀INDEX(a,b,c)但WHERE b2 AND c3无法命中 最左匹配原则的本质联合索引(a,b,c)在 B 树中排序规则是先按 a 排序 → a 相同再按 b 排序 → b 相同再按 c 排序所以只有满足“最左连续”的条件才能使用索引查找查询条件是否命中说明a1✅走索引a1 AND b2✅走索引a1 AND b2 AND c3✅走索引b2❌不命中跳过 aa1 AND c3⚠️ 部分命中只能用到 ac 不能利用索引顺序 注意MySQL 5.7 支持Index Condition Pushdown (ICP)可以在存储引擎层过滤 c 字段减少回表次数。 B 树结构的影响高度稳定通常 3~4 层可支持上亿数据每页 16KB扇出 ~1000磁盘友好顺序 I/O 多适合机械硬盘/SSD范围查询高效叶子节点链表连接支持BETWEEN,ORDER BYEXPLAIN SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; -- type: rangekey_len 显示使用了多少索引字节 企业级实践建议实践说明索引设计遵循“高频过滤字段优先”如(tenant_id, status, create_time)避免过度索引每个额外索引增加写成本维护 B 树使用覆盖索引减少回表SELECT id,name FROM t WHERE status1若(status,id,name)是联合索引则无需回表定期分析慢查询日志结合pt-query-digest工具识别潜在问题-- 查看索引使用情况 SHOW INDEX FROM table_name; -- 分析执行计划 EXPLAIN FORMATJSON SELECT ...;3.MySQL 如何实现可重复读Repeatable ReadMVCC 是怎么工作的为什么 RR 级别下还能出现幻读✅ 深度解析 隔离级别的选择MySQL 默认隔离级别REPEATABLE READ (RR)隔离级别脏读不可重复读幻读READ UNCOMMITTED✅✅✅READ COMMITTED❌✅✅REPEATABLE READ❌❌⚠️ InnoDB 下基本消除SERIALIZABLE❌❌❌ MVCC 工作原理Multi-Version Concurrency ControlInnoDB 通过以下字段实现版本控制DB_TRX_ID最后修改该行的事务 IDDB_ROLL_PTR指向 undo log 中的历史版本链DB_ROW_ID隐藏主键如果没有显式主键当开启事务时记录当前活跃事务列表read_view查询时判断某行是否可见如果trx_id min_trx_id→ 已提交可见如果trx_id max_trx_id→ 将来才提交不可见如果在中间 → 查看是否在m_ids列表中 → 是则不可见未提交✅ 因此同一个事务多次读取同一行看到的是第一次读取时的快照版本。 为什么 RR 下仍有“幻读”风险虽然 MVCC 解决了普通幻读快照读但当前读current read仍可能产生幻影现象。-- Session A START TRANSACTION; SELECT * FROM users WHERE age 25; -- 返回 3 条 -- Session B 插入新用户并提交 INSERT INTO users (name, age) VALUES (Tom, 25); -- Session A 再次查询 SELECT * FROM users WHERE age 25 FOR UPDATE; -- 突然多出一条这就是幻读✅ 解决方案使用间隙锁Gap Lock或临键锁Next-Key LockInnoDB 在 RR 下自动使用 Next-Key Lock 来防止幻读范围锁例如WHERE age25不仅锁住该值还锁定(20,30)区间阻止插入。 企业级建议场景建议高并发更新使用SELECT ... FOR UPDATE控制并发避免长事务减少read_view生存时间降低 undo log 占用金融类系统可考虑升级为SERIALIZABLE或使用乐观锁 版本号分布式事务结合 Seata/XA 实现跨库一致性4.线上出现慢查询CPU 飙升你如何定位和优化请描述完整排查路径。✅ 深度解析SOP 流程 定位步骤企业级 SRE 思维确认现象是单实例还是集群CPU 高是用户态us还是系统态sy是否伴随连接数暴涨查看慢查询日志SHOW VARIABLES LIKE slow_query_log; SET GLOBAL long_query_time 1; 使用 mysqldumpslow 或 pt-query-digest 分析 Top SQL。实时抓取正在执行的 SQLSHOW PROCESSLIST; -- 或更详细的 SELECT * FROM performance_schema.threads WHERE TYPEFOREGROUND; SELECT * FROM performance_schema.events_statements_current;分析执行计划EXPLAIN FORMATJSON SELECT ...; -- 关注typeALL 表示全表、key是否用索引、rows估算行数、filtered检查锁竞争SELECT * FROM sys.innodb_lock_waits\G SHOW ENGINE INNODB STATUS; -- 查看 LATEST DETECTED DEADLOCK监控系统资源使用 Prometheus Grafana 监控 QPS、TPS、InnoDB Buffer Pool Hit Rate检查磁盘 IO 是否瓶颈iostat️ 优化手段问题类型优化方式缺失索引添加复合索引注意顺序回表过多改为覆盖索引排序溢出增加sort_buffer_size或避免ORDER BY RAND()锁等待拆分大事务、减少FOR UPDATE范围统计信息不准ANALYZE TABLE更新元数据 实际案例电商订单查询-- 原始 SQL慢 SELECT * FROM orders WHERE user_id ? AND status IN (1,2,3) ORDER BY create_time DESC LIMIT 10; -- 优化后 -- 创建覆盖索引 ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); -- 查询只取必要字段 SELECT order_id, amount, status FROM orders WHERE user_id ? AND status IN (1,2,3) ORDER BY create_time DESC LIMIT 10;⏱️ 效果从 2s → 20msQPS 从 100 → 50005.分库分表后全局唯一 ID 如何生成分布式环境下如何保证查询效率与事务一致性✅ 深度解析 全局唯一 ID 方案对比方案优点缺点适用场景UUID简单、去中心化太长36位、无序、B树分裂严重日志类Snowflake自增趋势、64bit、高性能依赖时钟同步主流推荐数据库号段Segment批量获取、减少 DB 请求单点风险美团 CATRedis INCR简单可靠存在单点、网络开销中小规模TiDB 自增分布式自增仅限 TiDB 生态新架构✅ 推荐组合Snowflake 机器 ID 动态分配ZooKeeper/K8s Hostname// Java 示例 改良 Snowflake public class IdWorker { private long workerId; private long sequence 0L; private long lastTimestamp -1L; ... } 分布式事务解决方案方案说明本地消息表最终一致性适合订单→库存扣减Seata AT 模式两阶段提交支持 Spring CloudTCCTry-Confirm-Cancel高性能适用于金融交易Saga 模式长事务补偿机制GlobalTransactional public void createOrder() { orderService.create(); inventoryService.reduce(); // 异常则全局回滚 } 查询效率优化Sharding Key 设计合理如user_id作为分片键避免跨库 JOINES 辅助查询异步同步数据到 Elasticsearch 实现复杂查询GSI全局二级索引阿里云 DRDS / Apache ShardingSphere 支持异步归档冷数据提升热表性能 企业级架构参考2026 年主流应用层↓Spring Boot MyBatis Plus↓ShardingSphere-JDBC分库分表中间件↓MySQL 集群MGR 或主从 MHA↓备份XtraBackup Binlog 监控Prometheus AlertManager 审计McAudit安全合规6.并发一致性问题问题现象解决方案隔离级别要求丢失修改两个写操作冲突一个被覆盖加锁、乐观锁至少要控制并发写读脏数据读到未提交的数据提高隔离级别≥ 读已提交Read Committed不可重复读同一行两次读值不同MVCC、行锁≥ 可重复读Repeatable Read幻读同样查询结果行数变了间隙锁、串行化≥ 串行化Serializable丢失修改问题描述T1 和 T2 两个事务都对一个数据进行修改T1 先修改T2 随后修改T2 的修改覆盖了 T1 的修改顺序错乱导致结果不符合预期解决核心让对同一数据的修改串行进行不能同时读和写。加锁Locking使用事务 隔离级别例如数据库设置为“可重复读”或“串行化”隔离级别乐观锁适用于冲突少的场景。思路是先不锁提交时检查有没有人改过。版本控制读脏数据问题描述T1 修改一个数据T2 随后读取这个数据。如果 T1 撤销了这次修改那么 T2 读取的数据是脏数据。读到了未提交 可能会撤销的数据解决核心只要把隔离级别设为“读已提交”或更高就能避免脏读。提高隔离级别事务B在事务A提交之前看不到那条修改就不会读到脏数据不可重复读问题描述T2 读取一个数据T1 对该数据做了修改。如果 T2 再次读取这个数据此时读取的结果和第一次读取的结果不同。在同一个事务中两次读取同一条数据结果不一样因为中间被别的事务改了并提交了。解决核心在事务期间锁定这条数据不让别人改或者确保你能看到一致的版本。提高隔离级别 → 使用 可重复读加行锁悲观锁幻影读问题描述T1 读取某个范围的数据T2 在这个范围内插入新的数据T1 再次读取这个范围的数据此时读取的结果和和第一次读取的结果不同。在同一个事务中两次执行相同的查询结果集不一样 —— 多了几行或少了几行解决核心提高隔离级别 → 使用串行化单线程性能差使用“间隙锁Gap Lock”或“范围锁”