MySQL 分区表的维护策略

[小灰狼]

项目中一个表用于存储设备的运行时数据，如电压、电流、温度等，表结构基本：
Id BigInt Unsigned Not Null Auto_Increment Comment '主键',
DeviceId Int Unsigned Not Null Comment '设备Id',
DeviceDateTime Bigint Not Null Comment '设备时间，格式yyyyMMddHHmmss',
V1 Int Signed Comment '电压值',
I1 Int Signed Comment '电流值',
......

由于设备数量大，数据采集周期短，因此这个表数据量会很大，而业务需求基本上会按设备Id和时间范围进行查询，因此分区方案是按DeviceDate进行Range分区
经过测试，发现一个分区的数据量在1亿到1.3亿之间是比较好的，太大时查询效率不高，太小时没什么优势。为此我们写了一个事件，每天检查分区情况，如果必要则追加一个分区。
但是设备是慢慢接入的，系统初始上线以及后期运营过程中，很难估算一个固定时间范围会产生多少数量，这样导致一个分区要划分多长时间变得很困难。理想的情况是，每天检查最后一个分区，如果分区的数据量达到一个值，则追加一个分区，从第二天起的数据存储到新的分区中去。
但是在 mysql 中，一个range分区的边界值是固定了的，无法修改，并且也没有提供类似oracle那样，会提供一个缺省分区，当数据不满足列表中的要求时，存储到缺省分区 中，这该如何处理啊？

[小灰狼]

引用 2 楼 zjcxc 的回复:

create table _p(id int primary key) 
	partition by range(id)(
		partition p0 values less than(1),
		partition pm values less than maxvalue
	);
--  <1 和 >=1 的两个区，可以容纳任意数据
insert into _p values(0),(1),(2),(3);
select * from _p partition(p0);
select * from _p partition(pm);

-- 最后一个区pm 为默认区，觉得数据多了，拆分成 3 个: <1, <2,, >=2
alter table _p reorganize partition pm into(
		partition p1 values less than(2),
		partition pm values less than maxvalue
	);
select * from _p partition(p0);
select * from _p partition(p1);
select * from _p partition(pm);

drop table if exists _p;

3K U 4 Ur reply 我要解决的麻烦问题是，如何进行动态地增加分区，使一个分区的数据量不超过1.3亿 因为数据采集周期基本上是固定的，但是设备数量无法预估计，因此我希望在事件中每天（或每周）检查分区中的数据量，如果达到一个量，则将这个分区再进行重新划分 现在的思路是，系统刚上线时，只创建一个分区，即pmax，然后一个事件每天检查这个分区的数据量：select count(Id) from DeviceData partition(pmax)，如果 pmax 中的数据量超过1.3亿，则执行 alter table t1 reorganize partition pmax into (partition p20180601 values less than (20180601), partition pmax values less than (maxvalue)); 但是我刚才测试时发现，当分区 pmax 数据量过大时（其实也不大，也就100来W）执行上述语句要将近30秒。如果是在运行环境里，必须造成业务被阻塞30来秒。 现在想知道有没有优化的办法，因为在执行 reorgnize 时，我一定会把 pmax 中的数据全部划分到一个分区，新的 pmax 绝对是没有数据的。

[zjcxc]

对于包含大量数据的分区进行拆分是肯定花时间的，考虑换个策略吧 你应该预先为未来的数据分配两个分区，一个存放随后会加入的数据（根据历史数据确定这个分区存储到指定量的数据，应该达到的上限是多少），另一个是 maxvalue 的分区，用来存放不可预估的数据 然后定时，当最近分区的数据即将达到上限时，估算下个分区的上限，然后对 maxvalue 的分区进行分拆，这时候 maxvalue 分区通常是应该不包含数据的，分拆可以很快完成

[zjcxc]

可以使用 VALUES LESS THAN MAXVALUE 做为缺少分区

[zjcxc]

create table _p(id int primary key) 
	partition by range(id)(
		partition p0 values less than(1),
		partition pm values less than maxvalue
	);
--  <1 和 >=1 的两个区，可以容纳任意数据
insert into _p values(0),(1),(2),(3);
select * from _p partition(p0);
select * from _p partition(pm);

-- 最后一个区pm 为默认区，觉得数据多了，拆分成 3 个: <1, <2,, >=2
alter table _p reorganize partition pm into(
		partition p1 values less than(2),
		partition pm values less than maxvalue
	);
select * from _p partition(p0);
select * from _p partition(p1);
select * from _p partition(pm);

drop table if exists _p;

[小灰狼]

引用 6 楼 zjcxc 的回复:

