MySQL知识体系（DS整理）

一、知识图谱

二、数据库基础

略。

三、核心功能

3.1 存储引擎

3.1.1 知识图谱

3.1.2 什么是存储引擎？

1. 定义与作用

存储引擎（Storage Engine）是数据库管理系统中负责数据存储、检索和管理的核心组件，它决定了：

• 数据如何存储在磁盘上
• 如何建立索引
• 事务是否支持
• 并发控制机制
• 崩溃恢复能力

类比：数据库相当于一辆车，存储引擎是它的发动机，决定了车辆的动力特性（燃油车、电动车、混合动力）。

2. 核心功能模块

(1) 数据存储结构

• 文件组织：如 InnoDB 的 .ibd 文件 vs MyISAM 的 .MYD/.MYI
• 页管理：数据按页（通常 16KB）存储，通过 B+Tree 组织
• 行格式：Compact/Redundant（MyISAM） vs Dynamic/Compressed（InnoDB）

(2) 事务管理

• 实现机制：
  • Undo Log：记录数据修改前的状态（用于回滚）
  • Redo Log：记录物理修改（用于崩溃恢复）
  • MVCC：多版本并发控制（如 InnoDB 的 Read View）

(3) 锁机制

• 共享锁（S Lock）：读锁，允许其他读操作
• 排他锁（X Lock）：写锁，独占资源
• 高级锁类型：
  • 意向锁（Intention Lock）
  • 间隙锁（Gap Lock）
  • 临键锁（Next-Key Lock）

(4) 缓存管理

• 缓冲池（Buffer Pool）：InnoDB 的数据页缓存
• 键缓存（Key Cache）：MyISAM 的索引缓存
• 查询缓存（Query Cache）：已废弃的全局缓存

3.1.3 InnoDB架构

InnoDB 是 MySQL 最核心的存储引擎之一，支持事务、行级锁、多版本并发控制（MVCC）等关键特性，适用于高并发事务场景。

1. 存储结构与数据组织

InnoDB 的数据存储基于表空间（Tablespace）模型，包括：

• 系统表空间（ibdata1）：存储元数据、双写缓冲（DoubleWrite Buffer）、Undo 日志等。
• 独立表空间（*.ibd）：每张表单独存储数据与索引（通过 innodb_file_per_table 配置）。
• 段（Segment）：数据段（B+树叶子节点）、索引段（B+树非叶子节点）等。
• 页（Page）：基本存储单元（默认 16KB），存储行数据（Row）和索引条目。
• 行（Row）：采用紧凑格式存储，支持变长字段（如 VARCHAR）和行溢出机制。

关键点：数据按主键顺序组织（聚集索引），主键查询效率极高；非主键索引（二级索引）存储主键值，需回表查询。

2. 事务与 ACID 特性

InnoDB 通过以下机制实现事务的 ACID（原子性、一致性、隔离性、持久性）：

• 原子性（Atomicity）：依赖 Undo Log，记录事务修改前的旧版本数据，用于回滚。
• 持久性（Durability）：依赖 Redo Log（物理日志），事务提交前先将修改写入 Redo Log，确保崩溃后可通过 Redo 恢复。
• 隔离性（Isolation）：通过 MVCC（多版本并发控制） 和 锁机制 实现不同隔离级别（默认 可重复读 Repeatable Read）。
• 一致性（Consistency）：由应用逻辑、数据库约束（如外键）和上述机制共同保证。

3. 锁与并发控制

• 行级锁：支持共享锁（S Lock）与排他锁（X Lock），粒度细，并发度高。
  • 记录锁（Record Lock）：锁定索引记录。
  • 间隙锁（Gap Lock）：锁定索引记录间的间隙，防止幻读。
  • 临键锁（Next-Key Lock）：记录锁 + 间隙锁，解决幻读问题。
• 表级锁：如元数据锁（MDL），用于 DDL 操作。
• 死锁处理：自动检测并通过回滚较小事务解除死锁。

4. MVCC（多版本并发控制）

