When implementing the sharding rule, the implemented interface is precise sharding algorithm, that is, accurate sharding, which maps the specified key value records to a specified table (at most one table). This interface can basically meet 80% of the requirements.

Other rules include Range, ComplexKey, and Hint fragmentation rules, which can map eligible key value records to multiple tables, that is, record A can be inserted into multiple tables A, B or B, C at the same time.

Among them,

Range is the range filter fragment. I personally understand that, for example, the id mantissa 1-5 is inserted into table A, and the id mantissa 6-0 is inserted into table B. in this case, it is more convenient to use range as the filter condition. It can also be divided according to the time range. (please correct if there is any mistake).

The name of ComplexKey is the combination of keys. You can make mapping rules based on multiple keys at the same time.

Hint didn't understand the name, but the source code is actually a combination of key fragments, but only supports the precise selection of combination keys.

ComplexKey supports range filtering of key combinations. So it can be understood that ComplexKey is an advanced version of Hint. 　　

No matter which sharding algorithm is implemented, ensure that the algorithm covers all possible key values.

4, Use row expression to configure fragmentation strategy (skip step 3 Optimization)

In the third step above, we define the fragmentation algorithm by implementing the PreciseShardingValue interface. In this way, every table needs to be partitioned and a class needs to be redefined. It's too troublesome.

Sharding provides a way to configure row expressions. For simple sharding logic, you can define a row expression directly. (this method is actually to configure the parsing method of fragmentation policy directly in the. yml file.)

Similar to the above code, 6 or 8 lines have been changed here to directly new an InlineShardingStrategyConfiguration, which saves the tedious steps of defining fragmentation algorithm classes.

 1              // .....Omit other codes
 2  
 3             // to configure t_user Table rules
 4             TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds${0..1}.t_user${0..1}");
 5             // Row expression table splitting rule
 6             userRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user${id % 2}"));
 7             // Row expression sub base rule
 8             userRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds${id % 2}"));
 9 
10             // Sharding Global configuration
11             ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
12             shardingRuleConfiguration.getTableRuleConfigs().add(userRuleConfiguration);
13             // create data source
14             DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
15             return dataSource;

5, Distributed primary key (snowflake algorithm)

After the database is partitioned, you can no longer use mysql's auto added primary key, otherwise duplicate primary keys will be generated. There are two main problems to be solved in customizing the primary key:

1. Unique primary key (required)
2. Monotonically increasing primary key (optional) (improve index efficiency and reduce space fragmentation caused by index rearrangement)

There are two primary key generators in Sharding, one is snow flake shardingkeygenerator, and the other is UUID (considering Article 2 above, so UUID is not used).

The main principle of snowflake algorithm: use a 64 bit long number as the primary key. Among them,

The first bit, 1 bit as the sign bit is always 0, which means it is a positive number.

Bit 2 - 42, 41 bit time stamp.

bit 43 - 52, 10 bits to fill the unique id of the machine. For example, the first four digits can be used to identify the machine room number, and the last six digits can be used to identify the machine number.

53 - 64 bit, 12 bits are filled with id sequence number. The range is 0 - 4095, which means that each machine can generate up to 4096 different primary key IDS every 1 millisecond.

The main implementation code of snowflake algorithm is as follows:

1. First determine whether the clock callback. The default tolerance callback time is 0. If there is a callback, an exception will be generated. Can be configured by max.tolerate.time.difference.milliseconds Property to spin and wait for the clock to return to the last execution time.
2. The id sequence number is incremented according to the current milliseconds. If the clock enters the next millisecond, the id sequence number is regenerated from 0.
3. Assemble the time stamp + machine serial number + ID serial number into the primary key ID. Here, the machine serial number is 0 by default. You can use the worker.id Property. Different servers need to be configured with different numbers, ranging from 0 - 1023.

EPOCH is the clock benchmark, and sharding is set on November 1, 2016, so the 41 bit timestamp can be used for almost 70 years, until 2086.

    public synchronized Comparable<?> generateKey() {
        long currentMilliseconds = timeService.getCurrentMillis();
        if (this.waitTolerateTimeDifferenceIfNeed(currentMilliseconds)) {
            currentMilliseconds = timeService.getCurrentMillis();
        }

        if (this.lastMilliseconds == currentMilliseconds) {
            if (0L == (this.sequence = this.sequence + 1L & 4095L)) {
                currentMilliseconds = this.waitUntilNextTime(currentMilliseconds);
            }
        } else {
            this.vibrateSequenceOffset();
            this.sequence = (long)this.sequenceOffset;
        }

        this.lastMilliseconds = currentMilliseconds;
        return currentMilliseconds - EPOCH << 22 | this.getWorkerId() << 12 | this.sequence;
    }

6, Business code

To use the distributed primary key ID generator, you need to inject different ID generators into different tables, and add a KeyIdConfig class under the config package, as follows:

Here, in order to keep the clock uniform, you can find a special machine as the clock service, and then configure the clock service for all the primary key generators.

@Configuration
public class KeyIdConfig {
    @Bean("userKeyGenerator")
    public SnowflakeShardingKeyGenerator userKeyGenerator() {
        return new SnowflakeShardingKeyGenerator();
    }

    @Bean("orderKeyGenerator")
    public SnowflakeShardingKeyGenerator orderKeyGenerator() {
        return new SnowflakeShardingKeyGenerator();
    }
}

Other business codes are as follows:

package com.example.shardingjdbc.entity;

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
public class User implements Serializable {
    private Long id;
    private String name;
    private String phone;
    private String email;
    private String password;
    private Integer cityId;
    private Date createTime;
    private Integer sex;
}

package com.example.shardingjdbc.mapper;

