MySQL 動態創建分表存儲過程：按站點數據自動分表遷移

面對大表性能瓶頸，按業務維度（如站點 ID）水平拆分為多張結構相同的子表是常見優化手段。本文提供一個完整的存儲過程，實現：根據基礎表記錄數動態計算分表數量、批量創建子表、按範圍遷移數據。

業務場景

• 源表：tbl_base_syslog（系統日誌），含 site_id 字段
• 基準表：tbl_base_site（站點表），記錄總數決定分表數
• 目標：每 300 個站點建一張子表 tbl_base_syslog_N

存儲過程完整代碼

DELIMITER $$

CREATE PROCEDURE `sp_split_syslog_by_site`()
BEGIN
    DECLARE v_i INT DEFAULT 1;
    DECLARE v_siteCount INT;
    DECLARE v_sqlStr VARCHAR(2560);
    DECLARE v_sqlInsert VARCHAR(2560);

    -- 1. 獲取站點總數
    SELECT COUNT(0) INTO v_siteCount FROM tbl_base_site;

    -- 2. 循環創建分表並遷移數據
    WHILE (v_i - 1) * 300 < v_siteCount DO
        -- 2.1 動態建表
        SET v_sqlStr = CONCAT(
            'CREATE TABLE tbl_base_syslog', v_i, '(',
            'syslog_id INT(11) AUTO_INCREMENT PRIMARY KEY,',
            'create_user VARCHAR(32),',
            'description TEXT,',
            'create_time DATETIME,',
            'site_id INT(11),',
            'ip VARCHAR(64),',
            'version_id SMALLINT(2),',
            'module_identity VARCHAR(64),',
            'right_name VARCHAR(64)',
            ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
        );
        PREPARE stmt FROM v_sqlStr;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 2.2 遷移對應站點範圍的數據
        SET v_sqlInsert = CONCAT(
            'INSERT INTO tbl_base_syslog', v_i, '(',
            'syslog_id, create_user, description, create_time,',
            'site_id, ip, version_id, module_identity, right_name',
            ')',
            'SELECT syslog_id, create_user, description, create_time,',
            'site_id, ip, version_id, module_identity, right_name',
            'FROM tbl_base_syslog',
            'WHERE site_id IN (',
                'SELECT tbs.site_id FROM (',
                    'SELECT site_id FROM tbl_base_site LIMIT ', (v_i * 300), ', 300',
                ') AS tbs',
            ')',
            'ORDER BY syslog_id'
        );
        PREPARE stmt FROM v_sqlInsert;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET v_i = v_i + 1;
    END WHILE;
END$$

DELIMITER ;

關鍵技術點解析

1. 動態 SQL + PREPARE/EXECUTE

MySQL 存儲過程不支持直接執行變量 SQL，必須用 PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 三步走。

2. LIMIT 子查詢分頁取站點 ID

SELECT site_id FROM tbl_base_site LIMIT offset, count

配合 WHERE site_id IN (...) 精確篩選每批遷移的站點範圍。

3. 變量命名規範

使用 v_ 前綴區分局部變量，避免與列名衝突；原文用 @i 易混淆為用戶變量。

執行與驗證

-- 執行分表遷移
CALL sp_split_syslog_by_site();

-- 驗證結果
SHOW TABLES LIKE 'tbl_base_syslog%';
SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_NAME LIKE 'tbl_base_syslog%';

生產環境注意事項

1. 鎖表風險：大表遷移會長時間持有鎖，建議：
   • 低峰期執行
   • 或使用 pt-online-schema-change / gh-ost 在線遷移
2. 事務控制：每張子表遷移建議獨立事務，失敗可重試單表
3. 索引同步：源表有索引需在建表 SQL 中同步創建
4. 外鍵/觸發器：遷移前暫停相關觸發器、外鍵檢查
5. 應用層路由：分表後需修改應用查詢邏輯，根據 site_id 路由到對應子表

擴展：通用分表模板

CREATE PROCEDURE sp_generic_sharding(
    IN src_table VARCHAR(64),
    IN dst_prefix VARCHAR(64),
    IN shard_key VARCHAR(64),
    IN ref_table VARCHAR(64),
    IN batch_size INT
)
-- 通用化參數：源表、目標前綴、分片鍵、基準表、批次大小
-- 邏輯同上，動態拼接列名（需查詢 information_schema.COLUMNS）

動態分表存儲過程適合一次性歷史數據遷移；線上業務分表建議使用中間件（ShardingSphere、MyCat）或應用層路由，避免 DDL 阻塞業務。