zabbix 历史表分区和迁移测试



某些行业数据量的增长速度极快，随着数据库中数据量的急速膨胀，数据库的插入和查询效率越来越低。此时，除了程序代码和查询语句外，还得在数据库的结构上做点更改；在一个主读辅写的数据库中，当数据表数据超过1000w行后，那查询效率真的很让人抓狂。就算早前建了索引，也很难满足用户对于系统查询效率的体验。

优化方案是分表或分区。至于分区的原理以及分区和分表的区别，搜索一下，都介绍的很详细，这里就不作冗余介绍。简单来讲，分表旨在提高数据库的并发能力，分区旨在优化磁盘的IO和数据的读写，所以采用什么方案，还得根据业务再作斟酌。由于我们的系统对并发要求不高，所以便采用了分区。

分区是MySQL5.1以后实现的。其中分区类型有RANGE分区、LIST分区、HASH分区、KEY分区。我们这里是使用RANGE分区来讲解。

分区需要注意的一点是：要么不定义主键，要么把分区字段添加到主键中。并且分区字段不能为NULL，要不然就难以确定分区范围。所以要设为NOT NULL。

首先执行一下show plugins; 查看partition这一栏是否为ACTIVE，是则表示数据库支持分区。

1、创建一个数据表并分区：

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 p161130 VALUES LESS THAN (1480550399),

    PARTITION p161231 VALUES LESS THAN (1483228799),

    PARTITION p170131 VALUES LESS THAN (1485907199),

    PARTITION p170228 VALUES LESS THAN (1488326399),

    PARTITION p170331 VALUES LESS THAN (1491004799),

    PARTITION p170430 VALUES LESS THAN (1493596799),

    PARTITION p170531 VALUES LESS THAN (1496275199),

    PARTITION p170631 VALUES LESS THAN (1498867199),

    PARTITION pnow VALUES LESS THAN MAXVALUE

);

2、修改一个数据表分区：

ALTER TABLE `history_uint`

PARTITION BY RANGE(`clock`) (

       PARTITION p161130 VALUES LESS THAN (1480550399),

　　PARTITION p161231 VALUES LESS THAN (1483228799),

　　PARTITION p170131 VALUES LESS THAN (1485907199),

　　PARTITION p170228 VALUES LESS THAN (1488326399),

　　PARTITION p170331 VALUES LESS THAN (1491004799),

　　PARTITION p170430 VALUES LESS THAN (1493596799),

　　PARTITION p170531 VALUES LESS THAN (1496275199),

　　PARTITION p170631 VALUES LESS THAN (1498867199),

　　PARTITION pnow VALUES LESS THAN MAXVALUE

);

说明：1、2中使用end_time (时间是以时间戳的形式记录的) 作为分区字段对表进行分区。分区的区分值为分区名中的时间的时间戳形式，比如2016/11/30 23:59:59 转为秒数为1480550399。以上的代码中，我将数据表分为9个区，从16年11月30日到17年06月31日 共8个区加上pnow这个区存放17年6月31日以后的数据；如上所示，16年11月30日以前的数据，将会存放在p161130这个分区中16年12月01日至16年12月31日的数据将会存放在p161231分区中，以此类推…

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

 

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

分区后可以执行以下语句查看效果（后面也可以用该语句查看每个分区中有多少数据）：

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'history_uint';

3、删除一个分区：

执行语句：ALTER TABLE history_uint DROP PARTITION p170331;

注意：删除一个分区时，该分区内的所有数据也都会被删除；

如果用这样来删除数据，要比用delete from table_name where …要有效得多；

4、新增一个分区：

执行语句：ALTER TABLE history_uint ADD PARTITION (PARTITION p170731 VALUES LESS THAN (1501516800));

注意：如果原先最后一个分区是PARTITION pnow VALUES LESS THAN MAXVALUE; 那么应该先删除该分区，然后在执行新增分区语句，然后再新增回该分区；

1,ALTER TABLE history_uint DROP PARTITION pnow;

2,SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'history_uint';

3,ALTER TABLE history_uint ADD PARTITION (PARTITION p170731 VALUES LESS THAN (1501516800));

4,ALTER TABLE history_uint ADD PARTITION (PARTITION pnow VALUES LESS THAN (MAXVALUE));

                                                                                                       模拟表分区迁移测试

交换分区的实现

1、交换分区的语法

alter table pt exchange partition p with table nt;

解释：

• 将 分区表pt 的 分区p 和 一个普通表nt 中的数据 进行互换。

交换的前提条件：

• 普通表nt 不为临时表，且不是分区表。
• 表结构和分区表pt的结构一致。
• 普通表nt 没有外键引用。
• 普用表nt 若为非空。在MySQL5.6以及之前的版本，必须在分区的范围内；在MySQL5.7之后则可以不再其分区范围内，也依然会成功存入该分区中。

CREATE TABLE `history_uint_1711` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

alter table history_uint exchange partition p171130 with table history_uint_1711;

区后可以执行以下语句查看效果（后面也可以用该语句查看每个分区中有多少数据）：

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'history_uint';

现在的分区数据 已经没有了