MySQL數(shù)據(jù)庫實現(xiàn)批量表分區(qū)完整示例

更新時間：2025年05月23日 10:53:00 作者：0xfather

通俗地講表分區(qū)是將一大表,根據(jù)條件分割成若干個小表,這篇文章主要介紹了MySQL數(shù)據(jù)庫實現(xiàn)批量表分區(qū)的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

一、表分區(qū)條件
二、常規(guī)表和分區(qū)表的區(qū)別
三、表分區(qū)的創(chuàng)建
四、將既有表轉換分區(qū)表腳本
五、批量轉換表為分區(qū)表
六、表分區(qū)維護：添加表分區(qū)
七、批量維護：批量添加表分區(qū)
總結

對于單表大數(shù)據(jù)量大的問題，如果數(shù)據(jù)支持分片，使用表分區(qū)是個不錯的選擇，那么MySQL是如何實現(xiàn)表分區(qū)的？

一、表分區(qū)條件

1.數(shù)據(jù)庫存儲引擎支持：InnoDB 和 MyISAM引擎

2.數(shù)據(jù)庫版本支持：MySQL 5.1以后（版本不同，具體的特性支持可能會有所不同）

3.數(shù)據(jù)必須有一個或多個分區(qū)鍵：作為分區(qū)的鍵（字段）必須是主鍵的一部分（聯(lián)合主鍵）

4.分區(qū)定義：每個分區(qū)必須明確地定義數(shù)據(jù)范圍

5.分區(qū)維護：隨著時間推移，可能需要添加新的分區(qū)或刪除舊的分區(qū)，以保持數(shù)據(jù)庫的性能和結構

二、常規(guī)表和分區(qū)表的區(qū)別

常規(guī)表和分區(qū)表對比

	常規(guī)表	分區(qū)表
數(shù)據(jù)結構	所有數(shù)據(jù)存儲在單一數(shù)據(jù)文件	數(shù)據(jù)被邏輯上分成多個部分，可能存放在多個文件甚至多個磁盤
查詢優(yōu)化	查詢時默認掃描整表數(shù)據(jù)	只訪問相關分區(qū)數(shù)據(jù)
I/O操作	添加、刪除或修改行操作直接作用于整表	只對單個分區(qū)操作，不影響其他分區(qū)數(shù)據(jù)
備份恢復	通常備份整表數(shù)據(jù)	可以單獨備份或恢復特定分區(qū)
存儲管理	所有數(shù)據(jù)集中存儲	數(shù)據(jù)分散到多個分區(qū)
擴展性	隨著數(shù)據(jù)量增長，可能會遇到性能瓶頸	更容易水平擴展，可以通過增加新分區(qū)來處理更大的數(shù)據(jù)集，而不需要改變應用程序邏輯
限值和復雜性	相對簡單，沒有特殊的創(chuàng)建或維護要求	設計和實現(xiàn)更加復雜，需要考慮如何正確地設置分區(qū)策略以滿足業(yè)務需求

從上面看分區(qū)表是否有很大的優(yōu)勢？但是同樣分區(qū)表也存在一些限值：

分區(qū)表的限制

	常規(guī)表	分區(qū)表
外鍵約束	√	×
全文索引	√	×（5.6以前版本） ?（5.6以后版本）
臨時表	√	×
列修改	√	×
特定的ALTER TABLE語句	√	×（修改主鍵、唯一鍵等）
性能影響	數(shù)據(jù)量影響	添加、刪除、合并表分區(qū)，可能會導致鎖表從而影響性能
備份和恢復工具支持	通常工具都支持	不是所有的備份和恢復工具都完全支持分區(qū)表的所有特性
主備服務器數(shù)據(jù)復制	無特殊要求	必須保證分區(qū)規(guī)則一致性，任何不匹配都可能導致復制失敗或數(shù)據(jù)不一致
分區(qū)類型限制	無	存儲引擎可能限制分區(qū)類型
查詢優(yōu)化器的行為	簡單索引	表分區(qū)+索引，特情情況的復雜查詢可能會有表分區(qū)裁剪失效問題

