全网最易详解-数据仓库分区详解

最近要搭某个业务域的数仓，本来设计规划的挺好的，该搭DIM,DWD,DWS的也都设计好了，结果一跑数仓，全是大大小小的BUG，最后揪出来整个过程，最大的烦人东西就是设计ETL数据入库分区的问题。

那么这时候肯定有人说：咳，小事，不整那么多分区表，整全量表就不行了吗？但事实就是如果业务实际到前后两天必须要作环比审核的时候，就不得不做分区了，这几天对每个业务表设计分区有感，对数仓分区概率又多了很多新的认知和理解。

1.为什么要做数据表分区？

这样，我们先重新理解一下数据表分区的理念，

数据分而治之：分区的核心在于“分而治之”，即将一个非常大的数据表划分为多个更小的数据块。这样做的好处是可以加快查询速度，因为查询可以仅针对相关分区而非整个表进行扫描。分区可以按时间、地理位置、ID等多个不同维度来进行划分。

提高查询效率：数据仓库中的数据量通常非常庞大，直接对整表进行扫描会导致低效且耗时的操作。通过分区，只需要扫描满足条件的分区，而不必扫描整个表，这大幅减少了扫描的数据量。例如，当对按日期进行分区的数据表进行查询时，如果只查询某一天的数据，那么系统只会访问相关日期的分区，而不需要扫描整张表。

便于数据管理：分区使得数据的管理更加灵活和高效。例如，可以对历史数据进行分区，从而只需对特定时间范围的数据进行归档或删除，避免对整个表进行大规模的删除操作。这样可以降低锁表的概率，提升数据库的可用性和数据的更新效率。

并行处理：分区可以使数据库引擎在并行处理中更加有效，因为每个分区可以作为独立的单位进行处理。多个查询进程可以在不同的分区上并行执行操作，这有助于提高整体的查询性能。

以上四点记不住也没关系，毕竟足够抽象，没做几次数仓建设想要深入理解还是难的，那么就让我们情景带入以下，作为超市管理员我们如何处理货物关系：

假设你经营了一家大型超市，而超市的任务就是要方便顾客快速找到他们需要的商品。超市里的商品种类繁多，数量庞大，如果不进行任何分类和组织，顾客要找到他们想要的东西会变得非常困难，这就像面对一个没有分区的大型数据表，要从中找到特定的数据非常耗时而且低效。

1.将商品按类别进行分区，提高查找效率： 在超市里，商品通常会按照类别进行划分，比如饮料区、日用品区、零食区、蔬果区、冷冻食品区等。这些类别就相当于数据分区的过程。通过将商品划分到不同的区域，顾客可以根据他们的需求，直接去相应的区域找商品，而不需要在整个超市里到处找。这就像在一个大数据表里，如果我们把数据按时间或类别分区，查询时只需要去相关的分区查找，而不是扫描整个表。

2.历史商品处理（管理历史数据）：超市还会定期对旧的、不再销售的商品进行清理。例如，旧款的商品会被移除或放到折扣区，而这些商品不再占据主要货架上的空间。这就像数据分区可以帮助管理历史数据的归档和清理。对于一个按时间分区的数据表，我们可以方便地把老旧的数据分区移除或归档，而不影响新数据的管理和查询。

3.分区的并行处理： 设想多个顾客同时在不同的区域购物，比如一个顾客在蔬果区挑选水果，另一个顾客在冷冻区选择速冻食品，超市的布局使得这些顾客可以并行地完成他们的购物任务，互不干扰。这类似于数据分区支持并行查询的概念。数据库系统可以对不同分区的数据进行并行处理，提高整体的响应速度和处理能力。

4.避免过于细小的分区（避免分区过多）： 如果超市的分区过于复杂，比如每种商品都有独立的区域（牛奶、酸奶、纯牛奶、低脂牛奶等都在不同的分区），顾客反而会感到迷惑，找商品也会变得困难。这就像数据分区中如果我们把数据划分得过于细小，系统需要管理太多的分区，反而导致性能下降。所以分区的设计要平衡颗粒度，既能有效地帮助查找，又不会增加太多管理成本。

