PolarDB MySQL分区表 分区方式及各种方式适用场景

分区作为传统企业级数据库的特性，早已经在很多大数据和数仓场景中得到广泛应用。基于维基百科的解释，分区是将逻辑数据库或其组成元素如表、表空间等划分为不同的独立部分。数据库分区通常是出于可管理性、性能或可用性的原因，或者是为了负载平衡。它在分布式数据库管理系统中很流行，其中每个分区可能分布在多个节点上，节点上的用户在分区上执行本地事务。这提高了具有涉及某些数据视图的常规事务的站点的性能，同时保持可用性和安全性。

MySQL分区为什么在云托管RDS时代被分库分表概念所代替？这个我们不得不回到用户场景中，由于MySQL是单机版本加上主备模式部署，除了其自身优化器执行器能力差和基于索引组织存储的限制外，还受到硬件资源的限制。虽然在云托管时代通过一写多读的系统部署架构来解决读写的吞吐，但由于MySQL的设计之初并没有想到有这么大规模的使用需要，即使它在8.0的最新版本中仍然努力的不断演进和重构，仍然无法满足用户场景的需要，这也让MySQL的分区表只是一个支持的功能，更没法说通过使用MySQL分区表来彻底解决大表的痛点，例如：

1. 无法支持更多更丰富的分区类型组合，导致用户使用受限
2. 无法支持分区锁，导致用户在运维分区（增加和删除分区等）的时候，影响了其他分区上的业务（数据增删改查）
3. 无法支持并发处理能力，导致数据存储后无法进行大规模分析
4. 优化器不支持分区相关的优化（只有静态pruning剪枝），导致执行计划和执行层不能很好的选择合适的计划，也没有能够选择的执行手段，从而带来性能问题
5. 分区切换代价和调用栈增加，导致很多场景性能和单表下降10%+
6. 无法支持数据生命周期的管理，只能借助用户自己的脚本或者外部工具的配置
7. 不支持多机访问，无法达到资源隔离的读写扩展能力

因此，由于当前的云原生数据库自身发展的不成熟，退而求其次的方案诞生了，那就是使用分库分表。虽然对用户不友好，但是架不住使用灵活方便，简单易用也是非常占优势的方式。另外，可以通过加强JDBC驱动或者Proxy的增强，如Sharding JDBC，TDDL等，也能够解决客户的问题。当然分库分表作为中间的历史产物，也终究在云原生的时代所淘汰，因为它也带来了很多问题：

1. 不同异构软件的系统架构部署，交互无法做到高效，运维复杂
2. 元数据信息等冗余存储
3. SQL语句兼容性弱，解析等步骤重复执行
4. 无法支持复杂查询，跨库查询性能极差
5. 各个数据节点各自为政，很难做到高效交互，依赖于下层的适配改造
6. 分布式事务问题
7. 横向扩容的问题
8. 结果集合并、排序的问题

当然，很多中间件希望努力解决上述的问题，将它改造成为非常强大的“数据库系统”，来解决上述问题，虽然是快捷的方式，但是无疑是忽略了已经沉淀的下层的数据库的计算。简单将数据库看做需要联邦的异构数据库，或者看成是存储下推计算的引擎，只会让整体系统更为复杂，兼容性变的更差，难以运维，本文由于单独介绍分区技术，这里就不再赘述。

PolarDB MySQL大表解决方案 - 分区特性增强（PolarDB Partitioning Feature）

虽然MySQL可以支持原则上64T（16KB Page）的数据量，但是实际上由于数据存储的格式是b+tree，增删改造成的索引分裂和大数据量查询性能都急剧下降，常常让用户对大表后续的维护难以下手。分区表提供了解决支持非常大的表和索引的关键技术，方法是将它们分解为更小且更易于管理的部分，称为分区（Partition），这些部分对应用程序完全透明。无需修改 SQL 查询和数据操作语言 (DML) 语句即可访问分区表。在定义分区之后，数据定义语言 (DDL) 语句可以访问和操作单个分区，而不是整个表或索引。这就是分区如何简化大型数据库对象的可管理性。表或索引的每个分区可以具有单独的物理属性，例如启用或禁用压缩、物理存储设置和表空间，而不需要存储更多共用的元数据信息。

分区对于管理大量数据的用户应用有很大的帮助。OLTP 系统可以受益于可管理性和可用性的改进，而OLAP数据仓库系统可以受益于其性能改进和可管理性。

分区具有以下优点：

• 它支持在分区级别而不是在整个表上进行数据加载、索引创建和重建以及备份和恢复等数据管理操作。这导致这些操作的时间大大减少。
• 它提高了查询性能。通常可以通过访问分区的子集而不是整个表来获得查询的结果。对于某些查询，分区修剪技术可以提供数量级的性能提升，减少无效IO访问。
• 分区维护操作的分区独立性，允许用户对同一表或索引的一些分区执行维护操作，而同时保证无运维操作的分区运行并发和DML操作不受影响。查询以及 DML 和 DDL 支持并行执行。
• 如果将关键表和索引划分为分区以减少维护窗口，则可以提高关键应用的数据库的可用性。
• 无需重写应用就可以利用分区能力。
• 更容易的数据生命周期管理能力。

PolarDB分区表完全兼容原生MySQL的语法和功能。同时，PolarDB分区表相对于原生MySQL进行了性能增强，支持丰富的分区类型及组合，使您可以更加便捷、简单和高效的使用分区表。

分区表是将一个大的逻辑表，按照分区规则分割成多个小的物理表， 大的逻辑表为分区表，小的物理表为分区，每一个分区在存储引擎上独立组织管理数据和索引。分区规则主要包括RANGE、LIST、HASH三种，您需要指定分区键， 根据分区键字段的值按照这三种规则把数据划分到不同的分区。PolarDB还支持创建混合分区，可以将每个分区放在不同的存储引擎上。Orders表做二级分区的示意图如下：

优势

• 丰富的分区类型及组合。

  • 支持RANGE、LIST、HASH和LIST DEFAULT HASH分区及其分区的任意组合，使得分区表具有更加灵活的数据分治能力。

  • 支持INTEVAL RANGE分区，让分区运维自动化更加简单高效。

  • 支持INNODB和OSS混合分区，助力您的业务降本增效。

  • 支持在不同分区下创建不同的索引（Partial index）及全局二级索引（Global Secondary Index），更好的满足基于分区的HTAP交易和查询负载对表索引的不同要求。

  • 支持在Serverless集群下进行分区运维，提供更好的兼容性。

