分区表

对分区表，之前有点误解，自己建了一些表table_yearmoth。但是这不是分区表。由于需要按时间分区，一般都需要将主键跟分区的时键字段做一个联合索引，才能分区。分区的用法使用如下：

Range分区

原表结构

分区表的分区字段必须是在联合主键中. created部分所示. created在联合主键中.

CREATE TABLE `yc_advert_records` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `advert_id` int unsigned NOT NULL COMMENT '广告ID',
  `platform_id` int unsigned NOT NULL COMMENT '广告平台ID',
  `mailno` bigint unsigned NOT NULL COMMENT '运单号',
  `visit_count` smallint unsigned NOT NULL DEFAULT '0' COMMENT '访问次数',
  `created` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`,`created`) USING BTREE,
  KEY `mailno` (`mailno`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1415278 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='广告访问记录'

建立分区表

数据存储到分区

• p0分区, 通过检索语句发现 2020-08-01之前的数据都是存在 p0分区的.

- 2020-08-01 - 2020-08-30 之间的数据是存储在p1分区

• BETWEEN “2020-07-01 00:00:00” AND “2020-08-30 00:00:00” 是存在两个分区的,p0,p1

查看分区使用情况

SELECT * FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=’yc_advert_records’;

测试说明:

1500万数据测试
不跨区域查询响应速度<=0.5s，跨区查询在第一次比较慢，但之后在翻页查询的时候，相应速度<=1s
查询的时候由于计算机性能差异，所以同样的查询在不同的机器上查询速度会有所不同

分区表相关介绍

• 由于range分区函数无法识别char型字段，所以要在分区之前将collectTime由char类型改为datetime类型，才能进行range分区操作。

• 采用range分区时，要用to_days(collectime)的分区方式，采用这种方式，在查询的时候只会在相应的分区查找，而如果不加to_days(),在查询的时候，会对全表进行扫描。

• 分区优化后，查询速度提升主要体现在非跨区查询的时候，当查询条件均属于一个区域时，数据库可以快速定位到所查分区，而不会扫描全表。

• 每次插入数据的时候，数据库会判定对应的collectTime属于哪个分区，从而存储到对应的分区中，不会影响其它分区。

分区函数支持情况

不要硬记忆.能查询获取个版本支持哪些函数即可.

mysql5.1支持的partition函数。

mysql5.5支持的partition函数。不要硬记忆.能查询获取个版本支持哪些函数即可.

mysql5.6支持的partition函数。

list分区

分区字段必须是整型或者转换为整型 ， 按照某个字段上的规则，不同的数据离散地分布在不同的区中。

对于List分区，分区字段必须是已知的，如果插入的字段不在分区时枚举值中，将无法插入 。

示例：

create table test_list_partiotion
(
    id int auto_increment,
    data_type tinyint,
    primary key(id,data_type)
) ENGINE=InnoDB partition by list(data_type)
(
    partition p0 values in (0,1,2,3,4,5,6),
    partition p1 values in (7,8,9,10,11,12),
    partition p2 values in (13,14,15,16,17)
);

Hash分区

分区字段必须是整型或者转换为整型

Hash分区可以将数据均匀地分不到预先定义的分区中，使得各个分区的数据量分布基本上一致。同样，分区字段必须是整型或者转换为整型

create table test_hash_partiotion
(
    id int auto_increment,
    create_date datetime,
    primary key(id,create_date)
) ENGINE=InnoDB partition by hash(year(create_date)) partitions 10;

一个很明显的问题就是，如果分区字段本身的分布不匀均，那么hash分区之后存储的分区也是不均匀的，hash分区时对于hash的字段，需要慎重。
对于单个值的查询hash分区可以定位到某一个分区

hash分区在查询优化方面，无法优化范围查询，因为无法确定一个某个字段经过hash计算之后究竟分布了在哪个分区之中。

Key分区

分区字段必须是整型或者转换为整型
与hash分区不用的是，key分区使用MySQL自定义的库函数进行分区，不需要hash分区那样对字段整型进行转换，同样，分区字段必须是整型或者转换为整型

create table test_key_partiotion
(
    id int auto_increment,
    create_date datetime,
    primary key(id,create_date)
) ENGINE=InnoDB  partition by key(create_date) partitions 10;

column 分区

解决了分区字段必须是整型或者必须转换为整型的限制，可以对整型，date或者datetime进行支持。

create table test_column_partiotion
(
    id int auto_increment,
    data_type datetime,
    primary key(id,data_type)
) ENGINE=InnoDB partition by range columns(data_type) (
    partition p0 values less than ('20180101'),
    partition p1 values less than ('20180201'),
    partition p2 values less than ('20180301'),
    partition p3 values less than ('20180401'),
    partition p4 values less than ('20180501'),
    partition p5 values less than ('20180601'),
    partition p6 values less than ('20180701'),
    partition p7 values less than ('20180801')
);

注意事项

指定存储引擎为ENGINE=InnoDB，另外顺序上，分区相关的语句在最后。

代码及示例

模板工具

<?php
/**
 * 2021-08-04 11:42:50
 */
$time = strtotime('2021-09-01');
for($i = 0 ;$i<30;$i++){
	$list[]= strtotime("+$i months",$time);
}
	var_dump($list);
?>

<?php foreach($list as $v):?>
    partition p<?=date('Ym',strtotime('-1 month',$v))?> values less than ('<?=date('Ymd',$v)?>'),
<?php endforeach;?>
    partition pmax values less than (MAXVALUE)

示例sql

CREATE TABLE `yc_qr_records` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `aid` int unsigned NOT NULL COMMENT '广告ID',
  `uid` int unsigned NOT NULL COMMENT '广告主ID',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   PRIMARY KEY (`id`,`created_time`) USING BTREE,
   KEY `created_time_idx` (`created_time`, `aid`, `uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='广告访问记录' partition by range columns(created_time) (
    partition p202108 values less than ('20210901'),
    partition p202109 values less than ('20211001'),
    partition pmax values less than (MAXVALUE)
);

示例sql2

参见上图。区别点，增加了计算函数,to_days。

而to_days的逆函数为，FROM_DAYS

select FROM_DAYS(738003);