如何优雅的遍历Mycat分库表

Mycat作为数据库的中间件,被许多项目用来做关系型数据库的分库分表;我目前所在的项目由于业务和数据量的增长,也使用Mycat来进行分库分表。最近由于一个需求需要遍历一个分库表的数据,对数据进行相应的业务操作。

例如我们有一个user表,该表被分为16个库,那么可以如何高效而优雅的实现对user表中所有数据的遍历呢?

使用表的主键ID分页遍历
这种方法是最直接了当,也是最容易想到的;例如使用如下的sql语句分批查询表中的数据:

select * from user order by id
limit a, 10000;

该sql语句以每批1万条纪录的方式遍历user表,每次查询使用上次id的最大值替换a的值,直到没有纪录返回。

这种实现固然可行,但其最大的问题在于效率,每次查询都会从所有的分库中各获取1万条纪录,那么Mycat总共得到16万行的数据,java培训再进行排序,取前1万条记录返回。如此遍历下来,总共查询了user表总记录数16倍的数据量,而且还进行排序,这种消耗无疑是巨大的。如果分库数量更多,资源的消耗和浪费更严重。此方案在性能上是不可接受的。

单独遍历每个分库
Mycat提供了通过注解的方式指定从某个分库执行sql语句,注解语法为

/!mycat:dataNode = ${dn} /

其中,dn为分片节点的名称;这样,我们就可以对上述的查询语句进行改造,指定分片节点进行查询遍历,如:

/!mycat:dataNode = dn1 /
select * from user order by id
limit a, 10000;

使用以上的sql语句,先对名称为dn1分片节点的数据进行遍历,然后修改注解中的节点名称,继续遍历其他节点;把所有的节点都遍历完成后,user表的数据也就遍历完毕。这种遍历方式大大提高了效率,不管user表被分成多少个分片,所有的数据都只被读取一次。

此方法的不足之处是必须事先获取所有分片节点的名称,写在代码或配置文件中;当分片进行扩容时,分片节点数量增加,配置文件或代码就必须做对应的修改,否则就获取不到表中所有的数据。

自动获取所有分片节点名称
那么,有没有一种方法可以实时自动的获得分库表的所有分片节点名称呢?

Mycat除了数据操作端口(默认8066)外,还提供了管理端口(默认9066),通过连接该端口,可以查看Mycat的运行数据并做管理操作。通过管理端口登录Mycat后,可以通过show @@datanode命令查看分片节点信息,如下所示(结果已略去部分列):
如何优雅的遍历Mycat分库表_第1张图片
NAME列即为节点名称,只要获取到该列所有的值即可。但是管理端口有较高的操作权限,一般情况下出于安全原因,该端口仅运维有权限登录操作,应用无权访问。

还有其他更好办法吗?

这时想到了执行计划, 对,执行计划! Mycat支持explain命令查看sql语句的执行计划,对于分库表,会返回sql语句执行需要查询哪些节点;这样我们就可以通过构造一个需要查询所有节点的sql语句来获取全部节点信息,例如通过如下所示的语句:
如何优雅的遍历Mycat分库表_第2张图片
DATA_NODE列即为节点名称;节点取到之后,剩下的就简单了,无非是对各个节点单独遍历。这种方式通过查询一次Mycat就获得了所有分片节点的名称,java培训而且数据库的连接也可以复用,真正做到了既高效又优雅。

当然,以上的解决方案是限定于Mycat与MySQL的范围之内,如果数据可以同步到NoSQL中,那又是从另外的角度去解决了。

你可能感兴趣的