A quick introduction to sharding JDBC (required course)

Programmer's internal affairs 2020-11-10 11:22:07
quick introduction sharding jdbc required


The book follows 《 A quick introduction to sub database sub table ( required course )》, As Sharding-JDBC The first article of the practical combat series of sub database and sub table , In the previous article, we reviewed the basic knowledge of sub database and sub table , I have a certain understanding of the splitting method of sub database and sub table , Let's introduce Sharding-JDBC Framework and quickly build a sub database and sub table case , Prepare the environment for explaining the following function points .

One 、Sharding-JDBC brief introduction

Sharding-JDBC The earliest is Dangdang network internal use of a sub database sub table framework , To 2017 We started to open up to the outside world in 2005 , In recent years, with the continuous iteration of a large number of community contributors , The function is gradually improved , Now renamed ShardingSphere,2020 year 4⽉16⽇ Officially become Apache Software base ⾦ The top item of the meeting ⽬.

As the versions change ShardingSphere The core functions of the system have also become diversified . From the very beginning Sharding-JDBC 1.0 The version only has data fragmentation , To Sharding-JDBC 2.0 Database governance is now supported in the version ( Registry Center 、 Configuration center, etc ), Until then Sharding-JDBC 3.0 Version plus distributed transaction ( Support AtomikosNarayanaBitronixSeata), Now it's iterated to Sharding-JDBC 4.0 edition .

 Insert picture description here

current ShardingSphere Not just a framework, but an ecosystem , This ecosystem Sharding-JDBCSharding-Proxy and Sharding-Sidecar These three open source distributed database middleware solutions constitute .

ShardingSphere The predecessor of Sharding-JDBC, So it's the most classic of the whole framework 、 Mature components , Let's start with Sharding-JDBC Start with the framework and learn the sub database and sub table .

Two 、 The core concept

At the beginning Sharding-JDBC Before the actual combat of sub database and sub table , It is necessary for us to understand some core concepts of sub database and sub table .

Fragmentation

In general, when we talk about sub database and sub table , Mostly in horizontal mode ( Horizontal sub database 、 table ) On the basis of , Data fragmentation will be a large amount of data table t_order Split and generate several small data scales with identical table structure t_order_0t_order_1、···、t_order_n, Each table only stores a part of the data in the original large table , When a SQL Will pass Sub library strategy Fragmentation strategy Spread the data to different databases 、 In table .

 Insert picture description here

Data nodes

The data node is the smallest data unit that can not be subdivided in the sub database and sub table ( surface ), It consists of a data source name and a data table , For example, in the figure above order_db_1.t_order_0order_db_2.t_order_1 It means 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 form t_order Split into t_order_0 ··· t_order_9 etc. 10 A watch . At this point, we will find that after the database is divided into tables, there is no more t_order This table , In its place t_order_n, But we write in the code SQL Still press t_order To write . here t_order It's these split tables Logic table .

True table

The real table is the one mentioned above t_order_n The physical tables that exist in the database .

Patch key

Database fields for sharding . We will t_order After the table is sliced , When a SQL when , Through the field order_id How to take the mold to decide , This data should be executed in which table in which database , here order_id The field is t_order The piecewise health of the watch .

 Insert picture description here

In this way, the relevant data of the same order will be stored in the same database table , Greatly improve the performance of data retrieval , More Than This sharding-jdbc It also supports slicing according to multiple fields as slicing keys .

Sharding algorithm

Above we mentioned that we can use the slice key to take the regular slice of the mold , But it's just a simple one , In actual development, we also hope to use >=<=><BETWEEN and IN Equal conditions are used as partition rules , Custom slicing logic , At this time, we need to use the partition strategy and algorithm .

From execution SQL From the perspective of , Sub database and sub table can be regarded as a routing mechanism , hold SQL Statement to route to the desired database or data table and get the data , Fragmentation algorithm can be understood as a routing rule .

Let's sort out the relationship between them first , Fragmentation strategy is just an abstract concept , It is a combination of slicing algorithm and slicing key , The slicing algorithm does the specific data slicing logic .

sub-treasury 、 The partition policy configuration of split table is relatively independent , You can use different strategies and algorithms , Each strategy can be a combination of multiple slicing algorithms , Each partition algorithm can make logical judgment on multiple partition keys .

 The relationship between slicing algorithm and slicing strategy

