You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Fottas (via GitHub)" <gi...@apache.org> on 2025/09/02 08:01:51 UTC
[I] [Feature Request] IN Predicate Sharding Optimization [shardingsphere]
Fottas opened a new issue, #36454:
URL: https://github.com/apache/shardingsphere/issues/36454
Problem: Currently ShardingSphere sends all values in IN predicates to every shard, causing unnecessary data transfer and computation. For example, WHERE id IN (1,2,3,4) is sent as-is to all shards, even though based on sharding keys we can determine that values 1,3 belong to shard A while values 2,4 belong to shard B.
Solution: Route-aware IN predicate splitting that distributes values only to their target shards based on sharding algorithms.
Technical Design
Implementation Principle: Parse each value in IN expressions, invoke existing sharding algorithms to calculate target shards, then rewrite IN clauses for each RouteUnit to contain only values belonging to that shard.
Core Components:
1. ShardingInPredicateValue - Encapsulates IN values with their target route information
2. ShardingInPredicateToken - SQL token that rewrites IN clauses per route, filtering values and parameters
3. ShardingInPredicateTokenGenerator - Analyzes IN expressions and calculates shard distribution using existing sharding algorithms
4. ParameterFilterable - Cross-module decoupling interface that enables parameter filtering capability between modules, since ShardingInPredicateToken is in the sharding module while RouteSQLRewriteEngine is in the infra module
Key Features:
- Supports both standard and complex sharding strategies
- Handles parameter markers and literals
- Optimizes single-value cases to equality comparisons
- Achieves module decoupling through interface abstraction, allowing core engine to work without direct dependencies on sharding token implementations
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request] IN Predicate Sharding Optimization [shardingsphere]
Posted by "Fottas (via GitHub)" <gi...@apache.org>.
Fottas commented on issue #36454:
URL: https://github.com/apache/shardingsphere/issues/36454#issuecomment-3251338992
Hi @RaigorJiang @strongduanmu,
Thanks for your attention and support! I'd like to check the review status:
1. Are there any issues with the current technical approach that need improvement?
2. Do I need to supplement more documentation or tests?
Looking forward to your feedback, thanks!
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request] IN Predicate Sharding Optimization [shardingsphere]
Posted by "Fottas (via GitHub)" <gi...@apache.org>.
Fottas commented on issue #36454:
URL: https://github.com/apache/shardingsphere/issues/36454#issuecomment-3609814922
### 1. Parameter filtering modeling
Current tokens can only rewrite SQL via `toString(RouteUnit)`, but cannot tell the rewrite engine which parameters to remove. For IN optimization, I need both capabilities: rewrite SQL per route AND filter parameters per route.
That's why I added `ParameterFilterable` interface in `infra.rewrite.core` - it's an extension point that lets tokens communicate their parameter filtering needs to the engine. Currently designed for IN optimization, but the mechanism is generic enough for other scenarios if needed.
### 2. Compatibility with existing token generators
The interface is optional. Existing tokens don't implement it, so they're completely unaffected. When no filterable tokens exist, the engine returns original parameters unchanged.
### 3. Custom algorithm compatibility
I don't modify any SPI interfaces. For each IN value, I call the algorithm's existing `doSharding()` method (same as routing does) to determine target tables, then map them to route units. If the algorithm returns null or empty, the value is marked as orphan and excluded from all routes.
Since I'm using the same SPI that routing uses, any algorithm that works with routing will work with this optimization.
### 4. Complex SQL support
The optimization works at the parameter level, not SQL structure level. It applies to any query using `StandardParameterBuilder` (all non-batch queries including subqueries and JOINs), regardless of SQL complexity.
In the token generator, I currently support `StandardShardingAlgorithm` and `ComplexKeysShardingAlgorithm`. For each IN value, I invoke the algorithm to determine target tables. For other strategy types, the value is included in all routes (original behavior), ensuring compatibility without requiring changes.
---
## PR Split Plan
### PR 1: Add ParameterFilterable interface
Introduce the parameter filtering extension point in `infra.rewrite.core`. This interface allows tokens to declare which parameters should be removed for each route, enabling route-aware parameter filtering in the rewrite engine.
---
### PR 2: Integrate parameter filtering into RouteSQLRewriteEngine
Implement the parameter filtering mechanism in the rewrite engine. When processing each route, the engine checks for filterable tokens and applies parameter filtering accordingly. When no filterable tokens exist, it maintains original behavior with zero overhead.
---
### PR 3: Add data structures (ShardingInPredicateValue and Token)
Introduce the data model for IN predicate optimization. `ShardingInPredicateValue` represents a single IN value with its target route information. `ShardingInPredicateToken` generates optimized SQL per route and implements parameter filtering, with single-value optimization (IN → =) for better database performance.
---
### PR 4: Simple optimization - literal values only
First end-to-end working version. The token generator detects IN predicates on sharding keys, calls sharding algorithms to determine target routes for each value, and generates tokens with route distribution information. This PR handles only literal values to keep the change manageable.
---
### PR 5: Add parameter marker support
Extend the generator to support parameter markers (`?`) in IN predicates. Implement `ParametersAware` interface to access bound parameters, enabling optimization for prepared statements.
---
### PR 6: Complex algorithm support
Extend support to `ComplexKeysShardingAlgorithm` for multi-column sharding keys. The generator builds appropriate sharding values and invokes the algorithm to determine target routes.
---
### PR 7: Nested expression support
Support IN predicates nested in AND/OR expressions. The generator recursively extracts IN predicates from binary operations, handling arbitrary nesting depth and multiple IN predicates in the same query.
---
@terrymanu Thanks again for the detailed feedback. I've tried to address the architectural concerns you raised and broken down the work into smaller PRs.
I'm not entirely sure if this is what you're looking for - if I've misunderstood anything or if you'd like me to approach it differently, please let me know. Happy to adjust the plan based on your guidance.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request] IN Predicate Sharding Optimization [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #36454:
URL: https://github.com/apache/shardingsphere/issues/36454#issuecomment-3592445436
Thank you very much for the contribution and for providing the detailed design explanation in the issue.
The optimization goal (sharding-aware IN predicate rewrite) is valuable, and the high-level design direction is reasonable.
However, after reviewing the current PR, the overall change set is still too large and crosses several core modules:
1. The modification scope is very wide
This PR touches multiple layers at the same time, including:
Sharding algorithm SPI
Sharding rewrite token system
RouteSQLRewriteEngine in infra rewrite core
New interface (ParameterFilterable)
SQL rewrite pipeline integration
Many new types + large generator logic
IT tests + binder interactions
Any issue in these areas can introduce subtle problems such as wrong routing, parameter index mismatches, inconsistent rewrite results, or silent data deviation.
2. The PR introduces structural changes that need architectural discussion
The approach of filtering parameters per-route using a new interface and extending the rewrite engine is conceptually correct, but it changes the core rewrite flow.
Such changes require agreement on:
How parameter filtering should be modeled
Whether this should be a generic rewrite capability or sharding-specific
How to guarantee compatibility with existing token generators
How to avoid breaking custom sharding algorithms
How to handle complex SQL (subqueries, nested IN, multi-sharding-keys)
These points still need further design refinement before implementation can safely move forward.
3. The change is too large to review and validate safely in one PR
This PR includes 1300+ lines across 10+ files.
Given the sensitivity of the modules involved, it is not suitable to merge such a large change at once.
We risk introducing regressions that are difficult to detect through unit tests alone.
4. Suggested next steps
To proceed safely, we recommend:
Continue design discussion in the issue (e.g., #36454)
Clarify module boundaries, SPI behavior, rewrite engine responsibilities, and parameter-filter design.
Split the implementation into smaller, incremental PRs, for example:
Introduce the new rewrite extension point in isolation.
Add minimal sharding IN optimization for simple cases.
Gradually extend to complex sharding, placeholders, subqueries, etc.
Add more targeted tests per step so review becomes manageable and regressions easier to detect.
Conclusion
The problem and the proposed direction are reasonable, but the current PR is too large and involves core architecture changes. We need further design iteration and smaller, incremental PRs before it can be safely accepted.
Thank you again for the effort—happy to continue the discussion and help refine the approach.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request] IN Predicate Sharding Optimization [shardingsphere]
Posted by "Fottas (via GitHub)" <gi...@apache.org>.
Fottas commented on issue #36454:
URL: https://github.com/apache/shardingsphere/issues/36454#issuecomment-3594144293
Thank you for the detailed feedback!
I'll continue the design discussion in #36454 first, then submit smaller incremental PRs based on the refined approach.
Appreciate the guidance!
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org