terrymanu commented on issue #23635:
URL:
https://github.com/apache/shardingsphere/issues/23635#issuecomment-3637190183
## Understanding
- Ask: Add a fine-grained control so that “same-database multi-route
queries auto-merged into a single UNION ALL” can be limited by a threshold of
merged sub-queries, preventing overly long SQL from hitting MySQL
max_execution_time.
- Current behavior: For simple queries (no subquery/Join/Order
By/Limit/Lock) routed to the same datasource, the rewriter merges actual SQLs
into one UNION ALL to reduce connection usage.
## Root Cause
- RouteSQLRewriteEngine always merges same-database route units via
String.join(" UNION ALL ", …) to minimize same-database connections.
- max-connections-size-per-query only affects connection
grouping/ConnectionMode and does not control UNION ALL or batching.
- With many shards and a low MySQL max_execution_time, the long merged SQL
may run effectively serially and exceed the timeout.
## Analysis
- The default strategy reduces connections and handshakes, but in
high-shard and strict-timeout scenarios, merging sacrifices parallelism and can
approach the sum of per-shard times.
- Introducing a “merge upper bound” keeps default compatibility while
giving timeout-sensitive workloads a tunable merge size.
## Conclusion (proposal + PR welcome)
- Add a global property max-union-size-per-datasource (default
Integer.MAX_VALUE to keep current behavior):
- If same-database route count ≤ threshold: keep UNION ALL merge.
- If count > threshold: skip merge (or batch, each batch ≤ threshold)
to regain parallelism and reduce single-SQL runtime.
- Implementation sketch:
1. Add max-union-size-per-datasource to the configuration property
enum with a sensible default.
2. In RouteSQLRewriteEngine’s merge decision, read the property; when
exceeding the threshold, either do not merge or split routeUnits into batches
and merge per batch.
3. Add unit tests for: under-threshold merge; over-threshold
no-merge/batching; SQL text and parameter correctness.
4. Document the new property, default, and usage guidance in the
common properties docs (official entry:
https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/properties/
).
- We warmly invite community contributors to submit a PR implementing and
testing this; we’ll gladly help review and validate.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
