MySQL(MariaDB) 依据时间戳按月添加分区(加速查询)

xcli25

已于 2024-06-29 23:05:48 修改

阅读量301

点赞数 9

文章标签： mysql 数据库

于 2024-06-29 23:05:02 首次发布

本文链接：https://blog.csdn.net/xcli25/article/details/140071816

版权

如果有个表比较大，比如访问记录、通话详单、购物详单、出库明细等，往往都需要限制时间进行查询，建议一定要建个分区能大大提高访问效率，但必须在where 条件中使用分区的字段

使用说明

end_stamp 必须是datetime字段
必须和主键是组合主键，且不能有其他唯一索引，最好也加个索引

组合索引

加个索引

添加分区（前期加的不多，后期自行添加）

-- 第一个分区p20220501 代表20220501前所有
ALTER TABLE `my_list` PARTITION by RANGE  (UNIX_TIMESTAMP(end_stamp))
(
PARTITION `p20220501` VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01')),
PARTITION `p20220601` VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-01')),
PARTITION `p20220701` VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-01')),
PARTITION `p99999999` VALUES LESS THAN (MAXVALUE)
)


-- 新增分区
 alter table my_list REORGANIZE PARTITION p99999999 INTO (
    PARTITION `p20220801` VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-01')),
    PARTITION `p99999999` VALUES LESS THAN MAXVALUE
	) 
	
-- 删除某个表所有分区
ALTER TABLE 表名 REMOVE PARTITIONING;

-- 删除某个表的一个分区
ALTER TABLE 表名 DROP PARTITION 分区名;

复制使用

