MySQL database optimization: detailed explanation of table and database sharding operations

This article uses examples to illustrate the table and database sharding operations for MySQL database optimization. Share with you for your reference, the details are as follows:

Sub-table and sub-database

Vertical Split

Vertical splitting is to divide the table into different database tables according to modules (of course, the principle is not to destroy the third normal form). This kind of splitting is very common in the evolution of large websites. When a website is still very small, there are only a small number of people to develop and maintain it, and all modules and tables are together. As the website continues to enrich and grow, it will become supported by multiple subsystems. At this time, there is a need to divide the tables according to modules and functions. In fact, compared with vertical segmentation, service-oriented transformation is a step further. To put it simply, it is to split the original strongly coupled system into multiple weakly coupled services, and meet business needs through calls between services. Therefore, after the table is split, it must be exposed in the form of services instead of directly calling tables of different modules. The most important part of Taobao's continuous evolution of architecture is service-oriented transformation. Extracting core concepts such as users, transactions, stores, and products into independent services is also very conducive to local optimization and governance, and ensuring the stability of core modules. Vertical splitting is used in distributed scenarios.

Horizontal Split

The vertical split mentioned above only divides the table into different databases by module, but does not solve the problem of large data volume in a single table. Horizontal splitting is to divide the data of a table into different tables or databases according to certain rules. For example, in a billing system, it is more appropriate to divide the table by time, because the system processes data within a certain period of time. For SaaS applications, it is more appropriate to divide data by user dimension, because users are isolated from each other, and generally there is no need to process multiple user data. Simply split horizontally by user_id range. In layman's terms: horizontally split rows, split row data into different tables, vertically split columns, split table data into different tables

Split Horizon Example

Idea: In large e-commerce systems, the number of members continues to increase every day. How to optimize queries after reaching a certain bottleneck.
You may think of indexes. What if the number of users reaches hundreds of millions? How to optimize it?
Split the database table using horizontal partitioning.

How to use a horizontally split database

Use horizontal partitioning to split the table, depending on business needs, some are based on registration time, lottery, account rules, year, etc.

Use the touch method to divide the table

First, I create three tables user0 / user1 / user2, and then I create the uuid table, which is used to provide auto-incrementing ids.

create table user0(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd varchar(32) not null default '')
engine=myisam charset utf8;
create table user1(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd varchar(32) not null default '')
engine=myisam charset utf8;
create table user2(
id int unsigned primary key ,
name varchar(32) not null default '',
pwd varchar(32) not null default '')
engine=myisam charset utf8;
create table uuid(
id int unsigned primary key auto_increment)engine=myisam charset utf8;

Create a demo project

POM File

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.3.3.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    </dependencies>

Service Code

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public String regit(String name, String pwd) {
        // 1. Get the custom growth ID first
        String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
        jdbcTemplate.update(idInsertSQL);
        Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        // 2. Determine the storage table name String tableName = "user" + insertId % 3;
        // 3. Registration data String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
                + "');";
        System.out.println("insertUserSql:" + insertUserSql);
        jdbcTemplate.update(insertUserSql);
        return "success";
    }
    public String get(Long id) {
        String tableName = "user" + id % 3;
        String sql = "select name from " + tableName + " where id="+id;
        System.out.println("SQL:" + sql);
        String name = jdbcTemplate.queryForObject(sql, String.class);
        return name;
    }
}

Controller

@RestController
public class UserController {
    @Autowired
    private UserService userService;
    @RequestMapping("/regit")
    public String regit(String name, String pwd) {
        return userService.regit(name, pwd);
    }
    @RequestMapping("/get")
    public String get(Long id) {
        String name = userService.get(id);
        return name;
    }
}

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.