第一种：
优点:支持进一步分片
缺点:schema配置繁琐
注解式 /*!mycat:schema=[schemaName] */ 注意:这在navicat 里面是会报错的,请用命令行登陆mycat 来测试
mysql> explain /*!mycat:schema=USER1 */ select * from order;
可以在每个sql语句前面添加此注解，Mybatis 可以重写 MappedStatement 的 getBoundSql 来添加。
不管使用什么方式，感觉这都很搓。还要手动在Mycat schema.xml 中添加很冗长的 配置。
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USER1" checkSQLschema="false" sqlMaxLimit="10000">
.....
</schema> <schema name="USER2" checkSQLschema="false" sqlMaxLimit="10000">
......
</schema>
以下省略无数个 schema
第二种：使用枚举分片实现多租户
优点:schema.xml 配置相对简洁
缺点:不可进一步分片
explain /*!mycat:schema=[schema] */ /*!mycat:dataNode=dn1 */ select * from order;
创建分片枚举规则文件.
cat sharding-by-enum.txt
= = =
修改rule.xml的 function
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">sharding-by-enum.txt</property>
<property name="type">0</property>
<!-- <property name="mapFile">partition-hash-int.txt</property> -->
</function>
测试枚举分片命中率,这就可以使用枚举分片,达到多租户效果
枚举分片,解决查询分片命中问题
mysql> explain select * from order a left join detail b on a.id = b.orderId where a.sharding_id = 0;
+-----------+----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------------------------------------------+
| dn1 | select * from order a left join detail b on a.id = b.orderId where a.sharding_id = 0 |
+-----------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
两个表,都有枚举分片字段
mysql> explain select * from order a left detail b on a.id = b.orderId where a.sharding_id and b.sharding_id = 0;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------+
| dn1 | select * from order a left detail b on a.id = b.orderId where a.sharding_id and b.sharding_id = 0 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql> 没有命中条件,造成全盘扫描 mysql> explain select * from order a left join detail b on a.id = b.orderId;
+-----------+----------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------------------+
| dn1 | select * from rder a left join detail b on a.id = b.orderId |
| dn2 | select * from rder a left join detail b on a.id = b.orderId |
| dn3 | select * from rder a left join detail b on a.id = b.orderId |
+-----------+----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec) UPDATE Database changed
mysql> explain update detail set itemNum='' where id = 8079
-> ;
+-----------+--------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------+
| dn1 | update detail set itemNum='' where id = 8079 |
| dn2 | update detail set itemNum='' where id = 8079 |
| dn3 | update detail set itemNum='' where id = 8079 |
+-----------+--------------------------------------------------------------+
3 rows in set (0.02 sec) 分片字段不能被更新
mysql> explain update detail set itemNum='',shardingId=0 where id = 8079;
ERROR 1064 (HY000): Sharding column can't be updated DETAIL->SHARDINGID 加上分片字段 mysql> explain update order set itemNum='100' where id = 8079 and shardingId = 0;
+-----------+---------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------------------------------+
| dn1 | update order set itemNum='100' where id = 8079 and shardingId = 0 |
+-----------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec) 删除,同样全盘扫描
mysql> explain delete detail where id = 8079;
+-----------+--------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------+
| dn1 | delete detail where id = 8079 |
| dn2 | delete detail where id = 8079 |
| dn3 | delete detail where id = 8079 |
+-----------+--------------------------------------------+
3 rows in set (0.00 sec) 强制命中条件
mysql> explain delete detail where id = 8079 and shardingId = 0;
+-----------+---------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------------+
| dn1 | delete detail where id = 8079 and shardingId = 0 |
+-----------+---------------------------------------------------------------+
1 row in set (0.00 sec) 全局表与分配表 inner join 以及 left jion right jion 都可以命中枚举
mysql> explain select * from user a inner join order b where a.id=b.userId and b.shardingId = 0;
+-----------+----------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------------------------------+
| dn1 | select * from user a inner join order b where a.id=b.userId and b.shardingId = 0|
+-----------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql> explain select * from user a right join order b on a.id = b.userId where b.shardingId = 0;
+-----------+------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------------------------------------+
| dn1 |select * from user a right join order b on a.id = b.userId where b.shardingId = 0|
+-----------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
无论是用哪一种方式,而枚举方式的schema还简单一点
只需要添加dataNode,即可,没有第一种的方式那么膨胀XML
<dataNode name="dn1" dataHost="localhost1" database="db1" />
关于这个分片标识可以记录再session里面(配合redis session ,不再考虑session 导致的内存溢出问题,万一溢出,那非常值得开心,用户量已经那么高了)
/**
* Created by laizhenwei
*/
@Component("sessionAttributes")
@Scope(scopeName = WebApplicationContext.SCOPE_SESSION, proxyMode = ScopedProxyMode.TARGET_CLASS)
public class SessionAttributes implements Serializable { private static final long serialVersionUID = -8521804511291179982L; //用户分片标识
private Integer shardId; /**
* 获取当前用户的分片标识
*/
public Integer getCurrentShardId() {
Optional<Integer> shardIdOptional = Optional.ofNullable(shardId);
return shardIdOptional.orElseThrow(() -> new RuntimeException("无法获取当分片标识!"));
}
以上方式不支持多线程.请不要让 SessionAttributes 离开Controller 作用域.如果整个程序都不涉及多线程,那么随意在那一层注入
支持多线程方式,借助 InheritableThreadLocal然后写一个过滤器
/**
* Created by laizhenwei
*/
public class ShardContextFilter implements Filter { @Autowired
private SessionAttributes sessionAttributes; @Override
public void destroy() {
// Do nothing
} @Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
ContextHolder.create(createContext());
chain.doFilter(request, response);
} @Override
public void init(FilterConfig config) throws ServletException {
// Do nothing
} private Context createContext() {
//....
return context;
} }
当然可以使用注解来过滤需要分片命中的请求的规则
@WebFilter(filterName="contextFilter",urlPatterns = {"/addOrder/*","/whatever/*"})
我喜欢在Security需要验证的资源添加到过滤器链,这样我不需要考虑资源规则问题
http.addFilterAfter(contextFilter(), FilterSecurityInterceptor.class);
最后总结一下:
如果单个用户的数据还需要进一步分片,那么只能使用schema的注解拦截实现,
如果单个用户数据,不需要再进一步分片,那么使用枚举分片会简单一些
但是,无论使用哪种方式,都难免需要人工处理schema.xml,除非开始就预建了很多schema或者dataNode,否则再添加的时候,mycat不能像nginx一样reload配置文件,必须停机重启读取配置文件
mongodb也有类似的多租户分片规则[tag]分片,但是这样无法处理分片数据不均衡,所以还是不建议使用tag分片.
定制业务适合的片键,让mongodb自动分片,能很大程度减少维护成本.