MySQL 分区表介绍与测试

一、基本介绍：

分区表是一个独立的逻辑表，底层由多个物理的文件组成，分区对于 SQL 层来说是透明的；

下图可以看出，表 em_elec_meter_data 的多个分区是使用了 # 进行标识。

分区表的使用场景：

• 表非常大以至于无法全部都放在内存中，或者热点数据只有在表的最后部分，其他都是历史数据。
• 分区表的数据作用在不同的设备上，高效地利用多个硬件设备；例如有 1TB 的数据，服务器的硬盘容量只有 500 GB，那么可以使用其他设备平摊未能放下的数据。
• 需要独立备份和恢复分区，这在非常大数据集的场景下效果非常好。

分区表的限制：

• 一个表最多只能有 1024 个分区。
• MySQL 5.1 中，分区表表达式必须是整数，或者返回整数表达式。在 MySQL 5.5 中，某些场景中可以直接使用列来进行分区。
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须组合成一个主键；原因是防止同一个主键一个在 p0 区，一个在 p1 区。
• 分区表中无法使用外键约束（现在也没人使用外键，都是程序保证约束）。
• 在 MySQL 8 中不支持 myIsam 存储引擎使用分区表。

分区表的类型：

• 范围（range）分区，例如某年某月、不同省份的区域范围（常用）

  注：每个分区都是按顺序进行定义，从最低到最高，例如以下的 2023 不能在 2022 的前面，这是 PARTITION BY RANGE 语法的要求

  # 按照范围分区的表
  create table `range_partition1` (
    `id` bigint not null auto_increment,
    `date_time` datetime not null,
    `range_name` varchar(50) default null,
    primary key (`id`,
  `date_time`)
  ) ENGINE = InnoDB default CHARSET = utf8mb4 collate = utf8mb4_0900_ai_ci partition by range(year(date_time))
  (
      -- 必须按照顺序定义，否则报错
  	partition p0 values less than (2022),
  	partition p1 values less than (2023)