一、 需求概述

在使用 Mysql 數據庫存儲設備上報日志時，存在一張設備實時狀態(tài)表，隨著時間推移，數據量變得十分龐大。為了更好地管理和查詢數據，提高數據庫性能，需要對該表進行水平分表操作。同時，存在分頁查詢的需求，不過僅在針對單個設備狀態(tài)查詢時才需要分頁展示結果，以方便查看設備在不同時間段的狀態(tài)信息，避免一次性返回大量數據影響性能和使用體驗。

二、分表鍵的選擇策略詳解

1. 哈希取模分片

哈希取模分片是常用的水平分表策略，通過對選定的分片鍵（如設備編號）進行哈希運算后取模，確定數據存儲的分表。

• 原理及優(yōu)勢：

  • 均勻分布數據：對于設備實時狀態(tài)表這種大數據量且設備眾多的情況，能讓數據均勻分散到各分表。例如，假設有 10 張分表，對設備編號哈希取模 10，不同設備的狀態(tài)日志可均衡落入這 10 張表，避免數據傾斜，使各表數據量相近，查詢時各分表負載均衡，提升數據庫整體性能。
  • 簡單高效的路由：查詢時按相同哈希取模規(guī)則，可快速定位對應分表。比如查詢某個設備狀態(tài)日志，經設備編號哈希取模運算，就能知曉去哪個分表獲取數據，減少全表掃描和復雜查找邏輯，尤其適用于單個設備狀態(tài)查詢場景。

• 缺點及注意事項：

  • 擴容復雜：業(yè)務發(fā)展需增加分表數量時（如從 10 張擴到 20 張），哈希取模規(guī)則改變，原本數據分布打亂，需進行數據遷移來重新平衡各表數據，操作復雜耗時，涉及大量數據讀寫和系統(tǒng)調整，所以規(guī)劃分表數量初期要考慮未來業(yè)務增長規(guī)模，預留擴展空間。
  • 哈希沖突：雖然哈希算法通常能保證唯一性，但理論上存在不同設備編號哈希計算后結果相同（取模后也相同）的情況，即哈希沖突。不過實際應用中，選擇合適哈希函數（如 MD5、SHA 等或數據庫自帶算法）可將沖突概率降至極低，開發(fā)人員仍需在代碼中考慮應對沖突，比如增加額外處理邏輯區(qū)分沖突記錄。

例如，在 Spring Boot + MyBatis 手動分表時，按以下代碼邏輯實現哈希取模確定分表（以設備編號后三位數字簡單取模為例，實際可采用更嚴謹算法）：

public class TableShardingUtil {
    private static final int TABLE_COUNT = 10;  // 假設分表數量為 10

    public static String getTableNameByDeviceId(String deviceId) {
        int deviceIdSuffix = Integer.parseInt(deviceId.substring(deviceId.length() - 3));  // 獲取設備編號后三位并轉為整數
        int tableIndex = deviceIdSuffix % TABLE_COUNT;  // 取模確定分表索引
        return "device_status_" + String.format("%03d", tableIndex);  // 構建分表名，格式化為三位數字，如 device_status_001
    }
}

在 MyBatis 的 SQL 語句中利用該方法構建動態(tài)表名（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.example.demo.mapper.DeviceStatusMapper">
    <select id="getDeviceStatusByDeviceIdPage" resultMap="DeviceStatusResultMap">
        SELECT * FROM #{tableName}  <!-- 這里使用動態(tài)表名 -->
        WHERE device_id = #{deviceId}
        LIMIT #{offset}, #{limit}
    </select>
</mapper>

對應的 Mapper 接口方法傳入計算得到的表名：

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;

@Mapper
public interface DeviceStatusMapper {
    List<DeviceStatus> getDeviceStatusByDeviceIdPage(
            @Param("tableName") String tableName,  // 新增表名參數
            @Param("deviceId") String deviceId,
            @Param("offset") int offset,
            @Param("limit") int limit);
}

業(yè)務邏輯層調用時先算出表名再傳遞給 Mapper 方法查詢：

import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;

@Service
public class DeviceStatusService {
    @Resource
    private DeviceStatusMapper deviceStatusMapper;

