一、 需求概述

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

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

1. 哈希取模分片

哈希取模分片是常用的水平分表策略，通過對(duì)選定的分片鍵（如設(shè)備編號(hào)）進(jìn)行哈希運(yùn)算后取模，確定數(shù)據(jù)存儲(chǔ)的分表。

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

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

• 缺點(diǎn)及注意事項(xiàng)：

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

例如，在 Spring Boot + MyBatis 手動(dòng)分表時(shí)，按以下代碼邏輯實(shí)現(xiàn)哈希取模確定分表（以設(shè)備編號(hào)后三位數(shù)字簡(jiǎn)單取模為例，實(shí)際可采用更嚴(yán)謹(jǐn)算法）：

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

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

在 MyBatis 的 SQL 語句中利用該方法構(gòu)建動(dòng)態(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}  <!-- 這里使用動(dòng)態(tài)表名 -->
        WHERE device_id = #{deviceId}
        LIMIT #{offset}, #{limit}
    </select>
</mapper>

對(duì)應(yīng)的 Mapper 接口方法傳入計(jì)算得到的表名：

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,  // 新增表名參數(shù)
            @Param("deviceId") String deviceId,
            @Param("offset") int offset,
            @Param("limit") int limit);
}

業(yè)務(wù)邏輯層調(diào)用時(shí)先算出表名再傳遞給 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);
    }
}

通過這種方式實(shí)現(xiàn)基于哈希取模的分片鍵策略，動(dòng)態(tài)依據(jù)設(shè)備編號(hào)確定分表并進(jìn)行分頁(yè)查詢操作。

2. 范圍分片

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

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

• 缺點(diǎn)及注意事項(xiàng)：

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

3. 一致性哈希分片

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

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

• 缺點(diǎn)及注意事項(xiàng)：

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

三、水平分表及分頁(yè)查詢實(shí)現(xiàn)示例

（一）數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)

1.設(shè)備實(shí)時(shí)狀態(tài)表結(jié)構(gòu)（分表前）

假設(shè)設(shè)備上報(bào)的日志主要包含設(shè)備的基本信息、狀態(tài)信息以及上報(bào)時(shí)間等內(nèi)容，以下是一個(gè)簡(jiǎn)單的表結(jié)構(gòu)設(shè)計(jì)示例：

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

2.分表后的表結(jié)構(gòu)

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

例如，假設(shè)按照設(shè)備編號(hào)對(duì) 10 取模的方式將數(shù)據(jù)分到 10 張表中，表名可以分別命名為 device_status_0、device_status_1、device_status_2…… device_status_9。

以 device_status_0 為例，其表結(jié)構(gòu)如下：

其他 device_status_1 到 device_status_9 等表結(jié)構(gòu)均與之相同，只是每張表中存儲(chǔ)的數(shù)據(jù)是根據(jù) device_id 取模規(guī)則分配過來的對(duì)應(yīng)設(shè)備的狀態(tài)日志信息。

（二）使用 springboot + mybatis 手動(dòng)水平分表并實(shí)現(xiàn)分頁(yè)

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

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

在 XML 映射文件中編寫 SQL 語句，通過傳入的設(shè)備編號(hào)確定要查詢的分表，結(jié)合傳入的偏移量（offset）和每頁(yè)數(shù)量（limit）來實(shí)現(xiàn)分頁(yè)查詢，示例 SQL 如下：

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

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

（三）使用 springboot + sharing-jdbc + mybatis 實(shí)現(xiàn)水平分表并分頁(yè)查詢

1. 分表配置：
   同樣選擇設(shè)備編號(hào)（device_id）作為分表鍵。在 Spring Boot 項(xiàng)目中引入 Sharding-JDBC 相關(guān)依賴，然后通過配置文件（如 application.yml）進(jìn)行分表規(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 會(huì)按照配置的規(guī)則自動(dòng)根據(jù)設(shè)備編號(hào)對(duì)數(shù)據(jù)進(jìn)行分表存儲(chǔ)。
2. 分頁(yè)查詢實(shí)現(xiàn)：
在 MyBatis 的 Mapper 接口中定義查詢方法，和上面類似，例如：

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

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

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

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

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

最新評(píng)論