• 原理：每个事务启动时获取一个唯一的事务 ID（trx_id），通过 Undo Log 维护数据的多版本快照。
• 可见性规则：基于 Read View（活跃事务 ID 列表）判断数据版本是否可见。
  • 若数据版本的 trx_id 小于所有活跃事务 ID，则可见。
  • 若数据版本由当前事务自身修改，则可见。
• 优势：读操作无需加锁，读写不冲突，提升并发性能。

5. 索引与 B+ 树

• 聚集索引（Clustered Index）：按主键构建的 B+ 树，叶子节点存储完整行数据。
• 二级索引（Secondary Index）：按非主键列构建的 B+ 树，叶子节点存储主键值。
• 自适应哈希索引（AHI）：自动缓存热点索引页的哈希表，加速等值查询。
• B+ 树优势：有序存储、范围查询高效、叶子节点链表连接便于全表扫描。

6. 缓冲池与日志

• 缓冲池（Buffer Pool）：内存缓存数据页和索引页，减少磁盘 I/O。
  • LRU 算法：管理页的淘汰策略。
  • Change Buffer：缓存非唯一索引的更新操作，减少随机 I/O。
• 日志系统：
  • Redo Log：顺序写入，保证事务持久性；采用 WAL（Write-Ahead Logging） 机制。
  • Undo Log：记录事务修改前的数据版本，支持回滚和 MVCC。

7. 文件与物理结构

• 表空间文件（.ibd）：存储表数据和索引。
• Redo Log 文件（ib_logfile*）：循环写入，记录物理修改。
• Undo 表空间：存储 Undo Log，支持独立表空间配置。

总结:

InnoDB 通过 B+ 树索引、行级锁、MVCC、Redo/Undo Log 等技术，在保证事务 ACID 的同时实现了高并发和高性能。其设计充分权衡了磁盘 I/O、内存管理和并发控制，是现代数据库引擎的经典范例。对于开发者而言，合理设计索引、控制事务粒度和隔离级别是优化 InnoDB 性能的关键。

3.1.4 MyISam引擎

MySQL 的 MyISAM 存储引擎是早期版本（如 MySQL 5.5 之前）的默认存储引擎，其设计目标是以高性能处理大量读操作，但在事务支持、并发写入和崩溃恢复等方面存在明显局限性。以下是 MyISAM 引擎的核心技术原理和实现细节：

1. 存储结构

MyISAM 将每个表的数据和索引分别存储在磁盘上的两个独立文件中：

• .MYD 文件（MYData）：存储表的实际数据（所有行记录）。
• .MYI 文件（MYIndex）：存储表的索引数据（B-Tree 结构）。
• .frm 文件：存储表的结构定义（如字段、类型等，与其他引擎共用）。

这种物理分离的设计使得数据插入和索引更新的效率较高，但牺牲了事务和行级锁的支持。

2. 索引机制

MyISAM 默认使用 B-Tree 结构存储索引（支持主键索引和非主键索引）。

• B-Tree 索引：
  • 非聚集索引：索引和数据文件物理分离，索引中仅存储指向数据行的指针（行在 .MYD 文件中的偏移量）。
  • 全文索引（FULLTEXT）支持：MyISAM 提供对文本字段的全文检索功能（但性能和功能弱于现代搜索引擎）。
• 查询优化
  • 对于读操作，MyISAM 能快速定位索引并读取数据，适合 静态表 或 读多写少 的场景。
  • 写入时需要更新索引和数据文件，可能导致性能下降。

3. 锁机制

• 表级锁：MyISAM 仅支持表级锁（Table-Level Locking），即任何写操作（INSERT/UPDATE/DELETE）都会锁定整个表。
  • 读锁与写锁互斥：当一个会话对表进行写操作时，其他会话的读写操作会被阻塞。
  • 读锁共享：多个会话可以同时持有读锁。
  • 锁竞争问题：高并发写入场景下，表级锁会成为性能瓶颈。

4. 事务与崩溃恢复

• 不支持事务：
  • MyISAM 不提供 ACID 事务支持，无法保证数据操作的原子性和一致性。
  • 例如：批量插入中途失败时，已插入的数据不会回滚，导致数据不一致。
• 崩溃恢复能力弱：
  • 服务器崩溃或意外断电后，MyISAM 表可能损坏，需通过 CHECK TABLE 和 REPAIR TABLE 命令手动修复。
  • 无事务日志（如 InnoDB 的 Redo Log），恢复过程依赖数据文件的完整性。

5. 性能特点

• 优势：
  • 读取速度快：数据与索引分离，B-Tree 结构优化读操作。
  • 全表扫描高效：适合无复杂查询条件的统计类操作。
  • 存储空间占用低：相比 InnoDB，MyISAM 的存储压缩率更高（尤其是静态表）。
• 劣势：
  • 写入性能差：表级锁导致并发写入效率低下。
  • 不支持行级锁和 MVCC：无法处理高并发更新场景。
  • 无外键约束：依赖应用层维护数据完整性。

6. 适用场景

• 只读或读多写少的场景：例如数据仓库、日志表、静态配置表。
• 全文索引需求：在早期版本中，MyISAM 是唯一支持全文索引的引擎（MySQL 5.6+ 后 InnoDB 也支持）。
• 临时表：MySQL 内部临时表默认使用 MyISAM（内存不足时转为磁盘临时表）。

总结

MyISAM 的核心设计是牺牲事务和并发能力，换取读性能和高存储效率。 在现代 MySQL 版本中，InnoDB 已成为默认引擎（自 MySQL 5.5 起），因其支持事务、行级锁和崩溃恢复，更适合 OLTP 场景。 MyISAM 仅适用于特定场景（如只读表、临时表），在需要高并发或数据一致性时建议使用 InnoDB。

3.1.5 性能对比

3.2 索引系统

MySQL 的索引系统是数据库高效查询的核心机制，它通过合理的数据结构设计，显著提升数据检索速度。

3.2.1 索引的作用

• 加速查询：避免全表扫描，快速定位数据。
• 保证数据唯一性：唯一索引（Unique Index）防止重复数据。
• 优化排序和分组：索引天然有序，减少排序操作开销。
• 支持外键约束：通过索引维护表间关联关系。

3.2.2. 索引的底层数据结构

1. B+Tree 索引（主流）

• 适用场景：范围查询、排序、精确查找。
• 结构特点：
  • 平衡树结构，所有叶子节点深度相同。
  • 非叶子节点存储键值和指针，叶子节点存储数据（InnoDB）或数据指针（MyISAM）。
  • 叶子节点通过双向链表连接，支持高效范围遍历。
• 聚簇索引（InnoDB）：
  • 主键索引的叶子节点直接存储行数据。
  • 二级索引的叶子节点存储主键值，需回表查询。

2. Hash 索引

• 适用场景：精确等值查询（如 =、IN）。
• 实现方式：
  • 基于哈希表，哈希函数映射键值到桶（Bucket）。
  • 查询时间复杂度接近 O(1)，但不支持范围查询。
• 限制：
  • Memory 引擎默认使用哈希索引。
  • InnoDB 支持自适应哈希索引（自动优化高频访问页）。

3. 全文索引（FULLTEXT）

• 适用场景：文本模糊匹配（如 MATCH ... AGAINST）。
• 实现方式：基于倒排索引（关键词到文档的映射），支持自然语言和布尔搜索。

4. 空间索引（R-Tree）

• 适用场景：地理空间数据（如 GEOMETRY 类型）。
• 结构特点：基于 R-Tree，支持空间范围查询（如 MBRContains）。

3.2.3 索引分类（逻辑层面）

1. 主键索引（Primary Key）
   • 唯一且非空，每表仅一个。InnoDB 中为主键聚簇索引。
2. 唯一索引（Unique Index)
   • 列值唯一，允许单个 NULL 值。
3. 普通索引（Secondary Index）
   • 无唯一约束，仅加速查询。
4. 联合索引（Composite Index）
   • 多列组合索引，遵循 最左前缀原则：
     • 索引 (a, b, c) 可优化 WHERE a=1，但无法优化 WHERE b=2。
