第35章 分区表与复制
本章目标
- 掌握 MySQL 四种分区类型（RANGE、LIST、HASH、KEY）的语法和应用场景
- 理解分区裁剪（Partition Pruning）的原理和 EXPLAIN 验证方法
- 掌握分区的日常管理操作（ADD、DROP、TRUNCATE、REORGANIZE PARTITION）
- 深入理解 MySQL 主从复制的架构和工作原理
- 掌握复制的配置步骤和常见问题的排查方法
- 理解 GTID 复制的优势和配置方法
- 掌握读写分离的架构和实现策略
前置知识
- 熟练掌握 CREATE TABLE 和 ALTER TABLE
- 理解 InnoDB 存储引擎的基本原理
- 了解二进制日志（Binlog）的基本概念和作用
- 有一定的 Linux 运维经验
35.1 分区表
35.1.1 分区的概念和优势
概念：
一个逻辑表物理上被分割为多个分区文件（.ibd），
但对应用层来说仍然是一张表。
+------------------------------------------------+
| 逻辑表: orders |
| +--------------------------------------------+ |
| | PARTITION p2022 | 2022年的数据 | |
| | PARTITION p2023 | 2023年的数据 | |
| | PARTITION p2024 | 2024年的数据 | |
| +--------------------------------------------+ |
+------------------------------------------------+
检查分区文件：
$ ls /var/lib/mysql/mydb/orders#p#p2022.ibd
$ ls /var/lib/mysql/mydb/orders#p#p2023.ibd
$ ls /var/lib/mysql/mydb/orders#p#p2024.ibd分区的三大优势：
sql
-- 1. 查询裁剪（Partition Pruning）
-- 只扫描相关分区，不扫描不需要的分区
SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- 自动只扫描 p2024 分区
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- partitions 列显示：p2024（而非 p2022,p2023,p2024）
-- 2. 数据生命周期管理
-- 删除 2022 年的数据（ALTER TABLE ... DROP PARTITION 是元数据操作，毫秒级完成）
ALTER TABLE orders DROP PARTITION p2022;
-- 对比：DELETE FROM orders WHERE YEAR(created_at) = 2022;
-- 需要扫描、删除、产生 Undo Log、锁表，可能需要数小时！
-- 3. 分散 I/O
-- 不同分区的物理文件可放置在不同磁盘上
ALTER TABLE orders
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2022 VALUES LESS THAN (2023) DATA DIRECTORY = '/ssd1',
PARTITION p2023 VALUES LESS THAN (2024) DATA DIRECTORY = '/ssd2',
PARTITION p2024 VALUES LESS THAN (2025) DATA DIRECTORY = '/ssd3'
);35.1.2 四种分区类型
RANGE 分区（范围分区，最常用）：
sql
-- 按创建年份分区
CREATE TABLE orders (
id INT NOT NULL,
user_id INT NOT NULL,
total_amount DECIMAL(10,2),
created_at DATE NOT NULL,
PRIMARY KEY (id, created_at) -- 注意：主键必须包含分区键！
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p_future VALUES LESS THAN MAXVALUE -- 兜底分区（超过2024的数据流入这里）
);
-- RANGE COLUMNS：可以直接使用 DATE 类型，不需要函数
CREATE TABLE orders_v2 (
id INT NOT NULL,
user_id INT NOT NULL,
total_amount DECIMAL(10,2),
created_at DATE NOT NULL,
PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS(created_at) (
PARTITION p2024_q1 VALUES LESS THAN ('2024-04-01'),
PARTITION p2024_q2 VALUES LESS THAN ('2024-07-01'),
PARTITION p2024_q3 VALUES LESS THAN ('2024-10-01'),
PARTITION p2024_q4 VALUES LESS THAN ('2025-01-01')
);LIST 分区（列表分区）：
sql
-- 按区域分片
CREATE TABLE regional_sales (
id INT NOT NULL,
region_id INT NOT NULL,
amount DECIMAL(10,2),
sale_date DATE NOT NULL,
PRIMARY KEY (id, region_id)
)
PARTITION BY LIST (region_id) (
PARTITION p_north VALUES IN (1, 2, 3),
PARTITION p_south VALUES IN (4, 5, 6),
PARTITION p_east VALUES IN (7, 8),
PARTITION p_west VALUES IN (9, 10)
);
-- LIST COLUMNS：可以使用字符串类型
CREATE TABLE users_by_country (
id INT NOT NULL,
country_code CHAR(2) NOT NULL,
name VARCHAR(100),
PRIMARY KEY (id, country_code)
)
PARTITION BY LIST COLUMNS(country_code) (
PARTITION p_asia VALUES IN ('CN', 'JP', 'KR', 'SG'),
PARTITION p_europe VALUES IN ('GB', 'DE', 'FR', 'IT'),
PARTITION p_americas VALUES IN ('US', 'CA', 'BR', 'MX')
);HASH 分区（哈希分区，均匀分布）：
sql
-- 按用户 ID 哈希，将数据均匀分布到 4 个分区
CREATE TABLE user_events (
id BIGINT NOT NULL,
user_id INT NOT NULL,
event_type VARCHAR(50),
event_data JSON,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 4;
-- HASH 分区的优势：
-- 1. 数据均匀分布（避免热点分区）
-- 2. 写入压力被分散到多个分区文件
-- 3. 指定 user_id 查询时可以裁剪到 1 个分区
-- 线性 HASH（LINEAR HASH）：添加/合并分区时数据移动更少
PARTITION BY LINEAR HASH (user_id)
PARTITIONS 4;KEY 分区（和 HASH 类似，但由 MySQL 计算哈希）：
sql
-- KEY 分区适用于非整型列
CREATE TABLE sessions (
session_id VARCHAR(128) NOT NULL,
user_id INT,
data TEXT,
created_at DATETIME,
PRIMARY KEY (session_id)
)
PARTITION BY KEY (session_id)
PARTITIONS 8;
-- KEY 分区的特点：
-- 1. MySQL 使用自己的哈希算法（不是用户可控的）
-- 2. 可以用于字符串等非整型列
-- 3. 如果未指定分区键，默认使用主键35.1.3 分区表的核心限制
sql
-- 限制 1：主键/唯一键必须包含所有分区键
-- 正确：
CREATE TABLE t (
id INT,
created_at DATE NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (...);
-- 错误：
CREATE TABLE t (
id INT PRIMARY KEY, -- 主键只有 id
created_at DATE NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (...);
-- ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function
-- 限制 2：不支持外键（外键引用的表不能是分区表）
-- 分区表不能有外键约束
-- 限制 3：不支持 FULLTEXT 索引
-- 限制 4：最大 8192 个分区（MySQL 8.0）
-- 限制 5：没有全局唯一索引（唯一索引必须包含分区键）
-- 这意味着不能创建不在分区键上的唯一列！35.1.4 分区管理
sql
-- 查看分区信息
SELECT
PARTITION_NAME,
PARTITION_ORDINAL_POSITION,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH,
PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';
-- 添加分区
ALTER TABLE orders ADD PARTITION (
PARTITION p2025 VALUES LESS THAN (2026)
);
-- 删除分区（数据也被删除！）
ALTER TABLE orders DROP PARTITION p2021;
-- 注意：DROP PARTITION 是即时操作（只修改元数据），不是逐行删除
-- 截断分区（删除数据但保留分区定义）
ALTER TABLE orders TRUNCATE PARTITION p2024;
-- 重组分区（合并/拆分分区）
-- 将 p2021 和 p2022 合并为 p_archive
ALTER TABLE orders REORGANIZE PARTITION p2021, p2022 INTO (
PARTITION p_archive VALUES LESS THAN (2023)
);
-- 拆分分区
ALTER TABLE orders REORGANIZE PARTITION p2024 INTO (
PARTITION p2024_h1 VALUES LESS THAN (20240701),
PARTITION p2024_h2 VALUES LESS THAN (20250101)
);
-- 注意：
-- 1. REORGANIZE PARTITION 会锁表并复制数据，大分区耗时很长
-- 2. 如果有 MAXVALUE 分区，不能直接 ADD PARTITION，
-- 需要用 REORGANIZE 拆分 MAXVALUE 分区
-- 重建分区（碎片整理）
ALTER TABLE orders REBUILD PARTITION p2024;
-- 修复分区
ALTER TABLE orders REPAIR PARTITION p2024;
-- 检查分区
ALTER TABLE orders CHECK PARTITION p2024;
-- 优化分区（等价于在分区上执行 OPTIMIZE TABLE）
ALTER TABLE orders OPTIMIZE PARTITION p2024;35.1.5 分区策略选择指南
+-------------------+---------------------+---------------------------+
| 分区类型 | 适合场景 | 典型例子 |
+-------------------+---------------------+---------------------------+
| RANGE | 时间序列数据 | 订单表按年/月分区 |
| LIST | 离散分类数据 | 按国家/地区分区 |
| HASH | 均匀分布（无业务语义） | 用户表按 user_id 哈希分区 |
| KEY | 字符串列的哈希分布 | 会话表按 session_id 分区 |
+-------------------+---------------------+---------------------------+
选择建议：
- 有时间维度的 → RANGE 分区（最常用）
- 有明确分类的 → LIST 分区
- 仅需要分散 I/O → HASH/KEY 分区
- 分区数不是越多越好：100-500 个分区通常足够
- 大多数业务用 RANGE + 时间维度即可覆盖 80% 的拆分需求35.2 MySQL 主从复制
35.2.1 复制架构
+------------------+ +------------------+
| Master | | Slave |
| (主库/写库) | | (从库/读库) |
+------------------+ +------------------+
| | | |
| Binary Log | ──────> | Relay Log |
| (binlog) | 网络复制 | (relay log) |
| | | |
| SQL Thread | | IO Thread |
| (写binlog) | | (读binlog→relay) | ← 从库IO线程
| | | |
| | | SQL Thread |
| | | (中继日志→数据) | ← 从库SQL线程
+------------------+ +------------------+
复制流程：
1. Master 将数据变更写入 Binary Log
2. Slave 的 IO Thread 连接到 Master，读取 Binary Log
3. Slave 的 IO Thread 将读取的 binlog 事件写入本地的 Relay Log
4. Slave 的 SQL Thread 读取 Relay Log，执行其中的事件
5. 循环往复，保持数据同步35.2.2 复制模式
异步复制（默认）：
text
Master 写入 binlog 后立即返回客户端，不等 Slave 确认。
优点：性能好，Master 不受 Slave 状态影响
缺点：如果 Master 崩溃，未传输到 Slave 的 binlog 会丢失半同步复制（Semi-Synchronous）：
text
Master 写入 binlog 后，等待至少一个 Slave 确认收到后才返回客户端。
优点：减少了数据丢失的风险
缺点：增加了写入延迟（等待网络往返）
# 配置半同步
# Master 和 Slave 都需要安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled = ON;
SET GLOBAL rpl_semi_sync_slave_enabled = ON;
# 查看状态
SHOW STATUS LIKE 'Rpl_semi_sync%';35.2.3 复制搭建步骤（基于日志位置）
在 Master 上：
sql
-- 1. 创建复制专用账号
CREATE USER 'repl'@'slave_ip' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip';
FLUSH PRIVILEGES;
-- 2. 获取当前 binlog 位置
SHOW MASTER STATUS;
-- +-------------------+----------+--------------+------------------+
-- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-- +-------------------+----------+--------------+------------------+
-- | binlog.000010 | 1234 | | |
-- +-------------------+----------+--------------+------------------+
-- 3. 导出数据（如果 Slave 从零开始）
mysqldump -u root -p --single-transaction \
--master-data=2 --all-databases > master_dump.sql配置文件设置：
ini
# Master 的 my.cnf
[mysqld]
server-id = 1 # 必须全局唯一
log_bin = /var/log/mysql/binlog # 必须开启 binlog
binlog_format = ROW
binlog_expire_logs_seconds = 604800
sync_binlog = 1
# Slave 的 my.cnf
[mysqld]
server-id = 2 # 必须与 Master 不同
relay_log = /var/log/mysql/relaylog # 中继日志路径
read_only = ON # 从库只读（推荐）
log_bin = /var/log/mysql/binlog # 可选：如果此 Slave 也将是其他 Slave 的 Master
log_slave_updates = ON # 可选：将复制的变更也写入自己的 binlog在 Slave 上执行：
sql
-- MySQL 8.0.23+ 新语法
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'master_host',
SOURCE_PORT = 3306,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'repl_password',
SOURCE_LOG_FILE = 'binlog.000010',
SOURCE_LOG_POS = 1234;
-- MySQL 8.0.23 之前（旧语法，仍可用）
CHANGE MASTER TO
MASTER_HOST = 'master_host',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'repl_password',
MASTER_LOG_FILE = 'binlog.000010',
MASTER_LOG_POS = 1234;
-- 启动复制
START REPLICA; -- 新语法
-- START SLAVE; -- 旧语法（仍可用）
-- 检查复制状态
SHOW REPLICA STATUS\G
-- 关键字段：
-- Slave_IO_Running: Yes ← IO 线程正常
-- Slave_SQL_Running: Yes ← SQL 线程正常
-- Seconds_Behind_Master: 0 ← 延迟（0=无延迟，理想状态）
-- Last_IO_Error: '' ← IO 线程错误
-- Last_SQL_Error: '' ← SQL 线程错误35.2.4 复制状态监控
sql
-- 综合监控查询
SELECT
@@server_id AS server_id,
@@read_only AS read_only,
CONNECTION_NAME,
CHANNEL_NAME,
SERVICE_STATE,
RECEIVED_TRANSACTION_SET,
LAST_ERROR_NUMBER,
LAST_ERROR_MESSAGE,
LAST_ERROR_TIMESTAMP
FROM performance_schema.replication_connection_status;
-- 查看复制延迟
SHOW REPLICA STATUS\G
-- Seconds_Behind_Master: 0
-- 如果延迟增大，检查：
-- 1. 是否有大事务阻塞了 SQL 线程
-- 2. 网络是否正常（ping, iperf）
-- 3. Slave 磁盘 I/O 是否成为瓶颈
-- 4. Slave 是否也在承担大量读请求
-- 查看复制过滤规则
SHOW REPLICA STATUS\G
-- Replicate_Do_DB:
-- Replicate_Ignore_DB:35.2.5 GTID 复制（全球事务 ID）
GTID 的优势：
传统复制（基于日志+位置）：
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000010', MASTER_LOG_POS=1234;
→ 需要手动找到正确的位置
→ 切换 Master（failover）时容易出错
GTID 复制：
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;
→ MySQL 自动找到需要复制的下一个事务！
→ failover 时自动从正确位置开始配置 GTID 复制：
ini
# Master 和 Slave 都需要配置
[mysqld]
server-id = 1
gtid_mode = ON
enforce_gtid_consistency = ON # 确保所有语句都能安全地使用GTID
log_bin = /var/log/mysql/binlog
log_slave_updates = ONsql
-- 查看 GTID 执行状态
SHOW MASTER STATUS\G
-- Executed_Gtid_Set: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-100
-- Slave 上配置（使用 GTID 自动定位）
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'master_host',
SOURCE_PORT = 3306,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'repl_password',
SOURCE_AUTO_POSITION = 1; -- 关键：自动位置
-- GTID 跳过一个错误的事务（当从库因某事务而出错时）
-- 方法1：注入空事务（最安全）
SET GTID_NEXT = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:101';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
-- 方法2：直接设置（但不推荐）
STOP REPLICA;
SET GTID_NEXT = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:101';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;35.2.6 读写分离策略
架构方案1：应用层读写分离（代码实现）
- 应用代码中使用 AOP/中间件识别读写
- @Master → 走主库（INSERT/UPDATE/DELETE/SELECT ... FOR UPDATE）
- @Slave → 走从库（普通 SELECT）
- 需处理复制延迟（刚写入的数据可能还没复制到从库）
架构方案2：代理中间件
- ProxySQL, MySQL Router, MaxScale
- 对应用透明，通过端口区分读写
- 支持负载均衡、故障转移、查询路由
架构方案3：数据库中间件
- ShardingSphere, MyCat
- 同时支持读写分离和分库分表
- 功能最强大，但运维复杂度也最高sql
-- 复制延迟处理的关键 SQL
-- 场景：用户刚下单，立刻跳转到订单详情页
-- 方案 1：关键读操作强制走主库（如订单详情页）
-- 在代码中标记此类查询为 @Master
-- 方案 2：从库读时检查是否可达（应用层实现）
-- SELECT * FROM orders WHERE id = 123; -- 从库
-- if (result == null) { 查询主库 }
-- 方案 3：检查从库延迟，超过阈值则走主库
SHOW REPLICA STATUS;
-- if Seconds_Behind_Master > 1 → 走主库常见错误
错误 1：MaxValue 分区导致无法添加新分区
问题：创建了 PARTITION p_max VALUES LESS THAN MAXVALUE 后，
想再添加 2026 年的分区时：
ALTER TABLE orders ADD PARTITION (PARTITION p2026 VALUES LESS THAN (2027));
→ 错误：MAXVALUE can only be used in last partition definition
解决：
不能 ADD，需要用 REORGANIZE 拆分 maxvalue 分区：
ALTER TABLE orders REORGANIZE PARTITION p_max INTO (
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_max VALUES LESS THAN MAXVALUE
);错误 2：分区键不在主键/唯一键中
问题：
CREATE TABLE t (
id INT AUTO_INCREMENT PRIMARY KEY,
text VARCHAR(100),
created_at DATE NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (...);
→ ERROR 1503: A PRIMARY KEY must include all columns in the partitioning function
解决：
ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
-- 或者不使用分区错误 3：复制延迟时强制读从库导致数据不一致
问题：用户下单后跳转到订单详情页面，数据还没复制到从库，页面显示"订单不存在"。
解决：
1. 刚写入后的读操作强制走主库
2. 在应用层设置"刚写入的 Key"缓存到 Redis（TTL 几秒），读时先检查
3. 复制延迟监控告警，延迟过高时暂时将所有读切回主库错误 4：忘记分配唯一的 server-id
问题：搭建从库时忘记设置 server-id 或设为与主库相同的值，
复制无法正常工作。
解决：
每个 MySQL 实例必须有全局唯一的 server-id：
-- 主库：server-id = 1
-- 从库1：server-id = 2
-- 从库2：server-id = 3
-- ...
-- 不能重复！错误 5：使用 Replicate_Do_DB 导致的"意外"
问题：在 Slave 上设置 Replicate_Do_DB = 'mydb'，
但在 Master 上执行 USE otherdb; INSERT INTO mydb.orders ...
时，这个 INSERT 不会被复制！因为 Replicate_Do_DB 只根据
当前的默认数据库（USE）判断，不检查被操作的表属于哪个库。
建议：使用 Replicate_Wild_Do_Table = 'mydb.%' 代替，
它基于被操作的表名判断。本章练习
练习 1：RANGE 分区实践
- 创建 orders 表，按年进行 RANGE 分区（2020-2025）
- 插入跨年份的测试数据
- 使用 EXPLAIN 验证分区裁剪是否生效
- 添加 2026 年分区
- 删除 2020 年分区（与 DELETE 操作对比耗时）
- 查看 information_schema.PARTITIONS 获取各分区的行数统计
练习 2：主从复制环境搭建
- 在同一台机器上使用不同的端口和数据目录搭建 1 主 + 2 从
- 配置 GTID 复制
- 测试：在主库插入数据，验证从库同步
- 测试：在从库尝试写入（验证 read_only 效果）
- 模拟主库故障，手动将从库提升为主库
- 配置半同步复制，对比异步和半同步的写入延迟
练习 3：复制故障排查
- 故意在从库上删除某行数据，然后在主库更新该行
- 观察复制中断（SQL 线程报错）
- 修复错误并重启复制
- 使用 GTID 跳过错误事务
- 记录排查过程和常用诊断命令
练习 4：分区类型对比
- 分别创建 RANGE、LIST、HASH、KEY 分区的表
- 每种分区插入 100 万行测试数据
- 对比不同分区类型在以下场景的性能：
- 按分区键精确查询
- 跨分区范围查询
- 插入性能
- 删除整分区 vs 删除数据行
练习 5：复制延迟场景
- 搭建主从复制环境
- 在主库执行大量 UPDATE 操作（模拟大事务）
- 观察从库的 Seconds_Behind_Master 的增长
- 实现应用层的读写分离逻辑（读从库 + 关键读主库）
- 测试复制延迟 > 1s 时自动切换读主库
练习 6：综合架构设计
设计一个支持 100 万日活用户的数据库架构方案：
- 数据量：用户表 500 万、订单表 5 亿、订单明细表 20 亿
- 设计分区方案（订单表按时间分区）
- 设计分库分表方案（用户维度分库）
- 设计读写分离方案（1 主 + N 从）
- 画出完整的架构图，标注数据流向
- 估算硬件资源需求