以上差不多就是整个数据分区的设计理念了，让我们最后对接业务更加快速便捷，减少不必要的重复劳动时间。那么我们知道分区是有好处的，但是也不是所有表都需要建立分区，反而会跟第四条一样十分冗余。

2.哪些表适合分区？哪些表不用分区？

并不是所有的数据表都适合进行分区操作，分区的应用需要根据表的特点和使用场景来决定。

适合分区的表

数据量特别大的表：

• 典型特征：表中包含了大量的数据，通常有数百万甚至数十亿行记录。
• 如果表非常大，在进行全表扫描时非常耗时，通过分区可以显著减少需要处理的数据量，从而提升查询性能。例如：日志表、历史订单表、传感器数据记录等。

按时间维度查询的数据表：

• 典型特征：数据具有明显的时间属性，且查询时常按时间进行过滤。
• 例如日志表、交易记录表等，这些表的数据通常按时间来保存，且查询时往往需要获取特定时间段的数据。按时间分区可以显著减少扫描的数据量，提升查询速度，同时便于做数据的归档和管理。

具有显著的逻辑划分的数据表：

• 典型特征：表中的数据可以很自然地分成几个部分，如按地理区域、产品类型等进行分组。
• 例如，用户信息表可以按地区分区。这样，在进行地区相关的分析时，可以仅访问特定区域的分区，从而提高查询的性能。

历史数据需要归档的数据表：

• 典型特征：表中有较多历史数据，旧数据不再频繁访问。
• 例如，某些系统中的历史业务数据，可能需要定期归档。使用分区可以方便地对特定的旧数据进行归档、清理，而不影响当前正在使用的最新数据。

频繁对特定分组进行操作的表：

• 典型特征：对表的操作通常集中在某一子集上。
• 例如，在电商系统中对未完成的订单与已完成的订单的操作频率不同，可以对订单表按状态进行分区，以便对未完成的订单进行更快的操作。

不适合分区的表

数据量较小的表：

• 典型特征：表的数据量不大，通常只有几千行到几十万行。
• 分区会增加管理的复杂性和系统的开销，对于数据量较小的表，这些额外的开销反而可能使得性能下降，并没有显著的好处。对于小表，全表扫描的代价也不高，分区的优势难以体现。

没有明显分区条件的表：

• 典型特征：表中的数据没有一个明显的字段适合作为分区键，也没有自然的分区方式。
• 例如，一个仅用于存储配置项或参考数据的表，通常这些数据没有分区的逻辑，也不具备足够大的数据量，不适合进行分区。

查询模式不适合分区的表：

• 典型特征：查询模式没有规律可循，涉及多个字段的组合，且查询时往往无法利用分区键。
• 如果查询时很难限定到某个特定的分区，或者每次都需要扫描多个分区，那么分区带来的好处将变得有限。例如，如果表中的数据分区是按“产品类型”分区的，但实际查询时大部分是按“用户 ID”来过滤，那么这种分区方案可能无法起到预期的优化效果。

频繁更新分区键的表：

• 典型特征：分区键的值可能会被频繁修改，数据在分区之间频繁移动。
• 当分区键的值变化时，数据库需要将相应的数据从一个分区移动到另一个分区，这种操作非常昂贵，可能带来大量的性能开销。因此，对于频繁需要更新分区键的表，不建议使用分区。

分区可能导致“热点”问题的表：

• 典型特征：某个分区的数据量远大于其他分区，导致负载不均衡。
• 例如，如果分区的方式不合理（如按时间分区），而某个时间段内的数据量集中在一个分区内，这样就会导致对某个分区的操作频繁，形成热点，影响性能。

我们拿一个实际风险业务域的数据仓库来看，总共有

risk

表（风险表）、

risk_expedite

表（催办表）、

risk_handle

表（处理表）、

risk_read

表（风险查看表）、

risk_rule

表（规则表）、

risk_company_rule

表（公司规则表）、

risk_trigger_obj

表（触发对象表）。

适合建立分区的表

1.**

risk

表（风险表）**：

risk

表记录了大量的风险事件，每个风险事件都会关联到具体的时间（如风险时间、订单发布时间等）。这类表通常数据量非常大，而业务上通常只关心某一时间段内的风险记录，按时间进行分区可以有效地减少查询数据量，提升查询效率。另外，随着时间的推移，历史数据可能不再需要经常查询，按时间分区也便于进行归档和清理。

