近来要搭某个业务域的数仓，本来设计规划的挺好的，该搭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）
按时间举行范围分区是最常见的方式之一，尤其适合具有时间属性的数据表，如日志表、生意业务记载表等。
生意业务风险表按年份分区：
- CREATE TABLE risk (
- id BIGINT PRIMARY KEY AUTO_INCREMENT,
- risk_code VARCHAR(50) NOT NULL UNIQUE,
- risk_company_id BIGINT NOT NULL,
- risk_time DATETIME DEFAULT NULL,
- risk_level TINYINT DEFAULT NULL,
- risk_desc LONGTEXT DEFAULT NULL,
- create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- )
- PARTITION BY 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 -- 未来的数据
- );
2.列表分区（List Partitioning）
按某些离散的值举行分区，如按地区、产品类型、风险品级等。实用于数据具有离散特征的场景。
风险规则按风险品级举行分区：
- CREATE TABLE risk_rule (
- id BIGINT PRIMARY KEY AUTO_INCREMENT,
- rule_code VARCHAR(150) NOT NULL UNIQUE,
- rule_name VARCHAR(150) NOT NULL,
- risk_level TINYINT NOT NULL DEFAULT 0,
- create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- )
- PARTITION BY LIST (risk_level) (
- PARTITION p_high VALUES IN (1), -- 高风险
- PARTITION p_medium VALUES IN (2), -- 中风险
- PARTITION p_low VALUES IN (3), -- 低风险
- PARTITION p_reminder VALUES IN (4) -- 提醒等级
- );
3.哈希分区（Hash Partitioning）
哈希分区用于将数据均匀分布到多个分区中，以避免数据倾斜。特别实用于数据量较大且没有自然分区字段的情况。
按公司 ID 对风险表举行哈希分区：
- CREATE TABLE risk (
- id BIGINT PRIMARY KEY AUTO_INCREMENT,
- risk_code VARCHAR(50) NOT NULL UNIQUE,
- risk_company_id BIGINT NOT NULL,
- risk_time DATETIME DEFAULT NULL,
- risk_level TINYINT DEFAULT NULL,
- create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- )
- PARTITION BY HASH (risk_company_id) PARTITIONS 4;
4.复合分区（Composite Partitioning）
复合分区结合了两种或多种分区方法，通常用于具有多个维度的数据。
按时间和公司 ID 举行复合分区：
- CREATE TABLE risk (
- id BIGINT PRIMARY KEY AUTO_INCREMENT,
- risk_code VARCHAR(50) NOT NULL UNIQUE,
- risk_company_id BIGINT NOT NULL,
- risk_time DATETIME DEFAULT NULL,
- risk_level TINYINT DEFAULT NULL,
- create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- )
- PARTITION BY RANGE (YEAR(risk_time))
- SUBPARTITION BY HASH (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_company_id 举行哈希分区，将数据均匀分布到 4 个子分区中。
如许做可以有效结合时间和公司的两个维度，进一步优化查询性能。
本次分享就到此，下次不见不散，期待关注！
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
