[关闭]
@dawn 2016-03-07T09:10:05.000000Z 字数 6536 阅读 352

payment优化方案

未分类

现状

payment只用到了uc_ap数据库，涉及到四张表，credit_error_log，game_channel_cfg，game_region_cfg，user_credit，mysql版本为5.5.30-log

其中game_channel_cfg，game_region_cfg。由运营、产品在ops中配置后直接写到的这两个表中。

user_credit中保存了所有的订单数据，以下的相关的表结构及信息。

  1. CREATE TABLE `user_credit` (
  2.   `orderid` varchar(64) NOT NULL,
  3.   `money` int(11) DEFAULT '0',
  4.   `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0:未知,1:支付成功,2:支付失败,3:同步成功,4:同步失败 5:沙箱',
  5.   `failed_desc` varchar(100) DEFAULT NULL,
  6.   `game_appkey` varchar(32) NOT NULL,
  7.   `region_code` varchar(20) NOT NULL,
  8.   `channel_code` varchar(20) NOT NULL,
  9.   `pr_channel_code` varchar(20) DEFAULT NULL,
  10.   `uid` varchar(50) NOT NULL,
  11.   `ucuid` varchar(50) DEFAULT NULL,
  12.   `deviceid` varchar(100) DEFAULT NULL,
  13.   `item_code` varchar(60) NOT NULL,
  14.   `game_callback_extra` varchar(1000) DEFAULT NULL,
  15.   `channel_callback_extra` text,
  16.   `callback_date` datetime DEFAULT NULL,
  17.   `confirm_date` datetime DEFAULT NULL,
  18.   `create_date` datetime DEFAULT NULL,
  19.   `pay_count` int(11) DEFAULT NULL,
  20.   `currency` varchar(10) DEFAULT NULL,
  21.   `nickname` varchar(50) DEFAULT NULL,
  22.   `region_pay_count` int(11) DEFAULT NULL,
  23.   PRIMARY KEY (`orderid`),
  24.   KEY `index_ucuid` (`ucuid`) USING HASH,
  25.   KEY `appkey_region_uid` (`game_appkey`,`region_code`,`uid`) USING BTREE,
  26.   KEY `uid` (`uid`)
  27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user_credit的状态

问题

  1. Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\x8C' for column 'nickname' at row 1
  2.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) ~[mysql-connector-java-5.1.37.jar:5.1.37]
  3.         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) ~[mysql-connector-java-5.1.37.jar:5.1.37]

本次相关的修改

新的表结构为

  2. CREATE TABLE `user_credit` (
  3.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  4.   `orderid` varchar(64) CHARACTER SET utf8 NOT NULL,
  5.   `money` int(11) DEFAULT '0',
  6.   `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0:未知,1:支付成功,2:支付失败,3:同步成功,4:同步失败 5:沙箱',
  7.   `failed_desc` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  8.   `game_appkey` varchar(32) CHARACTER SET utf8 NOT NULL,
  9.   `region_code` varchar(20) CHARACTER SET utf8 NOT NULL,
  10.   `channel_code` varchar(20) CHARACTER SET utf8 NOT NULL,
  11.   `pr_channel_code` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  12.   `uid` varchar(50) CHARACTER SET utf8 NOT NULL,
  13.   `ucuid` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  14.   `deviceid` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  15.   `item_code` varchar(60) CHARACTER SET utf8 NOT NULL,
  16.   `game_callback_extra` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
  17.   `channel_callback_extra` text CHARACTER SET utf8mb4,
  18.   `callback_date` datetime DEFAULT NULL,
  19.   `confirm_date` datetime DEFAULT NULL,
  20.   `create_date` datetime NOT NULL,
  21.   `currency` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  22.   `nickname` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
  23.   PRIMARY KEY (`id`,`create_date`),
  24.   KEY `appkey_region_uid` (`game_appkey`,`region_code`,`uid`) USING BTREE
  25. ) ENGINE=InnoDB AUTO_INCREMENT=2005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  26.  PARTITION BY RANGE (TO_DAYS(create_date))
  27. (PARTITION p201604 VALUES LESS THAN (TO_DAYS('2016-04-01')) ENGINE = InnoDB,
  28.  PARTITION p201605 VALUES LESS THAN (TO_DAYS('2016-05-01')) ENGINE = InnoDB,
  29.  PARTITION p201606 VALUES LESS THAN (TO_DAYS('2016-06-01')) ENGINE = InnoDB,
  30.  PARTITION p201607 VALUES LESS THAN (TO_DAYS('2016-07-01')) ENGINE = InnoDB,
  31.  PARTITION p201608 VALUES LESS THAN (TO_DAYS('2016-08-01')) ENGINE = InnoDB,
  32.  PARTITION p201609 VALUES LESS THAN (TO_DAYS('2016-09-01')) ENGINE = InnoDB,
  33.  PARTITION p201610 VALUES LESS THAN (TO_DAYS('2016-10-01')) ENGINE = InnoDB,
  34.  PARTITION p201611 VALUES LESS THAN (TO_DAYS('2016-11-01')) ENGINE = InnoDB,
  35.  PARTITION p201612 VALUES LESS THAN (TO_DAYS('2016-12-01')) ENGINE = InnoDB,
  36.  PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-01-01')) ENGINE = InnoDB,
  37.  PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
  38.  PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB)

