使用sharding-jdbc實現水平分表的示例代碼
在mysql中新建數據庫sharding_db,新增兩張結構一樣的表student_1和student_2。
CREATE TABLE `student_1` ( `ID` bigint(20) NOT NULL , `NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL , `AGE` int(11) NOT NULL , `GENDER` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL , PRIMARY KEY (`ID`) );
此處未指定主鍵自增,因為兩張表的id不能重復,所以隻能從後端傳入id。
添加依賴
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- Druid連接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.20</version> </dependency> <!-- Mysql驅動依賴 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- MybatisPlus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.5</version> </dependency> <!-- Sharding-JDBC --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency>
編寫配置文件
spring.main.allow-bean-definition-overriding=true # 配置Sharding-JDBC的分片策略 # 配置數據源,給數據源起名g1,g2...此處可配置多數據源 spring.shardingsphere.datasource.names=g1 # 配置數據源具體內容:連接池,驅動,地址,用戶名,密碼 # 由於上面配置數據源隻有g1因此下面隻配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.g1.url=jdbc:mysql://localhost:3306/sharding_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC spring.shardingsphere.datasource.g1.username=root spring.shardingsphere.datasource.g1.password=123456 # 配置表的分佈,表的策略 spring.shardingsphere.sharding.tables.student.actual-data-nodes=g1.student_$->{1..2} # 指定student表 主鍵gid 生成策略為 SNOWFLAKE spring.shardingsphere.sharding.tables.student.key-generator.column=id spring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE # 指定分片策略 約定id值是偶數添加到student_1表,如果id是奇數添加到student_2表 spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression=student_$->{id % 2 + 1} # 打開sql輸出日志 spring.shardingsphere.props.sql.show=true
或者是yml格式
spring: main: allow-bean-definition-overriding: true shardingsphere: datasource: g1: driver-class-name: com.mysql.cj.jdbc.Driver password: 123456 type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/sharding_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC username: root names: g1 props: sql: show: true sharding: tables: student: actual-data-nodes: g1.student_$->{1..2} key-generator: column: id type: SNOWFLAKE table-strategy: inline: algorithm-expression: student_$->{id % 2 + 1} sharding-column: id
編寫實體類
@Data public class Student { private Long id; private String name; private int age; private String gender; }
編寫mapper接口
@Repository public interface StudentMapper extends BaseMapper<Student> { }
編寫測試類
@SpringBootTest class ShardingJdbcDemoApplicationTests { @Autowired private StudentMapper studentMapper; @Test public void test01() { for (int i = 0; i < 10; i++) { Student student = new Student(); student.setName("wuwl"); student.setAge(27); student.setGender("男"); studentMapper.insert(student); } } }
執行測試
執行成功,主鍵通過雪花算法在後端生成,傳入到數據庫中,根據奇偶性進行分表。
student_1表數據:
student_2表數據:
兩張表的數據分別有5條,但這隻是因為雪花算法生成的id奇數偶數各5個,不是1:1的關系,需要註意。
主鍵生成後,根據策略插入到對應的表中,從打印出來的sql可以證明這一點。
通過mapper接口的selectById方法進行查詢時,會先根據主鍵策略判斷在哪個庫,再直接去那個庫根據主鍵查詢。而如果是通過其它條件查詢,或者是多個id的selectById方法查詢,又是如何的呢?
@Test public void test03() { List<Long> list = new ArrayList<>(); list.add(1362282042768609282l); list.add(1362282040277192705l); List<Student> studentList = studentMapper.selectBatchIds(list); System.out.println(studentList); }
取瞭兩張表的id進行查詢。
執行同樣的sql,在兩張表中都查詢一遍,再組合結果。
如果所有的id,都來自同一張表,那是否會去多個表中重復查詢呢?
隻執行瞭一遍。所以,在執行查詢時,sharding會先判斷是否可以確定需要的數據來自那張表,如果能,則直接去那一張表中查詢數據即可,而如果不能確定,則會多個表重復查詢,以確定查詢結果的完整性。
到此這篇關於使用sharding-jdbc實現水平分表的示例代碼的文章就介紹到這瞭,更多相關sharding-jdbc 水平分表內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 使用sharding-jdbc實現水平分庫+水平分表的示例代碼
- springboot 整合fluent mybatis的過程,看這篇夠瞭
- 利用Sharding-Jdbc進行分庫分表的操作代碼
- SpringBoot整合Sharding-JDBC實現MySQL8讀寫分離
- Easycode自動化springboot的curd