給小白演示分庫分表案例

大家好，我是老田，受羣裏小夥伴之邀，搞一個分庫分表案例，這樣讓很多沒用過分庫分表的心裏也有個底，不然永遠看到的都是網上的各種概念和解決方案性的文章。

說明：由於是給小白看的，所以大神勿噴，建議出門左轉去學更牛逼的技術。

需求

由於用戶表過於龐大，採取相關 SQL 優化，還是不能滿足，所以現對其進行做分庫分表。

數據庫：my-sharding

數據庫表：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;

關於數據庫分庫分表通常有兩種方案：

垂直拆分
水平拆分

下面我們來演示水平拆分，大致思路：

通過 t_user 表的 id 進行 hash，然後再和數據庫個數進行取模，得出對應數據庫。

通過 hash 值和每個數據庫中表的個數進行取模，得出對應表名。

創建數據庫和表

加入有 2000 萬條數據，那麼爲了方便演示，我們就暫定分爲五個庫，每個數據庫對應五個表。

理想狀態：2000 萬 / 5/4，那麼每個數據庫分得 400 萬，每個表分得 80 萬。

總之，分庫分表後，我們的每一張表的數據庫和表都與之前的確實不是一個量級了。

五個數據庫：

每個數據庫有五張表：

建表語句如下：

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;

項目創建

使用技術棧：JDK8+MySQL+Spring Boot +Mybatis +Shardingsphere +Druid

maven 相關依賴：

<dependency>
    <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
 <groupId>org.mybatis.spring.boot</groupId>
 <artifactId>mybatis-spring-boot-starter</artifactId>
 <version>2.1.0</version>
</dependency>
<dependency>
     <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.2</version>
</dependency>

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.16</version>
  <scope>runtime</scope>
</dependency>
<dependency>
      <groupId>com.github.pagehelper</groupId>
   <artifactId>pagehelper-spring-boot-starter</artifactId>
   <version>1.2.3</version>
</dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
</dependency>
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.0.1</version>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.1.17</version>
</dependency>
<dependency>
   <groupId>com.google.guava</groupId>
   <artifactId>guava</artifactId>
   <version>29.0-jre</version>
</dependency>

配置文件相關配置如下：

server.port=9002
mybatis.mapper-locations=classpath:/mapper/*.xml
# mybatis.type-aliases-package=com.neutral.idmapping.dbshard.pojo

##### 連接池配置 #######
# 過濾器設置（第一個stat很重要，沒有的話會監控不到SQL）
spring.datasource.druid.filters=stat,wall,log4j2
##### WebStatFilter配置 #######
#啓用StatFilter
spring.datasource.druid.web-stat-filter.enabled=true
#添加過濾規則
spring.datasource.druid.web-stat-filter.url-pattern=/*
#排除一些不必要的url
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
#開啓session統計功能
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配置 #######
#啓用內置的監控頁面
spring.datasource.druid.stat-view-servlet.enabled=true
#內置監控頁面的地址
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=admin

spring.shardingsphere.props.sql.show=false
#數據庫名
spring.shardingsphere.datasource.names=dp0,dp1,dp2,dp3,dp4
#datasource
spring.shardingsphere.datasource.dp0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.dp0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.dp0.url=jdbc:mysql://localhost:3306/my-sharding_0?useUnicode=true&characterEncoding=utf-8&serverTimeZone=CTT&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.shardingsphere.datasource.dp0.username=root
spring.shardingsphere.datasource.dp0.password=123456

 ----------相同的代碼部分這裏就不貼了-------
# 對應 dp1、dp2、dp3、dp4 和上面dp0配置類似，不一樣的就是數據庫名字不一樣
# 因爲我使用的本地創建多個數據庫演示的，這裏就沒有必要重複累贅了

#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作爲參數傳入到下面配置的我們自定義的分表方法中做具體操
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.precise-algorithm-class-name=com.tian.shardingdemo.common.TableShardingAlgorithm
#database
#設置了以data中字段id作爲分庫的標準，這樣到時候就會將id作爲參數傳入到下面配置的我們自定義的分庫方法中做具體操作
spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.t_user.database-strategy.standard.precise-algorithm-class-name=com.tian.shardingdemo.common.DbShardingAlgorithm

分庫分表的兩個分片類：

/**
 * 分庫
 */
