Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve sub-table and sub-database operation in the MySQL database, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Table and database

Vertical split

Vertical splitting is to divide tables into different database tables by module (of course, the principle is not to break the third paradigm), which is 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, the modules and tables are all together, and when the website continues to enrich and grow, it will become multiple subsystems to support. At this time, there is a need to divide the table by module and function. In fact, compared with vertical segmentation, a further step is service-oriented reform. to put it simply, it is necessary to split the original strongly coupled system into several weakly coupled services to meet business needs through calls between services. therefore, after the table is split out, it should be exposed in the form of services, rather than directly calling tables of different modules. Taobao is in the process of continuous evolution of architecture, the most important link is service transformation. Extracting the core concepts such as users, transactions, stores and babies into independent services is also very conducive to local optimization and governance to ensure the stability of the core modules.

Vertical split is used for distributed scenarios.

Horizontal split

The above mentioned vertical segmentation only divides the table into different databases according to modules, but does not solve the problem of large amount of data in a single table, while horizontal segmentation is to divide a table into different tables or databases according to certain rules. For example, like a billing system, it is more appropriate to divide the table by time, because the system deals with data for a certain period of time. However, for SaaS applications, it is more appropriate to divide the data by user dimension, because the isolation between users generally does not exist to deal with multiple user data, and it is simply split horizontally according to the user_id range.

Popular understanding: split rows horizontally, split row data into different tables, split vertically, split table data into different tables

Horizontal split case

Idea: in large-scale e-commerce systems, the number of members is increasing every day. How to optimize the query after reaching a certain bottleneck.

You may think of the index, if the number of users reaches hundreds of millions of levels, how to optimize it?

Split the database table using split horizon.

How to split a database horizontally

Use horizontal split table, according to business requirements, some according to registration time, touch, account rules, year and so on.

Use 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 self-increasing id.

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; creates a demo project

POM file

Org.springframework.boot spring-boot-starter-parent 1.3.3.RELEASE org.springframework.boot spring-boot-starter-jdbc org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-test test Mysql mysql-connector-java org.springframework.boot spring-boot-starter-web

Service code

Servicepublic class UserService {@ Autowired private JdbcTemplate jdbcTemplate; public String regit (String name, String pwd) {/ / 1. First get the custom growth ID 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

@ RestControllerpublic 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 }} on how to achieve sub-table sub-database operation in the MySQL database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.