三、表分區(qū)的創(chuàng)建

表分區(qū)創(chuàng)建關鍵的三個點：創(chuàng)建表、設置分區(qū)鍵、設置分片策略

示例：

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

創(chuàng)建表名：sales

分區(qū)鍵：sale_date字段的year()結果，即sale_date字段的年份

分片策略：p0分區(qū)存儲小于2020年數(shù)據(jù)、 p1分區(qū)存儲小于2021年數(shù)據(jù)、p2分區(qū)存儲小于2022年數(shù)據(jù)、p3分區(qū)存儲其他年份數(shù)據(jù)（注意：這里的數(shù)據(jù)“擋板”很重要，設置時一定要小心）

注意：這里的分片策略是“LESS THAN xxx”，表示小于后面策略的數(shù)據(jù)數(shù)據(jù)，如上面就是小于指定年份的數(shù)據(jù)歸屬于這個分區(qū)，因此上面用“數(shù)據(jù)擋板”這個詞

四、將既有表轉換分區(qū)表腳本

因為表的創(chuàng)建結構不同，因此既有表不能直接轉換為分區(qū)表，要實現(xiàn)既有表轉換為分區(qū)表，需要經(jīng)過以下幾步：

1.根據(jù)既有表創(chuàng)建同字段結構的新分區(qū)表、定義好相關分區(qū)策略

2.遷移數(shù)據(jù)到分區(qū)表

3.刪除舊表、并將分區(qū)表改名為原表

具體實現(xiàn)腳本如下：

