CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATETIME NOT NULL,
    product_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (TO_DAYS(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

CREATE TABLE log_data (
    log_id BIGINT NOT NULL AUTO_INCREMENT,
    log_time DATETIME NOT NULL,
    user_id INT,
    action VARCHAR(50),
    PRIMARY KEY (log_id, log_time)
) PARTITION BY RANGE (YEAR(log_time)*100 + MONTH(log_time)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查看表的分區(qū)結構
SELECT partition_name, partition_expression, partition_description 
FROM information_schema.partitions 
WHERE table_name = 'sales';

-- 查看分區(qū)數(shù)據量
SELECT partition_name, table_rows 
FROM information_schema.partitions 
WHERE table_name = 'sales';

-- 在MAXVALUE分區(qū)前添加新分區(qū)
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 或者更簡單的方式(MySQL 5.7+)
ALTER TABLE sales ADD PARTITION (
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01'))
);

-- 合并相鄰分區(qū)
ALTER TABLE sales REORGANIZE PARTITION p202301, p202302 INTO (
    PARTITION p2023_q1 VALUES LESS THAN (TO_DAYS('2023-04-01'))
);

-- 重建分區(qū)優(yōu)化存儲
ALTER TABLE sales REBUILD PARTITION p202301, p202302;

-- 刪除單個分區(qū)
ALTER TABLE sales DROP PARTITION p202201;

-- 刪除多個分區(qū)
ALTER TABLE sales DROP PARTITION p202201, p202202, p202203;

-- 清空分區(qū)數(shù)據但保留分區(qū)結構
ALTER TABLE sales TRUNCATE PARTITION p202301;

DELIMITER //
CREATE PROCEDURE clean_time_partitions(
    IN p_table_name VARCHAR(64),
    IN p_retain_months INT
)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_part_name VARCHAR(64);
    DECLARE v_part_date DATE;
    DECLARE v_cutoff_date DATE;
    
    DECLARE v_cur CURSOR FOR 
        SELECT partition_name, 
               STR_TO_DATE(SUBSTRING(partition_name, 2), '%Y%m%d')
        FROM information_schema.partitions 
        WHERE table_schema = DATABASE() 
          AND table_name = p_table_name
          AND partition_name != 'pmax';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    SET v_cutoff_date = DATE_SUB(CURRENT_DATE(), INTERVAL p_retain_months MONTH);
    
    OPEN v_cur;
    
    read_loop: LOOP
        FETCH v_cur INTO v_part_name, v_part_date;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        IF v_part_date < v_cutoff_date THEN
            SET @sql = CONCAT('ALTER TABLE ', p_table_name, ' DROP PARTITION ', v_part_name);
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            
            SELECT CONCAT('Dropped partition: ', v_part_name) AS message;
        END IF;
    END LOOP;
    
    CLOSE v_cur;
END //
DELIMITER ;

-- 啟用事件調度器
SET GLOBAL event_scheduler = ON;

-- 創(chuàng)建每月執(zhí)行的事件
CREATE EVENT event_clean_old_partitions
ON SCHEDULE EVERY 1 MONTH STARTS '2023-05-01 02:00:00'
DO
BEGIN
    -- 保留最近12個月數(shù)據
    CALL clean_time_partitions('sales', 12);
    CALL clean_time_partitions('log_data', 12);
END;

-- 為分區(qū)表添加本地索引
ALTER TABLE sales ADD INDEX idx_product (product_id);

-- 查看分區(qū)索引使用情況
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 單獨備份特定分區(qū)
mysqldump -u username -p dbname sales --where="TO_DAYS(sale_date) < TO_DAYS('2023-02-01')" > sales_partition_q1.sql

-- 物理備份分區(qū)文件(需要InnoDB文件每表空間)
cp /var/lib/mysql/dbname/sales#P#p202301.ibd /backup/

-- 監(jiān)控分區(qū)表空間使用
SELECT partition_name, table_rows, 
       ROUND(data_length/(1024*1024),2) AS data_mb,
       ROUND(index_length/(1024*1024),2) AS index_mb
FROM information_schema.partitions
WHERE table_name = 'sales';

-- 監(jiān)控分區(qū)查詢命中率
SELECT * FROM sys.schema_table_statistics 
WHERE table_name = 'sales';

-- 確保WHERE條件使用分區(qū)鍵
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 避免在分區(qū)鍵上使用函數(shù)
-- 不好的寫法: WHERE YEAR(sale_date) = 2023
-- 好的寫法: WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'

-- 檢查當前分區(qū)數(shù)
SELECT COUNT(*) FROM information_schema.partitions WHERE table_name = 'sales';

???????-- 必要時合并歷史分區(qū)
ALTER TABLE sales REORGANIZE PARTITION p202201, p202202, p202203 INTO (
    PARTITION p2022_q1 VALUES LESS THAN (TO_DAYS('2022-04-01'))
);

-- 考慮調整分區(qū)粒度(如從按月改為按季度)
ALTER TABLE sales PARTITION BY RANGE (QUARTER(sale_date)) (
    PARTITION p2022_q1 VALUES LESS THAN (2),
    PARTITION p2022_q2 VALUES LESS THAN (3),
    -- ...
);

-- 或添加匯總表
CREATE TABLE sales_monthly_summary (
    month DATE PRIMARY KEY,
    total_amount DECIMAL(15,2),
    total_orders INT
);

-- 使用事件定期刷新匯總數(shù)據

-- 創(chuàng)建季度分區(qū)表
CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT,
    order_date DATETIME NOT NULL,
    customer_id INT,
    amount DECIMAL(12,2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (QUARTER(order_date)) (
    PARTITION p2022_q1 VALUES LESS THAN (2),
    PARTITION p2022_q2 VALUES LESS THAN (3),
    PARTITION p2022_q3 VALUES LESS THAN (4),
    PARTITION p2022_q4 VALUES LESS THAN (5),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

???????-- 創(chuàng)建歸檔過程
DELIMITER //
CREATE PROCEDURE archive_old_orders(IN retain_years INT)
BEGIN
    DECLARE cutoff_quarter INT;
    DECLARE cutoff_year INT;
    
    SET cutoff_year = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL retain_years YEAR));
    SET cutoff_quarter = QUARTER(DATE_SUB(CURRENT_DATE(), INTERVAL retain_years YEAR));
    
    -- 將舊數(shù)據歸檔到歷史表
    INSERT INTO orders_archive
    SELECT * FROM orders PARTITION (p2022_q1, p2022_q2)
    WHERE YEAR(order_date) < cutoff_year
       OR (YEAR(order_date) = cutoff_year AND QUARTER(order_date) < cutoff_quarter);
    
    -- 刪除已歸檔分區(qū)
    ALTER TABLE orders DROP PARTITION p2022_q1, p2022_q2;
    
    -- 添加新分區(qū)
    ALTER TABLE orders ADD PARTITION (
        PARTITION p2023_q1 VALUES LESS THAN (2)
    );
END //
DELIMITER ;