Be careful :sharding-jdbc It does not directly provide the implementation of the partition algorithm , Developers need to implement it according to their business .

sharding-jdbc Provides 4 A kind of slicing algorithm :

1、 Accurate segmentation algorithm

Accurate segmentation algorithm (PreciseShardingAlgorithm) Used for a single field as a tile key ,SQL There is = And IN The partition of equal conditions , Need to be in standard fragmentation strategy (StandardShardingStrategy ) Next use .

2、 Range fragmentation algorithm

Range fragmentation algorithm (RangeShardingAlgorithm) Used for a single field as a tile key ,SQL There is BETWEEN AND><>=<= The partition of equal conditions , Need to be in standard fragmentation strategy (StandardShardingStrategy ) Next use .

3、 Composite slicing algorithm

Composite slicing algorithm (ComplexKeysShardingAlgorithm) It is used for slicing operations where multiple fields are used as slicing keys , At the same time, the values of multiple patch keys are obtained , Processing business logic based on multiple fields . Need to be in the composite fragmentation strategy (ComplexShardingStrategy ) Next use .

4、Hint Sharding algorithm

Hint Sharding algorithm (HintShardingAlgorithm) not quite the same , In the algorithm above, we are all parsing SQL Statement extraction fragment key , And set up the partition strategy to partition . But sometimes we don't use any slicing keys or slicing strategies , But also want to SQL Route to target database and table , You need to specify by manual intervention SQL Target database and table information for , This is also called forced routing .

Fragmentation strategy

When I talked about the partition algorithm, I already said , Fragmentation strategy is an abstract concept , The actual partition operation is done by the partition algorithm and the partition key .

1、 Standard fragmentation strategy

The standard slicing strategy is suitable for single fragmentation keys , This strategy supports PreciseShardingAlgorithm and RangeShardingAlgorithm Two sharding algorithms .

among PreciseShardingAlgorithm It's a must , Used for processing = and IN The fragmentation of .RangeShardingAlgorithm It's optional , Used for processing BETWEEN AND, >, <,>=,<= Conditional fragmentation , If you don't configure RangeShardingAlgorithm,SQL The conditions in will be processed according to the whole database routing .

2、 Composite fragmentation strategy

Composite fragmentation strategy , Also support for SQL Statement =,>, <, >=, <=,IN and BETWEEN AND Slice operation of . The difference is that it supports multiple partition keys , The specific distribution details are completely implemented by application developers .

3、 Line expression fragmentation strategy

Line expression fragmentation strategy , Support for SQL Statement = and IN Slice operation of , But it only supports single fragment keys . This strategy is usually used for simple fragmentation , You don't need a custom slicing algorithm , You can write rules directly in the configuration file .

t_order_$->{t_order_id % 4} representative t_order For its fields t_order_id modulus , Split into 4 A watch , And the table names are t_order_0 To t_order_3.

4、Hint Fragmentation strategy

Hint Fragmentation strategy , Corresponding to the above Hint Sharding algorithm , By specifying slice keys instead of from SQL The method of extracting the fragment key in the method of slicing .

Distributed primary key

Data points ⽚ after , Different data nodes ⽣ The only way to make the whole game is ⼀ The primary key is ⾮ Often thorn ⼿ The problem of , Same as ⼀ Logic table (t_order) Different real tables in (t_order_n) Between ⾃ Increasing the bond is due to ⽆ Dharma comes from mutual perception ⽣ Duplicate primary key .

Although you can set ⾃ Add primary key Initial value and Step ⻓ Of ⽅ To avoid ID Collision , But it will increase the maintenance cost , Lack of integrity and scalability . If you need to increase the number of split tables , The step size of the partition table should be modified one by one , Operation and maintenance costs are very high , So this is not recommended .

Implement distributed primary key ⽣ There are many ways to be successful , You can refer to what I wrote before 《9 Species distributed ID generation 》.

To make it easier to get started ,ApacheShardingSphere Built in UUIDSNOWFLAKE Two types of distributed primary keys ⽣ Make it , By default ⽤ Snowflake algorithm (snowflake)⽣ become 64bit Of ⻓ Integer data . Not only that, it also takes out distributed primary keys ⽣ The interface of the device ,⽅ Then we can realize ⾃ Defined ⾃ Add primary key ⽣ The algorithm .

Broadcast table

