MyBatis Sharding Internals: Source Code Analysis of Table Routing & Join Failures

Introduction

The author investigated three questions after years of encountering sharding scenarios:

Why mapper.xml SQL doesn't need to concatenate shard table names.

How MyBatis identifies shard positions.

Why a three-table join failed to find a table, forcing a workaround with ${} that introduced SQL injection risk.

Environment: mybatis-plus-boot-starter 3.4.3, leveraging core MyBatis features.

Flow Overview

A flow diagram (referenced image) illustrates the query process: SQL parsing → logical SQL generation → route context creation → SQL rewrite → execution context creation.

Key Classes and Objects

MappedStatement

Full class: org.apache.ibatis.mapping.MappedStatement (final). Not related to java.sql.Statement. Holds metadata for one mapper method (one SQL), instantiated from XML. Retrieved by SQL ID from MybatisConfiguration/Configuration which maintains a mappedStatements map.

BoundSql

Full class: org.apache.ibatis.mapping.BoundSql. Stores the processed SQL and parameter information. Key fields: sql: processed SQL where ${} is directly replaced with actual values (injection risk), while #{} becomes ? placeholders. parameterMappings: records parameter mapping methods. parameterObject: actual parameter values from the mapper interface; e.g., 9 interface parameters become 18 entries with paramXX keys.

Generated by Executor from MappedStatement.

Connection

JDBC java.sql.Connection interface. In sharding scenarios, the concrete instance is ShardingSphereConnection, which holds a dataSourceMap containing data source configurations (connection timeout, JDBC URL, username, plaintext password) matching application.properties.

Statement / ShardingSpherePreparedStatement

JDBC Statement executes static SQL. In sharding, it creates intermediate contexts: ExecutionContext, TrafficContext, RouteContext. LogicSQL acts as a context holding structured parse of the original SQL (FROM, WHERE, GROUP BY). FROM supports multi-level join nesting. WHERE parameters participate in sharding computation.

Critical path: KernelProcessor.generateExecutionContext calls route() to obtain RouteContext (physical table names), then rewrite() to produce SQLRewriteResult, finally createExecutionContext().

public ExecutionContext generateExecutionContext(final LogicSQL logicSQL, final ShardingSphereMetaData metaData, final ConfigurationProperties props) { RouteContext routeContext = route(logicSQL, metaData, props); SQLRewriteResult rewriteResult = rewrite(logicSQL, metaData, props, routeContext); ExecutionContext result = createExecutionContext(logicSQL, metaData, routeContext, rewriteResult); logSQL(logicSQL, props, result); return result; }

The route() method involves: WhereClauseShardingConditionEngine.createShardingConditions extracts sharding conditions from WHERE segments of the parsed SQL. ShardingRouteEngineFactory.getDQLRoutingEngine filters out tables whose names don't match logical table names (e.g., a table written as table_c_${} resolved to table_c_001 won't match any sharding rule).

If binding table rules indicate consistent sharding, processing is deduplicated. RouteSQLRewriteEngine rewrites logical table names to physical names via toSQL() which sorts SQLToken s and rebuilds the SQL string.

public final String toSQL() { if (context.getSqlTokens().isEmpty()) { return context.getSql(); } Collections.sort(context.getSqlTokens()); StringBuilder result = new StringBuilder(); result.append(context.getSql(), 0, context.getSqlTokens().get(0).getStartIndex()); for (SQLToken each : context.getSqlTokens()) { result.append(each instanceof ComposableSQLToken ? getComposableSQLTokenText((ComposableSQLToken) each) : getSQLTokenText(each)); result.append(getConjunctionText(each)); } return result.toString(); }

ShardingRule

Stores sharding and single-table rules. Example configuration:

Three data sources: ds-master (no sharding), ds0, ds1 (with sharding).

Logical table c_voucher maps to physical c_voucher_${companyId}_${subYear} using two sharding columns.

Various sharding algorithms available (hash, inline, complex inline); the project uses COMPLEX_INLINE for tables and INLINE for databases.

Mapping to application.properties (partial)

Data source names → dataSourceNames → Prefix for spring.shardingsphere.datasource.ds-0.xxx=yyy Sharding algorithms → shardingAlgorithms (Map) → Table:

spring.shardingsphere.rules.sharding.sharding-algorithms.ts-c-voucher.type=COMPLEX_INLINE

,

spring.shardingsphere.rules.sharding.sharding-algorithms.ts-c-voucher.props.algorithm-expression=c_voucher_$->{companyId}_$->{subYear}

; Database:

spring.shardingsphere.rules.sharding.sharding-algorithms.t-database-inline.type=INLINE

,

spring.shardingsphere.rules.sharding.sharding-algorithms.t-database-inline.props.algorithm-expression=ds-$->

. Type defines algorithm class; table prefix ts-, database prefix t-.

Key generators → keyGenerators → Not used in example (Snowflake, UUID, etc.)

Table rules → tableRules (Map) → See next section

Binding table rules → bindingTableRules → spring.shardingsphere.rules.sharding.binding-tables[0]=a,b,c. Binds tables with same sharding rules to avoid Cartesian product.

TableRule Mapping

Logical table name → logicTable Actual data nodes → actualDataNodes →

