# 使用查询过滤器最佳实践 #### 场景介绍 在企业级数据库业务开发与运维过程中，SQL语句的质量直接决定了数据库的性能稳定性、数据安全性以及业务系统的响应效率。 随着业务规模扩大、开发团队人员流动、业务场景复杂化，非标准化、低性能、高风险的劣质 SQL会频繁出现，且难以通过人工审核完全规避： 1. 开发人员因对数据库底层原理不熟悉、业务迭代紧急等原因，可能写出关联表过多、未加索引的全表扫描、大事务查询、非法权限访问等 SQL。 2. 这类SQL 一旦上线执行，轻则导致数据库CPU/IO使用率飙升、查询响应超时，影响正常业务流程；重则引发数据库锁表、事务阻塞，甚至造成数据泄露或误删改，带来不可挽回的损失。 3. 传统的事后排查（如 SQL慢查询日志分析）存在滞后性，往往是慢SQL已经引发性能问题后才被发现，无法提前规避风险。 4. 人工代码评审难以覆盖所有场景，尤其在多团队协作、高频迭代的场景下，效率低下且容易遗漏。 为解决上述痛点，**DWS查询过滤器** 应运而生。它是一种前置性SQL管控机制，允许运维人员或DBA**提前识别劣质SQL的核心特征** （如表关联数量、SQL 类型、执行时长阈值、权限范围等），**通过DDL语句将这些特征抽象为可落地的过滤规则并存储在数据库中**。 当业务系统提交 SQL语句请求执行时，数据库会先触发过滤规则校验，匹配到劣质SQL特征的请求会**被直接拦截并返回报错信息**，只有通过校验的合法SQL才能进入执行流程，从而实现 "防患于未然" 的SQL质量管控。 #### 约束限制 **该功能仅9.1.0.100及以上集群版本支持。** #### 前提条件 如果普通用户无法创建查询过滤规则，需要通过dbadmin系统管理员按以下语法进行授权。建议创建查询过滤规则时尽量缩小适用范围，避免误过滤，或者范围过大导致性能劣化。 ``` GRANT gs_role_block TO user; ``` #### 创建查询过滤规则 创建查询过滤规则支持两种方式： - **控制台方式** ，参见[使用DWS查询过滤器拦截慢SQL](https://support.huaweicloud.com/mgtg-dws/dws_01_72368.html)章节。 - **DDL语法创建方式** ，语法格式如下，详细解释参见[CREATE BLOCK RULE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0292.html)。 ``` 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语句创建过滤规则： ``` 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指的是一个语句中出现的表的个数，此时所有查询语句不能包含有两张以上的表查询。 ``` ----两张表直接关联查询，可以正常执行 SET enable_fast_query_shipping=off; SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002579538260.png "点击放大") ``` ----三张表直接关联查询，被拦截 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; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002609795111.png "点击放大") 所有选项均支持二次修改，如果需要去除部分字段的限制，可以指定default关键词，例如： ``` --修改为只能查询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; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002609797087.png "点击放大") ``` --去除查询中表个数的限制 ALTER BLOCK RULE forbid_2_t_sel with(table_num=default); --再次查询报错拦截 SELECT * FROM test_block_rule1 t1; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002579518040.png "点击放大") 如果想查看或者导入查询过滤规则的定义，可以通过**pg_get_blockruledef**进行查询。 ``` SELECT * FROM pg_get_blockruledef('forbid_2_t_sel'); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002579358482.png "点击放大") 所有的查询过滤规则元数据全部保存在pg_blocklists系统表中，可以通过查看系统表浏览所有的查询过滤规则。 ``` gaussdb=# SELECT * FROM pg_blocklists; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002579518632.png "点击放大") #### 使用关键词进行查询过滤 ``` 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命中任意一项，且其他字段的特征也符合**即会判定为符合查询过滤规则。 特别注意，不同的计划，可能部分字段无法按照预期进行拦截，例如： ``` 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行的限制，但却无法拦截。 ``` 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计划，也能拦截成功： ``` 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结果。 ``` 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记录。 ``` 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。 1. 设置异常熔断阈值。假设设置query_exception_count_limit=1，即只要作业触发异常规则作业就会被加入黑名单。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002501139450.png "点击放大") 2. 配置异常规则。 创建CPU平均使用率异常规则cpu_percent_except，作业运行时间超过2000秒且CPU使用率达到30%时触发异常退出。 ``` CREATE EXCEPT RULE cpu_percent_except WITH(ELAPSEDTIME=2000, CPUAVGPERCENT=30); ``` 异常规则还支持BLOCKTIME、ALLCPUTIME、SPILLSIZE等异常的识别处理。 3. 创建资源池respool1关联异常规则cpu_percent_except。 ``` CREATE RESOURCE POOL respool1 WITH(except_rule='cpu_percent_except'); ``` 资源池支持最多关联63个异常规则集，每个异常规则集间独立生效，互不影响。 4. 创建业务用户usr1。 ``` CREATE USER usr1 RESOURCE POOL 'respool1' PASSWORD 'XXXXXX'; ``` 5. 用户usr1运行作业触发异常规则后作业被记录到黑名单。 创建的用户usr1运行作业，作业运行时间超过2000秒且CPU使用率达到30%时触发"cpu_percent_except"异常规则，作业触发异常规则后系统对作业进行以下处理： 将作业异常信息保存至系统表GS_BLOCKLIST_QUERY中。 如果作业触发异常熔断，会将系统表GS_BLOCKLIST_QUERY中作业黑名单标志置为true， 更新GS_BLOCKLIST_QUERY中作业黑名单信息。 6. 查询作业黑名单和异常信息。 ``` 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) ``` 7. 用户usr1再次运行作业触发异常熔断，DWS的异常熔断机制禁止该作业执行。 ``` 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. ``` 8. 优化用户usr1所运行的SQL后，将ID为4066836196的SQL从黑名单移除。 确认SQL异常原因，如果异常规则配置不合理，修改异常规则；如果异常规则合理，对SQL进行优化后重新运行。确认问题解决后将SQL移出黑名单。 ``` SELECT gs_remove_blocklist(4066836196); gs_remove_blocklist --------------------- t (1 row) ``` #### 查看过滤时间和拦截记录 可以配置GUC参数analysis_options查看查询过滤规则对正常语句所消耗的时间。 ``` 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会记录拦截信息，也就是查询的报错信息。 ``` 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的查询过滤规则，超过最大并发数的语句就会被拦截。**注意**，并发管控功能针对子语句场景，只管控主语句，不限制子语句的并发。 #### 了解更多 - [CREATE BLOCK RULE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0292.html) - [使用DWS查询过滤器拦截慢SQL](https://support.huaweicloud.com/mgtg-dws/dws_01_72368.html)