• 提升性能。

  • 分区剪枝（Partition Pruning）：在查询数据时，优化器会根据查询条件和分区元数据自动过滤不符合条件的分区，减少数据扫描，PolarDB还支持分区动态剪枝（Partition Dynamic Pruning）功能，帮助您提升查询效率。

  • 分区连接（PartitionWise Joins）：在连接分区时，根据分区条件将分区表打散成分区与分区之间的连接，过滤不符合条件的分区之间的连接，把连接的数据控制在最小范围内，提高查询效率。

  • 统计信息管理：相比普通表，以分区粒度进行的信息统计和管理，更加精确和灵活，从而支持选择更优的查询计划。

  • 支持并行查询加速，为基于分区的大表方案提供成倍性能提升。

• 加强数据管理能力。

  • 索引创建、删除和重建：以分区为单位进行索引的创建、删除和重建。

  • 备份和恢复： 以分区粒度进行数据备份和恢复。

• 降低成本。

  分区可以根据数据重要性、数据存储性能、数据存储可靠性和数据存储形式等多种维度进行存储。

功能优化和性能增强

相对于原生MySQL，PolarDB如下功能得到了优化，性能得到了增强：

类型		优化项	相关文档

类型		优化项	相关文档
扩展分区类型	支持全类型二级分区	支持HASH/KEY分区下面做二级分区	选择二级分区
		二级分区支持RANG/LIST类型
		支持二级分区的Partition Pruning
		支持二级分区粒度上的部分DDL操作
	List Default [Hash]分区类型	支持List Default [Hash]分区	LIST DEFAULT HASH
		支持List Default [Hash]分区下面做二级分区
		二级分区支持List Default分区
		支持通过reorganize从default [Hash]分区分离出List分区
		支持通过reorganize List分区合并到default [Hash]分区
		支持通过reorganize改变default hash分区的个数
		支持List default Hash分区的Partition Pruning
		支持List Default Hash分区、ONLINE ADD List分区
	Interval Range分区类型	支持Interval Range分区	INTERVAL RANGE简介
		Interval Range支持Hash二级分区	INTERVAL RANGE转换分区表
	主键/唯一键与分区键解绑 （UNIQUE CHECK IGNORE）	创建分区表支持UNIQUE、CHECK和IGNORE选项	UNIQUE CHECK IGNORE
		单表转分区表支持UNIQUE、CHECK和IGNORE选项
支持分区索引	部分索引（Partial Index）	Partial Index支持一级分区	部分索引
		Partial Index支持二级分区
		支持一级/二级分区粒度的索引DDL操作（ADD/DROP/REBUILD Index）
		支持Partial Index的Index Pruning
		统计信息支持Partial Index
		Hybrid Partition表支持 Partial Index
		List Default Hash分区支持Partial Index
	全局二级索引（Global Secondary Index）	支持CREATE/DROP全局二级索引	全局二级索引（GSI）
		支持DML和大部分表级的DDL
		支持全局二级索引的Online DDL
		支持unique的全局二级索引
Query优化	查询优化器	支持Partial Partition Wise join	无
		支持Partition动态剪枝
		支持Partition Index Pruning
		支持分区粒度的统计信息（Hyperloglog）
		分区表支持分区间的并行扫描
		支持全局索引优化器选择和并行扫描
		支持分区与MPP相结合
	分区粒度MDL锁	ADD/DROP PARTITION支持分区粒度的MDL锁	在线分区维护
		EXCHANGE PARTITION支持分区粒度的MDL锁
		REBULD/REORGANIZE PARTITION支持分区粒度的MDL锁
		二级分区支持子分区粒度的MDL锁 说明 不支持对子分区进行单独备份还原。
DDL功能	普通表与分区表的ONLINE DDL	普通表转分区表ONLINE DDL用于历史数据转Range分区表	普通表快速转换为RANGE分区表
	Hybrid Partition	支持不同类型的存储引擎	创建混合分区

RANGE分区

RANGE分区是对历史数据进行分区的一种方便的方法。RANGE分区用边界定义了表或索引中分区的范围和分区间的顺序。

RANGE分区通常用于在DATE类型的列上按时间间隔组织数据。因此，大多数访问RANGE分区的SQL语句都关注时间范围。这方面的一个例子是类似于“从特定时间段选择数据”的SQL语句。在这样的场景中，如果每个分区代表一个月的数据，那么查询“查找21-12的数据”必须只访问2021年的12月份的分区。这将扫描的数据量减少到可用数据总量的一小部分，这种优化方法称为分区修剪（Partition Purning）。

对于定期加载新数据和清除旧数据的场景，RANGE分区也是理想的分区方式。例如，通常会保留一个滚动的数据窗口，将过去36个月的数据保持在线。RANGE分区简化了这个过程。要添加新月份的数据，需要将其加载到一个单独的表中，对其进行清理、建立索引，然后使用EXCHANGE PARTITION语句将其添加到RANGE分区表中，同时原始表保持在线状态。添加新分区后，可以使用DROP PARTITION语句删除最后一个月。

以下情况下可以考虑使用RANGE分区：

• 经常在某些列上的按照范围扫描非常大的表（例如订单表ORDER或购买明细表LINEITEM）。在这些列上对表进行分区可以实现分区剪枝。

• 希望维护数据的滚动窗口。

• 不能在指定的时间内完成大型表的管理操作，例如备份和恢复，但是可以根据分区范围列将它们划分为更小的逻辑块。