CREATE DEFINER=`root`@`%` PROCEDURE `convert_table_to_partition`(IN tbl_name VARCHAR(200),OUT out_status INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    -- 輸出狀態(tài)，開始執(zhí)行狀態(tài) 100，執(zhí)行成功狀態(tài) 200，執(zhí)行失敗狀態(tài) 50
    SET out_status = 100;
    
    -- 創(chuàng)建一個新的空表，不包含表分區(qū)（要轉換為分區(qū)表，必須是空表）
    SET @create_empty_tbl_sql = CONCAT(
        'CREATE TABLE ', tbl_name, '_partitioned LIKE ', tbl_name, ';'
    );
    PREPARE stmt FROM @create_empty_tbl_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 獲取所有唯一的 year_no 和 month_no 組合作為構建分區(qū)定義分區(qū)鍵
    SET @partition_def = '';
    SET @query = CONCAT(
        'SELECT GROUP_CONCAT(
            CONCAT("PARTITION p_", year_no, "_", LPAD(month_no, 2, "0"), 
                   " VALUES LESS THAN (", 
                   CASE WHEN month_no = 12 THEN year_no + 1 ELSE year_no END, ", ", 
                   CASE WHEN month_no = 12 THEN 1 ELSE month_no + 1 END, ")")
            ORDER BY year_no, month_no SEPARATOR ",\n"
        ) INTO @partition_def
        FROM (
            SELECT DISTINCT year_no, month_no
            FROM ', tbl_name, '
            ORDER BY year_no, month_no
        ) AS unique_years_months;'
    );
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 調試信息：輸出分區(qū)定義字符串
    --SELECT tbl_name,@partition_def;

    -- 檢查是否有有效的分區(qū)定義
    IF @partition_def IS NULL OR @partition_def = '' THEN
        SELECT tbl_name,'No data found for partitioning. Skipping partition creation and data migration.';
        -- 空表則直接添加 p_max 分區(qū)用于捕獲未來數(shù)據(jù)
        SET @partition_def = '\nPARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)';
    ELSE
        -- 添加 p_max 分區(qū)用于捕獲未來數(shù)據(jù)
        SET @partition_def = CONCAT(@partition_def, ',\nPARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)');
    END IF;

    -- 使用 ALTER TABLE 添加分區(qū)定義
    SET @add_partitions_sql = CONCAT(
        'ALTER TABLE ', tbl_name, '_partitioned 
        PARTITION BY RANGE COLUMNS(year_no, month_no) (', @partition_def, ');'
    );

    -- 調試信息：輸出添加分區(qū)的 SQL 語句
    --SELECT tbl_name,@add_partitions_sql;

    PREPARE stmt FROM @add_partitions_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 遷移數(shù)據(jù)到新的分區(qū)表
    SET @insert_into_partitioned_sql = CONCAT(
        'INSERT INTO ', tbl_name, '_partitioned SELECT * FROM ', tbl_name, ';'
    );

    -- 調試信息：輸出插入數(shù)據(jù)的 SQL 語句
    -- SELECT tbl_name,@insert_into_partitioned_sql;

    PREPARE stmt FROM @insert_into_partitioned_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 驗證數(shù)據(jù)遷移是否成功
    SET @count_original = CONCAT('SELECT COUNT(*) INTO @count_original FROM ', tbl_name);
    PREPARE stmt FROM @count_original;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @count_partitioned = CONCAT('SELECT COUNT(*) INTO @count_partitioned FROM ', tbl_name, '_partitioned');
    PREPARE stmt FROM @count_partitioned;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 比較原表和新分區(qū)表的數(shù)據(jù)行數(shù)
    -- SELECT tbl_name,@count_original, @count_partitioned;

    
    -- 如果數(shù)據(jù)遷移成功，刪除舊表并重命名新表（無論是否有數(shù)據(jù)，均刪除緩存表）
    IF @count_original = @count_partitioned THEN
        -- 刪除舊表
        SET @drop_old_table_sql = CONCAT('DROP TABLE IF EXISTS ', tbl_name);
        PREPARE stmt FROM @drop_old_table_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 重命名新表為舊表名
        SET @rename_tables_sql = CONCAT('RENAME TABLE ', tbl_name, '_partitioned TO ', tbl_name);
        PREPARE stmt FROM @rename_tables_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- SELECT tbl_name,'Table conversion and data migration completed successfully.';
        SET out_status = 200;
    ELSE
        -- SELECT tbl_name,'Data migration failed, check the logs for more information.';
        SET out_status = 50;
    END IF;
    
END

上面腳本是一個完整的將既有表轉換為以“year_no”和“month_no”字段為分區(qū)鍵的分區(qū)表，主要有以下幾步操作：

1）以既有表為模板創(chuàng)建一個新的空表，不包含表分區(qū)（要轉換為分區(qū)表，必須是空表）

2）獲取所有唯一的 year_no 和 month_no 組合并構建分區(qū)定義字符串（對既有數(shù)據(jù)分析需要劃分的分區(qū)策略）

3）檢查是否有效的分區(qū)定義，若無分區(qū)定義，強烈建議則創(chuàng)建一個默認的分區(qū)策略p_max以存儲未來的數(shù)據(jù)

4）更新空表，添加相關的分區(qū)策略

5）遷移歷史數(shù)據(jù)到分區(qū)表

6）數(shù)據(jù)遷移校驗（驗證數(shù)據(jù)完整性）

7）刪除舊表（回收表名）

8）將新分區(qū)表改名為原表名

五、批量轉換表為分區(qū)表

批量將常規(guī)表轉換為分區(qū)表，具體腳本如下：

