您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 水平拆分（分庫分表）實戰筆記：規則、優缺點、跨節點難題與 Java 案例

編輯：MySQL 綜合教學

MySQL 水平拆分（分庫分表）實戰筆記：規則、優缺點、跨節點難題與 Java 案例

1. 什麼是水平拆分

將單表按某字段規則（如用戶 ID 取模、時間範圍、字串 Hash）將行數據分散到多個表/庫，每個表包含部分數據。本質是按行切分。

2. 優缺點權衡

優點

表關聯多在資料庫端完成。
消除超大表與高負載瓶頸。
應用端架構改動較小。
事務處理相對簡單（單庫內）。
規則定義好後擴展性極強。

缺點

切分規則複雜，難抽象出通用規則滿足全庫。
後期維護難，人工定位數據困難。
模組耦合度高，遷移再拆分困難。

3. 常見切分規則

3.1 取模分片

電商、遊戲業務核心表多關聯會員 ID，適合按 user_id % N 分片。不同用戶數據落入不同庫，關聯查詢無跨庫交互。

3.2 地域分片

如全國劃 10 大片區（江浙滬、齊魯、兩廣等），業務量大的片區配高配伺服器，量小的配普配。資源規劃內部化，不對外宣稱。

地域分片架構圖

4. 應用整合視圖

水平拆分與應用整合視圖

5. 拆分後的棘手問題與應對

5.1 分布式事務

數據分佈多庫後，原本單庫事務可能跨庫。MySQL 5.0+ 僅 InnoDB 支援 XA 分布式事務，但效能差、異常處理難。
變通方案：應用層協調多個單庫本地事務，應用需具備足夠健壯性。

5.2 跨節點 JOIN

數據源分佈不同庫，無法直接 JOIN。
方案：應用層先查驅動表結果集，再按結果集去被驅動庫查數據，最後應用層組裝。雖有效能損耗，但因單庫負載下降，單查詢響應反而可能更快。Federated 引擎可嘗試但風險大（遠端結構變更本地不同步）。

5.3 跨節點排序分頁

數據源分佈多節點，單 SQL 無法完成排序分頁。
方案：應用層並行從各節點取數據，合併排序後分頁。無驅動/被驅動順序關係，並行度高，效能損耗比跨庫 JOIN 更小。應用層需消耗更多記憶體合併結果集。

5.4 核心觀點

所有跨節點難題建議在應用層解決。應用易擴展，資料庫擴展代價大且難避免新問題。為整體擴展性，讓應用承擔更多邏輯是值得的取捨。

6. 實戰案例：Java 實現按 User ID 取模路由

6.1 建立 3 個 MySQL 實例（3307/3308/3309）

參考：多實例部署指南

6.2 建庫、建表、授權

CREATE DATABASE hwdb DEFAULT CHARSET utf8;
CREATE TABLE uc_user(
    user_id BIGINT PRIMARY KEY,
    uc_name VARCHAR(200),
    created_time DATETIME
) ENGINE=InnoDB CHARSET utf8;

GRANT INSERT,UPDATE,DELETE,SELECT ON hwdb.* TO 'tim'@'192.168.%' IDENTIFIED BY 'timgood2013';

6.3 Java 路由與寫入代碼

public class MySQLTest {
    public String getDBPort(long user_id) {
        long mod = user_id % 3;
        if (mod == 1) return "3308";
        if (mod == 2) return "3309";
        return "3307";
    }
    public Connection getConn(String port) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://192.168.52.130:" + port + "/hwdb";
        Connection conn = DriverManager.getConnection(url, "tim", "timgood2013");
        System.out.println("the current db is :" + url);
        return conn;
    }
    public int insert(Connection conn, BigInteger user_id, String name) throws SQLException {
        String sql = "INSERT INTO hwdb.uc_user(user_id,uc_name,created_time) VALUES(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, user_id.longValue());
        ps.setString(2, name);
        ps.setString(3, getTimeByCalendar());
        return ps.executeUpdate();
    }
    // ... getTimeByCalendar() 省略
}