5. 覆盖索引（Covering Index）**
   • 索引包含查询所需所有字段，避免回表。undefined-示例：索引 (a, b)，查询 SELECT a, b FROM table WHERE a=1。

3.2.4 索引的代价

1. 存储空间：额外占用磁盘（尤其 B+Tree）。
2. 维护成本：
   • 数据增删改需同步更新索引。
   • 频繁写入可能导致页分裂和碎片化。
3. 优化器误选：错误索引可能导致性能下降。

3.2.5 索引使用策略

1. 适合创建索引的场景
   • WHERE 条件中的高频查询字段。
   • 常用于排序（ORDER BY）或分组（GROUP BY）的字段。
   • 多表 JOIN 的关联字段。
   • 高选择性（唯一值比例高）的列。
2. 避免索引失效的常见问题
   • 隐式类型转换：如字符串字段用数字查询。
   • 对索引列使用函数：如 WHERE YEAR(create_time)=2023。
   • OR 连接非索引字段：如 WHERE a=1 OR b=2（若 b 无索引）。
   • 违反最左前缀原则：联合索引未按顺序使用。
3. 索引优化工具
   • EXPLAIN：分析执行计划（关注 key 和 type 字段）。
   • 慢查询日志：定位低效 SQL。
   • 索引统计信息：SHOW INDEX FROM table 查看基数（Cardinality）。

3.2.6 存储引擎的索引差异

3.2.7 最佳实践

1. 优先使用主键查询：InnoDB 中主键访问效率最高。
2. 避免过度索引：按需创建，定期清理无用索引。
3. 长字段使用前缀索引：如 INDEX(email(10))。
4. 监控索引效果：定期分析慢查询，调整策略。

3.2.8 总结

MySQL 索引通过 B+Tree、Hash 等结构以空间换时间加速查询，需权衡读写性能和维护成本。合理设计需结合业务场景、数据分布及存储引擎特性，并借助工具持续优化。

3.3 事务管理

3.3.1 事务的基本概念

事务（Transaction） 是数据库操作的最小逻辑单元，用于保证一组数据库操作要么全部成功，要么全部失败，确保数据一致性。

其核心特性遵循 ACID 原则：

• 原子性（Atomicity）：事务中的操作要么全部提交，要么全部回滚。
• 一致性（Consistency）：事务执行后，数据库从一个有效状态转换到另一个有效状态。
• 隔离性（Isolation）：并发事务之间互不干扰。
• 持久性（Durability）：事务提交后，数据永久保存（即使系统崩溃）。

3.3.2 事务的实现机制（以 InnoDB 为例）

1. 事务的原子性与持久性

• Redo Log（重做日志）：
  • 记录事务对数据页的物理修改，用于崩溃恢复。
  • 事务提交时，Redo Log 先写入磁盘（innodb_flush_log_at_trx_commit=1 确保持久性）。
• Undo Log（回滚日志）：
  • 记录事务修改前的数据镜像，用于回滚和 MVCC。
  • 存储于回滚段（Rollback Segment）中。

2. 事务的隔离性

• 锁机制：
  • 行级锁：InnoDB 支持共享锁（S锁）和排他锁（X锁）。
  • 表级锁：如意向锁（IS/IX）辅助行锁管理。
  • 间隙锁（Gap Lock）：防止幻读（仅在 REPEATABLE READ 隔离级别下生效）。
• MVCC（多版本并发控制）：
  • 通过 Undo Log 维护数据的多个版本，实现非阻塞读。
  • 每个事务启动时分配唯一的事务 ID（trx_id），通过 ReadView 判断数据可见性。

3. 事务的一致性

• 通过约束（主键、外键、唯一性）、触发器和应用逻辑共同保障。

3.3.3 事务的隔离级别

MySQL 支持四种隔离级别（由低到高）：

*InnoDB 在 RR 级别通过间隙锁（Gap Lock）和 Next-Key Lock 避免幻读。

3.3.4 事务的常见问题与解决方案

1. 脏读（Dirty Read）

