MySQL 常见面试题汇总

小鸡脚来咯

495人浏览 · 2026-03-09 09:20:12

小鸡脚来咯 · 2026-03-09 09:20:12 发布

一、基础与架构

1. MySQL 逻辑架构

客户端 → 连接器 → 查询缓存(8.0移除) → 分析器 → 优化器 → 执行器 → 存储引擎

2. 存储引擎对比

特性	InnoDB	MyISAM
事务	支持	不支持
锁粒度	行级锁	表级锁
外键	支持	不支持
缓存	数据+索引	仅索引
主键	必须有	可以没有
崩溃恢复	支持	较差
应用场景	OLTP	OLAP/只读

二、索引

1. 索引类型

B+Tree索引：默认索引，适用于范围查询
Hash索引：Memory引擎，等值查询快
全文索引：MyISAM/InnoDB，全文搜索
空间索引：MyISAM，地理数据

2. B+Tree vs B-Tree

B+Tree:
- 非叶子节点只存key，不存data
- 叶子节点双向链表连接
- 所有数据在叶子节点
- 更适合范围查询

3. 聚簇索引 vs 非聚簇索引

-- 聚簇索引：InnoDB，数据与索引一起存储
-- 非聚簇索引：MyISAM，数据与索引分开存储
-- 回表查询：先查二级索引，再查主键索引

4. 索引优化原则

最左前缀原则
避免在索引列上做计算、函数、类型转换
尽量使用覆盖索引
字符串前缀索引
索引下推（5.6+）

三、事务

1. ACID 特性

Atomicity：原子性，undo log保证
Consistency：一致性，其他三者保证
Isolation：隔离性，锁/MVCC保证
Durability：持久性，redo log保证

2. 事务隔离级别

-- 查看：SELECT @@tx_isolation;
-- 设置：SET SESSION TRANSACTION ISOLATION LEVEL ...;

级别	脏读	不可重复读	幻读	实现方式
READ UNCOMMITTED	✓	✓	✓	无锁
READ COMMITTED	✗	✓	✓	行锁，无间隙锁
REPEATABLE READ	✗	✗	△	MVCC+间隙锁
SERIALIZABLE	✗	✗	✗	全表锁

3. MVCC 多版本并发控制

-- 每行数据有隐藏字段：
-- DB_TRX_ID：最后修改事务ID
-- DB_ROLL_PTR：回滚指针
-- DB_ROW_ID：行ID
-- 通过ReadView实现一致性视图

四、锁机制

1. 锁类型

-- 行级锁
S锁（共享锁）：SELECT ... LOCK IN SHARE MODE
X锁（排他锁）：SELECT ... FOR UPDATE

-- 表级锁
LOCK TABLES ... READ/WRITE

2. 锁算法

Record Lock：记录锁，锁单行
Gap Lock：间隙锁，锁范围，防止幻读
Next-Key Lock：记录锁+间隙锁

3. 死锁处理

-- 查看：SHOW ENGINE INNODB STATUS\G
-- 默认策略：等待超时或最小代价回滚

五、SQL 优化

1. EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE age > 20;

关键字段：

type：ALL < index < range < ref < eq_ref < const
key：实际使用的索引
rows：预估扫描行数
Extra：Using index(覆盖索引), Using filesort, Using temporary

2. 优化建议

避免 SELECT *
合理使用索引
避免大事务
分页优化
避免 NULL 值
批量操作

3. 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

-- 分析工具
mysqldumpslow
pt-query-digest

六、存储与备份

1. 日志文件

错误日志：error.log
二进制日志：binlog，主从复制/数据恢复
查询日志：general.log
慢查询日志：slow-query.log
中继日志：relay.log，从库使用
重做日志：redo log，崩溃恢复
回滚日志：undo log，事务回滚

2. 备份策略

# 物理备份
mysqldump -u root -p dbname > backup.sql

# 物理备份
xtrabackup --backup --target-dir=/backup/

# 恢复
mysql -u root -p dbname < backup.sql

七、主从复制

1. 复制原理

主库：写操作 → binlog → dump线程 → 网络
从库：IO线程 → relay log → SQL线程 → 应用日志

2. 复制模式

异步复制（默认）
半同步复制
全同步复制
组复制（MGR）

3. 搭建步骤

-- 主库配置
server-id=1
log-bin=mysql-bin
sync_binlog=1

-- 从库配置
server-id=2
relay-log=mysql-relay-bin
read-only=1

八、分库分表

1. 拆分策略

垂直拆分：按业务分库
水平拆分：按规则分表
常见路由：取模、范围、哈希、一致性哈希

2. 分页问题

-- 全局分页：汇总各分表结果
-- 建议：业务层避免深分页

3. 分布式ID

UUID
雪花算法
数据库自增（步长区分）
Redis/MongoDB生成

九、高可用

1. 主从切换

手动切换
MHA（Master High Availability）
Orchestrator

2. 读写分离

应用层实现
中间件：MyCat、ShardingSphere
Proxy：MySQL Router、ProxySQL

3. 集群方案

主从复制
MGR（MySQL Group Replication）
Galera Cluster
NDB Cluster

十、性能调优

1. 参数调优

# 连接相关
max_connections = 1000
thread_cache_size = 64

# 缓冲池
innodb_buffer_pool_size = 总内存的70-80%
innodb_log_file_size = 1-2G
innodb_flush_log_at_trx_commit = 1/2

# 查询缓存（5.7有，8.0移除）
query_cache_size = 0
query_cache_type = 0

2. 硬件优化

SSD硬盘
足够内存
CPU多核
RAID配置

十一、常见问题

1. COUNT() vs COUNT(1) vs COUNT(列)*

-- 效率：COUNT(*) ≈ COUNT(1) > COUNT(列)
-- COUNT(列) 会排除NULL值

2. CHAR vs VARCHAR

CHAR：定长，存储快
VARCHAR：变长，节省空间
建议：长度固定用CHAR，否则用VARCHAR

3. JOIN 优化

-- 驱动表选择：小表驱动大表
-- 避免笛卡尔积
-- 使用索引

4. 大表优化

-- 历史数据归档
-- 分区表
-- 垂直拆分
-- 读写分离

十二、MySQL 8.0 新特性

1. 新功能

窗口函数
通用表表达式（CTE）
不可见索引
降序索引
原子DDL
JSON增强

2. 性能提升

直方图统计信息
资源组
并行查询
临时表性能改进

十三、实战案例

1. 索引失效场景

-- 1. 隐式类型转换
WHERE phone = 13800138000  -- phone是varchar

-- 2. 对索引列做运算
WHERE YEAR(create_time) = 2024

-- 3. 使用函数
WHERE LEFT(name, 3) = 'abc'

-- 4. 模糊查询前缀
WHERE name LIKE '%abc%'

2. 死锁分析

-- 场景：事务A先删后插，事务B先插后删
-- 解决：统一操作顺序

3. 分页优化

-- 低效：
SELECT * FROM table LIMIT 1000000, 20;

-- 优化1：记录上次最大ID
SELECT * FROM table WHERE id > 1000000 LIMIT 20;

-- 优化2：JOIN优化
SELECT * FROM table a
JOIN (SELECT id FROM table LIMIT 1000000, 20) b
ON a.id = b.id;

面试准备建议：