MySQL 分区表数据查询跨分区性能损耗问题及优化

-- 示例1：订单表按月份分区（MySQL 8.0）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    create_time DATETIME
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
    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'))
);

-- 跨分区查询示例（扫描全部分区）
EXPLAIN SELECT * FROM orders 
WHERE amount > 1000;  -- 没有包含分区键的条件
/* 
执行计划显示：
partitions: p202301,p202302,p202303
type: ALL
*/

-- 优化后查询（限定分区范围）
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-02-15' AND '2023-03-15'
AND amount > 1000;

-- 查看执行计划验证
EXPLAIN SELECT ...;
/*
执行计划显示：
partitions: p202302,p202303  -- 仅扫描2个分区
type: range
*/

-- 添加覆盖索引（MySQL 5.7+）
ALTER TABLE orders ADD INDEX idx_amount_create(amount, create_time);

-- 优化后查询
SELECT order_id, user_id 
FROM orders 
WHERE amount > 1000 
AND create_time >= '2023-01-01';
/*
索引结构：
(amount值, create_time) -> order_id, user_id
*/

-- 按月分区的订单表查询
SELECT * FROM orders 
WHERE create_time >= '2023-03-01'
AND create_time < '2023-04-01';

-- 执行计划验证
EXPLAIN SELECT ...;
/*
partitions: p202303  -- 仅访问单个分区
*/

-- 将历史数据归档到独立分区
ALTER TABLE orders REORGANIZE PARTITION p202301 INTO (
    PARTITION p202301_archive VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202301_active VALUES LESS THAN MAXVALUE
);

-- 仅查询活跃分区
SELECT * FROM orders PARTITION (p202301_active)
WHERE amount > 1000;