1. 首先我們要先建立 測試用的表,並且使用測試用的流水號Table幫我們建立測試用的資料。
Copy -- 如果表存在,則刪除它
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;
2. 我自己使用 Store Procedure 建立一個自動分區的程式
Copy 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 ;
此過程幫助數據庫管理員自動維護分區表,確保數據庫性能穩定,減少了手動管理分區的工作負擔。這對於大型數據庫非常重要,因為它們需要定期處理大量的數據,而手動管理分區將變得非常繁瑣和容易出錯。這個過程可以根據特定的數據和需求進行調整,以確保分區策略能夠有效地運作。
Copy -- 執行自動分區
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分區是一種強大的數據庫設計技術,特別適用於需要高效處理大量數據的應用場景。它可以幫助提高查詢性能、簡化數據管理和優化數據庫操作。但是,分區需要謹慎設計,選擇適當的分區鍵,以確保它能夠實現預期的性能優勢。