Solution] How to Replace MongoDB with MySQL for Project Refactoring

preamble

In the author's Java back-end development project experience, MySQL and MongoDB have been used as a back-end database for business data persistence, there is no superiority or inferiority between the two, they can be used in the appropriate scenarios to play out their advantages.

Today to share is a project to refactor the process of how to change the database selection from the original MongoDB to MySQL thinking, involving the business of the current pain points, selection analysis, the core idea of the solution, the last will give a simple demo.

This post focuses on the conversion between the two in terms of table design thinking, while the solution for business data migration and synchronization will be given in the next post.

---

I. Where the pain is

The project is a [PC management background] + [mobile h5 page] for the main business framework of the system, the original expectation is: in the background configuration of the parameters required for the activity, h5 can be placed in the app client to open, but also as a form of url link directly in the browser to open. During the first phase of the project, the business side thought that such operational activities would bring a lot of traffic and users. But later the business focus was adjusted, the way of attracting traffic changed, which eventually led to a reconstruction of the project.

The main reasons for this are the following:

1. The overall amount of data was not as large as expected

   The number of participants in the event was estimated to be 30w+ in the early stage, and the actual total number of participants after 2 online activities was 5w+, with the number of registered users in the client side being 3w+, which accounted for about 65% of the total number of participants, which was far less than the expected scale;

2. Concurrency on the core interface was also not as high as expected

   The 5-8 core interfaces on the h5 side only reached a maximum QPS of around 200-300 during actual online activities, and the CPU and memory usage did not reach the set alarm line (60%);

3. MySQL is more cost-effective in terms of hardware resource costs.

   Comparing AliCloud's RDS for MySQL with the cloud database MongoDB, the former will be cheaper than the latter by 7w+RMB under the specifications of cluster deployment + 8 cores and 16GB + 100GB storage + 1 year of duration;

4. MySQL's Dynamic Data Source Switching Solution is More Mature

   At that time, all the back-end projects have been required to access the multi-tenant transformation, there are not many open source, mature dynamic data source switching solutions on the market, and there are very few that are fully dedicated to support MongoDB.

Combining the above reasons, completely abandon the project is not necessary, but also need to adapt to the current business changes and cost control, is expected to spend 30 people / day, i.e., 2 back-end development in 2-3 weeks to complete the refactoring of the system, the interface and the front-end page basically do not need to be adjusted.

---

II. Selection analysis

Here is the formal entry into the technical part, the first comparison of the two respective characteristics and applicable scenarios, which is crucial to grasp the direction of the entire project.

2.1 Comparison of characteristics

Table 2-1

comparison term	MySQL	MongoDB
data model	Relational databases that store data in the form of tables, where each row is a record.	Non-relational (NoSQL), document-based databases where data is stored in the unstructured form of a document.
Inquiry Method	Use standard SQL for querying, providing rich query conditions, join, sorting, paging and other functions.	Supports aggregation, statistics, and analysis of large amounts of data using query statements based on JSON structural features.
Transaction support	Support for ACID transactions ensures data consistency and reliability in transactions consisting of multiple operations. In particular, full transaction support is provided in the InnoDB engine	Since version 4.0, multi-document transaction support has been introduced to ensure that read and write operations within a certain range have ACID characteristics. However, for complex business scenarios that require strict transaction characteristics, MySQL is not as mature as MySQL.
data processing	When dealing with complex queries and highly concurrent writes, you need to rely on indexes to optimize performance or scale horizontally by means of partitioning, sharding, etc.	In the horizontal expansion and real-time data processing advantages, through the sharding (sharding) technology can easily cope with massive data storage and high concurrency read and write
space occupation	Due to the compactness of the data structure, the storage of data is often more space-efficient, especially for simple data structures and data sets with clear relationships.	Typically take up more space due to factors such as flexibility of document storage and inclusion of metadata
Project Integration	There are already mature third-party ORM frameworks supported, such as Mybatis, Mybatis Plus, and so on.	Currently, additions, deletions, and modifications integrated into Spring Boot projects are based on MongoRepository and MongoTemplate.

