电商分库分表经典实例
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客户端或者代理方案，也可以尝试下，不过都有一些坑未必想象中好用，扩容迁移都要考虑。