跪拜 Guibai
← All articles
TiDB · Database

Zhiwen Group Drops MySQL Sharding for TiDB: One Cluster for Real-Time OLTP and OLAP

By 平凯数据库 ·
Read original on juejin.cn ↗ Google Translate ↗ Alt translation

This migration shows a mature production path from MySQL sharding to a distributed HTAP database that handles both OLTP and OLAP without separate ETL pipelines. For Western teams running social or transaction-heavy systems, the validation and switchover patterns here are directly reusable, especially the dual-write and observation-window approaches that keep rollback possible.

Summary

Zhiwen Group's social apps span China and overseas, with real-time data demands that its old MySQL architecture could not keep up with. Each business line ran its own MySQL cluster; large tables were sharded, and offline analytics went through sync tools into Hive, producing T+1 results. As overseas growth accelerated, the pain points piled up: dozens of clusters made cross-database queries nearly impossible, sync latency grew with data volume, and scaling meant either hitting single-machine hardware limits or rewriting proxy routing rules for more shards. Large-table DDL operations also caused master-slave lag and heavy operational overhead.

The company evaluated four criteria and chose TiDB: HTAP capability to serve both online transactions and real-time analytics from one system, near-complete MySQL compatibility so only a connection string needed to change, flexible horizontal scaling by adding TiDB compute or TiKV storage nodes without failover, and distributed SQL execution that uses the whole cluster's compute for analytical queries. Two production use cases follow. The overseas real-time unified data platform deploys TiDB across multiple availability zones, using DM for sync and sync-diff-inspector for validation. The SPAM moderation system relies on TiDB's horizontal scaling to handle growing moderation orders, avoiding the long recovery times of MySQL primary-failure scenarios.

Migration safety was built on four layers: structure checks (including clustered vs. non-clustered table differences), data validation (row counts, samples, full compare), business validation (core SQL result comparison, daily reconciliation, key metric checks), and two switchover strategies. Non-core business uses an observation window; core business uses dual-write mode with rollback ability. Experience from the migrations points to three practical rules: evaluate migration cycles for very large tables, freeze DDL during migration whenever possible, and never drop latency monitoring.

Takeaways
— Zhiwen Group operated dozens of MySQL clusters across business lines, making cross-database queries inconsistent and real-time analytics latent.
— Data sync to Hive for T+1 analytics became a bottleneck as daily data volume grew, failing real-time business analysis needs.
— Scaling MySQL horizontally required proxy routing changes for each shard expansion; vertical scaling hit single-machine hardware limits.
— TiDB was selected for HTAP (mixed OLTP/OLAP), MySQL compatibility, horizontal scaling without master-slave switch, and distributed SQL query optimization.
— Two production scenarios: an overseas real-time unified data platform with multi-AZ deployment, and a SPAM moderation system that scales TiKV for order growth.
— Migration validation used four layers: structure checks, data checks (row count, sample, full), business checks (SQL result comparison, daily reconciliation, key metrics), and switchover modes.
— Core business used dual-write mode with read traffic switched to TiDB, allowing rollback to MySQL on issues; non-core used a 30-60 minute observation window.
— Three migration lessons: estimate large-table migration cycles, freeze DDL during migration, and keep latency monitoring active to detect sync breaks.
— The switchover window is for switching, not for migration work — all migration and validation should finish beforehand.
Conclusions

TiDB's MySQL compatibility is the decisive factor for adoption: the article states only a connection string change is needed, which removes the biggest cost of database migration.

The dual-write and observation-window switchover patterns are a pragmatic risk-control approach for any database migration, whether to TiDB or another distributed system.

The article treats HTAP as a direct replacement for the T+1 data warehouse, cutting the ETL pipeline out of real-time analytics — a cost saving that goes beyond latency.

The emphasis on business validation (core SQL result comparison, daily reconciliation) shows that tool-based data checks alone are insufficient for systems where financial loss is possible.

Concepts & terms
HTAP
Hybrid Transaction/Analytical Processing, a database architecture that runs online transaction (OLTP) and analytical (OLAP) workloads on the same system without data duplication or ETL.
分库分表 (Sharding and table splitting)
A MySQL scaling technique that distributes rows across multiple databases or tables based on a shard key, enabling horizontal growth but creating cross-shard query and operational complexity.
TiDB DM
TiDB Data Migration, a tool that syncs data from MySQL-compatible databases into TiDB, supporting full and incremental replication, and used here for the migration pipeline.
sync-diff-inspector
A TiDB tool that compares data between MySQL and TiDB to verify consistency after sync or migration, capable of checking full or sampled datasets.
双写模式 (Dual-write mode)
A migration switchover strategy where the application writes to both the old MySQL and new TiDB simultaneously, then switches read traffic after verifying consistency, allowing rollback.
Source: juejin.cn ↗ Google Translate ↗ Backup ↗