第二代营销网站,网站建设的上机报告,罗湖高端网站设计,瀑布流网站如何seo数据库版本#xff1a;KingbaseES V008R006C008B0014 简介 分区表是一种将大型数据库表拆分为更小、更可管理的部分的技术。它通过将表数据分散存储到多个物理存储单元中#xff0c;可以提高查询和数据维护的性能#xff0c;并优化对大型数据集的处理。本篇文章以kingbase为…
数据库版本KingbaseES V008R006C008B0014 简介 分区表是一种将大型数据库表拆分为更小、更可管理的部分的技术。它通过将表数据分散存储到多个物理存储单元中可以提高查询和数据维护的性能并优化对大型数据集的处理。本篇文章以kingbase为例介绍分区表的用法。 文章目录如下
1. 基本语法
1.1. 语法一
1.1.1. 一级分区
1.1.2. 二级分区
1.2. 语法二
1.2.1. 一级分区
1.2.2. 二级分区
2. 分区类型
2.1. 范围分区
2.1.1. 按金额范围分区
2.1.2. 按日期分区
2.1.3. 自动创建日期分区
2.1.4. 自动创建整数分区
2.2. 列表分区
2.2.1. 按日志级别分区
2.2.2. 按地区分区
2.3. 哈希分区
2.3.1. 按用户ID分区
2.3.2. 按订单号分区
2.3.3. 自定义hash模
3. 应用场景
3.1. 利用EXTRACT提取年份
3.2. 分区表性能对比 1. 基本语法
1.1. 语法一
1.1.1. 一级分区
创建分区表有2种语法第1种在创建普通表后面加上指定的分区信息。
CREATE TABLE 表名
(列名1 数据类型,列名2 数据类型,...
)
PARTITION BY RANGE (分区键) --要分区的列名
(PARTITION 分区名1 VALUES LESS THAN (分区值), --存储的范围PARTITION 分区名2 VALUES LESS THAN (分区值), --存储的范围...
); 举个例子创建一张列表分区将性别分区存储
CREATE TABLE p1 (id int,name varchar(64),sex varchar(4))
PARTITION BY LIST(sex) --指定sex列分区(PARTITION boy VALUES (男), --sex为男的数据存储到该分区PARTITION girl VALUES (女) --sex为女的数据存储到该分区); 创建完成后包含一张分区表和n张子分区表。 此时向分区表插入3条数据
INSERT INTOp1
VALUES(1, 小李, 男),(2, 小张, 女),(3, 小王, 男);
查询主表存在3条数据 查询分区表p1_boy存储性别为男的数据 查询分区表p1_girl存储性别为女的数据 总结 分区表利用不同的列数据来分别存储插入数据后不同的子分区根据自己的规则存储不同的数据。主分区可以查询所有数据子分区只包含规则内的数据。 1.1.2. 二级分区
《目录1.1》描述了如何创建一个分区表一级分区而真实场景会使得一级分区覆盖度远远不够所以需要使用二级分区语法如下
CREATE TABLE 表名(列名 数据类型
)
PARTITION BY RANGE (一级分区键) --指定一级分区
SUBPARTITION BY RANGE (二级分区键) --指定二级分区
(PARTITION 一级分区名 VALUES LESS THAN (分区值)(SUBPARTITION 二级分区名 VALUES LESS THAN (分区值))
);
PARTITION BY普通分区语法SUBPARTITION BY子分区语法 注意KingbasES的MySQL、Oracle模式最大支持二级分区PG模式无限层级。 举个例子年份作为一级分区、月份作为二级分区
CREATE TABLE sales_data_two_level (sale_date DATE, --销售日期amount DECIMAL(10,2) --销售金额
)
PARTITION BY RANGE (sale_date) --将销售日期分区
SUBPARTITION BY RANGE (sale_date) --作一级分区
(/*一级分区为2020-01-01以前*/PARTITION p2019 VALUES LESS THAN (2020-01-01)(/*二级分区按月份分区*/SUBPARTITION p201901 VALUES LESS THAN (2019-02-01),SUBPARTITION p201902 VALUES LESS THAN (2019-03-01),SUBPARTITION p201903 VALUES LESS THAN (2019-04-01),SUBPARTITION p201904 VALUES LESS THAN (2019-05-01),SUBPARTITION p201905 VALUES LESS THAN (2019-06-01),SUBPARTITION p201906 VALUES LESS THAN (2019-07-01),SUBPARTITION p201907 VALUES LESS THAN (2019-08-01),SUBPARTITION p201908 VALUES LESS THAN (2019-09-01),SUBPARTITION p201909 VALUES LESS THAN (2019-10-01),SUBPARTITION p201910 VALUES LESS THAN (2019-11-01),SUBPARTITION p201911 VALUES LESS THAN (2019-12-01),SUBPARTITION p201912 VALUES LESS THAN (2020-01-01)),/*一级分区2020-01-01 ~ 2020-12-31*/PARTITION p2020 VALUES LESS THAN (2021-01-01)(/*二级分区按月份分区*/SUBPARTITION p202001 VALUES LESS THAN (2020-02-01),SUBPARTITION p202002 VALUES LESS THAN (2020-03-01),SUBPARTITION p202003 VALUES LESS THAN (2020-04-01),SUBPARTITION p202004 VALUES LESS THAN (2020-05-01),SUBPARTITION p202005 VALUES LESS THAN (2020-06-01),SUBPARTITION p202006 VALUES LESS THAN (2020-07-01),SUBPARTITION p202007 VALUES LESS THAN (2020-08-01),SUBPARTITION p202008 VALUES LESS THAN (2020-09-01),SUBPARTITION p202009 VALUES LESS THAN (2020-10-01),SUBPARTITION p202010 VALUES LESS THAN (2020-11-01),SUBPARTITION p202011 VALUES LESS THAN (2020-12-01),SUBPARTITION p202012 VALUES LESS THAN (2021-01-01)),/*一级分区存储2020-12-31以后的数据*/PARTITION p_future VALUES LESS THAN (MAXVALUE)(SUBPARTITION p_future_month VALUES LESS THAN (MAXVALUE))
); 1.2. 语法二
1.2.1. 一级分区
这种语法来自于PG语法与上述类似将分区表和子分区分开创建。
创建范围分区
--创建范围分区表
CREATE TABLE t1(id int
) PARTITION BY RANGE(id);--创建子分区将1~4的数值存储到t1_p1
CREATE TABLE t1_p1 PARTITION OF t1 FOR VALUES FROM(1) TO (5);
--创建子分区将大于4的数值存储到t1_max
CREATE TABLE t1_max PARTITION OF t1 FOR VALUES FROM(5) TO (maxvalue); 创建列表分区
--创建列表分区表
CREATE TABLE t1(id int,sex varchar(4)
) PARTITION BY LIST(sex);--创建子分区将性别为男存储到t1_boy
CREATE TABLE t1_boy PARTITION OF t1 FOR VALUES IN (男);
--创建子分区将性别为女存储到t1_girl
CREATE TABLE t1_girl PARTITION OF t1 FOR VALUES IN (女); 创建哈希分区
--创建列表分区表
CREATE TABLE t1(id int
) PARTITION BY HASH(id);--创建子分区存储模为3余数为0的数据
CREATE TABLE t1_h1 PARTITION OF t1 FOR VALUES WITH(modulus 3, remainder 0); 1.2.2. 二级分区
二级分区在一级分区的基础上增加
CREATE TABLE p1 (id int,name varchar(64),sex varchar(4))
PARTITION BY LIST(sex); --指定sex列分区--创建一级子分区将id列指定为范围分区
CREATE TABLE p1_boy PARTITION OF p1 FOR VALUES IN (男) PARTITION BY RANGE(id);--创建二级子分区目标表为p1_boy(作为它的二级分区)分区数据为id列1~10
CREATE TABLE p1_boy_id10 PARTITION OF p1_boy FOR VALUES FROM (1) TO (11) ; 创建多个子分区根据上述语法继续写例如
--创建一级子分区
CREATE TABLE p1_girl PARTITION OF p1 FOR VALUES IN (女) PARTITION BY RANGE(id);--创建二级子分区
CREATE TABLE p1_girl_id10 PARTITION OF p1_girl FOR VALUES FROM (1) TO (11) ;--创建二级子分区
CREATE TABLE p1_girl_id_max PARTITION OF p1_girl FOR VALUES FROM (11) TO (MAXVALUE) ; 2. 分区类型
在目录1中理解了如何创建分区那么分区的类型在这一目录介绍包括
范围分区数值范围、日期范围等。列表分区固定列表值如[北京, 上海]。哈希分区基于用户指定分区键上的哈希算法自动将各个分区中均匀分布。
它们的语法在 PARTITION BY 后面
PARTITION BY RANGE(分区键) --范围分区
PARTITION BY LIST(分区键) --列表分区
PARTITION BY HASH(分区键) --哈希分区 2.1. 范围分区
范围分区是根据列的范围值将表数据分布到不同的分区中常见的日期范围、数值范围等。
语法
PARTITION BY RANGE (列名)(PARTITION 分区名 VALUES LESS THAN (范围)
)
2.1.1. 按金额范围分区
CREATE TABLE orders1 (order_id INT PRIMARY KEY, --订单IDorder_date DATE, --订单日期customer_id INT, --客户IDtotal_amount DECIMAL(10, 2) --总金额
)
PARTITION BY RANGE (total_amount) ( --将总金额分区PARTITION p_1 VALUES LESS THAN (1000), --存储1000PARTITION p_2 VALUES LESS THAN (10000), --存储1wPARTITION p_3 VALUES LESS THAN (100000), --存储10wPARTITION p_max VALUES LESS THAN (MAXVALUE) --存储10w
); p_1范围0~999 p_2范围1000~9999 p_3范围10000~99999 p_max范围100000~最大值 2.1.2. 按日期分区
CREATE TABLE sales_orders (order_id INT PRIMARY KEY, --订单IDorder_date DATE, --订单日期customer_id INT, --客户IDtotal_amount DECIMAL(10, 2) --总金额
)
PARTITION BY RANGE (order_date) ( --将订单日期分区PARTITION p2019 VALUES LESS THAN (2020-01-01),PARTITION p2020 VALUES LESS THAN (2021-01-01),PARTITION p2021 VALUES LESS THAN (2022-01-01),PARTITION p2022 VALUES LESS THAN (2023-01-01),PARTITION p_max VALUES LESS THAN (MAXVALUE)
); p2019范围2020以前 p2020范围2020年日期 p2021范围2021年日期 p2022范围2022年日期 p_max范围2023年~以后 2.1.3. 自动创建日期分区
CREATE TABLE sales_orders (order_id INT PRIMARY KEY, --订单IDorder_date DATE, --订单日期customer_id INT, --客户IDtotal_amount DECIMAL(10, 2) --总金额
)
PARTITION BY RANGE (order_date) INTERVAL(3 MONTH::INTERVAL) --自动创建
(PARTITION p1 VALUES LESS THAN (2020-01-01) --小于等于2020-01-01存储到p1
); p1存储2020-01-01以前的数据后面插入的数据每隔3个月自动创建分区 1 YEAR每隔1年创建一个分区1 MONTH每个1个月创建一个分区1 DAY每个1天创建一个分区 2.1.4. 自动创建整数分区
CREATE TABLE sales_orders (order_id INT PRIMARY KEY, -- 订单IDorder_date DATE, -- 订单日期customer_id INT, -- 客户IDtotal_amount DECIMAL(10, 2) -- 总金额
)
PARTITION BY RANGE (order_id)INTERVAL(1000::BIGINT) --自动创建
(PARTITION p0 VALUES LESS THAN (0) --小于等于0存储到p0
);
1000::BIGINT数字每增长1000自动创建1个分区也就是说插入 order_id 列 2000 后自动创建1个分区3000、4000...同理。 2.2. 列表分区
列表分区不同于范围而是将每个分区基于列值的列表。比如指定列表为 (aa, bb)那么只会将该列为 aa 或 bb 值存储到指定分区中。
语法
PARTITION BY LIST (列名)(PARTITION 分区名 VALUES (固定值1, 固定值2),
)
2.2.1. 按日志级别分区
CREATE TABLE log_entries (log_id SERIAL PRIMARY KEY, --日志IDlog_message TEXT, --日志信息log_date TIMESTAMP, --日志日期log_type VARCHAR(50) --日志类型
)
PARTITION BY LIST (log_type) --将日志类型分区
(PARTITION type_error VALUES (ERROR, CRITICAL),PARTITION type_warning VALUES (WARNING),PARTITION type_info VALUES (INFO, DEBUG)
);
将 ERROR、CRITICAL 存储到 type_error 中将 WARNING 存储到 type_warning 将 INFO、DEBUG 存储到 type_info 中其他类型无法插入 2.2.2. 按地区分区
CREATE TABLE sales_orders (order_id SERIAL PRIMARY KEY, --订单IDorder_date DATE, --订单日期customer_id INT, --客户IDtotal_amount DECIMAL(10, 2), --总金额region VARCHAR(50) --地区
)
PARTITION BY LIST (region)
(PARTITION First_tier VALUES (北京, 上海, 广州, 深圳),PARTITION second_tier VALUES (天津, 南京, 杭州, 成都),PARTITION third_tier VALUES (哈尔滨, 福州, 长春, 石家庄)
);
将一线城市分区到 First_tier将二线城市分区到 second_tier将三线城市分区到 third_tier 2.3. 哈希分区 哈希分区是基于用户指定分区键上的哈希算法数据库自动将各个分区中均匀分布可用于划分大表提高可管理性。当查询需要扫描整个表时数据在各个分区上分布均匀可以有效地利用并行查询来加速查询操作提高查询性能。例如将一张频繁更新的表创建为哈希分区一张表被分为几个部分后则这几个分区可以被同时更新以减少锁冲突次数。 语法
PARTITION BY HASH (列名) PARTITIONS 分区数; 2.3.1. 按用户ID分区
CREATE TABLE users (user_id INT PRIMARY KEY, --用户IDusername VARCHAR(50), --用户名email VARCHAR(100) --用户邮箱
) PARTITION BY HASH (user_id) PARTITIONS 4; --将用户ID分4个区
PARTITIONS 4表示分4个区 2.3.2. 按订单号分区
CREATE TABLE product_orders (order_id INT PRIMARY KEY, --订单IDproduct_id INT, --产品IDquantity INT, --产品数量order_date DATE --订单日期
) PARTITION BY HASH (order_id) PARTITIONS 8; --将订单ID分8个区
PARTITIONS 8表示分8个区 2.3.3. 自定义hash模
CREATE TABLE t1(id INT
) PARTITION BY HASH (id);CREATE TABLE t1_hash_m3_r0 PARTITION OF t1 FOR VALUES WITH(modulus 3, remainder 0);
CREATE TABLE t1_hash_m3_r1 PARTITION OF t1 FOR VALUES WITH(modulus 3, remainder 1);
CREATE TABLE t1_hash_m3_r2 PARTITION OF t1 FOR VALUES WITH(modulus 3, remainder 2);
创建完成后会存在如下4张表 向 t1 表插入数值 3。按正常来讲 3 的模是0应该插入到 t1_hash_m3_r0实际上插入在 t1_hash_m3_r1 中 这里的模并不是平时数学中的模是通过哈希算法得出的结果
SELECT ora_hash(3, 3); 3. 应用场景
3.1. 利用EXTRACT提取年份
正常情况下创建一个日期分区
CREATE TABLE sales (id SERIAL PRIMARY KEY, --销售IDsale_date DATE --销售日期
) PARTITION BY RANGE (sale_date);CREATE TABLE sales_2019 PARTITION OF sales FOR VALUES FROM (2019-01-01) TO (2020-01-01);
CREATE TABLE sales_2020 PARTITION OF sales FOR VALUES FROM (2020-01-01) TO (2021-01-01);
CREATE TABLE sales_2021 PARTITION OF sales FOR VALUES FROM (2021-01-01) TO (2022-01-01);
自动将sale_date列2019年的数据存储到sales_2019表自动将sale_date列2020年的数据存储到sales_2020表自动将sale_date列2021年的数据存储到sales_2021表 利用EXTRACT函数创建年份分区在创建子分区时可以简写
CREATE TABLE sales (id SERIAL PRIMARY KEY, --销售IDsale_date DATE --销售日期
) PARTITION BY RANGE (EXTRACT(YEAR FROM sale_date));CREATE TABLE sales_2019 PARTITION OF sales FOR VALUES FROM (2019) TO (2020);
CREATE TABLE sales_2020 PARTITION OF sales FOR VALUES FROM (2020) TO (2021);
CREATE TABLE sales_2021 PARTITION OF sales FOR VALUES FROM (2021) TO (2022);
存储信息与案例一相同写法不同 EXTRACT的各种用法
EXTRACT(类型 FROM 列名)
类型如下/*YEAR提取年份部分QUARTER提取季度部分MONTH提取月份部分DAY提取日期中的天部分HOUR提取小时部分MINUTE提取分钟部分SECOND提取秒部分 */
提取年份、季度、月份在创建子分区时直接使用数值
CREATE TABLE sales (id SERIAL PRIMARY KEY, --销售IDsale_date DATE --销售日期
) PARTITION BY RANGE (EXTRACT(YEAR FROM sale_date), --年份EXTRACT(QUARTER FROM sale_date), --季度EXTRACT(MONTH FROM sale_date) --月份
);/*2020年子分区*/
CREATE TABLE sales_2020_q1_m1 PARTITION OF sales FOR VALUES FROM (2020, 1, 1) TO (2020, 1, 2);
CREATE TABLE sales_2020_q1_m2 PARTITION OF sales FOR VALUES FROM (2020, 1, 2) TO (2020, 1, 3);
CREATE TABLE sales_2020_q1_m3 PARTITION OF sales FOR VALUES FROM (2020, 1, 3) TO (2020, 1, 4);
CREATE TABLE sales_2020_q2_m4 PARTITION OF sales FOR VALUES FROM (2020, 2, 4) TO (2020, 2, 5);
CREATE TABLE sales_2020_q2_m5 PARTITION OF sales FOR VALUES FROM (2020, 2, 5) TO (2020, 2, 6);
CREATE TABLE sales_2020_q2_m6 PARTITION OF sales FOR VALUES FROM (2020, 2, 6) TO (2020, 2, 7);
CREATE TABLE sales_2020_q3_m7 PARTITION OF sales FOR VALUES FROM (2020, 3, 7) TO (2020, 3, 8);
CREATE TABLE sales_2020_q3_m8 PARTITION OF sales FOR VALUES FROM (2020, 3, 8) TO (2020, 3, 9);
CREATE TABLE sales_2020_q3_m9 PARTITION OF sales FOR VALUES FROM (2020, 3, 9) TO (2020, 3, 10);
CREATE TABLE sales_2020_q4_m10 PARTITION OF sales FOR VALUES FROM (2020, 4, 10) TO (2020, 4, 11);
CREATE TABLE sales_2020_q4_m11 PARTITION OF sales FOR VALUES FROM (2020, 4, 11) TO (2020, 4, 12);
CREATE TABLE sales_2020_q4_m12 PARTITION OF sales FOR VALUES FROM (2020, 4, 12) TO (2021, 1, 1);/*2021年子分区*/
CREATE TABLE sales_2021_q1_m1 PARTITION OF sales FOR VALUES FROM (2021, 1, 1) TO (2021, 1, 2);
CREATE TABLE sales_2021_q1_m2 PARTITION OF sales FOR VALUES FROM (2021, 1, 2) TO (2021, 1, 3);
CREATE TABLE sales_2021_q1_m3 PARTITION OF sales FOR VALUES FROM (2021, 1, 3) TO (2021, 1, 4);
CREATE TABLE sales_2021_q2_m4 PARTITION OF sales FOR VALUES FROM (2021, 2, 4) TO (2021, 2, 5);
CREATE TABLE sales_2021_q2_m5 PARTITION OF sales FOR VALUES FROM (2021, 2, 5) TO (2021, 2, 6);
CREATE TABLE sales_2021_q2_m6 PARTITION OF sales FOR VALUES FROM (2021, 2, 6) TO (2021, 2, 7);
CREATE TABLE sales_2021_q3_m7 PARTITION OF sales FOR VALUES FROM (2021, 3, 7) TO (2021, 3, 8);
CREATE TABLE sales_2021_q3_m8 PARTITION OF sales FOR VALUES FROM (2021, 3, 8) TO (2021, 3, 9);
CREATE TABLE sales_2021_q3_m9 PARTITION OF sales FOR VALUES FROM (2021, 3, 9) TO (2021, 3, 10);
CREATE TABLE sales_2021_q4_m10 PARTITION OF sales FOR VALUES FROM (2021, 4, 10) TO (2021, 4, 11);
CREATE TABLE sales_2021_q4_m11 PARTITION OF sales FOR VALUES FROM (2021, 4, 11) TO (2021, 4, 12);
CREATE TABLE sales_2021_q4_m12 PARTITION OF sales FOR VALUES FROM (2021, 4, 12) TO (2022, 1, 1);3.2. 分区表性能对比
简介中说到了分区表性能比普通表更好这里直接举例说明
1、创建一张分区表数据100w行每个分区存储1w行共100分区
--创建一个按数值自动分区的分区表每1w行自动分1个区
CREATE TABLE t1 (c1 INT,c2 TEXT
)
PARTITION BY RANGE (c1)INTERVAL(10000::BIGINT)
(PARTITION p0 VALUES LESS THAN (0)
);--插入100w行数据
INSERT INTO t1 VALUES(generate_series(1, 1000000), md5(random())); 2、创建一张普通表数据100w行结构与分区表一样
CREATE TABLE t2 (c1 INT,c2 TEXT
);
INSERT INTO t2 VALUES(generate_series(1, 1000000), md5(random())); 执行t1和t2的查询语句
explain analyze select * from t1 where c1 900000;
explain analyze select * from t2 where c1 900000;
结果如下
kingbase# explain analyze select * from t1 where c1 900000;QUERY PLAN
---------------------------------------------------------------------------------------------------Seq Scan on t1_p91 (cost0.00..210.00 rows1 width37) (actual time0.020..0.777 rows1 loops1)Filter: (c1 900000)Rows Removed by Filter: 9999Planning Time: 28.207 msExecution Time: 0.895 ms
(5 rows)kingbase# explain analyze select * from t2 where c1 900000;QUERY PLAN
------------------------------------------------------------------------------------------------------------------Gather (cost1000.00..14612.43 rows1 width37) (actual time94.872..120.737 rows1 loops1)Workers Planned: 2Workers Launched: 2- Parallel Seq Scan on t2 (cost0.00..13612.33 rows1 width37) (actual time31.346..35.375 rows0 loops3)Filter: (c1 900000)Rows Removed by Filter: 333333Planning Time: 0.150 msExecution Time: 120.775 ms
(8 rows) 如上分区表成本 0.777普通表成本 120.737。从100w数据耗时看分区表性能是普通表的155倍那么为什么性能提升这么多呢 从t1表的扫描结果来看查询的是 t1_p91 表这张表仅存储1w行数据。而普通表t2扫描的是全表100w行相较之下分区表自然快得多。 需要注意的是这个例子没有索引当加上索引后若非超大数据分区表与普通表性能相差无几。