    public List<DeviceStatus> getDeviceStatusByDeviceIdPage(String deviceId, int pageNum, int pageSize) {
        String tableName = TableShardingUtil.getTableNameByDeviceId(deviceId);
        int offset = (pageNum - 1) * pageSize;
        return deviceStatusMapper.getDeviceStatusByDeviceIdPage(tableName, deviceId, offset, pageSize);
    }
}

通過這種方式實現基于哈希取模的分片鍵策略，動態(tài)依據設備編號確定分表并進行分頁查詢操作。

2. 范圍分片

• 原理及優(yōu)勢：

  • 按業(yè)務邏輯自然劃分：依據數據的某個范圍屬性來劃分分表，像按時間范圍（如按天、月、年等）對設備實時狀態(tài)表分表就是典型的范圍分片。這種方式契合按時間段查詢數據的業(yè)務習慣，查詢特定時間段內設備狀態(tài)日志時，可直接定位對應時間范圍分表，減少不必要數據檢索，提高查詢效率，且對基于時間序列的數據分析、歷史數據歸檔等操作更便捷，數據組織形式直觀易懂。
  • 易于數據管理和維護：進行數據清理、備份等操作時，基于范圍分片能按時間等范圍屬性方便地批量處理分表數據，比如定期清理久遠時間范圍分表中的過期數據，不影響其他活躍時間段的數據表。

• 缺點及注意事項：

  • 可能出現數據傾斜：若業(yè)務數據在某些范圍內分布不均，易導致數據傾斜。比如某些時間段設備上報狀態(tài)日志多，有些時間段少，對應的分表數據量差異大，查詢時各分表負載不均衡，影響整體性能。所以選擇范圍分片時，要充分考慮業(yè)務數據在該范圍屬性上的分布特點，必要時結合其他策略緩解數據傾斜，如細分范圍或配合哈希取模均勻分布數據。
  • 跨表查詢需求處理復雜：涉及跨越多個范圍分表查詢（如查詢一個設備較長時間跨度內狀態(tài)，跨越多個月分表）時，需編寫復雜查詢邏輯整合多表數據，不像單表查詢簡單直接，要特別注意處理分表連接、數據去重等問題，避免數據不一致或查詢結果不準確。

3. 一致性哈希分片

• 原理及優(yōu)勢：

  • 數據分布相對穩(wěn)定：一致性哈希是特殊哈希算法，面對節(jié)點（分表可看作節(jié)點）增減時，相比普通哈希取模，能最大程度減少數據遷移量。例如在分布式數據庫環(huán)境中，新增或減少分表數量時，一致性哈?？杀ＷC只有少部分數據需重新分配到新分表，使數據分布在動態(tài)變化場景下保持相對穩(wěn)定，減少對業(yè)務影響。
  • 可擴展性較好：對于業(yè)務發(fā)展、數據量增長需不斷擴充分表的情況，一致性哈希分片能更平滑適應變化，降低分表擴展帶來的運維成本和數據調整難度，提高系統(tǒng)整體可擴展性，更利于應對復雜多變的業(yè)務需求。

• 缺點及注意事項：

  • 實現相對復雜：一致性哈希算法原理和實現比普通哈希取模復雜，要求開發(fā)人員有更深入理解和專業(yè)編程能力進行代碼實現與部署，增加開發(fā)和維護難度。在一些對可擴展性要求不高的簡單應用場景，使用一致性哈希可能增加不必要復雜度。
  • 存在數據傾斜風險：雖然一致性哈希能一定程度均勻分配數據，但在極端情況（如節(jié)點分布不均或數據哈希值分布有偏差）下，也可能出現數據傾斜，導致部分分表負載過重，影響查詢性能，實際應用中需關注數據分布并適當優(yōu)化。

三、水平分表及分頁查詢實現示例

（一）數據庫表結構設計

1.設備實時狀態(tài)表結構（分表前）

假設設備上報的日志主要包含設備的基本信息、狀態(tài)信息以及上報時間等內容，以下是一個簡單的表結構設計示例：

在這個表結構中，id 作為主鍵保證每條記錄的唯一性，便于數據的索引和管理。而 device_id 是區(qū)分不同設備的關鍵字段，后續(xù)水平分表就會基于它來進行操作，report_time 用于記錄狀態(tài)上報的時間點，方便后續(xù)按時間維度查詢和分析設備狀態(tài)變化情況等。

2.分表后的表結構

根據 device_id 作為分表鍵進行水平分表，分表后的每張表結構與原始表結構基本一致，只是數據根據分表規(guī)則分散到了不同的表中。

例如，假設按照設備編號對 10 取模的方式將數據分到 10 張表中，表名可以分別命名為 device_status_0、device_status_1、device_status_2…… device_status_9。

以 device_status_0 為例，其表結構如下：

其他 device_status_1 到 device_status_9 等表結構均與之相同，只是每張表中存儲的數據是根據 device_id 取模規(guī)則分配過來的對應設備的狀態(tài)日志信息。

（二）使用 springboot + mybatis 手動水平分表并實現分頁

1. 分表設計：
   首先要確定分表鍵，對于設備實時狀態(tài)表來說，設備編號（device_id）是比較合適的分表鍵選擇。因為往往是針對單個設備的操作和查詢較多，以設備編號進行分表能讓同一設備的數據集中存儲在一張分表中，方便后續(xù)查詢和管理。可以按照一定規(guī)則，比如根據設備編號對分表數量取模的方式，將數據均勻分散到不同的分表中，例如有 10 張分表，設備編號為 1001 的設備，通過 1001 % 10 確定其存儲在對應的分表中。
2. 代碼實現：
   在 Spring Boot 項目中，配置好 MyBatis 相關依賴和數據庫連接信息。創(chuàng)建針對不同分表的 Mapper 接口和對應的 XML 映射文件。在查詢單個設備狀態(tài)并分頁時，需要在 Mapper 接口中定義相應的方法，例如：

List<DeviceStatus> getDeviceStatusByPage(@Param("deviceId") String deviceId, @Param("offset") int offset, @Param("limit") int limit);

在 XML 映射文件中編寫 SQL 語句，通過傳入的設備編號確定要查詢的分表，結合傳入的偏移量（offset）和每頁數量（limit）來實現分頁查詢，示例 SQL 如下：

SELECT * FROM device_status_${deviceId % 10} 
WHERE device_id = #{deviceId} 
LIMIT #{offset}, #{limit};

在 Service 層調用該 Mapper 方法，傳入相應參數即可實現單個設備狀態(tài)的分頁查詢，通過這種手動方式靈活控制分表和分頁邏輯，但需要自行處理較多的細節(jié)，如分表規(guī)則的維護等。

（三）使用 springboot + sharing-jdbc + mybatis 實現水平分表并分頁查詢

1. 分表配置：
   同樣選擇設備編號（device_id）作為分表鍵。在 Spring Boot 項目中引入 Sharding-JDBC 相關依賴，然后通過配置文件（如 application.yml）進行分表規(guī)則配置。例如：

sharding:
  tables:
    device_status:
      actual-data-nodes: device_status_$->{0..9}.device_status
      table-strategy:
        inline:
          sharding-column: device_id
          algorithm-expression: device_status_$->{device_id % 10}

這樣 Sharding-JDBC 會按照配置的規(guī)則自動根據設備編號對數據進行分表存儲。
2. 分頁查詢實現：
在 MyBatis 的 Mapper 接口中定義查詢方法，和上面類似，例如：

List<DeviceStatus> getDeviceStatusByPageWithSharding(@Param("deviceId") String deviceId, @Param("offset") int offset, @Param("limit") int limit);

在 XML 映射文件中編寫 SQL 語句時，無需像手動分表那樣關注具體分表的選擇，只需要按照常規(guī)的查詢語法編寫，Sharding-JDBC 會在底層根據配置的分表規(guī)則自動路由到正確的分表上進行查詢并實現分頁，示例 SQL 如下：

SELECT * FROM device_status 
WHERE device_id = #{deviceId} 
LIMIT #{offset}, #{limit};

在 Service 層調用該方法即可輕松實現單個設備狀態(tài)的分頁查詢，Sharding-JDBC 幫助簡化了分表相關的很多復雜操作，提高了開發(fā)效率。

到此這篇關于Mysql 數據庫中設備實時狀態(tài)表水平分表的文章就介紹到這了,更多相關Mysql 設備實時狀態(tài)表水平分表內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論