电商分库分表经典实例

1.表结构和分表策略

以电商订单和商品为例分表。

1.1交易中心订单表t_order

*order_id  订单主键
order_no  唯一业务订单编号
order_amt  订单总金额，单位分
order_create_time  创建时间
order_modify_time  最近修改时间
order_status  为演示简单统一一个订单状态
buyer_id  购买用户id
merchant_id  商户id
...

订单表t_order水平分割到n个物理库，为保证数据均匀分布到n个库中，一般根据hash(order_id)%n，sharding column不使用购买人id或商户id是因为他们的订单有多有少，会导致每个库数据不均匀。

1.2 交易中心订单商品表t_order_goods

order_goods_id  订单商品主键
*order_id  订单id
merchant_goods_id 商户商品id
unit_price  销售单价，单位分
sale_pcs  销售商品件数
sale_amt  商品销售总金额，单位分
...

而商户商品表t_order_goods表和t_order强依赖，很需要分在同一个库上，所以也使用hash(order_id)%n做分割。

1.3 商品中心商户商品表g_merchant_goods

*merchant_goods_id  商户商品id，主键
merchant_id  商户id
goods_id  商品id，假设平台方维护商品和类目
unit_price  销售单价
goods_name  商品标题
goods_desc  商品描述
...

hash(merchant_goods_id)%n做切分会均匀一些,商户足够多的时候才切分。

1.4 用户中心商户表u_merchant

*merchant_id 商户主键
merchant_name 商户名称
...

商户足够多的时候才按照hash(merchant_id)%n切分。

2.分表带来的副作用,查询问题

2.1 跨库join问题

例如: select * from t_order t inner join u_merchant m on t.merchant_id=m.merchant_id 不能执行因为跨库了, 只能先查询出t_order并从记录中获取对应所有的merchant_id, 再通过这merchant_id到对应的数据库的商户表查询商户信息, 最后内存里面把t_order信息和u_merchant信息串联起来。

2.2 非sharding column作为查询条件

例如: select * from t_order where merchant_id in (1,2,3,4,5)

t_order被水平切分到n个库, 从merchant_id的值无法定位具体在哪些库，所以一般情况下只能在n个库执行以上SQL，而这样操作效率低下且难以维护。

这些问题我们需要冗余些数据, 例如增加冗余表 m_merchant_order

*merchant_id 商户id
order_id 订单id
...

hash(merchant_id)%n, 我们可以在对应库分别查询:

select order_id from m_merchant_order where merchant_id=1;
select order_id from m_merchant_order where merchant_id=2;
...
select order_id from m_merchant_order where merchant_id=5;

获得所有order_id之后,在hash(order_id)%n所有的t_order库查询对应的order详情即可。

空间换时间，也有些维护成本,新增订单时需要同步到m_merchant_order中。这种冗余表我们称为异构索引表, 我们通常是建议冗余索引, 不冗余其它订单信息, 否则订单同步的又需要同步这些冗余表维护成本就不小了。

2.3 分页查询

订单数据分散到不同的库之后, 想重新排序分页是有些麻烦的。假设分页按时间排序select * from t_order order by order_create_time

(1) Top N分页

到n个t_order数据库中, 获取所需翻页的第x页前的所有订单, 最后n个数据库中的订单内存排序, 获取到第x页订单列表。

如果使用mycat等中间件, 正常的分页sql会被类似改写为以上的top n SQL, 翻页数越大性能越差, 需要谨慎使用。

(2) 业务折衷,不支持跳转页数

t_order n个库执行第一页分页, select * from t_order order by order_create_time limit 10; 获取最大的时间, 假设为${maxOrderTime}。

则第二页为 select * from t_order where order_create_time > ${maxOrderTime} limit 10;

总是记录当前分页的最大时间, 同理，这样就只能上一页，下一页这样去分页了。

(3) 二次查找

第一次查询n个库select * from t_order order by order_create_time offset x/n limit y; 获取最小的order_create_time记为${minOrderTime}, 记录每个库最大order_create_time

第二次查询n个库 select * from t_order order by order_create_time where order_create_time between ${minOrderTime} and ${nMaxOrderTime}. 因为每个库记录有序，所以可以计算${minOrderTime} 在n个库中相对的偏移量, 继而计算出${minOrderTime}在全局n个库中的全局偏移量。最后基于这个偏移量在二次查询返回的所有记录中则可以查询出offset x limit 10的所有记录。

具体细节可以参考 https://mp.weixin.qq.com/s/h99sXP4mvVFsJw6Oh3aU5A

(4) 更复杂的查询

例如淘宝的商品搜索，很多维度，这时单纯使用SQL是比较困难的，可以考虑ElasticSearch 和Solr商品录入时做多维度的索引。

3. 分库分表之外的选择

如果不想折腾，可以考虑下TiDB, OceanBase这种新型的分布式数据库，基本功能应该也趋于稳定了，性能也还可以，专注于解决业务也好。

而一些分库分表的中间件例如mycat, shardingsphere客户端或者代理方案，也可以尝试下，不过都有一些坑未必想象中好用，扩容迁移都要考虑。

文章目录