数据库扩容方案 (支持千亿级别数据)
一、扩容原则
- 兼容性原则:确保现有系统正常运行,新旧数据格式共存
- 渐进式迁移:分阶段实施,降低风险
- 性能优化:在扩容同时考虑查询效率
- 数据安全:确保迁移过程数据完整性
二、核心表扩容方案
1. CHARBASE 表 (角色基础表)
-- 原结构
CREATE TABLE `CHARBASE` (
`CHARID` int(10) unsigned NOT NULL auto_increment,
`ACCID` int(10) unsigned NOT NULL default '0',
-- ...其他字段
`MONEY` int(10) unsigned NOT NULL default '0',
`GOLD` int(10) unsigned NOT NULL default '0',
`EXP` bigint(20) unsigned NOT NULL default '0'
);
-- 扩容方案
ALTER TABLE `CHARBASE`
MODIFY COLUMN `CHARID` bigint(20) unsigned NOT NULL auto_increment,
MODIFY COLUMN `ACCID` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `MONEY` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `GOLD` bigint(20) unsigned NOT NULL default '0',
ADD COLUMN `accid_high` int(10) unsigned NOT NULL default '0' AFTER `ACCID`, -- 兼容字段
ADD COLUMN `charid_high` int(10) unsigned NOT NULL default '0' AFTER `CHARID`; -- 兼容字段
说明:
- 将ID和数值类字段从INT改为BIGINT
- 添加高低位兼容字段,确保旧代码能继续运行
2. BALANCE 表 (账户余额表)
ALTER TABLE `BALANCE`
MODIFY COLUMN `ACCID` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `ALLGOLDIN` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `ALLGOLDOUT` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `ALLMONEYIN` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `ALLMONEYOUT` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `GOLD` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `MONEY` bigint(20) unsigned NOT NULL default '0';
3. COUNTRY 表 (国家资源表)
ALTER TABLE `COUNTRY`
MODIFY COLUMN `GOLD` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `SILK` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `ORE` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `BOWLDER` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `WOOD` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `COAT` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `HERBAL` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `MATERIAL` bigint(20) unsigned NOT NULL default '0',
MODIFY COLUMN `STOCK` bigint(20) unsigned NOT NULL default '0';
三、相关表扩容清单
表名 | 需要扩容的字段 | 新类型 | 备注 |
---|---|---|---|
AUCTION | ID, MINMONEY, MAXMONEY, MINGOLD, MAXGOLD | BIGINT | 拍卖行数据 |
CONSIGNGOLD | ID, ACCID, NUM, PRICE | BIGINT | 金币寄售 |
CONSIGNMONEY | ID, ACCID, NUM, PRICE | BIGINT | 银子寄售 |
ID, SENDMONEY, RECVMONEY, SENDGOLD, RECVGOLD | BIGINT | 邮件系统 | |
SEPT | SEPTID, CHARID, REPUTE, SPENDGOLD | BIGINT | 家族数据 |
UNION | UNIONID, CHARID, EXP, MONEY | BIGINT | 帮会数据 |
四、数据迁移方案
1. 准备阶段
-- 创建临时表存储旧数据
CREATE TABLE CHARBASE_BAK LIKE CHARBASE;
INSERT INTO CHARBASE_BAK SELECT * FROM CHARBASE;
-- 添加兼容性视图
CREATE VIEW v_charbase_compat AS
SELECT
CHARID, ACCID,
CHARID as charid_low, 0 as charid_high,
ACCID as accid_low, 0 as accid_high,
-- 其他字段...
FROM CHARBASE;