一、存储引擎
- MySQL主要存储引擎及特点
| 引擎 | 事务支持 | 锁粒度 | 特性 | 适用场景 |
|---|---|---|---|---|
| InnoDB | ✅ | 行级锁 | 默认引擎，支持MVCC、外键，崩溃恢复能力强 | 高并发事务场景 |
| MyISAM | ❌ | 表级锁 | 全文索引、压缩、空间函数，不支持事务 | 读多写少，静态数据 |
| Archive | ❌ | 行级锁 | 仅支持INSERT/SELECT，高压缩比 | 日志存储，历史数据归档 |
| Memory | ❌ | 表级锁 | 数据存内存，哈希索引，重启数据丢失 | 缓存/映射表，中间结果集 |
- InnoDB vs MyISAM核心区别
- 事务：InnoDB支持ACID事务，MyISAM不支持。
- 锁机制：InnoDB行级锁（减少并发冲突），MyISAM表级锁（写阻塞读）。
- 索引结构：InnoDB聚簇索引（数据存主键旁），MyISAM非聚簇索引（数据与索引分离）。
- 外键：InnoDB支持，MyISAM不支持。
二、索引机制
- 索引类型与区别
- 数据结构：B+树（范围查询）、Hash（等值查询）、全文索引（文本搜索）。
- 逻辑分类：
- 主键索引：唯一且非空，聚簇索引（InnoDB）。
- 唯一索引：列值唯一，可为NULL。
- 联合索引：多列组合，遵循最左匹配原则。
- 为什么用B+树而非B树？
- B+树非叶子节点仅存键值，增大了扇出（减少树高度）。
- 叶子节点双向链表链接，支持高效范围查询。
- 计算示例：3层B+树可存储约2200万行数据（主键8B+指针6B，每页16KB）。
- 覆盖索引与回表查询
- 覆盖索引：查询字段均在索引中，无需回表（
EXPLAIN显示Using index）。 - 回表：需根据普通索引的主键ID回聚簇索引取数据，性能较低。
- 三星索引原则
- ⭐ 索引行相邻（减少I/O）
- ⭐⭐ 数据顺序与查询排序一致（避免排序）
- ⭐⭐⭐ 包含查询所有字段（避免回表）
三、事务与锁
MySQL 是一个客户端／服务器架构的软件。同一个服务器可以有多个客户端连接，每个连接称为一个会话（Session）。每个会话中的客户端都可以向服务器发出请求语句，这些请求可能属于不同的事务，因此服务器可能同时处理多个事务。
- ACID实现原理
| 特性 | 实现机制 |
|---|---|
| 原子性 | Undo Log：回滚未提交事务的修改。 |
| 持久性 | Redo Log：事务提交前写日志，崩溃恢复时重放。 |
| 隔离性 | MVCC+锁：Read View控制可见性，行锁保证写隔离。 |
| 一致性 | 由原子性、隔离性、持久性共同保证。 |
- 事务隔离级别与问题
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现方式 |
|---|---|---|---|---|
| 读未提交 | ✅ | ✅ | ✅ | 无锁 |
| 读已提交 | ❌ | ✅ | ✅ | 每次快照读生成新Read View |
| 可重复读 | ❌ | ❌ | ⚠️ | 首次快照读生成Read View（默认） |
| 串行化 | ❌ | ❌ | ❌ | 读写均加锁 |
可以通过以下语句修改事务的隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中 level 可选值：
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
原文链接：https://blog.csdn.net/qq_26893655/article/details/149711697
幻读特殊处理：InnoDB通过Next-Key Lock（Gap Lock+Record Lock）在RR级别避免幻读。
- 锁类型
- 行级锁：
- Record Lock：锁定单行。
- Gap Lock：锁定范围间隙（防插入）。
- Next-Key Lock：锁定范围+行（默认）。
- 表级锁：
- MDL锁：元数据锁（DDL阻塞DML）。
- AUTO-INC锁：自增主键插入锁。
四、SQL优化与设计
- 表设计原则
- 字段选择：最小数据类型 > 避免NULL > 简单类型（如整型优于字符串）。
- 货币存储：
DECIMAL（精确）或BIGINT（扩大倍数存整数）。 - 字符串类型：
VARCHAR：变长字符串（省空间）。CHAR：定长字符串（如邮编）。- 避免
TEXT/BLOB：分表存储或合成索引。
- 索引优化策略
- 避免索引失效：
- ❌ 对索引列计算/函数：
WHERE YEAR(create_time)=2023 - ❌ 隐式类型转换：
WHERE id='100'（id为整型）
- ❌ 对索引列计算/函数：
- 利用虚拟生成列：
ALTER TABLE users ADD name_hash BINARY(16) AS (UNHEX(MD5(name))) VIRTUAL;
CREATE INDEX idx_name_hash ON users(name_hash);
- 大表优化方案
- 分页查询：
-- 避免OFFSET 50000，改用ID过滤
SELECT * FROM orders WHERE id > 50000 LIMIT 200;
- 关联查询：
-- 先查小表ID，再关联大表
SELECT b.* FROM big_table b
JOIN (SELECT id FROM small_table LIMIT 50000,200) a ON b.id = a.id;
五、高阶机制
- 日志系统
| 日志 | 作用 | 关键特性 |
|---|---|---|
| Redo | 保证事务持久性 | 物理日志，循环写，崩溃恢复重放 |
| Undo | 事务回滚和MVCC | 逻辑日志，链式存储版本链 |
| Binlog | 主从复制和数据归档 | 逻辑日志，追加写，支持ROW/STATEMENT |
- InnoDB三大特性
- Buffer Pool：缓存数据页，LRU算法管理。
- 自适应哈希索引：自动为高频查询字段建哈希索引。
- 双写缓冲区：防止页断裂（Partial Page Write）。
- MySQL 8.0重要特性
- 原子DDL操作（DDL失败自动回滚）。
- 降序索引（真正物理降序存储）。
- 窗口函数（
ROW_NUMBER(),RANK()）。 - 通用表表达式CTE（
WITH RECURSIVE）。
六、高频面试题
- VARCHAR(50)能存多少汉字？
- UTF8：每个汉字3字节 → 最大存
(65535-1-2)/3 ≈ 21844个（无NOT NULL）。
- 自增主键的优势
- 插入顺序写入，减少页分裂 → 比随机主键（如UUID）性能高30%。
- 死锁处理
- 设置
innodb_lock_wait_timeout（默认50s）。 - 开启死锁检测（
innodb_deadlock_detect=ON），自动回滚代价小的事务。
- 分区表 vs 分表
| 方案 | 优点 | 缺点 |
|---|---|---|
| 分区表 | 单表逻辑透明 | 分区键限制，全局锁风险 |
| 分表 | 水平扩展灵活 | 需应用层路由 |
- 索引失效的11个场景
- 范围查询右侧失效：
WHERE a>10 AND b=20-- 联合索引(a,b)中b失效
- 隐式类型转换：
WHERE id = '100'-- id为整型时触发全表扫描
- 函数操作索引列：
WHERE UPPER(name)='JOHN'
- OR连接非索引列：
WHERE a=10 OR d='foo'-- d无索引则全索引失效
- LIKE通配符开头：
WHERE name LIKE '%abc'
6.自增主键不连续原因
- 事务回滚（自增值不回收）
- 唯一键冲突（已分配ID废弃）
7.NULL值对索引的影响
WHERE col IS NULL可走索引COUNT(col)忽略NULL值（推荐COUNT(*)）
附：速查公式
- B+树容量：
总行数 = 1170^(h-1) * 16（h为树高度，1170为单页指针数）- 索引长度：
UTF8字段索引长度 = 字符数×3 + 2（变长字段额外字节）
完整知识图谱
详细资料请关注面试专栏
