受群里小伙伴之邀，搞一個分庫分表案例，這樣讓很多沒用過分庫分表的心里也有個底，不然永遠看到的都是網上的各種概念和解決方案性的文章。
需求
由于用戶表過于龐大，采取相關SQL優(yōu)化，還是不能滿足，所以現(xiàn)對其進行做分庫分表。
數(shù)據(jù)庫：my-sharding
數(shù)據(jù)庫表：t_user
建表語句如下：
DROP TABLE IF EXISTS `t_user`;CREATE TABLE `t_user` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int NOT NULL, `gender` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
關于數(shù)據(jù)庫分庫分表通常有兩種方案：
- 垂直拆分
- 水平拆分
下面我們來演示水平拆分，大致思路：
通過t_user表的id進行hash，然后再和數(shù)據(jù)庫個數(shù)進行取模，得出對應數(shù)據(jù)庫。
通過hash值和每個數(shù)據(jù)庫中表的個數(shù)進行取模，得出對應表名。
創(chuàng)建數(shù)據(jù)庫和表
加入有2000萬條數(shù)據(jù)，那么為了方便演示，我們就暫定分為五個庫，每個數(shù)據(jù)庫對應五個表。
理想狀態(tài)：2000萬/5/4，那么每個數(shù)據(jù)庫分得400萬，每個表分得80萬。
總之，分庫分表后，我們的每一張表的數(shù)據(jù)庫和表都與之前的確實不是一個量級了。
五個數(shù)據(jù)庫：
每個數(shù)據(jù)庫有五張表：
建表語句如下：
DROP TABLE IF EXISTS `t_user_0`;CREATE TABLE `t_user_0` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int NOT NULL, `gender` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `t_user_1`;CREATE TABLE `t_user_1` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int NOT NULL, `gender` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `t_user_2`;CREATE TABLE `t_user_2` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int NOT NULL, `gender` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `t_user_3`;CREATE TABLE `t_user_3` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int NOT NULL, `gender` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `t_user_4`;CREATE TABLE `t_user_4` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int NOT NULL, `gender` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
項目創(chuàng)建
使用技術棧：JDK8+MySQL+Spring Boot +Mybatis +Shardingsphere +Druid
maven 相關依賴：
org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.0 org.mybatis mybatis 3.5.2 mysql mysql-connector-java 8.0.16 runtime com.github.pagehelper pagehelper-spring-boot-starter 1.2.3 org.springframework.boot spring-boot-starter-test test org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.0.1 com.alibaba druid 1.1.17 com.google.guava guava 29.0-jre
配置文件相關配置如下：
server.port=9002mybatis.mapper-locations=classpath:/mapper/*.xml# mybatis.type-aliases-package=com.neutral.idmapping.dbshard.pojo##### 連接池配置 ######## 過濾器設置（第一個stat很重要，沒有的話會監(jiān)控不到SQL）spring.datasource.druid.filters=stat,wall,log4j2##### WebStatFilter配置 ########啟用StatFilterspring.datasource.druid.web-stat-filter.enabled=true#添加過濾規(guī)則spring.datasource.druid.web-stat-filter.url-pattern=/*#排除一些不必要的urlspring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*#開啟session統(tǒng)計功能spring.datasource.druid.web-stat-filter.session-stat-enable=true#缺省sessionStatMaxCount是1000個spring.datasource.druid.web-stat-filter.session-stat-max-count=1000#spring.datasource.druid.web-stat-filter.principal-session-name=#spring.datasource.druid.web-stat-filter.principal-cookie-name=#spring.datasource.druid.web-stat-filter.profile-enable=##### StatViewServlet配置 ########啟用內置的監(jiān)控頁面spring.datasource.druid.stat-view-servlet.enabled=true#內置監(jiān)控頁面的地址spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*#關閉 Reset All 功能spring.datasource.druid.stat-view-servlet.reset-enable=false#設置登錄用戶名spring.datasource.druid.stat-view-servlet.login-username=admin#設置登錄密碼spring.datasource.druid.stat-view-servlet.login-password=adminspring.shardingsphere.props.sql.show=false#數(shù)據(jù)庫名spring.shardingsphere.datasource.names=dp0,dp1,dp2,dp3,dp4#datasourcespring.shardingsphere.datasource.dp0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.dp0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.dp0.url=jdbc:mysql://localhost:3306/my-sharding_0?useUnicode=true&characterEncoding=utf-8&serverTimeZone=CTT&allowPublicKeyRetrieval=true&serverTimezone=UTCspring.shardingsphere.datasource.dp0.username=rootspring.shardingsphere.datasource.dp0.password=123456 ———-相同的代碼部分這里就不貼了——-# 對應 dp1、dp2、dp3、dp4 和上面dp0配置類似，不一樣的就是數(shù)據(jù)庫名字不一樣# 因為我使用的本地創(chuàng)建多個數(shù)據(jù)庫演示的，這里就沒有必要重復累贅了#actual-data-nodes#這里是配置所有的 庫.表 的集合#比如我這里配置的意思是 dp0.data_0 , dp0.data_1 ,dp0.data_2 , …#此縮寫方式使用了shardingsphere 官方推薦的語法#t_user 邏輯表名 在UserMapper.xml中使用spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=dp$->{0..4}.t_user_$->{0..4}#table#設置了以data中字段id作為分表的標準，這樣到時候就會將id作為參數(shù)傳入到下面配置的我們自定義的分表方法中做具體操spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.sharding-column=idspring.shardingsphere.sharding.tables.t_user.table-strategy.standard.precise-algorithm-class-name=com.tian.shardingdemo.common.TableShardingAlgorithm#database#設置了以data中字段id作為分庫的標準，這樣到時候就會將id作為參數(shù)傳入到下面配置的我們自定義的分庫方法中做具體操作spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.sharding-column=idspring.shardingsphere.sharding.tables.t_user.database-strategy.standard.precise-algorithm-class-name=com.tian.shardingdemo.common.DbShardingAlgorithm
分庫分表的兩個分片類：
/** * 分庫 */public class DbShardingAlgorithm implements PreciseShardingAlgorithm { private Logger logger = LoggerFactory.getLogger(DbShardingAlgorithm.class); @Override public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) { String databaseName = availableTargetNames.stream().findFirst().get(); for (String dbName : availableTargetNames) { //shardingValue.getValue()就是配置的傳入的值 //我們這里選用的是傳入sql中的id字段的值 String targetDbName= “dp” + genderToTableSuffix(shardingValue.getValue()); if (dbName.equals(targetDbName)) { //匹配到對應的數(shù)據(jù)庫，比如 dp0 //這個數(shù)據(jù)庫名對應數(shù)據(jù)源處配置的dp0，dp1,… logger.info(“數(shù)據(jù)庫名=” + dbName); databaseName = dbName; } } return databaseName; } private String genderToTableSuffix(Long value) { //將id字段的值去hash值后去模運算得到分庫的數(shù)字（就是一種算法而已） int i = Hashing.murmur3_128(1823977).newHasher().putString(String.valueOf(value), Charsets.UTF_8).hash().asInt(); //hash與表個數(shù)進行取模 return String.valueOf(Math.abs(i) % 5); }}/** * 分表 */public class TableShardingAlgorithm implements PreciseShardingAlgorithm { private Logger logger = LoggerFactory.getLogger(TableShardingAlgorithm.class); @Override public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) { String table = availableTargetNames.stream().findFirst().get(); String targetName = “t_user_” + genderToTableSuffix(shardingValue.getValue()); for (String tableName : availableTargetNames) { //檢查計算出來的表名是否存在 if (tableName.equals(targetName)) { logger.info(“表名= ” + tableName); table = tableName; } } return table; } private String genderToTableSuffix(Long value) { //算出一個hash值 int類型 int i = Hashing.murmur3_128(8947189).newHasher().putString(String.valueOf(value), Charsets.UTF_8).hash().asInt(); //hash與表個數(shù)進行取模 return String.valueOf(Math.abs(i) % 5); }}
下面是業(yè)務部分代碼，先看UserMapper.xml內容：
INSERT INTO t_user (id, user_name,age,gender) VALUES ( #{id},#{userName},#{age},#{gender} ); select * from t_user id = #{id} update t_user `user_name` = #{userName}, gender = #{gender}, age = #{age}, where id=#{id}
UserMapper接口：
import com.tian.shardingdemo.entity.User;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import org.springframework.stereotype.Repository;@Mapper@Repositorypublic interface UserMapper { User selectUserById(@Param(“id”) Long id); int updateAuthorIfNecessary(User user); int insert(User user);}
為了更好地演示，我這里加入了controller層和service層，這也是大家平常開發(fā)套路。
service層代碼如下：
public interface IUserService { User selectUserById(Long id); void add(Long id);}@Servicepublic class UserServiceImpl implements IUserService { @Resource private UserMapper userMapper; @Override public User selectUserById(Long id) { return userMapper.selectUserById(id); } @Override public void add(Long id) { User user = new User(); user.setAge(22); user.setGender(1); user.setId(id); user.setUserName(“tian” + id); userMapper.insert(user); }}
controller層代碼如下：
@RestController@RequestMappingpublic class UserController { @Resource private IUserService userService; @RequestMapping(value = “/user/{id}”, method = RequestMethod.GET) public User selectUserById(@PathVariable(“id”) Long id) { return userService.selectUserById(id); } @PostMapping(“/add”) public Object add(@RequestBody Map params) { Long id = params.get(“id”); userService.add(id); return “ok”; }}
最后是項目的啟動類：
@SpringBootApplication@MapperScan({“com.tian.shardingdemo.mapper”})public class ShardingDemoApplication { public static void main(String[] args) { SpringApplication.run(ShardingDemoApplication.class, args); }}
啟動項目，啟動成功：
下面我們來演示一下新增數(shù)據(jù)和查詢。
添加數(shù)據(jù)到數(shù)據(jù)庫中
先來添加數(shù)據(jù)到數(shù)據(jù)庫中，這里使用的是IDEA中restful工具：
后臺日志：
再查看數(shù)據(jù)庫表中：
到此，我們的數(shù)據(jù)依舊落庫，下面我們來演示一下數(shù)據(jù)查詢。
數(shù)據(jù)查詢
瀏覽器里輸入：
http://localhost:9002/user/7
返回數(shù)據(jù)：
{“id”:7,”userName”:”tian7″,”age”:22,”gender”:1}
后臺日志：
從日志和返回結果可以看出，已經為我們正確的選擇到對應的數(shù)據(jù)庫和表了，這樣，一個分庫分表的查詢就成功了。
總結
本文沒有太多的概念，直接使用案例演示。相關概念性的文章，還有分庫分表解決方案的文章，網上一堆堆的，感興趣可以自行查閱。
