Mycat 實戰：聊天消息表按月分表與 Java 客戶端跨月查詢

Mycat 實戰：聊天消息表按月分表與 Java 客戶端跨月查詢

業務場景：聊天消息量巨大，按自然月分表存儲（msg_201501、msg_201502...），Mycat 路由分片，Java 客戶端需支援跨月分頁查詢（倒序、游標翻頁）。

一、分表設計

• 邏輯表：msg
• 分片鍵：create_month (INT, 如 201501)
• 分片算法：PartitionByMonth（Mycat 1.4+ 內置，1.3 需手動打包）
• 每月一庫，庫內按 gid (群ID) 做 Key 分區（10 個分區）

二、Mycat 配置

1. rule.xml 定義分片規則

<?xml version="1.0" encoding="UTF-8"?>
<mycat:rule xmlns:mycat="https://org.opencloudb/">
  <tableRule name="sharding-by-month">
    <rule>
      <columns>create_month</columns>
      <algorithm>sharding-by-month</algorithm>
    </rule>
    <function name="sharding-by-month" class="org.opencloudb.route.function.PartitionByMonth">
      <property name="dateFormat">yyyyMM</property>
      <property name="sBeginDate">201501</property>
    </function>
  </tableRule>
</mycat:rule>

2. schema.xml 定義邏輯庫、分片節點、數據源

<?xml version="1.0"?>
<mycat:schema xmlns:mycat="https://org.opencloudb/">
  <schema name="msg" checkSQLschema="false" sqlMaxLimit="100">
    <table name="msg" dataNode="nodeMsg201501,nodeMsg201502,nodeMsg201503,nodeMsg201504" rule="sharding-by-month" />
  </schema>

  <dataNode name="nodeMsg201501" dataHost="dataHost01" database="msg_201501" />
  <dataNode name="nodeMsg201502" dataHost="dataHost01" database="msg_201502" />
  <dataNode name="nodeMsg201503" dataHost="dataHost01" database="msg_201503" />
  <dataNode name="nodeMsg201504" dataHost="dataHost01" database="msg_201504" />

  <dataHost name="dataHost01" maxCon="1000" minCon="100" balance="0" writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select 1</heartbeat>
    <writeHost host="hostM1" url="192.168.100.1:3306" user="root" password="password"></writeHost>
  </dataHost>
</mycat:schema>

3. server.xml 用戶授權

<user name="msg">
  <property name="password">msg</property>
  <property name="schemas">msg</property>
</user>

三、底層 MySQL 建表

-- 每月一庫
CREATE DATABASE msg_201501 DEFAULT CHARSET utf8mb4;
-- 每庫建表，按 gid Key 分區 10 個分區
CREATE TABLE `msg` (
  `id` BIGINT NOT NULL,
  `gid` BIGINT DEFAULT NULL COMMENT '群ID，MySQL分區鍵',
  `content` VARCHAR(4000),
  `create_time` DATETIME DEFAULT NULL,
  `create_month` INT(6) DEFAULT NULL COMMENT '按月分表鍵，如201501',
  PRIMARY KEY (`id`,`gid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY KEY(`gid`) PARTITIONS 10;

四、MySQL 客戶端測試驗證

mysql -umsg -pmsg -P8066 -h 127.0.0.1
INSERT INTO msg(id,gid,content,create_time,create_month) VALUES(1,1,'java',NOW(),201501);
SELECT * FROM msg WHERE gid=1 AND create_month=201501;
-- 精準路由至 msg_201501 庫

五、Java 客戶端跨月分頁查詢核心邏輯

需求：按 gid、create_month 倒序分頁，單月數據不足時自動向前月遞歸補齊。

public List selectByGidMonth(int gid, int month, long lastId, int limit) {
    List list = new ArrayList<>();
    String sql = (lastId == 0) ?
        "SELECT id,gid,content,create_time,create_month FROM msg WHERE gid=? AND create_month=? ORDER BY id DESC LIMIT ?" :
        "SELECT id,gid,content,create_time,create_month FROM msg WHERE gid=? AND create_month=? AND id < ? ORDER BY id DESC LIMIT ?";
    
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, gid);
        ps.setInt(2, month);
        if (lastId == 0) ps.setInt(3, limit);
        else { ps.setLong(3, lastId); ps.setInt(4, limit); }
        
        try (ResultSet rs = ps.executeQuery()) {
            long currentLastId = lastId;
            while (rs.next()) {
                Msg msg = new Msg();
                msg.setId(rs.getInt("id"));
                msg.setGid(rs.getInt("gid"));
                msg.setContent(rs.getString("content"));
                msg.setCreateTime(rs.getTimestamp("create_time"));
                msg.setCreateMonth(rs.getInt("create_month"));
                list.add(msg);
                currentLastId = msg.getId();
            }
            // 遞歸向前月補齊
            if (currentLastId > 1 && list.size() < limit && month >= 201501) {
                int remain = limit - list.size();
                list.addAll(selectByGidMonth(gid, month - 1, currentLastId, remain));
            }
        }
    } catch (SQLException e) { e.printStackTrace(); }
    return list;
}

六、查詢結果演示

插入 100 條測試數據（28 條/月），每頁 20 條，從 201504 向前翻頁：

# 第1頁 (201504)
Msg{id=99, gid=99, month=201504} ... Msg{id=80, gid=99, month=201504} (20條)
# 第2頁 (跨月 201504→201503)
Msg{id=79, gid=99, month=201503} ... Msg{id=60, gid=99, month=201503} (20條)
# 第3頁 (201503)
Msg{id=59, gid=99, month=201503} ... Msg{id=40, gid=99, month=201502} (20條)
# 第4頁 (201502)
...
# 第5頁 (201501)
Msg{id=19, gid=99, month=201501} ... Msg{id=1, gid=99, month=201501}

七、架構總結與擴展

• 存儲擴展：按年增庫、掛載新磁盤，歷史數據不遷移
• 查詢優化：避免 SELECT * FROM msg WHERE gid=1 全表掃描，必須帶 create_month
• 分片算法擴展：季度分片、半月分片、業務自定義類繼承 PartitionByMonth
• 中間件選型：Mycat 適合中小規模、Java 生態；大規模建議 ShardingSphere Proxy / TiDB / PolarDB-X

分表易、查詢難。將「分片邏輯下沉中間件，跨片查詢邏輯上浮應用」是分庫分表架構的必經之路。