你沒見過的分庫分表原理決議和解決方案(二)

高并發三駕馬車:分庫分表、MQ、快取，今天給大家帶來的就是分庫分表的干貨解決方案,哪怕你不用我的框架也可以從中聽到不一樣的結局方案和實作，

一款支持自動分表分庫的orm框架easy-query 幫助您解脫跨庫帶來的復雜業務代碼,并且提供多種結局方案和自定義路由來實作比中間件更高性能的資料庫訪問，

• GITHUB github地址 https://github.com/xuejmnet/easy-query

• GITEE gitee地址 https://gitee.com/xuejm/easy-query

上篇文章簡單的帶大家了解了分表分庫的原理和聚合決議,但是還留了兩個坑一個是分組如何實作一個是分頁如何實作

介紹

分庫分表的難題一直不是如何插入一直都是如何實作聚合查詢,讓用戶無感知的使用才是分庫分表的最終形態,所以資料坐落和資料聚合將是分庫分表的重中之重,隨著版本迭代easy-query正式發布了1.0.0版本相對的api基本已經穩定,分庫分表和之前稍微有點不一樣但是大部分都是一樣的,那么這次我們將使用1.0.6來實作分庫分表下的資料分組和分頁，

• 資料準備

• 分組聚合
  　　- 記憶體分組聚合
  　　- 流式分組聚合

• 分頁聚合
  　　- 記憶體分頁
  　　- 流式分頁
  　　- 反排分頁
  　　- 順序分頁
  　　- 指定分頁

資料準備

本次我們以訂單為例,然后以訂單創建時間進行按年分庫,按月分表,最后來實作上述的分組和分頁的功能

默認配置項

添加依賴

        <!--druid依賴-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.15</version>
        </dependency>
        <!-- mysql驅動 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>
        <dependency>
            <groupId>com.easy-query</groupId>
            <artifactId>sql-processor</artifactId>
            <version>1.1.7</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.easy-query</groupId>
            <artifactId>sql-springboot-starter</artifactId>
            <version>1.1.7</version>
            <scope>compile</scope>
        </dependency>

添加組態檔

server:
  port: 8081

spring:
  profiles:
    active: dev

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/sharding-order?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
    username: root
    password: root
    druid:
      initial-size: 10
      max-active: 100


easy-query:
  enable: true
  name-conversion: underlined
  database: mysql
  default-data-source-merge-pool-size: 60
  default-data-source-name: ds0

新建一個訂單QOrderEntity按季度進行分表分庫

//分片表
@Data
@Table(value = "https://www.cnblogs.com/xuejiaming/archive/2023/06/30/t_order", shardingInitializer = OrderInitializer.class)
@EntityProxy
public class OrderEntity {
    @Column(primaryKey = true)
    private String id;
    private Integer orderNo;
    private String userId;
    @ShardingTableKey
    @ShardingDataSourceKey
    private LocalDateTime createTime;
}


//分片初始化器
@Component
public class OrderInitializer extends AbstractShardingMonthInitializer<OrderEntity> {
    /**
     * 分片起始時間
     * @return
     */
    @Override
    protected LocalDateTime getBeginTime() {
        return LocalDateTime.of(2020,1,1,0,0,0);
    }

    /**
     * 格式化時間到資料源
     * @param time
     * @param defaultDataSource
     * @return
     */
    @Override
    protected String formatDataSource(LocalDateTime time, String defaultDataSource) {
        String year = DateTimeFormatter.ofPattern("yyyy").format(time);
        int i = Integer.parseInt(year)-2020;
        
        return "ds"+i;
    }
    @Override
    public void configure0(ShardingEntityBuilder<OrderEntity> builder) {

    }
}

//動態添加spring 啟動后的動態資料源額外的ds1、ds2、ds3