示例：创建为期9年+的表orders，并根据列o_orderdate按范围对其进行分区，将数据分成8个年度，每个年度对应一个分区。通过短时间间隔分析销售数据可以利用分区修剪。销售表也支持滚动窗口方法。

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(o_orderdate))
(PARTITION item1 VALUES LESS THAN (TO_DAYS('1992-01-01')),
 PARTITION item2 VALUES LESS THAN (TO_DAYS('1993-01-01')),
 PARTITION item3 VALUES LESS THAN (TO_DAYS('1994-01-01')),
 PARTITION item4 VALUES LESS THAN (TO_DAYS('1995-01-01')),
 PARTITION item5 VALUES LESS THAN (TO_DAYS('1996-01-01')),
 PARTITION item6 VALUES LESS THAN (TO_DAYS('1997-01-01')),
 PARTITION item7 VALUES LESS THAN (TO_DAYS('1998-01-01')),
 PARTITION item8 VALUES LESS THAN (TO_DAYS('1999-01-01')),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

 EXPLAIn select * from orders where o_orderDATE = '1992-03-01';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | item2      | ref  | i_o_orderdate | i_o_orderdate | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

由于RANGE本身的限制，使用TO_DAYS分区后，再执行SHOW CREATE TABLE后可能看不到原来的DDL。示例如下：

show create table orders;
| orders | CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`o_orderDATE`))
(PARTITION item1 VALUES LESS THAN (727563),
 PARTITION item2 VALUES LESS THAN (727929),
 PARTITION item3 VALUES LESS THAN (728294),
 PARTITION item4 VALUES LESS THAN (728659),
 PARTITION item5 VALUES LESS THAN (729024),
 PARTITION item6 VALUES LESS THAN (729390),
 PARTITION item7 VALUES LESS THAN (729755),
 PARTITION item8 VALUES LESS THAN (730120),
 PARTITION item9 VALUES LESS THAN MAXVALUE) */ |

RANGE COLUMNS分区是RANGE分区的扩展，是基于多个列值使用范围定义分区，可以使用非整数类型的列作为分区列。

RANGE COLUMNS分区与RANGE分区的区别如下：

• RANGE COLUMNS不接受表达式，只接受列的名称；

• RANGE COLUMNS接受一个或多个列的列表。RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。RANGE COLUMNS分区中的行位置也基于元组之间的比较；

• RANGE COLUMNS分区列不限于整数列；字符串类型、DATE和DATETIME列也可以用作分区列。

创建RANGE COLUMNS分区的示例如下：

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

HASH分区

对于分布规则不明显的数据，并没有明显的范围查找等特征，可以使用HASH分区，将数据分区列的值按照HASH算法打散到不同的分区上，将数据随机分布到各个分区。

使用HASH分区的目的如下：

• 使分区间数据分布均匀，分区间可以并行访问；

• 根据分区键使用分区修剪，基于分区键的等值查询开销减小；

• 随机分布数据，以避免I/O瓶颈。

分区键的选择一般要满足以下要求：

• 选择唯一或几乎唯一的列或列的组合；

• 为每个2的幂次分区创建多个分区和子分区。例如：2、4、8、16、32、64、128等。

示例：使用列c_custkey作为分区键为表customer创建4个HASH分区。

CREATE TABLE `customer` (
  `c_custkey` int(11) NOT NULL,
  `c_name` varchar(25) DEFAULT NULL,
  `c_address` varchar(40) DEFAULT NULL,
  `c_nationkey` int(11) DEFAULT NULL,
  `c_phone` char(15) DEFAULT NULL,
  `c_acctbal` decimal(10,2) DEFAULT NULL,
  `c_mktsegment` char(10) DEFAULT NULL,
  `c_comment` varchar(117) DEFAULT NULL,
  PRIMARY KEY (`c_custkey`),
  KEY `i_c_nationkey` (`c_nationkey`)
) ENGINE=InnoDB
PARTITION BY HASH(c_custkey)
( PARTITION p1,
  PARTITION p2,
  PARTITION p3,
  PARTITION p4
);

指定HASH分区的数量将自动生成各个分区的内部名称。示例如下：

CREATE TABLE `customer` (
  `c_custkey` int(11) NOT NULL,
  `c_name` varchar(25) DEFAULT NULL,
  `c_address` varchar(40) DEFAULT NULL,
  `c_nationkey` int(11) DEFAULT NULL,
  `c_phone` char(15) DEFAULT NULL,
  `c_acctbal` decimal(10,2) DEFAULT NULL,
  `c_mktsegment` char(10) DEFAULT NULL,
  `c_comment` varchar(117) DEFAULT NULL,
  PRIMARY KEY (`c_custkey`),
  KEY `i_c_nationkey` (`c_nationkey`)
) ENGINE=InnoDB
PARTITION BY HASH (`c_custkey`)
PARTITIONS 64;

LIST分区

LIST分区根据数据的枚举值进行分区。

以下示例为北京、天津、内蒙、河北的所有客户存储在一个分区中，其他省市的客户存储在不同的分区中。按区域分析账户的管理人员可以利用分区剪枝。

区域	编号
华北（p_cn_north）	1:BJ,2:TJ,3:HB,4:NM
华南（p_cn_south）	5:GD, 6:GX, 7:HN
华东（p_cn_east）	8:SH, 9:ZJ, 10:JS

创建具有LIST分区的表：

CREATE TABLE `accounts` (
  `id` int(11) DEFAULT NULL,
  `account_number` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  `region` varchar(5) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL
) ENGINE=InnoDB
PARTITION BY LIST (`region_id`)
(PARTITION p_cn_north VALUES IN (1,2,3,4),
 PARTITION p_cn_south VALUES IN (5,6,7),
 PARTITION p_cn_east VALUES IN (8,9,10)
 );

LIST COLUMNS分区是LIST分区的扩展，它允许使用多个列作为分区键，并允许使用非整数类型的数据类型列作为分区列，您可以使用字符串类型、DATE和DATETIME列。与使用RANGE COLUMNS进行分区一样，不需要在COLUMNS()子句中使用表达式将列值转换为整数。事实上，在COLUMNS()中不允许使用列名以外的表达式。

创建具有LIST COLUMNS分区的表：

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts
( id             INT,
account_number INT,
customer_id    INT,
branch_id      INT,
region_id      INT,
region         VARCHAR(5),
status         VARCHAR(1)
)
PARTITION BY LIST COLUMNS(region)
(PARTITION p_cn_north VALUES IN ('BJ','TJ','HB','NMG'),
 PARTITION p_cn_south VALUES IN ('GD','GX','HN'),
 PARTITION p_cn_east VALUES IN ('SH','ZJ','JS')
 );

LIST DEFAULT HASH分区