ALTER TABLE `my_list` PARTITION by RANGE  (UNIX_TIMESTAMP(end_stamp))
(
PARTITION `p20240101` VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
PARTITION `p20240201` VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
PARTITION `p20240301` VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
PARTITION `p20240401` VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
PARTITION `p20240501` VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01')),
PARTITION `p20240601` VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01')),
PARTITION `p20240701` VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01')),
PARTITION `p20240801` VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01')),
PARTITION `p20240901` VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01')),
PARTITION `p20241001` VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01')),
PARTITION `p20241101` VALUES LESS THAN (UNIX_TIMESTAMP('2024-11-01')),
PARTITION `p20241201` VALUES LESS THAN (UNIX_TIMESTAMP('2024-12-01')),

PARTITION `p20250101` VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
PARTITION `p20250201` VALUES LESS THAN (UNIX_TIMESTAMP('2025-02-01')),
PARTITION `p20250301` VALUES LESS THAN (UNIX_TIMESTAMP('2025-03-01')),
PARTITION `p20250401` VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01')),
PARTITION `p20250501` VALUES LESS THAN (UNIX_TIMESTAMP('2025-05-01')),
PARTITION `p20250601` VALUES LESS THAN (UNIX_TIMESTAMP('2025-06-01')),
PARTITION `p20250701` VALUES LESS THAN (UNIX_TIMESTAMP('2025-07-01')),
PARTITION `p20250801` VALUES LESS THAN (UNIX_TIMESTAMP('2025-08-01')),
PARTITION `p20250901` VALUES LESS THAN (UNIX_TIMESTAMP('2025-09-01')),
PARTITION `p20251001` VALUES LESS THAN (UNIX_TIMESTAMP('2025-10-01')),
PARTITION `p20251101` VALUES LESS THAN (UNIX_TIMESTAMP('2025-11-01')),
PARTITION `p20251201` VALUES LESS THAN (UNIX_TIMESTAMP('2025-12-01')),

PARTITION `p20260101` VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01')),
PARTITION `p20260201` VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01')),
PARTITION `p20260301` VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01')),
PARTITION `p20260401` VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01')),
PARTITION `p20260501` VALUES LESS THAN (UNIX_TIMESTAMP('2026-05-01')),
PARTITION `p20260601` VALUES LESS THAN (UNIX_TIMESTAMP('2026-06-01')),
PARTITION `p20260701` VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01')),
PARTITION `p20260801` VALUES LESS THAN (UNIX_TIMESTAMP('2026-08-01')),
PARTITION `p20260901` VALUES LESS THAN (UNIX_TIMESTAMP('2026-09-01')),
PARTITION `p20261001` VALUES LESS THAN (UNIX_TIMESTAMP('2026-10-01')),
PARTITION `p20261101` VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-01')),
PARTITION `p20261201` VALUES LESS THAN (UNIX_TIMESTAMP('2026-12-01')),

PARTITION `p20270101` VALUES LESS THAN (UNIX_TIMESTAMP('2027-01-01')),
PARTITION `p20270201` VALUES LESS THAN (UNIX_TIMESTAMP('2027-02-01')),
PARTITION `p20270301` VALUES LESS THAN (UNIX_TIMESTAMP('2027-03-01')),
PARTITION `p20270401` VALUES LESS THAN (UNIX_TIMESTAMP('2027-04-01')),
PARTITION `p20270501` VALUES LESS THAN (UNIX_TIMESTAMP('2027-05-01')),
PARTITION `p20270601` VALUES LESS THAN (UNIX_TIMESTAMP('2027-06-01')),
PARTITION `p20270701` VALUES LESS THAN (UNIX_TIMESTAMP('2027-07-01')),
PARTITION `p20270801` VALUES LESS THAN (UNIX_TIMESTAMP('2027-08-01')),
PARTITION `p20270901` VALUES LESS THAN (UNIX_TIMESTAMP('2027-09-01')),
PARTITION `p20271001` VALUES LESS THAN (UNIX_TIMESTAMP('2027-10-01')),
PARTITION `p20271101` VALUES LESS THAN (UNIX_TIMESTAMP('2027-11-01')),
PARTITION `p20271201` VALUES LESS THAN (UNIX_TIMESTAMP('2027-12-01')),

PARTITION `p20280101` VALUES LESS THAN (UNIX_TIMESTAMP('2028-01-01')),
PARTITION `p20280201` VALUES LESS THAN (UNIX_TIMESTAMP('2028-02-01')),
PARTITION `p20280301` VALUES LESS THAN (UNIX_TIMESTAMP('2028-03-01')),
PARTITION `p20280401` VALUES LESS THAN (UNIX_TIMESTAMP('2028-04-01')),
PARTITION `p20280501` VALUES LESS THAN (UNIX_TIMESTAMP('2028-05-01')),
PARTITION `p20280601` VALUES LESS THAN (UNIX_TIMESTAMP('2028-06-01')),
PARTITION `p20280701` VALUES LESS THAN (UNIX_TIMESTAMP('2028-07-01')),
PARTITION `p20280801` VALUES LESS THAN (UNIX_TIMESTAMP('2028-08-01')),
PARTITION `p20280901` VALUES LESS THAN (UNIX_TIMESTAMP('2028-09-01')),
PARTITION `p20281001` VALUES LESS THAN (UNIX_TIMESTAMP('2028-10-01')),
PARTITION `p20281101` VALUES LESS THAN (UNIX_TIMESTAMP('2028-11-01')),
PARTITION `p20281201` VALUES LESS THAN (UNIX_TIMESTAMP('2028-12-01')),

PARTITION `p20290101` VALUES LESS THAN (UNIX_TIMESTAMP('2029-01-01')),
PARTITION `p20290201` VALUES LESS THAN (UNIX_TIMESTAMP('2029-02-01')),
PARTITION `p20290301` VALUES LESS THAN (UNIX_TIMESTAMP('2029-03-01')),
PARTITION `p20290401` VALUES LESS THAN (UNIX_TIMESTAMP('2029-04-01')),
PARTITION `p20290501` VALUES LESS THAN (UNIX_TIMESTAMP('2029-05-01')),
PARTITION `p20290601` VALUES LESS THAN (UNIX_TIMESTAMP('2029-06-01')),
PARTITION `p20290701` VALUES LESS THAN (UNIX_TIMESTAMP('2029-07-01')),
PARTITION `p20290801` VALUES LESS THAN (UNIX_TIMESTAMP('2029-08-01')),
PARTITION `p20290901` VALUES LESS THAN (UNIX_TIMESTAMP('2029-09-01')),
PARTITION `p20291001` VALUES LESS THAN (UNIX_TIMESTAMP('2029-10-01')),
PARTITION `p20291101` VALUES LESS THAN (UNIX_TIMESTAMP('2029-11-01')),
PARTITION `p20291201` VALUES LESS THAN (UNIX_TIMESTAMP('2029-12-01')),

PARTITION `p20300101` VALUES LESS THAN (UNIX_TIMESTAMP('2030-01-01')),
PARTITION `p20300201` VALUES LESS THAN (UNIX_TIMESTAMP('2030-02-01')),
PARTITION `p20300301` VALUES LESS THAN (UNIX_TIMESTAMP('2030-03-01')),
PARTITION `p20300401` VALUES LESS THAN (UNIX_TIMESTAMP('2030-04-01')),
PARTITION `p20300501` VALUES LESS THAN (UNIX_TIMESTAMP('2030-05-01')),
PARTITION `p20300601` VALUES LESS THAN (UNIX_TIMESTAMP('2030-06-01')),
PARTITION `p20300701` VALUES LESS THAN (UNIX_TIMESTAMP('2030-07-01')),
PARTITION `p20300801` VALUES LESS THAN (UNIX_TIMESTAMP('2030-08-01')),
PARTITION `p20300901` VALUES LESS THAN (UNIX_TIMESTAMP('2030-09-01')),
PARTITION `p20301001` VALUES LESS THAN (UNIX_TIMESTAMP('2030-10-01')),
PARTITION `p20301101` VALUES LESS THAN (UNIX_TIMESTAMP('2030-11-01')),
PARTITION `p20301201` VALUES LESS THAN (UNIX_TIMESTAMP('2030-12-01')),




PARTITION `p99999999` VALUES LESS THAN (MAXVALUE)
)