结合车辆GPS数据存储总结MySQL分区使用经验

查看MySQL是否支持分区

show plugins;

根据车辆GPS的记录时间进行按天分区。

注意这边id和要分区的record_time添加了联合主键。

drop table if EXISTS t_car_gps;
CREATE TABLE `t_car_gps` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键编码id',
  `car_no` varchar(30) DEFAULT NULL COMMENT '车牌号',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `record_time` datetime not NULL COMMENT '记录时间'
  PRIMARY KEY (`id`,`record_time`) USING BTREE,
  UNIQUE KEY `idx_car_no_record_time` (`car_no`,`record_time`) USING BTREE,KEY `idx_car_no` (`car_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='车辆GPS数据表'
PARTITION BY RANGE (TO_DAYS(record_time)) (
partition     p20220601     values less than (TO_DAYS('2022-06-01')) engine=innodb,
partition     p20220602     values less than (TO_DAYS('2022-06-02')) engine=innodb,
partition     p20220603     values less than (TO_DAYS('2022-06-03')) engine=innodb,
partition     p20220604     values less than (TO_DAYS('2022-06-04')) engine=innodb,
partition     p20220605     values less than (TO_DAYS('2022-06-05')) engine=innodb,
partition     p20220606     values less than (TO_DAYS('2022-06-06')) engine=innodb,
partition     p20220607     values less than (TO_DAYS('2022-06-07')) engine=innodb,
partition     p20220608     values less than (TO_DAYS('2022-06-08')) engine=innodb,
partition     p20220609     values less than (TO_DAYS('2022-06-09')) engine=innodb,
partition     p20220610     values less than (TO_DAYS('2022-06-10')) engine=innodb,
partition     p20220611     values less than (TO_DAYS('2022-06-11')) engine=innodb,
partition     p20220612     values less than (TO_DAYS('2022-06-12')) engine=innodb,
partition     p20220613     values less than (TO_DAYS('2022-06-13')) engine=innodb,
partition     p20220614     values less than (TO_DAYS('2022-06-14')) engine=innodb,
partition     p20220615     values less than (TO_DAYS('2022-06-15')) engine=innodb,
partition     p20220616     values less than (TO_DAYS('2022-06-16')) engine=innodb,
partition     p20220617     values less than (TO_DAYS('2022-06-17')) engine=innodb,
partition     p20220618     values less than (TO_DAYS('2022-06-18')) engine=innodb,
partition     p20220619     values less than (TO_DAYS('2022-06-19')) engine=innodb,
partition     p20220620     values less than (TO_DAYS('2022-06-20')) engine=innodb,
PARTITION   pcatchall    VALUES LESS THAN MAXVALUE                engine=innodb
);

因为这边分区时用到了TO_DAYS函数，所以sql语句里去掉了column关键字，如果不使用函数，也可以这样写

PARTITION BY RANGE columns (record_time) (
partition p20220601 values less than ('2022-06-01') engine=innodb,
partition p20220602 values less than ('2022-06-02') engine=innodb,

如果已经存在的表没有分区，也可以使用下面的语句添加分区

alter table xiaweiyi8080.`t_car_gps` drop primary key, add primary key (id, record_time);

alter table xiaweiyi8080.`t_car_gps` partition by range columns(record_time)(
partition p20210201 values less than('2021-02-02'),
partition p20210202 values less than('2021-02-03'),
partition p20210203 values less than('2021-02-04')
);

注意：

1.如果原来添加的分区日期到了临界值了，那就需要拓展分区了，不然所有新插入的值都会落到MAXVALUE分区里。

2.拓展分区时，要先把 MAXVALUE drop 掉，再从最大分区，上面是2022-06-21开始建分区，再把MAXVALUE加上。

拓展分区

alter table xiaweiyi8080.`t_car_gps` 
REORGANIZE PARTITION pcatchall 
INTO (
PARTITION p20220601 VALUES less than (TO_DAYS('20220601')),
PARTITION pcatchall VALUES less than MAXVALUE
);

查看某张表的分区信息

select 
partition_name,
partition_description,
PARTITION_EXPRESSION,
TABLE_ROWS 
from information_schema.partitions
where table_name = 't_car_gps' and table_schema = 'xiaweiyi8080';

指定分区查询数据

select * from t_car_gps partition(p20220201) where id > 0;

清空指定分区数据

ALTER TABLE people TRUNCATE PARTITION p20220601,p20220602;

清空全部分区数据

alter table people truncate partition all;

删除分区

alter table people drop partition p20220601;