MySQL分区与分表实战：优化大数据性能指南
MySQL分区与分表实战指南
一、分区类型详解
1. RANGE分区
RANGE分区是最常用的分区类型，根据列值范围将数据分配到不同分区。
CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION pmax VALUES LESS THAN MAXVALUE
);实践建议：
- 适合时间序列数据，如日志、交易记录
- 定期增加新分区，删除旧分区（
ALTER TABLE ... REORGANIZE PARTITION） - 使用
MAXVALUE分区作为兜底
2. LIST分区
LIST分区基于离散的值列表进行分区。
CREATE TABLE users (
id INT NOT NULL,
region_id INT NOT NULL,
name VARCHAR(100)
PARTITION BY LIST (region_id) (
PARTITION p_east VALUES IN (1, 3, 5),
PARTITION p_west VALUES IN (2, 4, 6),
PARTITION p_other VALUES IN (DEFAULT)
);实践建议：
- 适合有明确分类标准的数据
- 分区列值相对稳定，不频繁变化
- 考虑使用DEFAULT分区处理异常值
3. HASH分区
HASH分区通过哈希函数均匀分布数据。
CREATE TABLE products (
id INT NOT NULL,
name VARCHAR(100),
category_id INT
) PARTITION BY HASH(category_id)
PARTITIONS 4;实践建议：
- 适合没有明显分区键但需要分散I/O的场景
- 分区数通常设为2的幂次方
- 避免频繁更新的列作为哈希键
4. KEY分区
类似HASH分区，但使用MySQL内置的哈希函数。
CREATE TABLE logs (
id INT NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP,
content TEXT,
PRIMARY KEY (id)
) PARTITION BY KEY()
PARTITIONS 8;实践建议：
- 适合没有自然分区键的表
- 主键或唯一键自动作为分区键
- 比HASH分区更简单但灵活性较低
5. 复合分区（Subpartitioning）
在分区基础上再进行子分区。
CREATE TABLE sales_archive (
id INT,
sale_date DATE,
region_id INT
) PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH(region_id) (
PARTITION p2020 VALUES LESS THAN (2021) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p2021 VALUES LESS THAN (2022) (
SUBPARTITION s2,
SUBPARTITION s3
)
);实践建议：
- 适合需要两级分区的场景
- 会增加管理复杂度，谨慎使用
- 确保子分区数量合理，避免过多
二、分表策略
1. 水平分表（按行拆分）
实现方式：
- 按ID范围：
user_1到user_n - 按哈希取模：
user_[user_id % 10] - 按时间范围：
orders_2022,orders_2023
实践建议：
- 选择稳定的分片键，避免后期数据迁移
- 考虑热点数据问题，避免某个分片过载
- 预先规划扩容方案
2. 垂直分表（按列拆分）
-- 原始表
CREATE TABLE user (
id INT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(100),
profile_json TEXT,
login_stats JSON,
created_at TIMESTAMP
);
-- 拆分后
CREATE TABLE user_basic (
id INT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(100),
created_at TIMESTAMP
);
CREATE TABLE user_profile (
user_id INT PRIMARY KEY,
profile_json TEXT,
FOREIGN KEY (user_id) REFERENCES user_basic(id)
);
CREATE TABLE user_stats (
user_id INT PRIMARY KEY,
login_stats JSON,
FOREIGN KEY (user_id) REFERENCES user_basic(id)
);实践建议：
- 将频繁访问的列与不频繁访问的列分开
- 将大字段（BLOB/TEXT/JSON）单独存放
- 确保关联查询的性能
3. 分库分表中间件对比
| 特性 | ShardingSphere | MyCat | Vitess |
|---|---|---|---|
| 协议支持 | 多协议 | MySQL | MySQL |
| 分片策略 | 灵活 | 中等 | 中等 |
| 分布式事务 | 支持 | 有限支持 | 支持 |
| 生态系统 | 丰富 | 一般 | 云原生 |
| 运维复杂度 | 中等 | 较高 | 较高 |
ShardingSphere示例配置：
spring:
shardingsphere:
datasource:
names: ds0,ds1
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..15}
table-strategy:
inline:
sharding-column: order_id
algorithm-expression: t_order_$->{order_id % 16}
database-strategy:
inline:
sharding-column: user_id
algorithm-expression: ds$->{user_id % 2}实践建议：
- 评估业务需求选择合适中间件
- 分片键选择要考虑数据分布均匀性
- 预先考虑跨分片查询和事务问题
- 做好数据迁移和扩容规划
三、分区与分表的选择
1. 分区适用场景
- 单表数据量大但不想分表
- 有明显的分区键（如时间）
- 需要定期删除历史数据
- 查询通常只涉及部分分区
2. 分表适用场景
- 单表数据量超过单机容量
- 需要更高的并发性能
- 需要分布式部署
- 不同分表可以有不同的存储策略
3. 性能对比
barChart
title 查询性能对比
x-axis 方案
y-axis QPS
bar 单表: 1000
bar 分区表: 3500
bar 分表: 8000实践建议：
- 首先考虑SQL优化和索引优化
- 数据量在千万级可先尝试分区
- 数据量过亿或需要分布式部署考虑分表
- 监控系统性能，及时调整策略
四、常见问题解决方案
1. 全局唯一ID生成
- 雪花算法（Snowflake）
- UUID（注意索引效率问题）
- 数据库序列（如Redis INCR）
2. 跨分片查询
- 使用中间件聚合
- 应用层并行查询后合并
- 避免不带分片键的条件查询
3. 分布式事务
- 最终一致性方案
- TCC（Try-Confirm-Cancel）模式
- 使用Seata等分布式事务框架
4. 数据迁移与扩容
- 双写过渡方案
- 停机窗口迁移
- 考虑使用阿里云DTS等工具
五、监控与维护
-- 查看分区使用情况
SELECT partition_name, table_rows
FROM information_schema.partitions
WHERE table_name = 'sales';
-- 查看分片均衡情况
SELECT shard, COUNT(*)
FROM user_shards
GROUP BY shard;实践建议：
- 定期检查各分区/分片数据量
- 监控热点分片
- 建立自动化均衡机制
- 做好备份策略（分片越多备份越复杂）
通过合理使用分区和分表技术，可以有效解决MySQL单表数据量过大导致的性能问题。选择方案时需要综合考虑业务特点、增长预期和运维成本。