SQL 效能調校 - Partition

在MySQL中，分區（Partitioning）是一種數據庫設計技術，允許將表拆分成多個子表，每個子表稱為分區，以更有效地管理和查詢大量數據。這種技術的主要目的是提高性能，減少查詢時間，並更好地處理大型數據集。

以下是MySQL分區的主要特點和相關概念：

  1. 分區類型：MySQL支持多種分區類型，包括範圍分區（RANGE）、列表分區（LIST）、哈希分區（HASH）、鍵分區（KEY），以及子分區。每種類型都有自己的使用場景和特點。

  2. 分區鍵（Partition Key）：這是用來決定數據如何分佈到不同分區的列或列組合。根據分區鍵的值，數據被映射到不同的分區。通常，分區鍵選擇的依據是那些經常被用作查詢條件的列，這樣可以提高查詢性能。

  3. 分區表：分區表是一個包含多個分區的表。每個分區都有自己的數據存儲區域，它們可以獨立地進行維護和優化。分區表的名稱和結構與普通表相同，但它在背後實際上由多個物理表構成。

  4. 分區管理：MySQL提供了一組SQL語句來管理分區，包括添加分區、刪除分區、重建分區等。這使得數據的維護和管理變得更簡單。

  5. 查詢性能：通過使用分區，可以僅查詢包含所需數據的分區，而不必掃描整個表。這在處理大型數據集時能夠大幅提高查詢性能。

  6. 日期範圍分區：一個常見的用例是按日期對表進行範圍分區。例如，可以根據每個月的數據創建一個新的分區，以便更容易管理歷史數據。

實戰測試

1. 首先我們要先建立 測試用的表，並且使用測試用的流水號Table幫我們建立測試用的資料。

-- 如果表存在，則刪除它
DROP TABLE IF EXISTS local_test.t_big_data_practise;

-- 創建一個名為t_big_data_practise的新表
-- 這個表用於存儲大數據實踐數據，包括id、名稱、內容、創建日期、類型和IP地址等字段
CREATE TABLE local_test.t_big_data_practise
(
    id          bigint auto_increment,  -- 自增ID
    name        varchar(20) null,       -- 名稱
    content_m   mediumtext null,        -- 中型文本內容
    content_t   text null,              -- 文本內容
    create_date datetime default CURRENT_TIMESTAMP not null,  -- 創建日期，默認為當前時間
    type        char(2) null,           -- 類型
    ip          bigint not null,        -- IP地址，非空
    primary key (id, create_date)       -- 主鍵由id和創建日期組成
);

-- 向表中插入數據
-- 這個INSERT語句從local_test.sequence_number表和隨機數生成數據，插入到t_big_data_practise表中
INSERT INTO t_big_data_practise(name, content_m, content_t, create_date, type, ip)
SELECT HEX(RAND() * 0xFFFFFFFF),                   -- 隨機數的HEX表示
       concat(HEX(RAND() * 0xFFFFFFFF), HEX(RAND() * 0xFFFFFFFF)),  -- 兩個隨機HEX的結合
       HEX(RAND() * 0xFFFFFFFF),                   -- 隨機數的HEX表示
       DATE_ADD('2023-01-01', INTERVAL RAND() * 364 DAY),          -- 隨機日期
       LEFT(HEX(RAND() * 0xFFFFFFFF), 2),         -- 隨機HEX表示的前兩個字符
       ROUND(RAND() * 4294967295)                 -- 隨機整數
FROM local_test.sequence_number a
         JOIN (SELECT id
               FROM local_test.sequence_number
               LIMIT 10) b;

-- 查詢t_big_data_practise表的數據統計信息
SELECT FORMAT(COUNT(1), 0) AS cnt,         -- 計算數據行數
     MIN(create_date) AS min_create_date,   -- 最早的創建日期
     MAX(create_date) AS max_create_date    -- 最晚的創建日期
FROM t_big_data_practise;

這個SQL執行了以下操作：

  1. 創建一個名為t_big_data_practise的新表，用於存儲大數據實踐數據。

  2. 使用隨機生成的數據插入數據到t_big_data_practise表中。

  3. 查詢t_big_data_practise表的統計信息，包括數據行數、最早的創建日期和最晚的創建日期。

