SoFunction
Updated on 2025-05-16

Project Practice for Implementing Library and Tables in JAVA+MySQL

introduction

As the business scale continues to expand, MySQL databases with single libraries and single tables often face performance bottlenecks. To solve this problem,Library and tableIt has become a common technical means. By distributing data into multiple databases or tables, the system's concurrent processing capacity and storage capacity can be effectively improved. This article will combine Java code to introduce in detail the design ideas and implementation methods of MySQL library and table division.

1. What is a database and table?

  • Horizontal Table: Disperse records in the table into multiple physical tables according to some rules (such as user ID modulo).
  • Vertical table: Split a large table into multiple small tables according to the business module or field type, and each table stores different business data.
  • Horizontal library: Spread different tables onto different database instances to relieve the pressure on a single database.

Horizontal Table

Horizontal Sharding refers to splitting a large data table into multiple smaller tables according to some rules, each table containing part of the rows of data from the original table. This splitting method can effectively distribute hotspot data and avoid performance bottlenecks caused by excessive data volume in a single table. Common subtable strategies include hash values ​​based on user ID, timestamps, etc.

Vertical table

Vertical Sharding refers to splitting the columns in a table into multiple tables according to different business logic. Typically, fields that are often used together are placed in the same table, and less used fields are split into another table. This method is mainly used to reduce the width of the table, thereby reducing the amount of data that needs to be scanned every time you query.

Horizontal library

Horizontal Partitioning is based on horizontal partitioning, distributing different partitioning tables to different database instances. This method can not only improve the read and write performance of data, but also improve the fault tolerance and availability of the system.

2. Design ideas for dividing databases and tables

1. Data sharding strategy

The core of the database and table is how to store data in a distributed manner. Common data sharding strategies include:

  • Hash sharding: Sharding according to the hash value of a certain field. For example, a user ID is hashed and then the library or table of the data is determined based on the hash value.

  • Range sharding: Sharding according to the range of a certain field. For example, disperse data into different libraries or tables according to the scope of the user ID.

  • Time fragmentation: Slicing according to time. For example, spread data into different libraries or tables by month or year.

2. Implementation of library and tables

  • Client sharding: Implement sharding logic at the application layer, and the application determines the library or table of data storage based on sharding strategy.

  • Middleware sharding: Use middleware (such as MyCat, ShardingSphere) to implement sharding logic, and the application does not need to care about sharding details.

Middleware solution

Middleware is a layer of software that connects applications and underlying databases. It is responsible for handling the logic of subtables and databases, simplifying developers' programming work. There are many mature middleware solutions on the market:

1. MyCAT

MyCAT is an open source database middleware that supports multiple sharding algorithms and can implement transparent database partitioning and table partitioning. It defines sharding rules through configuration files, allowing you to easily access applications into a sharded database cluster. Advantages of MyCAT include:

Simple configuration: You can define sharding rules through XML files.
High Availability: Supports master-slave replication and read-write separation.
Easy to integrate: seamlessly connects most Java applications.

2. ShardingSphere

ShardingSphere is a distributed database middleware project incubated by the Apache Foundation, providing a complete set of solutions including library division, reading and writing separation, elastic scaling and other functions. The characteristics of ShardingSphere are as follows:

Flexibility: Supports multiple sharding strategies, including range sharding, list sharding, etc.
Dynamic adjustment: You can dynamically adjust sharding rules at runtime.
Eco-compatibility: supports multiple database engines, easy to integrate into microservice frameworks such as Spring Cloud

3. Challenges of dividing databases and tables

  • Cross-base query: After dividing the database and tables, cross-store query becomes complicated, and it may require multiple queries and data aggregation at the application layer.

  • Transaction Management: After dividing the library and tables, cross-store transaction management becomes complicated and may require the use of distributed transaction solutions (such as XA transactions, TCC transactions).

  • Data migration: After dividing the database and tables, data migration and expansion become complicated, and a reasonable data migration plan is required.

3. Practice of implementing library and tables in JAVA + MySQL

1. Environmental preparation

  • database：MySQL

  • programming language：JAVA

  • Dependency library: ShardingSphere (optional)

2. Library and table configuration

Suppose we have a user tableuser, we need to store it scatter in 4 libraries, each with 4 tables. We can use ShardingSphere to implement library and table division.

2.1 Introducing ShardingSphere dependencies

<dependency>
    <groupId></groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>5.1.0</version>
</dependency>

2.2 Configure library and table rules

existConfigure library and table rules:

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1, ds2, ds3
      ds0:
        url: jdbc:mysql://localhost:3306/db0
        username: root
        password: root
      ds1:
        url: jdbc:mysql://localhost:3306/db1
        username: root
        password: root
      ds2:
        url: jdbc:mysql://localhost:3306/db2
        username: root
        password: root
      ds3:
        url: jdbc:mysql://localhost:3306/db3
        username: root
        password: root
    sharding:
      tables:
        user:
          actual-data-nodes: ds$->{0..3}.user_$->{0..3}
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: user_$->{user_id % 4}
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds$->{user_id % 4}

2.3 Writing JAVA code

import ;
import ;
import ;

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void addUser(Long userId, String username) {
        String sql = "INSERT INTO user (user_id, username) VALUES (?, ?)";
        (sql, userId, username);
    }

    public String getUser(Long userId) {
        String sql = "SELECT username FROM user WHERE user_id = ?";
        return (sql, new Object[]{userId}, );
    }
}

3. Test library and table

import ;
import ;
import ;
import ;

@SpringBootApplication
public class ShardingApplication implements CommandLineRunner {

    @Autowired
    private UserService userService;

    public static void main(String[] args) {
        (, args);
    }

    @Override
    public void run(String... args) throws Exception {
        (1L, "user1");
        (2L, "user2");
        (3L, "user3");
        (4L, "user4");

        ("User 1: " + (1L));
        ("User 2: " + (2L));
        ("User 3: " + (3L));
        ("User 4: " + (4L));
    }
}

4. Operation results

After running the program, the data will be scattered in 4 tables in 4 libraries. By querying the log, you can see that the data is correctly stored and queried.

Summarize

Library and table division is an effective means to solve the performance problems of massive data storage and query. Through reasonable design and implementation, the performance and scalability of the system can be significantly improved. This article introduces the design ideas and practices of using JAVA and MySQL to implement library and tables, and hopes that they will be helpful to readers.

In practical applications, the database and table division still face many challenges, such as cross-store query, transaction management, data migration, etc. Therefore, when designing a library and table plan, it is necessary to comprehensively consider business needs, technology selection and system architecture to ensure the stability and scalability of the system.

This is the end of this article about JAVA+MySQL's project practice of implementing library and tables. For more related contents of JAVA MySQL database and tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!