维护方案

首次维护
1、安装新的mysql，配置utf8mb4
2、停服
3、旧的表改名，创建新的表
4、启动，使用新的jar包，马上提供服务
5、将最近七天的订单导入到新表中去
6、测试
7、完成

旧的表将弃用，由的旧的表数据量较大，所以，查询最近一天的数据可能会花费一些时间，会造成在这段时间内无法提供服务，但不会造成丢单的情况，如果不考虑丢单，可以直接切换到新的表中，在payment启动之后再将最近七天的数据导入进去。

  1. //导数据SQL，游戏表名
  2. insert into user_credit (orderid, money, order_status, failed_desc, game_appkey, region_code, channel_code, pr_channel_code, uid, ucuid, deviceid, item_code, game_callback_extra, channel_callback_extra, callback_date, confirm_date, create_date, currency, nickname) select orderid, money, order_status, failed_desc, game_appkey, region_code, channel_code, pr_channel_code, uid, ucuid, deviceid, item_code, game_callback_extra, channel_callback_extra, callback_date, confirm_date, create_date, currency, nickname from user_credit_old as u where create_date > date_sub(CURDATE(),  interval 7 DAY)

例行维护

系统工程每隔固定时间将数据库备份，备份后可以删除老的分区，只保留最近两个月的分区（可保证不丢单），同时按日期分区创建新的分区。

  1. 添加分区
  2. alter table user_credit add partition (PARTITION p201603 VALUES LESS THAN (TO_DAYS('2016-04-01'))) ;
  3. alter table user_credit add partition (PARTITION p201604 VALUES LESS THAN (TO_DAYS('2016-05-01'))) ;
  4. alter table user_credit add partition (PARTITION p201605 VALUES LESS THAN (TO_DAYS('2016-06-01'))) ;
  5. alter table user_credit add partition (PARTITION p201606 VALUES LESS THAN (TO_DAYS('2016-07-01'))) ;
  6. alter table user_credit add partition (PARTITION p201607 VALUES LESS THAN (TO_DAYS('2016-08-01'))) ;
  7. alter table user_credit add partition (PARTITION p201608 VALUES LESS THAN (TO_DAYS('2016-09-01'))) ;
  8. alter table user_credit add partition (PARTITION p201609 VALUES LESS THAN (TO_DAYS('2016-10-01'))) ;
  9. alter table user_credit add partition (PARTITION p201610 VALUES LESS THAN (TO_DAYS('2016-11-01'))) ;
  10. alter table user_credit add partition (PARTITION p201611 VALUES LESS THAN (TO_DAYS('2016-12-01'))) ;
  11. alter table user_credit add partition (PARTITION p201612 VALUES LESS THAN (TO_DAYS('2017-01-01'))) ;
  12. alter table user_credit add partition (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01'))) ;
  13. alter table user_credit add partition (PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01'))) ;
  14. alter table user_credit add partition (PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01'))) ;

参考

mysql添加对emoji的支持
实战mysql分区(PARTITION)

测试SQL使用的分区

  1. explain partitions select * from user_credit as u where create_date > date_sub(Date('2017-03-01 08:01:01'),  interval 1 week) order by create_date

测试用的存储过程

  1. BEGIN  
  3. DECLARE i INT DEFAULT 1;
  4. DECLARE sub INT;
  5. DECLARE sd DATETIME;
  6. DECLARE ed DATETIME;
  7. DECLARE rndTime DATETIME;
  9. SET sd = "2015-12-01 08:01:01";
  10. SET ed = "2017-03-30 08:01:01";
  13. SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));  
  15. WHILE i<1000  
  16. DO  
  17.     SET rndTime =DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);  
  19.     insert into user_credit (orderid, order_status, game_appkey, region_code, channel_code, uid, item_code, create_date) values (concat(curTime(), i), 1, 'appkey1', 'region1','channel1', 'uid1', 'itemid1', rndTime);  
  20.     SET i=i+1;  
  21. END WHILE ;  
  22. commit;  
  24. END
  1. 新增一个分区
  2. alter table user_credit add partition (partition p201603 values LESS THAN (TO_DAYS('2016-03-30')));
  1. 删除一个分区
  2. alter table user_credit drop partition p201603;

注：对date字段只能使用>、 <、 =

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注