1.为什么要分库Or分表

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

（1）IO瓶颈

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。

第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

（2）CPU瓶颈

第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。

第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

今天我们着重看一下水平分库分表

2.步骤

我这里选择用一个新的MySQL来演示，首先用Docker启动一个新的MySQL，端口为3308

  1. docker run -p 3308:3306 --name mysql-shardingsphere -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

分库分表是需要手动完成的，我们这里新建数据库ds0、ds1，每个数据库里有user_0、user_1两张表

ds0建表语句

  1. CREATE DATABASE IF NOT EXISTS `ds0`; 
  2. USE `ds0`; 
  3. SET NAMES utf8mb4; 
  4. SET FOREIGN_KEY_CHECKS = 0; 
  6. DROP TABLE IF EXISTS `user_0`; 
  7. CREATE TABLE `user_0` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
  9. DROP TABLE IF EXISTS `user_1`; 
  10. CREATE TABLE `user_1` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  12. DROP TABLE IF EXISTS `user_2`; 
  13. CREATE TABLE `user_1` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
  14. SET FOREIGN_KEY_CHECKS = 1;

ds1建表语句

  1. CREATE DATABASE IF NOT EXISTS `ds1`; 
  2. USE `ds1`; 
  3. SET NAMES utf8mb4; 
  4. SET FOREIGN_KEY_CHECKS = 0; 
  6. DROP TABLE IF EXISTS `user_0`; 
  7. CREATE TABLE `user_0` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
  9. DROP TABLE IF EXISTS `user_1`; 
  10. CREATE TABLE `user_1` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
  12. DROP TABLE IF EXISTS `user_2`; 
  13. CREATE TABLE `user_1` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
  14. SET FOREIGN_KEY_CHECKS = 1;

然后我们新建一个SpringBoot项目，导入依赖

  1. <dependencies>
  2.         <dependency>
  3.             <groupId>org.springframework.boot</groupId>
  4.             <artifactId>spring-boot-starter-web</artifactId>
  5.         </dependency>
  6.         <!--  DB: MyBatis Plus, Druid     -->
  7.         <dependency>
  8.             <groupId>mysql</groupId>
  9.             <artifactId>mysql-connector-java</artifactId>
  10.             <scope>runtime</scope>
  11.         </dependency>
  12.         <dependency>
  13.             <groupId>com.baomidou</groupId>
  14.             <artifactId>mybatis-plus-boot-starter</artifactId>
  15.             <version>3.3.1</version>
  16.         </dependency>
  17.         <dependency>
  18.             <groupId>com.alibaba</groupId>
  19.             <artifactId>druid-spring-boot-starter</artifactId>
  20.             <version>1.1.21</version>
  21.         </dependency>
  22.         <dependency>
  23.             <groupId>org.apache.shardingsphere</groupId>
  24.             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  25.             <version>4.0.0-RC1</version>
  26.         </dependency>
  27.         <dependency>
  28.             <groupId>org.projectlombok</groupId>
  29.             <artifactId>lombok</artifactId>
  30.             <optional>true</optional>
  31.         </dependency>
  32.         <dependency>
  33.             <groupId>org.springframework.boot</groupId>
  34.             <artifactId>spring-boot-starter-test</artifactId>
  35.             <scope>test</scope>
  36.         </dependency>
  37.         <dependency>
  38.             <groupId>junit</groupId>
  39.             <artifactId>junit</artifactId>
  40.             <version>4.13.1</version>
  41.             <scope>test</scope>
  42.         </dependency>
  43.     </dependencies>

然后是User实体类与Mapper

  1. @Data
  2. @TableName("user")
  3. @Accessors(chain = true)
  4. public class User {
  5.     /**
  6.      * 主键Id
  7.      */
  8.     private int id;
  10.     /**
  11.      * 名称
  12.      */
  13.     private String name;
  15.     /**
  16.      * 年龄
  17.      */
  18.     private int age;
  19. }
  1. package com.zym.mapper;
  3. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
  4. import com.zym.entity.User;
  5. import org.apache.ibatis.annotations.Mapper;
  7. @Mapper
  8. public interface UserMapper extends BaseMapper<User> {
  10. }

