Mysql分区表

分区不是在引擎层实现的，所以常见的引擎都支持，至少MyISAM和InnoDB是支持的。就访问数据库的程序而言，从逻辑上将，只有一个表或一个索引，但在物理上这个表或者索引可能由数十个物理分区组成。每个分区都是独立的对象，可以单独处理，也可以作为一个更大对象的一部分处理。

MySQL支持水平分区，即按行分区，不支持垂直分区。MySQL数据库的分区是局部分区索引，一个分区既存放数据也存放索引。

可通过如下命令查看当前数据库是否启用了分区功能。

show variables like '%partition%';
或者
show pulgins;

分区类型

RANGE分区：行数据基于一个给定连续范围分区。不好理解，看例子吧。5.5开始支持RANGE COLUMNS分区。

LIST分区：同RANGE，区别在于给定的不是连续范围，是离散的值。5.5开始支持LIST COLUMNS分区。

HASH分区：根据用户自定义的表达式的返回值进行分区，返回值不能是负数。

KEY分区：根据MySQL内部提供的哈希函数进行分区。

COLUMNS分区：5.5开始支持，可以直接使用非整形的数据进行分区，分区根据类型直接比较而得，不需要转换为整形。

无论创建何种类型的分区，如果表中存在主键或唯一索引的列，则分区列必须是主键或唯一索引的一部分。索引列可以是null值。在没有主键和唯一索引的表中可以指定任意列为索引列。

按日期分表（range）

datetime

CREATE TABLE `km_sensor_info_his` (
	`DEVICE_ID` VARCHAR (16) NOT NULL COMMENT '设备编号  temp：温度 humidity：湿度 hzsppm：硫化氢浓度 energy:电量',
  `TYPE` VARCHAR (11) NOT NULL COMMENT '设备类型',
	`TEMP` FLOAT (11, 1) DEFAULT NULL COMMENT '参数名称 temp：温度',
	`HUMIDITY` FLOAT (11, 1) DEFAULT NULL COMMENT '参数名称 humidity：湿度',
	`HZSPPM` FLOAT (11, 2) DEFAULT NULL COMMENT '参数名称 hzsppm：硫化氢浓度 ',
	`MOISTURE` FLOAT (11, 1) DEFAULT NULL COMMENT '参数名称 moisture:水分',
	`ENERGY` FLOAT (11, 0) DEFAULT NULL COMMENT '参数名称 energy:电量',
	`COLLECT_TIME` datetime NOT NULL COMMENT '时间戳',
	`import_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`PRESSURE` FLOAT (11, 2) DEFAULT NULL COMMENT '气压',
	`CSQ` FLOAT (11, 2) DEFAULT NULL COMMENT '信号强度',
	`device_status` TINYINT (2) DEFAULT NULL COMMENT '设备状态',
	`run_status` TINYINT (2) DEFAULT NULL COMMENT '设备运行状态',
	`protect` TINYINT (2) DEFAULT NULL COMMENT '保护状态',
	`micro_range_hzsppm` FLOAT (11, 2) DEFAULT NULL COMMENT '小量程',
	`macro_range_hzsppm` FLOAT (11, 1) DEFAULT NULL COMMENT '大量程',
	`exception_status` TINYINT (1) DEFAULT '1' COMMENT '是否异常，1为无异常,2为异常',
	`sw_ver` VARCHAR (30) NOT NULL COMMENT '软件版本',
	PRIMARY KEY (`DEVICE_ID`,`TYPE`, `COLLECT_TIME`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8 
PARTITION BY RANGE(TO_DAYS(COLLECT_TIME))
(
PARTITION part1 VALUES LESS THAN ( TO_DAYS('20190101') ),
PARTITION part2 VALUES LESS THAN ( TO_DAYS('20190701') ),
PARTITION part3 VALUES LESS THAN ( TO_DAYS('20200101') )
);

新增分区

ALTER TABLE `km_sensor_info_his_f` ADD PARTITION (
	PARTITION p4
	VALUES
		LESS THAN (TO_DAYS(20200701)) ENGINE = INNODB
);

分区成功

查看执行结果

EXPLAIN
SELECT *
FROM
	km_sensor_info_his_f
WHERE
	COLLECT_TIME BETWEEN '2019-07-23 15:13:54.0' AND '2019-08-24 15:13:54.0';

可以看到分区成功，但mysql默认扫描第一个分区，所以可以将第一个分区置空，提升速率

没有分区的表

查看分区信息

SELECT
	partition_name part,
	partition_expression expr,
	partition_description descr,
	table_rows
FROM
	INFORMATION_SCHEMA. PARTITIONS
WHERE
	TABLE_SCHEMA = SCHEMA ()
AND TABLE_NAME = 'km_sensor_info_his_f';

timestamp

CREATE TABLE `km_sensor_info_his` (
	`DEVICE_ID` VARCHAR (16) NOT NULL ,
	`TYPE` VARCHAR (11)  NOT NULL,
	`COLLECT_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`DEVICE_ID`, `TYPE`,`COLLECT_TIME`) USING BTREE
) ENGINE = INNODB  DEFAULT CHARSET=utf8 
PARTITION BY RANGE (
	UNIX_TIMESTAMP(COLLECT_TIME)
)(
	PARTITION part_1 VALUES LESS THAN (1546272000) ENGINE = INNODB,
	PARTITION part_2 VALUES LESS THAN (1559318400) ENGINE = INNODB,
	PARTITION part_3 VALUES LESS THAN (1577808000) ENGINE = INNODB
);

List分区

LIST分区和RANGE分区非常相似，只是LIST分区的值是离散的。与RANGE分区的 VALUES LESS THAN 不同，LIST分区使用 VALUES IN，所以每个分区的值是离散的，只能是定义的值。如

create table t(
a int(11), b int(11)
)engine=innodb partition by list(b)(
partition pe values in(1,3,5,7,9),partition p1 values in(2,4,6,8,10)
);

如果插入的值不在分区定义中，会报错

insert into t select 11;
[Err] 1526 - Table has no partition for value 11

参考链接：
Mysql分区表
Mysql基于时间字段进行分区