2.**

risk_expedite

表（催办表）**：催办操作通常与时间紧密相关，数据量较大且持续增加。按时间进行分区，可以方便地获取特定时间段的催办记录，并便于历史数据的归档。

3.**

risk_handle

表（处理表）**：处理记录通常也与时间密切相关，按处理时间分区可以方便地管理处理记录，提高针对某段时间内处理数据的查询效率。

4.**

risk_read

表（风险查看表）**：风险查看记录通常会随着时间积累变得庞大。按查看时间进行分区，有助于提升查询特定时间段内查看记录的效率，并便于管理历史数据。

不需要建立分区的表

1.**

risk_rule

表（规则表）**：

risk_rule

表通常存储的是风险管理的规则，规则的数量通常较少，不会频繁发生更新或者新增。由于数据量不大，全表扫描的开销不高，所以没有必要对其进行分区，分区反而会增加系统复杂性。

2.**

risk_company_rule

表（公司规则表）**：该表存储公司与规则之间的映射关系，这类表的数据通常不会很庞大，也不会频繁地查询，因此进行分区并不会带来显著的性能提升。相反，分区的管理成本可能会超过其带来的效益。

3.**

risk_trigger_obj

表（触发对象表）**：

risk_trigger_obj

表中的数据主要记录风险触发的具体对象，数据量相对较小，通常只在需要查询特定风险的对象信息时使用。由于数据量不大且查询频率较低，分区的管理成本较高，效益不明显。

判断点

可以参考四点来判断一个业务表适不适合分区：

数据量是否庞大：如风险记录表、催办表、处理表等，随着业务积累数据量可能非常庞大，这些表适合分区。

数据是否具有时间属性：如果表中的数据具有明显的时间维度（如风险发生时间、催办时间、处理时间等），按时间进行分区可以显著提高查询效率和便于历史数据管理。

查询模式是否明确：如果查询通常集中在某个维度（如时间），该维度适合用于分区键。

数据量较小或规则信息：如风险规则表、公司规则表等，这些表数据量较小，全表扫描的性能消耗低，不需要分区。

3.写SQL分区建表需要注意什么？

在编写 SQL 分区建表时，需要考虑分区类型、分区键、数据分布、查询优化、分区维护和索引等多方面的因素。合理设计分区结构可以显著提高数据的查询效率和管理的便利性，但分区也增加了一些复杂性，因此需要结合实际数据量和业务查询场景来选择最合适的方案。

SQL 中的分区类型有多种，如 范围分区（Range Partitioning）、列表分区（List Partitioning）、哈希分区（Hash Partitioning） 和 复合分区（Composite Partitioning）。选择合适的分区类型非常重要：

• 范围分区：适合按时间等连续数据分割，例如按年份、月份进行分区。
• 列表分区：适合对具有离散值的数据进行分割，例如按地区、分类进行分区。
• 哈希分区：适合均匀分布数据，防止数据倾斜，尤其当没有明确的自然分区键时。
• 复合分区：可以结合两种以上的分区方法，如先按时间范围分区，再在每个分区内按哈希分布。这种方法适合需要更灵活分区策略的场景。

每个分区表的命名也有讲究，为分区设置有意义的名字，便于管理和维护。
字段中文字段字段全称说明日dday每天周wweek每周月mmonth每月年yyear每年小时hhour每小时半小时hhhalfhour每半小时
抽取字段则为是不是全量、增量还是是否有分区限制抽取：
抽取方式字段字段全称分区增量表iincremental分区全量表ffull非分区全量表aall拉链表cchain
实际应用中，可以选择采用增量、全量存储或拉链存储的方式。

• 事务管理：当对分区表进行操作时，可能涉及多个分区的修改。在编写 SQL 时需要特别关注事务一致性，确保所有分区的数据都能在事务中得到正确处理。
• 分区键选择不当导致的数据倾斜：如果分区键选择不当，可能导致某些分区包含大量数据，而其他分区相对较少。这种数据倾斜会导致某些分区在查询时承受很高的负载，而其他分区则很少被访问，从而影响整体性能。因此，分区键需要选择能够尽量均匀分布数据的字段。
• 避免过于频繁的分区变更：频繁变更分区（如频繁的分区合并、拆分等）会影响表的稳定性和性能，应尽量减少分区的频繁变更。