测试类：

  1. @RunWith(SpringJUnit4ClassRunner.class)
  2. @SpringBootTest
  3. public class TestSharding {
  5.     @Autowired
  6.     private UserMapper userMapper;
  8.     @Test
  9.     public void insertUser(){
  10.         for (int i = 1; i <= 20; i++) {
  11.             User user = new User();
  12.             user.setId(i).setAge(i+10).setName("test"+i);
  13.             userMapper.insert(user);
  14.         }
  15.     }
  17.     @Test
  18.     public void getAllUser(){
  19.         QueryWrapper<User> queryWrapper = new QueryWrapper<>();
  20.         queryWrapper.orderByAsc("id");
  21.         List<User> userList = userMapper.selectList(queryWrapper);
  22.         for (User user : userList) {
  23.             System.out.println(user.toString());
  24.         }
  25.     }
  26. }

配置类：

  1. spring:
  2.   shardingsphere:
  3.     datasource:
  4.       names: ds0,ds1
  5.       ds0:
  6.         type: com.alibaba.druid.pool.DruidDataSource
  7.         driver-class-name: com.mysql.jdbc.Driver
  8.         url: jdbc:mysql://192.168.60.10:3308/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
  9.         username: root
  10.         password: 123456
  11.       ds1:
  12.         type: com.alibaba.druid.pool.DruidDataSource
  13.         driver-class-name: com.mysql.jdbc.Driver
  14.         url: jdbc:mysql://192.168.60.10:3308/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
  15.         username: root
  16.         password: 123456
  17.     sharding:
  18.       tables:
  19.         user:
  20.           actualDataNodes: ds$->{0..1}.user_$->{0..2}
  21.           # 分库策略
  22.           databaseStrategy:
  23.             inline:
  24.               shardingColumn: id
  25.               algorithmExpression: ds${id % 2}
  26.           # 分表策略
  27.           tableStrategy:
  28.             inline:
  29.               shardingColumn: age
  30.               algorithmExpression: user_${age % 3}
  31.     props:
  32.       sql:
  33.         show: true
  35.   main:
  36.     allow-bean-definition-overriding: true

然后我们来进行测试