這些操作用於模擬和測試大數據實踐場景，並通過隨機生成的數據填充表格。

2. 我自己使用 Store Procedure 建立一個自動分區的程式

DELIMITER ||

DROP PROCEDURE IF EXISTS auto_part ||

# `auto_part` 存儲過程具有以下輸入參數，它們控制了存儲過程的行為：
#
# 1. `IN_SCHEMA_NAME`：這是 VARCHAR(64) 型別的參數，代表要操作的數據庫模式（Schema）的名稱。
# 2. `IN_TABLE_NAME`：這是 VARCHAR(64) 型別的參數，代表要操作的表的名稱。
# 3. `OLD_DATE `：這是 VARCHAR(10) 型別的參數，代表要操作的表的最小(舊)日期。
# 3. `IN_CREATE`：這是 INT 型別的參數，代表要創建的新分區數量，以月為單位。
# 4. `IN_DELETE`：這是 INT 型別的參數，代表要刪除的過期分區數量，以月為單位。
# 這些參數允許您在呼叫存儲過程時指定要操作的數據庫、表，以及要創建和刪除的分區數量。
# 這使得存儲過程可以根據您的需求自動管理日期範圍分區。
#
CREATE PROCEDURE auto_part(IN_SCHEMA_NAME VARCHAR(64), IN_TABLE_NAME VARCHAR(64), OLD_DATE VARCHAR(10), IN_CREATE INT, IN_DELETE INT)
BEGIN
    -- 定義變數
    DECLARE isexist_partition VARCHAR(255) DEFAULT ''; -- 用來檢查表是否存在分區
    DECLARE new_p_condition DATE; -- 新分區的描述
    DECLARE new_p_name VARCHAR(255); -- 新分區的名稱
    DECLARE n_sysdate DATE; -- 存放始終代表下個月的第一天日期
    DECLARE min_partition_day DATE; -- 存放最小分區日期
    DECLARE del_partition_name VARCHAR(255); -- 要刪除的分區名稱

    -- 檢查表是否存在分區
    SELECT partition_name INTO isexist_partition FROM information_schema.partitions WHERE table_schema = IN_SCHEMA_NAME AND table_name = IN_TABLE_NAME LIMIT 1;

    IF isexist_partition <=> NULL THEN
        SET @p_check = 'The table has no partition。 STEP: Add table partition';
        SELECT @p_check;

        SET OLD_DATE = REPLACE(OLD_DATE, '-', '');

        -- 創建新分區的名稱和條件
        SET new_p_name = DATE_FORMAT(OLD_DATE, 'p%Y%m');
        SET new_p_condition = DATE_ADD(OLD_DATE, INTERVAL 1 MONTH);

        -- 創建 ALTER TABLE 語句以添加新的分區，這個語句將動態生成
        SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMA_NAME, '`.`', IN_TABLE_NAME, '`',
                          ' PARTITION BY RANGE (to_days(`create_date`)) (',
                          'PARTITION ', new_p_name, ' VALUES LESS THAN (to_days(\'', new_p_condition, '\')),',
                          'PARTITION pmax VALUES LESS THAN MAXVALUE);');

        -- 準備、執行並釋放動態生成的 SQL 語句
        PREPARE stmt FROM @SQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END IF;

    SET @STEP = 'STEP: check partition [ PASS ]';
    SELECT @STEP;

    -- 確保 n_sysdate 始終代表下個月的第一天
    SELECT CASE
               WHEN DAY(SYSDATE()) > 1 THEN DATE_SUB(SYSDATE(), INTERVAL (DAY(SYSDATE()) - 1) DAY)
               ELSE SYSDATE()
               END AS month_first_day
    INTO n_sysdate;

    -- 從信息模式中獲取最小分區日期，該日期是表的最早分區日期
    SELECT FROM_DAYS(partition_description) INTO min_partition_day
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMA_NAME
      AND table_name = IN_TABLE_NAME
      AND partition_description < 'MAXVALUE'
    ORDER BY partition_description ASC
    LIMIT 1;

    -- 添加新分區
    WHILE min_partition_day < n_sysdate DO
            SET new_p_name = DATE_FORMAT(min_partition_day, 'p%Y%m');
            SET new_p_condition = DATE_ADD(min_partition_day, INTERVAL 1 MONTH);

            -- 動態生成 ALTER TABLE 語句以添加新分區
            SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMA_NAME, '`.`', IN_TABLE_NAME,
                              '` REORGANIZE PARTITION `pmax` INTO  (PARTITION ', new_p_name,
                              ' VALUES LESS THAN (to_days(\'', new_p_condition,
                              '\')), PARTITION `pmax` VALUES LESS THAN MAXVALUE );');

            -- 準備、執行並釋放動態生成的 SQL 語句
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            SET min_partition_day = min_partition_day + INTERVAL 1 MONTH;
        END WHILE;

    SET @STEP = 'STEP: add historical date to today [ PASS ]';
    SELECT @STEP;

    -- 再次找到最小分區日期
    SELECT FROM_DAYS(partition_description) INTO min_partition_day
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMA_NAME
      AND table_name = IN_TABLE_NAME
      AND partition_description < 'MAXVALUE'
    ORDER BY partition_description ASC
    LIMIT 1;

    -- 刪除舊分區
    WHILE min_partition_day <= (n_sysdate - INTERVAL IN_DELETE MONTH) DO
            SET del_partition_name = DATE_FORMAT(min_partition_day - INTERVAL 1 MONTH, 'p%Y%m');

            -- 動態生成刪除分區的 SQL 語句
            SET @SQL = CONCAT('ALTER TABLE ', IN_SCHEMA_NAME, '.', IN_TABLE_NAME, ' DROP PARTITION ', del_partition_name);

            -- 準備、執行並釋放動態生成的 SQL 語句
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            SET min_partition_day = min_partition_day + INTERVAL 1 MONTH;
        END WHILE;

    SET @STEP = 'STEP: drop partition [ PASS ]';
    SELECT @STEP;