Broadcast table : Tables that exist in all fragmented data sources , The table structure and the data in the table are exactly the same in each database . It's usually a dictionary table or a configuration table t_config, Once a table is configured as a broadcast table , Just modify the broadcast table of a database , The broadcast table data in all data sources will be synchronized with .

Binding table

Binding table : The main tables and sub tables that have the same fragmentation rules . such as :t_order Order form and t_order_item Order service item list , All press order_id Field fragmentation , So the two tables are bound to each other .

What is the meaning of binding tables ?

We usually use... In our business t_order and t_order_item Wait for tables to perform multi table joint query , But after the database is divided into tables, these tables are split into N Multiple watches . If the binding table relationship is not configured , A Cartesian product association query will appear , It will produce the following four SQL.

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

 Cartesian product query

When you configure the binding table relationship and then perform the associated query , As long as the corresponding table partition rules are consistent, the data generated will fall into the same database , So just t_order_0 and t_order_item_0 Table Association .

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

 Binding table relationships

Be careful : When associating queries t_order It serves as the main table for the entire federated query . All related routing calculations only use the policy of the main table ,t_order_item Table partition related calculations are also used t_order Conditions , So make sure that the partition keys between bound tables are exactly the same .

3、 ... and 、 and JDBC Tricky

It is not difficult to see from the name that ,Sharding-JDBC and JDBC It matters a lot , We know JDBC It's a kind of Java Language access to relational database specification , It is designed to provide a set of unified standards for various databases , Different manufacturers abide by this standard together , And provide their own implementation scheme, supply program call .

 Insert picture description here

But for developers , We only care how to call JDBC API To access the database , As long as you use DataSourceConnectionStatementResultSet etc. API Interface , You can operate the database directly . So if you want to be in JDBC The implementation of data slicing in the layer must be based on the existing API Expand the function , and Sharding-JDBC It's based on this idea , Rewrote JDBC Standard and fully compatible with JDBC standard .

JDBC technological process

To the original DataSourceConnection When the interface is extended to ShardingDataSourceShardingConnection, And the external exposure of the slice operation interface and JDBC The interfaces provided in the specification are exactly the same , As long as you are familiar with JDBC You can easily apply Sharding-JDBC To achieve sub database and sub table .

 Insert picture description here

So it applies to anything based on JDBC Of ORM frame , Such as :JPA, Hibernate,Mybatis,Spring JDBC Template Or directly used JDBC. Perfectly compatible with any third party database connection pool , Such as :DBCP, C3P0, BoneCP,Druid, HikariCP etc. , Almost all the mainstream relational databases support .

that Sharding-JDBC How to expand these interfaces ? Want to know the answer, we start from the source code , Below we use JDBC API Medium DataSource For example, see how it is rewritten and extended .

data source DataSource The core function of the interface is to obtain the database connection object Connection, We can see that it provides two ways to get database connection , And inherited CommonDataSource and Wrapper Two 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;
}

among CommonDataSource It's the root interface that defines the data source , and Wrapper Interfaces are extensions JDBC The key to the slicing function .

Due to different database vendors , They may each offer something beyond the standard JDBC API Extension of , But these functions are not JDBC Standards can't be used directly , and Wrapper The function of the interface is to provide a 、 Not JDBC Standard interfaces are packaged as standard interfaces , That is to say Adapter pattern .

Now that we're talking about the adapter mode, I'm going to say a few more words , It 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 , Make the original interface mismatch ( Or not compatible with ) Two classes that cannot work together can work together . Like listening to music with headphones , I have a round head headset , But the jack is flat , If I want to use headphones to listen to music, I have to use an adapter , This adaptor acts as an adaptation . Take a chestnut : If we Target There are hello() and word() Two methods .

public interface Target {
void hello();
void world();
}

But because of the interface version iteration Target Interface word() Methods may be obsolete or unsupported ,Adaptee Class greet() Methods will replace hello() Method .

public class Adaptee {
public void greet(){
}
public void world(){
}
}

But at this time, there are still a lot of old versions word() Methods are being used , The best way to solve this is to create an adapter Adapter, So it fits Target class , The compatibility problem caused by interface upgrade is solved .

public class Adapter extends Adaptee implements Target {
@Override
public void world() {
}
@Override
public void hello() {
super.greet();
}
@Override
public void greet() {
}
}

