使用查询过滤器最佳实践
场景介绍
在企业级数据库业务开发与运维过程中，SQL语句的质量直接决定了数据库的性能稳定性、数据安全性以及业务系统的响应效率。
随着业务规模扩大、开发团队人员流动、业务场景复杂化，非标准化、低性能、高风险的劣质 SQL会频繁出现，且难以通过人工审核完全规避：
- 开发人员因对数据库底层原理不熟悉、业务迭代紧急等原因，可能写出关联表过多、未加索引的全表扫描、大事务查询、非法权限访问等 SQL。
- 这类SQL 一旦上线执行，轻则导致数据库CPU/IO使用率飙升、查询响应超时，影响正常业务流程；重则引发数据库锁表、事务阻塞，甚至造成数据泄露或误删改，带来不可挽回的损失。
- 传统的事后排查（如 SQL慢查询日志分析）存在滞后性，往往是慢SQL已经引发性能问题后才被发现，无法提前规避风险。
- 人工代码评审难以覆盖所有场景，尤其在多团队协作、高频迭代的场景下，效率低下且容易遗漏。
为解决上述痛点，DWS查询过滤器应运而生。它是一种前置性SQL管控机制，允许运维人员或DBA提前识别劣质SQL的核心特征（如表关联数量、SQL 类型、执行时长阈值、权限范围等），通过DDL语句将这些特征抽象为可落地的过滤规则并存储在数据库中。
当业务系统提交 SQL语句请求执行时，数据库会先触发过滤规则校验，匹配到劣质SQL特征的请求会被直接拦截并返回报错信息，只有通过校验的合法SQL才能进入执行流程，从而实现 “防患于未然” 的SQL质量管控。
约束限制
该功能仅9.1.0.100及以上集群版本支持。
前提条件
如果是普通用户无法创建查询过滤规则，需要通过dbadmin系统管理员按以下语法进行授权。建议创建查询过滤规则时尽量缩小适用范围，避免误过滤，或者范围过大导致性能劣化。
1 | GRANT gs_role_block TO user; |
创建查询过滤规则
创建查询过滤规则支持两种方式：
- 控制台方式，参见使用DWS查询过滤器拦截慢SQL章节。
- DDL语法创建方式，语法格式如下，详细解释参见CREATE BLOCK RULE。
1 2 3 4 5 6
CREATE BLOCK RULE [ IF NOT EXISTS ] block_name [ [ TO user_name@'host' ] | [ TO user_name ] | [ TO 'host' ] ] | [ FOR UPDATE | SELECT | INSERT | DELETE | MERGE ] | FILTER BY { SQL ( 'text' ) | TEMPLATE ( template_parameter = value ) } [ WITH ( { with_parameter = value }, [, ... ] ) ];
举例，在业务开发过程中，要想禁止对2张以上的表进行关联查询，此时可以使用DDL语句创建过滤规则：
1 2 3 4
CREATE TABLE test_block_rule1 (c1 int,c2 int); CREATE TABLE test_block_rule2 (c1 int,c2 int); CREATE TABLE test_block_rule3 (c1 int,c2 int); CREATE BLOCK RULE forbid_2_t_sel FOR SELECT FILTER BY SQL('test_block_rule') WITH(table_num='2');
table_num指的是一个语句中出现的表的个数，此时所有查询语句不能包含有两张表以上的查询。
1 2 3
----两张表直接关联查询，可以正常执行 SET enable_fast_query_shipping=off; SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2;
1 2
----三张表直接关联查询，被拦截 SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2 JOIN test_block_rule3 t3 ON t2.c1=t3.c1;
所有选项均支持二次修改，如果需要去除部分字段的限制，可以指定default关键词，例如：
1 2 3 4
--修改为只能查询1张表 ALTER BLOCK RULE forbid_2_t_sel with(table_num='1'); SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2; SELECT * FROM test_block_rule1 t1;
1 2 3 4
--去除查询中表个数的限制 ALTER BLOCK RULE forbid_2_t_sel with(table_num=default); --再次查询报错拦截 SELECT * FROM test_block_rule1 t1;
如果想查看或者导入查询过滤规则的定义，可以通过pg_get_blockruledef进行查询。
1 | SELECT * FROM pg_get_blockruledef('forbid_2_t_sel'); |
所有的查询过滤规则元数据全部保存在pg_blocklists系统表中，可以通过查看系统表浏览所有的查询过滤规则。
1 | gaussdb=# SELECT * FROM pg_blocklists; |
使用关键词进行查询过滤
CREATE BLOCK RULE bl1
To block_user
FOR SELECT
FILTER BY SQL ('tt')
WITH(partition_num='2',
table_num='1',
estimate_row='5'
);
SELECT * FROM tt;
ERROR: hit block rule bl1(user_name: block_user, block_type: SELECT, regexp_sql: tt, partition_num: 2(3), table_num: 1(1), estimate_row: 5(1)) 从上面的查询可以看出，查询语句包含了tt关键字，并且扫描的分区个数超过了2，此时执行语句被过滤拦截。需要注意的是，扫描分区的个数并不总是准确的，仅能识别静态的分区剪枝个数，执行过程中的动态剪枝并不能被识别。
使用关键词过滤时可以先使用正则匹配符~*进行测试，正则匹配是忽略大小写的。
另外，由于查询过滤器的规则直接作用在用户block_user上，因此在删除用户block_user时，会提示有依赖项，此时可以通过在语句最后加上cascade进行删除，此时作用在此用户上的查询过滤规则也会被一同删除。
注意，过滤规则命中的依据是，with_parameter命中任意一项，且其他字段的特征也符合即会判定为符合查询过滤规则。
特别注意，不同的计划，可能部分字段无法按照预期进行拦截，例如：
1 2 3 4 5 6 7 8 9 10 11 | gaussdb=# CREATE BLOCK RULE test FILTER BY sql('test')WITH(estimate_row='3'); CREATE BLOCK RULE gaussdb=# SELECT * FROm test; c1 | c2 ----+---- 1 | 2 1 | 2 1 | 2 1 | 2 1 | 2 (5 rows) |
此时，语句关键字是可以匹配上的，查询的行数也超过了3行的限制，那为什么无法拦截呢？
1 2 3 4 5 6 7 8 9 10 11 12 13 | gaussdb=# EXPLAIN VERBOSE SELECT * FROM test; QUERY PLAN ----------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-width | E-costs ----+----------------------------------------------+--------+------------+---------+--------- 1 | -> Data Node Scan on "__REMOTE_FQS_QUERY__" | 0 | | 0 | 0.00 Targetlist Information (identified by plan id) -------------------------------------------------------- 1 --Data Node Scan on "__REMOTE_FQS_QUERY__" Output: test.c1, test.c2 Node/s: All datanodes (node_group, bucket:16384) Remote query: SELECT c1, c2 FROM public.test |
通过计划可以看出，此时是FQS（fast_query_shipping）计划，导致没有估算信息。因此此时无法进行拦截，对于CN轻量化的计划也是一样的，如果让语句强制走stream计划，那么就可以拦截成功：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | gaussdb=# SET enable_stream_operator=on; SET gaussdb=# SET enable_fast_query_shipping=off; SET gaussdb=# SELECT * FROM test; ERROR: hit block rule test(regexp_sql: test, estimate_row: 3(5)) gaussdb=# EXPLAIN VERBOSE SELECT * FROM test; QUERY PLAN ----------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-width | E-costs ----+----------------------------------------+--------+------------+---------+--------- 1 | -> Row Adapter | 5 | | 8 | 69.00 2 | -> Vector Streaming (type: GATHER) | 5 | | 8 | 69.00 3 | -> CStore Scan on public.test | 5 | | 8 | 59.01 Targetlist Information (identified by plan id) -------------------------------------------------------- 1 --Row Adapter Output: c1, c2 2 --Vector Streaming (type: GATHER) Output: c1, c2 Node/s: All datanodes (node_group, bucket:16384) 3 --CStore Scan on public.test Output: c1, c2 Distribute Key: c1 |
所以，如果估算信息不准确，也会导致误拦截或者漏拦截的情况，因为计划的信息是通过估算得到的，因此这种情况无法避免。
使用语句归一化特征值进行查询过滤
语句归一化的特征值，目前有两个，分别是unique_sql_id和sql_hash，两者均是对查询树进行哈希计算之后得出的，区别在于前者是64位哈希值，后者是md5值，因此前者的重复概率会大于后者，在使用时尽量使用sql_hash进行过滤。
获取这两个值的方法：
方法一：查看explain结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | gaussdb=> EXPLAIN VERBOSE SELECT * FROM tt WHERE a>1; QUERY PLAN ---------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-width | E-costs ----+---------------------------------------------------+--------+------------+---------+--------- 1 | -> Row Adapter | 1 | | 8 | 16.00 2 | -> Vector Streaming (type: GATHER) | 1 | | 8 | 16.00 3 | -> Vector Partition Iterator | 1 | | 8 | 6.00 4 | -> Partitioned CStore Scan on public.tt | 1 | | 8 | 6.00 Predicate Information (identified by plan id) ------------------------------------------------- 3 --Vector Partition Iterator Iterations: 3 4 --Partitioned CStore Scan on public.tt Filter: (tt.a > 1) Pushdown Predicate Filter: (tt.a > 1) Partitions Selected by Static Prune: 1..3 Targetlist Information (identified by plan id) ---------------------------------------------- 1 --Row Adapter Output: a, b 2 --Vector Streaming (type: GATHER) Output: a, b Node/s: datanode1 3 --Vector Partition Iterator Output: a, b 4 --Partitioned CStore Scan on public.tt Output: a, b ====== Query Summary ===== ----------------------------------------------------- Parser runtime: 0.029 ms Planner runtime: 0.286 ms Unique SQL Id: 2229243778 Unique SQL Hash: sql_aae71adfaa3d91bfe75499d92ad969e8 (34 rows) |
方法二：查看TopSQL记录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | queryid | 95701492082350773 query | select * from tt where a>10; query_plan | 1 | Row Adapter (cost=14.00..14.00 rows=1 width=8) | 2 | ->Vector Streaming (type: GATHER) (cost=0.06..14.00 rows=1 width=8) | 3 | ->Vector Partition Iterator (cost=0.00..4.00 rows=1 width=8) | | Iterations: 2 | 4 | ->Partitioned CStore Scan on public.tt (cost=0.00..4.00 rows=1 width=8) | | Filter: (tt.a > 10) | | Pushdown Predicate Filter: (tt.a > 10) | | Partitions Selected by Static Prune: 2..3 node_group | installation pid | 139803379566936 lane | fast unique_sql_id | 2229243778 session_id | 1732413324.139803379566936.coordinator1 min_read_bytes | 0 max_read_bytes | 0 average_read_bytes | 0 min_write_bytes | 0 max_write_bytes | 0 average_write_bytes | 0 recv_pkg | 2 send_pkg | 2 recv_bytes | 3297 send_bytes | 57 stmt_type | SELECT except_info | unique_plan_id | 0 sql_hash | sql_aae71adfaa3d91bfe75499d92ad969e8 |
可以看出两种方法都可以轻松获取这两个语句归一化的特征值，explain可以在事前提前获取，topsql可以在语句执行后进行获取。
注意，语句中的条件有变化，也不会影响归一化的特征值，因为归一化过程中会去除常量的影响，上述的举例中两个语句条件中的常量值并不相同，但归一化的特征值确实一样的。
异常熔断示例
确保CCN运行正常、功能正常。 用户配置自定义异常规则或触发默认异常规则。 作业异常熔断阈值query_exception_count_limit大于等于0
- 设置异常熔断阈值。假设设置query_exception_count_limit=1，即只要作业触发异常规则作业就会被加入黑名单。
- 配置异常规则。
创建CPU平均使用率异常规则cpu_percent_except，作业运行时间超过2000秒且CPU使用率达到30%时触发异常退出。
1CREATE EXCEPT RULE cpu_percent_except WITH(ELAPSEDTIME=2000, CPUAVGPERCENT=30);
异常规则还支持BLOCKTIME、ALLCPUTIME、SPILLSIZE等异常的识别处理。
- 创建资源池respool1关联异常规则cpu_percent_except。
1CREATE RESOURCE POOL respool1 WITH(except_rule='cpu_percent_except');
资源池支持最多关联63个异常规则集，每个异常规则集间独立生效，互不影响。
- 创建业务用户usr1。
1CREATE USER usr1 RESOURCE POOL 'respool1' PASSWORD 'XXXXXX';
- 用户usr1运行作业触发异常规则后作业被记录到黑名单。
创建的用户usr1运行作业，作业运行时间超过2000秒且CPU使用率达到30%时触发“cpu_percent_except”异常规则，作业触发异常规则后系统对作业进行以下处理： 将作业异常信息保存至系统表GS_BLOCKLIST_QUERY中。 如果作业触发异常熔断，将系统表GS_BLOCKLIST_QUERY中作业黑名单标志置为true。 更新GS_BLOCKLIST_QUERY中作业黑名单信息。
- 查询作业黑名单和异常信息。
1 2 3 4 5
SELECT * FROM dbms_om.gs_blocklist_query; unique_sql_id | block_list | except_num | except_time ---------------+------------+------------+---------------------------- 4066836196 | t | 1 | 2022-08-08 18:00:00.596269 (1 row)
- 用户usr1再次运行作业触发异常熔断，DWS的异常熔断机制禁止该作业执行。
1 2
ERROR: The query is in the blocklist and cannot be run, unique_sql_id(4066836196). HINT: If you want to run the query later, confirm the reason why the query is blocklisted and remove the query from the blocklist after resolving the problem.
- 优化用户usr1所运行的SQL后，将ID为4066836196的SQL从黑名单移除。
确认SQL异常原因，如果异常规则配置不合理，修改异常规则；如果异常规则合理，对SQL进行优化后重新运行。确认问题解决后将SQL移除黑名单。
1 2 3 4 5
SELECT gs_remove_blocklist(4066836196); gs_remove_blocklist --------------------- t (1 row)
查看过滤时间和拦截记录
可以配置GUC参数analysis_options查看查询过滤规则对正常语句所消耗的时间。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SET analysis_options='on(BLOCK_RULE)'; -- explain performance + query User Define Profiling ----------------------------------------------------------------- Segment Id: 3 Track name: Datanode build connection datanode1 (time=0.288 total_calls=1 loops=1) datanode2 (time=0.301 total_calls=1 loops=1) datanode3 (time=0.321 total_calls=1 loops=1) datanode4 (time=0.268 total_calls=1 loops=1) Segment Id: 3 Track name: Datanode wait connection datanode1 (time=0.016 total_calls=1 loops=1) datanode2 (time=0.038 total_calls=1 loops=1) datanode3 (time=0.021 total_calls=1 loops=1) datanode4 (time=0.017 total_calls=1 loops=1) Segment Id: 1 Track name: block rule check time coordinator1 (time=0.028 total_calls=1 loops=1) |
创建查询过滤规则后会拦截很多烂SQL，如何看拦截的语句有哪些呢？可以通过TopSQL进行查看，abort_info会记录拦截信息，也就是查询的报错信息。
1 2 3 4 5 | gaussdb=# SELECT abort_info,query from GS_WLM_SESSION_INFO WHERE abort_info LIKE '%hit block rule test%'; abort_info | query -----------------------------------------------------------+--------------------- hit block rule test(regexp_sql: test, estimate_row: 3(5)) | select * from test; (1 rows) |
并发管控
创建查询过滤规则时通过设置max_active_num，可以实现对命中过滤规则的语句进行并发控制。需要注意，max_active_num的最大可选值为1000。
gaussdb=# CREATE BLOCK RULE bl1 filter by sql('t1') WITH (max_active_num='2');
CREATE BLOCK RULE
-- \parallel 模拟并发场景
gaussdb=# \parallel on
Parallel is on with scale default 1024.
gaussdb=# select * from t1;
gaussdb=# select * from t1;
gaussdb=# select * from t1;
gaussdb=# select * from t1;
gaussdb=# \parallel
Parallel is off.
ERROR: hit block rule bl1(regexp_sql: t1, max_active_num: 2)
ERROR: hit block rule bl1(regexp_sql: t1, max_active_num: 2)
a
---
(0 rows)
a
---
(0 rows) 可以看到，创建了一个并发限制2的查询过滤规则，超过最大并发数的语句就会被拦截。需要注意的是，并发管控功能针对子语句场景，只管控主语句，不限制子语句的并发。