• 现象：事务 A 读取到事务 B 未提交的数据。
• 解决：提升隔离级别至 READ COMMITTED。

2. 不可重复读（Non-Repeatable Read）

• 现象：事务 A 多次读取同一数据，结果不一致（因事务 B 修改了数据）。
• 解决：提升隔离级别至 REPEATABLE READ。

3. 幻读（Phantom Read）

• 现象：事务 A 读取到事务 B 新增或删除的行。
• 解决：在 REPEATABLE READ 下，InnoDB 通过间隙锁避免。

4. 死锁（Deadlock）

• 现象：多个事务互相等待对方释放锁。
• 解决：
  • InnoDB 自动检测并回滚代价较小的事务。
  • 优化事务设计：减少锁范围、按相同顺序访问资源。

3.3.5 事务性能优化

1. 控制事务粒度

• 避免长事务：减少锁持有时间，降低死锁概率。
• 拆分大事务：将批量操作拆分为小事务（如分批提交）。

2. 合理选择隔离级别

• 默认使用 REPEATABLE READ，高并发场景可降级为 READ COMMITTED。

3. 监控事务状态

-- 查看当前运行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

4. 参数调优

• innodb_lock_wait_timeout：锁等待超时时间（默认 50 秒）。
• innodb_rollback_on_timeout：超时是否回滚整个事务（默认 OFF）。

3.3.6 事务与存储引擎

• InnoDB：完整支持 ACID 事务，是 MySQL 默认引擎。
• MyISAM：不支持事务。
• NDB Cluster：支持事务，但主要用于分布式场景。

3.3.7 总结

MySQL 的事务管理通过 Redo/Undo Log、锁机制 和 MVCC 实现 ACID 特性，其中 InnoDB 的默认隔离级别（REPEATABLE READ）在性能与一致性之间取得了平衡。实际开发中需根据业务场景合理设计事务，避免死锁和性能瓶颈，并通过监控工具持续优化。

3.4 锁系统

3.4.1 锁的作用与分类

MySQL 的锁系统用于协调并发事务对共享资源的访问，保证数据一致性和事务隔离性。

主要分类如下：

1. 按锁的粒度划分

2. 按锁的兼容性划分

3. InnoDB 的特殊锁

3.4.2 锁的兼容性与冲突矩阵

说明： 共享锁 (S) 与意向共享锁 (IS) 兼容，但与排他锁 (X) 冲突。undefined意向锁 (IS/IX) 之间兼容，但 IX 与 S 锁冲突。

3.4.3 InnoDB 行锁的实现机制

1. 锁的加锁流程

• 步骤 1：对表加意向锁（IS 或 IX）。
• 步骤 2：对目标行加行级锁（S 或 X）。
• 步骤 3：若涉及间隙（如范围查询），加间隙锁或临键锁。

2. 锁的释放时机

• 事务提交或回滚时释放所有锁（隐式释放）。
• 显式释放：UNLOCK TABLES（仅表级锁）。

3. 锁的优化策略

• 索引设计：合理使用索引缩小锁范围（避免全表扫描导致锁表）。
• 事务拆分：减少长事务持有锁的时间。
• 隔离级别：降低隔离级别（如从 RR 降为 RC）减少间隙锁使用。

3.4.4 死锁的产生与处理

1. 死锁条件

• 互斥：资源被独占。
• 占有且等待：事务持有资源并等待其他资源。
• 不可剥夺：资源只能由持有者释放。
• 循环等待：事务间形成环形等待链。

2. 死锁示例

-- 事务 A
BEGIN;
UPDATE t SET val=1 WHERE id=1;  -- 持有 id=1 的 X 锁
UPDATE t SET val=2 WHERE id=2;  -- 等待 id=2 的 X 锁

-- 事务 B
BEGIN;
UPDATE t SET val=2 WHERE id=2;  -- 持有 id=2 的 X 锁
UPDATE t SET val=1 WHERE id=1;  -- 等待 id=1 的 X 锁（死锁）

3. 死锁解决

