Migrating a Project from MongoDB to MySQL: Design Considerations and Demo

Source: https://www.cnblogs.com/CodeBlogMan/p/18257793

Preface

In the author’s Java backend development experience, both MySQL and MongoDB have been used for persisting business data, and neither is inherently superior; each can excel in appropriate scenarios.

This article shares the thinking behind switching the database choice from MongoDB to MySQL during a project refactor, covering current business pain points, selection analysis, core solution ideas, and finally a simple demo.

The focus of this article is on the transformation of table‑design thinking; the data migration and synchronization scheme will be presented in a follow‑up article.

1. Pain Points

The project is a system mainly consisting of a PC‑side management backend and a mobile H5 page. Initially, the plan was to configure activity parameters in the backend, allowing the H5 page to be opened either inside an app client or directly via a URL. The first phase expected significant traffic and user growth, but later business priorities shifted, changing the traffic acquisition method and prompting a project overhaul.

The main reasons are:

Total data volume was far smaller than expected. The estimated 300k+ participants for an activity turned out to be only about 50k, with 30k registered users, representing roughly 65% of total participants.

Core interface concurrency was lower than anticipated. The 5‑8 core H5 interfaces peaked at only 200‑300 QPS, and CPU/memory usage never reached the alert threshold (60%).

MySQL offers a better cost‑performance ratio on hardware resources. Comparing Alibaba Cloud RDS for MySQL with Cloud Database MongoDB under the same 8‑core/16 GB/100 GB/1‑year spec, MySQL is about ¥70,000 cheaper.

MySQL’s dynamic data‑source switching solutions are more mature. At the time, the backend project was required to adopt multi‑tenant refactoring, but open‑source, mature dynamic data‑source switching solutions for MongoDB were scarce.

Given these points, abandoning the project is unnecessary, but it must adapt to the new business direction and cost control. The estimated effort is 30 person‑days, i.e., two backend developers can complete the refactor in 2‑3 weeks with minimal changes to interfaces and front‑end pages.

2. Selection Analysis

The technical part begins here, first comparing the characteristics and applicable scenarios of the two databases, which is crucial for guiding the project direction.

2.1 Feature Comparison

Table 2‑1

Comparison Item

MySQL

MongoDB

Data Model

Relational database storing data in tables; each row is a record.

Document‑oriented NoSQL database storing data as unstructured documents.

Query Method

Standard SQL with rich conditions, joins, sorting, pagination, etc.

JSON‑style query language supporting large‑scale aggregation, statistics, analysis.

Transaction Support

Full ACID transaction support, especially in InnoDB engine.

Multi‑document transactions introduced in 4.0, but less mature for complex business scenarios.

Data Processing

Relies on indexes, partitioning, sharding for complex queries and high‑concurrency writes.

Excels at horizontal scaling and real‑time processing via sharding.

Space Consumption

Compact structure, generally more space‑efficient for simple, well‑structured data.

Flexible document storage with metadata leads to larger space usage.

Project Integration

Mature third‑party ORM frameworks such as MyBatis, MyBatis‑Plus, io.mybatis, tk.mybatis, etc.

Typically integrated in Spring Boot via MongoRepository and MongoTemplate.

2.2 Scenario Comparison

MySQL Web applications: typical management backends, e‑commerce sites, mobile H5 pages. Enterprise applications: CRM, HRM, SCM systems that require strong transaction support. Embedded development: lightweight databases for software/hardware devices. Cloud computing and big data: widely used in cloud database services, supporting cloud‑native apps and distributed processing frameworks like Hadoop and Spark.

MongoDB Real‑time data handling: mobile internet scenarios such as user activities, social interactions, online shopping. Content Management Systems (CMS): storing articles, comments, media with full‑text search and real‑time updates. Data aggregation warehouses: storing raw or semi‑processed business data for aggregation, statistics, visualization. Game data management: player accounts, progress, achievements, virtual items, social relations, leaderboard calculations.

3. Core Idea

In MongoDB, a record (document) is stored in JSON format, emphasizing key‑value relationships.

For a MongoDB document, many collection attributes can be included, similar to chapters in an article.

Consider the example where the "activity" collection contains basic fields (id, name, status) and extra fields (button, share). This structure cannot be directly represented in MySQL because MySQL emphasizes relational models with 1:1 and 1:N relationships.

The solution is to place basic attributes in the main "activity" table, and move extra attributes into separate "button" and "share" tables, using the primary key id as a foreign key (ac_id) in the related tables.

The core of replacing MongoDB with MySQL lies in flattening the original nested collection relationships into 1 : N normalized tables, linking them via primary‑key/foreign‑key relationships while avoiding costly JOIN queries.

4. Demo Example

Below we first present the actual table designs and entity mappings for both MongoDB and MySQL, then demonstrate simple query code to illustrate their differences.

4.1 Entity Mapping

4.1.1 MongoDB Entity

@EqualsAndHashCode(callSuper = true)
@Data
public class Activity extends BaseEntity {
    @Id
    private String id;
    private String name;
    private ActivityStatusEnum status;
    private ReviewStatusEnum review;
    private ActivityTypeEnum type;
    private ActivityButton button;
    private ActivityShare share;
}

4.1.2 MySQL Entity

@Data
public class Activity extends BaseEntity {
    @Id
    private Integer id;
    private String name;
    private Integer status;
    private Integer review;
    private Integer type;
}

@Data
public class ActivityButton extends BaseEntity {
    @Id
    private Integer id;
    private Integer acId;
    private String signUp;
    private Integer status;
    private String desc;
}

@Data
public class ActivityShare extends BaseEntity {
    @Id
    private String id;
    private Integer acId;
    private String title;
    private String iconUrl;
}

4.2 Query Code

The following queries retrieve activity details based on primary key id and status.

4.2.1 MongoDB Query

/**
 * @apiNote Query activity by primary key id and status
 * @param id primary key id
 * @return entity
 */
@Override
public Avtivity getDetailById(String id) {
    return this.repository.findById(id)
        .filter(val -> ActivityStatusEnum.ON.equals(val.getStatus()))
        .orElseThrow(() -> new RuntimeException("该活动不存在！"));
}

4.2.2 MySQL Query

@Resource
private ActivityShareService activityShareService;
@Resource
private ActivityButtonService activityButtonService;
@Override
public ActivityVO detail(Integer id) {
    ExampleWrapper
wrapper = this.wrapper();
    wrapper.eq(Activity::getId, id)
           .eq(Activity::getStatus(), DataStatusEnum.NORMAL.getCode());
    Activity activity = Optional.ofNullable(this.findOne(wrapper.example()))
        .orElseThrow(() -> new RuntimeException("该活动不存在！"));
    ActivityVO vo = new ActivityVO();
    vo.setName(Optional.ofNullable(activity.getName()).orElse(StringUtils.EMPTY));
    // query two related tables
    vo.setShare(this.activityShareService.getShare(activity.getId()));
    vo.setButton(this.activityButtonService.getButton(activity.getId()));
    return vo;
}

5. Article Summary

When making technology selections, fully compare characteristics and application scenarios to choose the most suitable solution.

If not necessary, continue using the existing design; once a refactor is decided, consider the associated costs.

The original nested collection relationships must be flattened into 1 : N normalized tables, using primary‑key/foreign‑key links.

Finally, any shortcomings or errors are welcome for correction, and readers are invited to share their thoughts in the comments.