当前位置：首页 > article >正文

MySQL面试知识点详解

article 2025/9/15 9:29:05

一、MySQL基础架构

1. MySQL逻辑架构

MySQL采用分层架构设计，主要分为：

连接层：处理客户端连接、授权认证等
服务层：包含查询解析、分析、优化、缓存等
引擎层：负责数据存储和提取（InnoDB、MyISAM等）

2. 查询执行流程

客户端发送SQL语句
连接器验证身份
查询缓存（MySQL 8.0已移除）
分析器进行词法语法分析
优化器生成执行计划
执行器调用存储引擎接口执行

二、存储引擎对比

InnoDB vs MyISAM

特性	InnoDB	MyISAM
事务支持	支持	不支持
锁粒度	行锁	表锁
外键	支持	不支持
崩溃恢复	支持	不支持
全文索引	MySQL 5.6+支持	支持
存储文件	.frm, .ibd	.frm, .MYD, .MYI
适合场景	高并发写/事务型应用	读多写少/非事务应用

三、索引原理与优化

1. 索引类型

B+树索引：最常用，适合范围查询
哈希索引：精确匹配快，不支持范围查询
全文索引：用于文本搜索
空间索引：用于地理数据

2. B+树索引特点

多路平衡查找树
非叶子节点只存键值
叶子节点形成有序链表
通常3-4层就能存储大量数据

3. 索引优化原则

最左前缀原则
避免在索引列上使用函数
选择合适的索引列顺序
使用覆盖索引减少回表
避免过度索引

四、事务与锁机制

1. 事务特性（ACID）

原子性(Atomicity)：事务不可分割
一致性(Consistency)：数据状态一致
隔离性(Isolation)：事务间相互隔离
持久性(Durability)：提交后永久生效

2. 事务隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✓	✓	✓
READ COMMITTED	×	✓	✓
REPEATABLE READ	×	×	✓
SERIALIZABLE	×	×	×

3. 锁类型

共享锁(S锁)：读锁，多个事务可同时持有
排他锁(X锁)：写锁，独占资源
意向锁：表级锁，表明事务将要获取的行锁类型
间隙锁：锁定索引记录间隙，防止幻读
临键锁：记录锁+间隙锁组合

五、SQL优化技巧

1. EXPLAIN执行计划分析

关键字段：

type：访问类型（const > eq_ref > ref > range > index > ALL）
key：实际使用的索引
rows：预估扫描行数
Extra：额外信息（Using index/Using filesort等）

2. 常见优化方法

避免SELECT *，只查询需要的列
合理使用JOIN，小表驱动大表
避免在WHERE子句中对字段进行NULL值判断
使用LIMIT分页时优化大偏移量查询
避免使用OR连接条件，考虑使用UNION ALL

六、高可用与性能调优

1. 主从复制原理

主库将变更写入binlog
从库IO线程读取主库binlog
从库SQL线程重放binlog中的事件

2. 分库分表策略

垂直拆分：按业务维度拆分
水平拆分：按数据行拆分
常见中间件：MyCat、ShardingSphere

3. 性能调优参数

ini

复制

下载

# 缓冲池大小（推荐总内存的50-70%）
innodb_buffer_pool_size = 4G# 日志文件大小
innodb_log_file_size = 256M# 连接数设置
max_connections = 500
thread_cache_size = 50# 查询缓存（MySQL 8.0已移除）
query_cache_size = 0

七、常见面试题

为什么使用B+树而不是B树？
- B+树非叶子节点不存数据，能容纳更多键值
- 叶子节点形成链表，范围查询更高效
- 查询性能更稳定（任何查询都要到叶子节点）
什么是回表查询？如何避免？
- 回表：通过二级索引查到主键后，再通过主键查完整数据
- 避免：使用覆盖索引（查询列都在索引中）
MVCC实现原理？
- 通过版本链和ReadView实现
- 每行记录有隐藏字段：DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针）
- ReadView包含：m_ids（活跃事务列表）、min_trx_id、max_trx_id等
大表优化方案？
- 分库分表
- 读写分离
- 冷热数据分离
- 适当增加冗余字段减少JOIN
如何解决死锁问题？
- 设置锁等待超时参数：innodb_lock_wait_timeout
- 分析死锁日志（show engine innodb status）
- 保证事务中锁的获取顺序一致
- 尽量缩小事务范围