mysql分区关联查询语句

-- 创建分区表 orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- 创建分区表 order_items
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
)
PARTITION BY HASH (order_id) PARTITIONS 8;

-- 插入示例数据
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 101, '2009-01-01'),
(2, 102, '2014-05-15'),
(3, 103, '2019-11-20');

INSERT INTO order_items (item_id, order_id, product_id, quantity) VALUES
(1, 1, 1001, 2),
(2, 2, 1002, 1),
(3, 3, 1003, 3);

-- 分区关联查询
SELECT o.order_id, o.customer_id, o.order_date, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

-- 优化查询条件，利用分区键
SELECT o.order_id, o.customer_id, o.order_date, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2014-01-01' AND '2019-12-31';