CREATE DEFINER=`root`@`%` PROCEDURE `tables_convert_to_partition`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE convert_status INT;
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'ai_result_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
      
        -- 調試信息：輸出正在轉換的表
        SELECT tbl_name,'covering...';

        CALL convert_table_to_partition(tbl_name,@status);
        
        SET convert_status = @status;
        
        -- 根據(jù)返回的狀態(tài)進行相應的處理
        CASE convert_status
            WHEN 100 THEN
                -- 開始狀態(tài)，可以忽略，因為這是預期的初始狀態(tài)
                SELECT tbl_name, 'Started conversion.';
            WHEN 200 THEN
                -- 成功完成
                SELECT tbl_name, 'Conversion and data migration completed successfully.';
            WHEN 50 THEN
                -- 失敗
                SELECT tbl_name, 'Data migration failed. Check the logs for more information.';
            ELSE
                -- 未知狀態(tài)
                SELECT tbl_name, CONCAT('Unknown status: ', status);
        END CASE;
    END LOOP;

    CLOSE cur;
END

這里是以“ai_result_”開頭的表為例，將所有相關表轉換為分區(qū)表，在執(zhí)行這個存儲過程時，操作用戶必須要有information_schema數(shù)據(jù)庫讀取權限，這樣才能查詢出相關的表名從而進行轉換。

該腳本建議為一次性執(zhí)行腳本，避免對標頻繁轉換，防止鎖表（因此表名前綴已固定在代碼中，需根據(jù)自身需求修改）

六、表分區(qū)維護：添加表分區(qū)

表分區(qū)經(jīng)過上面的過程創(chuàng)建，理論上已經(jīng)對歷史數(shù)據(jù)進行表分區(qū)，對未來數(shù)據(jù)也能存儲到p_max分區(qū)，但是p_max分區(qū)數(shù)據(jù)如果不進行維護，同樣會有數(shù)據(jù)量過大問題，因此我們需要定期切割p_max分區(qū)并增加相關表分區(qū)，這個操作需要在數(shù)據(jù)進入之前執(zhí)行，具體執(zhí)行腳本如下：

CREATE DEFINER=`root`@`%` PROCEDURE `add_monthly_partitions`(IN tbl_name VARCHAR(64), IN year_no INT, IN month_no INT,OUT out_status INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    
    -- 輸出狀態(tài)，開始執(zhí)行狀態(tài) 100，執(zhí)行成功狀態(tài) 200，執(zhí)行失敗狀態(tài) 50
    SET out_status = 100;
    
    -- 檢查待添加的分區(qū)是否已經(jīng)存在
    SET @partition_exists = EXISTS (SELECT 1 FROM information_schema.PARTITIONS 
                                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name 
                                      AND PARTITION_NAME = CONCAT('p_', year_no, '_', LPAD(month_no, 2, '0')));
                                      
    IF @partition_exists THEN
        -- 如果分區(qū)已存在，直接返回消息
        -- SELECT CONCAT('Partition p_', year_no, '_', LPAD(month_no, 2, '0'), ' already exists. No action taken.') AS message;
        SET out_status = 200;
    ELSE
      -- 檢查表中是否已經(jīng)存在 p_max 分區(qū)
      SET @has_p_max = EXISTS (SELECT 1 FROM information_schema.PARTITIONS 
                               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name 
                                 AND PARTITION_NAME = 'p_max');

      -- 構建添加分區(qū)的 SQL 語句
      IF @has_p_max THEN
          -- 如果存在 p_max 分區(qū)，則重新組織分區(qū)，將 p_max 分割成新分區(qū)和更新后的 p_max
          SET @reorganize_partition_sql = CONCAT(
              'ALTER TABLE ', tbl_name, ' REORGANIZE PARTITION p_max INTO (
                  PARTITION p_', year_no, '_', LPAD(month_no, 2, '0'), 
                  ' VALUES LESS THAN (', 
                  CASE WHEN month_no = 12 THEN year_no + 1 ELSE year_no END, ', ', 
                  CASE WHEN month_no = 12 THEN 1 ELSE month_no + 1 END, '),
                  PARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)
              )'
          );
          PREPARE stmt FROM @reorganize_partition_sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- SELECT CONCAT('Partition p_', year_no, '_', LPAD(month_no, 2, '0'), ' and updated p_max added successfully.') AS message;
          SET out_status = 200;
      ELSE
          -- 如果不存在 p_max 分區(qū)，則直接添加新分區(qū)
          SET @add_partition_sql = CONCAT(
              'ALTER TABLE ', tbl_name, ' ADD PARTITION (
                  PARTITION p_', year_no, '_', LPAD(month_no, 2, '0'), 
                  ' VALUES LESS THAN (', 
                  CASE WHEN month_no = 12 THEN year_no + 1 ELSE year_no END, ', ', 
                  CASE WHEN month_no = 12 THEN 1 ELSE month_no + 1 END, ')
              )'
          );
          PREPARE stmt FROM @add_partition_sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
          
          -- SELECT CONCAT('Partition p_', year_no, '_', LPAD(month_no, 2, '0'), ' added successfully.') AS message;
          SET out_status = 200;
      END IF;
    END IF;
