1. ShardingSphere Usage Specifications

1.1 Supported Items

Routing to Single Data Node

ShardingSphere currently achieves 100% compatibility for MySQL database.

Routing to Multiple Data Nodes

SQL Support Scope:

TypeDescription
DQL (Data Query Language)Supports SELECT queries
DML (Data Manipulation Language)Supports INSERT, UPDATE, DELETE
DDL (Data Definition Language)Supports CREATE, ALTER, DROP
DCL (Data Control Language)Supports GRANT, REVOKE
TCL (Transaction Control Language)Supports COMMIT, ROLLBACK

Advanced Query Features:

  • Pagination, deduplication, sorting, grouping and aggregation
  • Join queries (limited to within same database instance, cross-database joins not supported)

1.2 Unsupported Items

Routing to Multiple Data Nodes

  • All queries can only execute on a single data node
  • Cross-node data association operations need to be implemented at the application layer

SQL Syntax Limitations

  1. CASE WITH syntax: Nested WITH clauses in CASE expressions not supported
  2. HAVING clause: Filter conditions after grouping cannot be used directly
  3. UNION/UNION ALL operations: Cannot merge multiple query result sets

1.3 Pagination Subquery Limitations

Supported pagination subquery types:

  • Single-layer nested pagination subqueries

Unsupported subquery types:

  • Multi-layer nested subqueries
  • Subqueries containing business logic

1.4 Aggregation Queries

Due to merging limitations, aggregation functions in subqueries are currently not supported.

1.5 Schema

SQL containing Schema is not supported. ShardingSphere’s design philosophy is to let users use multiple data sources like using a single data source.

2. Sharding Key Operation Expression Handling Mechanism

2.1 Routing Principle

When the sharding key is in an operation expression or function in the SQL query, ShardingSphere will execute the query using full routing.

2.2 Typical Scenario Examples

Date Function Processing Scenario

SELECT * FROM b_order
WHERE to_date(create_time, 'yyyy-MM-dd') = '2025-06-25';

Math Operation Scenario

SELECT * FROM user_transaction
WHERE user_id % 10 = 5;

2.3 Optimization Suggestions

To avoid performance problems caused by full table scans, it is recommended:

  1. Move operation logic to the application layer in advance
  2. Use explicit sharding key values for queries

3. Pagination Query Optimization Details

3.1 Large Offset Performance Problem Analysis

When query offset is too large, the following performance bottlenecks occur:

  • Database needs to scan and skip a large number of records
  • Client needs to handle merging and sorting of large numbers of records
  • Network transmission volume grows exponentially

3.2 ShardingSphere’s Optimization Solution

  • Streaming batch processing: Use batch data fetching method
  • Merge sort optimization: Use heap sort algorithm for multi-way merging

3.3 Pagination Alternatives

ID Continuous Pagination Method

SELECT * FROM b_order
WHERE id > 1000000 AND id <= 1000010
ORDER BY id;

Cursor Pagination Method

SELECT * FROM b_order
WHERE id > 100000
ORDER BY id
LIMIT 10;

3.4 Best Practices

  1. Avoid using LIMIT N,M style pagination
  2. Pagination queries must use with ORDER BY
  3. Recommended data volume per page is controlled within 100 records
  4. For deep pagination, cursor pagination is mandatory