受群里小伙伴之邀，搞一個(gè)分庫分表案例，這樣讓很多沒用過分庫分表的心里也有個(gè)底，不然永遠(yuǎn)看到的都是網(wǎng)上的各種概念和解決方案性的文章。
需求
由于用戶表過于龐大，采取相關(guān)SQL優(yōu)化，還是不能滿足，所以現(xiàn)對(duì)其進(jì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;
關(guān)于數(shù)據(jù)庫分庫分表通常有兩種方案：
- 垂直拆分
- 水平拆分
下面我們來演示水平拆分，大致思路：
通過t_user表的id進(jìn)行hash，然后再和數(shù)據(jù)庫個(gè)數(shù)進(jìn)行取模，得出對(duì)應(yīng)數(shù)據(jù)庫。
通過hash值和每個(gè)數(shù)據(jù)庫中表的個(gè)數(shù)進(jìn)行取模，得出對(duì)應(yīng)表名。
創(chuàng)建數(shù)據(jù)庫和表
加入有2000萬條數(shù)據(jù)，那么為了方便演示，我們就暫定分為五個(gè)庫，每個(gè)數(shù)據(jù)庫對(duì)應(yīng)五個(gè)表。
理想狀態(tài)：2000萬/5/4，那么每個(gè)數(shù)據(jù)庫分得400萬，每個(gè)表分得80萬。
總之，分庫分表后，我們的每一張表的數(shù)據(jù)庫和表都與之前的確實(shí)不是一個(gè)量級(jí)了。
五個(gè)數(shù)據(jù)庫：
每個(gè)數(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;
項(xiàng)目創(chuàng)建
使用技術(shù)棧：JDK8+MySQL+Spring Boot +Mybatis +Shardingsphere +Druid
maven 相關(guān)依賴：
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##### 連接池配置 ######## 過濾器設(shè)置（第一個(gè)stat很重要，沒有的話會(huì)監(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)計(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)控頁面spring.datasource.druid.stat-view-servlet.enabled=true#內(nèi)置監(jiān)控頁面的地址spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*#關(guān)閉 Reset All 功能spring.datasource.druid.stat-view-servlet.reset-enable=false#設(shè)置登錄用戶名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ù)庫名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配置類似，不一樣的就是數(shù)據(jù)庫名字不一樣# 因?yàn)槲沂褂玫谋镜貏?chuàng)建多個(gè)數(shù)據(jù)庫演示的，這里就沒有必要重復(fù)累贅了#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#設(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作為分庫的標(biāo)準(zhǔn)，這樣到時(shí)候就會(huì)將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
分庫分表的兩個(gè)分片類：
/** * 分庫 */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ù)庫，比如 dp0 //這個(gè)數(shù)據(jù)庫名對(duì)應(yīng)數(shù)據(jù)源處配置的dp0，dp1,… logger.info(“數(shù)據(jù)庫名=” + dbName); databaseName = dbName; } } return databaseName; } private String genderToTableSuffix(Long value) { //將id字段的值去hash值后去模運(yùn)算得到分庫的數(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ì)算出來的表名是否存在 if (tableName.equals(targetName)) { logger.info(“表名= ” + tableName); table = tableName; } } return table; } private String genderToTableSuffix(Long value) { //算出一個(gè)hash值 int類型 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層，這也是大家平常開發(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)類：
@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)成功：
下面我們來演示一下新增數(shù)據(jù)和查詢。
添加數(shù)據(jù)到數(shù)據(jù)庫中
先來添加數(shù)據(jù)到數(shù)據(jù)庫中，這里使用的是IDEA中restful工具：
后臺(tái)日志：
再查看數(shù)據(jù)庫表中：
到此，我們的數(shù)據(jù)依舊落庫，下面我們來演示一下數(shù)據(jù)查詢。
數(shù)據(jù)查詢
瀏覽器里輸入：
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ù)庫和表了，這樣，一個(gè)分庫分表的查詢就成功了。
總結(jié)
本文沒有太多的概念，直接使用案例演示。相關(guān)概念性的文章，還有分庫分表解決方案的文章，網(wǎng)上一堆堆的，感興趣可以自行查閱。
