young138120 commented on issue #25251: URL: https://github.com/apache/shardingsphere/issues/25251#issuecomment-3539579072
> Hello, I understand your concern about the join performance between single tables and sharding tables. Let me clarify the root cause and provide solutions. > > ## Root Cause Analysis > This issue is not a bug in ShardingSphere, but rather a limitation of the architecture design: > > 1. Join Limitation: In ShardingSphere, sharding tables cannot directly join with single tables. This is a designed architectural limitation. > 2. Query Optimizer Constraint: When a single table joins with sharding tables, ShardingSphere cannot perform effective query routing optimization, leading to scanning all sharding nodes. > > ## Recommended Solutions > Solution 1: Broadcast Table (Recommended) > > Convert your single table to a broadcast table: > > spring: shardingsphere: rules: broadcast: tables: - your_single_table_name > > Pros: > > * Aligns with ShardingSphere design patterns > * Excellent join performance > * Simple configuration > > Cons: > > * Requires maintenance across all sharding databases > * Data synchronization needs to be considered > > Solution 2: Federation Query (Not Recommended) > > Enable federation queries (experimental feature): > > spring: shardingsphere: rules: federation: type: SQL_FEDERATION props: sql-federation-enabled: true > > Why not recommended: > > * Federation queries are still experimental > * Stability cannot be guaranteed > * Performance is typically not as good as broadcast tables > * Complex configuration and debugging > > Solution 3: Application-Level Join > > * Query both tables separately at the application layer > * Perform join operations in memory > * Suitable for small data volumes > > ## Conclusion > According to ShardingSphere best practices: > > * Sharding tables cannot directly join with single tables - This is a design limitation > * Use broadcast tables instead of single tables - This is the standard approach > * Federation queries are experimental - Not recommended for production use > > I recommend using the broadcast table solution. While it has some maintenance overhead, it provides the best join performance and stability. 3Q for your reply, but it's to later, i give up shardingsphere . -- 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]
