The plot to review

Previously, we learned a total of read/write split, vertical split, vertical split + read/write split. The corresponding articles are as follows:

Sharding-jdbc: How to optimize large query volume?

Sharding-jdbc: How to do vertical split?

With the above optimization, most of the requirements have been satisfied. The only time we need to optimize again is when the number of single tables increases dramatically beyond 10 million, and the table is split horizontally.

What is the horizontal split of a table?

Is to split a table into N tables, like a big stone, can not move, and then cut into 10 pieces, so you can move. The principle is the same.

Depending on your sharding algorithm, the proper algorithm will help evenly distribute data and improve performance.

Today we mainly talk about the separation of the table in the single library, that is, not divided into the library, only divided into tables.

The operation of both library and table will be talked about later, first to a picture to feel the undivided table:

And then here’s another picture to feel the list:

Table user=user0+user1+user2+user3; table user=user0+user1+user2+user3;

Table configuration

First we need to create 4 user tables as follows:

CREATE TABLE `user_0`(
	id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_1`(
	id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_2`(
	id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_3`(
	id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

The number of sub-tables you will need to evaluate based on the amount of data you have and how it will grow over the next few years.

Table rule configuration:

spring.shardingsphere.datasource.names=master

# data sourcespring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0? characterEncoding=utf-8 spring.shardingsphere.datasource.master.username=root spring.shardingsphere.datasource.master.password=123456Table configuration
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_The ${0.. 3}

# inline expression
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_${id.longValue() % 4}

Copy the code
  • Actual-data-nodes configures sub-table information. The inline expression used here translates to master.user_0,master.user_1,master.user_2,master.user_3
  • Inline. Sharding-column Specifies the field of the sub-table, where id is used
  • Inline-algorithm-expression Expression of a sub-table algorithm. This expression must comply with groovy syntax. The preceding configuration is that modules are divided by id

If we have more complex sharding requirements, we can customize the sharding algorithm to achieve:

# Custom sub-table algorithmspring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.cxytiandi.sharding.a lgorithm.MyPreciseShardingAlgorithmCopy the code

Algorithm:

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
		for (String tableName : availableTargetNames) {
			if (tableName.endsWith(shardingValue.getValue() % 4 + "")) {
				returntableName; } } throw new IllegalArgumentException(); }}Copy the code

In the doSharding method you can do something with shardingValue and return the name of the table that needs to be sharded.

In addition to single-column field sharding, but also support multi-field sharding, you can go to see the document operation.

The database operation code does not need to change a line.

If we want to do read/write separation on the basis of single database sub-table, it is also very simple, as long as one more secondary data source can be configured, configuration is as follows:

spring.shardingsphere.datasource.names=master,slave

# primary data sourcespring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/ds_0? characterEncoding=utf-8 spring.shardingsphere.datasource.master.username=root spring.shardingsphere.datasource.master.password=123456# from data sourcespring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3306/ds_1? characterEncoding=utf-8 spring.shardingsphere.datasource.slave.username=root spring.shardingsphere.datasource.slave.password=123456Table configuration
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_The ${0.. 3}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_${id.longValue() % 4}

Read/write separation configuration
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
Copy the code

The last

You’ll find that at the end of the day, it’s easy to solve complex table scenarios with a framework. At least it’s better than calculating the routing table by field and summarizing the query yourself.

Source code reference: github.com/yinjihuan/s…

Remember to pay attention to oh, give a Star!

Welcome to join my knowledge planet, exchange technology together, learn free ape world courses (Cxytiandi.com/course)