我要评论一、分库分表是什么？——数据库的“甜蜜烦恼”
把你的mysql数据库想象成是个小单身公寓（单库单表）：
- 刚开始住进去时，东西不多，找啥都方便
- 后来你结婚了（用户量增加），生了娃（数据量暴增），还养了条二哈（业务复杂了）
- 现在全家挤在小公寓里，每天早上一家人抢厕所（数据库锁竞争），找双袜子要翻遍全家（全表扫描）
- 邻居天天投诉你家太吵（性能影响其他服务）
这时候你需要： 分库：买下整栋楼！把不同的家人安排在不同楼层（业务垂直拆分） 分表：把每个房间再隔成小隔间！把袜子、内裤、外套分开放（数据水平拆分）
二、详细实施步骤——从“蜗居”到“豪宅”的装修指南
第1步：设计蓝图——想清楚再动手
-- 先看看现在的“房子”有多大
select
table_schema as '数据库',
table_name as '表名',
round((data_length + index_length) / 1024 / 1024, 2) as '大小(mb)',
table_rows as '行数'
from information_schema.tables
where table_schema = '你的数据库名'
order by (data_length + index_length) desc;
-- 思考人生三连问：
-- 1. 按业务分？(用户相关、订单相关、商品相关)
-- 2. 按时间分？(2024订单、2025订单)
-- 3. 按地域分？(北京用户、上海用户)
第2步：垂直分库——让专业的人住专业的楼层
-- 原来都挤在一个库里
create database single_apartment;
-- 现在买栋楼，每层一个专业户
create database user_villa; -- 用户专属楼层
create database order_mansion; -- 订单豪华层
create database product_tower; -- 商品展示层
-- 用户表搬到用户楼层
create table user_villa.user_info (
user_id bigint primary key auto_increment,
username varchar(50) not null,
email varchar(100),
created_at timestamp default current_timestamp
-- 用户相关的其他字段...
);
-- 订单表搬到订单楼层
create table order_mansion.order_info (
order_id bigint primary key auto_increment,
user_id bigint not null,
amount decimal(10, 2),
status tinyint,
created_at timestamp default current_timestamp
-- 订单相关的其他字段...
);
第3步：水平分表——每个房间都装上帝视角衣柜
方法一：按范围分表（适合时间序列）
-- 订单表按月份拆分，再也不怕找去年的订单像考古了
-- 2024年订单表
create table order_mansion.order_202401 (
order_id bigint primary key auto_increment,
user_id bigint not null,
-- ... 其他字段
created_at timestamp default current_timestamp,
index idx_user_id (user_id),
index idx_created_at (created_at)
) partition by range (month(created_at)) (
partition p1 values less than (2),
partition p2 values less than (3),
-- ... 更多分区
);
-- 2024年2月订单表
create table order_mansion.order_202402 (
-- 结构同上
);
方法二：按哈希分表（均匀分布）
-- 用户表按id取模分表，16张表够不够？
-- 先创建用户表模板
delimiter $$
create procedure create_user_tables()
begin
declare i int default 0;
while i < 16 do
set @table_name = concat('user_villa.user_info_', lpad(i, 2, '0'));
set @sql = concat('
create table if not exists ', @table_name, ' (
user_id bigint primary key auto_increment,
username varchar(50) not null,
email varchar(100),
created_at timestamp default current_timestamp,
index idx_username (username)
) engine=innodb default charset=utf8mb4
');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
set i = i + 1;
end while;
end$$
delimiter ;
-- 执行创建
call create_user_tables();
第4步：路由策略——给每个数据配个“导航系统”
// java代码示例：数据路由导航
public class shardingnavigator {
// 用户表路由：根据user_id决定去哪个表
public static string getusertablename(long userid) {
int tableindex = (int) (userid % 16);
return string.format("user_info_%02d", tableindex);
}
// 订单表路由：根据时间决定去哪个表
public static string getordertablename(date ordertime) {
simpledateformat sdf = new simpledateformat("yyyymm");
return "order_" + sdf.format(ordertime);
}
// 组合查询：跨表查询就像组织家庭聚会
public list<order> getuserorders(long userid, date starttime, date endtime) {
list<order> allorders = new arraylist<>();
calendar calendar = calendar.getinstance();
calendar.settime(starttime);
// 遍历时间范围内的所有月份表
while (calendar.gettime().before(endtime)) {
string tablename = getordertablename(calendar.gettime());
string sql = "select * from " + tablename + " where user_id = ?";
// 执行查询并添加到结果集
// ...
calendar.add(calendar.month, 1);
}
return allorders;
}
}
第5步：中间件配置——请个“万能管家”
# shardingsphere配置文件示例（yaml格式）
# 这位管家知道所有房间在哪
datasources:
ds0:
url: jdbc:mysql://localhost:3306/user_villa?usessl=false
username: root
password: your_password
ds1:
url: jdbc:mysql://localhost:3307/order_mansion?usessl=false
username: root
password: your_password
shardingrule:
tables:
user_info:
actualdatanodes: ds0.user_info_${0..15}
tablestrategy:
inline:
shardingcolumn: user_id
algorithmexpression: user_info_${user_id % 16}
order_info:
actualdatanodes: ds1.order_${2024..2025}${1..12}
tablestrategy:
standard:
shardingcolumn: created_at
precisealgorithmclassname: com.example.timeshardingalgorithm
bindingtables:
- user_info,order_info
第6步：数据迁移——搬家不能丢东西
-- 第一步：先抄家（备份）
create table order_mansion.order_202401_new like order_mansion.order_202401;
-- 第二步：慢慢搬（增量迁移）
insert into order_mansion.order_202401_new
select * from single_apartment.orders
where created_at >= '2024-01-01'
and created_at < '2024-02-01';
-- 第三步：检查有没有落下的
select
(select count(*) from single_apartment.orders
where created_at >= '2024-01-01' and created_at < '2024-02-01') as old_count,
(select count(*) from order_mansion.order_202401_new) as new_count;
-- 第四步：切换门牌号（重命名）
rename table
order_mansion.order_202401 to order_mansion.order_202401_backup,
order_mansion.order_202401_new to order_mansion.order_202401;
第7步：全局id生成——给每个数据发身份证
// 雪花算法：twitter出品，必属精品
public class snowflakeidgenerator {
private final long twepoch = 1288834974657l;
private final long workeridbits = 5l;
private final long datacenteridbits = 5l;
private final long sequencebits = 12l;
private long workerid;
private long datacenterid;
private long sequence = 0l;
private long lasttimestamp = -1l;
public synchronized long nextid() {
long timestamp = timegen();
if (timestamp < lasttimestamp) {
throw new runtimeexception("时间倒流了，检查系统时间！");
}
if (lasttimestamp == timestamp) {
sequence = (sequence + 1) & 4095; // 2^12-1
if (sequence == 0) {
timestamp = tilnextmillis(lasttimestamp);
}
} else {
sequence = 0l;
}
lasttimestamp = timestamp;
return ((timestamp - twepoch) << 22) |
(datacenterid << 17) |
(workerid << 12) |
sequence;
}
// 全局唯一，趋势递增，适合分库分表
}
三、注意事项——别墅区的物业管理条例
分布式事务：楼上转账，楼下扣款，必须同时成功或失败
// 使用seata等分布式事务解决方案
@globaltransactional
public void transfermoney(long fromuser, long touser, bigdecimal amount) {
// 1. 从用户库扣款
// 2. 向订单库插入记录
// 要么都成功，要么都回滚
}
跨表查询：想找张三的所有订单？得问遍所有表！
-- 分表前：一步到位 select * from orders where user_id = 123; -- 分表后：变成侦查行动 select * from order_202401 where user_id = 123 union all select * from order_202402 where user_id = 123 union all -- ... 查遍所有月份表
join操作：用户表和订单表在不同库？只能业务层join
// 1. 先从用户库查用户 list<user> users = userdao.getusers(condition); list<long> userids = users.stream().map(user::getid).collect(collectors.tolist()); // 2. 再去订单库查这些用户的订单 list<order> orders = orderdao.getordersbyuserids(userids); // 3. 在内存中组装 map<long, list<order>> userordersmap = groupordersbyuser(orders);
四、总结
分库分表就像数据库的成人礼，意味着你的业务从“小打小闹”变成了“正经事业”。但是：
不要过早优化：如果你的数据还没到百万级别，别急着分表，就像不能因为将来可能变胖，现在就买xxl号裤子。
选择合适的策略：按时间分？按地域分？按业务分？这就像选择衣柜整理方式，有人喜欢按季节，有人喜欢按颜色，适合的才是最好的。
准备好工具：中间件（shardingsphere、mycat）是你的瑞士军刀，监控工具（prometheus、grafana）是你的健康检测仪。
接受不完美：分库分表后，事务复杂了，查询麻烦了，运维困难了。但这就是成长的代价，就像长大后发现世界不是非黑即白。
最后提醒：分库分表前，先试试这些“减肥方法”：
- 加索引（给东西贴标签）
- 优化sql（整理收纳技巧）
- 升级硬件（换个大房子）
- 读写分离（男女分开用厕所）
只有当这些都不够用时，才考虑分库分表这个大工程。记住，好的架构是演进而来的，不是设计出来的。就像好的婚姻，需要慢慢磨合，不能一开始就分房睡（分库）还分床（分表）！
祝你的数据库从“小公寓”顺利升级到“豪华别墅区”，住得宽敞，查得飞快！
以上就是一文详解mysql实现分库分表的详细步骤的详细内容，更多关于mysql分库分表的资料请关注代码网其它相关文章！
发表评论