分区表常见问题

PolarDB MySQL版是否支持表分区?

支持。目前PolarDB MySQL版100%兼容MySQL，同时做了功能和性能的增强，详情请参考分区表概述。

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);