and Sharding-JDBC What is provided is right and wrong JDBC Standard interface , So it provides a similar implementation , It also uses Wrapper The interface is used to adapt the function of data slicing . except DataSource outside ,Connection、Statement、ResultSet Core objects also inherit this interface .

So let's go through ShardingDataSource Class source code simple look at the implementation process , The following is the flow chart of inheritance relationship .

ShardingDataSource Implementation process

ShardingDataSource It's in the original DataSource On the basis of the function expansion , Partition is registered during initialization SQL Routing wrapper 、SQL Rewrite context and result set processing engine , The data source type is also verified , Because it supports multiple different types of data sources at the same time . I don't seem to see how to fit here , Then look up ShardingDataSource The inheritance class of AbstractDataSourceAdapter .

@Getter
public class ShardingDataSource extends AbstractDataSourceAdapter {
private final ShardingRuntimeContext runtimeContext;
/**
* Registered routing 、SQl Rewrite context 、 Result set processing engine
*/
static {
NewInstanceServiceLoader.register(RouteDecorator.class);
NewInstanceServiceLoader.register(SQLRewriteContextDecorator.class);
NewInstanceServiceLoader.register(ResultProcessEngine.class);
}
/**
* Verify the data source type during initialization And according to the data source map、 Fragmentation rule 、 The database type gets a shard context , To get a database connection
*/
public ShardingDataSource(final Map<String, DataSource> dataSourceMap, final ShardingRule shardingRule, final Properties props) throws SQLException {
super(dataSourceMap);
checkDataSourceType(dataSourceMap);
runtimeContext = new ShardingRuntimeContext(dataSourceMap, shardingRule, props, getDatabaseType());
}
private void checkDataSourceType(final Map<String, DataSource> dataSourceMap) {
for (DataSource each : dataSourceMap.values()) {
Preconditions.checkArgument(!(each instanceof MasterSlaveDataSource), "Initialized data sources can not be master-slave data sources.");
}
}
/**
* Database connection
*/
@Override
public final ShardingConnection getConnection() {
return new ShardingConnection(getDataSourceMap(), runtimeContext, TransactionTypeHolder.get());
}
}

AbstractDataSourceAdapter Abstract class mainly obtains the database connection objects corresponding to different types of data sources , Realization AutoCloseable The interface is to automatically close the resources after using them ( call close Method ), Then look at the inheritance class AbstractUnsupportedOperationDataSource .

@Getter
public abstract class AbstractDataSourceAdapter extends AbstractUnsupportedOperationDataSource implements AutoCloseable {
private final Map<String, DataSource> dataSourceMap;
private final DatabaseType databaseType;
public AbstractDataSourceAdapter(final Map<String, DataSource> dataSourceMap) throws SQLException {
this.dataSourceMap = dataSourceMap;
databaseType = createDatabaseType();
}
public AbstractDataSourceAdapter(final DataSource dataSource) throws SQLException {
dataSourceMap = new HashMap<>(1, 1);
dataSourceMap.put("unique", dataSource);
databaseType = createDatabaseType();
}
private DatabaseType createDatabaseType() throws SQLException {
DatabaseType result = null;
for (DataSource each : dataSourceMap.values()) {
DatabaseType databaseType = createDatabaseType(each);
Preconditions.checkState(null == result || result == databaseType, String.format("Database type inconsistent with '%s' and '%s'", result, databaseType));
result = databaseType;
}
return result;
}
/**
* Different data source types get database connections
*/
private DatabaseType createDatabaseType(final DataSource dataSource) throws SQLException {
if (dataSource instanceof AbstractDataSourceAdapter) {
return ((AbstractDataSourceAdapter) dataSource).databaseType;
}
try (Connection connection = dataSource.getConnection()) {
return DatabaseTypes.getDatabaseTypeByURL(connection.getMetaData().getURL());
}
}
@Override
public final Connection getConnection(final String username, final String password) throws SQLException {
return getConnection();
}
@Override
public final void close() throws Exception {
close(dataSourceMap.keySet());
}
}

AbstractUnsupportedOperationDataSource Realization DataSource Interface and inherits WrapperAdapter class , There is no specific method inside it, it only serves as a bridge , But see if it's a little bit similar to the way we talked about the adapter pattern earlier .

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)");
}
}

WrapperAdapter Is a wrapper adaptation class , Realized JDBC Medium Wrapper Interface , There are two core approaches recordMethodInvocation Used to add methods and parameters that need to be executed , and replayMethodsInvocation The added methods and parameters are executed through reflection . If you look closely, you can see that both methods use JdbcMethodInvocation class .