spring.shardingsphere.rules.sharding.tables.c_voucher.actual-data-nodes=ds-$->{0..1}.c_voucher_$->{1..2}_$->

. Expands to concrete nodes like ds-0.c_voucher_1_1, ds-1.c_voucher_2_2, etc.

Actual tables → actualTables → Same as above; not real table names, similar to actualDataNodes.

Data node index map → dataNodeIndexMap → Sequential indices 0~7 for the example.

Database sharding strategy → databaseShardingStrategyConfig →

spring.shardingsphere.rules.sharding.tables.c_voucher.database-strategy.standard.sharding-column=schemaId

,

spring.shardingsphere.rules.sharding.tables.c_voucher.database-strategy.standard.sharding-algorithm-name=t-database-inline

Table sharding strategy → tableShardingStrategyConfig →

spring.shardingsphere.rules.sharding.tables.c_voucher.table-strategy.complex.sharding-columns=companyId,subYear

,

spring.shardingsphere.rules.sharding.tables.c_voucher.table-strategy.complex.sharding-algorithm-name=ts-c-voucher-result

. Sharding columns and algorithm name referencing shardingAlgorithms.

Actual data source names → actualDatasourceNames → ds-0, ds-1 Data source to tables map → datasourceToTablesMap → Shard table names same as actualDataNodes.

Key Steps for Sharding Routing

Configure data sources with database/table sharding rules (algorithm + source columns) so the assembled Connection carries routing info.

At SQL execution, Statement finds sharding rules by logical table name and computes actual table names:

Generate LogicSQL (structured SQL).

Parse FROM clause, extract sharding parameters, assemble logical-to-physical mapping RouteUnit (one per bindingTableRules group).

KernelProcessor rewrites logical table names in LogicSQL per RouteUnit.

Answers to the Three Questions

The first two are answered by the above analysis: logical-to-physical conversion and sharding rule/parameter interaction.

Third Question: Three-Table Join Failure

Original problematic SQL (simplified):

SELECT a.biz_date, a.type_id, a.voucher_id, a.create_time, a.update_time FROM table_a a INNER JOIN table_c c ON a.biz_date = c.biz_date AND a.type_id = c.type_id AND a.voucher_id = c.voucher_id LEFT JOIN table_b b ON a.biz_date = b.biz_date AND a.type_id = b.type_id AND a.voucher_id = b.voucher_id AND a.schema_id = b.schema_id AND a.sub_year = b.sub_year AND a.company_id = b.company_id WHERE a.company_id = #{company_id} AND a.schema_id = #{schema_id} AND a.sub_year = #{sub_year}

Sharding details: table_a, table_b: sharding columns company_id, sub_year (identical rules). table_c: sharding column accPackageId (different rule).

Root cause: table_c 's sharding column accPackageId did not appear in the WHERE clause (though present in mapper.java method parameters). The routing engine couldn't compute the physical table for table_c.

Workaround used: rewrite table_c as table_c_${accPackageId} in XML, which bypasses sharding rules but introduces injection risk.

Correct fix: add the missing sharding condition to WHERE: and c.acc_package_id = #{accPackageId} Then revert table_c_${accPackageId} back to table_c.

Extended Question: Shards Across Different Databases

Tested via direct SQL in Navicat across dev_account_0.table_a_1722_2024 and dev_account_1.table_c_757. Query succeeded and returned data.

Caveats:

Requires permissions on all involved shards.

Performance penalty observed: cross-database join took ~50s vs 0.2s for same-database join when table_c had no matching data.

Performance Regression After Fix

Adding the sharding column to WHERE may cause index mismatch. Final SQL added two conditions for table_c:

and c.acc_package_id = #{accPackageId} and c.company_id = #{companyId}

Nested Query Routing Issue

Example:

SELECT a.id FROM c_a a WHERE a.accPackageId = 1447 AND a.schemaId = 1 AND a.companyId = 2104 AND a.subYear = 2016 AND NOT EXISTS ( SELECT 1 FROM c_b b WHERE b.companyId = 2104 AND b.subYear = 2016 AND b.schemaId = 1 AND b.vchDate = a.vchDate AND b.typeId = a.typeId AND b.voucherId = a.voucherId )

Sharding: both tables shard by schemaId at database level; c_a shards by accPackageId, c_b by companyId+subYear at table level.

Symptom: physical table for c_a not found. Debug showed RouteUnit construction produced multiple conditions (two from WHERE?), whereas join queries produced only one condition, leading to incomplete table name replacement.

Temporary fix: pre-concatenate c_a as a_${xx} in SQL.

Source Code Reading Insights

Best approach: debug while drawing flow charts. Static reading is hard due to:

Heavy reflection and proxies (framework + logging) causing debug stack confusion.

Many objects are instantiated as subclasses only visible at runtime; inheritance hierarchies are complex.

Objects hold both intermediate and processed data simultaneously. Example: executionContext.logicSQL SQL text never changes; rewritten physical tables appear in executionContext.executionUnits, which is easy to miss.

Author previously opposed Lombok but now accepts it after seeing ShardingSphere's TableRule using it.

Debug Suggestions (Ongoing)

Check AbstractSQLBuilder.toSQL() to see if logical table names are incorrectly replaced.

Inspect RouteSQLBuilder.getSQLTokenText() to verify routeUnit matches expectations.