受群里小伙伴之邀，搞一個(gè)分庫(kù)分表案例，這樣讓很多沒(méi)用過(guò)分庫(kù)分表的心里也有個(gè)底，不然永遠(yuǎn)看到的都是網(wǎng)上的各種概念和解決方案性的文章。

需求

由于用戶(hù)表過(guò)于龐大，采取相關(guān)SQL優(yōu)化，還是不能滿(mǎn)足，所以現(xiàn)對(duì)其進(jìn)行做分庫(kù)分表。

數(shù)據(jù)庫(kù)：my-sharding

數(shù)據(jù)庫(kù)表：t_user

建表語(yǔ)句如下：

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;

關(guān)于數(shù)據(jù)庫(kù)分庫(kù)分表通常有兩種方案：

• 垂直拆分
• 水平拆分

下面我們來(lái)演示水平拆分，大致思路：

通過(guò)t_user表的id進(jìn)行hash，然后再和數(shù)據(jù)庫(kù)個(gè)數(shù)進(jìn)行取模，得出對(duì)應(yīng)數(shù)據(jù)庫(kù)。

通過(guò)hash值和每個(gè)數(shù)據(jù)庫(kù)中表的個(gè)數(shù)進(jìn)行取模，得出對(duì)應(yīng)表名。

創(chuàng)建數(shù)據(jù)庫(kù)和表

加入有2000萬(wàn)條數(shù)據(jù)，那么為了方便演示，我們就暫定分為五個(gè)庫(kù)，每個(gè)數(shù)據(jù)庫(kù)對(duì)應(yīng)五個(gè)表。

理想狀態(tài)：2000萬(wàn)/5/4，那么每個(gè)數(shù)據(jù)庫(kù)分得400萬(wàn)，每個(gè)表分得80萬(wàn)。

總之，分庫(kù)分表后，我們的每一張表的數(shù)據(jù)庫(kù)和表都與之前的確實(shí)不是一個(gè)量級(jí)了。

五個(gè)數(shù)據(jù)庫(kù)：

每個(gè)數(shù)據(jù)庫(kù)有五張表：

建表語(yǔ)句如下：

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;

項(xiàng)目創(chuàng)建

使用技術(shù)棧：JDK8+MySQL+Spring Boot +Mybatis +Shardingsphere +Druid

maven 相關(guān)依賴(lài)：

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

配置文件相關(guān)配置如下：

