MySQL分区表策略与实战应用

在处理大数据量的MySQL数据库时，分区表是提升查询性能、优化存储管理的重要技术。本文将通过实战案例详细介绍MySQL分区表的各种策略和最佳实践。

分区表基础概念

1. 分区表原理

-- 查看MySQL分区支持情况
SHOW PLUGINS;
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM information_schema.PLUGINS 
WHERE PLUGIN_NAME = 'partition';

-- 查看分区相关变量
SHOW VARIABLES LIKE '%partition%';

-- 创建简单的分区表示例
CREATE TABLE sales_data (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region VARCHAR(50) NOT NULL,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查看分区信息
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'sales_data'
    AND PARTITION_NAME IS NOT NULL;

2. 分区类型详解

-- 1. RANGE分区 - 按范围分区
CREATE TABLE orders_range (
    id INT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- 2. LIST分区 - 按列表值分区
CREATE TABLE users_list (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    region VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北', '山西', '内蒙古'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江', '安徽', '福建', '江西', '山东'),
    PARTITION p_south VALUES IN ('河南', '湖北', '湖南', '广东', '广西', '海南'),
    PARTITION p_west VALUES IN ('重庆', '四川', '贵州', '云南', '西藏', '陕西', '甘肃', '青海', '宁夏', '新疆'),
    PARTITION p_northeast VALUES IN ('辽宁', '吉林', '黑龙江')
);

-- 3. HASH分区 - 按哈希值分区
CREATE TABLE user_activities_hash (
    id INT AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(50),
    activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;

-- 4. KEY分区 - 按主键哈希分区
CREATE TABLE sessions_key (
    session_id VARCHAR(64) NOT NULL,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    PRIMARY KEY (session_id)
) PARTITION BY KEY() PARTITIONS 16;

-- 5. 复合分区 - RANGE和HASH组合
CREATE TABLE logs_composite (
    id INT AUTO_INCREMENT,
    log_date DATE NOT NULL,
    user_id INT NOT NULL,
    log_level ENUM('INFO', 'WARN', 'ERROR'),
    message TEXT,
    PRIMARY KEY (id, log_date, user_id)
) PARTITION BY RANGE (YEAR(log_date))
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 4 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

实战分区策略

1. 时间序列数据分区

-- 创建日志表的时间分区
CREATE TABLE system_logs (
    id BIGINT AUTO_INCREMENT,
    log_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    log_level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL') NOT NULL,
    module VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    user_id INT,
    ip_address VARCHAR(45),
    request_id VARCHAR(64),
    PRIMARY KEY (id, log_time),
    INDEX idx_module_time (module, log_time),
    INDEX idx_user_time (user_id, log_time),
    INDEX idx_level_time (log_level, log_time)
) PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p20230801 VALUES LESS THAN (TO_DAYS('2023-08-02')),
    PARTITION p20230802 VALUES LESS THAN (TO_DAYS('2023-08-03')),
    PARTITION p20230803 VALUES LESS THAN (TO_DAYS('2023-08-04')),
    PARTITION p20230804 VALUES LESS THAN (TO_DAYS('2023-08-05')),
    PARTITION p20230805 VALUES LESS THAN (TO_DAYS('2023-08-06')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入测试数据
INSERT INTO system_logs (log_time, log_level, module, message, user_id, ip_address) VALUES
('2023-08-01 10:00:00', 'INFO', 'auth', '用户登录成功', 1001, '192.168.1.100'),
('2023-08-01 10:05:00', 'WARN', 'payment', '支付超时', 1002, '192.168.1.101'),
('2023-08-02 09:30:00', 'ERROR', 'database', '连接失败', NULL, '192.168.1.102'),
('2023-08-03 14:20:00', 'INFO', 'order', '订单创建', 1003, '192.168.1.103'),
('2023-08-04 16:45:00', 'FATAL', 'system', '系统崩溃', NULL, '192.168.1.104');

-- 查询特定日期的日志（分区裁剪）
EXPLAIN PARTITIONS 
SELECT * FROM system_logs 
WHERE log_time >= '2023-08-02 00:00:00' 
    AND log_time < '2023-08-03 00:00:00';

-- 查看分区数据分布
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH/1024/1024, 2) as 'DATA_SIZE_MB',
    ROUND(INDEX_LENGTH/1024/1024, 2) as 'INDEX_SIZE_MB'
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'system_logs'
    AND PARTITION_NAME IS NOT NULL;

2. 用户数据分区策略

-- 按用户ID范围分区的用户行为表
CREATE TABLE user_behaviors (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    behavior_type ENUM('view', 'click', 'purchase', 'share', 'favorite') NOT NULL,
    target_id INT NOT NULL,
    target_type VARCHAR(20) NOT NULL,
    behavior_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_id VARCHAR(64),
    device_type VARCHAR(20),
    PRIMARY KEY (id, user_id),
    INDEX idx_user_behavior_time (user_id, behavior_time),
    INDEX idx_target (target_type, target_id),
    INDEX idx_behavior_time (behavior_type, behavior_time)
) PARTITION BY RANGE (user_id) (
    PARTITION p_user_1_10000 VALUES LESS THAN (10001),
    PARTITION p_user_10001_20000 VALUES LESS THAN (20001),
    PARTITION p_user_20001_30000 VALUES LESS THAN (30001),
    PARTITION p_user_30001_40000 VALUES LESS THAN (40001),
    PARTITION p_user_40001_50000 VALUES LESS THAN (50001),
    PARTITION p_user_max VALUES LESS THAN MAXVALUE
);

-- 按地区分区的用户表
CREATE TABLE users_by_region (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    region_code VARCHAR(10) NOT NULL,
    city VARCHAR(50),
    registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_time TIMESTAMP,
    status TINYINT DEFAULT 1,
    PRIMARY KEY (id, region_code),
    UNIQUE KEY uk_username_region (username, region_code),
    INDEX idx_email (email),
    INDEX idx_registration_time (registration_time)
) PARTITION BY LIST COLUMNS(region_code) (
    PARTITION p_beijing VALUES IN ('BJ'),
    PARTITION p_shanghai VALUES IN ('SH'),
    PARTITION p_guangdong VALUES IN ('GD'),
    PARTITION p_jiangsu VALUES IN ('JS'),
    PARTITION p_zhejiang VALUES IN ('ZJ'),
    PARTITION p_shandong VALUES IN ('SD'),
    PARTITION p_others VALUES IN (DEFAULT)
);

-- 插入测试数据
INSERT INTO user_behaviors (user_id, behavior_type, target_id, target_type, session_id, device_type) VALUES
(5000, 'view', 1001, 'product', 'sess_001', 'mobile'),
(15000, 'click', 2001, 'ad', 'sess_002', 'desktop'),
(25000, 'purchase', 3001, 'product', 'sess_003', 'mobile'),
(35000, 'share', 4001, 'article', 'sess_004', 'tablet'),
(45000, 'favorite', 5001, 'product', 'sess_005', 'mobile');

-- 查询特定用户范围的行为（利用分区裁剪）
EXPLAIN PARTITIONS 
SELECT * FROM user_behaviors 
WHERE user_id BETWEEN 10000 AND 20000 
    AND behavior_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

3. 订单数据分区实战

-- 创建按月分区的订单表
CREATE TABLE orders_monthly (
    id BIGINT AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_status TINYINT NOT NULL DEFAULT 1,
    payment_method VARCHAR(20),
    shipping_address TEXT,
    order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payment_time TIMESTAMP NULL,
    shipping_time TIMESTAMP NULL,
    completed_time TIMESTAMP NULL,
    PRIMARY KEY (id, order_time),
    UNIQUE KEY uk_order_no_time (order_no, order_time),
    INDEX idx_user_order_time (user_id, order_time),
    INDEX idx_status_time (order_status, order_time),
    INDEX idx_product_time (product_id, order_time)
) PARTITION BY RANGE (YEAR(order_time) * 100 + MONTH(order_time)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION p202304 VALUES LESS THAN (202305),
    PARTITION p202305 VALUES LESS THAN (202306),
    PARTITION p202306 VALUES LESS THAN (202307),
    PARTITION p202307 VALUES LESS THAN (202308),
    PARTITION p202308 VALUES LESS THAN (202309),
    PARTITION p202309 VALUES LESS THAN (202310),
    PARTITION p202310 VALUES LESS THAN (202311),
    PARTITION p202311 VALUES LESS THAN (202312),
    PARTITION p202312 VALUES LESS THAN (202401),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 创建订单详情的复合分区表
CREATE TABLE order_details_composite (
    id BIGINT AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    sku VARCHAR(50),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    total_price DECIMAL(10,2) NOT NULL,
    order_time TIMESTAMP NOT NULL,
    PRIMARY KEY (id, order_time, order_id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    INDEX idx_sku (sku)
) PARTITION BY RANGE (YEAR(order_time))
SUBPARTITION BY HASH(order_id) SUBPARTITIONS 4 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入测试订单数据
INSERT INTO orders_monthly (
    order_no, user_id, product_id, quantity, unit_price, total_amount, 
    order_status, payment_method, order_time
) VALUES
('ORD202308001', 1001, 2001, 2, 99.99, 199.98, 2, 'alipay', '2023-08-01 10:30:00'),
('ORD202308002', 1002, 2002, 1, 299.00, 299.00, 1, 'wechat', '2023-08-15 14:20:00'),
('ORD202309001', 1003, 2003, 3, 49.99, 149.97, 3, 'credit_card', '2023-09-05 09:15:00'),
('ORD202309002', 1004, 2004, 1, 599.00, 599.00, 2, 'alipay', '2023-09-20 16:45:00');

-- 查询特定月份的订单（分区裁剪）
EXPLAIN PARTITIONS 
SELECT 
    order_no,
    user_id,
    total_amount,
    order_status,
    order_time
FROM orders_monthly 
WHERE order_time >= '2023-08-01 00:00:00' 
    AND order_time < '2023-09-01 00:00:00'
    AND order_status = 2;

-- 统计各分区的数据量
SELECT 
    PARTITION_NAME as '分区名',
    TABLE_ROWS as '行数',
    ROUND(DATA_LENGTH/1024/1024, 2) as '数据大小MB',
    ROUND(INDEX_LENGTH/1024/1024, 2) as '索引大小MB',
    ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) as '总大小MB'
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'orders_monthly'
    AND PARTITION_NAME IS NOT NULL
ORDER BY PARTITION_ORDINAL_POSITION;

分区管理操作

1. 动态分区管理

-- 添加新分区
ALTER TABLE orders_monthly 
ADD PARTITION (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403)
);

-- 删除旧分区（注意：会删除数据）
ALTER TABLE orders_monthly DROP PARTITION p202301;

-- 重组分区（分割分区）
ALTER TABLE orders_monthly 
REORGANIZE PARTITION p_future INTO (
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p202404 VALUES LESS THAN (202405),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 合并分区
ALTER TABLE orders_monthly 
REORGANIZE PARTITION p202401, p202402 INTO (
    PARTITION p2024_q1 VALUES LESS THAN (202403)
);

-- 交换分区（用于数据迁移）
-- 首先创建相同结构的临时表
CREATE TABLE orders_temp LIKE orders_monthly;
ALTER TABLE orders_temp REMOVE PARTITIONING;

-- 将分区数据交换到临时表
ALTER TABLE orders_monthly 
EXCHANGE PARTITION p202308 WITH TABLE orders_temp;

-- 查看交换结果
SELECT COUNT(*) as temp_table_rows FROM orders_temp;
SELECT COUNT(*) as partition_rows FROM orders_monthly PARTITION(p202308);

2. 分区维护存储过程

-- 创建自动分区管理存储过程
DELIMITER //
CREATE PROCEDURE ManagePartitions(
    IN table_name VARCHAR(64),
    IN partition_type ENUM('monthly', 'daily'),
    IN keep_months INT DEFAULT 12
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE partition_name VARCHAR(64);
    DECLARE partition_desc VARCHAR(64);
    DECLARE cutoff_date DATE;
    DECLARE next_month_start DATE;
    DECLARE partition_sql TEXT;
    
    -- 计算保留数据的截止日期
    SET cutoff_date = DATE_SUB(CURDATE(), INTERVAL keep_months MONTH);
    
    -- 删除过期分区
    CASE partition_type
        WHEN 'monthly' THEN
            -- 查找需要删除的月度分区
            BEGIN
                DECLARE partition_cursor CURSOR FOR
                    SELECT PARTITION_NAME, PARTITION_DESCRIPTION
                    FROM information_schema.PARTITIONS 
                    WHERE TABLE_SCHEMA = DATABASE()
                        AND TABLE_NAME = table_name
                        AND PARTITION_NAME IS NOT NULL
                        AND PARTITION_NAME != 'p_future'
                        AND CAST(SUBSTRING(PARTITION_NAME, 2) AS UNSIGNED) < 
                            (YEAR(cutoff_date) * 100 + MONTH(cutoff_date));
                
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
                
                OPEN partition_cursor;
                
                drop_loop: LOOP
                    FETCH partition_cursor INTO partition_name, partition_desc;
                    IF done THEN LEAVE drop_loop; END IF;
                    
                    SET partition_sql = CONCAT('ALTER TABLE ', table_name, 
                                             ' DROP PARTITION ', partition_name);
                    
                    SET @sql = partition_sql;
                    PREPARE stmt FROM @sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                    
                    INSERT INTO partition_management_log (
                        table_name, operation, partition_name, executed_at
                    ) VALUES (
                        table_name, 'DROP', partition_name, NOW()
                    );
                    
                END LOOP;
                
                CLOSE partition_cursor;
            END;
            
        WHEN 'daily' THEN
            -- 类似的日度分区处理逻辑
            SELECT '日度分区管理功能待实现' as message;
    END CASE;
    
    -- 添加未来分区
    IF partition_type = 'monthly' THEN
        SET next_month_start = DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 2 MONTH);
        
        -- 检查是否需要添加新分区
        IF NOT EXISTS (
            SELECT 1 FROM information_schema.PARTITIONS 
            WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = table_name
                AND PARTITION_NAME = CONCAT('p', DATE_FORMAT(next_month_start, '%Y%m'))
        ) THEN
            SET partition_sql = CONCAT(
                'ALTER TABLE ', table_name, 
                ' REORGANIZE PARTITION p_future INTO (',
                'PARTITION p', DATE_FORMAT(next_month_start, '%Y%m'),
                ' VALUES LESS THAN (', YEAR(next_month_start) * 100 + MONTH(next_month_start) + 1, '),',
                'PARTITION p_future VALUES LESS THAN MAXVALUE)'
            );
            
            SET @sql = partition_sql;
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            
            INSERT INTO partition_management_log (
                table_name, operation, partition_name, executed_at
            ) VALUES (
                table_name, 'ADD', CONCAT('p', DATE_FORMAT(next_month_start, '%Y%m')), NOW()
            );
        END IF;
    END IF;
    
    SELECT '分区管理完成' as result;
END //
DELIMITER ;

-- 创建分区管理日志表
CREATE TABLE partition_management_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    operation ENUM('ADD', 'DROP', 'REORGANIZE') NOT NULL,
    partition_name VARCHAR(64),
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_time (table_name, executed_at)
);

-- 调用分区管理存储过程
CALL ManagePartitions('orders_monthly', 'monthly', 6);

-- 查看管理日志
SELECT * FROM partition_management_log 
ORDER BY executed_at DESC 
LIMIT 10;

3. 分区性能监控

-- 创建分区性能监控视图
CREATE VIEW partition_performance_stats AS
SELECT 
    p.TABLE_SCHEMA as '数据库',
    p.TABLE_NAME as '表名',
    p.PARTITION_NAME as '分区名',
    p.PARTITION_ORDINAL_POSITION as '分区序号',
    p.TABLE_ROWS as '行数',
    ROUND(p.DATA_LENGTH/1024/1024, 2) as '数据大小MB',
    ROUND(p.INDEX_LENGTH/1024/1024, 2) as '索引大小MB',
    ROUND((p.DATA_LENGTH + p.INDEX_LENGTH)/1024/1024, 2) as '总大小MB',
    ROUND(p.DATA_LENGTH/NULLIF(p.TABLE_ROWS, 0), 2) as '平均行大小',
    p.PARTITION_METHOD as '分区方法',
    p.PARTITION_EXPRESSION as '分区表达式',
    p.PARTITION_DESCRIPTION as '分区描述'
FROM information_schema.PARTITIONS p
WHERE p.TABLE_SCHEMA = DATABASE()
    AND p.PARTITION_NAME IS NOT NULL
ORDER BY p.TABLE_NAME, p.PARTITION_ORDINAL_POSITION;

-- 查看分区性能统计
SELECT * FROM partition_performance_stats;

-- 分区查询性能测试
DELIMITER //
CREATE PROCEDURE TestPartitionPerformance(
    IN table_name VARCHAR(64),
    IN test_queries TEXT
)
BEGIN
    DECLARE start_time DECIMAL(20,6);
    DECLARE end_time DECIMAL(20,6);
    DECLARE execution_time DECIMAL(10,6);
    
    -- 记录开始时间
    SET start_time = UNIX_TIMESTAMP(NOW(6));
    
    -- 执行测试查询
    SET @sql = test_queries;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 记录结束时间
    SET end_time = UNIX_TIMESTAMP(NOW(6));
    SET execution_time = end_time - start_time;
    
    -- 记录性能测试结果
    INSERT INTO partition_performance_log (
        table_name, query_text, execution_time, test_time
    ) VALUES (
        table_name, test_queries, execution_time, NOW()
    );
    
    SELECT 
        table_name as '表名',
        execution_time as '执行时间(秒)',
        test_queries as '测试查询'
    ;
END //
DELIMITER ;

-- 创建性能测试日志表
CREATE TABLE partition_performance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    query_text TEXT NOT NULL,
    execution_time DECIMAL(10,6) NOT NULL,
    test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_time (table_name, test_time)
);

-- 测试分区查询性能
CALL TestPartitionPerformance(
    'orders_monthly',
    'SELECT COUNT(*) FROM orders_monthly WHERE order_time >= "2023-08-01" AND order_time < "2023-09-01"'
);

-- 对比分区表和非分区表的性能
-- 创建相同结构的非分区表
CREATE TABLE orders_no_partition LIKE orders_monthly;
ALTER TABLE orders_no_partition REMOVE PARTITIONING;

-- 插入相同的测试数据
INSERT INTO orders_no_partition SELECT * FROM orders_monthly;

-- 性能对比测试
CALL TestPartitionPerformance(
    'orders_no_partition',
    'SELECT COUNT(*) FROM orders_no_partition WHERE order_time >= "2023-08-01" AND order_time < "2023-09-01"'
);

-- 查看性能对比结果
SELECT 
    table_name,
    AVG(execution_time) as avg_execution_time,
    MIN(execution_time) as min_execution_time,
    MAX(execution_time) as max_execution_time,
    COUNT(*) as test_count
FROM partition_performance_log 
WHERE test_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY table_name;

分区优化技巧

1. 分区裁剪优化

-- 创建测试表验证分区裁剪
CREATE TABLE sales_partition_test (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region_id INT NOT NULL,
    PRIMARY KEY (id, sale_date, region_id)
) PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH(region_id) SUBPARTITIONS 4 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 插入测试数据
INSERT INTO sales_partition_test (sale_date, product_id, customer_id, amount, region_id) VALUES
('2022-06-15', 1001, 2001, 299.99, 1),
('2022-12-20', 1002, 2002, 199.99, 2),
('2023-03-10', 1003, 2003, 399.99, 3),
('2023-08-25', 1004, 2004, 499.99, 4),
('2024-01-05', 1005, 2005, 599.99, 1);

-- 好的查询：能够利用分区裁剪
EXPLAIN PARTITIONS 
SELECT * FROM sales_partition_test 
WHERE sale_date >= '2023-01-01' 
    AND sale_date < '2024-01-01'
    AND region_id = 3;

-- 不好的查询：无法利用分区裁剪
EXPLAIN PARTITIONS 
SELECT * FROM sales_partition_test 
WHERE MONTH(sale_date) = 8;  -- 函数包装导致无法分区裁剪

-- 优化后的查询
EXPLAIN PARTITIONS 
SELECT * FROM sales_partition_test 
WHERE sale_date >= '2023-08-01' 
    AND sale_date < '2023-09-01';

-- 分区裁剪效果分析
SELECT 
    '查询类型' as query_type,
    '是否分区裁剪' as partition_pruning,
    '扫描分区数' as partitions_scanned,
    '性能影响' as performance_impact
UNION ALL
SELECT '日期范围查询', '是', '1个主分区', '最优'
UNION ALL
SELECT '日期函数查询', '否', '所有分区', '最差'
UNION ALL
SELECT '复合条件查询', '部分', '部分分区', '中等';

2. 索引策略优化

-- 分区表索引设计最佳实践
CREATE TABLE user_actions_optimized (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    action_type VARCHAR(20) NOT NULL,
    action_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    target_id INT,
    target_type VARCHAR(20),
    ip_address VARCHAR(45),
    user_agent TEXT,
    PRIMARY KEY (id, action_time),  -- 包含分区键
    INDEX idx_user_action_time (user_id, action_time),  -- 本地索引
    INDEX idx_action_type_time (action_type, action_time),  -- 本地索引
    INDEX idx_target (target_type, target_id, action_time)  -- 复合索引
) PARTITION BY RANGE (TO_DAYS(action_time)) (
    PARTITION p20230801 VALUES LESS THAN (TO_DAYS('2023-08-02')),
    PARTITION p20230802 VALUES LESS THAN (TO_DAYS('2023-08-03')),
    PARTITION p20230803 VALUES LESS THAN (TO_DAYS('2023-08-04')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 索引使用效果测试
INSERT INTO user_actions_optimized (user_id, action_type, target_id, target_type) VALUES
(1001, 'view', 2001, 'product'),
(1002, 'click', 2002, 'ad'),
(1003, 'purchase', 2003, 'product');

-- 测试索引效果
EXPLAIN PARTITIONS 
SELECT * FROM user_actions_optimized 
WHERE user_id = 1001 
    AND action_time >= '2023-08-01 00:00:00'
    AND action_time < '2023-08-02 00:00:00';

3. 分区表查询优化

-- 创建查询优化示例表
CREATE TABLE order_analytics (
    id BIGINT AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    payment_status TINYINT NOT NULL,
    region_code VARCHAR(10) NOT NULL,
    PRIMARY KEY (id, order_date),
    INDEX idx_user_date (user_id, order_date),
    INDEX idx_product_date (product_id, order_date),
    INDEX idx_region_status (region_code, payment_status, order_date)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION p202304 VALUES LESS THAN (202305),
    PARTITION p202305 VALUES LESS THAN (202306),
    PARTITION p202306 VALUES LESS THAN (202307),
    PARTITION p202307 VALUES LESS THAN (202308),
    PARTITION p202308 VALUES LESS THAN (202309),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 优化的聚合查询
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(order_amount) as total_amount,
    AVG(order_amount) as avg_amount
FROM order_analytics 
WHERE order_date >= '2023-06-01' 
    AND order_date < '2023-09-01'
    AND payment_status = 1
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- 跨分区JOIN优化
SELECT 
    o.order_date,
    o.order_amount,
    u.username,
    p.product_name
FROM order_analytics o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2023-08-01'
    AND o.order_date < '2023-08-02'
    AND o.payment_status = 1;

分区表最佳实践

1. 分区设计原则

-- 分区设计检查清单
CREATE VIEW partition_design_checklist AS
SELECT 
    '设计原则' as principle,
    '说明' as description,
    '示例' as example
UNION ALL
SELECT '选择合适的分区键', '分区键应该是查询中经常使用的条件', '时间字段、用户ID、地区等'
UNION ALL
SELECT '分区数量控制', '分区数量不宜过多，建议不超过1000个', '按月分区而不是按天'
UNION ALL
SELECT '分区大小均衡', '各分区数据量应该相对均衡', '避免热点分区'
UNION ALL
SELECT '索引策略', '主键必须包含分区键', 'PRIMARY KEY (id, partition_key)'
UNION ALL
SELECT '查询优化', '查询条件应包含分区键以利用分区裁剪', 'WHERE date_col >= "2023-01-01"'
UNION ALL
SELECT '维护策略', '制定分区的创建、删除和维护计划', '定期清理历史分区';

SELECT * FROM partition_design_checklist;

2. 分区监控和告警

-- 创建分区监控表
CREATE TABLE partition_monitoring (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    partition_name VARCHAR(64) NOT NULL,
    table_rows BIGINT NOT NULL,
    data_size_mb DECIMAL(10,2) NOT NULL,
    index_size_mb DECIMAL(10,2) NOT NULL,
    check_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_time (table_name, check_time)
);

-- 分区监控存储过程
DELIMITER //
CREATE PROCEDURE MonitorPartitions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_partition_name VARCHAR(64);
    DECLARE v_table_rows BIGINT;
    DECLARE v_data_length BIGINT;
    DECLARE v_index_length BIGINT;
    
    DECLARE partition_cursor CURSOR FOR
        SELECT 
            TABLE_NAME,
            PARTITION_NAME,
            TABLE_ROWS,
            DATA_LENGTH,
            INDEX_LENGTH
        FROM information_schema.PARTITIONS 
        WHERE TABLE_SCHEMA = DATABASE()
            AND PARTITION_NAME IS NOT NULL;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 清空当前监控数据
    DELETE FROM partition_monitoring WHERE DATE(check_time) = CURDATE();
    
    OPEN partition_cursor;
    
    monitor_loop: LOOP
        FETCH partition_cursor INTO 
            v_table_name, v_partition_name, v_table_rows, v_data_length, v_index_length;
        
        IF done THEN LEAVE monitor_loop; END IF;
        
        INSERT INTO partition_monitoring (
            table_name, partition_name, table_rows, 
            data_size_mb, index_size_mb
        ) VALUES (
            v_table_name, v_partition_name, v_table_rows,
            ROUND(v_data_length/1024/1024, 2),
            ROUND(v_index_length/1024/1024, 2)
        );
        
    END LOOP;
    
    CLOSE partition_cursor;
    
    -- 检查异常分区
    SELECT 
        table_name,
        partition_name,
        table_rows,
        data_size_mb,
        '分区过大' as alert_type
    FROM partition_monitoring 
    WHERE check_time >= CURDATE()
        AND (data_size_mb > 1000 OR table_rows > 1000000)
    
    UNION ALL
    
    SELECT 
        table_name,
        partition_name,
        table_rows,
        data_size_mb,
        '空分区' as alert_type
    FROM partition_monitoring 
    WHERE check_time >= CURDATE()
        AND table_rows = 0
        AND partition_name != 'p_future';
        
END //
DELIMITER ;

-- 定期执行监控
-- 可以配合事件调度器定期执行
CALL MonitorPartitions();

3. 分区迁移和重构

-- 分区重构示例：从按年分区改为按月分区
-- 1. 创建新的按月分区表
CREATE TABLE orders_monthly_new (
    id BIGINT AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id INT NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    order_date DATE NOT NULL,
    status TINYINT NOT NULL,
    PRIMARY KEY (id, order_date),
    UNIQUE KEY uk_order_no_date (order_no, order_date),
    INDEX idx_user_date (user_id, order_date)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION p202304 VALUES LESS THAN (202305),
    PARTITION p202305 VALUES LESS THAN (202306),
    PARTITION p202306 VALUES LESS THAN (202307),
    PARTITION p202307 VALUES LESS THAN (202308),
    PARTITION p202308 VALUES LESS THAN (202309),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 2. 数据迁移存储过程
DELIMITER //
CREATE PROCEDURE MigratePartitionData(
    IN source_table VARCHAR(64),
    IN target_table VARCHAR(64),
    IN batch_size INT DEFAULT 10000
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE total_migrated INT DEFAULT 0;
    DECLARE batch_count INT DEFAULT 0;
    
    -- 分批迁移数据
    REPEAT
        SET @sql = CONCAT(
            'INSERT INTO ', target_table, 
            ' SELECT * FROM ', source_table, 
            ' LIMIT ', batch_size, ' OFFSET ', batch_count * batch_size
        );
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET batch_count = batch_count + 1;
        SET total_migrated = total_migrated + ROW_COUNT();
        
        -- 避免长时间锁表
        SELECT SLEEP(0.1);
        
    UNTIL ROW_COUNT() = 0 END REPEAT;
    
    SELECT CONCAT('迁移完成，共迁移 ', total_migrated, ' 条记录') as result;
END //
DELIMITER ;

-- 3. 执行迁移
-- CALL MigratePartitionData('orders_yearly', 'orders_monthly_new', 5000);

-- 4. 验证数据一致性
-- SELECT COUNT(*) FROM orders_yearly;
-- SELECT COUNT(*) FROM orders_monthly_new;

-- 5. 切换表名
-- RENAME TABLE orders_yearly TO orders_yearly_backup;
-- RENAME TABLE orders_monthly_new TO orders_monthly;

4. 性能基准测试

-- 创建性能测试框架
CREATE TABLE partition_benchmark_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(100) NOT NULL,
    table_type ENUM('partitioned', 'non_partitioned') NOT NULL,
    query_type VARCHAR(50) NOT NULL,
    execution_time DECIMAL(10,6) NOT NULL,
    rows_examined BIGINT,
    rows_returned BIGINT,
    test_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_test_name (test_name),
    INDEX idx_table_type (table_type),
    INDEX idx_timestamp (test_timestamp)
);

-- 性能测试存储过程
DELIMITER //
CREATE PROCEDURE BenchmarkPartitionPerformance(
    IN test_name VARCHAR(100),
    IN partitioned_table VARCHAR(64),
    IN non_partitioned_table VARCHAR(64),
    IN test_query_template TEXT,
    IN iterations INT DEFAULT 10
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE start_time DECIMAL(20,6);
    DECLARE end_time DECIMAL(20,6);
    DECLARE execution_time DECIMAL(10,6);
    DECLARE query_sql TEXT;
    
    -- 测试分区表
    WHILE i < iterations DO
        SET query_sql = REPLACE(test_query_template, '{TABLE}', partitioned_table);
        
        SET start_time = UNIX_TIMESTAMP(NOW(6));
        
        SET @sql = query_sql;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET end_time = UNIX_TIMESTAMP(NOW(6));
        SET execution_time = end_time - start_time;
        
        INSERT INTO partition_benchmark_results (
            test_name, table_type, query_type, execution_time
        ) VALUES (
            test_name, 'partitioned', 'SELECT', execution_time
        );
        
        SET i = i + 1;
    END WHILE;
    
    -- 测试非分区表
    SET i = 0;
    WHILE i < iterations DO
        SET query_sql = REPLACE(test_query_template, '{TABLE}', non_partitioned_table);
        
        SET start_time = UNIX_TIMESTAMP(NOW(6));
        
        SET @sql = query_sql;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET end_time = UNIX_TIMESTAMP(NOW(6));
        SET execution_time = end_time - start_time;
        
        INSERT INTO partition_benchmark_results (
            test_name, table_type, query_type, execution_time
        ) VALUES (
            test_name, 'non_partitioned', 'SELECT', execution_time
        );
        
        SET i = i + 1;
    END WHILE;
    
    -- 输出测试结果
    SELECT 
        table_type,
        COUNT(*) as test_count,
        ROUND(AVG(execution_time), 6) as avg_time,
        ROUND(MIN(execution_time), 6) as min_time,
        ROUND(MAX(execution_time), 6) as max_time,
        ROUND(STDDEV(execution_time), 6) as stddev_time
    FROM partition_benchmark_results 
    WHERE test_name = test_name
        AND test_timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY table_type;
    
END //
DELIMITER ;

-- 执行性能测试
CALL BenchmarkPartitionPerformance(
    'date_range_query',
    'orders_monthly',
    'orders_no_partition',
    'SELECT COUNT(*) FROM {TABLE} WHERE order_date >= "2023-08-01" AND order_date < "2023-09-01"',
    5
);

-- 查看测试结果汇总
SELECT 
    test_name,
    table_type,
    COUNT(*) as test_runs,
    ROUND(AVG(execution_time), 6) as avg_execution_time,
    ROUND(MIN(execution_time), 6) as min_execution_time,
    ROUND(MAX(execution_time), 6) as max_execution_time,
    ROUND(
        (SELECT AVG(execution_time) 
         FROM partition_benchmark_results p2 
         WHERE p2.test_name = p1.test_name 
           AND p2.table_type = 'non_partitioned') / AVG(execution_time), 2
    ) as performance_ratio
FROM partition_benchmark_results p1
WHERE test_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY test_name, table_type
ORDER BY test_name, table_type;

常见问题和解决方案

1. 分区表常见错误

-- 错误1：主键不包含分区键
/*
CREATE TABLE error_example1 (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 错误：主键不包含分区键
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024)
);
*/

-- 正确做法
CREATE TABLE correct_example1 (
    id INT AUTO_INCREMENT,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)  -- 主键包含分区键
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 错误2：外键约束问题
-- 分区表不支持外键约束，需要在应用层处理

-- 错误3：全文索引问题
-- 分区表不支持全文索引，需要使用其他搜索方案

-- 分区表限制检查
SELECT 
    '限制类型' as limitation_type,
    '说明' as description,
    '解决方案' as solution
UNION ALL
SELECT '主键约束', '主键必须包含分区键', '调整主键定义'
UNION ALL
SELECT '外键约束', '不支持外键约束', '应用层处理或使用触发器'
UNION ALL
SELECT '全文索引', '不支持FULLTEXT索引', '使用ElasticSearch等搜索引擎'
UNION ALL
SELECT '分区函数', '只支持特定的分区函数', '选择合适的分区函数'
UNION ALL
SELECT '分区数量', '最多支持8192个分区', '合理规划分区数量';

2. 分区维护最佳实践

-- 创建分区维护计划
CREATE TABLE partition_maintenance_plan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    partition_type ENUM('RANGE', 'LIST', 'HASH', 'KEY') NOT NULL,
    maintenance_type ENUM('ADD', 'DROP', 'REORGANIZE') NOT NULL,
    schedule_time TIME NOT NULL,
    retention_months INT DEFAULT 12,
    is_active TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_name (table_name),
    INDEX idx_schedule (schedule_time, is_active)
);

-- 插入维护计划
INSERT INTO partition_maintenance_plan (
    table_name, partition_type, maintenance_type, schedule_time, retention_months
) VALUES
('orders_monthly', 'RANGE', 'ADD', '02:00:00', 12),
('orders_monthly', 'RANGE', 'DROP', '03:00:00', 12),
('system_logs', 'RANGE', 'DROP', '01:00:00', 3),
('user_behaviors', 'RANGE', 'ADD', '02:30:00', 6);

-- 自动维护执行存储过程
DELIMITER //
CREATE PROCEDURE ExecutePartitionMaintenance()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_maintenance_type VARCHAR(20);
    DECLARE v_retention_months INT;
    
    DECLARE maintenance_cursor CURSOR FOR
        SELECT table_name, maintenance_type, retention_months
        FROM partition_maintenance_plan 
        WHERE is_active = 1
            AND TIME(NOW()) BETWEEN schedule_time AND ADDTIME(schedule_time, '00:30:00');
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN maintenance_cursor;
    
    maintenance_loop: LOOP
        FETCH maintenance_cursor INTO v_table_name, v_maintenance_type, v_retention_months;
        
        IF done THEN LEAVE maintenance_loop; END IF;
        
        CASE v_maintenance_type
            WHEN 'ADD' THEN
                CALL AddFuturePartitions(v_table_name);
            WHEN 'DROP' THEN
                CALL DropOldPartitions(v_table_name, v_retention_months);
            WHEN 'REORGANIZE' THEN
                CALL ReorganizePartitions(v_table_name);
        END CASE;
        
    END LOOP;
    
    CLOSE maintenance_cursor;
END //
DELIMITER ;

总结

MySQL分区表是处理大数据量的有效解决方案，通过合理的分区策略可以显著提升查询性能：

分区表优势：

1. 查询性能提升：通过分区裁剪减少扫描数据量
2. 维护效率：可以对单个分区进行维护操作
3. 并行处理：支持分区级别的并行查询
4. 存储管理：便于数据归档和清理

选择建议：

1. RANGE分区：适用于时间序列数据
2. LIST分区：适用于有限枚举值的数据
3. HASH分区：适用于数据均匀分布的场景
4. KEY分区：适用于主键分区的场景

注意事项：

1. 分区键选择：选择查询中经常使用的字段
2. 主键设计：必须包含分区键
3. 索引策略：合理设计本地索引
4. 维护计划：制定分区的创建和清理策略
5. 性能监控：定期监控分区性能和数据分布

通过合理使用分区表技术，可以有效解决大数据量场景下的性能问题，提升数据库的整体处理能力。