public class DbShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    private Logger logger = LoggerFactory.getLogger(DbShardingAlgorithm.class);

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String databaseName = availableTargetNames.stream().findFirst().get();
        for (String dbName : availableTargetNames) {
            //shardingValue.getValue()就是配置的傳入的值
            //我們這裏選用的是傳入sql中的id字段的值
            String targetDb + genderToTableSuffix(shardingValue.getValue());
            if (dbName.equals(targetDbName)) {
                //匹配到對應的數據庫，比如 dp0
                //這個數據庫名對應數據源處配置的dp0，dp1,...
                logger.info("數據庫名=" + dbName);
                databaseName = dbName;
            }
        }
        return databaseName;
    }

    private String genderToTableSuffix(Long value) {
        //將id字段的值去hash值後去模運算得到分庫的數字（就是一種算法而已）
        int i = Hashing.murmur3_128(1823977).newHasher().putString(String.valueOf(value), Charsets.UTF_8).hash().asInt();
        //hash與表個數進行取模
        return String.valueOf(Math.abs(i) % 5);
    }
}
/**
 * 分表
 */
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    private Logger logger = LoggerFactory.getLogger(TableShardingAlgorithm.class);

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> 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與表個數進行取模
        return String.valueOf(Math.abs(i) % 5);
    }
}

下面是業務部分代碼，先看UserMapper.xml內容：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tian.shardingdemo.mapper.UserMapper">
    <resultMap id="User" type="com.tian.shardingdemo.entity.User">
        <id column="id" property="id"/>
        <result column="user_name" property="userName"/>
    </resultMap>
    <insert id="insert">
        INSERT INTO t_user (id, user_name,age,gender) VALUES ( #{id},#{userName},#{age},#{gender}
        );
    </insert>
    <select id="selectUserById" resultMap="User">
        select * from t_user
        <where>
            <if test="id != null">
                id = #{id}
            </if>
        </where>
    </select>

    <update id="updateAuthorIfNecessary">
        update t_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="userName != null and userName != ''">
                `user_name` = #{userName},
            </if>
            <if test="gender != null and gender != 0">
                gender = #{gender},
            </if>
            <if test="age != null and age != 0">
                age = #{age},
            </if>
        </trim>
        where id=#{id}
    </update>
</mapper>

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
@Repository
public interface UserMapper {

    User selectUserById(@Param("id") Long id);

    int updateAuthorIfNecessary(User user);

    int insert(User user);
}

爲了更好地演示，我這裏加入了controller層和service層，這也是大家平常開發套路。

service層代碼如下：

public interface IUserService {

    User selectUserById(Long id);

    void add(Long id);
}
@Service
public 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
@RequestMapping
public 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<String,Long> 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);
 }
}

啓動項目，啓動成功：

下面我們來演示一下新增數據和查詢。

添加數據到數據庫中

先來添加數據到數據庫中，這裏使用的是 IDEA 中 restful 工具：

後臺日誌：

再查看數據庫表中：

到此，我們的數據依舊落庫，下面我們來演示一下數據查詢。

數據查詢

瀏覽器裏輸入：

http://localhost:9002/user/7

返回數據：

{"id":7,"userName":"tian7","age":22,"gender":1}

後臺日誌：

從日誌和返回結果可以看出，已經爲我們正確的選擇到對應的數據庫和表了，這樣，一個分庫分表的查詢就成功了。

總結

本文沒有太多的概念，直接使用案例演示。相關概念性的文章，還有分庫分表解決方案的文章，網上一堆堆的，感興趣可以自行查閱。

參考：http://01vh0.cn/mLQwN

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/rE9YNaF_qJkM93wRZezbdw

需求

創建數據庫和表

項目創建

添加數據到數據庫中

數據查詢

總結

猜你喜歡