In the previous article we talked about read/write separation based on Mysql8 (linked at the end of this article), but this time we will talk about the implementation of separate libraries and tables.

Concept of analytical

Vertical fragmentation

According to the way of business separation, vertical sharding, also known as vertical split, its core idea is dedicated library. Before the split, a database consisted of multiple tables, each corresponding to a different business. After the split, the tables are grouped according to the business and distributed to different databases, thus spreading the pressure to different databases. The following figure shows a scenario for vertically shard user tables and order tables into different databases based on business needs.

Vertical sharding often requires architectural and design adjustments. Generally speaking, it is too late to cope with the rapid change of Internet business requirements; And it doesn’t really solve a single point of bottleneck. Vertical split can alleviate the problems caused by data volume and visits, but it cannot cure them. If the amount of data in the table still exceeds the threshold that can be carried by a single node after vertical splitting, horizontal sharding is required for further processing.

Level of fragmentation

Horizontal sharding is also called horizontal splitting. As opposed to vertical sharding, it no longer categorizes data according to business logic. Instead, it divides data into multiple libraries or tables according to certain rules through a certain field (or fields), with each shard containing only a portion of the data. For example, according to primary key sharding, records with even primary keys are put into library 0 (or table), and records with odd primary keys are put into library 1 (or table), as shown in the following figure.

Horizontal sharding breaks through the bottleneck of single machine data processing theoretically, and expands relative freedom. It is a standard solution of database and table.

The development of preparation

Shardingsphere is the commonly used component of the sub-library sub-table. Currently, it is a top-level project of Apache. This time, we use SpringBoot2.1.9 + ShardingSphere4.0.0-RC2 (both the latest version) to complete the operation of the sub-library sub-table.

Suppose there is an order table, we need to divide it into two libraries, each library has three tables, according to the ID field module to determine the location of the final data, the database environment configuration is as follows:

  • 172.31.0.129
    • blog
      • t_order_0
      • t_order_1
      • t_order_2
  • 172.31.0.131
    • blog
      • t_order_0
      • t_order_1
      • t_order_2

The logical table for the three tables is T_ORDER, and you can prepare all the other tables according to the table construction clause.

DROP TABLE IF EXISTS `t_order_0;
CREATE TABLE `t_order_0` (`id` bigint(20) NOT NULL, `name'varchar(255) DEFAULT NULL COMMENT' name ', 'type'varchar(255) DEFAULT NULL COMMENT' Type ', 'gmt_create'timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT' create time ', PRIMARY KEY ('id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Copy the code

Note that the primary key generation rule should not be set to auto-growth. It is necessary to generate primary keys according to certain rules. In this case, SNOWFLAKE algorithm is used to generate primary keys

Code implementation

  • Modify pom.xml to introduce related components
<properties>
        <java.version>1.8</java.version>
        <mybatis-plus.version>3.1.1</mybatis-plus.version>
        <sharding-sphere.version>4.0.0 - RC2</sharding-sphere.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
Copy the code
  • Configure the mysql – plus
	@Configuration
	@MapperScan("com.github.jianzh5.blog.mapper"Public class MybatisPlusConfig {/** * attack SQL block parser */ @bean public PaginationInterceptorpaginationInterceptor(){
					PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
					List<ISqlParser> sqlParserList = new ArrayList<>();
					sqlParserList.add(new BlockAttackSqlParser());

					paginationInterceptor.setSqlParserList(sqlParserList);
					returnnew PaginationInterceptor(); } /** * SQL execution efficiency plug-in */ @bean // @profile ({"dev"."test"})
			public PerformanceInterceptor performanceInterceptor() {
					returnnew PerformanceInterceptor(); }}Copy the code
  • Write the entity class Order
	@Data
	@TableName("t_order")
	public class Order {
			private Long id;

			private String name;

			private String type;

			private Date gmtCreate;

	}
Copy the code
  • Write the DAO layer,OrderMapper
	/** * order Dao layer */
	public interface OrderMapper extends BaseMapper<Order> {}Copy the code
  • Write interface and interface implementation
	public interface OrderService extends IService<Order> {}/** * order implementation layer *@author jianzh5
	 * @date2019/10/15 17:05 * /
	@Service
	public class OrderServiceImpl extends ServiceImpl<OrderMapper.Order> implements OrderService {}Copy the code
  • Configuration file (see Remarks for configuration description)
	server.port=8080

	Configure ds0 and DS1 data sources
	spring.shardingsphere.datasource.names = ds0,ds1

	# ds0 configurationspring.shardingsphere.datasource.ds0.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds0.driver-class-name = com.mysql.cj.jdbc.Driver Spring. Shardingsphere. The datasource. The ds0. JDBC - url = JDBC: mysql: / / 192.168.249.129:3306 / blog? characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
	spring.shardingsphere.datasource.ds0.username = root
	spring.shardingsphere.datasource.ds0.password = 000000

	# ds1 configurationspring.shardingsphere.datasource.ds1.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds1.driver-class-name = com.mysql.cj.jdbc.Driver Spring. Shardingsphere. The datasource. Ds1. JDBC - url = JDBC: mysql: / / 192.168.249.131:3306 / blog? characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
	spring.shardingsphere.datasource.ds1.username = root
	spring.shardingsphere.datasource.ds1.password = 000000

	Select a database based on the id of the database
	spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = id
	spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{id % 2}

	# Specific sub-table strategy
	# nodes ds0. T_order_0 ds0 t_order_1, an t_order_0, an t_order_1spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = ds$->{0.. 1}.t_order_$->{0.. 2}Select * from table where id = 1
	spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = id
	Select the table where the data will end up according to the id
	spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{id % 3}


	# Use the SNOWFLAKE algorithm to generate primary keys
	spring.shardingsphere.sharding.tables.t_order.key-generator.column = id
	spring.shardingsphere.sharding.tables.t_order.key-generator.type = SNOWFLAKE

	#spring.shardingsphere.sharding.binding-tables=t_order

	spring.shardingsphere.props.sql.show = true
Copy the code
  • Write unit tests to see if the results are correct
	public class OrderServiceImplTest extends BlogApplicationTests {
		@Autowired
		private OrderService orderService;


		@Test
		public void testSave(a){
			for (int i = 0 ; i< 100 ; i++){
				Order order = new Order();
				order.setName("Computer"+i);
				order.setType("Office"); orderService.save(order); }}@Test
		public void testGetById(a){
			long id = 1184489163202789377L; Order order = orderService.getById(id); System.out.println(order.toString()); }}Copy the code

  • View the data in the data table to ensure that the data is inserted properly

  • So far sub – library sub – table development completed

Review past

SpringBoot+Mysql8 implements read and write separation

For more content, please pay attention to the public number: JAVA Daily Records