MySQL面试核心知识点深度解析

第一章 基础架构与存储引擎

1.1 MySQL逻辑架构解析

• 连接层：线程池、连接认证机制
• 服务层：查询缓存（8.0+已移除）、解析器、优化器、执行引擎
• 存储引擎层：插件式架构设计

1.2 InnoDB vs MyISAM核心差异

1.3 核心日志系统

• ‌Redo Log‌：物理日志，保证事务持久性（WAL机制）
• ‌Undo Log‌：逻辑日志，实现事务回滚和MVCC
• ‌Binlog‌：逻辑日志，主从复制与数据恢复

第二章 索引机制与优化（1500字）

2.1 B+Tree索引原理

sqlCopy Code-- 创建组合索引示例
CREATE INDEX idx_name_age ON users(name, age);

• 叶子节点存储数据页指针（InnoDB）或数据记录（MyISAM）
• 最左前缀原则的底层实现逻辑

2.2 索引失效场景分析

1. 隐式类型转换：WHERE name = 123（name为字符串类型）
2. 函数操作：WHERE YEAR(create_time) = 2023
3. 前导模糊查询：WHERE name LIKE '%张'
4. 未遵循最左匹配原则

2.3 索引优化策略

• 覆盖索引：减少回表操作
• 索引下推（ICP）：5.6+版本特性
• MRR优化：随机IO转顺序IO

第三章 事务与锁机制

3.1 ACID实现原理

• ‌原子性‌：Undo Log
• ‌隔离性‌：锁+MVCC
• ‌持久性‌：Redo Log
• ‌一致性‌：应用层保证

3.2 隔离级别对比

3.3 锁类型详解

• 共享锁（S锁）：SELECT ... LOCK IN SHARE MODE
• 排他锁（X锁）：SELECT ... FOR UPDATE
• 意向锁：IS/IX锁优化锁检测
• 间隙锁：解决幻读问题的关键

第四章 SQL优化与执行计划

4.1 EXPLAIN关键字段解析

CodeEXPLAIN SELECT * FROM orders WHERE user_id = 100;

• type字段：const > eq_ref > ref > range > index > ALL
• Extra字段：
• Using index：覆盖索引
• Using filesort：需要额外排序
• Using temporary：使用临时表

4.2 慢查询优化步骤

1. 定位高消耗SQL：slow_query_log
2. 分析执行计划
3. 优化索引策略
4. 重构查询语句

第五章 高可用架构设计

5.1 主从复制原理

• 异步复制 vs 半同步复制
• GTID模式的优势

5.2 常见高可用方案

1. MHA：故障自动切换
2. InnoDB Cluster：基于Group Replication
3. ProxySQL+Keepalived：读写分离方案

第六章 实战案例分析

6.1 死锁问题排查

logCopy CodeLATEST DETECTED DEADLOCK
...

• 查看SHOW ENGINE INNODB STATUS
• 调整事务顺序/降低隔离级别

6.2 分页查询优化

sqlCopy Code-- 低效写法
SELECT * FROM orders LIMIT 1000000, 20;

-- 优化方案
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
LIMIT 20;