升级 lq_restock

第一步：修改表结构

根据 keytime 来设置分区，需要合理设置 keytime
（如果已经存在 create_time 且 完全可以按照 create_time 设置分区，就不需要新建字段了）

# 创建用于分区的分割字段(lq_key_time)
alter table lq_restock_logs add lq_key_time datetime default now() not null;
alter table lq_restock_passenger add lq_key_time datetime default now() not null;

# 设置 lq_key_time 为创建时间
UPDATE lq_restock_logs p INNER JOIN lq_restock_order o ON p.order_id= o.id SET p.lq_key_time = o.create_time;
UPDATE lq_restock_passenger p INNER JOIN lq_restock_order o ON p.order_id= o.id SET p.lq_key_time = o.create_time;

# 删除原主键
alter table lq_restock_order drop primary key;
alter table lq_restock_logs drop primary key;
alter table lq_restock_passenger drop primary key;

# 创建联合主键
alter table lq_restock_order add PRIMARY KEY(id, create_time);
alter table lq_restock_logs add PRIMARY KEY(id, lq_key_time);
alter table lq_restock_passenger add PRIMARY KEY(id, lq_key_time);

第二步：创建分区表

创建老数据的分区，其他的数据全部放到 a1 中，后续拆分 a1 来实现分区。
每个月拆一次，超过12个月的可以合并到年分区

# 创建分区
ALTER TABLE `lq_restock_order`  PARTITION BY RANGE(TO_DAYS(create_time)) (
    PARTITION p2018 VALUES LESS THAN (TO_DAYS('2019-01-01')) ENGINE = INNODB,
    PARTITION p2019 VALUES LESS THAN (TO_DAYS('2020-01-01')) ENGINE = INNODB,
    PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')) ENGINE = INNODB,
    PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01')) ENGINE = INNODB,
    PARTITION p2022 VALUES LESS THAN (TO_DAYS('2023-01-01')) ENGINE = INNODB,
    PARTITION p2023 VALUES LESS THAN (TO_DAYS('2023-02-01')) ENGINE = INNODB,
    PARTITION a1 VALUES LESS THAN  MAXVALUE
);

ALTER TABLE `lq_restock_logs`  PARTITION BY RANGE(TO_DAYS(lq_key_time)) (
    PARTITION p2018 VALUES LESS THAN (TO_DAYS('2019-01-01')) ENGINE = INNODB,
    PARTITION p2019 VALUES LESS THAN (TO_DAYS('2020-01-01')) ENGINE = INNODB,
    PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')) ENGINE = INNODB,
    PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01')) ENGINE = INNODB,
    PARTITION p2022 VALUES LESS THAN (TO_DAYS('2023-01-01')) ENGINE = INNODB,
    PARTITION p2023 VALUES LESS THAN (TO_DAYS('2023-02-01')) ENGINE = INNODB,
    PARTITION a1 VALUES LESS THAN  MAXVALUE
);

ALTER TABLE `lq_restock_passenger`  PARTITION BY RANGE(TO_DAYS(lq_key_time)) (
    PARTITION p2018 VALUES LESS THAN (TO_DAYS('2019-01-01')) ENGINE = INNODB,
    PARTITION p2019 VALUES LESS THAN (TO_DAYS('2020-01-01')) ENGINE = INNODB,
    PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')) ENGINE = INNODB,
    PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01')) ENGINE = INNODB,
    PARTITION p2022 VALUES LESS THAN (TO_DAYS('2023-01-01')) ENGINE = INNODB,
    PARTITION p2023 VALUES LESS THAN (TO_DAYS('2023-02-01')) ENGINE = INNODB,
    PARTITION a1 VALUES LESS THAN  MAXVALUE
);

第三步：查看分区情况

分析一下分区，可以看到分区的数据情况

#分析
ALTER TABLE lq_restock_order ANALYZE PARTITION p2018,p2019,p2020,p2021,p2022,p2023,a1;

# 查看全部分区
SELECT table_name,partition_name,partition_description,from_days(partition_description),table_rows
FROM information_schema.PARTITIONS
WHERE table_name='lq_restock_order';

# 分析
explain select * from lq_restock_order where create_time>'2023-1-1' and create_time<'2023-6-10';

附：后续

时间越来越长后会导致 a1 分区越来越大，需要将 a1 的数据拆分出来，比如6个月前的放到各自的分区中
例如(下例的 2023 和 202309 可以动态获取（p2023已经包含1-8月的数据）)：

# 从 a1 中拆分出9月的放到p202309和其他的继续放到a1
alter table lq_restock_order reorganize partition a1 into (
    PARTITION p202309 VALUES LESS THAN (to_days('2023-10-01')),
    PARTITION a1 VALUES LESS THAN  MAXVALUE
);

# 合并 p2023,p202309 到 p2023
alter table lq_restock_order reorganize partition p2023,p202309 into (
    PARTITION p2023 VALUES LESS THAN (to_days('2023-10-01'))
);

# 重新分析
ALTER TABLE lq_restock_order ANALYZE PARTITION p2023,a1;