那你考虑 EXCHANGE PARTITION，数据始终放到 MAXVALUE 的分区，在数据量达到拆分前，通过 EXCHANGE PARTITION 把 MAXVALUE 分区的数据放到一个同结构的空表中，这样分区就空了，然后你做拆分，因为没有数据，这个可以极快完成，完成后你再通过 EXCHANGE PARTITION 把数据切回到新拆出来的分区中 整个过程你可以通过 LOCK TABLE WRITE 锁表来实现访问的一致性（整个操作过程时间极短，所以这个锁的影响极小）

你好 你的方案里，怎么把交换出去的分区再交换回来呢？ 尝试前面的过程都比较成功，但 mysql 要求交换分区时，接收数据的那个表是非分区表 假设 t1 是数据表，t2 是空表：

Create Table t2 Like t1;
Alter Table t2 Remove Partitioning;
Alter Table t1 Exchange Partition pmax With t2;
Alter Table t1 Reorganize Partition pmax Into (
    Partition p20180601 Values Less Than (20180601000000),
    Partition pmax Values Less Than (Maxvalue)
);
-- 接下来如何把 t2 中的数据交换回来？

[zjcxc]

称换出一样的语句 Alter Table t1 Exchange Partition pmax With t2; 这个操作是互换，不是单纯的换出或换入，也就是允许两个都有数据，执行就是互相交换，如果是一个有数据，那就是换入或换出了

[小灰狼]

引用 8 楼 zjcxc 的回复:

称换出一样的语句 Alter Table t1 Exchange Partition pmax With t2; 这个操作是互换，不是单纯的换出或换入，也就是允许两个都有数据，执行就是互相交换，如果是一个有数据，那就是换入或换出了

试了一下，成功交换，并且速度也非常快 目前还有一个问题： 查询一个分区的数据量时，如果用 Select Count(Id) From t1 Partition(p20180601) 会消耗很长时间，估计会对业务性能也有一定影响 而使用 Select Table_Rows From information_schema.`PARTITIONS` where TABLE_SCHEMA = database() And Table_Name = 't1' And ...... 时，查询结果非常快，但得到的行数量却比前一条查询语句少了不少。

[小灰狼]

引用 4 楼 zjcxc 的回复:

对于包含大量数据的分区进行拆分是肯定花时间的，考虑换个策略吧 你应该预先为未来的数据分配两个分区，一个存放随后会加入的数据（根据历史数据确定这个分区存储到指定量的数据，应该达到的上限是多少），另一个是 maxvalue 的分区，用来存放不可预估的数据 然后定时，当最近分区的数据即将达到上限时，估算下个分区的上限，然后对 maxvalue 的分区进行分拆，这时候 maxvalue 分区通常是应该不包含数据的，分拆可以很快完成

我以前的处理方案与你说的差不多： 先估计一下达到预定上限的数据量大约需要的时间，比如估计是一个季度。则写一个事件，每天检查下一个季度的时间点是否即将到来，如果时间快到了，则再分配新的分区。只不过，我原来没有定义 maxvalue 分区。因为分区规则中的时间一定不会超过当前系统时间。 但是这样做的弊端是，新增加分区的依据是固定的时间范围，而将来的数据量以哪种形式增长我们很难估计。有时可能市场拿到一个大单，突然增加几千个设备，必然导致从那时起分区的数据量比前面的分区增加快很多，也有可能系统上线后，连续几个月没什么量的增长，这时分区的数据量会很少。

[zjcxc]

那你考虑 EXCHANGE PARTITION，数据始终放到 MAXVALUE 的分区，在数据量达到拆分前，通过 EXCHANGE PARTITION 把 MAXVALUE 分区的数据放到一个同结构的空表中，这样分区就空了，然后你做拆分，因为没有数据，这个可以极快完成，完成后你再通过 EXCHANGE PARTITION 把数据切回到新拆出来的分区中 整个过程你可以通过 LOCK TABLE WRITE 锁表来实现访问的一致性（整个操作过程时间极短，所以这个锁的影响极小）

[小灰狼]

在 Navicat 或者命令行终端执行是没问题 但是放到存储过程里，提示不在能存储过程里出现锁语句 而放到应用程序里，则在执行 Exchange Partition 时： 如果先执行 Lock table t1; 再执行 Lock table t2; 会提示 t1 表没有锁； 而如果先执行 Lock table t2; 再执行 Lock table t1; 则会提示 t2 表没有锁； 错误提示语句：“Table t1 was not locked with LOCK TABLES” 这不会要用手动方式每天去执行一次吧！

[小灰狼]

知道了，锁表要放在一条语句里：Lock table t1 write, t2 writer;

[zjcxc]

正常现象，需要准确的记录必须做 COUNT，大致的就可以直接查系统表，这个不是实时的记录数