The use and pit of SHARDING-JDBC and distributed worker:elastic-job in the Sub-Library sub-table framework

first, why to use sharding

In today's Internet enterprise, the multiplication of data is a very troublesome problem. Because more data is stored in a table in a database, the slower the data is for database operations. However, if we divide the data into different tables according to the specified sub-Library table strategy, we will scan a single table of a library based on the global primary key, which reduces the pressure on the individual libraries and speeds up our query. It's like Concurrenthashmap in the JDK. Divide the big problems into small problems to raise the efficiency problem.
second, the use of sharding to determine the elements 2.1. How the global primary key works

2.1.1 Select the primary key according to the business itself

In this tutorial we just use sharding simple sub-Library table, the strategy of the library is to take the model. The global primary key can be selected according to the fields, as long as it is globally unique, and the random hash is as good as possible.

2.1.2 using sharding to generate global primary keys

The documents generated for the primary key in the Sharding official document are as follows: http://shardingjdbc.io/docs/02-guide/key-generator/
2.2. Multi-Data Source configuration

In the traditional ORM project, we only need to configure a database (only to consider the most basic case, not including dynamic data sources, read-write classification, etc.), but in the sub-database table, we put the data into a number of different libraries, this time we need to configure multiple sub-data sources, You can then centralize multiple data sources on one total data source. When we do this, we dynamically assign operations to multiple data sources based on the Shard policy and the global primary key.
2.3. sharding algorithm

We already have the global primary key, and multiple databases. So how do we assign to a different database based on the primary key, we need the Shard algorithm so that SHARDING-JDBC knows how to allocate the data for processing. Sharding provides a number of different sub-list strategies to facilitate our implementation.

Official documents are as follows:

The SHARDING-JDBC provides 5 sharding strategies. Because of the close correlation between the sharding algorithm and the business implementation, SHARDING-JDBC does not provide built-in sharding algorithms, but extracts the various scenarios through the sharding strategy, providing a higher level of abstraction and providing an interface to enable the application developer to implement the sharding algorithm on its own. Standardshardingstrategy

Standard sharding policy. Provides support for Shard operations in SQL statements =, in, and between and. The Standardshardingstrategy only supports single-shard keys, and provides a preciseshardingalgorithm and Rangeshardingalgorithm two shard algorithms. Preciseshardingalgorithm is required for processing = and in shards. Rangeshardingalgorithm is optional and is used to process between and shards, if between and in Rangeshardingalgorithm,sql are not configured, and will be handled in full library routing. Complexshardingstrategy

Composite Shard policy. Provides support for Shard operations in SQL statements =, in, and between and. Complexshardingstrategy support multi-partition key, because the relationship between the multi-partition key is complex, so SHARDING-JDBC does not do too much encapsulation, but directly to the partition key value combination and the Shard operator to the algorithm interface, completely by the application developer, Provide maximum flexibility. Inlineshardingstrategy

The inline expression shard policy. Use groovy's inline expression to provide support for Shard operations in the SQL statement for = and in. Inlineshardingstrategy only supports single-shard key, for simple shard algorithm, can be used by simple configuration, so as to avoid tedious Java code development, such as: tuser${user_id% 8} represents T_user table according to User_ The ID is divided into 8 tables by 8 and the table name is t_user_0 to t_user_7. Hintshardingstrategy

A policy that is fragmented by hint rather than by SQL parsing. Noneshardingstrategy

Non-sharding policy. " 2.4 Transaction processing

Since our purpose in this paper is to complete the use of the most basic sharding, there is no master-slave synchronization and other settings, so we deal with the transaction as the single table as well. When you are doing insert,delete,uodate and so on. We add a transaction to the service, and when it is processed, it will operate according to your incoming global primary key. In this way, the operation is mapped to a table on one of the libraries.
Third, the code implementation