Book continued“One article quick introduction sub database sub table (compulsory course)”This article took a long time. It was originally planned to be finished a week ago. As a result, the personnel in the family suddenly changed, and the leadership handed over the power, and then announced that I was officially a father. Then my family status fell to the third place, and I was assigned a long-term maintenance task: taking care of children. Take a look at our beautiful photos, the standard little lady is a cute da.
AsSharding-JDBCIn the first article of the practical combat series of sub database and sub table, we reviewed the basic knowledge of sub database and sub table in the previous article, and had a certain understanding of the splitting method of sub database and sub table. Next, we will introduce itSharding-JDBCFramework and quickly build a case of sub database and sub table to prepare the environment for explaining the follow-up function points.
1、 Introduction to sharding JDBC
Sharding-JDBCIt was originally used by dangdang.com as a sub database and sub table framework. It started to open source in 2017. In recent years, with the continuous iteration of a large number of community contributors, the function has gradually improved, and now it has been renamed asShardingSphereIn 2020, 4 ⽉ 16 ⽇ApacheTop level item of the software foundation.
As the version changesShardingSphereIts core functions have also become diversified. From the beginning, sharding-jdbc version 1.0 only had data fragmentation, to sharding-jdbc version 2.0 began to support database governance (registry, configuration center, etc.), and then to sharding-jdbc version 3.0 with distributed transactions (supportingAtomikos、Narayana、Bitronix、Seata）, has now been iterated to sharding JDBC version 4.0.
Now shardingsphere is not just a framework, but an ecosystemSharding-JDBC、Sharding-ProxyandSharding-SidecarThese three open source distributed database middleware solutions.
ShardingSphereIts predecessor isSharding-JDBCSo it is the most classic and mature component in the whole frameworkSharding-JDBCStart with the framework and learn the sub database and sub table.
2、 Core concepts
At the beginningSharding-JDBCBefore the actual combat, it is necessary for us to understand some core concepts of sub database and sub table.
Sectioning
In general, when we talk about the sub database and sub table, most of them are based on the horizontal segmentation mode (horizontal sub database and sub table). Data fragmentation will be a table with a large amount of datat_orderSplit and generate several small data scales with the same table structuret_order_0、t_order_1、···、t_order_nEach table only stores a part of the data in the original large tableSQLWill passSub database strategy、Fragmentation strategyDistribute the data to different databases and tables.
Data node
Data node is the smallest data unit (table) that can not be subdivided in sub database and sub table. It is composed of data source name and data table, for example, in the above figureorder_db_1.t_order_0、order_db_2.t_order_1It represents a data node.
Logic table
Logical table refers to a group of tables with the same logic and data structure. For example, we will order formt_orderSplit intot_order_0 ··· t_order_9Wait for 10 tables. At this point, we will find that the database is no longer in the database after the sub database and sub tablet_orderThis is the list. It’s replaced by this onet_order_nBut we write in the codeSQLPress againt_orderTo write. heret_orderThese are the split tablesLogic table。
Real table
The real table is the one mentioned abovet_order_nThe physical table that exists in the database.
Fragment key
Database fields for sharding. We willt_orderAfter the table is partitioned, when a SQL statement is executed, theorder_idThis data is executed in which database and in which tableorder_idThe field ist_orderThe slice key of the watch.
In this way, the related data of the same order will be stored in the same database table, which greatly improves the performance of data retrievalsharding-jdbcIt also supports slicing according to multiple fields as the slicing key.
Partition algorithm
We mentioned that we can use the slicing key to take the regular slice of the mold, but this is only a relatively simple one, and we hope to use it in the actual development>=、<=、>、<、BETWEENandINIn this case, partition strategy and algorithm are needed.
From the point of view of executing SQL, sub database and sub table can be regarded as a routing mechanism, which can route SQL statements to the desired database or data table and obtain data. The fragmentation algorithm can be understood as a routing rule.
Let’s first sort out the relationship between them. The partitioning strategy is only an abstract concept. It is composed of the slicing algorithm and the slicing key. The slicing algorithm does the specific data slicing logic.
The partition strategy configuration of the database and the table is relatively independent. Different strategies and algorithms can be used respectively. Each strategy can be a combination of multiple partition algorithms, and each partition algorithm can make logical judgment on multiple slice health.
be careful: sharding JDBC does not directly provide the implementation of fragmentation algorithm, so developers need to implement it according to their business.
sharding-jdbcFour algorithms are provided
1. Accurate segmentation algorithm
Precision shardingalgorithm is used as partition key for a single field. SQL has=AndINEqual conditions of fragmentation, need to be in the standard fragmentation strategy（StandardShardingStrategy）It can be used under.
2. Range slicing algorithm
The range sharding algorithm is used for a single field as a partition keyBETWEEN AND、>、<、>=、<=Equal conditions of fragmentation, need to be in the standard fragmentation strategy（StandardShardingStrategy）It can be used under.
3. Compound partition algorithm
Complex keys sharding algorithm is used to partition multiple fields as partition keys. It can obtain the values of multiple partition keys at the same time, and process business logic according to multiple fields. Need to be in the composite fragmentation strategy（ComplexShardingStrategy）It can be used under.
4. Hint partition algorithm
The hint sharding algorithm is slightly different. In the algorithm above, we are all parsingSQLStatement to extract the partition key, and set the partition strategy to partition. However, sometimes we don’t use any partition key and partition strategy, but if we want to route SQL to the target database and table, we need to manually intervene to specify the target database and table information of SQL, which is also called forced routing.
Fragmentation strategy
When we talked about the partition algorithm, we have already said that the partition strategy is an abstract concept, and the actual operation of the partition is completed by the partition algorithm and the partition key.
1. Standard fragmentation strategy
The standard partition strategy is suitable for single partition key, which supportsPreciseShardingAlgorithmandRangeShardingAlgorithmTwo segmentation algorithms.
amongPreciseShardingAlgorithmRequired for processing=andINA piece of.RangeShardingAlgorithmIs optional for processingBETWEEN AND， >， <，>=，<=Conditional slicing, if not configuredRangeShardingAlgorithm, the conditions in SQL, etc. will be processed according to the full library routing.
2. Compound fragmentation strategy
The composite partition strategy also supports the=，>， <， >=， <=，INandBETWEEN ANDPartition operation. The difference is that it supports multiple partition keys, and the specific allocation details are completely implemented by application developers.
3. Line expression slicing strategy
Row expression slicing strategy, support SQL statements in the=andINBut only single partition key is supported. This strategy is usually used for simple slicing, without the need for custom slicing algorithm, and rules can be written directly in the configuration file.
t_order_$->{t_order_id % 4}representativet_orderFor its fieldt_order_idTake the module and split it into four tables, and the table names aret_order_0reacht_order_3。
4. Hint fragmentation strategy
The hint partition strategy corresponds to the above hint partition algorithm by specifying the partition key instead of fromSQLThe strategy is to extract the fragment key from the database.
Distributed primary key
After data partition, it is a common problem that different data nodes become global only primary keys. It is the same logical table（t_order）Different real tables in（t_order_n）Because of the mutual perception of the method, the increasing key between the two produces a duplicate primary key.
Although the primary key can be increased by setting ⾃Initial valueandStep by stepHowever, it will increase the maintenance cost, lack of integrity and scalability. If you need to increase the number of partitioned tables in the future, you need to modify the step size of partitioned tables one by one. The operation and maintenance cost is very high, so this method is not recommended.
There are many ways to implement a distributed primary key generator. Please refer to the previous one《Nine distributed ID generation methods》。
To make it easier to get started, Apache shardingsphere has built-inUUID、SNOWFLAKETwo kinds of distributed primary key generator, the default snow algorithm（snowflake）It is a 64 bit integer data. In addition, it also separates the interface of distributed primary key generator, so that we can implement the algorithm of adding primary key defined by.
Broadcast table
Broadcast table: a table that exists in all partitioned data sources. The table structure and data in the table are identical in each database. It is usually a dictionary table or a configuration tablet_configOnce a table is configured as a broadcast table, as long as the broadcast table of a database is modified, the data of broadcast table in all data sources will be synchronized.
Binding table
Bound table: the main table and sub table whose fragmentation rules are consistent. For example:t_orderOrder form andt_order_itemOrder service items list, all byorder_idThe fields are partitioned, so the two tables are bound to each other.
What is the meaning of binding tables?
It is usually used in our businesst_orderandt_order_itemThe tables are divided into n sub tables because of the sub database and sub table. If the binding table relationship is not configured, Cartesian product association query will appear, and the following four items will be generatedSQL。
SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id
When the binding table relationship is configured and then the association query is performed, as long as the corresponding table partition rules are consistent, the data generated will fall into the same databaset_order_0andt_order_item_0Table association is enough.
SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id
be careful: when associating queries
t_orderIt serves as the main table for the entire federated query. All related routing calculations only use the primary table policy,t_order_itemThe slice related calculations of the table will also be usedt_orderTo ensure that the partition keys between bound tables are exactly the same.
3、 And JDBC
It’s not hard to see from the name,Sharding-JDBCandJDBCIt has a lot to do with it. We know that JDBC is aJavaThe specification of language accessing relational database is designed to provide a set of unified standards for all kinds of databases. Different manufacturers should abide by this set of standards and provide their own implementation schemes for program call.
But in fact, for developers, we only care about how to call JDBC API to access the database, as long as it is used correctlyDataSource、Connection、Statement 、ResultSetAPI interface, directly operate the database. Therefore, if you want to implement data fragmentation in JDBC level, you must expand the existing API functions, and sharding JDBC rewrites the JDBC specification and is fully compatible with the JDBC specification.
To the originalDataSource、ConnectionAnd so onShardingDataSource、ShardingConnectionAs long as you are familiar with JDBC, you can easily use sharding JDBC to implement the partition table.
So it applies to anyJDBCOfORMFramework, such as:JPA， Hibernate，Mybatis，Spring JDBC TemplateOr use JDBC directly. Perfectly compatible with any third-party database connection pool, such as:DBCP， C3P0， BoneCP，Druid， HikariCPIt supports almost all mainstream relational databases.
thatSharding-JDBCHow to expand these interfaces? Want to know the answer, we start from the source code, next we use JDBC APIDataSourceFor example, see how it is rewritten and extended.
data sourceDataSourceThe core function of the interface is to obtain the database connection objectConnectionWe can see that it provides two methods to obtain the database connection, and inherits theCommonDataSourceandWrapperTwo interfaces.
public interface DataSource extends CommonDataSource, Wrapper {
/**
* <p>Attempts to establish a connection with the data source that
* this {@code DataSource} object represents.
* @return a connection to the data source
*/
Connection getConnection() throws SQLException;
/**
* <p>Attempts to establish a connection with the data source that
* this {@code DataSource} object represents.
* @param username the database user on whose behalf the connection is
* being made
* @param password the user's password
*/
Connection getConnection(String username, String password)
throws SQLException;
}amongCommonDataSourceIs the root interface that defines the data source, which is understandable, andWrapperThe interface is the key to expand the function of JDBC fragmentation.
Due to different database vendors, they may provide some extended functions beyond the standard JDBC API, but these functions cannot be used directly without JDBC standardWrapperThe function of the interface is to package a non JDBC standard interface provided by a third-party supplier into a standard interfaceAdapter mode。
Now that we have talked about the adapter mode, I’d like to say a few more words, which is also convenient to understand later.
Adapter pattern is a common design pattern. Its function is to convert the interface of a class to another interface expected by the client, so that two classes that cannot work together due to interface mismatch (or incompatibility) can work together.
For example, if I want to listen to music with headphones, I have a round head headset, but the mobile phone jack is flat. If I want to use the headset to listen to music, I have to use an adapter. This adapter plays an adaptive role.
Take a chestnut: if weTargetThere arehello()andword()Two methods.
public interface Target {
void hello();
void world();
}Due to the interface version iterationTargetInterfaceword()Methods may be obsolete or unsupported,AdapteeClassgreet()Method will replacehello()method.
public class Adaptee {
public void greet(){
}
public void world(){
}
}But there are still a lot of old versions at this timeword()Method is used, and the best way to solve this problem is to create an adapterAdapterSo it fitsTargetClass to solve the compatibility problem caused by interface upgrade.
public class Adapter extends Adaptee implements Target {
@Override
public void world() {
}
@Override
public void hello() {
super.greet();
}
@Override
public void greet() {
}
}andSharding-JDBCIt provides a non JDBC standard interface, so it also provides a similar implementation scheme, which is also usedWrapperThe interface is used to adapt the function of data slicing. In addition to datasource, core objects such as connection, statement and resultset also inherit this interface.
Let’s go throughShardingDataSourceClass source code simple look at the implementation process, the following is the inheritance relationship flow chart.
ShardingDataSourceClass it is in the originalDataSourceAt the same time, it also checks the data source type, because it supports multiple different types of data sources at the same time. I don’t seem to see how to fit here, so look upShardingDataSourceInheritance class ofAbstractDataSourceAdapter 。
@GetterAbstractDataSourceAdapterThe abstract class mainly obtains the database connection objects corresponding to different types of data sourcesAutoCloseableThe interface is to automatically close the resources after using them (callcloseMethod), and then look at the inheritance classAbstractUnsupportedOperationDataSource 。
@Getter AbstractUnsupportedOperationDataSourcerealizationDataSourceInterface and inheritsWrapperAdapterClass. There are no concrete methods inside it, and it only plays the role of bridging. But it seems that it is a little similar to the previous example of adapter pattern.
public abstract class AbstractUnsupportedOperationDataSource extends WrapperAdapter implements DataSource {
@Override
public final int getLoginTimeout() throws SQLException {
throw new SQLFeatureNotSupportedException("unsupported getLoginTimeout()");
}
@Override
public final void setLoginTimeout(final int seconds) throws SQLException {
throw new SQLFeatureNotSupportedException("unsupported setLoginTimeout(int seconds)");
}
}WrapperAdapterIs a wrapper adaptation class, which implements theWrapperInterface, which has two core methodsrecordMethodInvocationUsed to add methods and parameters that need to be executed, andreplayMethodsInvocationThe added methods and parameters are executed through reflection. If you look closely, you can see that both methods are usedJdbcMethodInvocationClass.
public abstract class WrapperAdapter implements Wrapper {JdbcMethodInvocationClass mainly applies reflection through themethodMethods andargumentsParameter to execute the corresponding method, so that non JDBC methods can be called through the JDBC API.
@RequiredArgsConstructor
public class JdbcMethodInvocation {
@Getter
private final Method method;
@Getter
private final Object[] arguments;
/**
* Invoke JDBC method.
*
* @param target target object
*/
@SneakyThrows
public void invoke(final Object target) {
method.invoke(target, arguments);
}
}thatSharding-JDBCAfter expanding the JDBC API interface, what are the new sharding functions?
A table is divided into multiple sub tables after sub database and sub table, and distributed to different databases. On the premise of not modifying the original business SQL,Sharding-JDBCIt is necessary to make some modifications to SQL to execute normally.
General implementation process:SQL parsing -> Actuator optimization -> SQL routing -> SQL rewriting -> SQL execution -> Result mergingSix steps. Let’s see what each step does.
SQL parsing
The SQL parsing process is divided into two steps: lexical parsing and syntax parsing. For example, in the next SQL query for user orders, lexical parsing is used to decompose the SQL into atomic units that cannot be further divided. According to the dictionaries provided by different database dialects, these units are classified as keywords, expressions, variables or operators.
SELECT order_no,price FROM t_order_ where user_id = 10086 and order_status > 0Then syntax parsing will convert the split SQL into an abstract syntax tree. Through traversing the abstract syntax tree, the context needed for fragmentation can be extracted. The context contains the query field information（Field）, table information（Table）, query conditions（Condition）, sort information（Order By）Group information（Group By）And paging information（Limit）And mark the location in SQL that may need to be rewritten.
Optimization of actuator
The executor optimization optimizes the SQL partition conditions and processes the keywordsORThis bad smell affects performance.
SQL routing
SQL routing matches the partition policy configured by users by parsing the fragmentation context, and generates the routing path. The simple understanding is that we can calculate which database and which table SQL should be executed according to our configured partition strategy, and SQL routing can be distinguished according to whether there are partition keysFragment routingandBroadcast routing。
The routing with partition key is called partitioned routing, which can be divided into three types: direct routing, standard routing and Cartesian product routing.
Standard routing
Standard routing is the most recommended and most common formula. Its suitable range is SQL that does not contain associated queries or only contains associated queries between bound tables.
When the SQL fragment key operator is=When the partition operator is, the routing result will fall into a single database (table)BETWEENorINWhen the range is equal, the routing result will not be determined by the only database (table), so the logical SQL may be split into multiple real SQL.
SELECT * FROM t_order where t_order_id in (1,2)After SQL route processing
SELECT * FROM t_order_0 where t_order_id in (1,2)
SELECT * FROM t_order_1 where t_order_id in (1,2)Direct routing
Direct routing is achieved by usingHintAPIA method of directly routing SQL to the specified database table. Moreover, direct routing can be used in the scenario where the key is not in SQL. It can also execute any SQL in complex situations such as query and function definition.
For example, according tot_order_idIn this case, you want to add theuser_idAs a fragmentation condition, direct routing can be used.
Cartesian product routing
Cartesian routing is generated by the association query between the binding tables, and the query performance is low. Try to avoid this routing mode.
Key free routing, also known as broadcast routing, can be divided into five types: full database table routing, full database routing, full instance routing, unicast routing and blocking routing.
Whole database table routing
Database table routing is for databasesDQLandDML, andDDLWhen we execute a logical tablet_orderIn SQL, the corresponding real tables in all fragment librariest_order_0 ··· t_order_nOne by one.
Whole database routing
The whole database routing is mainly for database level operations, such as databaseSETType of database management commands, and transaction control statements such as TCL.
Setting the logical libraryautocommitProperty, the command is executed in all corresponding real libraries.
SET autocommit=0;Full instance routing
Full instance routing is a DCL operation (setting or changing database user or role permissions) for database instances, such as creating a user order, which will be executed in all real database instances to ensure that order users can normally access each database instance.
CREATE USER [email protected] Identified by ';Unicast routing
Unicast routing is used to obtain the information of a real table, such as the description information of the table
DESCRIBE t_order;t_orderThe real table of ist_order_0 ···· t_order_nThey have the same description structure. We only need to execute it once in any real table.
Blocking routing
To mask SQL operations on databases, for example:
USE order_db;This command will not be executed in a real database becauseShardingSphereIt adopts logical schema (organization and structure of database), so there is no need to send the command of switching database to real database.
SQL rewriting
The SQL based on logical table is rewritten into statements that can be executed correctly in real database. For example, queryt_orderOrder table, SQL in our actual development is based on logical tablet_orderIt was written.
SELECT * FROM t_orderBut after sub database and sub table, the real databaset_orderThe table does not exist, but is split into multiple sub tablest_order_nIt is obviously impossible to execute the original SQL in different databases. In this case, the logical table name in the sub table configuration needs to be rewritten to the real table name obtained after routing.
SELECT * FROM t_order_nSQL execution
The real SQL after routing and rewriting is sent to the underlying data source safely and efficiently for execution. However, this process does not simply send SQL directly to the data source for execution through JDBC, but balances the consumption of data source connection creation and memory consumption. It will automatically balance resource control and execution efficiency.
Result merging
Merging multiple data result sets from each data node into a large result set and returning it to the requesting client correctly is called result merging. The syntax of sorting, grouping, pagination and aggregation in SQL is operated on the merged result set.
4、 Quick practice
Let’s combineSpringboot + mybatisplusQuickly build a case of sub database and sub table.
1. Preparatory work
Do the preparatory work first, create two databasesds-0、ds-1, create tables in the two databases respectivelyt_order_0、t_order_1、t_order_2 、t_order_item_0、t_order_item_1、t_order_item_2，t_configTo facilitate the verification of broadcast table and binding table.
The table structure is as follows:
t_order_0Order form
CREATE TABLE `t_order_0` (
`order_id` bigint(200) NOT NULL,
`order_no` varchar(100) DEFAULT NULL,
`create_name` varchar(50) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;t_order_0Andt_order_item_0Mutually related table
CREATE TABLE `t_order_item_0` (
`item_id` bigint(100) NOT NULL,
`order_no` varchar(200) NOT NULL,
`item_name` varchar(50) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;Broadcast tablet_config
`id` bigint(30) NOT NULL,
`remark` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;ShardingSphereFour partition configuration modes are provided
-
Java code configuration
-
Yaml, properties configuration
-
Spring namespace configuration
-
Spring boot configuration
In order to make the code look more concise and intuitive, the unified use of the backpropertiesConfiguration mode, introductionshardingsphereCorrespondingsharding-jdbc-spring-boot-starterandsharding-core-commonPackage, version 4.0.0-rc1.
2. Partition configuration
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-common</artifactId>
<version>4.0.0-RC1</version>
</dependency>After the preparatory work is finished (the mybatis setup will not be discussed in detail). Next, we will interpret the partition configuration information one by one.
We first define two data sourcesds-0、ds-1And add the basic information of the data source respectively.
#Define two global data sourcesAfter configuring the data source, add the sub database and sub table policy for the table, and use thesharding-jdbcWe need to set partition rules for each table separately.
#Configure partition table t_ orderactual-data-nodesProperty specifies the real data node of the partition,$Is a placeholder, {0.. 1} represents the actual number of database tables split.
ds-$->{0..1}.t_order_$->{0..2}The expression is equivalent to six data nodes
- ds-0.t_order_0
- ds-0.t_order_1
- ds-0.t_order_2
- ds-1.t_order_0
- ds-1.t_order_1
- ds-1.t_order_2
###Sub database strategySet the sub database policy for the tablesharding-jdbcIt provides four kinds of slicing strategies. In order to build them quickly, we first implement them with the simplest in line expression slicing strategy. In the next chapter, we will introduce the detailed usage and usage scenarios of the four slicing strategies.
database-strategy.inline.sharding-columnAttributedatabase-strategyThe database strategy is divided into two parts,inlineFor the specific segmentation strategy,sharding-columnIt stands for piecewise health.
database-strategy.inline.algorithm-expressionIt is a specific segmentation algorithm under the current strategy,ds-$->{order_id % 2}The expression means yesorder_idField is used to extract module and divide the library. 2 represents the number of fragment libraries. Different strategies correspond to different algorithms. Here, we can also customize the partition algorithm class.
#Sub table strategyThe configuration of sub table policy and sub database policy are similar, but the difference is that the sub table policy can be configured through thekey-generator.columnandkey-generator.typeSet the auto increment primary key and specify the generation scheme of the auto increment primary key. Currently, theSNOWFLAKEandUUIDThe two methods can also customize the primary key generation algorithm class, which will be explained in detail later.
#Binding table relationshipTables that must be partitioned according to the same partition key can be bound to each other, and Cartesian product query can be avoided in joint query.
#Configure broadcast tableBroadcast table, open SQL parsing log, you can clearly see the process of SQL fragment parsing
#Do you want to open SQL parsing log3. Validation fragment
After the partition configuration is completed, we do not need to modify the business code. We can directly add, delete, modify, and query the business logic. Next, verify the effect of fragmentation.
At the same time, wet_order、t_order_itemTable inserts 5 order records without a primary keyorder_id ，item_idField value.
public String insertOrder() {You can see that the order records have been successfully dispersed into different warehouse tables,order_idThe field also automatically generates the primary key ID, and the basic fragmentation function is completed.
That’s the broadcast tablet_configWhat is the effect of inserting a piece of data into?
public String config() {Found in all librariest_configTables have executed this SQL. Broadcast table and MQ broadcast subscription mode are very similar, and all subscribers will receive the same message.
Simple SQL operation verification failed. Next, we will try a more complex joint query. We have already put thet_order 、t_order_itemSet the table as a bound table and execute the query directly.
Through the console log, it is found that after parsing the logical table SQL, only thet_order_0andt_order_item_0Tables are associated to produce a SQL.
What happens if tables are not bound to each other? Removespring.shardingsphere.sharding.binding-tableswant a go.
It was found that the console parsed out three real table SQL, and removedorder_idAfter being executed again as a query condition, 9 SQL pieces were parsed and Cartesian product query was carried out. So the advantages of binding tables are self-evident.
5、 Summary
The above database and table Middlewaresharding-jdbcIn the next chapter, we will introduce the specific usage and usage scenarios of the four fragmentation strategies in detail (we must know it). In the following part, we will explain the self-defined distributed primary key, distributed database transaction, distributed service governance, data desensitization, etc.
case
GitHubAddress:github.com/chengxy-nds/Springboot-…
Hundreds of technical e-books have been sorted out and sent to our friends. Attention to public name reply【666】Collect it by yourself. We have set up a technology exchange group with some partners to discuss technology and share technical information, aiming to learn and progress together. If you are interested, please join us!
This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint