一、 需求概述
在使用 Mysql 數據庫存儲設備上報日志時，存在一張設備實時狀態表，隨著時間推移，數據量變得十分龐大。為了更好地管理和查詢數據，提高數據庫性能，需要對該表進行水平分表操作。同時，存在分頁查詢的需求，不過僅在針對單個設備狀態查詢時才需要分頁展示結果，以方便查看設備在不同時間段的狀態信息，避免一次性返回大量數據影響性能和使用體驗。
二、分表鍵的選擇策略詳解
1. 哈希取模分片
哈希取模分片是常用的水平分表策略，通過對選定的分片鍵（如設備編號）進行哈希運算后取模，確定數據存儲的分表。
-
原理及優勢：
- 均勻分布數據：對于設備實時狀態表這種大數據量且設備眾多的情況，能讓數據均勻分散到各分表。例如，假設有 10 張分表，對設備編號哈希取模 10，不同設備的狀態日志可均衡落入這 10 張表，避免數據傾斜，使各表數據量相近，查詢時各分表負載均衡，提升數據庫整體性能。
- 簡單高效的路由：查詢時按相同哈希取模規則，可快速定位對應分表。比如查詢某個設備狀態日志，經設備編號哈希取模運算，就能知曉去哪個分表獲取數據，減少全表掃描和復雜查找邏輯，尤其適用于單個設備狀態查詢場景。
-
缺點及注意事項：
- 擴容復雜：業務發展需增加分表數量時（如從 10 張擴到 20 張），哈希取模規則改變，原本數據分布打亂，需進行數據遷移來重新平衡各表數據，操作復雜耗時，涉及大量數據讀寫和系統調整，所以規劃分表數量初期要考慮未來業務增長規模，預留擴展空間。
- 哈希沖突：雖然哈希算法通常能保證唯一性，但理論上存在不同設備編號哈希計算后結果相同（取模后也相同）的情況，即哈希沖突。不過實際應用中，選擇合適哈希函數（如 MD5、SHA 等或數據庫自帶算法）可將沖突概率降至極低，開發人員仍需在代碼中考慮應對沖突，比如增加額外處理邏輯區分沖突記錄。
例如，在 Spring Boot + MyBatis 手動分表時，按以下代碼邏輯實現哈希取模確定分表（以設備編號后三位數字簡單取模為例，實際可采用更嚴謹算法）：
public class TableShardingUtil {private static final int TABLE_COUNT = 10; // 假設分表數量為 10public 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 語句中利用該方法構建動態表名（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} <!-- 這里使用動態表名 -->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);
}
業務邏輯層調用時先算出表名再傳遞給 Mapper 方法查詢：
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;@Service
public class DeviceStatusService {@Resourceprivate 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);}
}
通過這種方式實現基于哈希取模的分片鍵策略，動態依據設備編號確定分表并進行分頁查詢操作。
2. 范圍分片
-
原理及優勢：
- 按業務邏輯自然劃分：依據數據的某個范圍屬性來劃分分表，像按時間范圍（如按天、月、年等）對設備實時狀態表分表就是典型的范圍分片。這種方式契合按時間段查詢數據的業務習慣，查詢特定時間段內設備狀態日志時，可直接定位對應時間范圍分表，減少不必要數據檢索，提高查詢效率，且對基于時間序列的數據分析、歷史數據歸檔等操作更便捷，數據組織形式直觀易懂。
- 易于數據管理和維護：進行數據清理、備份等操作時，基于范圍分片能按時間等范圍屬性方便地批量處理分表數據，比如定期清理久遠時間范圍分表中的過期數據，不影響其他活躍時間段的數據表。
-
缺點及注意事項：
- 可能出現數據傾斜：若業務數據在某些范圍內分布不均，易導致數據傾斜。比如某些時間段設備上報狀態日志多，有些時間段少，對應的分表數據量差異大，查詢時各分表負載不均衡，影響整體性能。所以選擇范圍分片時，要充分考慮業務數據在該范圍屬性上的分布特點，必要時結合其他策略緩解數據傾斜，如細分范圍或配合哈希取模均勻分布數據。
- 跨表查詢需求處理復雜：涉及跨越多個范圍分表查詢（如查詢一個設備較長時間跨度內狀態，跨越多個月分表）時，需編寫復雜查詢邏輯整合多表數據，不像單表查詢簡單直接，要特別注意處理分表連接、數據去重等問題，避免數據不一致或查詢結果不準確。
3. 一致性哈希分片
-
原理及優勢：
- 數據分布相對穩定：一致性哈希是特殊哈希算法，面對節點（分表可看作節點）增減時，相比普通哈希取模，能最大程度減少數據遷移量。例如在分布式數據庫環境中，新增或減少分表數量時，一致性哈希可保證只有少部分數據需重新分配到新分表，使數據分布在動態變化場景下保持相對穩定，減少對業務影響。
- 可擴展性較好：對于業務發展、數據量增長需不斷擴充分表的情況，一致性哈希分片能更平滑適應變化，降低分表擴展帶來的運維成本和數據調整難度，提高系統整體可擴展性，更利于應對復雜多變的業務需求。
-
缺點及注意事項：
- 實現相對復雜：一致性哈希算法原理和實現比普通哈希取模復雜，要求開發人員有更深入理解和專業編程能力進行代碼實現與部署，增加開發和維護難度。在一些對可擴展性要求不高的簡單應用場景，使用一致性哈希可能增加不必要復雜度。
- 存在數據傾斜風險：雖然一致性哈希能一定程度均勻分配數據，但在極端情況（如節點分布不均或數據哈希值分布有偏差）下，也可能出現數據傾斜，導致部分分表負載過重，影響查詢性能，實際應用中需關注數據分布并適當優化。
三、水平分表及分頁查詢實現示例
（一）數據庫表結構設計
1.設備實時狀態表結構（分表前）
假設設備上報的日志主要包含設備的基本信息、狀態信息以及上報時間等內容，以下是一個簡單的表結構設計示例：
| 字段名 | 類型 | 說明 | 是否可空 | 主鍵 |
|---|---|---|---|---|
| id | bigint | 自增唯一標識，每條日志記錄的唯一編號 | 否 | 是 |
| device_id | varchar(50) | 設備編號，用于唯一標識每一臺設備 | 否 | 否 |
| device_name | varchar(100) | 設備名稱，方便直觀了解設備情況 | 否 | 否 |
| status_code | int | 設備狀態碼，不同數值代表不同的運行狀態，例如 0 表示正常，1 表示故障等 | 否 | 否 |
| status_detail | text | 設備狀態詳細描述，比如故障具體原因等信息 | 是 | 否 |
| report_time | datetime | 設備上報該狀態的時間 | 否 | 否 |
| other_info | varchar(255) | 其他可能的補充信息，如設備所在位置等（可根據實際情況擴展） | 是 | 否 |
在這個表結構中，id 作為主鍵保證每條記錄的唯一性，便于數據的索引和管理。而 device_id 是區分不同設備的關鍵字段，后續水平分表就會基于它來進行操作，report_time 用于記錄狀態上報的時間點，方便后續按時間維度查詢和分析設備狀態變化情況等。
2.分表后的表結構
根據 device_id 作為分表鍵進行水平分表，分表后的每張表結構與原始表結構基本一致，只是數據根據分表規則分散到了不同的表中。
例如，假設按照設備編號對 10 取模的方式將數據分到 10 張表中，表名可以分別命名為 device_status_0、device_status_1、device_status_2…… device_status_9。
以 device_status_0 為例，其表結構如下：
| 字段名 | 類型 | 說明 | 是否可空 | 主鍵 |
|---|---|---|---|---|
| id | bigint | 自增唯一標識，每條日志記錄的唯一編號 | 否 | 是 |
| device_id | varchar(50) | 設備編號，用于唯一標識每一臺設備 | 否 | 否 |
| device_name | varchar(100) | 設備名稱，方便直觀了解設備情況 | 否 | 否 |
| status_code | int | 設備狀態碼，不同數值代表不同的運行狀態，例如 0 表示正常，1 表示故障等 | 否 | 否 |
| status_detail | text | 設備狀態詳細描述，比如故障具體原因等信息 | 是 | 否 |
| report_time | datetime | 設備上報該狀態的時間 | 否 | 否 |
| other_info | varchar(255) | 其他可能的補充信息，如設備所在位置等（可根據實際情況擴展） | 是 | 否 |
其他 device_status_1 到 device_status_9 等表結構均與之相同，只是每張表中存儲的數據是根據 device_id 取模規則分配過來的對應設備的狀態日志信息。
（二）使用 springboot + mybatis 手動水平分表并實現分頁
- 分表設計：
首先要確定分表鍵，對于設備實時狀態表來說，設備編號（device_id）是比較合適的分表鍵選擇。因為往往是針對單個設備的操作和查詢較多，以設備編號進行分表能讓同一設備的數據集中存儲在一張分表中，方便后續查詢和管理。可以按照一定規則，比如根據設備編號對分表數量取模的方式，將數據均勻分散到不同的分表中，例如有 10 張分表，設備編號為 1001 的設備，通過 1001 % 10 確定其存儲在對應的分表中。 - 代碼實現：
在 Spring Boot 項目中，配置好 MyBatis 相關依賴和數據庫連接信息。創建針對不同分表的 Mapper 接口和對應的 XML 映射文件。在查詢單個設備狀態并分頁時，需要在 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 方法，傳入相應參數即可實現單個設備狀態的分頁查詢，通過這種手動方式靈活控制分表和分頁邏輯，但需要自行處理較多的細節，如分表規則的維護等。
（三）使用 springboot + sharing-jdbc + mybatis 實現水平分表并分頁查詢
- 分表配置：
同樣選擇設備編號（device_id）作為分表鍵。在 Spring Boot 項目中引入 Sharding-JDBC 相關依賴，然后通過配置文件（如 application.yml）進行分表規則配置。例如：
sharding:tables:device_status:actual-data-nodes: device_status_$->{0..9}.device_statustable-strategy:inline:sharding-column: device_idalgorithm-expression: device_status_$->{device_id % 10}
這樣 Sharding-JDBC 會按照配置的規則自動根據設備編號對數據進行分表存儲。
2. 分頁查詢實現：
在 MyBatis 的 Mapper 接口中定義查詢方法，和上面類似，例如：
List<DeviceStatus> getDeviceStatusByPageWithSharding(@Param("deviceId") String deviceId, @Param("offset") int offset, @Param("limit") int limit);
在 XML 映射文件中編寫 SQL 語句時，無需像手動分表那樣關注具體分表的選擇，只需要按照常規的查詢語法編寫，Sharding-JDBC 會在底層根據配置的分表規則自動路由到正確的分表上進行查詢并實現分頁，示例 SQL 如下：
SELECT * FROM device_status
WHERE device_id = #{deviceId}
LIMIT #{offset}, #{limit};
在 Service 層調用該方法即可輕松實現單個設備狀態的分頁查詢，Sharding-JDBC 幫助簡化了分表相關的很多復雜操作，提高了開發效率。
 拉取失敗解決方法)
