Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to carry out Sharding-JDBC sub-library in ShardingSphere. 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.

We use SpringBoot+Mybaits-plus to build. Database tables We use four business objects, User, HealthRecord, HealthLevel and HealthTask. In the following figure, the most basic field definition for each business object is given, as well as the relationship between the four objects:

The pom.xml structure is as follows: 1.8 UTF-8 UTF-8 2.3.0.RELEASE org.springframework.boot spring-boot-starter-web org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.1.1 Com.baomidou mybatis-plus-boot-starter 3.4.0 org.projectlombok lombok true mysql mysql-connector-java runtime org.springframework.boot spring-boot-starter-test The test org.junit.vintage junit-vintage-engine project structure is as follows

Construct test data @ SpringBootTest@ActiveProfiles ("sharding-database") public class InitData {@ Autowired private UserService userService; @ Autowired private HealthLevelService healthLevelService; @ Autowired private HealthRecordMapper healthRecordMapper; @ Autowired private OtherTableMapper otherTableMapper; @ Test public void init () {insertUser ();} public int insertHealthLevel (int count) {for (int I = 1; I {user_id% 2}

Set up binding table and broadcast table

Binding table

The so-called binding table refers to a set of main tables and child tables that are consistent with the slicing rules. For example, in our business scenario, there is a record_id field in both the health_record table and the health_task table. If we slice according to this record_id field during the application, then the two tables can form a binding table relationship with each other.

The fundamental reason for introducing the concept of bound tables is that multi-table associative queries that bind tables to each other will not have Cartesian product, so the efficiency of associative queries will be greatly improved. For example, if you are executing the following SQL:

SELECT record.remark_name FROM health_record record JOIN health_task task ON record.record_id=task.record_id WHERE record.record_id in (1,2)

If there is no binding relationship, it will appear as Cartesian product:

SELECT record.remark_name FROM health_record0 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1,2); SELECT record.remark_name FROM health_record0 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1,2); SELECT record.remark_name FROM health_record1 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1,2) SELECT record.remark_name FROM health_record1 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1,2)

Then, after configuring the bound table relationship, the number of SQL for the route is reduced to 2:

SELECT record.remark_name FROM health_record0 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1,2); SELECT record.remark_name FROM health_record1 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1,2)

Broadcast table

The so-called broadcast table (BroadCastTable) refers to the table that exists in all sharded data sources, that is, the table structure and the data in the table are exactly the same in each database. The applicable scenarios of broadcast tables are relatively clear, usually for application scenarios where the amount of data is small and need to be associated with massive data tables. A typical example is the dictionary table that should exist in each shard database.

When the broadcast table inserts data, each database inserts the same data.

The configuration is as follows:

# setting binding table spring.shardingsphere.sharding.binding-tables [0] = health_record,health_task# setting broadcast table spring.shardingsphere.sharding.broadcast-tables [0] = health_level

Set sharding rules

# user if this is not added, the data will be randomly inserted into the database The results obtained are the same for {[0jue 1]} and {0.1}. It's just a different way to route spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$- > {[0jin1]} .user # to test0 or it will be randomly added to two databases spring.shardingsphere.sharding.tables.other_table.actual-data-nodes=test$- > {0} .other _ table# health_recordspring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$- > {0.1} .health _ recordspring.shardingsphere.sharding.tables.health_record.key- Generator.column=record_idspring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE# health_taskspring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$- > {0.. 1} .health _ taskspring.shardingsphere.sharding.tables.health_task.key-generator.column=task_idspring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE

The complete configuration is as follows (application-sharding-database.properties)

Server.port=8080# print sqlspring.shardingsphere.props.sql.show=true# configuration data source spring.shardingsphere.datasource.names=test0 Test1#test0spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0spring.shardingsphere.datasource.test0.username=devadminspring.shardingsphere.datasource.test0.password=#test1spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test1. JdbcUrl=jdbc:mysql://127.0.0.1:3306/test1spring.shardingsphere.datasource.test1.username=devadminspring.shardingsphere.datasource.test1.password=# specifies the shardingColumnspring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id# of the shard column name specifies the algorithmExpressionspring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$- of the sharding algorithm row expression > {user_id% 2} # sets the binding table spring.shardingsphere.sharding.binding-tables [0] = health_record Health_task# set broadcast table spring.shardingsphere.sharding.broadcast-tables [0] = health_level# user if you don't add this The data will be randomly inserted into the database spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$- > {[0Magne1]} .user # routed to test0 otherwise it will be randomly added to both databases spring.shardingsphere.sharding.tables.other_table.actual-data-nodes=test$- > {0} .other _ table# health_recordspring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$- > {0.1} .health _ recordspring.shardingsphere.sharding.tables.health The results in the _ record.key-generator.column=record_idspring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE# health_taskspring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$- > {0.1} .health _ taskspring.shardingsphere.sharding.tables.health_task.key-generator.column=task_idspring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE database are as follows:

The structure of the two databases is as follows

The health_level data are as follows

Health_level is a broadcast table, and the data in the two libraries are exactly the same.

The data distribution of the user table in both databases is as follows

Policy test$- > {user_id% 2} for sub-library, inserting test1 and test0 according to user_id parity distribution

The health_record data are as follows:

The health_task data are as follows:

Query test

Test the association between health_record and health_task and filter through user_id

SELECT t.taskroomidjournal. Recordroomidjournal. Userroomidnotet.taskroomnamerecoveryr. FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id WHERE t.user_id = 2

Execution log:

Actual SQL: test0: SELECT t.taskSecretidret.recordroomidret.useraccounidret.taskroomnamerecoveryr. Remark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id WHERE t.user_id =?

As you can see from the log, because user_id=2 is routed to the test0 table for query.

* Test the association between health_record and health_task without filtering

SELECT t.taskroomidjournal. Recordroomidrecovert.userroomidnotet.taskroomnamerecoveryr. Level roomidremark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id.

Execution log:

Actual SQL: test0: SELECT t.tasknormalidret.recordroomidret.userroomidrecovert.taskroomnamememr.levelcorrecidjournal r.remark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id Actual SQL: test1: SELECT t.taskroomidgradlegt.recordroomidwritt.useraccounidplayidstamt.taskroomnameplayr.levelroomid R.remark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id so much about how to do Sharding-JDBC sub-library in ShardingSphere. I hope the above content can be of some help to you and 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.