import com.example.shardingjdbc.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

public interface UserMapper {
    /**
     * preservation
     */
    void save(User user);

    /**
     * query
     * @param id
     * @return
     */
    User get(Long id);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.shardingjdbc.mapper.UserMapper">
    <resultMap id="resultMap" type="com.example.shardingjdbc.entity.User">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="phone" property="phone"  />
        <result column="email" property="email"  />
        <result column="password" property="password"  />
        <result column="city_id" property="cityId"  />
        <result column="create_time" property="createTime"  />
        <result column="sex" property="sex"  />
    </resultMap>

    <insert id="save">
        insert into t_user (id, name, phone, email, password, city_id, create_time, sex)
        values (#{id}, #{name}, #{phone}, #{email}, #{password}, #{cityId}, #{createTime}, #{sex})
    </insert>

    <select id="get" resultMap="resultMap">
        select *
        from t_user
        where id = #{id}
    </select>
</mapper>

 1 package com.example.shardingjdbc.controller;
 2 
 3 import com.example.shardingjdbc.entity.User;
 4 import com.example.shardingjdbc.mapper.UserMapper;
 5 import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.stereotype.Controller;
 8 import org.springframework.web.bind.annotation.PathVariable;
 9 import org.springframework.web.bind.annotation.RequestMapping;
10 import org.springframework.web.bind.annotation.ResponseBody;
11 
12 import javax.annotation.Resource;
13 import java.util.Date;
14 
15 @Controller
16 public class UserController {
17     @Autowired
18     private UserMapper userMapper;
19 
20     @Resource
21     SnowflakeShardingKeyGenerator userKeyGenerator;
22 
23     @RequestMapping("/user/save")
24     @ResponseBody
25     public String save() {
26         for (int i = 0; i < 50; i++) {
27             Long id = (Long)userKeyGenerator.generateKey();
28             User user = new User();
29             user.setId(id);
30             user.setName("test" + i);
31             user.setCityId(i);
32             user.setCreateTime(new Date());
33             user.setSex(i % 2 == 0 ? 1 : 2);
34             user.setPhone("11111111" + i);
35             user.setEmail("xxxxx");
36             user.setCreateTime(new Date());
37             user.setPassword("eeeeeeeeeeee");
38             userMapper.save(user);
39         }
40 
41         return "success";
42     }
43 
44     @RequestMapping("/user/get/{id}")
45     @ResponseBody
46     public User get(@PathVariable Long id) {
47         User user = userMapper.get(id);
48         return user;
49     }
50 }

 1 CREATE TABLE `t_user` (
 2   `id` bigint(20) NOT NULL,
 3   `name` varchar(64) DEFAULT NULL COMMENT 'name',
 4   `city_id` int(12) DEFAULT NULL COMMENT 'city',
 5   `sex` tinyint(1) DEFAULT NULL COMMENT 'Gender',
 6   `phone` varchar(32) DEFAULT NULL COMMENT 'Telephone',
 7   `email` varchar(32) DEFAULT NULL COMMENT 'mailbox',
 8   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Creation time',
 9   `password` varchar(32) DEFAULT NULL COMMENT 'password',
10   PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The startup class is as follows:

 1 package com.example.shardingjdbc;
 2 
 3 import org.mybatis.spring.annotation.MapperScan;
 4 import org.springframework.boot.SpringApplication;
 5 import org.springframework.boot.autoconfigure.SpringBootApplication;
 6 
 7 @MapperScan("com.example.shardingjdbc.mapper")
 8 @SpringBootApplication
 9 public class ShardingjdbcApplication {
10     public static void main(String[] args) {
11         SpringApplication.run(ShardingjdbcApplication.class, args);
12     }
13 }

Note that here I add the @ MapperScan annotation to the startup class. Maybe it's because of the problem of reference dependency. The mybatis package configured by. properties scans the directory and doesn't work. I will study it later.

7, Others

In addition to the basic database and table rules, there are other configurations, such as binding tables. Let's not give examples one by one here, just match them with the official documents.

For example: now there is order, order_ Two tables, 1:1 relationship.

When configuring, you should set the same order_ Order record and order of ID_ The detail record is mapped to a table with the same tail number. This makes it easy to connect queries. For example, it's all inserted into order 0, order_ In detail0.

If the binding relationship is configured, only one query will be generated, select * from order0 as o join order_detail0 as d  on o.order_id = d.order_id.

Otherwise, a Cartesian product query will be generated

　　　　select * from order0 as o join order_detail0 as d  on o.order_id = d.order_id.

　　　　select * from order0 as o join order_detail1 as d  on o.order_id = d.order_id.

　　　　select * from order1 as o join order_detail0 as d  on o.order_id = d.order_id.

　　　　select * from order1 as o join order_detail1 as d  on o.order_id = d.order_id.

8, Summary

Before starting the project, create the database Test0 and test1, and then create the tables t respectively_ user0, t_ user1. It can all be on the same machine.

After project launch, visit http://localhost:8080/user/save, even id are inserted into the T of test0 Library_ In the user0 table, odd numbers are inserted into the T in the test1 library_ User1 table.

Druid's background monitoring page address: http://localhost:8080/druid/.

After the project is started, the sharding log will print out the configuration in the form of yml format, or it can save the java configuration and optimize it to the. yml configuration file, as shown in the following figure:

Original address: https://www.cnblogs.com/lyosaki88/p/springboot_shardingjdbc_druid_mybatis.html

Source download address: https://474b.com/file/14960372-448059323

Author QQ: 116269651

Keywords: Java Druid Spring JDBC

Added by htcilt on Thu, 11 Jun 2020 06:47:20 +0300