MySQL 分库分表实战

# MySQL 分库分表实战数据量到了千万级单表扛不住了就要分库分表。这篇说说怎么做。## 什么时候需要分库分表单表数据量- 500万不用分加索引、优化 SQL- 500万~2000万考虑分表- 2000万建议分表单库容量- 50GB不用分库- 50GB~200GB考虑分库- 200GB建议分库## 分库 vs 分表| 维度 | 分库 | 分表 ||------|------|------|| 对象 | 数据库级别 | 表级别 || 解决 | 单库连接数、IO瓶颈 | 单表数据量瓶颈 || 代价 | 跨库事务、JOIN困难 | 路由复杂 || 建议 | 先分表不够再分库 | 优先考虑 |## 垂直拆分### 垂直分库按业务拆库mydb单库├── user、order、product、log 全在一个库↓user_db 用户库order_db 订单库product_db 商品库log_db 日志库### 垂直分表按字段拆表sql-- user 表字段太多拆成两张-- user_base常用字段CREATE TABLE user_base (id BIGINT PRIMARY KEY,name VARCHAR(50),phone VARCHAR(20),status TINYINT);-- user_ext不常用字段CREATE TABLE user_ext (user_id BIGINT PRIMARY KEY,bio TEXT,avatar_url VARCHAR(200),created_at DATETIME);## 水平拆分### 水平分表数据按规则分布到多张表order 表 1000万数据 → 按 user_id 分成 4 张表order_0user_id % 4 0order_1user_id % 4 1order_2user_id % 4 2order_3user_id % 4 3### 分片策略| 策略 | 规则 | 优点 | 缺点 ||------|------|------|------|| Hash | user_id % N | 数据均匀 | 扩容麻烦 || Range | 按时间/ID范围 | 范围查询方便 | 数据可能不均匀 || 一致性Hash | Hash环 | 扩容只迁移部分 | 实现复杂 |## ShardingSphere 实战### Maven 依赖xmldependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion5.4.0/version/dependency### 配置水平分表yamlspring:shardingsphere:datasource:names: ds0ds0:type: com.zaxxer.hikari.HikariDataSourcejdbc-url: jdbc:mysql://localhost:3306/mydbusername: rootpassword: 123456rules:sharding:tables:order:actual-data-nodes: ds0.order_$-{(0..3)}table-strategy:standard:sharding-column: user_idsharding-algorithm-name: order-modsharding-algorithms:order-mod:type: MODprops:sharding-count: 4props:sql-show: truejava// 使用和普通 MyBatis 一样Mapperpublic interface OrderMapper {Insert(INSERT INTO order (user_id, amount) VALUES (#{userId}, #{amount}))void insert(Order order);Select(SELECT * FROM order WHERE user_id #{userId})ListOrder selectByUserId(Long userId);}// ShardingSphere 自动路由到 order_0~3### 配置水平分库yamlspring:shardingsphere:datasource:names: ds0,ds1ds0:jdbc-url: jdbc:mysql://192.168.1.100:3306/mydbds1:jdbc-url: jdbc:mysql://192.168.1.101:3306/mydbrules:sharding:tables:order:actual-data-nodes: ds$-{0..1}.order_$-{(0..3)}database-strategy:standard:sharding-column: user_idsharding-algorithm-name: db-modtable-strategy:standard:sharding-column: user_idsharding-algorithm-name: table-modsharding-algorithms:db-mod:type: MODprops:sharding-count: 2table-mod:type: MODprops:sharding-count: 4## 分布式ID分库分表后不能用自增主键了。### 1. 雪花算法推荐java// ShardingSphere 内置// 配置spring:shardingsphere:rules:sharding:key-generators:snowflake:type: SNOWFLAKEprops:worker-id: 1// 使用public class Order {TableId(type IdType.ASSIGN_ID)private Long id; // 自动生成分布式ID}### 2. UUIDsqlSELECT UUID(); -- 36 位字符串-- 缺点太长、无序、索引性能差### 3. Leaf美团基于数据库号段 ZooKeeper 的分布式ID方案优点高性能、趋势递增## 分库分表后的 JOIN 问题sql-- ❌ 跨表 JOIN 在 ShardingSphere 中有限制SELECT * FROM order o JOIN user u ON o.user_id u.id;-- 如果 order 和 user 不在同一个库/表无法直接 JOIN-- ✅ 方案1绑定表分片键相同spring:shardingsphere:rules:sharding:binding-tables:- order, order_item # 同一个 user_id 分片-- ✅ 方案2应用层组装ListOrder orders orderMapper.selectByUserId(userId);ListLong productIds orders.stream().map(Order::getProductId).collect(Collectors.toList());ListProduct products productMapper.selectByIds(productIds);// 内存中关联-- ✅ 方案3全局表字典表广播到每个库spring:shardingsphere:rules:sharding:broadcast-tables:- dict, config # 每个库都有完整数据## 分页问题sql-- ❌ LIMIT 在分表场景下有问题SELECT * FROM order ORDER BY id LIMIT 1000000, 10;-- 需要查询所有分片取 Top N再合并性能很差-- ✅ 方案1用上次的最大 IDSELECT * FROM order WHERE id #{lastId} ORDER BY id LIMIT 10;-- ✅ 方案2禁止深度分页-- 业务层面限制只能翻前 100 页## 扩容问题从 4 张表扩到 8 张表怎么办- Hash 取模扩容数据需要全量迁移- 一致性Hash扩容只迁移部分数据推荐初始分片数留余量比如初始 16 张表先只用 4 张## 小结| 问题 | 解决方案 ||------|---------|| 拆分方式 | 先垂直再水平 || 分片策略 | Hash取模简单 || 分布式ID | 雪花算法 || 跨表JOIN | 绑定表/应用层/全局表 || 分页 | 避免深度分页用游标 || 扩容 | 初始留余量 |---**相关阅读**- [MySQL 读写分离实战]- [MySQL 集群方案对比]- [MySQL 一千万级数据分页查询]