拉取淘宝订单服务数据mysql分表存储亿条数据

淘宝订单有每天晚上同步机制,公司有10万个淘宝卖家客户,如果每个客户平均1000个订单就达到1亿条订单的数据量,除去重复订单也会有千万买家会员数据,如果采用mysql存储查询起来性能肯定不行,这里指单台mysql服务器。这里的表 主要是会员信息表和订单信息表以及会员订单关系表。

        为解决这个办法我们采取了分表的方式,把一张大表分成几张小表,类似oracle的Partition表。

        这里需要考虑几个问题

       1确定哪条信息存到哪个表的关键信息

       2怎么保证数据库表信息分布均匀和表不够后表的数量扩张

1.首先从业务需求看,卖家有这样的需求,向自己的会员发短信,优惠券,统计分析等等。

所有的操作基本是只限在自己能看到的数据范围内,这就好办了,卖家有唯一标识符

sellerNick,如果把一个卖家的数据的存放到同一张表里这样就省取了很多麻烦。

2.为了均衡数据,就需要设计一张元数据表,根据元数据选择和创建表级支持业务查询。

   

以下是一些配置信息     

#初始会员表数量
max.member.tables=50
#初始会员订单关系表数量
max.relation.tables=100
#初始订单表数量
max.order.tables=100
#需要新表时,所有已存在的表记录数达到这个值才开始创建
start.create.newTable.size=500000
#买个表的最大卖家数
max.seller.perTable=1000

元数据表结构基本是:

sellerNick    memberTableName  memberCount  orderTableName orderCount relationTableName relationCount  lastUpdateTime


一些关键代码:

protected String selectOrderTableName(){
		String sql="SELECT * FROM (SELECT  b.orderTableName, SUM(b.orderTableCount) AS COUNT FROM s_table_direction b  WHERE b.orderTableName IS NOT NULL GROUP BY b.orderTableName) a WHERE "
				+ "a.count=(SELECT MIN(COUNT) FROM (SELECT b.orderTableName,SUM(b.orderTableCount) AS COUNT FROM s_table_direction b  WHERE b.orderTableName IS NOT NULL GROUP BY b.orderTableName) a )";
		List<Map<String,Object>> directionMapList=queryForList(sql);
		Map<String,Object> directionMap=null;
		if(directionMapList!=null&&directionMapList.size()>=1){
			directionMap=directionMapList.get(0);
		}
		int index=getExistCurrentTable(TABLE_MAX_INDEX_TYPE.ORDER_TABLE_INDEX_TYPE);
		if(directionMap==null||directionMap.get("count")==null){
			updateToNextIndex(TABLE_MAX_INDEX_TYPE.ORDER_TABLE_INDEX_TYPE);
			return "s_trade_order_"+(index+1);
		}
		int orderTableCount=index;
		if(orderTableCount<SplitTableConstant.maxOrderTables){
			String tableName=(String)directionMap.get("orderTableName");
			sql="select count(distinct sellerNick) from s_table_direction where orderTableName=?";
			Long sellerCount=this.queryForObject(sql, Long.class, new Object[]{tableName});
			if(sellerCount>SplitTableConstant.maxSellerPerTable){
				updateToNextIndex(TABLE_MAX_INDEX_TYPE.ORDER_TABLE_INDEX_TYPE);
				return "s_trade_order_"+(index+1);
			}else{
				if(((BigDecimal)directionMap.get("count")).intValue()>SplitTableConstant.startCreateNewTableSize){
					updateToNextIndex(TABLE_MAX_INDEX_TYPE.ORDER_TABLE_INDEX_TYPE);
					return "s_trade_order_"+(index+1);
				}else{
					return tableName;
				}
			}
		}else{
			String tableName=(String)directionMap.get("orderTableName");
			return tableName;
		}
	}
	
	protected String genOrderTableName(String sellerNick){
		String sql="select * from s_table_direction where sellerNick=?";
		TableDirection direction=queryForBean(sql, TableDirection.class, new Object[]{sellerNick});
		if(direction==null){
			 direction=new TableDirection();
			 direction.setOrderTableName(selectOrderTableName());
			 direction.setSellerNick(sellerNick);
			 direction.setOrderTableCount(0L);
			 direction.setLastUpdateTime(new Date().getTime());
			 direction.setMemberSynStatus(MemberSynStatusEnum.INIT.getValue());
			 direction.setOrderSynStatus(OrderSynStatusEnum.INIT.getValue());
			 sql="insert into s_table_direction(sellerNick,orderTableName,orderTableCount,lastUpdateTime,memberSynStatus,orderSynStatus)values(:sellerNick,:orderTableName,:orderTableCount,:lastUpdateTime,:memberSynStatus,:orderSynStatus)";
			 update(sql, direction);
			 return direction.getOrderTableName();
		}else{
			if(StringUtils.isNotEmpty(direction.getOrderTableName())){
				return direction.getOrderTableName();
			}else{
				direction.setOrderTableName(selectOrderTableName());
				direction.setOrderTableCount(0L);
				direction.setLastUpdateTime(new Date().getTime());
				if(StringUtils.isNotEmpty(direction.getMemberTableName())) {
					direction.setMemberSynStatus(MemberSynStatusEnum.FINISH.getValue());
				}else{
					direction.setMemberSynStatus(MemberSynStatusEnum.INIT.getValue());
				}
				direction.setOrderSynStatus(OrderSynStatusEnum.INIT.getValue());
				sql="update s_table_direction set orderTableName=:orderTableName,orderTableCount=:orderTableCount,lastUpdateTime=:lastUpdateTime,memberSynStatus=:memberSynStatus,orderSynStatus=:orderSynStatus where sellerNick=:sellerNick";
				update(sql, direction);
				return direction.getOrderTableName();
			}
		}
	}