@Component
public class ShardingInitRunner implements ApplicationRunner {
    @Autowired
    private EasyQuery easyQuery;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        Map<String, DataSource> dataSources = createDataSources();
        DataSourceManager dataSourceManager = easyQuery.getRuntimeContext().getDataSourceManager();
        for (Map.Entry<String, DataSource> stringDataSourceEntry : dataSources.entrySet()) {

            dataSourceManager.addDataSource(stringDataSourceEntry.getKey(), stringDataSourceEntry.getValue(), 60);
        }
        System.out.println("初始化完成");
    }

    private Map<String, DataSource> createDataSources() {
        HashMap<String, DataSource> stringDataSourceHashMap = new HashMap<>();
        for (int i = 1; i < 4; i++) {
            DataSource dataSource = createDataSource("ds" + i, "jdbc:mysql://127.0.0.1:3306/sharding-order" + i + "?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true", "root", "root");
            stringDataSourceHashMap.put("ds" + i, dataSource);
        }
        return stringDataSourceHashMap;
    }

    private DataSource createDataSource(String dsName, String url, String username, String password) {

        // 設定properties
        Properties properties = new Properties();
        properties.setProperty("name", dsName);
        properties.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
        properties.setProperty("url", url);
        properties.setProperty("username", username);
        properties.setProperty("password", password);
        properties.setProperty("initialSize", "10");
        properties.setProperty("maxActive", "100");
        try {
            return DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new EasyQueryException(e);
        }
    }
}

//新建分庫路由

@Component
public class OrderDataSourceRoute extends AbstractDataSourceRoute<OrderEntity> {
    protected Integer formatShardingValue(LocalDateTime time) {
        String year = time.format(DateTimeFormatter.ofPattern("yyyy"));
        return Integer.parseInt(year);
    }
    public boolean lessThanTimeStart(LocalDateTime shardingValue) {
        LocalDateTime timeYearFirstDay = EasyUtil.getYearStart(shardingValue);
        return shardingValue.isEqual(timeYearFirstDay);
    }

    protected Comparator<String> getDataSourceComparator(){
        return IgnoreCaseStringComparator.DEFAULT;
    }
    @Override
    protected RouteFunction<String> getRouteFilter(TableAvailable table, Object shardingValue, ShardingOperatorEnum shardingOperator, boolean withEntity) {
        //將分片鍵轉成對應的型別
        LocalDateTime shardingTime = (LocalDateTime)shardingValue ;
        Integer intYear = formatShardingValue(shardingTime);
        String dataSourceName="ds"+String.valueOf((intYear-2020));//ds0 ds1 ds2 ds3....
        switch (shardingOperator) {
            case GREATER_THAN:
            case GREATER_THAN_OR_EQUAL:
                return ds -> getDataSourceComparator().compare(dataSourceName, ds) <= 0;
            case LESS_THAN: {
                //如果小于月初那么月初的表是不需要被查詢的 如果小于年初也不需要查詢
                if (lessThanTimeStart(shardingTime)) {
                    return ds -> getDataSourceComparator().compare(dataSourceName, ds) > 0;
                }
                return ds -> getDataSourceComparator().compare(dataSourceName, ds) >= 0;
            }
            case LESS_THAN_OR_EQUAL:
                return ds -> getDataSourceComparator().compare(dataSourceName, ds) >= 0;

            case EQUAL:
                return ds -> getDataSourceComparator().compare(dataSourceName,ds) == 0;
            default:
                return ds -> true;
        }
    }
}

//新建分表路由
//分表路由由系統提供默認按月分片
@Component
public class OrderTableRoute extends AbstractMonthTableRoute<OrderEntity> {

