Comparison of Cross‑Node Join Queries Between DBLE and Mycat Middleware

The background describes a retail backend that uses two distributed middlewares—Mycat and DBLE—sharing the same MySQL instances and identical sharding rules. A cross‑node join query on financial settlement details shows inconsistent results between the two middlewares.

DBLE is introduced as an open‑source enterprise‑grade middleware (nicknamed “MyCat Plus”) known for stability and community support.

Environment preparation involves building a virtual‑machine test architecture where DBLE and Mycat share the same database. The test software versions are DBLE 2.18.10.1‑cb392c3, Mycat 1.6‑RELEASE, and MySQL 5.7.21‑log.

Table structures are defined as:

CREATE TABLE `t_bl_detail` ( `unit_num` int(11) DEFAULT NULL, `tenantid` int(11) DEFAULT NULL, `detail_num` int(11) DEFAULT NULL, `balance_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_bl_super_detail` ( `unit_num` int(11) DEFAULT NULL, `sup_id` int(11) DEFAULT NULL, `tenantid` int(11) DEFAULT NULL, `bl_unit_num` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sharding rule configuration uses a modulo‑4 algorithm. DBLE schema configuration:

<schema name="testdb" sqlMaxLimit="100" dataNode="dn01"> <table name="t_bl_detail" rule="mod4Series" dataNode="dn01,dn02,dn03,dn04"/> <table name="t_bl_super_detail" rule="mod4Series" dataNode="dn01,dn02,dn03,dn04"/> </schema> <tableRule name="mod4Series"> <rule> <columns>unit_num</columns> <algorithm>mod4DB</algorithm> </rule> </tableRule> <function name="mod4DB" class="Hash"> <property name="partitionCount">4</property> <property name="partitionLength">1</property> </function>

Mycat schema configuration (simplified):

<schema name="testdb" sqlMaxLimit="100" dataNode="dn01"> <table name="t1" rule="mod4Series" dataNode="dn01,dn02,dn03,dn04"/> <table name="t2" rule="mod4Series" dataNode="dn01,dn02,dn03,dn04"/> </schema> <tableRule name="mod4Series"> <rule> <columns>id</columns> <algorithm>mod4DB</algorithm> </rule> </tableRule> <function name="mod4DB" class="io.mycat.route.function.PartitionByMod"> <property name="count">4</property> </function>

Test data insertion uses the following statements:

insert into t_bl_detail values(1,3,123443,'2019-01-01 00:00:00'); insert into t_bl_detail values(2,3,3423524,'2019-01-01 00:00:00'); insert into t_bl_detail values(3,3,245245,'2019-01-01 00:00:00'); insert into t_bl_detail values(4,4,356356,'2019-01-01 00:00:00'); insert into t_bl_super_detail values(1,10342,3,2); insert into t_bl_super_detail values(2,12355,3,2); insert into t_bl_super_detail values(3,62542,3,3); insert into t_bl_super_detail values(4,74235,4,1);

The cross‑node join query executed on both middlewares is:

select m.unit_num, m.balance_date from t_bl_detail m join t_bl_super_detail n where n.tenantid = 3 and m.unit_num = n.bl_unit_num;

Running the query directly on MySQL yields the expected result set. DBLE reproduces this result, while Mycat returns a truncated set missing rows from other nodes.

Execution plan analysis shows that DBLE first fetches relevant rows from each shard, merges them in the middleware, and then performs the join, producing correct results. Mycat, however, pushes the join to each node and simply aggregates the partial results, which leads to loss of cross‑node join data.

Conclusion DBLE provides an optimized handling of cross‑node joins by merging shard data before joining, whereas Mycat’s current strategy cannot correctly process such joins, resulting in incomplete query results.