PostgreSQL/LightDB 分割槽表之分割槽裁剪

哎呀我的天吶發表於2022-07-14

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章