Smooth Migration to Sharding: Design, Implementation, and Componentization of Qunar's Database Sharding Solution

Author Introduction Chen Li, who joined Qunar in 2020, has over ten years of experience in internet application and game development and currently focuses on backend component development and performance optimization.

Background Sharding (database and table partitioning) is a common optimization for large‑scale internet applications. While middleware such as sharding‑jdbc and MyCat can handle the routing, the real difficulty lies in smoothly upgrading an existing single‑database system to a sharded one without downtime. Qunar's ticket ancillary business underwent such a migration, eventually achieving a configuration‑driven switch from single‑database to sharding.

Original Problems

How to roll back if data errors occur during migration, especially when new data may have been written to the new shard.

How to handle SQL statements that lack the sharding key (e.g., queries by third‑party coupon code) without massive rewrites.

How to verify that the sharded system is functionally equivalent to the original.

Solving these three issues enables a seamless transition.

First Smooth Migration Practice The approach consists of three key steps:

Dual‑write of data with a mapping‑key concept that routes reads/writes to either the old or new database based on SQL analysis.

A special transaction mechanism that guarantees consistency between the two systems.

Using an iff check to confirm data equivalence across the two databases.

These directly address the three original problems.

Prerequisite Knowledge – MyBatis

The article briefly explains MyBatis architecture: the API layer (SqlSession), data‑processing layer (parameter handling, SQL parsing, execution, result building), and foundational layer (connection, transaction, configuration, cache). It also outlines the MyBatis‑Spring integration, including MapperProxy, SqlSessionFactoryBean, and transaction synchronization.

2.2 Data Double‑Write During migration, both the original single‑database and the new sharded databases remain active. Writes are performed on both, while reads can target either side once equivalence is verified. This is achieved via a MyBatis plugin that intercepts SQL, swaps the SqlSession, and rewrites statements. The plugin also handles connection management, ensuring proper auto‑commit handling and reference counting within Spring transaction contexts.

2.3 Sharding‑Key Mapping To avoid full‑table scans for queries lacking the sharding key, a mapping‑key is introduced. The mapping‑key (e.g., couponId) maps to the actual sharding key (orderId) via a dedicated mapping table, allowing the routing middleware to locate the correct physical shard. Both direct and indirect mappings are supported, and a manual iteration API handles unavoidable full‑table scans.

2.4 Diff and Transaction Diff compares data between the old and new systems to ensure equivalence. Offline diff runs full‑volume comparisons for historical data, while real‑time diff validates the results of write operations. A lightweight “distributed” transaction design synchronizes operations across the two data sources, using Spring’s transaction synchronizer to manage a second connection’s lifecycle and commit/rollback behavior.

New Issues

Testing difficulty due to the heavy sharding setup.

High maintenance cost because of numerous annotations, configuration entries, and environment‑specific routing rules.

Limited reusability and extensibility of the original implementation.

To address these, the solution was componentized, providing a clear separation between the access layer, core routing layer, and storage layer.

4.1‑4.5 Componentized Design The component consists of:

Access layer: Spring‑compatible adapters, annotations, and starters.

Core layer: Routing logic, lifecycle management, and plugin points.

Storage layer: Final SQL execution via data sources and MyBatis.

The core uses a SqlRouterInterceptor (MyBatis plugin) and a SqlRouteProcessor to parse SQL into a SqlInfo structure, fill parameters, select an appropriate RouteStrategy , and invoke the routing middleware’s guide API.

Configuration Example

#库的前缀(这么做完全是为了照顾qdb的配置)
 db.prefix=qmall_supply_

#分库配置
 db.index.qmall.flight={dbIndex: 0}
 db.index.qmall.inter={dbIndex: 1}
 db.index.qmall.ticket={dbIndex: 2}
 db.index.qmall.hermes={dbIndex: 3}

#分表键配置
 sharding.user_info=[{shardingKey: 'last_name',intervalMonth:2,hashCount:0,startTime: '2020-11-01'},...]
 sharding.supply_order=[{shardingKey: 'supply_order_id',intervalMonth:1,hashCount:2,startTime: '2022-11-01', hashGroupReg: '20[0-9]{2}(0[1-9]|1[0-2])[0-9]{6}'}]

#映射键配置
 table.supply_order=[{mapKey: 'business_order_id', type: 'one2many', priority: 1, maintain: 'auto_manual'}]
 table.user_info=[{mapKey: 'id', type: 'one2one', priority: 1},...]

By adjusting the sharding.properties file, a single‑table can be transitioned to a sharded table without code changes, and environment‑specific data sources enable local single‑database testing while production uses sharding.

Conclusion The article presents two smooth sharding migrations: the first using double‑write and diff for a legacy system, and the second componentized design for a new DDD‑based microservice architecture. Although the implementation is specific to Qunar, the concepts—dual‑write, mapping‑key, diff verification, and modular routing—are broadly applicable to other large‑scale database migrations.