1. MySQL面试核心要点解析作为Java开发者技术栈中不可或缺的一环MySQL的掌握程度直接影响着面试成败。我整理了一份经过实战检验的MySQL八股知识体系涵盖高频考点和易错细节这些内容曾帮助我在3个月内通过6家互联网大厂的技术面试。1.1 存储引擎选型策略InnoDB和MyISAM的本质区别不在于表面特性而在于设计哲学。InnoDB的MVCC实现通过隐藏事务ID字段和回滚指针构建版本链这种设计使得读操作不需要等待写锁释放非阻塞读通过undo log实现事务回滚二级索引查询需要回表操作实测对比在TPCC基准测试中InnoDB的并发处理能力是MyISAM的8-12倍。但MyISAM的count(*)操作确实更快因为其维护了行数计数器。重要提示MySQL 8.0已移除MyISAM的缓存池特性现在所有缓存管理都由InnoDB完成1.2 索引优化实战手册B树索引的高度计算有固定公式h ⌈log⌈m/2⌉(N1)/2⌉ 1其中m为阶数默认16KB页大小/索引字段大小N为记录数。以亿级数据为例3-4层就能覆盖。联合索引的最左匹配原则容易被误解实际上(a,b,c)索引可以用于a1、a1 AND b2、a1 AND b2 AND c3的查询但b2、c3这类查询无法使用索引范围查询后的列索引失效如a1 AND b22. 事务隔离级别深度剖析2.1 幻读问题解决方案REPEATABLE READ级别下MySQL通过间隙锁(Gap Lock)防止幻读对索引记录之间的间隙加锁阻止其他事务在间隙中插入数据Next-Key Lock 记录锁 间隙锁实测案例当执行SELECT * FROM users WHERE age 20 FOR UPDATE时对age21的记录加记录锁对(20,21)区间加间隙锁阻止其他事务插入age20.5的记录2.2 死锁检测机制InnoDB使用等待图(wait-for graph)检测死锁关键参数SHOW VARIABLES LIKE innodb_deadlock_detect; -- 死锁检测开关 SHOW VARIABLES LIKE innodb_lock_wait_timeout; -- 默认50秒典型死锁场景事务A先锁记录1再请求记录2事务B先锁记录2再请求记录1检测到循环依赖后回滚代价较小的事务3. 性能优化黄金法则3.1 EXPLAIN执行计划解密重点关注以下字段type列从优到差 system const eq_ref ref range index ALLExtra列出现Using filesort或Using temporary需警惕rows列估算扫描行数超过1万需优化优化案例某慢查询SELECT * FROM orders WHERE user_id100 AND status1优化过程原执行计划全表扫描10万行添加INDEX(user_id, status)后索引扫描3行查询时间从1200ms降至3ms3.2 连接池配置公式建议连接数计算公式最大连接数 (核心数 * 2) 有效磁盘数常用配置# HikariCP配置示例 spring.datasource.hikari.maximum-pool-size20 spring.datasource.hikari.connection-timeout30000 spring.datasource.hikari.idle-timeout6000004. 高可用架构设计4.1 主从复制原理基于binlog的复制流程Master将变更写入binlogROW格式最安全Slave的IO线程拉取binlog到relay logSQL线程重放relay log中的事件关键监控命令SHOW SLAVE STATUS\G -- 关注 -- Seconds_Behind_Master: 从库延迟秒数 -- Slave_IO_Running: IO线程状态 -- Slave_SQL_Running: SQL线程状态4.2 分库分表策略水平分片算法对比算法类型优点缺点适用场景范围分片易于扩展可能热点日志、时间序列哈希分片分布均匀扩容困难用户数据目录分片灵活单点风险复杂规则ShardingSphere配置示例spring: shardingsphere: datasource: names: ds0,ds1 sharding: tables: t_order: actual-data-nodes: ds$-{0..1}.t_order_$-{0..15} table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_$-{order_id % 16}5. 生产环境避坑指南5.1 慢查询优化实录典型慢查询特征单表扫描行数超过1万出现filesort或temporary执行时间超过500ms应急处理步骤使用SHOW PROCESSLIST定位问题会话对问题会话执行EXPLAIN FORMATJSON临时解决方案KILL [process_id]长期方案添加缺失索引或重写SQL5.2 备份恢复方案物理备份与逻辑备份对比类型工具速度恢复粒度适用场景物理xtrabackup快实例级大型数据库逻辑mysqldump慢表级小型数据库自动化备份脚本示例#!/bin/bash # 每天全备binlog增量备份 innobackupex --userbackup --passwordxxx /backup/full/ mysqladmin flush-logs # 滚动binlog6. 面试实战问题集锦高频问题清单说下MySQL的索引结构为什么用B树对比B树更低的高度、顺序访问优势、非叶子节点不存数据如何优化一个千万级大表的count(*)方案使用汇总表、Redis计数器、EXPLAIN预估事务隔离级别如何解决脏读、不可重复读、幻读各级别锁机制差异主从延迟怎么处理监控手段、并行复制、半同步复制深度问题准备建议准备2-3个实际遇到的性能问题案例能说清楚每个优化决策的权衡过程了解内部机制如change buffer、double write等7. 版本特性演进分析MySQL 8.0关键改进原子DDL数据字典事务化窗口函数支持OVER子句通用表表达式(CTE)WITH子句复用查询不可见索引测试索引影响不删除直方图统计优化非索引列查询升级检查清单测试所有复杂查询验证存储引擎兼容性检查连接器版本评估性能变化8. 监控体系搭建方案PrometheusGranafa监控体系关键指标采集# mysqld_exporter配置示例 collectors: - global_status - info_schema.innodb_metrics - perf_schema.eventsstatements报警规则示例groups: - name: MySQL rules: - alert: HighQPS expr: rate(mysql_global_status_questions[1m]) 5000 for: 5m9. 开发规范最佳实践SQL编写禁令禁止使用SELECT *明确列出字段禁止在WHERE条件使用函数如DATE(create_time)...禁止大事务单事务超过1000行禁止无索引的JOIN操作ORM使用建议// JPA正确示例 Query(value SELECT u.id, u.name FROM User u WHERE u.status :status, nativeQuery false) PageUserProjection findActiveUsers(Param(status) int status, Pageable pageable);10. 性能压测方法论sysbench基准测试流程# 准备数据 sysbench oltp_read_write --db-drivermysql prepare # 执行测试 sysbench oltp_read_write --db-drivermysql \ --threads32 --time300 run关键指标解读QPS每秒查询数5000为佳TPS每秒事务数OLTP场景核心指标95%延迟95%请求的响应时间100ms