protected String selectRelationTableName(){
		String sql="SELECT * FROM (SELECT  b.relationTableName, SUM(b.relationTableCount) AS COUNT FROM s_table_direction b  WHERE b.relationTableName IS NOT NULL GROUP BY b.relationTableName) a WHERE "
				+ "a.count=(SELECT MIN(COUNT) FROM (SELECT b.relationTableName,SUM(b.relationTableCount) AS COUNT FROM s_table_direction b  WHERE b.relationTableName IS NOT NULL GROUP BY b.relationTableName) a )";
		List<Map<String,Object>> directionMapList=queryForList(sql);
		Map<String,Object> directionMap=null;
		if(directionMapList!=null&&directionMapList.size()>=1){
			directionMap=directionMapList.get(0);
		}
		int index=getExistCurrentTable(TABLE_MAX_INDEX_TYPE.RELATION_TABLE_INDEX_TYPE);
		if(directionMap==null||directionMap.get("count")==null){
			updateToNextIndex(TABLE_MAX_INDEX_TYPE.RELATION_TABLE_INDEX_TYPE);
			return "s_member_group_relation_"+(index+1);
		}
		int relationTableCount=index;
		if(relationTableCount<SplitTableConstant.maxRelationTables){
			String tableName=(String)directionMap.get("relationTableName");
			sql="select count(distinct sellerNick) from s_table_direction where relationTableName=?";
			Long sellerCount=this.queryForObject(sql, Long.class, new Object[]{tableName});
			if(sellerCount>SplitTableConstant.maxSellerPerTable){
				updateToNextIndex(TABLE_MAX_INDEX_TYPE.RELATION_TABLE_INDEX_TYPE);
				return "s_member_group_relation_"+(index+1);
			}else{
				if(((BigDecimal)directionMap.get("count")).intValue()>SplitTableConstant.startCreateNewTableSize){
					updateToNextIndex(TABLE_MAX_INDEX_TYPE.RELATION_TABLE_INDEX_TYPE);
					return "s_member_group_relation_"+(index+1);
				}else{
					return tableName;
				}
			}
		}else{
			String tableName=(String)directionMap.get("relationTableName");
			return tableName;
		}
	}
	
	protected String genRelationTableName(String sellerNick){
		String sql="select * from s_table_direction where sellerNick=?";
		TableDirection direction=queryForBean(sql, TableDirection.class, new Object[]{sellerNick});
		if(direction==null){
			 direction=new TableDirection();
			 direction.setRelationTableName(selectRelationTableName());
			 direction.setSellerNick(sellerNick);
			 direction.setRelationTableCount(0L);
			 direction.setLastUpdateTime(new Date().getTime());
			 sql="insert into s_table_direction(sellerNick,relationTableName,relationTableCount,lastUpdateTime)values(:sellerNick,:relationTableName,:relationTableCount,:lastUpdateTime)";
			 update(sql, direction);
			 return direction.getRelationTableName();
		}else{
			if(StringUtils.isNotEmpty(direction.getRelationTableName())){
				return direction.getRelationTableName();
			}else{
				direction.setRelationTableName(selectRelationTableName());
				direction.setRelationTableCount(0L);
				direction.setLastUpdateTime(new Date().getTime());
				sql="update s_table_direction set relationTableName=:relationTableName,relationTableCount=:relationTableCount,lastUpdateTime=:lastUpdateTime where sellerNick=:sellerNick";
				update(sql, direction);
				return direction.getRelationTableName();
			}
		}
	}

protected String selectMemberTableName(){
		String sql="SELECT * FROM (SELECT  b.memberTableName, SUM(b.memberTableCount) AS COUNT FROM s_table_direction b  WHERE b.memberTableName IS NOT NULL GROUP BY b.memberTableName) a WHERE "
				+ "a.count=(SELECT MIN(COUNT) FROM (SELECT b.memberTableName,SUM(b.memberTableCount) AS COUNT FROM s_table_direction b  WHERE b.memberTableName IS NOT NULL GROUP BY b.memberTableName) a )";
		List<Map<String,Object>> directionMapList=queryForList(sql);
		Map<String,Object> directionMap=null;
		if(directionMapList!=null&&directionMapList.size()>=1){
			directionMap=directionMapList.get(0);
		}
		int index=getExistCurrentTable(TABLE_MAX_INDEX_TYPE.MEMBER_TABLE_INDEX_TYPE);
		if(directionMap==null||directionMap.get("count")==null){
			updateToNextIndex(TABLE_MAX_INDEX_TYPE.MEMBER_TABLE_INDEX_TYPE);
			return "s_member_"+(index+1);
		}
		int memberTableCount=index;
		if(memberTableCount<SplitTableConstant.maxMemberTables){
			String tableName=(String)directionMap.get("memberTableName");
			sql="select count(distinct sellerNick) from s_table_direction where memberTableName=?";
			Long sellerCount=this.queryForObject(sql, Long.class, new Object[]{tableName});
			if(sellerCount>SplitTableConstant.maxSellerPerTable){
				updateToNextIndex(TABLE_MAX_INDEX_TYPE.MEMBER_TABLE_INDEX_TYPE);
				return "s_member_"+(index+1);
			}else{
				if(((BigDecimal)directionMap.get("count")).intValue()>SplitTableConstant.startCreateNewTableSize){
					updateToNextIndex(TABLE_MAX_INDEX_TYPE.MEMBER_TABLE_INDEX_TYPE);
					return "s_member_"+(index+1);
				}else{
					return tableName;
				}
			}
		}else{
			String tableName=(String)directionMap.get("memberTableName");
			return tableName;
		}
	}
	



发布了391 篇原创文章 · 获赞 386 · 访问量 312万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览