How We Scaled a Ride‑Hailing Order System: From Single DB to Sharded MySQL

1 Single Database Architecture

In the early product stage the technical team focused on quickly delivering features, using a single SQLServer database with service‑layer domain separation. As order volume grew, peak‑time requests often timed out due to database I/O bottlenecks, CPU spikes from complex queries, and gateway timeouts caused by exhausted DB connections.

2 SQL Optimization and Read/Write Separation

To relieve pressure on the primary database the team first performed SQL optimization: adding appropriate indexes, reducing multi‑table JOINs by assembling data in the application layer, and shrinking large transactions to free connections quickly.

Another strategy was read/write separation, where the master handles INSERT/UPDATE/DELETE while replicas serve SELECT queries. The framework provided by the order team supported this pattern, as illustrated in the diagram.

3 Business‑Domain Sharding

Even after read/write separation, the master DB remained a bottleneck, prompting a move to business‑domain sharding: splitting the database by functional domains so each system accesses only its own database. This required replacing cross‑DB JOINs with RPC calls and adding redundant fields synchronized via message queues.

4 Cache and MQ

The order service, being the highest‑traffic component, introduced a cache and a message queue (MetaQ). When a user clicks “Call Now”, the order is first persisted to the DB, then cached. Subsequent modifications update the cache first and send an MQ message; a consumer validates and writes the order to the DB, providing peak‑shaving and reducing DB load.

Two core logics:

Cache stores recent seven‑day order details, serving most read requests directly.

Writes modify the cache and are asynchronously persisted via MQ, lowering DB pressure.

5 From SQLServer to MySQL

Order volume continued to explode, reaching hundreds of thousands of daily orders and billions of rows. The team decided to migrate the entire order database from SQLServer to MySQL, first preparing schema differences and redesigning the auto‑increment primary key for distributed scenarios.

Migration consisted of two phases: full historical data transfer and incremental data sync. Because SQLServer lacks binlog, the team rebuilt order‑service code to emit MQ messages on every write, enabling bidirectional synchronization.

Migration steps:

Old service sends change messages to MetaQ while old‑library consumption is paused.

Full historical data is migrated; once complete, old‑library consumption resumes.

New service (MySQL version) is deployed alongside the old one, gradually shifting traffic until the old service is retired.

6 Self‑Developed Sharding Component

Industry sharding solutions fall into proxy and client modes. The team chose a client‑side approach and built a custom component named SDDL , which requires only a single datasource ID in the configuration along with sharding key and routing rules.

7 Sharding Strategy

7.1 Passenger Dimension

The primary query dimension is the passenger, using user_id as the sharding key so all orders of a user reside in the same shard. The algorithm mirrors Alibaba’s cobar: hash the key, mod 1024, and map to a slot that determines the target shard.

Integer getWorkerId(Long orderId) {
    Long workerId = (orderId >> 12) & 0x03ff;
    return workerId.intValue();
}

SDDL supports up to 8192 shards (1024 databases × 8 tables), requiring a 13‑bit worker ID and a 38‑bit timestamp in the Snowflake ID.

7.2 Driver Dimension

Drivers query orders by driver_id . To avoid broadcasting queries to all passenger shards, the team asynchronously replicates passenger‑shard data to driver‑shard databases using Canal and SDDL.

7.3 Operation Dimension

Operational staff need complex queries; order data from passenger shards is synchronized to Elasticsearch via Canal for flexible search.

7.4 Small‑Table Broadcast

Configuration tables (e.g., city table) are broadcast to all shards, turning distributed JOINs into local queries and dramatically improving performance.

8 Smooth Migration

DBAs provision four passenger and four driver shards with full data snapshots.

Redundant data is cleaned according to sharding rules.

Forward sync streams existing orders from passenger shards to driver shards via Canal.

Reverse sync updates the new data source configuration; new orders go to passenger shards and are replicated to driver shards and a central order DB.

After verification, traffic is fully switched to the sharded architecture.

9 Data Exchange Platform

Full‑history migration still requires DBA involvement, while incremental sync via Canal lacks robust task management. To address this, the architecture team built dataLink for real‑time incremental sync and customized Alibaba’s dataX for full‑size data transfers.

10 Final Thoughts

The order service’s architectural evolution—from a single database to a fully sharded, MySQL‑based system—was incremental, data‑driven, and supported by custom tooling, ultimately strengthening the technical foundation for both the ride‑hailing platform and downstream projects.