辅助索引表
更新时间：2024-07-01 14:39:47
使用场景
很多分布式场景下，一个分片字段并不能满足所有的场景。
比如历史业务账单按用户 ID 来分片很适合做用户统计，但还有一些情况需要按订单号查询。这种情况下为了找到一个订单对应的记录，因为不知这条记录在哪个节点上，所以则需要全节点扫描。这里就无法体现分布式的优点，造成资源浪费（有效工作节点只有一个），特别是在高并发时，就很可能会造成 CPU 不够用。
基于这种情况，AntDB 的辅助表功能可以有效的应对。
原理
假如有表 t1(a,b,c,...)，a 为分片列，b 为上面场景中的“订单”列。
- 针对非分片列 b 增加一张辅助索引表。辅助索引表的分片列为 b，和非分片字段 a，所有的数据都与主表 t1 保持同步。
- 如果查询 t1 表时包含的过滤条件中有 “b=xxx” 这样的表达式，则把xxx带入到对应的辅助索引表中，查询出列 a 的值所在的节点。
- 修改执行计划，只在上一步查找出的节点上执行查询。
最终的执行时间分三种情况：
- 一般情况：本需要全节点扫描的查询，现在只需要在两个节点上扫描。单次查询时间变长，高并发查询时间变短。
- 最优情况：第二步查询不到符合条件的数据，则不需要再扫描主表。单次查询时间不变，高并发查询时间变短。
- 最坏情况：第二步查到的数据在所有节点上，仍需要全节点扫描主表。单次查询时间变长，高并发查询时间变长。
所以创建辅助索引表和创建索引相同，要找那些重复率低的列。否则最终反而会使查询时间变长。
因为有辅助表的存在，主表数据的更新操作会变的更慢（主表数据更新时会同步更新辅助表）。
用例
创建主表
CREATE TABLE tb(id int, name text, age int) DIESTRIBUTE BY hash(id);
在 name 上创建辅助索引
CREATE auxiliary TABLE ON tb(name);
\d+ tb
antdb=# \d+ tb
Table "public.tb"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | text | | | | extended | |
age | integer | | | | plain | |
Auxiliary table:
"tb_name_aux" on tb(name)
DISTRIBUTE BY HASH(id) TO NODE(dn1, dn2, dn3)
Access method: heap
在主表 tb 上插入一些数据
INSERT INTO tb SELECT n,'name'||n,random()*100 FROM generate_series(1,10) AS n;
SELECT *,adb_node_oid() AS node FROM tb;
id | name | age | node
----+--------+-----+-------
2 | name2 | 87 | 16388
6 | name6 | 76 | 16388
7 | name7 | 77 | 16388
1 | name1 | 70 | 16389
4 | name4 | 29 | 16389
8 | name8 | 16 | 16389
9 | name9 | 95 | 16389
10 | name10 | 66 | 16389
3 | name3 | 100 | 16387
5 | name5 | 12 | 16387
(10 rows)
非分片字段等值执行计划
--查询结果
SELECT * FROM tb WHERE name='name2';
id | name | age
----+-------+-----
2 | name2 | 87
(1 row)
--执行计划：name为非分片字段,最终执行计划只在16388这个节点上执行
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name='name2';
QUERY PLAN
-------------------------------------------------------
Cluster Gather (actual rows=1 loops=1)
Remote node: 16388
-> Tid Scan on public.tb (actual rows=0 loops=1)
Output: id, name, age
TID Cond: (tb.ctid = '(0,1)'::tid)
Filter: (tb.name = 'name2'::text)
Remote node: 16388
Node 16388: (actual rows=1 loops=1)
Planning Time: 0.728 ms
Execution Time: 1.664 ms
(10 rows)
--关闭使用辅助表功能后的执行计划
SET use_aux_type =off;
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name='name2';
QUERY PLAN
-----------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (actual rows=1 loops=1)
Output: tb.id, tb.name, tb.age
Primary node/s: dn1
Node/s: dn1, dn2, dn3
Remote query: SELECT id, name, age FROM public.tb tb WHERE (name = 'name2'::text)
Planning Time: 0.083 ms
Execution Time: 9.138 ms
(7 rows)
--对in表达式同样支持,两条记录在同一个节点上
SET use_aux_type =on;
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name IN('name2','name7');
QUERY PLAN
------------------------------------------------------------------
Cluster Gather (actual rows=2 loops=1)
Remote node: 16388
-> Tid Scan on public.tb (actual rows=0 loops=1)
Output: id, name, age
TID Cond: (tb.ctid = ANY ('{"(0,1)","(0,3)"}'::tid[]))
Filter: (tb.name = ANY ('{name2,name7}'::text[]))
Remote node: 16388
Node 16388: (actual rows=2 loops=1)
Planning Time: 11.996 ms
Execution Time: 1.516 ms
(10 rows)
--两条记录在两个不同节点上
EXPLAIN (verbose,analyze,costs off,timing off) SELECT * FROM tb WHERE name IN('name2','name5');
QUERY PLAN
------------------------------------------------------------------
Cluster Gather (actual rows=2 loops=1)
Remote node: 16387,16388
-> Tid Scan on public.tb (actual rows=0 loops=1)
Output: id, name, age
TID Cond: (tb.ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
Filter: (tb.name = ANY ('{name2,name5}'::text[]))
Remote node: 16387,16388
Node 16387: (actual rows=1 loops=1)
Node 16388: (actual rows=1 loops=1)
Planning Time: 1.465 ms
Execution Time: 2.651 ms
(11 rows)
语法
创建辅助索引表的语法为：
CREATE AUXILIARY TABLE [auxiliary_table_name]
ON master_table_name ( column_name [ index_options ])
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY OptDistributeType ]
[ TO NODE (node_name [, ...] ) | TO GROUP pgxcgroup_name ]
index_options:
/* empty */
| INDEX [ CONCURRENTLY ] [ name ] [ USING method ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
说明：
AUXILIARY 为辅助表关键字
auxiliary_table_name 为辅助表表名，可选，若不指定则按照：主表名+辅助字段名+tbl 命名。
master_table_name 为主表表名，必选。
column_name 为主表字段名（需要建辅助表的字段），必选。
index_options 辅助表索引信息，指定时按照指定语法创建索引。未指定时，则按照默认参数创建索引。（辅助表的column_name字段必然创建索引。默认时，索引方法默认btree，命名空间与辅助表保持一致。）
tablespace_name 辅助表命名空间，可选。未指定时，为当前默认tablespace。
DISTRIBUTE BY OptDistributeType 辅助表分片方式，可选。未指定时，以辅助字段HASH分片。
TO NODE (node_name [, ...] ) | TO GROUP pgxcgroup_name 辅助表分片节点，可选。未指定时，默认ALL DATANODES。
问题反馈