1.范围分区（Range Partitioning）

按时间进行范围分区是最常见的方式之一，尤其适合具有时间属性的数据表，如日志表、交易记录表等。

交易风险表按年份分区：

CREATETABLE risk (
    id BIGINTPRIMARYKEYAUTO_INCREMENT,
    risk_code VARCHAR(50)NOTNULLUNIQUE,
    risk_company_id BIGINTNOTNULL,
    risk_time DATETIMEDEFAULTNULL,
    risk_level TINYINTDEFAULTNULL,
    risk_desc LONGTEXTDEFAULTNULL,
    create_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,
    update_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP)PARTITIONBY RANGE (YEAR(risk_time))(PARTITION p_2022 VALUES LESS THAN (2023),-- 2022年的风险数据PARTITION p_2023 VALUES LESS THAN (2024),-- 2023年的风险数据PARTITION p_2024 VALUES LESS THAN (2025),-- 2024年的风险数据PARTITION p_future VALUES LESS THAN MAXVALUE -- 未来的数据);

p_future

分区用于存储超出目前年份的数据，避免数据插入失败。

2.列表分区（List Partitioning）

按某些离散的值进行分区，如按地区、产品类型、风险等级等。适用于数据具有离散特征的场景。

风险规则按风险等级进行分区：

CREATETABLE risk_rule (
    id BIGINTPRIMARYKEYAUTO_INCREMENT,
    rule_code VARCHAR(150)NOTNULLUNIQUE,
    rule_name VARCHAR(150)NOTNULL,
    risk_level TINYINTNOTNULLDEFAULT0,
    create_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,
    update_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP)PARTITIONBY LIST (risk_level)(PARTITION p_high VALUESIN(1),-- 高风险PARTITION p_medium VALUESIN(2),-- 中风险PARTITION p_low VALUESIN(3),-- 低风险PARTITION p_reminder VALUESIN(4)-- 提醒等级);

这样做的好处是可以更快地处理针对特定风险等级的查询。

3.哈希分区（Hash Partitioning）

哈希分区用于将数据均匀分布到多个分区中，以避免数据倾斜。特别适用于数据量较大且没有自然分区字段的情况。

按公司 ID 对风险表进行哈希分区：

CREATETABLE risk (
    id BIGINTPRIMARYKEYAUTO_INCREMENT,
    risk_code VARCHAR(50)NOTNULLUNIQUE,
    risk_company_id BIGINTNOTNULL,
    risk_time DATETIMEDEFAULTNULL,
    risk_level TINYINTDEFAULTNULL,
    create_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,
    update_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP)PARTITIONBYHASH(risk_company_id) PARTITIONS 4;

可以使数据较为均匀地分布在各个分区中，减少查询和写入的瓶颈。

4.复合分区（Composite Partitioning）

复合分区结合了两种或多种分区方法，通常用于具有多个维度的数据。

按时间和公司 ID 进行复合分区：

CREATETABLE risk (
    id BIGINTPRIMARYKEYAUTO_INCREMENT,
    risk_code VARCHAR(50)NOTNULLUNIQUE,
    risk_company_id BIGINTNOTNULL,
    risk_time DATETIMEDEFAULTNULL,
    risk_level TINYINTDEFAULTNULL,
    create_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,
    update_time DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP)PARTITIONBY RANGE (YEAR(risk_time))
SUBPARTITION BYHASH(risk_company_id)
SUBPARTITIONS 4(PARTITION p_2022 VALUES LESS THAN (2023),PARTITION p_2023 VALUES LESS THAN (2024),PARTITION p_2024 VALUES LESS THAN (2025),PARTITION p_future VALUES LESS THAN MAXVALUE
);

主分区：按

risk_time

的年份进行范围分区，将数据按年份划分。

子分区：在每个主分区内，按

risk_company_id

进行哈希分区，将数据均匀分布到 4 个子分区中。

这样做可以有效结合时间和公司的两个维度，进一步优化查询性能。

本次分享就到此，下次不见不散，期待关注！