业务场景为saas商品表
场景:
1.租户运营查询。
2.用户在某个租户内查询。
3.用户查询订单后中嵌套查询商品。
1.使用tenantId作为分表键。
优点:
如果使用tenantId作为分表键,三个场景都是满足的。而且简单。
缺点:
但是有数据倾斜的问题,某些租户商品很多,热点商品也多。这种一般不考虑在数据库实现,通常上缓存。
2.使用基因法。
采用基因法,将租户id拼接在商品后面。
具体方案:
假设商品id 64位
前60位使用分布式id算法:
1.雪花算法,优点简单,缺点需要时钟同步,不然会冲突。
2.分段法,单独起一个组件专门用于给客户端分段,例如一次性获取1000个id,优点不会冲突,缺点存在单点问题,需要考虑组件的高可用。参考百度开源:UidGenerator
后4位,通过取租户id%分片数,得到后四位。
查询时,通过截取后四位取余即可知道分片键。
这样,租户商品分在一个表,通过商品id,通过租户id,都可以查询到分片键。
单表深分页问题:
假设sql:
select * from table where limit 100000, 10
mysql扫描了前面100010,再返回的数据。因为mysql非顺序存储,没有办法直接定位到具体的第n行,只能一行一行过滤,直到第n行。
分页查询常见优化:
tips:数据库如果不带order by,排查的方式看查询条件,如果条件有索引,那么按照索引排序,如果没有,按照主键id排序。
如何优化?
1.所以使用子查询
select * from table where id >= (select id from table limit 100000,1)limit 10
使用了id主键索引更快。
2.使用join
select * from tb_cuser a inner join (select id from tb_cuser limit 499900,1) b where a.id>b.id limit 100
子查询和join没有太大的差别,本质都是减少回表查询,扫描的行数还是一样的。
这两个在复杂查询场景依然有问题,复杂场景可能没有办法走到索引,依然有大量回表。
3.标记法,通过记录上次的id再limit
select * from table where id > ? limit 100
这个方式很快但是有问题,就是id需要连续。
以上都是单表查询,如果多表多库的情况更复杂。
例如查询所有租户最近一百天的商品,按照创建日期排序。
涉及的问题:
1.需要全表路由
2.就算按照创建日志索引,减少回表,但是在深分页情况,依然需要扫描很多的数据。
1.应用层处理
如果数据量不大的,在n个表上前获取前n条数据,在应用层排序前n条。
优点:简单
缺点:深分页时,性能堪忧
2.异构索引,使用ES冗余数据
实际情况更复杂,最好ES查询。
如果分表后,涉及非分表键的多维度查询,最好冗余数据,有几个维度,冗余几份。
总结回答:
单表深分页问题,如果id连续,通过标记法查询,如果不连续,通过子查询或者join的方式减少回表查询。
分库分表后,考虑基因法,如果依然有多维度查询,做数据冗余,有几个维度,冗余几份数据。数据之间通过binlog使用中间件canal同步。
ps:如果有大商家,需要特殊处理,比如单独创建一个表。