After a brief introduction to the use of multiple and dynamic data sources, this article goes on to introduce a common scenario in real business operations — database and table splitting. With the continuous enrichment of software system functions, the huge user groups and the various mass data brought by the users have added huge pressure to the database. As a very simple example, the number of customer orders for many C-side products will increase geometrically over time as the system runs. In this way, when we search for orders according to certain conditions, the query efficiency is bound to be very low. Perhaps the establishment of indexes in high-frequency search fields can alleviate this phenomenon to a certain extent. But this does not solve the problem in essence and the time and storage costs associated with indexing are not “cheap”. A technical solution to this kind of problem – sub – database sub – table should be born, in fact, there are a variety of middleware on the market can choose. Famous middleware include :MyCat, Cobar and MySQL Router, etc. You can go to the corresponding official website or community to check the details of these middleware. Today, we are going to introduce Sharding JDBC, a lightweight table and library tool. Compared with the others, it has obvious advantages of low project invasion and simple deployment. The author carried out the transformation of sub-database and sub-table on a mature operating system, so the low invasion of the project is a very important factor for me to choose it. After all, refactoring a lot of old code to use a tool is not an easy and costly task.

Data Source Configuration

    @Bean(name = "crmDataSource") @ Primary public DataSource DataSource () throws SQLException {/ / configuration data fragmentation rules ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfiguration()); shardingRuleConfig.getTableRuleConfigs().add(orderItemTableRuleConfiguration()); shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item"); / / according to different user's user id will be the order information is routed to the two database shardingRuleConfig. SetDefaultDatabaseShardingStrategyConfig (new InlineShardingStrategyConfiguration("user_id"."ds${user_id % 2}")); / / in order according to the order number will be routed to the letter in the child table shardingRuleConfig. SetDefaultTableShardingStrategyConfig (new StandardShardingStrategyConfiguration ("order_id", new TableShardingAlgorithm());
        returnShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig); } // Order form subtable TableRuleConfigurationorderRuleConfiguration() {
         TableRuleConfiguration result = new TableRuleConfiguration();
         result.setLogicTable("t_order");
         result.setActualDataNodes("dsThe ${0.. 1}.t_orderThe ${0.. 4}");
         result.setKeyGeneratorColumnName("order_id");
         returnresult; } // Order details subtable TableRuleConfigurationorderItemRuleConfiguration() {
         TableRuleConfiguration result = new TableRuleConfiguration();
         result.setLogicTable("t_order_item");
         result.setActualDataNodes("dsThe ${0.. 1}.t_order_itemThe ${0.. 4}");
         returnresult; } // Map<String, DataSource>createDataSourceMapMysqlXADataSource =new MysqlXADataSource(); mysqlXADataSource.setUrl(db0Url); mysqlXADataSource.setPassword(password); mysqlXADataSource.setUser(userName); mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
        AtomikosDataSourceBean dataSource=new AtomikosDataSourceBean();
        dataSource.setXaDataSource(mysqlXADataSource);
        dataSource.setUniqueResourceName("ds0");
        dataSource.setMaxPoolSize(30);
        dataSource.setMinPoolSize(5); . Map<String, DataSource> dataSourceMap = new HashMap<>(); dataSourceMap.put("ds0",dataSource1);
        dataSourceMap.put("ds1",dataSource2); .return datasourceMap;
     }
    
Copy the code

Remember the distributed transactions introduced in the previous article, where database operations were performed on the table. Therefore, data source initialization using XADataSource is required for effective transaction management. In the operation of database and table, it is divided into database routing and data table routing, which need to be specified clearly respectively (the professional term here is called the formulation of data sharding rules). As shown in the figure above, JDBC determines which table in which database to operate on when opening a database connection to operate. All we need to do is make this rule, and then we can trust Sharding-JDBC to do the rest. For example, we split the order table T_ORDER into 10 sub-tables, and then spread the ten sub-tables evenly among ds0 and DS1 databases. The order has the user to which it belongs. If we take the module of the user number and map it to the corresponding database according to the remainder, then take the module according to the order number and map the data to the corresponding data table. After such double screening, it is the most simple and efficient data sharding rule. The code shown above follows this rule and can be downgraded from million-level order processing to 100,000-level order processing, which can greatly improve the efficiency of the final database operation.

Configure a sub-table policy

public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

	@Override
	public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue)for (String each : tableNames) {
			if (each.endsWith(shardingValue.getValue() % 5 + "")) {
				returneach; }} // Otherwise, use the table namereturnshardingValue.getLogicTableName(); }}Copy the code

T_order0-4 ds0.t_order0-4 ds1.t_order0-4 ds0.t_order0-4 ds0.t_order0-4 ds0.t_order0-4 ds0.t_order0-4

/ / even-numbered orders into the database of user belongs to 0, an odd number into 1 new InlineShardingStrategyConfiguration database ("user_id"."ds${user_id % 2}") // The orders in each database are stored in the corresponding sub-table according to the order numbersetActualDataNodes("dsThe ${0.. 1}.t_orderThe ${0.. 4}");
Copy the code

This tells JDBC an optional range for a logical table to be replaced by a real table when the database performs an operation, ensuring that the operation is within a reasonable boundary without error. If no replaceable table is found, the table name is returned directly, so that a database can be compatible with both parts of the database that need to be partitioned and parts that do not. Is it easy to configure, which is true to the title of lightweight, it does not require heavy weight database middleware like Mycat and requires a dedicated middleware server to work with. Maven only needs to introduce the corresponding dependencies through jar packages to easily enable the function of library and table, and has good support for SpringBoot projects. In fact, Sharding Sphere not only provides functions of library and table, but also various data services such as read and write separation, data governance and flexible transactions (details can be viewed on the official website of asynchronous: ShardingSphere.io). This article only introduces some of its functions, the rest of the richer features are waiting for readers to learn, thank you for reading!