基于UnixTime的动态MySQL分区 [英] Dynamic MySQL partitioning based on UnixTime
问题描述
每个行记录都包含自动递增的id，一些数据和一个表示unixtime的整数。
>我正在设计一个老化机制，我有兴趣使用MySQL分区来动态地基于unixtime分配每个这样的表。
说我有兴趣，每个分区将代表单个月份的数据，最后一个分区应该代表2个月，如果记录到达下一个未被表示的月份，则表示2个月的分区应该被重新组织以代表一个月，而新的应创建表示2个月的分区（1个取自最后一个分区，1个用于将来测量），
此外，当创建新分区时，我感兴趣的是，分区将被删除。
- 我应该使用什么类型的分区（我的unixtime不是唯一的密钥，我使用unixtime进行分区）？
- 根据添加到表中的新记录，我将如何将分区设计为完全动态？
更新12.12.12
我已经找到和有趣的链接到类似的方法，我有描述了您的魔法范围划分维护查询。
-
分区不需要完全基于唯一键。但是如果存在唯一的密钥，那么它应该包含在用于分区表的列中。要在UNIXTIME列上分区表，请执行以下操作：
ALTER TABLE MyTable
按RANGE COLUMNS分区（UNIX_TIMESTAMP（datetime_column））
（
分数p01值小于（2），
分数p02值小于（3），
分数p03值小于（4），
分数p04值小于（MAXVALUE））;
或者您可以在MySQL 5.5+中的datetime列stright上分区：
ALTER TABLE MyTable
按RANGE COLUMNS分段（datetime_column）
（
PARTITION p01 VALUES LESS THAN（'2013-01 -01'），
PARTITION p02价值不超过（'2013-02-01'），
PARTITION p03值不超过（'2013-03-01'），
PARTITION p04 VALUES小于（MAXVALUE））;
-
全自动版本（每个月都会保留在自己的分区中，5个月的数据持有）：
ALTER TABLE MyTable
按RANGE COLUMNS分隔（YEAR（datetime_column））* 100 + MONTH（datetime_column） ）
（
PARTITION p201301价值不超过（201301），
分部p201302价值不超过（201302），
分部p201303价值不超过（201303），
分部（201304），
PARTITION p201305值小于（201305），
分段p_MAXVALUE值小于（MAXVALUE））;
DECLARE @Min_Part int
DECLARE @Last_Part int
DECLARE @SQL varchar（1000）
如果（select从MyTable计数（不同MONTH（datetime_column））> 5 THEN
BEGIN
从MyTable中选择@Min_Part =（select min（year（datetime_column）* 100 + month（datetime_column）），
@Last_Part =（select max年份（datetime_column）* 100 +月（datetime_column））从$ MyBable
set @SQL ='更改表MyTable REORGANIZE PARTITION p_MAXVALUE（分区p'+ TO_CHAR（@Last_Part）+' （'+ TO_CHAR（@Last_Part）+'）'
调用common_schema.eval（@sql）
set @SQL ='更改表MyTable DROP PARTITION p'+ TO_CHAR （@Min_Part）
调用common_schema.eval（@sql）
END
PS如果SQL不完全正确 - 不能解析它。
My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing unixtime.
I am designing an aging mechanism, and i am interested to use MySQL partitioning to partition each such table based on unixtime dynamically.
Say that i am interested that each partition will represent single month of data, last partition should represent 2 months, if records arrive for the next not represented month, the partition that represented 2 months should be reorganized to represent single month, and new partition should be created representing 2 month (1 taken from the last partition and 1 for future measurements),
Additionally, when a new partition is created i am interested that the oldest partition will be dropped.
- What type of partitioning i should use (my unixtime is not a unique key, and how would i use unixtime for partitioning purposes)?
- How would i design the partitioning to be fully dynamical based on new records added to the tables?
UPDATE 12.12.12
I have found and interesting link to similar approach to what i have described your-magical-range-partitioning-maintenance-query.
Partitioning does not need to be based solely on a unique key. However if unique key is present, then it should be included in columns used to partition the table on. To partition table on UNIXTIME column do:
ALTER TABLE MyTable PARTITION BY RANGE COLUMNS (UNIX_TIMESTAMP(datetime_column)) ( PARTITION p01 VALUES LESS THAN (2), PARTITION p02 VALUES LESS THAN (3), PARTITION p03 VALUES LESS THAN (4), PARTITION p04 VALUES LESS THAN (MAXVALUE));Or you can partition on datetime column stright away in MySQL 5.5+ :
ALTER TABLE MyTable PARTITION BY RANGE COLUMNS (datetime_column) ( PARTITION p01 VALUES LESS THAN ('2013-01-01'), PARTITION p02 VALUES LESS THAN ('2013-02-01'), PARTITION p03 VALUES LESS THAN ('2013-03-01'), PARTITION p04 VALUES LESS THAN (MAXVALUE));Fully automated version (it would keep every month in its own partition, 5 months of data held):
ALTER TABLE MyTable PARTITION BY RANGE COLUMNS (YEAR(datetime_column)*100 + MONTH(datetime_column)) ( PARTITION p201301 VALUES LESS THAN (201301), PARTITION p201302 VALUES LESS THAN (201302), PARTITION p201303 VALUES LESS THAN (201303), PARTITION p201304 VALUES LESS THAN (201304), PARTITION p201305 VALUES LESS THAN (201305), PARTITION p_MAXVALUE VALUES LESS THAN (MAXVALUE)); DECLARE @Min_Part int DECLARE @Last_Part int DECLARE @SQL varchar (1000) If (select count (distinct MONTH(datetime_column)) from MyTable) > 5 THEN BEGIN select @Min_Part = (select min(year(datetime_column)*100 + month(datetime_column)) from MyTable), @Last_Part = (select max(year(datetime_column)*100 + month(datetime_column)) from MyTable) set @SQL = 'Alter table MyTable REORGANIZE PARTITION p_MAXVALUE (into partition p' +TO_CHAR (@Last_Part) + 'values less than (' + TO_CHAR (@Last_Part) + ')' call common_schema.eval (@sql) set @SQL = 'Alter table MyTable DROP PARTITION p' + TO_CHAR (@Min_Part) call common_schema.eval (@sql) END
P.S. Apologies if SQL is not exactly correct - cannot parse it right now.
这篇关于基于UnixTime的动态MySQL分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