server.port=9002mybatis.mapper-locations=classpath:/mapper/*.xml# mybatis.type-aliases-package=com.neutral.idmapping.dbshard.pojo##### 連接池配置 ######## 過(guò)濾器設(shè)置（第一個(gè)stat很重要，沒(méi)有的話(huà)會(huì)監(jiān)控不到SQL）spring.datasource.druid.filters=stat,wall,log4j2##### WebStatFilter配置 ########啟用StatFilterspring.datasource.druid.web-stat-filter.enabled=true#添加過(guò)濾規(guī)則spring.datasource.druid.web-stat-filter.url-pattern=/*#排除一些不必要的urlspring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*#開(kāi)啟session統(tǒng)計(jì)功能spring.datasource.druid.web-stat-filter.session-stat-enable=true#缺省sessionStatMaxCount是1000個(gè)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配置 ########啟用內(nèi)置的監(jiān)控頁(yè)面spring.datasource.druid.stat-view-servlet.enabled=true#內(nèi)置監(jiān)控頁(yè)面的地址spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*#關(guān)閉 Reset All 功能spring.datasource.druid.stat-view-servlet.reset-enable=false#設(shè)置登錄用戶(hù)名spring.datasource.druid.stat-view-servlet.login-username=admin#設(shè)置登錄密碼spring.datasource.druid.stat-view-servlet.login-password=adminspring.shardingsphere.props.sql.show=false#數(shù)據(jù)庫(kù)名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 ———-相同的代碼部分這里就不貼了——-# 對(duì)應(yīng) dp1、dp2、dp3、dp4 和上面dp0配置類(lèi)似，不一樣的就是數(shù)據(jù)庫(kù)名字不一樣# 因?yàn)槲沂褂玫谋镜貏?chuàng)建多個(gè)數(shù)據(jù)庫(kù)演示的，這里就沒(méi)有必要重復(fù)累贅了#actual-data-nodes#這里是配置所有的 庫(kù).表 的集合#比如我這里配置的意思是 dp0.data_0 , dp0.data_1 ,dp0.data_2 , …#此縮寫(xiě)方式使用了shardingsphere 官方推薦的語(yǔ)法#t_user 邏輯表名 在UserMapper.xml中使用spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=dp$->{0..4}.t_user_$->{0..4}#table#設(shè)置了以data中字段id作為分表的標(biāo)準(zhǔn)，這樣到時(shí)候就會(huì)將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#設(shè)置了以data中字段id作為分庫(kù)的標(biāo)準(zhǔn)，這樣到時(shí)候就會(huì)將id作為參數(shù)傳入到下面配置的我們自定義的分庫(kù)方法中做具體操作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

分庫(kù)分表的兩個(gè)分片類(lèi)：

/** * 分庫(kù) */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)) { //匹配到對(duì)應(yīng)的數(shù)據(jù)庫(kù)，比如 dp0 //這個(gè)數(shù)據(jù)庫(kù)名對(duì)應(yīng)數(shù)據(jù)源處配置的dp0，dp1,… logger.info(“數(shù)據(jù)庫(kù)名=” + dbName); databaseName = dbName; } } return databaseName; } private String genderToTableSuffix(Long value) { //將id字段的值去hash值后去模運(yùn)算得到分庫(kù)的數(shù)字（就是一種算法而已） int i = Hashing.murmur3_128(1823977).newHasher().putString(String.valueOf(value), Charsets.UTF_8).hash().asInt(); //hash與表個(gè)數(shù)進(jìn)行取模 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) { //檢查計(jì)算出來(lái)的表名是否存在 if (tableName.equals(targetName)) { logger.info(“表名= ” + tableName); table = tableName; } } return table; } private String genderToTableSuffix(Long value) { //算出一個(gè)hash值 int類(lèi)型 int i = Hashing.murmur3_128(8947189).newHasher().putString(String.valueOf(value), Charsets.UTF_8).hash().asInt(); //hash與表個(gè)數(shù)進(jìn)行取模 return String.valueOf(Math.abs(i) % 5); }}

下面是業(yè)務(wù)部分代碼，先看UserMapper.xml內(nèi)容：

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層，這也是大家平常開(kāi)發(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”; }}

最后是項(xiàng)目的啟動(dòng)類(lèi)：

@SpringBootApplication@MapperScan({“com.tian.shardingdemo.mapper”})public class ShardingDemoApplication { public static void main(String[] args) { SpringApplication.run(ShardingDemoApplication.class, args); }}

啟動(dòng)項(xiàng)目，啟動(dòng)成功：

下面我們來(lái)演示一下新增數(shù)據(jù)和查詢(xún)。

添加數(shù)據(jù)到數(shù)據(jù)庫(kù)中

先來(lái)添加數(shù)據(jù)到數(shù)據(jù)庫(kù)中，這里使用的是IDEA中restful工具：

后臺(tái)日志：

再查看數(shù)據(jù)庫(kù)表中：

到此，我們的數(shù)據(jù)依舊落庫(kù)，下面我們來(lái)演示一下數(shù)據(jù)查詢(xún)。

數(shù)據(jù)查詢(xún)

瀏覽器里輸入：

http://localhost:9002/user/7

返回?cái)?shù)據(jù)：

{“id”:7,”userName”:”tian7″,”age”:22,”gender”:1}

后臺(tái)日志：

從日志和返回結(jié)果可以看出，已經(jīng)為我們正確的選擇到對(duì)應(yīng)的數(shù)據(jù)庫(kù)和表了，這樣，一個(gè)分庫(kù)分表的查詢(xún)就成功了。

總結(jié)

本文沒(méi)有太多的概念，直接使用案例演示。相關(guān)概念性的文章，還有分庫(kù)分表解決方案的文章，網(wǎng)上一堆堆的，感興趣可以自行查閱。