首先插入100个用户后查看结果：
1.gif
然后我们再执行查询所有用户的方法：
image.png

  1. 2021-08-20 09:35:02.303  INFO 14644 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
  2. 2021-08-20 09:35:02.304  INFO 14644 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name,age  FROM user 
  6.   ORDER BY id ASC
  7. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=SELECT  id,name,age  FROM user 
  11.   ORDER BY id ASC)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=18, groupByLastIndex=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=name, alias=Optional.absent()), CommonSelectItem(expression=age, alias=Optional.absent())], groupByItems=[], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(id), orderDirection=ASC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
  12. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,name,age  FROM user_0 
  16.   ORDER BY id ASC
  17. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,name,age  FROM user_1 
  21.   ORDER BY id ASC
  22. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,name,age  FROM user_2 
  26.   ORDER BY id ASC
  27. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,name,age  FROM user_0 
  31.   ORDER BY id ASC
  32. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,name,age  FROM user_1 
  36.   ORDER BY id ASC
  37. 2021-08-20 09:35:02.305  INFO 14644 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,name,age  FROM user_2 
  41.   ORDER BY id ASC
  42. User(id=1, name=test1, age=1)
  43. User(id=2, name=test2, age=2)
  44. User(id=3, name=test3, age=3)
  45. User(id=4, name=test4, age=4)
  46. User(id=5, name=test5, age=5)
  47. User(id=6, name=test6, age=6)
  48. User(id=7, name=test7, age=7)
  49. User(id=8, name=test8, age=8)
  50. User(id=9, name=test9, age=9)
  51. User(id=10, name=test10, age=10)
  52. User(id=11, name=test11, age=11)
  53. User(id=12, name=test12, age=12)
  54. User(id=13, name=test13, age=13)
  55. User(id=14, name=test14, age=14)
  56. User(id=15, name=test15, age=15)
  57. User(id=16, name=test16, age=16)
  58. User(id=17, name=test17, age=17)
  59. User(id=18, name=test18, age=18)
  60. User(id=19, name=test19, age=19)
  61. User(id=20, name=test20, age=20)
  62. User(id=21, name=test21, age=21)
  63. User(id=22, name=test22, age=22)
  64. User(id=23, name=test23, age=23)
  65. User(id=24, name=test24, age=24)
  66. User(id=25, name=test25, age=25)
  67. User(id=26, name=test26, age=26)
  68. User(id=27, name=test27, age=27)
  69. User(id=28, name=test28, age=28)
  70. User(id=29, name=test29, age=29)
  71. User(id=30, name=test30, age=30)
  72. User(id=31, name=test31, age=31)
  73. User(id=32, name=test32, age=32)
  74. User(id=33, name=test33, age=33)
  75. User(id=34, name=test34, age=34)
  76. User(id=35, name=test35, age=35)
  77. User(id=36, name=test36, age=36)
  78. User(id=37, name=test37, age=37)
  79. User(id=38, name=test38, age=38)
  80. User(id=39, name=test39, age=39)
  81. User(id=40, name=test40, age=40)
  82. User(id=41, name=test41, age=41)
  83. User(id=42, name=test42, age=42)
  84. User(id=43, name=test43, age=43)
  85. User(id=44, name=test44, age=44)
  86. User(id=45, name=test45, age=45)
  87. User(id=46, name=test46, age=46)
  88. User(id=47, name=test47, age=47)
  89. User(id=48, name=test48, age=48)
  90. User(id=49, name=test49, age=49)
  91. User(id=50, name=test50, age=50)
  92. User(id=51, name=test51, age=51)
  93. User(id=52, name=test52, age=52)
  94. User(id=53, name=test53, age=53)
  95. User(id=54, name=test54, age=54)
  96. User(id=55, name=test55, age=55)
  97. User(id=56, name=test56, age=56)
  98. User(id=57, name=test57, age=57)
  99. User(id=58, name=test58, age=58)
  100. User(id=59, name=test59, age=59)
  101. User(id=60, name=test60, age=60)
  102. User(id=61, name=test61, age=61)
  103. User(id=62, name=test62, age=62)
  104. User(id=63, name=test63, age=63)
  105. User(id=64, name=test64, age=64)
  106. User(id=65, name=test65, age=65)
  107. User(id=66, name=test66, age=66)
  108. User(id=67, name=test67, age=67)
  109. User(id=68, name=test68, age=68)
  110. User(id=69, name=test69, age=69)
  111. User(id=70, name=test70, age=70)
  112. User(id=71, name=test71, age=71)
  113. User(id=72, name=test72, age=72)
  114. User(id=73, name=test73, age=73)
  115. User(id=74, name=test74, age=74)
  116. User(id=75, name=test75, age=75)
  117. User(id=76, name=test76, age=76)
  118. User(id=77, name=test77, age=77)
  119. User(id=78, name=test78, age=78)
  120. User(id=79, name=test79, age=79)
  121. User(id=80, name=test80, age=80)
  122. User(id=81, name=test81, age=81)
  123. User(id=82, name=test82, age=82)
  124. User(id=83, name=test83, age=83)
  125. User(id=84, name=test84, age=84)
  126. User(id=85, name=test85, age=85)
  127. User(id=86, name=test86, age=86)
  128. User(id=87, name=test87, age=87)
  129. User(id=88, name=test88, age=88)
  130. User(id=89, name=test89, age=89)
  131. User(id=90, name=test90, age=90)
  132. User(id=91, name=test91, age=91)
  133. User(id=92, name=test92, age=92)
  134. User(id=93, name=test93, age=93)
  135. User(id=94, name=test94, age=94)
  136. User(id=95, name=test95, age=95)
  137. User(id=96, name=test96, age=96)
  138. User(id=97, name=test97, age=97)
  139. User(id=98, name=test98, age=98)
  140. User(id=99, name=test99, age=99)
  141. User(id=100, name=test100, age=100)

根据控制台可以看到，即使是分库分表了，加入查询条件依然可以查询出来，代码零侵入~

这样一个简单的分库分表就实现了，当然也可以搭配读写分离一起使用

源码地址：https://gitee.com/zym213/sharding-sphere-demo-ff