zabbix 四张大表分区

https://blog.csdn.net/zhaoyangjian724/article/details/52945649

 

trends_uint.ibd

history

history_unit

trends

CREATE TABLE `trends` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT ‘0’,
`num` int(11) NOT NULL DEFAULT ‘0’,
`value_min` double(16,4) NOT NULL DEFAULT ‘0.0000’,
`value_avg` double(16,4) NOT NULL DEFAULT ‘0.0000’,
`value_max` double(16,4) NOT NULL DEFAULT ‘0.0000’,
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock)  (PARTITION  p20150806 values less than (1438876800));

[oracle@oadb mysql]$ date -d 20150806 +%s
1438790400
[oracle@oadb mysql]$ date -d 20150807 +%s
1438876800

CREATE TABLE `trends` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT ‘0’,
`num` int(11) NOT NULL DEFAULT ‘0’,
`value_min` double(16,4) NOT NULL DEFAULT ‘0.0000’,
`value_avg` double(16,4) NOT NULL DEFAULT ‘0.0000’,
`value_max` double(16,4) NOT NULL DEFAULT ‘0.0000’,
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock)  (PARTITION  p20161027 values less than (1477584000));

mysql> select * from zabbix.trends;
+——–+————+—–+———–+———–+———–+
| itemid | clock      | num | value_min | value_avg | value_max |
+——–+————+—–+———–+———–+———–+
|  23678 | 1477537200 |  26 |    0.0000 |    0.0005 |    0.0025 |
|  23679 | 1477537200 |  26 |    0.0000 |    0.0084 |    0.0325 |
|  23680 | 1477537200 |  26 |    0.0000 |    0.0036 |    0.0075 |
|  23682 | 1477537200 |  26 |   82.6072 |   97.7308 |   99.9917 |
|  23683 | 1477537200 |  26 |    0.0000 |    0.0023 |    0.0330 |

[oracle@oadb mysql]$ date -d@1477540800 “+%Y-%m-%d”
2016-10-27

mysql> select * from trends partition(p20161027);
+——–+————+—–+———–+———–+———–+
| itemid | clock      | num | value_min | value_avg | value_max |
+——–+————+—–+———–+———–+———–+
|  23719 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23720 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23721 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23723 | 1477544400 |  17 |   99.9667 |   99.9735 |   99.9792 |
|  23724 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23725 | 1477544400 |  17 |    0.0000 |    0.0007 |    0.0042 |
|  23726 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23727 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23728 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
|  23729 | 1477544400 |  17 |    0.0167 |    0.0223 |    0.0250 |
|  23730 | 1477544400 |  17 |    0.0000 |    0.0034 |    0.0042 |
|  23734 | 1477544400 |  17 |  100.0000 |  100.0000 |  100.0000 |
|  23777 | 1477544400 |  17 |    0.0000 |    0.0000 |    0.0000 |
+——–+————+—–+———–+———–+———–+
13 rows in set (0.00 sec)

mysql> select * from trends partition(p20161028);
Empty set (0.00 sec)

CREATE TABLE `trends_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT ‘0’,
`num` int(11) NOT NULL DEFAULT ‘0’,
`value_min` bigint(20) unsigned NOT NULL DEFAULT ‘0’,
`value_avg` bigint(20) unsigned NOT NULL DEFAULT ‘0’,
`value_max` bigint(20) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock)  (PARTITION  p20161027 values less than (1477584000));

CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT ‘0’,
`value` double(16,4) NOT NULL DEFAULT ‘0.0000’,
`ns` int(11) NOT NULL DEFAULT ‘0’,
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (clock) (PARTITION p20161027 VALUES LESS THAN (1477584000));

CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT ‘0’,
`value` bigint(20) DEFAULT NULL,
`ns` int(11) NOT NULL DEFAULT ‘0’,
KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (clock) (PARTITION p20161027 VALUES LESS THAN (1477584000));
————————————————
版权声明:本文为CSDN博主「zhaoyangjian724」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/zhaoyangjian724/article/details/52945649

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注