如果您想用LIST规则进行分区，但是分区键字段无法全部枚举，或者枚举值非常多，而对应的数据量很少。数据分布符合二八原则，20%的分区键值包含了80%的数据量，剩余80%的分区键值包含了20%的数据量。在这种场景下，您就可以选择LIST DEFAULT HASH分区类型，80%的数据按照LIST规则进行分区，不符合LIST规则的数据放到默认的DEFAULT分区里，按照HASH规则进行分区。

例如，对于多租户的业务系统，每个租户产生的用户数据量不均衡，您可以把大数据量的租户按照LIST规则分区，然后中小数据量的租户按照HASH规则分成多个分区，如下：

租户ID	数据量	分区
大客户1	3000万	p1
大客户2	2600万	p2
大客户3	2400万	p3
大客户4	2000万	p4
中小客户群	3000万	p_others

CREATE TABLE cust_orders
(
  customer_id   VARCHAR(36),
  year      VARCHAR(60),
  order_id     INT,
  order_content text
) PARTITION BY LIST COLUMNS(customer_id)
(
  PARTITION p1 VALUES IN ('大客户1'),
  PARTITION p2 VALUES IN ('大客户2'),
  PARTITION p3 VALUES IN ('大客户3'),
  PARTITION p4 VALUES IN ('大客户4'),
  PARTITION p_others DEFAULT PARTITIONS 3
);

间隔（Interval）分区

Interval Range分区是Range分区的扩展，在数据到达时自动创建间隔分区，不需要再手动创建新分区，方便了Range分区维护操作。

向RANGE分区表插入数据时，如果插入的数据超出当前已存在分区的范围，将无法插入并且会返回错误；而对于INTERVAL RANGE分区表，当新插入的数据超过现有分区的范围时，允许数据库自动创建新分区，根据INTERVAL子句指定的范围来新增分区。

如果分区范围设置为1个月，新插入的数据为当前转换点（当前存在的分区的最大边界值）两个月后的数据，将会创建该数据所在月份的分区，以及中间月份的分区。例如，您可以创建一个INTERVAL RANGE分区表，该表分区范围为1个月且当前的转换点为2021年9月15日。如果您尝试为2021年12月10日插入数据，那么将创建2021年9月15日至12月15日所需的3个分区，并将数据插入该分区。

下列情况下建议您使用间隔分区：

• 数据按时间维度维护。

• 维护滚动的数据窗口。

• 不希望手动运维增加新的分区。

以下示例将orde_time作为分区键，按间隔划分sales表。创建Interval Range分区表需要一个初始的转换点，然后才能在转换点之外自动创建新的分区。

在数据库中创建一个新的Interval Range分区表，并向表中插入数据，示例如下：

CREATE TABLE sales
(
  id BIGINT,
  uid BIGINT,
  order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
  PARTITION p0 VALUES LESS THAN('2021-9-1')
);

在Interval Range分区表中插入数据，示例如下：

INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');

插入数据后，通过SHOW CREATE TABLE查询sales表定义。新的表定义如下：

CREATE TABLE `sales` (
  `id` bigint(20) DEFAULT NULL,
  `uid` bigint(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
 PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */

Interval Range分区自动新增加了_p20211001000000、_p20211101000000和_p20211201000000三个分区。

说明

_p作为前缀的分区名将会保留为系统命名规则，手动管理分区（创建新分区或者重命名分区的操作）时，将不允许使用此类型的分区名。例如，ADD PARTITION和REORGANIZE PARTITION操作将不允许使用‘_p’作为前缀的分区名；但是DROP PARTITION操作可以使用‘_p’作为前缀的分区名。

在这样的业务场景下，此前通常需要DBA在插入值触及转换点之前手动增加分区，但操作不当就有可能导致新的数据插入失败。通过创建Interval Range分区表，可以由系统自动增加分区，避免数据不能及时插入的问题。

由于分区数量最多只能达到8192，自动增加分区的数量也会受限制，可以配合分区表的生命周期管理解决方案使用，定期增加分区，同时定期将冷数据所在的分区自动迁移到OSS上。

虚拟列分区

虚拟列分区可以对表达式进行分区，该表达式可能使用来自其他列的数据，并使用这些列进行计算。

虚拟列分区支持所有分区类型。如果查询条件不是某一个列上的原始数据，而是经过计算过的，又不想添加一个单独的列来存储计算过的正确值，可以考虑添加一个虚拟列。

在以下示例的sales表中，客户的确认号包含两个字符的省市名称，作为取车的地点，租车分析通常评估地区模式，因此可以按照省市划分：

CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `confirmation_number` varchar(12) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `order_type` varchar(10) DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `province` varchar(12) GENERATED ALWAYS AS (substr(`confirmation_number`,9,2)) VIRTUAL
) ENGINE=InnoDB
PARTITION BY LIST COLUMNS(province)
SUBPARTITION BY HASH (`customer_id`)
SUBPARTITIONS 16
(PARTITION p_cn_north VALUES IN ('BJ','TJ','HB','NMG'),
 PARTITION p_cn_south VALUES IN ('GD','GX','HN'),
 PARTITION p_cn_east VALUES IN ('SH','ZJ','JS'));

在上述示例中，列省市定义为从确认号派生的虚拟列。虚拟列不需要任何存储，虚拟列支持行移动。如果虚拟列在另一个分区中计算出不同的值，则数据库将一行迁移到另一个分区。

二级分区

组合分区提供了二维分区的优点。从性能的角度来看，您可以根据SQL语句在一个或两个维度上利用分区修剪。

二级分区允许将表、索引和索引组织的表细分为更小的块，从而能够以更细的粒度级别管理和访问这些数据库对象。例如可以为分区实现一个滚动窗口来支持历史数据分区，同时在另一个维度上进行二级分区。

数据库将二级分区表中的每个子分区存储为一个单独的物理表。因此，子分区的属性是独立的，可以与表的属性或子分区所属的一级分区不同，使用中更加灵活。

Range-Hash分区

Range-Hash分区是一级分区为Range分区，二级分区为Hash分区的组合分区类型。

Range-Hash分区对于存储历史数据的大表很常见，并且经常与其他大表连接。对于这些类型的表（典型的数据仓库系统)，组合Range-Hash分区提供了在Range级别进行分区修剪的优势，并有机会在Hash级别执行并行的全分区或部分分区连接。对于特定的SQL语句，特定的情况可以从两个维度上的分区修剪中受益。