自动检测：InnoDB 通过等待图（Wait-for Graph）检测死锁，回滚代价较小的事务。

• 手动处理：

SHOW ENGINE INNODB STATUS;  -- 查看死锁日志
KILL [事务ID];              -- 终止指定事务

3.4.5 锁的监控与诊断

1. 锁信息查询

-- 查看当前锁状态
SELECT * FROM information_schema.INNODB_LOCKS;      -- 锁详情
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 锁等待关系

-- 查看事务与锁关联
SELECT * FROM information_schema.INNODB_TRX;        -- 当前运行的事务

2. 锁等待超时参数

• innodb_lock_wait_timeout：锁等待超时时间（默认 50 秒）。
• innodb_rollback_on_timeout：超时是否回滚整个事务（默认 OFF，仅回滚最后语句）。

3.4.6 不同存储引擎的锁差异

3.4.7 最佳实践

• 减少锁竞争：
  • 避免长事务，及时提交。
  • 使用低隔离级别（如 READ COMMITTED）。
• 优化 SQL：
  • 通过索引缩小锁定范围。
  • 避免全表扫描（如 WHERE 条件未命中索引）。
• 死锁预防：
  • 按固定顺序访问资源。
  • 使用 SELECT ... FOR UPDATE 提前锁定必要行。

3.4.8 总结

MySQL 的锁系统通过多粒度锁（表锁、行锁）和复杂锁类型（间隙锁、临键锁）平衡并发性能与数据一致性。InnoDB 的行级锁和 MVCC 机制在高并发场景下表现优异，但需合理设计事务与索引以避免死锁和性能瓶颈。监控工具（如 information_schema）和参数调优是优化锁系统的关键手段。

四、性能优化

4.1 执行计划分析

4.1.1 获取执行计划

• 使用EXPLAIN或EXPLAIN FORMAT=TREE（MySQL 8.0+）查看查询的执行计划：EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY name;4.1.2 核心列解析执行计划结果中的关键列及其含义：
• id
  • 作用：查询的序列号，标识执行顺序。
  • 规则：相同id按顺序执行；不同id，id值大的先执行（如子查询）。
• select_type
  • 常见类型：
    • SIMPLE：简单查询（无子查询或UNION）。
    • PRIMARY：外层主查询。
    • SUBQUERY：子查询。
    • DERIVED：派生表（FROM子句中的子查询）。
• table
  • 作用：显示当前行操作的表名，可能是别名或派生表名（如<derived2>）。
• type（访问类型，从优到差）
  • const：通过主键或唯一索引等值查询。
  • eq_ref：联表查询时，主键或唯一索引的等值匹配。
  • ref：非唯一索引的等值查询。
  • range：索引范围扫描（如BETWEEN, >）。
  • index：全索引扫描。
  • ALL：全表扫描（需优化）。
• possible_keys
  • 作用：可能使用的索引，但不一定实际使用。
• key
  • 作用：实际使用的索引。若为NULL，表示未使用索引。
• rows
  • 作用：预估需要扫描的行数，越小越好。
• Extra
  • 关键提示：
    • Using index：覆盖索引，无需回表。
    • Using where：使用WHERE过滤数据。
    • Using temporary：使用临时表（GROUP BY/ORDER BY未走索引时常见）。
    • Using filesort：额外排序（需检查是否可优化为索引排序）。

4.1.3 优化分析流程

1. 定位低效步骤：
   • 检查type为ALL或index的行，优先优化全表扫描或全索引扫描。
   • 观察rows较大的步骤，确认是否可通过索引减少扫描行数。
2. 索引有效性验证：
   • 若possible_keys有值但key为NULL，可能缺少合适索引或优化器认为全表更快。
   • 考虑使用FORCE INDEX测试或优化索引选择性。
3. 覆盖索引优化：
   • 确保查询字段和条件在索引中，避免回表（Extra中出现Using index为佳）。
4. 排序与分组优化：
   • Using filesort或Using temporary时，尝试通过索引优化ORDER BY/GROUP BY。

4.2 查询优化技巧

