Optimizing DBLE Distributed Database Middleware: Cases of Query Push‑Down Failures and Their Solutions

1. Introduction

When using a distributed database middleware, business tables are split into multiple sub‑tables according to a specific sharding algorithm. The middleware hides the split details, parses client SQL requests, routes them to backend databases, and merges the result sets. Ideally each statement is fully pushed down to all backend nodes for parallel execution, but some join queries cannot achieve this, causing the DBLE layer to perform additional merging, increasing CPU usage, execution time, and in extreme cases making the service unavailable.

2. Demonstration and Optimization

Environment Check DBLE version: 2.19.11.1 MySQL version: 5.7.28 Sharded tables: h_tempinvm, h_acsn Global table: brhm Sharding rule: stringhash Node count: 4

The following four examples demonstrate situations that increase DBLE pressure and how to optimize them.

2.1 Inconsistent Sharding Rules

Conclusion: When the sharding rules of the tables involved in a join differ, the join cannot be correctly pushed down.

Sharded tables h_acsn and h_tempinvm join query:

Sharding rule configuration shows different function definitions:

Execution plan indicates DBLE splits the statement, scans both tables on each node, merges and joins results at the DBLE layer.

After adjusting the sharding rule to be consistent:

New execution plan shows the statement is fully pushed down to backend nodes, with only a MERGE operation at the DBLE layer.

2.2 Join Condition Not Using Sharding Key

Conclusion: If the join condition does not use the sharding key, the join cannot be correctly pushed down.

Join query for h_acsn and h_tempinvm (condition missing sharding key):

Sharding rule configuration (different from example 1):

Execution plan again shows splitting and DBLE‑layer JOIN, similar to example 1.

2.3 Global Table Position Impact

Conclusion: When a global table is used as the driving table, the statement cannot be correctly pushed down.

Join where global table brhm drives sharded table h_acsn:

Sharding rule:

Execution plan shows the statement is not fully pushed down.

After swapping roles (global table becomes the driven table):

New execution plan shows full push‑down and DBLE‑layer MERGE; a temporary workaround is to convert the global table to a sharded table when logic cannot be changed.

2.4 Multiple Sharded Tables Joined with a Global Table

Conclusion: This issue is caused by a bug in the global‑table handling logic.

Join of sharded tables h_acsn, h_tempinvm with global table brhm:

Sharding rule configuration:

Execution plan shows both sharded tables are pushed down, while the global table is processed separately, resulting in a DBLE‑layer JOIN. The temporary solution is again to convert the global table to a sharded one.

3. Summary

Inconsistent sharding rules (example 2.1) and joins that do not use sharding keys (example 2.2) can be avoided during project design by ensuring the function configuration keeps partitionCount[] , partitionLength[] and hashSlice consistent.

The global‑table position issue (example 2.3) has been reported to the community and will be fixed in future releases.

Problems with multiple sharded tables and global tables (example 2.4) were resolved in DBLE version 2.19.11.99; upgrading the product eliminates the bug.

Overall, ensure consistent sharding rules and use sharding keys in join conditions so that SQL can be fully pushed down to backend nodes; otherwise DBLE will split the statement and perform costly MERGE/JOIN operations at the middleware layer.