MySQL 分表分库知识整理
警告
能通过创建索引解决的问题就不要使用分表分库的方案来解决!
MySQL 分库分表是解决大数据量、高并发场景下数据库性能瓶颈的核心方案,其本质是通过数据拆分分散存储和计算压力。以下是结合参考文章的系统性总结:
一、分库分表的本质与目标
| 核心问题 | 解决方案 | 效果 |
|---|---|---|
| 单表数据量过大(>500 万) | 数据分片存储 | 提升查询效率,减少索引深度 |
| 高并发写入/查询 | 分散到多个库/表 | 降低单节点负载,提高吞吐量 |
| 业务耦合度高 | 垂直拆分(业务隔离) | 降低复杂度,便于独立扩展 |
二、拆分策略详解
1. 垂直拆分
原则:按业务模块拆分,类似微服务化
垂直分库
mermaidgraph LR A[单库] --> B[用户库] A --> C[订单库] A --> D[商品库]- 场景:用户、订单、商品等模块解耦
- 优势:业务隔离,降低锁竞争
垂直分表
sql-- 原表(用户信息+详情) CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, address TEXT, -- 大字段 bio TEXT -- 大字段 ); -- 拆分后 CREATE TABLE user_base (id INT, name VARCHAR(50), age INT); CREATE TABLE user_detail (id INT, address TEXT, bio TEXT);- 场景:大字段(如文本、JSON)分离
- 优势:避免大字段拖慢核心查询
2. 水平拆分
原则:将单表数据按规则分散存储
水平分表(单库内分表)
sql-- 原始订单表 CREATE TABLE orders (id BIGINT, user_id INT, amount DECIMAL); -- 按 user_id 分 10 张表 CREATE TABLE orders_0 (id BIGINT, user_id INT, amount DECIMAL); ... CREATE TABLE orders_9 (id BIGINT, user_id INT, amount DECIMAL); ```plain - 路由算法:`表名 = orders_{user_id % 10}` - 场景:单库磁盘IO瓶颈水平分库分表
mermaidgraph TD A[订单库] --> A0[orders_0] A --> A1[orders_1] B[订单库2] --> B0[orders_0] B --> B1[orders_1]- 路由算法:
库名 = order_db_{user_id % 2},表名 = orders_{user_id % 10} - 场景:高并发写入需分散到不同物理机器
- 路由算法:
三、关键实现技术
1. 路由策略
| 策略 | 实现方式 | 适用场景 |
|---|---|---|
| 取模分片 | shard_key % shard_num | 数据均匀分布 |
| 范围分片 | id BETWEEN 1000-2000 => shard1 | 按时间/ID 区间查询 |
| 一致性哈希 | 减少扩容时数据迁移量 | 动态扩容场景 |
| 基因分片 | 分片键嵌入业务 ID | 避免跨分片 Join |
2. 分布式 ID 生成
python
# Snowflake算法实现(64位ID)
0 | 41位时间戳 | 10位机器ID | 12位序列号- 要求:全局唯一、趋势递增、高可用
3. 分页查询优化
- 禁止跨分片排序:
ORDER BY + LIMIT需在各分片执行后归并 - 解决方案:
- 业务折衷:仅按分片键排序
- 冗余数据:建立全局搜索库(如 Elasticsearch)
四、挑战与解决方案
| 挑战 | 解决方案 |
|---|---|
| 跨分片 Join | 1. 全局表广播 2. 业务层拼装数据 3. 冗余字段 |
| 分布式事务 | 1. Seata(AT 模式) 2. 消息队列最终一致性 |
| 扩容复杂度 | 1. 双倍扩容法 2. 一致性哈希减少迁移量 |
| 多维度查询 | 1. 异构索引(ES/HBase) 2. 基因分片 |
五、何时需要分库分表
通过以下公式判断(参考自腾讯云文章):
latex
\text{分库分表条件} = \begin{cases}
\text{数据量 > 500万行} \\
\text{并发量 > 2000 QPS} \\
\text{磁盘IO > 70\%}
\end{cases}优先优化顺序:
mermaid
graph LR
A[SQL优化] --> B[索引优化] --> C[缓存] --> D[读写分离] --> E[分库分表]六、中间件选型
| 工具 | 特点 | 适用场景 |
|---|---|---|
| ShardingSphere | Apache 项目,支持 JDBC 直连 | Java 技术栈,轻量级改造 |
| MyCAT | 独立 Proxy 层,功能丰富 | 多语言支持,复杂分片规则 |
| Vitess | Kubernetes 原生,YouTube 验证 | 云原生部署,大规模集群 |
七、最佳实践总结
- 避免过度设计:单表 500 万以下优先考虑分区表+索引优化
- 选择合适分片键:高频查询字段,数据分布均匀
- 预留扩容空间:如从 2 库 8 表起步,按 2 倍数扩容
- 异构索引同步:将多维度查询同步到 ES
- 灰度发布:先切读流量,再切写流量
强调:分库分表是最后的核武器,需综合评估成本与收益。在分布式架构中,合理的设计比盲目拆分更重要。
