PostgreSQL/LightDB 分割槽表之分割槽裁剪
PostgreSQL分割槽表的分類
PostgreSQL10之前使用繼承式分割槽，這裡我們不再討論，目前LightDB使用的是宣告式分割槽，不再需要繁瑣的觸發器和繼承
直奔主題建立測試表並生成測試資料
drop table partition_table;
CREATE TABLE partition_table (
id int not null,
tm timestamptz not null,
name varchar(10)
) PARTITION BY RANGE (tm);
create table ptab01_202201 partition of partition_table for values from ('2022-01-01') to ('2022-02-01');
create table ptab01_202202 partition of partition_table for values from ('2022-02-01') to ('2022-03-01');
create table ptab01_202203 partition of partition_table for values from ('2022-03-01') to ('2022-04-01');
create table ptab01_202204 partition of partition_table for values from ('2022-04-01') to ('2022-05-01');
create table ptab01_202205 partition of partition_table for values from ('2022-05-01') to ('2022-06-01');
create table ptab01_202206 partition of partition_table for values from ('2022-06-01') to ('2022-07-01');
create table ptab01_202207 partition of partition_table for values from ('2022-07-01') to ('2022-08-01');
create table ptab01_202208 partition of partition_table for values from ('2022-08-01') to ('2022-09-01');
create table ptab01_202209 partition of partition_table for values from ('2022-09-01') to ('2022-10-01');
create table ptab01_202210 partition of partition_table for values from ('2022-10-01') to ('2022-11-01');
create table ptab01_202211 partition of partition_table for values from ('2022-11-01') to ('2022-12-01');
create table ptab01_202212 partition of partition_table for values from ('2022-12-01') to ('2023-01-01');
insert into partition_table
select extract(epoch from seq), seq ,chr(19968+(random()*20901)::int)
from
generate_series('2022-01-01'::timestamptz, '2022-12-31 23:59:59'::timestamptz, interval '10 seconds') as seq;
新增主鍵
lightdb@postgres=# alter table partition_table ADD CONSTRAINT partition_table_pkey PRIMARY KEY (id); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "partition_table" lacks column "tm" which is part of the partition key.
可以看到主鍵必須包含分割槽鍵,這和MySQL一樣,準確的說是唯一約束必須包含分割槽鍵,在 MySQL 資料庫中，分割槽表的索引都是區域性，而非全域性。也就是說，索引在每個分割槽檔案中都是獨立的，所以分割槽表上的唯一索引必須包含分割槽列資訊，否則建立會報錯
CREATE TABLE t (
a INT,
b INT,
c DATETIME,
d VARCHAR(32),
e INT,
KEY idx_e (e)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
mysql> alter table t add primary key(a);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> alter table t add unique key(d);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
mysql> alter table t add primary key(a,c);
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add unique key(d,c);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
而Oracle沒有這方面的限制
CREATE TABLE orders (
o_orderkey number(20,2) NOT NULL PRIMARY KEY,
O_ORDERDATE DATE NOT NULL,
O_NAME varchar(79) NOT NULL) PARTITION BY RANGE (O_ORDERDATE) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2022-01-01', 'yyyy-mm-dd')));
insert into orders values (1,to_date('2021-11-11','yyyy-mm-dd'),'xiaoming');
insert into orders values (2,to_date('2022-01-11','yyyy-mm-dd'),'xiaogang');
insert into orders values (3,to_date('2022-02-11','yyyy-mm-dd'),'xiaoju');
commit;
SQL> alter table orders drop primary key;
Table altered.
SQL> alter table orders add primary key(o_orderkey);
Table altered.
新增分割槽鍵，建立成功
lightdb@postgres=# alter table partition_table ADD CONSTRAINT partition_table_pkey PRIMARY KEY (id,tm); ALTER TABLE
LightDB分割槽裁剪功能
LightDB宣告式分割槽引數是enable_partition_pruning，預設開啟
lightdb@postgres=# show enable_partition_pruning; enable_partition_pruning -------------------------- on (1 row) lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm='2022-07-07'::timestamptz; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- --------------------- Index Scan using ptab01_202207_pkey on ptab01_202207 partition_table (cost=0.42..3043.23 rows=1 width=16) (actual time=0.941..4 .767 rows=1 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1030 Planning Time: 0.108 ms Execution Time: 4.794 ms (5 rows)
下面我們將引數關閉
lightdb@postgres=# set enable_partition_pruning = off; SET lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm='2022-07-07'::timestamptz; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.42..35833.24 rows=12 width=16) (actual time=28.395..55.075 rows=1 loops=1) Buffers: shared hit=12117 -> Index Scan using ptab01_202201_pkey on ptab01_202201 partition_table_1 (cost=0.42..3043.23 rows=1 width=16) (actual time=5.029..5.029 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202202_pkey on ptab01_202202 partition_table_2 (cost=0.42..2748.83 rows=1 width=16) (actual time=4.249..4.250 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=929 -> Index Scan using ptab01_202203_pkey on ptab01_202203 partition_table_3 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.743..4.744 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202204_pkey on ptab01_202204 partition_table_4 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.442..4.442 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202205_pkey on ptab01_202205 partition_table_5 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.633..4.634 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202206_pkey on ptab01_202206 partition_table_6 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.400..4.400 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202207_pkey on ptab01_202207 partition_table_7 (cost=0.42..3043.23 rows=1 width=16) (actual time=0.887..4.551 rows=1 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1030 -> Index Scan using ptab01_202208_pkey on ptab01_202208 partition_table_8 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.818..4.819 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202209_pkey on ptab01_202209 partition_table_9 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.420..4.421 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202210_pkey on ptab01_202210 partition_table_10 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.594..4.595 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202211_pkey on ptab01_202211 partition_table_11 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.453..4.454 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202212_pkey on ptab01_202212 partition_table_12 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.699..4.699 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 Planning Time: 0.293 ms Execution Time: 55.174 ms (40 rows)
可以看到關閉該引數，掃描所有分割槽
同時查詢分割槽列和不同列如何建立索引
lightdb@postgres=# select * from partition_table where tm>'2022-11-07' and name = '坐'; id | tm | name ------------+------------------------+------ 1667797350 | 2022-11-07 13:02:30+08 | 坐 1667842780 | 2022-11-08 01:39:40+08 | 坐 1667860540 | 2022-11-08 06:35:40+08 | 坐 1668111830 | 2022-11-11 04:23:50+08 | 坐 1668120440 | 2022-11-11 06:47:20+08 | 坐 1668367000 | 2022-11-14 03:16:40+08 | 坐 1669046500 | 2022-11-22 00:01:40+08 | 坐 1669095670 | 2022-11-22 13:41:10+08 | 坐 1669228590 | 2022-11-24 02:36:30+08 | 坐 1669235780 | 2022-11-24 04:36:20+08 | 坐 1669536830 | 2022-11-27 16:13:50+08 | 坐 1669671070 | 2022-11-29 05:31:10+08 | 坐 1669703440 | 2022-11-29 14:30:40+08 | 坐 1670254170 | 2022-12-05 23:29:30+08 | 坐 1670342590 | 2022-12-07 00:03:10+08 | 坐 1670582230 | 2022-12-09 18:37:10+08 | 坐 1670632600 | 2022-12-10 08:36:40+08 | 坐 1671060810 | 2022-12-15 07:33:30+08 | 坐 1671463300 | 2022-12-19 23:21:40+08 | 坐 1671568700 | 2022-12-21 04:38:20+08 | 坐 1671765020 | 2022-12-23 11:10:20+08 | 坐 1671865830 | 2022-12-24 15:10:30+08 | 坐 1672242530 | 2022-12-28 23:48:50+08 | 坐 1672496430 | 2022-12-31 22:20:30+08 | 坐 (24 rows) lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm>'2022-11-07' and name = '坐'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..11262.72 rows=23 width=16) (actual time=3.893..46.873 rows=24 loops=1) Buffers: shared hit=3357 -> Seq Scan on ptab01_202211 partition_table_1 (cost=0.00..5539.00 rows=10 width=16) (actual time=3.890..22.586 rows=13 loops=1) Filter: ((tm > '2022-11-07 00:00:00+08'::timestamp with time zone) AND ((name)::text = '坐'::text)) Rows Removed by Filter: 259187 Buffers: shared hit=1651 -> Seq Scan on ptab01_202212 partition_table_2 (cost=0.00..5723.60 rows=13 width=16) (actual time=3.891..24.238 rows=11 loops=1) Filter: ((tm > '2022-11-07 00:00:00+08'::timestamp with time zone) AND ((name)::text = '坐'::text)) Rows Removed by Filter: 267829 Buffers: shared hit=1706 Planning Time: 0.157 ms Execution Time: 46.922 ms (12 rows)
是建立包含分割槽鍵的索引還是？
我們知道Oracle的分割槽索引分Global Index 和Local Index，查詢條件有跨分割槽和不跨分割槽的情況，當查詢跨分割槽的時候，且where條件中包含分割槽欄位，我們可以只需要在非分割槽欄位建立本地索引（本地的組合索引），分割槽欄位會自行的進行分割槽裁剪，前提是分割槽欄位在其中的幾乎沒有過濾掉資料，另外如果是那中按照月分的，舉例: where t>'01-29’date and t < '0202’date 這種情況也是需要包含分割槽欄位的,因為選擇條件顧慮掉了大量資料，分割槽裁剪作用有限，需要時間欄位索引配合；
如果查詢不跨分割槽那麼就需要建立包含分割槽欄位的字首索引，這時候能過濾掉大量的不必要的資料，當然具體情況還要根據分割槽的大小、系統中一般的查詢條件、要求多長時間返回資料而定；
當查詢條件中沒有分割槽欄位這時候就需要建立global索引。
是建立(name)的索引還是(name,time)的索引呢？
lightdb@postgres=# create index i_partition_table_name on partition_table(name); CREATE INDEX lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm >'2022-11-07' and name = '坐'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Append (cost=0.29..28.21 rows=23 width=16) (actual time=0.035..0.123 rows=24 loops=1) Buffers: shared hit=31 -> Index Scan using ptab01_202211_name_idx on ptab01_202211 partition_table_1 (cost=0.29..13.53 rows=10 width=16) (actual time=0.033..0.057 rows=13 loops=1) Index Cond: ((name)::text = '坐'::text) Filter: (tm > '2022-11-07 00:00:00+08'::timestamp with time zone) Rows Removed by Filter: 3 Buffers: shared hit=18 -> Index Scan using ptab01_202212_name_idx on ptab01_202212 partition_table_2 (cost=0.30..14.56 rows=13 width=16) (actual time=0.017..0.036 rows=11 loops=1) Index Cond: ((name)::text = '坐'::text) Filter: (tm > '2022-11-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=13 Planning Time: 0.202 ms Execution Time: 0.169 ms (13 rows) lightdb@postgres=# create index i_partition_table_nametm on partition_table(name,tm); CREATE INDEX lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm>'2022-11-07' and name = '坐'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.42..26.29 rows=23 width=16) (actual time=0.031..0.122 rows=24 loops=1) Buffers: shared hit=29 -> Index Scan using ptab01_202211_name_tm_idx on ptab01_202211 partition_table_1 (cost=0.42..11.62 rows=10 width=16) (actual time=0.029..0.053 rows=13 loops=1) Index Cond: (((name)::text = '坐'::text) AND (tm > '2022-11-07 00:00:00+08'::timestamp with time zone)) Buffers: shared hit=16 -> Index Scan using ptab01_202212_name_idx on ptab01_202212 partition_table_2 (cost=0.30..14.56 rows=13 width=16) (actual time=0.018..0.039 rows=11 loops=1) Index Cond: ((name)::text = '坐'::text) Filter: (tm > '2022-11-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=13 Planning Time: 0.207 ms Execution Time: 0.207 ms (11 rows)
LightDB沒有local索引和global索引一說，你可以理解為LightDB的每個分割槽表都是一個單獨的實體表，基於上面的情況，多數情況下查詢條件涉及到分割槽欄位還是要建立包含分割槽欄位的索引的
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2905714/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL/LightDB分割槽表之常見問題SQL
- PostgreSQL：傳統分割槽表SQL
- PostgreSQL：內建分割槽表SQL
- oracle分割槽表和分割槽表exchangeOracle
- PostgreSQL分割槽表更新思路SQL
- oracle分割槽表和非分割槽表exchangeOracle
- 分割槽表之自動增加分割槽（11G）
- 從10046看Oracle分割槽裁剪Oracle
- 非分割槽錶轉換成分割槽表
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- PG的非分割槽表線上轉分割槽表
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- 【MYSQL】 分割槽表MySql
- PostgreSQL使用表繼承實現分割槽表SQL繼承
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- MySql資料分割槽操作之新增分割槽操作MySql
- 【Linux】MBR磁碟分割槽表只能有四個分割槽？Linux
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- 分割槽表-實戰
- MySQL 分割槽表探索MySql
- Linux分割槽方案、分割槽建議Linux
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- PostgreSQL分割槽表、繼承表記錄去重方法SQL繼承
- 非分割槽錶轉換成分割槽表以及注意事項
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- PostgreSQL 原始碼解讀（98）- 分割槽表#4（資料查詢路由#1-“擴充套件”分割槽表）SQL原始碼路由套件
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件