    @Override
    protected LocalDateTime convertLocalDateTime(Object shardingValue) {
        return (LocalDateTime)shardingValue;
    }
}
```
通過sql腳本我們創建好對應的資料庫表結構
![](https://img2023.cnblogs.com/blog/1346660/202306/1346660-20230626215913917-1277133380.png)

初始化專案代碼
``java

    private final EasyProxyQuery easyProxyQuery;
    @GetMapping("/init")
    public Object init() {

        long start = System.currentTimeMillis();
        LocalDateTime beginTime = LocalDateTime.of(2020, 1, 1, 0, 0, 0);
        LocalDateTime now = LocalDateTime.now();
        ArrayList<OrderEntity> orderEntities = new ArrayList<>();
        List<String> userIds = Arrays.asList("小明", "小紅", "小藍", "小黃", "小綠");
        int i=0;
        do {
            OrderEntity orderEntity = new OrderEntity();
            String timeFormat = DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(beginTime);
            orderEntity.setId(timeFormat);
            orderEntity.setOrderNo(i);
            orderEntity.setUserId(userIds.get(i%5));
            orderEntity.setCreateTime(beginTime);
            orderEntities.add(orderEntity);
            i++;
            beginTime=beginTime.plusMinutes(1);
        } while (beginTime.isBefore(now));

        long end = System.currentTimeMillis();

        long insertStart = System.currentTimeMillis();
        long rows = easyProxyQuery.insertable(orderEntities).executeRows();
        long insertEnd = System.currentTimeMillis();

        return "成功插入:" + rows+",其中路由物件生成耗時:"+(end-start)+"(ms),插入耗時:"+(insertEnd-insertStart)+"(ms)";
    }
```
![](https://img2023.cnblogs.com/blog/1346660/202306/1346660-20230626223148693-872730237.png)
資料初始化成功,接下來演示如何進行分組
# 分組聚合

分表分庫下我們應該如何分組聚合

代碼很簡單就是查詢userId in ["小明", "小綠"]的然后對userId分組求對應的訂單號求和
````java

    @GetMapping("/groupByWithSumOrderNo")
    public Object groupByWithSumOrderNo() {
        long start = System.currentTimeMillis();
        List<String> userIds = Arrays.asList("小明", "小綠");
        List<OrderGroupWithSumOrderNoVO> list = easyProxyQuery.queryable(OrderEntityProxy.DEFAULT)
                .where((filter, t) -> filter.in(t.userId(), userIds))
                .groupBy((group, t) -> group.column(t.userId()))
                .select(OrderGroupWithSumOrderNoVOProxy.DEFAULT, (selector, t) -> selector.columnAs(t.userId(), r -> r.userId()).columnSumAs(t.orderNo(), r -> r.orderNoSum()))
                .toList();
        long end = System.currentTimeMillis();
        return Arrays.asList(list,(end-start)+"(ms)");
    }

[[{"orderNoSum":993365517,"userId":"小明"},{"orderNoSum":992998911,"userId":"小綠"}],"1768(ms)"]共計耗時約1.8秒

    @GetMapping("/groupByWithSumOrderNoOrderByUserId")
    public Object groupByWithSumOrderNoOrderByUserId() {
        long start = System.currentTimeMillis();
        List<String> userIds = Arrays.asList("小明", "小綠");
        List<OrderGroupWithSumOrderNoVO> list = easyProxyQuery.queryable(OrderEntityProxy.DEFAULT)
                .where((filter, t) -> filter.in(t.userId(), userIds))
                .groupBy((group, t) -> group.column(t.userId()))
                .orderByAsc((order,t)->order.column(t.userId()))
                .select(OrderGroupWithSumOrderNoVOProxy.DEFAULT, (selector, t) -> selector.columnAs(t.userId(), r -> r.userId()).columnSumAs(t.orderNo(), r -> r.orderNoSum()))
                .toList();
        long end = System.currentTimeMillis();
        return Arrays.asList(list,(end-start)+"(ms)");
    }

[[{"orderNoSum":993365517,"userId":"小明"},{"orderNoSum":992998911,"userId":"小綠"}],"1699(ms)"]
我們非常快速的獲取了查詢結果,那么這個結果是如何獲取的呢接下來我將講解分組聚合的原理并且會講解order by對group在分片中的影響是有多大的影響

分組求和

原理決議這邊以兩個分片來進行聚合

將sql進行路由決議后分別對兩個分片節點進行查詢聚合,然后合并到記憶體中分別對group和sum進行處理實作無感知分片聚合group,但是大家可能已經發現了這個是sum那么各個節點的資料可以相加如果是avg呢應該怎么辦，接下來我來講解group下如何進行avg的資料聚合查詢，

分組取平均數

首先我們來假設一個表a和表b兩個表里面資料如下

結果錯誤！！！
可以看到單純的通過記憶體來進行平均值的聚合是不正確的,因為只有當各個分片內的資料和分片數一樣才可以簡單的avg,那么我們應該如何實作分組求平均呢，

1.avg本質等于什么？

我們都知道avg=sum/count通過這個公式avg是不可以簡單分片聚合的那么如果我們知道sum和count呢,是不是就可以知道avg了，在退一萬步我們已經知道avg的情況下是不是只需要知道sum或者count也可以算出對應的第三個值

2.如何實作

• 如果用戶存在group+avg那么強制要求進行對應avg欄位也進行sum或者count的其中一個
• 如果發現用戶存在group+avg那么就自動重寫添加sum或者count的查詢

那么之前的group+avg就會變成group+avg+sum

通過上述描述我們應該可以清晰的看到應該如何針對各個節點的分組求平均值來處理正確的方法

記憶體分組聚合

上述所有例子我們都是通過記憶體分組聚合來實作各個節點的分組聚合,缺點就是需要先把各個節點的資料存盤到記憶體中然后再次進行分組,那么如果各個節點的資料過多那么在分組聚合的第一階段可能會導致記憶體的大量消耗,所以接下來我將給大家講解流式分組聚合

流式分組聚合

上個文章我們講解過流式聚合,那么流式分組聚合和流式聚合的差別在哪呢，很明顯就是分組這個關鍵字上,我們如何保證下一個next所需的資料就是我上一個需要group的呢

答案就是order by

只要各個節點的order by后的排序欄位和編程語言在記憶體中的一樣即可保證

easy-query是如何實作的

        List<String> userIds = Arrays.asList("小明", "小綠");
        List<OrderGroupWithAvgOrderNoVO> list = easyProxyQuery.queryable(OrderEntityProxy.DEFAULT)
                .where((filter, t) -> filter.in(t.userId(), userIds))
                .groupBy((group, t) -> group.column(t.userId()))
                .orderByAsc((order, t) -> order.column(t.userId()))
                .select(OrderGroupWithAvgOrderNoVOProxy.DEFAULT, (selector, t) -> selector.columnAs(t.userId(), r -> r.userId()).columnAvgAs(t.orderNo(), r -> r.orderNoAvg()))
                .toList();

//生成的sql 會自動補齊count和sum來保證資料結果的正確性
SELECT t.`user_id` AS `user_id`,AVG(t.`order_no`) AS `order_no_avg`,COUNT(t.`order_no`) AS `orderNoRewriteCount`,SUM(t.`order_no`) AS `orderNoRewriteSum` 
FROM `t_order_202211` t 
WHERE t.`user_id` IN (?,?) 
GROUP BY t.`user_id` 
ORDER BY t.`user_id` ASC
[[{"orderNoAvg":916515.0000,"userId":"小明"},{"orderNoAvg":916516.5000,"userId":"小綠"}],"1924(ms)"]

分別對其進行求和和求count
[[{"orderNoSum":336000814605,"userId":"小明"}],"914(ms)"]
[[{"orderNoAvg":366607,"userId":"小明"}],"777(ms)"]
916515.0000=336000814605/366607 所以結果是正確的

分頁聚合

如果您在專案中使用過分庫分表,那么一定知道分庫分表的難點在哪里，那么就是聚合資料范圍和跨表資料回傳,如果跨表資料回傳再有一個難點那么就是分片資料跨分片分頁,并且支持條件排序等處理操作，

記憶體分頁

記憶體分頁作為最簡單的分頁方法,在前幾頁的處理中有著非常方便的和高效的實用,具體原理如下

--原始sql
select * from order where time between 2020 and 2021 order by time limit 1,5
假如他被路由到2020和2021兩張表那么要獲取前10條資料應該怎么寫
select * from order_2020 where time between 2020 and 2021 order by time limit 1,5
select * from order_2021 where time between 2020 and 2021 order by time limit 1,5

分別對兩張表進行前10條資料的獲取然后再記憶體中就有20條資料,針對這20條資料進行order by time的相同操作,然后獲取前10條

那么如果是獲取第二頁呢

--原始sql
select * from order where time between 2020 and 2021 order by time limit 2,5
假如他被路由到2020和2021兩張表那么要獲取前10條資料應該怎么寫

--錯誤的做法
select * from order_2020 where time between 2020 and 2021 order by time limit 2,5
select * from order_2021 where time between 2020 and 2021 order by time limit 2,5

通過上圖我們清晰地可以知道這個決議是錯誤那么正確的應該是怎么樣的呢

--原始sql
select * from order where time between 2020 and 2021 order by time limit 2,5
假如他被路由到2020和2021兩張表那么要獲取前10條資料應該怎么寫

--正確的做法
select * from order_2020 where time between 2020 and 2021 order by time limit 1,10
select * from order_2021 where time between 2020 and 2021 order by time limit 1,10

考慮到最壞的情況就是6-10全部在左側或者全部在右側,因為資料的分布無法知曉所以我們應該以最壞的情況來獲取資料然后獲取6-10條資料

好了這樣我們就實作了如何用記憶體來實作

 int pageIndex=x
int pageSize=y;
那么重寫后的sql應該是
 int pageIndex=1
int pageSize=x*y;

雖然我們發現了如何正確的獲取分頁資料但是也存在一個非常嚴重的問題,就是深度分頁導致的記憶體爆炸,因為每個分片的獲取物件都是xy那么如果有n個分片被本次查詢覆寫那么就需要獲取至多xy*n條資料到記憶體,其中pageIndex就是x是用戶自行選擇的所以會存在x的大小不確定這樣就會導致記憶體的嚴重消耗甚至oom，

流式分頁

既然我們已經知道了記憶體分頁的缺點那么是否有辦法針對上述缺點進行規避或者優化呢,答案是有的就是流式分頁，所謂流式分頁就是利用ResultSet的延遲獲取特點,配合之前的流式獲取來適當性的放棄頭部資料來達到節省記憶體的效果.

流式分頁是如何優化程式的

 int pageIndex=x
int pageSize=y;
那么重寫后的sql應該是
 int pageIndex=1
int pageSize=x*y;

因為jdbc的resultset延遲獲取的特性,所以每次呼叫next才會將資料取到客戶端,利用這個特性可以將前5條資料獲取到并且放棄來實作記憶體嚴格控制,并且滿足獲取條數后后面的11-20是不需要獲取的,有效的避免網路I/O的浪費和大大提高性能，

雖然流式分頁可以大大的提高記憶體利用率,并且可以用最少的I/O次數來獲取正確的分頁數量由原先的xyn變成x*y，但是我們會發現在深度分頁的情況下網路io還是需要實打實的獲取到客戶端進行判斷,所以在深分頁下不僅資料庫壓力大,客戶端網路I/O壓力也大，并且在頁數很大的情況下默認起始頁和結束頁是默認顯示在分頁組件上的那么就就會導致用戶很容易點到頁尾導致程式進入卡死狀態并且回應變慢從而拖慢應用

反排分頁

跨分片深度分頁解決方案

• 1.讓用戶妥協只支持瀑布流分頁,就是app滾動相似的分頁,不支持跳頁只支持next頁放棄count僅limit獲取,并且無法自定義排序,業務上直接禁止頁尾跳頁直接避免問題

• 2.反向排序分頁依然是count+limit的組合

我們都知道跨分片的聚合是因為深度分頁慢是因為網路I/O的大量讀取,所以如果我們可以保證網路I/O的讀取次數變少那么是否就能解決這個問題

何謂反向分頁首先我們來看一張圖

原來我們需要跳過大量的網路I/O才能獲取的正確資料如果我們有反向分頁那么只需要跳過少量的資料就可以實作深度分頁,并且因為大部分業務場景都支持跳頁所以count的查詢是一定會有的，我們只需要對各個分片的count進行第一次查詢的獲取那么就可以保證在深度分頁下的反向排序分頁

通過對order by的反向置換并且將offset重新計算來四線深度跨分片分頁下I/O的極大減少保證正序的健壯性

順序分頁

到目前為止我們的頁首和頁尾節點的分頁已經解決了,那么針對分頁的中間部分改怎么辦呢,是否還有優化方案呢，答案是有的但是這個優化方案對于分片方式有特殊的要求并沒有前兩種的通用化，順序分片，
什么叫做順序分頁，順序分頁就是例如按時間按月分表，按年分表，按天分表，每張的內部資料永遠是有一個特殊的排序欄位可以讓其依次從小到大排列，如果分片是這種特性的分片那么可以保證在order by這個特殊欄位的時候幾乎可以做到除了頁首和頁尾甚至中間任意節點的高性能

到目前為止如果您是順序分頁并且排序欄位是順序欄位那么可以保證跨分片的查詢和普通查詢基本沒有兩樣,但是我們其實還是發現了一個問題就是每次查詢都需要count一下這個其實是很費時間的,并且基本上如果是大數量的情況下基本大致資料不需要更新或者只需要更新最新的一頁即可

指定分頁

基于上述問題easy-query實作了指定分頁,就是可以通過第一次的分片記錄下當前條件的各個節點的count資料,那么接下來的查詢如果條件沒有變化就不需要再進行count了,并且針對最新節點依然可以選擇單獨查詢count從而來保證資料的準確性，

最后

通過上述幾個講述您應該已經對分表分庫有了一個全新的理解和優化,接下來的幾個篇章我將帶你通過簡單的實作和高級的抽象來完成easy-query的全新orm的變成之旅讓分表分庫變得非常簡單且非常高效,并且會提出多種解決方案來實作老舊資料的遷移,資料分片不均勻,多欄位分片索引的種種解決方案，

如果覺得有用請點擊star謝謝大家了

QQ群:170029046

• GITHUB github地址 https://github.com/xuejmnet/easy-query

• GITEE gitee地址 https://gitee.com/xuejm/easy-query

標籤：其他

上一篇：高并發場景下，6種解決SimpleDateFormat類的執行緒安全問題方法

下一篇：返回列表