2.2 Scene Comparison

• MySQL
  1. Web applications: such as the common xx management backend, xx management system, e-commerce web site, including some mobile h5 pages, etc.;
  2. Enterprise applications: such as common Customer Relationship Management (CRM), Human Resources Management (HRM), and Supply Chain Management (SCM) systems, MySQL provides powerful transaction support;
  3. Embedded Development: Software, hardware, and devices that require lightweight databases, MySQL can be integrated into a variety of applications as an embedded database engine, improving application portability;
  4. Cloud Computing and Big Data: MySQL is widely used in cloud database services, supporting cloud-native applications and distributed data processing frameworks such as Hadoop and Spark.
• MongoDB
  1. Handles real-time data: Ideally suited to handle most scenarios common to mobile Internet applications, such as user activity, social interactions, online shopping, etc;
  2. Content Management System (CMS): Used to handle the storage and additions, deletions and deletions of rich media content such as articles, manuscripts, comments, images, videos, etc., with support for full-text search and real-time updates;
  3. Data Aggregation Warehouse: Store raw or semi-processed business data and use the aggregation framework for real-time data aggregation, statistical analysis and data visualization;
  4. Game data management: store player account information, game progress, achievements, virtual items, social relationships, etc., quickly calculate and update game leaderboard data, support real-time query and so on.

---

III. Core ideas

As we know, in MongoDB, the format of a data record (document) is in json format, which emphasizes the key-value relationship.

Table 2-2

For a MongoDB document, it can contain many properties of this collection, just like an article has many chapters inside.

Taking the following figure 2-1 as an example, activity is a complete collection that contains a number of attributes, basic attributes such as id, name, status, etc., and additional attributes such as button and share, which together make up the collection.

But such a structure is not possible in MySQL for the simple reason that MySQL emphasizes relationships, and 1:1 and 1:N are very common relationships.As you can see, the basic attributes are placed in the activity table as the main table, while the additional attributes are placed in the button table and the share table, and the primary key id of the main table is used as the foreign key of ac_id in the associated table.

Figure 2-1

The core of the change from MongoDB to MySQL is that the original set and nested relationships need to be broken down into a 1 : N paradigm, and the primary key - foreign key approach is used to do the correlation query, while avoiding the join join query.

---

IV. Demo Example

The actual table designs and entity mappings are first given below, both for MongoDB and MySQL respectively, followed by simple query code to show the difference.

4.1 Entity mapping

4.1.1 MongoDB Entities

@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 Entities

@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 is a query for activity details based on two criteria: primary key id and status.

4.2.1 MongoDB Queries

    /**
     * @apiNote Query activity by primary key id and activity status
     * @param id primary key id
     * @return entity
     */
    @Override
    public Avtivity getDetailById(String id) {
        return (id)
                .filter(val -> (()))
                .orElseThrow(() -> new RuntimeException("The activity does not exist!")) ;
    }

4.2.2 MySQL Queries

    @Resource
    private ActivityShareService activityShareService;
    @Resource
    private ActivityButtonService activityButtonService;
    @Override
    public ActivityVO detail(Integer id) {
        ExampleWrapper<Activity, Serializable> wrapper = ();
        (Activity::getid, id)
                .eq(Activity::getStatus(), ());
        Activity activity = ((()))
            .orElseThrow(() -> new RuntimeException("The activity does not exist！"));
        ActivityVO vo = new ActivityVO();
        ((()).orElse());
        //Check two related tables
        ((()));
        ((()));
        return vo;
    }

---

V. Summary of articles

The summary of using MySQL to replace MongoDB is as follows:

1. Do technology selection should be fully considered when comparing the characteristics of the two as well as the application scenarios, choose the most appropriate
2. If you don't have to, then continue with the original design; if you choose to refactor, then consider costs
3. The original set of relationships and nested relationships need to be split into a 1 : N paradigm, using primary key - foreign key to make the association

Lastly, please correct any shortcomings or mistakes. Or if you have something else to say, please feel free to share it in the comment section!