public abstract class WrapperAdapter implements Wrapper {
private final Collection<JdbcMethodInvocation> jdbcMethodInvocations = new ArrayList<>();
/**
* Add a method to execute
*/
@SneakyThrows
public final void recordMethodInvocation(final Class<?> targetClass, final String methodName, final Class<?>[] argumentTypes, final Object[] arguments) {
jdbcMethodInvocations.add(new JdbcMethodInvocation(targetClass.getMethod(methodName, argumentTypes), arguments));
}
/**
* By reflection The method added above
*/
public final void replayMethodsInvocation(final Object target) {
for (JdbcMethodInvocation each : jdbcMethodInvocations) {
each.invoke(target);
}
}
}

JdbcMethodInvocation Class mainly applies reflection through the incoming method Methods and arguments Parameter to execute the corresponding method , So you can get through JDBC API Call not JDBC The method .

@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);
}
}

that Sharding-JDBC expand JDBC API After the interface , What's done in the new sharding function ?

A table is divided into several sub tables after sub database and sub table , And distributed to different databases , Without modifying the original business SQL Under the premise of ,Sharding-JDBC You have to be right SQL It's going to take some modifications to get it done .

The general execution process :SQL analysis -> Of board ⾏ Device optimization -> SQL route -> SQL rewrite -> SQL Of board ⾏ -> The results merge Six steps make up , Let's take a look at what each step has done .

 Insert picture description here

SQL analysis

SQL The process of parsing is divided into two steps: lexical parsing and grammatical parsing , For example, the following query user order SQL, First of all, we will use the lexical analysis SQL It's broken down into indivisible atomic units . In the dictionary provided according to different database dialects , Categorize these units as keywords , expression , Variables or operators, etc .

SELECT order_no,price FROM t_order_ where user_id = 10086 and order_status > 0

Then the parsing will split the SQL Convert to an abstract syntax tree , By traversing the abstract syntax tree , Extract the context needed for fragmentation , The context contains the query field information (Field)、 Table information (Table)、 Query criteria (Condition)、 Sort information (Order By)、 Group information (Group By) And paging information (Limit) etc. , And mark SQL May need to be rewritten in .

 Abstract syntax tree

Of board ⾏ Device optimization

Of board ⾏ Device optimization for SQL The partition conditions are optimized , Processing like keywords OR This bad taste affects the performance .

SQL route

SQL Routing through parsing fragmentation context , Matching the fragmentation policy configured by the user , And generate routing paths . The simple understanding is that we can calculate the partition strategy according to our configuration SQL It should be executed in which table in which library , and SQL The route can be distinguished according to whether there is a partition key or not Patch routing and Broadcast routing .

 Official route map

Some points ⽚ Key routing is called piecewise routing , It is subdivided into direct routing 、 Standard routing and Cartesian product routing 3 Types .

Standard route

Standard routing is the most recommended and most common ⽤ The score of ⽚⽅ type , Its suitability ⽤ The scope contains no associated queries or only associated queries between bound tables SQL.

When SQL The operator of the partitioned key is = when , The routing result will fall ⼊ Single storehouse ( surface ), Dangfen ⽚ The operator is BETWEEN or IN When the range is equal , The routing result is not ⼀ Set down ⼊ only ⼀ The library of ( surface ), therefore ⼀ Logic SQL It may eventually be broken down into multiple pieces ⽤ Yu Zhi ⾏ The real SQL.

SELECT * FROM t_order where t_order_id in (1,2)

SQL After routing 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 through the use of HintAPI Direct will SQL Route to specified ⾄ A branch of library table ⽚ The way , And direct routing can ⽤ Yu Fen ⽚ The key is not in SQL Scene in , You can hold on to ⾏ Include ⼦ Inquire about 、⾃ Define any complex situation such as function SQL.

For example, according to t_order_id The field is condition query order , At this time, I hope that I will not modify SQL Under the premise of , add user_id As a fragmentation condition, direct routing can be used .

Cartesian product routing

Cartesian routing is made up of ⾮ Bound tables are generated by associative queries between tables , Query performance is low, try to avoid this routing mode .


No points ⽚ Key routing is also called broadcast routing , It can be divided into whole database table routing 、 Database wide routing 、 Full instance routing 、 Unicast routing and blocking routing 5 Types .

