Scaling a Ride‑Hailing Order System: From Single SQLServer to Sharded MySQL

1. Single‑Database Architecture

Initially the ride‑hailing service used a single SQLServer database with the service layer partially split by business domain. This simple architecture allowed rapid development but soon hit performance bottlenecks as order volume grew, leading to IO saturation, CPU spikes, and gateway timeouts.

2. SQL Optimization and Read‑Write Splitting

To relieve pressure on the primary database, the team first optimized slow SQL statements by adding appropriate indexes, reducing multi‑table joins, and shrinking large transactions. Then they introduced read‑write splitting: the master handled INSERT/UPDATE/DELETE while replicas served SELECT queries.

Database bottlenecks: heavy disk IO, CPU spikes from non‑indexed queries, and exploding connection counts.

Gateway timeouts caused by massive concurrent requests directly hitting the database.

3. Business‑Domain Sharding

Even after read‑write splitting, the master remained a hotspot. The team split the database by business domain, creating separate databases for passenger‑related data and driver‑related data. This reduced cross‑service impact and improved overall stability.

Because tables were now on different instances, cross‑database JOINs were no longer possible. The solution was to replace JOINs with RPC calls and to duplicate necessary fields via message queues.

4. Caching and Message Queue (MQ)

The order service introduced a cache cluster storing the last seven days of order details, allowing most read requests to be served from cache. Write operations first update the cache, then publish a message to MetaQ; a consumer validates the order and persists it to the database, smoothing traffic spikes.

Cache holds recent order details for fast reads.

Writes go to cache first, then MQ ensures eventual consistency and reduces DB load.

5. Migration from SQLServer to MySQL

Order volume exceeded a hundred million rows, prompting a migration to MySQL. Preparations included adapting SQLServer syntax to MySQL and redesigning the auto‑increment order_id to a distributed ID scheme.

Full‑volume historical data was copied to a new MySQL instance by DBAs.

Incremental data was migrated via MQ messages because SQLServer lacks binlog support.

The migration proceeded in three steps: pause old‑library consumption, migrate historical data, then enable both old and new services for dual‑write, finally switch traffic entirely to the MySQL version.

6. Self‑Developed Sharding Component (SDDL)

The team evaluated proxy‑based (e.g., Mycat, cobar) and client‑based (e.g., sharding‑jdbc, TDDL) solutions. They chose a client‑side approach and built a custom component named SDDL, which supports up to 8192 shards (1024 databases × 8 tables each).

The sharding algorithm hashes a user‑defined key, maps the result to a slot, and routes the query to the corresponding shard. The slot is encoded in the lower 13 bits of the order ID (modified Snowflake algorithm).

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

7. Sharding Strategies

Passenger dimension : user_id is the sharding key; all orders of a passenger reside in the same shard.

Driver dimension : To serve driver queries, passenger data is asynchronously replicated to driver‑oriented shards via canal and SDDL. The driver sharding key is driver_id.

Operations dimension : Order data is also synced to Elasticsearch for complex analytics.

Small‑table broadcasting : Configuration tables (e.g., city table) are broadcast to all shards, turning distributed JOINs into local queries.

8. Smooth Migration to Sharded MySQL

After SDDL proved stable, the service migrated from a single MySQL instance to the sharded setup. The process mirrored the earlier SQLServer‑to‑MySQL migration: prepare all eight shards, clean redundant data, enable forward sync (old → new), then enable reverse sync (new → old) and gradually shift traffic.

9. Data Exchange Platform

To address limitations of canal (lack of task UI, monitoring, audit), the architecture team built a data exchange platform: dataLink for incremental sync (based on dataX) and a customized dataX for full‑volume sync.

Conclusion

The ride‑hailing order system’s architecture evolved through multiple stages—single DB, SQL tuning, read‑write splitting, domain sharding, caching/MQ, custom sharding component, and finally a robust data exchange platform—demonstrating that a good architecture emerges from continuous evolution rather than one‑off design.