MySQL 详解
一、整体架构
MySQL 是分层架构，从上到下：
1. 连接层
- 连接管理、认证授权
- 连接池复用，max_connections 控制上限
- 每个连接分配一个线程
2. Server 层（核心）
| 组件 | 职责 |
|---|---|
| 查询缓存 | 8.0已移除，8.0前按SQL全文本做key，命中率低 |
| 解析器 | 词法分析+语法分析，生成AST |
| 预处理器 | 语义检查（表/列是否存在、权限） |
| 优化器 | 决定执行计划：选索引、join顺序、子查询优化等 |
| 执行器 | 调用存储引擎API，逐行/批量返回结果 |
3. 存储引擎层
插件式架构，每张表可以选不同引擎。
| 引擎 | 特点 |
|---|---|
| InnoDB（默认） | 支持事务、行锁、MVCC、外键、崩溃恢复 |
| MyISAM | 不支持事务和行锁，表锁，读性能好 |
| Memory | 内存表，重启丢数据，适合临时表 |
生产基本都用 InnoDB，以下内容都基于 InnoDB。
二、InnoDB 存储结构
整体层次
plaintext
Tablespace（表空间）
└── Segment（段：数据段、索引段、回滚段）
└── Extent（区：1MB，64个页）
└── Page（页：16KB，最小IO单位）
└── Row（行：数据行）
行格式
InnoDB 支持的行格式：Compact、Dynamic（默认）、Compressed。
每行数据隐藏列：
- DB_ROW_ID：6字节，隐藏主键（无显式主键时自动生成）
- DB_TRX_ID：6字节，最后修改的事务ID
- DB_ROLL_PTR：7字节，回滚指针，指向undo log
三、索引
B+ Tree 索引结构
InnoDB 用 B+ Tree 作为索引结构，关键特征：
- 非叶子节点只存 key，叶子节点存完整数据
- 叶子节点之间双向链表连接，支持范围扫描
- 树高度通常 2-4 层，千万级数据也就 3-4 次IO
聚簇索引 vs 二级索引
聚簇索引（主键索引）：叶子节点存完整行数据。一张表只有一个。
二级索引（非主键索引）：叶子节点存主键值。
回表
查询: SELECT * FROM user WHERE name = '张三'
执行过程:
- 在 name 索引（二级索引）上找到 '张三' → 拿到主键 id=5
- 拿 id=5 回到聚簇索引再查一次 → 拿到完整行数据
这就是"回表"：二级索引 → 主键 → 聚簇索引，两次B+树查找。
每回表一次就是一次主键索引的B+树查找，如果扫描大量行，回表成本远大于索引扫描本身。
EXPLAIN 中 type=ref/ref_or_null，Extra 没有 Using index，就是在回表。
覆盖索引（消除回表）
如果查询的列都在索引中，就不需要回表。
索引: idx_name_age (name, age)
SELECT name, age FROM user WHERE name = '张三'; -- 覆盖索引，不回表
SELECT * FROM user WHERE name = '张三'; -- 需要回表
EXPLAIN 中 Extra 出现 Using index 就是覆盖索引。
最左前缀原则
联合索引 (a, b, c)，匹配规则：
- a ✅
- a, b ✅
- a, b, c ✅
- b ❌（跳过了a）
- a, c ✅（只用到a部分）
- b, c ❌
遇到范围查询（>, <, BETWEEN, LIKE）会中断：
- WHERE a=1 AND b>2 AND c=3 → 只用到 (a, b)，c用不上索引
索引下推（ICP，Index Condition Pushdown）
MySQL 5.6+ 优化，在存储引擎层提前过滤。
索引: idx_name_age (name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 25;
无ICP：存储引擎按 name LIKE '张%' 找到所有行 → 全部回表 → Server层过滤 age=25
有ICP：存储引擎在索引中直接判断 age=25 → 不满足的不回表 → 减少回表次数
EXPLAIN 中 Extra 出现 Using index condition 就是用到了ICP。
其他索引类型
| 类型 | 说明 |
|---|---|
| 唯一索引 | 值唯一，允许NULL，插入时做唯一性检查 |
| 前缀索引 | INDEX idx(name(10))，对长字符串只索引前N个字符，节省空间但无法覆盖索引 |
| 全文索引 | FULLTEXT，中文需用 ngram 分词器 |
| 空间索引 | GIS数据，SPATIAL |
四、事务与 MVCC
ACID
| 特性 | 实现方式 |
|---|---|
| 原子性 | undo log（回滚） |
| 一致性 | 是目标，由其他三个特性保证 |
| 隔离性 | MVCC + 锁 |
| 持久性 | redo log（WAL机制） |
隔离级别
| 级别 | 脏读 | 不可重复读 | 幻读 | 实现 |
|---|---|---|---|---|
| READ UNCOMMITTED | 会 | 会 | 会 | 直接读最新数据 |
| READ COMMITTED | 不会 | 会 | 会 | 每次SQL生成新Read View |
| REPEATABLE READ（默认） | 不会 | 不会 | 不会* | 事务开始时生成Read View |
| SERIALIZABLE | 不会 | 不会 | 不会 | 加锁，串行执行 |
*RR级别下，快照读通过MVCC避免幻读，当前读通过Next-Key Lock避免幻读。
MVCC 实现机制
每行数据有 DB_TRX_ID（最后修改的事务ID）和 DB_ROLL_PTR（回滚指针指向undo log）。
Read View 四个核心字段：
- m_ids：生成Read View时，当前活跃（未提交）的事务ID列表
- min_trx_id：活跃事务最小ID
- max_trx_id：下一个将分配的事务ID
- creator_trx_id：创建该Read View的事务ID
可见性判断：
- 如果 DB_TRX_ID == creator_trx_id → 自己的修改，可见
- 如果 DB_TRX_ID < min_trx_id → 事务已提交，可见
- 如果 DB_TRX_ID >= max_trx_id → 事务在Read View之后开启，不可见
- 如果 min_trx_id <= DB_TRX_ID < max_trx_id：
- 如果在 m_ids 中 → 未提交，不可见，沿 roll_ptr 找历史版本
- 如果不在 m_ids 中 → 已提交，可见
RC vs RR：RC每次SELECT生成新Read View，RR只在事务第一次SELECT生成。
当前读 vs 快照读
- 快照读：普通SELECT，读MVCC快照
- 当前读：SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT，读最新已提交数据并加锁
五、锁机制
锁类型
| 锁 | 粒度 | 说明 |
|---|---|---|
| 共享锁（S） | 行 | 读锁，多个事务可同时持有 |
| 排他锁（X） | 行 | 写锁，互斥 |
| 意向锁（IS/IX） | 表 | 表明事务打算加行锁，快速判断表级冲突 |
| Record Lock | 行 | 锁定索引记录 |
| Gap Lock | 间隙 | 锁定索引记录之间的间隙，防幻读 |
| Next-Key Lock | 行+间隙 | Record Lock + Gap Lock，左开右闭区间 |
| 插入意向锁 | 间隙 | INSERT特殊间隙锁，不互相阻塞 |
| 自增锁（AUTO-INC） | 表 | INSERT时保护自增列 |
加锁规则（InnoDB，RR级别）
- 加锁的基本单位是 Next-Key Lock
- 查找过程中访问到的对象才会加锁
- 等值查询，唯一索引，Next-Key Lock 退化为 Record Lock
- 等值查询，向右遍历到最后一个不满足条件的值时，Next-Key Lock 退化为 Gap Lock
- 唯一索引上的范围查询会访问到不满足条件的第一个值为止
死锁
两个事务互相等待对方持有的锁。InnoDB 检测到死锁后自动回滚代价较小的事务。
-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS;
-- 查看当前锁
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM performance_schema.data_locks; -- 8.0+
避免死锁：
- 按固定顺序访问表和行
- 保持事务短小
- 合理使用索引避免锁升级
六、日志系统
redo log（物理日志）
- 保证持久性，WAL机制
- 记录"在某页某偏移改了什么值"
- 循环写，固定大小（如4个1GB文件）
- innodb_flush_log_at_trx_commit：
- 1：每次提交都刷盘（最安全）
- 2：每次提交写OS缓存，每秒刷盘
- 0：每秒写一次，可能丢1秒数据
undo log（逻辑日志）
- 保证原子性（回滚）
- 记录"做了什么操作的逆向操作"
- 也是MVCC版本链的核心
binlog（逻辑日志，Server层）
- 主从复制、数据恢复
- 三种格式：
- STATEMENT：记SQL语句，可能主从不一致
- ROW：记行变更（推荐），数据一致但文件大
- MIXED：混合
- sync_binlog：1 每次提交刷盘最安全
两阶段提交
事务提交:
- 写 redo log（prepare状态）
- 写 binlog
- 写 redo log（commit状态）
保证 redo log 和 binlog 一致性
七、查询优化
EXPLAIN 详解
EXPLAIN SELECT * FROM user WHERE name = '张三' AND age > 20;
| 字段 | 含义 | 关注点 |
|---|---|---|
| id | 执行顺序 | id越大越先执行 |
| select_type | 查询类型 | SUBQUERY、DERIVED需关注 |
| table | 访问的表 | |
| type | 访问类型 | 从好到差：system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能用到的索引 | |
| key | 实际用的索引 | |
| key_len | 索引使用长度 | 判断联合索引用了几个字段 |
| ref | 索引查找的引用 | |
| rows | 预估扫描行数 | |
| filtered | 过滤比例 | |
| Extra | 额外信息 | 重点看 |
type 重点解读：
- const：主键/唯一索引等值查询，最多1行
- eq_ref：join时被驱动表走主键/唯一索引
- ref：非唯一索引等值查询
- range：索引范围扫描（BETWEEN、>、<）
- index：全索引扫描（比ALL好一点）
- ALL：全表扫描，必须优化
Extra 重点解读：
- Using index：覆盖索引，好
- Using index condition：索引下推，好
- Using where：Server层过滤，说明存储引擎返回了多余行
- Using temporary：用了临时表，需优化
- Using filesort：额外排序，需优化
- Using join buffer：join没走索引，需优化
索引优化实战
1. 避免索引失效
-- ❌ 对索引列做函数/运算
WHERE YEAR(create_time) = 2024 -- 索引失效
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' -- ✅
-- ❌ 隐式类型转换
WHERE phone = 13800138000 -- phone是varchar，索引失效
WHERE phone = '13800138000' -- ✅
-- ❌ 隐式字符编码转换
-- utf8mb4 表 join utf8 表，utf8列索引失效
-- ❌ LIKE 左模糊
WHERE name LIKE '%张' -- 索引失效
WHERE name LIKE '张%' -- ✅
-- ❌ OR 条件部分无索引
WHERE indexed_col = 1 OR no_index_col = 2 -- 全表扫描
-- ❌ NOT IN / NOT EXISTS / !=（大部分场景）
-- 优化器可能放弃索引，看实际执行计划
2. 联合索引设计原则
- 最左前缀：等值条件列放前面，范围条件列放后面
- 高频查询驱动：根据实际查询模式设计，不是拍脑袋
- 尽量覆盖索引：把 SELECT 的列纳入索引
-- 常见查询: SELECT name, age, city FROM user WHERE age = 25 AND city = '北京'
-- 索引设计: idx_city_age_name (city, age, name)
-- city等值过滤在前，age等值过滤在中，name覆盖索引在后
3. 避免回表过多的索引
-- 如果二级索引过滤后还有大量行需要回表
-- 优化器可能直接选全表扫描（因为顺序IO比大量随机IO快）
-- 优化：用覆盖索引或调整索引列顺序减少回表
慢查询优化流程
-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过1秒记录
-- 2. 分析慢查询
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
-- 3. EXPLAIN 分析执行计划
EXPLAIN SELECT ...
-- 4. 优化步骤
-- ① 有没有走索引？→ 加索引/改SQL
-- ② type是ALL？→ 必须优化
-- ③ Extra有filesort/temporary？→ 排序或分组没走索引
-- ④ rows很大？→ 过滤条件不够精确
-- ⑤ 回表太多？→ 覆盖索引
八、其他优化
表结构优化
-- 1. 主键选择
-- ✅ 自增整型主键（顺序写入，页分裂少）
-- ❌ UUID（随机，频繁页分裂，二级索引也更大）
-- ❌ 业务主键如身份证号（太长，所有二级索引都存主键值）
-- 2. 字段类型选择
-- 整数：TINYINT > SMALLINT > INT > BIGINT，够用就行
-- 字符串：VARCHAR 变长，CHAR 定长
-- 时间：DATETIME 8字节，TIMESTAMP 4字节（2038问题）
-- 金额：DECIMAL，别用FLOAT
-- 3. 避免过多列
-- InnoDB单行最大65535字节
-- 宽表考虑垂直拆分
-- 4. NOT NULL
-- NULL值额外占1字节标记，索引统计也复杂
-- 尽量 NOT NULL + 默认值
连接优化
-- Nested Loop Join（默认）
-- 驱动表逐行匹配被驱动表，被驱动表走索引
-- Block Nested Loop Join（BNL）
-- 被驱动表无索引时，把驱动表放入join buffer，被驱动表全表扫描
-- Extra: Using join buffer (Block Nested Loop)
-- 优化：确保被驱动表的join列有索引
-- 小表做驱动表（循环次数少）
分页优化
-- ❌ 深分页，扫描10万行丢弃前99990行
SELECT * FROM user ORDER BY id LIMIT 100000, 10;
-- ✅ 延迟关联，先用索引查id再回表
SELECT * FROM user
INNER JOIN (SELECT id FROM user ORDER BY id LIMIT 100000, 10) AS t
ON user.id = t.id;
-- ✅ 游标分页（推荐）
SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 10;
COUNT 优化
-- COUNT(*) 和 COUNT(1) 等价，都统计总行数，InnoDB都走最小索引
-- COUNT(列) 统计该列非NULL行数
-- 大表估算，不必精确
EXPLAIN SELECT COUNT(*) FROM user; -- 看rows估算值
参数调优
InnoDB Buffer Pool - 最重要，尽量大
innodb_buffer_pool_size = 物理内存的 60%-80%
redo log
innodb_flush_log_at_trx_commit = 1 # 安全优先
sync_binlog = 1 # 安全优先
连接
max_connections = 500
wait_timeout = 600
排序缓冲
sort_buffer_size = 2M
join_buffer_size = 2M
binlog
binlog_format = ROW
binlog_expire_logs_seconds = 604800 # 7天
九、高频面试/实战知识点总结
| 问题 | 答案 |
|---|---|
| 为什么用B+树不用B树 | 非叶子不存数据→扇出大→树矮→IO少；叶子链表→范围查询快 |
| 为什么主键建议自增 | 顺序插入，减少页分裂，索引紧凑 |
| 二级索引为什么存主键值 | 不存行指针（页分裂时指针失效），存主键通过聚簇索引定位 |
| 回表什么场景触发 | SELECT的列不在二级索引中 |
| 覆盖索引怎么判断 | EXPLAIN Extra出现 Using index |
| MVCC解决什么问题 | 快照读不加锁，RC/RR下实现一致性非锁定读 |
| 幻读怎么解决 | RR级别：快照读靠MVCC，当前读靠Next-Key Lock |
| MySQL主从延迟怎么处理 | 半同步复制、并行复制、读自己写走主库 |
| 分库分表什么时候做 | 单表超过2000万行或2GB，且优化已到瓶颈 |
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
8
0- 0
已为社区贡献2条内容
所有评论(0)