Full database table routing

The whole database table routing is aimed at the database DQL and DML, as well as DDL Wait for the operation , When we execute a logical table t_order SQL when , The corresponding real table in all tile Libraries t_order_0 ··· t_order_n One by one .

Database wide routing

The whole database routing is mainly the operation of database level , Like databases SET Type of database management command , as well as TCL Such transaction control statements .

Set the logical library autocommit After attribute , This command is executed in all the corresponding real libraries .

SET autocommit=0;

Full instance routing

Full instance routing is for database instances DCL operation ( Set or change database user or role permissions ), such as : Create a user order , This command will be executed in all real library instances , To ensure that order Users can normally access every database instance .

CREATE USER order@127.0.0.1 identified BY ' Something inside the programmer ';

Unicast routing

Unicast routing is used to obtain information about a real table , For example, get the description information of the table :

DESCRIBE t_order;

t_order The real table is t_order_0 ···· t_order_n, Their descriptive structure is exactly the same , We only need to execute once on any real table .

Blocking routing

⽤ To shield SQL Operation on Database , for example :

USE order_db;

This command will not be executed in a real database ⾏, because ShardingSphere Mining ⽤ It's logic Schema( The organization and structure of the database ) ⽅ type , So there is no need to send the command to switch the database ⾄ In real databases .

SQL rewrite

Will be developed based on logical table SQL Rewrite it into a statement that can be executed correctly in a real database . Such as query t_order The order sheet , We are actually developing SQL According to the logic table t_order Written .

SELECT * FROM t_order

But after sub database and sub table, in the real database t_order The watch doesn't exist , It's split into multiple sub tables t_order_n Scattered in different databases , Still according to the original SQL Execution is obviously not going to work , 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_n

SQL Of board ⾏

Routing and rewriting reality SQL Secure and efficient sending to the underlying data source for execution . But the process is not simple SQL adopt JDBC Send directly to the data source for execution , It's about balancing data source connection creation and memory consumption , It automatically balances resource control and execution efficiency .

The results merge

Multiple data result sets to be obtained from each data node , Merge into a large result set and return to the requesting client correctly , It's called result merging . And we SQL The sorting 、 grouping 、 Pagination and aggregation syntax , All operations are performed on the merged result set .

Four 、 Fast practice

Let's combine Springboot + mybatisplus Quickly build a case of sub database and sub table .

1、 preparation

Do the preparatory work first , Create two databases ds-0ds-1, Create tables in the two databases respectively t_order_0t_order_1t_order_2t_order_item_0t_order_item_1t_order_item_2,t_config, It is convenient to verify the broadcast table later 、 Binding table scenario .

 Create tile library table The table structure is as follows :

t_order_0 The order sheet

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_0 And t_order_item_0 They are mutually related tables

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 table t_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;

ShardingSphere Provides 4 There are three kinds of partition configuration :

  • Java Code configuration

  • Yaml 、properties To configure

  • Spring Namespace configuration

  • Spring Boot To configure

To make the code look more concise and intuitive , At the back of the page, you can use properties Configuration mode , introduce shardingsphere Corresponding sharding-jdbc-spring-boot-starter and sharding-core-common package , The version is unified 4.0.0-RC1.

2、 Slice 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>

Get the work done ( mybatis There is no need to elaborate on the construction ), Let's read the slice configuration information one by one .

Let's first define two data sources ds-0ds-1, And add the basic information of the data source respectively .

# Define two global data sources 
spring.shardingsphere.datasource.names=ds-0,ds-1
# Configure data sources ds-0
spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-0.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=root
# Configure data sources ds-1
spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-1.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=root

After configuring the data source, add sub database and sub table policy for the table , Use sharding-jdbc We need to set partition rules for each table separately .

# Configure the partition table t_order
# Specify the real data node 
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{0..2}

actual-data-nodes Property specifies the real data node of the partition ,$ It's a place holder ,{0..1} Represents the actual number of database tables split .

ds-$->{0..1}.t_order_$->{0..2} The expression is equivalent to 6 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 library strategy 
# The database is divided into several parts 
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id
# The algorithm of database partition 
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2}

Set the sub database policy for the table , It was said that sharding-jdbc It provides four fragmentation strategies , In order to build it quickly, we first use the simplest inline expression slicing strategy to implement , In the next article, we will introduce the detailed usage and scenarios of the four fragmentation strategies .