黄金法则：优化需要数据说话！通过EXPLAIN分析执行计划，使用慢查询日志定位问题，结合性能监控工具持续调优，才能实现最佳优化效果。

4.3 架构拓展

MySQL的读写分离和分库分表是应对高并发和大数据量的两种核心优化策略，它们分别解决不同维度的性能问题，并常结合使用以提升系统整体能力。以下是详细解析：

4.3.1 读写分离（Read/Write Splitting）

1. 核心原理

• 主从架构：设置一个主库（Master）处理写操作（INSERT/UPDATE/DELETE），多个从库（Slave）处理读操作（SELECT）。
• 数据同步：主库通过二进制日志（Binlog）记录写操作，从库通过I/O线程拉取Binlog并重放（Relay Log）实现数据同步。
• 读写分流：应用层或中间件将读请求路由到从库，写请求发送到主库。

2. 实现方式

• 代理层：使用MySQL Router、ProxySQL等中间件自动路由请求。
• 框架集成：通过ShardingSphere、Spring动态数据源等代码层面配置。
• DNS轮询：为从库配置多个DNS别名，应用层随机选择读库。

3. 优势

• 提升读性能：扩展多从库，分散读负载。
• 高可用：主库故障时，从库可切换为主库（需配合哨兵或MHA等工具）。

4. 挑战与解决

• 主从延迟：同步延迟导致从库数据不一致。
  • 方案：强制关键查询走主库、使用半同步复制（Semi-Sync）、并行复制（MTS）、监控延迟阈值。
• 路由逻辑：需区分读写SQL，或在事务内强制读主库。

5. 适用场景

• 读多写少（如资讯网站、电商商品页）。
• 对实时性要求不高的查询（如报表、历史数据分析）。

4.3.2 分库分表（Sharding）

1. 分库分表类型

• 垂直分库：按业务模块拆分库（如用户库、订单库、支付库）。
• 垂直分表：将宽表的列按冷热分离（如用户基础信息表 + 用户详情表）。
• 水平分库/分表：按分片键（如用户ID）将数据分散到多个库或表中。

2. 分片策略

• 哈希取模：数据均匀分布，但扩容需迁移数据。
• 范围分片：按时间或ID范围划分（如按月分表），易导致数据倾斜。
• 一致性哈希：减少扩容时的数据迁移量。
• 地理位置分片：按用户地域分配，提升本地访问速度。

3. 实现工具

• 客户端分片：ShardingSphere、TDDL（在应用层解析SQL）。
• 代理分片：MyCat、Vitess（通过中间件路由）。

4. 优势

• 突破单机瓶颈：分散存储压力，提升并发和磁盘IO。
• 降低故障影响：单库故障不影响其他分片。

5. 挑战与解决

• 跨库查询：无法直接JOIN多分片数据。
  • 方案：业务层多次查询合并、冗余字段、使用宽表或搜索引擎（如ES）。
• 分布式事务：跨分片操作需保证一致性。
  • 方案：Seata框架（2PC）、最终一致性（消息队列补偿）。
• 全局唯一ID：雪花算法（Snowflake）、UUID、数据库分段发号。
• 扩容复杂度：采用逻辑分片（如32个逻辑库映射到较少物理库），逐步迁移。

6. 适用场景

• 单表数据超千万，查询性能显著下降。
• 高并发写入导致锁竞争激烈（如订单、日志系统）。

4.3.3 读写分离 vs 分库分表

4.3.4 组合使用实践

大型系统通常 先分库分表，再为每个分片配置读写分离。例如：

1. 分库：订单库按用户ID哈希分为16个库。
2. 分表：每个订单库按月份水平分表（如order_202301）。
3. 读写分离：每个库配置1主2从，查询请求分发到从库。

4.3.4 总结

• 读写分离：低成本提升读性能，但需处理主从延迟。
• 分库分表：解决数据膨胀问题，但架构复杂度陡增。
• 实际选择：根据业务阶段逐步优化，初期可先读写分离，数据量增长后再引入分库分表。

4.4 参数调优

略。

五、高级功能

略。