组合Range-Hash分区还可以用于使用Hash分区的表，但需要使用滚动窗口的方法。随着时间的推移，数据可以从一个存储层移动到另一个存储层进行压缩，存储在只读表空间中并最终清除。信息生命周期管理场景通常使用Range分区来实现分层存储的方法。

以下示例为TPCH的orders表采用Range-Hash分区：

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderdate` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderdate`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderdate`)
) ENGINE=InnoDB
PARTITION BY RANGE  COLUMNS(o_orderdate)
SUBPARTITION BY HASH (`o_custkey`)
SUBPARTITIONS 64
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

Range-Range分区

Range-Range分区是指一级分区是Range分区，二级分区也是Range分区的组合分区类型。

Range-Range分区适用于在多个时间维度上存储与时间相关的数据应用程序，这些应用程序通常不使用某个特定的时间维度来访问数据，而是使用另一个时间维度，有时两者同时使用。

Range-Range分区的其他业务用例包括ILM场景和存储历史数据，并期望按另一个维度上的范围对其数据进行分类的应用程序。

以下示例为Range-Range分区表orders_history。银行可以使用对单个子分区的访问来联系用户，以获得低积分提醒或与某一类客户相关的特定促销：

CREATE TABLE `orders_history` (
  `id` int(11) NOT NULL,
  `customer_name` varchar(50) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `credit` int(11) NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (to_days(`order_date`))
SUBPARTITION BY RANGE (`credit`)
(PARTITION p0 VALUES LESS THAN (TO_DAYS('2021-01-01'))
 (SUBPARTITION low VALUES LESS THAN (100),
  SUBPARTITION normal VALUES LESS THAN (1000),
  SUBPARTITION high VALUES LESS THAN (10000),
  SUBPARTITION max VALUES LESS THAN MAXVALUE));

Range-List分区

Range-List分区是指一级分区是Range分区，二级分区是List分区的组合分区类型。

Range-List分区通常用于存储历史数据的大表，并且在多个维度上访问。数据的历史视图通常是一个访问路径，但是某些业务用例向访问路径添加了另一种分类。例如，区域客户经理非常感兴趣的是，在特定的时间段内有多少新客户在他们的区域注册。ILM及其分层存储方法是创建Range-List分区表的常见原因，以便可以移动和压缩旧数据，但列表维度上的分区修剪仍然可用。

以下示例创建一个Range-List分区order_records表。电信公司可以使用这个表来分析特定类型的电话：

CREATE TABLE `order_records` (
  `id` int(11) DEFAULT NULL,
  `order id` int(11) DEFAULT NULL,
  `description` varchar(200) DEFAULT NULL,
  `record_date` date DEFAULT NULL,
  `status` int(11) DEFAULT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (to_days(`record_date`))
SUBPARTITION BY LIST (`status`)
(PARTITION p0 VALUES LESS THAN (TO_DAYS('2021-01-01'))
 (SUBPARTITION `normal` VALUES IN (1),
  SUBPARTITION close VALUES IN (2),
  SUBPARTITION inflight VALUES IN (3),
  SUBPARTITION abnormal VALUES IN (4)));

确定分区个数

本文介绍如何确定HASH分区、List Default Hash分区以及RANGE分区的分区个数。

HASH分区

您可以在创建分区表时多尝试几个分区值，尽量找一个可以使得数据均衡分布的一个值作为分区值。以下命令可以辅助您来评估分区数量。

• 查看按照ID值分为10个分区后每个分区的数据量。

  select id%10 as part_id , count(1) from table_name group by id%10; 

• 查看每个ID值对应的数据量。

  select id, count(1) as cnt from table_name group by shop_id order by cnt desc;

说明

• 对于业务上有连接需求的表，创建分区表时可以采用相同的分区结构，以方便做连接。

• 规划表结构时，需考虑业务增长场景下对分区表的要求。

• 您可以通过以下命令来查看分区表中每个分区下大约有多少数据量：

  select * from mysql.innodb_table_stats where table_name like 'table_name#%' order by n_rows desc;

LIST DEFAULT HASH分区

以SaaS的多租户业务表为例，您可以参考以下两种方案来确定分区个数：

• 一个租户使用一个LIST分区，或者几个租户共用一个LIST分区。

  您可以评估下数据量，建议一般单表超过10亿行或1TB分区，单分区最大不超过64TB，或者考虑业务增长需求提前规划分区。

  大租户可以单独拥有一个LIST分区，或者多个租户共用一个LIST分区；对于中小租户不列举出来，默认存放在DEFAULT HASH分区。假设大租户1数据量为10亿，租户1单独拥有一个LIST分区，租户2至租户11数据量为1亿，8000万、……、5000万不等， 租户2至租户11，共10个租户共用一个LIST分区。其他1万租户共90亿数据量，不再枚举， 默认存放到DEFAULT HASH分区，DEFAULT HASH分区数为9、11、13等。

• 多尝试几个分区值。

  如果取整数不能满足数据分布均衡要求，您可以尝试取质数。如7和13等。

说明

• 对于业务上有连接需求的表，创建分区表时可以采用相同的分区结构，以方便做连接。

• 规划表结构时，需考虑业务增长场景下对分区表的要求。

• 您可以通过以下命令来查看分区表中每个分区下大约有多少数据量：

  select * from mysql.innodb_table_stats where table_name like 'table_name#%' order by n_rows desc;

RANGE分区

RANGE分区一般按照天、月、年三个维度进行分区。对每个分区的数据量要求不严格，主要由业务的实际需求而定。

分区常见问题 

PolarDB MySQL版一张表最多支持多少个分区？

最多支持8192个分区。如果定义了二级分区，则是所有二级分区的总和最多支持8192个。

数据量有多大适合使用分区表？

• 数据量下限：使用分区表对数据量的下限没有要求，空表也可以建分区表，但数据量太少没必要分区。

• 数据量上限：当数据量超过64 TB时必须进行分区，因为PolarDB MySQL版单表的最大数据容量为64 TB。

• 其他：与传统的MySQL数据库不同，PolarDB MySQL版对大表的支持做了很多优化，线上集群有超过40 TB大小的单表（单表指非分区表），访问性能没有明显的下降。目前，对于64 TB以下的数据量也没有绝对要求必须要分区，您可以综合考虑数据的增长和如何管理数据库比较方便来选择是否创建分区表。

  • 数据增长

    数据增长需要考虑单表转分区表（单表快速转RANGE分区表场景除外）的时间。目前，一般是通过DTS在迁移前全量读取和重写数据（1 TB数据大约需要5.8小时）的方式在线转分区表，然后再追齐增量数据。或者通过DDL操作转分区表，但DDL执行期间会阻塞该表线上的写操作。所以，在转分区表之前，需要提前规划分区。例如，预计未来的数据量较大需要分区，建议不要等到数据量增长到大于10 TB时才转分区表，您可以提前规划分区（5 TB的数据量在线转分区表大概需要1天多时间）。

  • 数据管理要求

    对于主要用于数据管理需求场景的分区表，以下场景可以不考虑数据量。

    • 按照月份删除或老化数据，增加新月份的数据，线上始终保持12个月份的数据。如果使用单表，需要从一张大表中按照时间条件通过一个DELETE大事务来清理掉一个月的数据，然后通过OPTIMIZER TABLE来释放删除数据的空间。而按照月份创建RANGE分区表，可以秒级DROP掉一个分区，清理数据非常方便。同理，需要按天、周、季度以及年份管理数据的业务场景使用分区表时也可以不考虑数据量。

    • SaaS客户按照租户进行HASH分区或LIST DEFAULT HASH分区等场景下使用分区表也易于管理数据。

是否需要进行分区主要看数据所占的空间大小，但是业务上更多按照超过多少数据量（行数）进行分区，数据量跟数据单行的长度有关，具体情况具体计算。一般10亿行（单行1K字节）估算成1 TB，建议分区数据量可以参考10亿行（PolarDB MySQL版线上集群有百亿级数据量的单表，没有性能问题）。

分区表创建多少个分区比较合适？

在满足分区不超过8192个的前提下，根据业务场景和数据量决定分区数。

如何使用分区表？

通常分区和业务有很大关系。如果业务数据和时间相关，建议使用时间范围分区，如果业务数据与地域和租户相关，可以使用List分区或者Hash分区，或者LIST DEFAULT HASH分区。如果一级分区数据过多，可以使用二级分区。具体请参考分区选择策略。

使用PolarDB MySQL版数据库是否需要分库分表？

不需要。考虑使用分区表代替分库分表。PolarDB MySQL版是基于共享存储和一写多读的计算存储分离架构的集中式数据库，单分区或单表数据量最大64 TB，不必过早考虑分库分表。

使用PolarDB MySQL版数据库，单张表数据量太大 ，想使用分表，如何使用？

建议使用分区表。分区表的更多介绍请参见分区表概述。

使用PolarDB MySQL版数据库进行分区后，是将分区碎片存储于不同的节点上，还是同一个节点上？

分区表把数据切分成小的分片独立管理，数据会存储在同一节点上。分区表的更多介绍请参见分区表概述。

PolarDB MySQL版中如果单表数据记录条数达到亿级，是否需要做分库分表？还是选择分区表？

建议使用分区表。分区表的更多介绍请参见分区表概述。

PolarDB MySQL版数据库支持对表进行分区的操作吗？在PolarDB MySQL版中对表进行分区是否有意义？

是的，分区表把数据切分成小的分片独立管理，从而使得拥有大数据量的表仍然拥有高性能和高可用。分区表的更多介绍请参见分区表概述。

业务上估算单张表的数据量为2 TB，选择使用PolarDB MySQL版还是PolarDB-X？

PolarDB MySQL版单表最大支持到64 TB， 2 TB的数据量相对较小，所以推荐使用PolarDB MySQL版。因为数据量超过1 TB，建议使用分区表。

PolarDB MySQL版分区表支持本地索引吗？是否支持给指定的一级分区或二级分区添加二级索引？

支持。具体请参见部分索引。

分区表是否支持FULLTEXT全文索引？

不支持

分区表是否支持空间类型，如POINT或者GEOMETRY？

不支持

分区表是否支持用户临时表？

不支持

分区表是否支持外键？

不支持

二级分区是否支持Range和List？

支持。具体请参考分区表类型和使用说明。

分区表是否支持列存索引？

支持

分区表是否支持X-Engine？

支持

创建和删除分区表时，是否会锁表？

PolarDB MySQL版8.0.2版本支持分区粒度锁，只锁住当前操作的分区，避免了大事务阻塞DDL，从而不影响其他分区的DML操作。具体请参考在线分区维护。

如何删除不带名字的分区？

• RANGE-LIST或LIST分区建议先通过SHOW CREATE TABLE语句查看分区结构，再使用ALTER TABLE ... DROP PARTITION命令移除。

• HASH建议先通过EXPLAIN SELECT * FROM ***语句查看分区名称。HASH分区不支持DROP PARTITION。需重新分区调整HASH桶个数位置ALTER TABLE XXX PARTITION BY HASH(XXX) PARTITIONS NUM;调整HASH桶NUM个数。

  说明

  该操作需等待时间较长，建议您在业务低峰期运行。

使用PolarDB MySQL版分区，会不会导致性能下降？

与单表相比，扫描相同的数据量，分区表的扫描有分区间切换的代价，会存在性能损耗。相同数据量的情况下，单表只有一个B+树，分区表是每个分区一个B+树，树的层级相对较低，insert性能会更好；分区表能使用where条件进行分区剪枝的查询场景可以减少数据的扫描和计算，性能也会更优；相对于分库分表，使用分区表在做JOIN、DDL时，性能上也有优势。

PolarDB MySQL版的分区表功能会单独收费吗？

分区表能力是内核的内置能力，不收费。

使用分区表时，是否需要调整参数的设置？

建议打开分区粒度的MDL锁。具体请参见在线分区维护。

在源端的数据库中某张表是不分区的，通过DTS迁移到目标数据库中，需要对该表进行分区，是否支持？

支持。在数据同步任务中手动创建好分区表的结构，然后配置映射关系进行数据同步即可。

大版本升级过程中，是否可以把普通单表转换为分区表？

可以。具体操作步骤如下：

1. 需要先在源库中添加一个无主键的辅助表，如create table t1 (a int)，这样可以确保大版本升级过程中的预检查失败任务中断；

2. 开始进行大版本升级任务，直到预检查失败；

3. 在目标库创建分区表（该步骤需要前往配额中心，在配额名称PolarDB MySQL版大版本升级的操作列，单击申请解决）；

4. 删除辅助表，如t1。单击继续升级（不跳过预检查），DTS预检查会再报目标库有同名表的错误；

5. 在DTS控制台屏蔽掉该报错，重新开始预检查，预检查成功后，任务正式开始同步；

6. 只要保证分区表和普通单表的数据存储格式完全一致，后续的同步任务都会顺利完成。

设置分区表后，如何查看所有分区表信息？

要查询分区信息，请在information_schema.PARTITIONS表中查找对应的分区表信息。

分区表只能对数据表的整型列进行分区吗？

可以使用KEY、RANGE COLUMN和LIST COLUMN语法对非整型数据的列进行分区。具体请参考KEY、RANGE和LIST。您也可以通过分区函数把数据列转化成整型列，来使用HASH/RANGE/LIST分区表。

分区表有什么使用上的限制？

• 最大分区数不能超过8192个。

• 单分区数据量最大为64 TB。

• 不支持外键。

• 不支持全文索引（FULL TEXT）。

如何创建分区表？

在创建表时通过PARTITON BY语法来创建。不同类型分区表的创建方法，请参见分区表类型和使用说明。

如何指定分区键？

通过part_expr来指定分区键。具体请参见分区表类型和使用说明。

PolarDB MySQL版的分区支持是否和PolarDB PostgreSQL版一样?

PolarDB PostgreSQL版分区表的分区是子表，每个分区是独立的表，PolarDB MySQL版的分区是InnoDB表，从Server层看每个分区不是独立的表。

对PolarDB MySQL版的数据表进行分区，性能提升是否明显？

对于按照分区键过滤条件进行分区剪枝的查询性能会有明显提升，另外PolarDB MySQL版对分区表做了性能优化，具体请参见分区表概述。

对于大批量的数据，例如一张表一天有1000万行新数据，一个月就3亿行，PolarDB如何应对？

请参见文档INTERVAL RANGE和自动化管理分区。可以打开在线分区维护功能，在自动增加和删除分区时不阻塞其他分区上的DML操作。

分区表支持事务吗？

支持。

使用分区表是否需要分库分表分布式事务？

不需要。

分区的写入性能提升吗？不同分区的数据写入相互阻塞吗？

在大数据量的情况下，是能够提升写入性能的。不同分区的写入是完全不阻塞的。

增加分区会导致锁超时吗？

使用在线分区维护功能可以避免这个情况的发生。

为什么DROP分区会导致整个表的业务阻塞？

在原生MySQL中DROP分区会获取整表的MDL锁，导致所有写入访问被阻塞。PolarDB MySQL版支持分区粒度的MDL锁。因此只会阻塞正在DROP的分区的DML，不阻塞其他分区的DML， 最大限度减少对业务的影响。

分区对查询和读写性能有影响吗？

建议在SQL语句中指定分区键，可以减少对性能的影响。

OPTIMIZE TABLE在分区表上是如何进行的？

OPTIMIZE TABLE会对整个分区表上MDL锁，阻塞所有分区上的DML操作。建议您使用REBUILD PARTITION命令并结合在线分区维护功能，这样只会阻塞当前重建分区的DML操作，不阻塞其他分区的DML操作， 最大限度减少对业务的影响。

分区表如果需要做数据删除，如何操作比较安全？

您可以创建一个新的相同定义的临时空表，然后将需要删除数据的分区做EXCHANGE PARTITION，再将临时表删除。

为什么分区表查询计划不准确？

分区表查询计划不准确主要的原因是统计信息不准确，在8.0.2版本中已经针对这个问题做了分区级别统计信息优化，建议您将版本升级到8.0.2。

分区时出现分区不均时应该怎么处理？

请您重新执行普通表转换为分区表，但不要添加WITHOUT VALIDATION关键字。重新执行分区操作后，系统会自动对数据进行重新校验并完成分区调整。

说明

如果您数据量较大，重新进行数据校验和分区设置所需时间较长，建议在业务低峰期进行操作。

PolarDB MySQL版的物理表如何分区？

一般情况下，每个分区是一个Innodb表， Hybrid分区可以放在其他存储引擎上。

误删了一个表的分区数据，能否恢复？

目前仅支持库表级的恢复，不支持分区级的数据恢复。

分区数太多，导致内存耗尽，如何解决这个问题？

在PolarDB MySQL版8.0.1和8.0.2版本中，不存在该问题，分区的内存都是共享的。建议您升级您的内核版本。

为什么添加分区的（ADD PARTITION）操作耗时长，如何避免？

操作耗时长是因为在该分区表上有大事务正在进行。PolarDB MySQL版8.0.2版本支持分区粒度的MDL锁只会阻塞正在添加分区的DML操作，不阻塞其他分区的DML操作， 最大限度减少对业务的影响。

PolarDB可以自动创建表分区吗？

可以。具体请参见INTERVAL RANGE简介和自动化管理分区。

使用自动化分区管理功能时，在RW节点创建的event是否会在RO节点执行？

PolarDB MySQL版是共享存储的架构，在RW节点上创建的event不会在RO节点执行，参数选择ENABLE即可。

使用自动化分区管理功能时，集群发生主备切换之后，新的RW节点是否可以继续执行event？

发生HA之后，新的RW节点是可以继续执行event的。

使用自动化管理分区时，参数event_scheduler是否需要在RW和RO节点都设置成ON？

RO节点不需要设置为ON，只需要设置RW节点为ON即可。

单表转分区表需要多长时间，以100 GB数据量的表为例？

如果使用普通表快速转换为RANGE分区表功能，可以秒级转换；如果您的业务场景不符合普通表快速转分区表的场景，您可以使用ALTER TABLE PARTITION BY来重写表中的全部数据，用时大约1~2个小时，实际用时与您的集群规格和业务负载等有关。

LINEAR HASH分区和HASH分区有什么差异？

• HASH分区

  即取模哈希，是最常用的一种分区，按照分区数取模来路由分区。

• LINEAR HASH分区

  LINEAR HASH分区是一种哈希算法，根据二次幂的特性进行计算。相对于HASH分区，优缺点如下：

  • 优点：新增分区时，每个新分区只可能由前一个确定分区分裂得到，增删分区时需要读写的数据比例很小。

  • 缺点：映射均匀性较差。

按照租户ID进行分区的场景，如何选择分区类型和分区个数？

按照租户ID进行分区的场景，可选择的分区类型有以下两种：

• HASH分区

  适用于随机产生租户ID的场景，数据分布相对比较离散。分区数量设置可参考以下3种场景：

  • 随机产生的租户ID，一般是按照数据总量来计算单个分区的数据量，单个分区的数据量在500万至5000万之间。由于数据分布可能不均衡，所以单个分区的数据量也不是绝对的。

  • 如果租户ID有一定规律，如按照100、200和500这样的分区数来拆分数据量，数据量在各个分区分布不均衡，则可以尝试使用一些质数作为分区数。

  • 如果有10亿的数据量，则建议创建100至200个分区，单个分区的数据量平均大约为500万至5000万。

  HASH分区有HASH和KEY两个子类型，其原理相同，均采用取模哈希算法。

  • 如果分区键租户ID是数值类型， 则选择HASH分区；

  • 如果分区键租户ID是字符类型，则选择KEY分区。

• LIST DEFAULT HASH分区

  适用于长尾业务场景，数据量分布不均衡，数据分布类似二八法则。如：大租户的数据量多，但大租户少；中小租户多但数据量少；或者随时会新增小租户，无法在建表时全部枚举出来。在这种场景下，您可以在一张分区表中同时使用两种分区：大租户单独使用LIST分区，或多个大租户组合使用一个LIST分区。分区个数取决于大租户的个数和数据量。其他中小租户使用HASH分区，HASH分区的分区个数确定方法同HASH分区表。

使用分区表时，如何选择索引？

针对PolarDB MySQL版分区表，您可以选择以下几种索引：

• 部分索引（Partial Index）

  您可以根据实际业务场景，在分区表的不同分区上创建不同的索引，以满足不同分区上的查询需求。

• 全局二级索引（GSI）

  针对查询条件中不包含分区键的等值查询场景，您可以使用全局二级索引。

如何将分区表中历史分区的数据迁移到该库的另一张新分区表的分区中？

假设分区表t1的历史分区为p0和p1，需要将这两个历史分区中的数据迁移到新分区表t2的p0和p1分区中。操作步骤如下：

1. 创建新的分区表t2，表结构与t1保持一致，且包含p0和p1分区。

2. 创建普通表temp，表结构与需要迁移的分区表的表结构相同。

3. 使用EXCHANGE PARTITION将t1表中历史分区p0中的数据迁移至temp表。

   ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE temp;

   如果t1中p0的数据范围与t2中p0的数据范围完全一致，您可以在数据迁移的SQL语句中使用WITHOUT VALIDATION选项来加快迁移速度。如：

   ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE temp WITHOUT VALIDATION;

4. 使用EXCHANGE PARTITION将temp表中的数据迁移至分区表t2的p0分区中。

   ALTER TABLE t2 EXCHANGE PARTITION p0 WITH TABLE temp;

   或使用如下SQL语句：

   ALTER TABLE t2 EXCHANGE PARTITION p0 WITH TABLE temp WITHOUT VALIDATION;

   迁移完成后，t1表历史分区p0中的数据已经迁移至新的分区表t2的p0分区中。

5. 使用步骤3~步骤4的方法将t1表历史分区p1中的数据迁移至t2的p1分区中。

6. 全部迁移完成后，删除表temp。

   若后续t1表中还有其他的分区需要迁移至t2中，您可以在t2表中使用ADD PARTITION添加新的空分区，再按照上述步骤进行数据迁移。

分区表或单表执行过INSTANT添加列或修改列操作后，再执行EXCHANGE PARTITION操作时报错，应该如何处理？

执行过INSTANT添加列或修改列的分区表或单表，再执行EXCHANGE PARTITION操作时，报错信息如下：

ERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and table

解决办法：重写单表或分区表，消除INSTANT信息后再进行交换。重写命令如下：

• 位于InnoDB引擎下的单表或分区表，重写命令如下：

  ALTER TABLE table_name ENGINE=InnoDB;

• 位于X-Engine引擎下的单表或分区表，重写命令如下：

  ALTER TABLE table_name ENGINE=xengine;

重写操作是online DDL，不影响DML和Query操作，但会占用资源。如果表的数据量很大，则需要在业务低峰期执行重写操作。您可以通过以下SQL语句来查询重写DDL的执行进度和预估剩余时间：

SELECT
pl.ID,
pl.INFO,
esc.THREAD_ID,
esc.EVENT_NAME,
(esc.WORK_COMPLETED / esc.WORK_ESTIMATED) * 100 as PROGRESS,
pl.TIME / 60 AS `EXECUTED TIME(min)`,
ROUND(
(
esc.WORK_ESTIMATED * pl.TIME / esc.WORK_COMPLETED - pl.TIME
) / 60,
2
) AS `ESTIMATED REMAINING TIME(min)`
FROM
performance_schema.events_stages_current esc
LEFT JOIN performance_schema.threads th ON esc.thread_id = th.thread_id
LEFT JOIN information_schema.PROCESSLIST pl ON th.PROCESSLIST_ID = pl.ID;

分区表如何指定分区名查询数据？

在PolarDB MySQL版中支持通过指定分区名称进行数据查询的语法格式如下：

语法格式：

PARTITION (partition_names)

其中，partition_names表示一个或多个分区名称的列表，具体格式为：

partition_name, partition_name, ...

示例说明：

• 查询特定分区的数据

  • 要查询表 t1中p0分区的数据，可以使用以下语句：

    SELECT * FROM t1 PARTITION (p0);

• 查询多个分区的数据

  • 如果需要同时查询多个分区的数据，可以将多个分区名称用逗号分隔：

    SELECT * FROM t1 PARTITION (p0, p1, p2);

说明

• 在执行分区查询时，必须确保指定的分区名称与实际表结构中的分区名称完全一致。

• 分区查询语法适用于支持分区表的表类型，且需要数据库版本支持相应功能。