mysql分区表

1.使用场景

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有任店数据，其他均是历史数据

• 分区表的数据更容易维护

• 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

• 可以使用分区表来避免某些特殊瓶颈

• 如果需要还可以备份和恢复独立的分区，这在的非常大的数据集的场景下效果非常好

2.分区限制：

• 一个表最多只能有1024个分区

• range和list分区类型，如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

• 分区表中无法使用外键约束

3.分区类型

• range分区：基于属于一个给定连续区间的列表，必须是连续且不能重叠的。
• list分区：类似于按range分区，区别在于list分区是基于列值分配一个离散值集合中的某个值来进行选择
• hash分区：基于用户定义的表达时的返回值来进行选择分区，该表达式使用插入到表中的的这些的列值进行计算
• key分区：类似于按hash分区，区别在于key分区只支持计算一列或多列，且mysql服务器提供其自身的哈希函数

4.range分区
1）values less then()定义分区
2）非整数的范围值需要使用单引号
3）分区的最高值使用MAXVALUE表示
4）分区列插入null值会将其当作最小值来处理插入最小分区中

-- 创建range分区
CREATE TABLE `tb_aa` (
  `id` int(11) NOT NULL DEFAULT '0',
  `addtime` int(11) NOT NULL DEFAULT '0',
  `addr` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`addtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`addtime`)
(PARTITION p20220102 VALUES LESS THAN (1641052800) ENGINE = InnoDB,
 PARTITION p20220109 VALUES LESS THAN (1641657600) ENGINE = InnoDB,
 PARTITION p20220116 VALUES LESS THAN (1642262400) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 

-- 添加分区,使用add partition只能是添加最大分区
alter table tb_aa add partition (partition p20220123 values less than (1642867200));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

-- 清空分区数据
alter table tb_aa truncate partition p20220102;

-- 删除分区
alter table tb_aa drop partition p20220102;

-- 移除分区,不会删除数据,只是移除分区定义
alter table tb_aa remove partition;

5.list分区

1）values in定义分区
2）null值必须出现在枚举列中，否则不被接受

-- 创建list分区
CREATE TABLE `tb_bb` (
  `id` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY LIST (`type`)
(PARTITION p_guangzhou VALUES IN (1,3,5,7,9) ENGINE = InnoDB,
 PARTITION p_shenzhen VALUES IN (2,4,6,8,10) ENGINE = InnoDB) */ 

-- 添加分区
alter table tb_bb add partition(partition p_foshan values in (11,13,15));


-- 删除，清理分区同range分区类型一样
alter table tb_bb trncate partition p_guangzhou;
alter table tb_bb drop partition p_guangzhou;

6.hash分区

1）只能针对整数进行hash分区
2）mysql支持两种hash分区，一是常规hash(hash)和线性hash(linear hash)
3）null值会被当零值来处理
4）分区列不一定要求为主键，但必须为非空

-- 创建hash分区
CREATE TABLE `tb_cc` (
  `id` int(11) NOT NULL DEFAULT '0',
  `storeid` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
/*!50100 PARTITION BY HASH (`storeid`)
PARTITIONS 5 */

-- 创建linear hash

CREATE TABLE `tb_dd` (
  `id` int(11) NOT NULL DEFAULT '0',
  `storeid` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LINEAR HASH (`storeid`)
PARTITIONS 5 */ 

-- 增加分区
alter table tb_cc add partition partitions 3;

-- 删除分区
alter table tb_cc coalesce partition 2;

7.key分区

1）null值会被当零值来处理

-- 创建key分区
CREATE TABLE `tb_ee` (
  `id` int(11) NOT NULL DEFAULT '0',
  `addr` varchar(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY LINEAR KEY (id)
PARTITIONS 3 */ 

-- 添加和删除分区与hash分区语法一致