END

上面腳本的執(zhí)行過程如下：

1）檢測待添加的分區(qū)是否已存在（已存在則不添加，不存在才添加）

2）檢測表中是否存在p_max 分區(qū)（檢測待切割分區(qū)，若存在則切割分區(qū)，若不存在這創(chuàng)建分區(qū)）

3）切割p_max分區(qū)為新分區(qū)和新的p_max分區(qū)（此處會調整p_max分區(qū)的分片策略）

七、批量維護：批量添加表分區(qū)

批量給相關表添加表分區(qū)，具體腳本如下：

CREATE DEFINER=`root`@`%` PROCEDURE `tables_add_monthly_partition`(IN tbl_prefix VARCHAR(64), IN year_no INT, IN month_no INT,OUT out_status INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE CONCAT(tbl_prefix, '%');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 輸出狀態(tài)，開始執(zhí)行狀態(tài) 100，執(zhí)行成功狀態(tài) 200，執(zhí)行失敗狀態(tài) 50
    SET out_status = 100;

    -- 打開游標
    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 檢查表是否已經(jīng)是分區(qū)表
        SET @is_partitioned = EXISTS (SELECT 1 FROM information_schema.PARTITIONS 
                                      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name);

        IF NOT @is_partitioned THEN
            -- 如果表不是分區(qū)表，先調用 convert_table_to_partition 進行轉換
            CALL convert_table_to_partition(tbl_name,@status);
            
            -- 轉換后再次檢查是否成功轉換為分區(qū)表
            SET @is_partitioned = EXISTS (SELECT 1 FROM information_schema.PARTITIONS 
                                          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name);
            
            IF NOT @is_partitioned THEN
                -- 如果轉換失敗，跳過后續(xù)操作并輸出錯誤信息
                SELECT CONCAT('Failed to convert table ', tbl_name, ' to partitioned. Skipping.') AS message;
                SET out_status = 50;
                ITERATE read_loop;
            END IF;
        END IF;

        -- 調用 add_monthly_partitions 為當前表添加分區(qū)
        CALL add_monthly_partitions(tbl_name, year_no, month_no,@status);

        -- 可選：輸出操作結果（用于調試）
        -- SELECT CONCAT('Processed table: ', tbl_name) AS status;
    END LOOP;

    -- 關閉游標
    CLOSE cur;

    -- 輸出完成信息
    -- SELECT CONCAT('Batch partition addition completed for tables with prefix "', tbl_prefix, '".') AS message;
    SET out_status = 200;
END

批量添加表分區(qū)需要傳入相關表前綴，如上面示例中的“ai_result_”，此腳本會將非分區(qū)表轉換為分區(qū)表，再給分區(qū)表添加相應的表分區(qū)，具體執(zhí)行過程如下：

1）獲取所有相關表

2）遍歷判斷表是否是分區(qū)表

3）非分區(qū)表被轉換為分區(qū)表

4）給分區(qū)表添加表分區(qū)策略

該腳本請慎重執(zhí)行，上面我們有常規(guī)表和分區(qū)表的對比，執(zhí)行腳本很簡單（批量自動完成），但執(zhí)行的后果請慎重考慮！