Database Storage Optimization and Migration Strategy for ZhaiZhai Financial System

Background : The ZhaiZhai financial system aggregates data from upstream business systems (orders, OMS, payments, after‑sales, etc.) to generate financial reports. Since its launch in 2021, rapid data growth has pushed the original MySQL storage to its limits, causing slow queries and resource contention.

Data Volume Statistics : Several core tables exceed tens of millions of rows, with sizes ranging from 4 GB to 30 GB and index lengths up to 34 GB. Monthly data increments have reached ten million rows, and daily slow‑query counts are in the thousands, impacting user experience and risking machine failures.

Design Goals : (1) Eliminate data‑volume constraints for the next five years, ensuring scalability for all business lines; (2) Improve read/write performance to accelerate report queries, reduce scheduled‑task duration, and prevent time‑outs caused by slow queries.

Solution Options :

Option 1 – Sharding & Partitioning : Distributes data across multiple databases/tables, reducing single‑node load and improving latency. Drawbacks include complex shard planning, limited extensibility, and cross‑database transaction challenges.

Option 2 – Hot‑Cold Storage : Moves infrequently accessed data to archival storage, lowering online storage demand and improving performance. Not suitable for ZhaiZhai due to complex business data and unclear boundaries.

Option 3 – TiDB : MySQL‑compatible, horizontally scalable, and supports elastic expansion. Suitable for high‑concurrency, large‑volume OLTP workloads. Minor incompatibilities with some MySQL features and higher operational complexity.

Option 4 – OceanBase : Offers high performance, MySQL/Oracle compatibility, and strong availability, but requires specific hardware and incurs steep learning/maintenance costs.

After evaluating the pros and cons, TiDB was chosen as the most appropriate solution for the financial system because it addresses data‑volume challenges with minimal code changes and provides strong scalability.

Slow‑Query Optimization : Most slow queries stem from join operations. The team decided to offload join processing to Elasticsearch (ES), designing an index model to improve query speed and ensure data consistency between the DB and ES.

Migration Steps :

Switch the underlying storage from MySQL to TiDB, ensuring SQL compatibility, data integrity, and rollback capability.

After TiDB stabilizes, integrate ES for query acceleration.

Data Synchronization Methods evaluated include dual‑write (synchronous and asynchronous), periodic sync, and data subscription. The team selected a data‑subscription approach using Kafka for near‑real‑time incremental sync, and employed an internal ECP tool for historical data migration.

Results : The storage migration completed successfully, eliminating data‑volume concerns. ES integration resolved report‑page time‑outs, enhancing user experience. Ongoing work includes continuous slow‑SQL remediation and scheduled‑task optimization.

Author : Dai Meiqi, R&D Engineer, ZhaiZhai Transaction Platform.