END ||

DELIMITER ;

auto_part 這個存儲過程旨在自動管理MySQL數據表的分區。分區是一種數據庫設計技術，可將表中的數據拆分為更小的、易於管理的部分。該過程主要完成以下三項操作：

  1. 新增分區：如果數據表尚未分區，它將動態計算最早數據記錄的日期，然後創建一個新的分區，以確保表中的數據按月份進行分區。這有助於提高查詢效率，並使數據管理更容易。

  2. 添加新分區：它定期檢查表中的數據，並在需要時添加新的分區。這是通過計算當前日期，找到最後一個分區的結束日期，然後創建一個新分區來實現的。這確保表能夠處理未來的數據。

  3. 刪除舊分區：為防止表過分區，該過程會刪除過時的分區。它會計算需要刪除的分區的結束日期，然後刪除它們，從而保持表的效能。

此過程幫助數據庫管理員自動維護分區表，確保數據庫性能穩定，減少了手動管理分區的工作負擔。這對於大型數據庫非常重要，因為它們需要定期處理大量的數據，而手動管理分區將變得非常繁瑣和容易出錯。這個過程可以根據特定的數據和需求進行調整，以確保分區策略能夠有效地運作。

3. 測試

-- 執行自動分區
call auto_part('local_test', 't_big_data_practise', (SELECT DATE(MIN(`create_date`)) FROM t_big_data_practise), 1, 3);

-- 檢查分區結果
SELECT *
FROM information_schema.partitions
WHERE table_schema = 'local_test'
  AND table_name = 't_big_data_practise'
ORDER BY partition_description ASC;

-- 查詢測試
select * from t_big_data_practise partition (p202308) order by create_date desc;

-- 解釋查詢內容，是否有使用到分區
explain select * from t_big_data_practise partition (p202308) order by create_date desc;
-- 結果我就不貼上來了.... 懶.....

總結

總之，MySQL分區是一種強大的數據庫設計技術，特別適用於需要高效處理大量數據的應用場景。它可以幫助提高查詢性能、簡化數據管理和優化數據庫操作。但是，分區需要謹慎設計，選擇適當的分區鍵，以確保它能夠實現預期的性能優勢。

PreviousSQL 效能調校 - ExplainNext排程 - Event

Last updated