Re: [I] Single table union all sharding table, sharding table routing problem [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #29539:
URL: https://github.com/apache/shardingsphere/issues/29539#issuecomment-3495544132

   Root Cause Analysis
   
     Conclusion: This is a design limitation of ShardingSphere, not a bug
   
     Through analysis of ShardingSphere source code, the issue occurs in ShardingSelectRouteContextChecker.java:38-40:
   
     if (selectStatement.getCombine().isPresent() && !isRouteToSingleDataSource(routeContext)) {
         throw new SelectMultipleDataSourcesWithCombineException(selectStatement.getCombine().get().getCombineType().name());
     }
   
     Technical Reasons:
     1. Routing Conflict: Single table t_order routes to a single data source, while sharded table t_order_his routes to specific shard nodes based on time conditions
     2. Multi-Data Source Limitation: UNION ALL queries need to route to multiple data sources, which violates ShardingSphere's architectural constraints
     3. Consistency Guarantee: This limitation ensures the correctness and consistency of complex query results
   
     Solutions Following ShardingSphere Best Practices
   
     Solution 1: Application-Level Merging (Most Recommended)
   
     // Execute queries separately
     List<Order> currentOrders = jdbcTemplate.query(
         "SELECT * FROM t_order WHERE create_time BETWEEN ? AND ?",
         params, orderMapper);
   
     List<Order> historyOrders = jdbcTemplate.query(
         "SELECT * FROM t_order_his WHERE create_time BETWEEN ? AND ?",
         params, orderMapper);
   
     // Merge at application level
     List<Order> allOrders = new ArrayList<>();
     allOrders.addAll(currentOrders);
     allOrders.addAll(historyOrders);
   
     Advantages:
     - Fully aligns with ShardingSphere design philosophy
     - Controllable performance, can be optimized separately
     - Easy to maintain and debug
   
     Solution 2: Convert Single Table to Broadcast Table
   
     Add broadcast table rules in sharding configuration:
   
     sharding:
       broadcast-tables:
         - t_order
   
     Applicable Scenarios:
     - t_order has relatively small data volume
     - Frequent JOIN queries with sharded tables are needed
     - Data update frequency is not high
   
     Advantages:
     - Supports UNION ALL queries
     - Good query performance
     - Simple configuration
   
     Not Recommended Solutions
   
     SQL Federation Engine
   
     Although ShardingSphere provides SQL Federation functionality to handle cross-data-source complex queries, it:
     - Is still in experimental stage, with insufficient stability
     - May have performance issues and unknown risks
     - Not recommended for production environments
   
     Information Needed from User
   
     To provide the most suitable solution recommendations, users should understand:
   
     1. Data Volume:
       - Approximately how much data does the t_order table contain?
       - What is the data distribution pattern of the t_order_his table?
     2. Query Characteristics:
       - How frequent are these UNION ALL queries?
       - What are the performance requirements for queries?
     3. Business Requirements:
       - Can t_order be converted to a broadcast table?
       - Is application-level merging acceptable?
   
     Summary
   
     This issue reflects ShardingSphere's trade-offs in distributed database architecture design: to ensure data consistency and query correctness, it restricts the use of certain complex SQL statements. It is recommended that
      users choose application-level merging or broadcast table solutions based on specific business scenarios, as this both follows ShardingSphere's design philosophy and ensures system stability and maintainability.
   
   


-- 
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