database-strategy.inline.sharding-column Properties of the database-strategy For the sub database strategy , inline For specific fragmentation strategy , sharding-column It's for slice health .

database-strategy.inline.algorithm-expression It is the specific segmentation algorithm under the current strategy ,ds-$->{order_id % 2} The expression means Yes order_id Field for modular sub Library ,2 Represents the number of fragment libraries , Different strategies correspond to different algorithms , Here can also be our custom segmentation algorithm class .


# Tabulation strategy 
# It's divided into two parts 
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# Tabulation algorithm 
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 3}
# Auto increment primary key field 
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# Since the primary key ID Generation scheme 
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

Tabulation strategy and Sub library strategy The configuration is similar , The difference is that sub tables can be made through key-generator.column and key-generator.type Set the auto increment primary key and specify the generation scheme of the auto increment primary key , Currently built-in SNOWFLAKE and UUID Two ways , Can also customize the primary key generation algorithm class , I'll explain it in detail later .

# Binding table relationships 
spring.shardingsphere.sharding.binding-tables= t_order,t_order_item

Tables that must be partitioned according to the same partition key can be bound to each other , The Cartesian product query can be avoided in the joint query .

# Configure broadcast table 
spring.shardingsphere.sharding.broadcast-tables=t_config

Broadcast table , Turn on SQL Parsing log , Can see clearly SQL The process of fragmentation analysis

# Open or not SQL Parsing log 
spring.shardingsphere.props.sql.show=true

3、 Validation fragment

After the partition configuration, we do not need to modify the business code , The addition of direct execution business logic 、 Delete 、 Change 、 Check it , Next, let's verify the effect of slicing .

We're going to t_ordert_order_item Table insert 5 Order records , No primary key given order_id ,item_id field value .

public String insertOrder() {
for (int i = 0; i < 4; i++) {
TOrder order = new TOrder();
order.setOrderNo("A000" + i);
order.setCreateName(" Order " + i);
order.setPrice(new BigDecimal("" + i));
orderRepository.insert(order);
TOrderItem orderItem = new TOrderItem();
orderItem.setOrderId(order.getOrderId());
orderItem.setOrderNo("A000" + i);
orderItem.setItemName(" Service project " + i);
orderItem.setPrice(new BigDecimal("" + i));
orderItemRepository.insert(orderItem);
}
return "success";
}

See that the order records have been successfully dispersed into different warehouse tables , order_id Fields also automatically generate primary keys ID, The basic slicing function is completed .

 Basic slice

That's to the broadcast table t_config What is the effect of inserting a piece of data into ?


public String config() {
TConfig tConfig = new TConfig();
tConfig.setRemark(" I'm the radio watch ");
tConfig.setCreateTime(new Date());
tConfig.setLastModifyTime(new Date());
configRepository.insert(tConfig);
return "success";
}

Found all the Libraries t_config The watch does this SQL, Broadcast schedule and MQ The broadcast subscription model is very similar , All subscribed clients receive the same message .

 Broadcast table

Simple SQL Operation verification failed to pass , Next, try a more complex union query , We've put t_ordert_order_item Set table as bound table , Directly join the table query to execute .

 Relational query

Discover through the console log , Logic table SQL After analysis , Only right t_order_0 and t_order_item_0 The table is associated to produce a SQL.

 Binding table SQL

What happens if you don't bind tables to each other ? Get rid of spring.shardingsphere.sharding.binding-tables Have a try .

I found that the console resolved 3 Real table SQL, And get rid of it order_id After the query condition is executed again , It turns out that 9 strip SQL, A Cartesian product query is made . So the advantages of binding tables are self-evident .

 Cartesian product query

5、 ... and 、 summary

The above database and table middleware sharding-jdbc The basic concepts of the basic concept of a simple comb , Quickly build a sub database sub table case , But this is only the first step in the practice of sub database and sub table , In the next article, we will introduce the specific usage and scenarios of the four slicing strategies in detail ( Will know ), We will talk about custom distributed primary keys later 、 Distributed database transactions 、 Distributed service governance , Data desensitization, etc .

Case study GitHub Address :https://github.com/chengxy-nds/Springboot-Notebook/tree/master/springboot-sharding-jdbc

版权声明
本文为[Programmer's internal affairs]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云