Estos contenidos se han traducido de forma automática para su comodidad, pero Huawei Cloud no garantiza la exactitud de estos. Para consultar los contenidos originales, acceda a la versión en inglés.
Centro de ayuda/ Data Warehouse Service/ Prácticas recomendadas / Gestión de carga de recursos y clústeres/ Uso de filtros de consulta
Actualización más reciente 2026-09-29 GMT+08:00
Ver PDF
Compartir

Uso de filtros de consulta

Escenarios

Durante el desarrollo y la operación y mantenimiento de los servicios de bases de datos de nivel empresarial, la calidad de las sentencias SQL determina directamente la estabilidad de la base de datos, la seguridad de los datos y la eficiencia de respuesta de los sistemas de servicio.

A medida que el negocio crece, los miembros del equipo de desarrollo cambian y los escenarios empresariales se vuelven más complejos, las sentencias SQL de baja calidad, no estándar, de bajo rendimiento y de alto riesgo aparecen con frecuencia y es difícil evitarlas mediante una revisión manual.

  1. Debido a la falta de familiaridad con los principios subyacentes de la base de datos o a la iteración urgente del servicio, los desarrolladores pueden escribir sentencias SQL que involucren tablas asociadas excesivas, análisis completos de tablas sin índices, consultas de transacciones grandes o acceso no autorizado.
  2. Cuando se ejecutan tales sentencias SQL, pueden causar un aumento brusco en el uso de la CPU o de E/S de la base de datos, un tiempo de espera de respuesta de consultas y una interrupción de los procesos comerciales normales. En casos graves, las tablas de la base de datos pueden bloquearse, las transacciones pueden bloquearse y los datos pueden filtrarse o eliminarse por error, lo que provoca una pérdida irreversible.
  3. Las verificaciones tradicionales posteriores al evento (como el análisis de registros de consultas lentas de SQL) detectan los problemas de rendimiento causados por las sentencias SQL lentas solo después de que se han producido, lo que hace imposible prevenir los riesgos.
  4. La revisión manual del código no puede cubrir todos los escenarios y es ineficiente y propensa a la omisión, especialmente en la colaboración de múltiples equipos y la iteración de alta frecuencia.

Filtros de consulta de DWS puede abordar estos puntos problemáticos. Este mecanismo de control de SQL preventivo permite al personal de O&M o a los DBA identificar con anticipación las características principales de las sentencias SQL de baja calidad (como el número de uniones de tablas, los tipos de SQL, el umbral de duración de la ejecución y el alcance de los permisos). Estas características se pueden abstraer en reglas de filtrado viables mediante sentencias DDL y almacenarse en la base de datos.

Cuando un sistema de servicio envía una sentencia SQL para su ejecución, la base de datos activa primero la verificación de la regla de filtrado. Las solicitudes que coinciden con las características de las sentencias SQL de baja calidad son interceptadas directamente, y se devuelve un mensaje de error. Solo las sentencias SQL válidas que pasan la verificación pueden ingresar al proceso de ejecución, lo que permite implementar un control de calidad de SQL proactivo.

Notas y restricciones

Esta función es compatible solo con los clústeres de versión 9.1.0.100 o posterior.

Requisitos previos

Si no puede crear una regla de filtrado de consultas como usuario común, debe otorgar al usuario los permisos necesarios como administrador del sistema dbadmin mediante la siguiente sintaxis. Se recomienda reducir el alcance de la aplicación al crear una regla de filtrado de consultas para mejorar el rendimiento.

1
 
GRANT gs_role_block TO user;

Creación de reglas de filtrado de consultas

Puede crear reglas de filtrado de consultas de cualquiera de las siguientes maneras:

  • Consola:Consulte Uso de filtros de consultas de DWS para interceptar sentencias SQL lentas.
  • Sintaxis DDL: El formato de sintaxis es el siguiente. Para obtener más información, consulte 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 }, [, ... ] ) ];

    Por ejemplo, durante el desarrollo del servicio, si desea deshabilitar las consultas de unión para más de dos tablas, puede utilizar las siguientes sentencias DDL para crear reglas de filtrado:

    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 indica el número de tablas en una sentencia. En este caso, no se pueden utilizar sentencias para consultar más de dos tablas.

    1
2
3
    		 
    ----Direct join query of two tables, which can be executed properly
SET enable_fast_query_shipping=off;
SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2;


    1
2
    		 
    ----Direct join query of three tables, which is intercepted
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;

    Todas las opciones admiten cambios secundarios. Para eliminar las restricciones de algunos campos, puede especificar la palabra clave default. A continuación se muestra un ejemplo:

    1
2
3
4
    		 
    --Change the value to allow query of only one table.
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
    		 
    --Remove the restrictions on the number of tables that can be queried.
ALTER BLOCK RULE forbid_2_t_sel with(table_num=default);
--An error is reported for the next query.
SELECT * FROM test_block_rule1 t1;

Para ver o importar la definición de una regla de filtrado de consultas, puede utilizar pg_get_blockruledef.

1
 
SELECT * FROM pg_get_blockruledef('forbid_2_t_sel');

Los metadatos de todas las reglas de filtrado de consultas se almacenan en el catálogo del sistema pg_blocklists. Puede ver todas las reglas de filtrado de consultas consultando el catálogo del sistema.

1
 
gaussdb=# SELECT * FROM pg_blocklists;

Filtrado de resultados de consultas mediante palabras clave

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))

La sentencia de consulta anterior contiene la palabra clave tt y se escanean más de dos particiones. La sentencia se filtra e intercepta. Tenga en cuenta que el número de particiones escaneadas no siempre es preciso. Solo se puede identificar el número de particiones recortadas estáticamente. No es posible identificar el recorte dinámico durante la ejecución.

Cuando utilice palabras clave para filtrar, puede utilizar primero el operador de coincidencia de expresiones regulares ~* para realizar pruebas. La coincidencia de expresiones regulares no distingue entre mayúsculas y minúsculas.

Además, las reglas del filtro de consulta se aplican directamente al usuario block_user. Cuando elimine este usuario, aparecerá un prompt que indicará que hay dependencias. En este caso, puede agregar CASCADE al final de la sentencia para eliminar el usuario. Las reglas de filtro de consulta aplicadas a este usuario también se eliminarán.

Una regla de filtro de consulta coincide cuando with_parameter coincide con cualquier elemento y las características de otros campos también cumplen con los requisitos.

Es posible que algunos campos no se intercepten como se esperaba en diferentes planes. A continuación se muestra un ejemplo:

 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)

En este caso, la palabra clave de la sentencia coincide y el número de filas consultadas excede el límite superior de tres filas, pero la sentencia no se puede interceptar.

 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

Este es un plan de fast_query_shipping (FQS) sin información de estimación, por lo que la sentencia no se puede interceptar. Un plan ligero de CN causará el mismo resultado. Una sentencia que se ve obligada a usar el plan de stream se puede interceptar.

 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

En conclusión, si la información de estimación es inexacta, puede producirse una interceptación falsa o una interceptación faltante. La información del plan se obtiene mediante estimación, por lo que este error no se puede evitar.

Filtrado de resultados de consultas mediante valores normalizados de características de sentencias

Los valores normalizados de característica de sentencias incluyen unique_sql_id y sql_hash. Ambos se obtienen mediante el cálculo de hash en el árbol de consultas. El primero es un valor hash de 64 bits y el segundo es un valor MD5. El primero tiene una mayor probabilidad de repetición que el segundo. Se recomienda utilizar sql_hash para el filtrado.

Para obtener los dos valores, utilice cualquiera de los siguientes métodos:

Método 1: Ver el resultado de 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)

Método 2: Ver el registro de 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

Ambos métodos se pueden utilizar para obtener los valores normalizados de característica de las dos sentencias. El método explain puede obtener los valores de antemano, mientras que el método TopSQL puede obtenerlos después de que se ejecuten las sentencias.

Tenga en cuenta que los cambios en las condiciones de las sentencias no afectan los valores normalizados de característica, ya que el impacto de las constantes se elimina durante la normalización. En el ejemplo anterior, los valores constantes en las condiciones de las dos sentencias son diferentes, pero los valores normalizados de característica son los mismos.

Ejemplo del mecanismo de disyuntor

Asegúrese de que el CCN se esté ejecutando correctamente. Una regla de excepción definida por el usuario o las reglas de excepción predeterminadas están en vigor. query_exception_count_limit se establece en un valor mayor o igual que 0.

  1. Configure el umbral del disyuntor. Por ejemplo, query_exception_count_limit=1. Es decir, una vez que un trabajo activa una regla de excepción, el trabajo se agregará a la lista de bloqueo.

  2. Configure una regla de excepción.

    Cree la regla cpu_percent_except para terminar los trabajos cuyo tiempo de ejecución excede los 2000 segundos y cuyo uso de CPU alcanza el 30 %.

    1
    		 
    CREATE EXCEPT RULE cpu_percent_except WITH(ELAPSEDTIME=2000, CPUAVGPERCENT=30);

    Las reglas de excepción también pueden identificar y manejar excepciones como BLOCKTIME, ALLCPUTIME y SPILLSIZE.

  3. Cree el grupo de recursos respool1 y asóciela con cpu_percent_except. 

    1
    		 
    CREATE RESOURCE POOL respool1 WITH(except_rule='cpu_percent_except');

    Un grupo de recursos puede asociarse con hasta 63 conjuntos de reglas de excepción, que surten efecto por separado y no se afectan entre sí.

  4. Cree el usuario usr1. 

    1
    		 
    CREATE USER usr1 RESOURCE POOL 'respool1' PASSWORD 'XXXXXX';

  5. Ejecute un trabajo como usuario usr1 y active la regla de excepción. El trabajo se registrará en la lista de bloqueo.

    Ejecute un trabajo como usuario usr1. Cuando el trabajo se ejecuta durante más de 2,000 segundos y su uso de CPU alcanza el 30 %, se activará la regla cpu_percent_except y la información de excepción del trabajo se guardará en el catálogo del sistema GS_BLOCKLIST_QUERY. Si el trabajo activa el disyuntor de excepción, el indicador de lista de bloqueo de trabajos en el catálogo del sistema GS_BLOCKLIST_QUERY se establecerá en true y la información de la lista de bloqueo de trabajos en el catálogo del sistema GS_BLOCKLIST_QUERY se actualizará.

  6. Consulte la lista de bloqueo de trabajos y la información de excepción. 

    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)

  7. Ejecute el trabajo de nuevo como usr1 para activar el disyuntor. DWS prohibirá la ejecución de este trabajo. 

    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.

  8. Optimice la sentencia SQL ejecutada por usr1 y elimine la sentencia SQL con el ID 4066836196 de la lista de bloqueo.

    Localice la causa de la excepción SQL. Si la regla de excepción no está configurada correctamente, modifíquela. Si la regla está configurada correctamente, optimice la sentencia SQL y vuelva a ejecutarla. Una vez rectificada la falla, elimine la sentencia SQL de la lista de bloqueo.

    1
2
3
4
5
    		 
    SELECT gs_remove_blocklist(4066836196);
 gs_remove_blocklist
---------------------
 t
(1 row)

Consulta del tiempo de filtrado y los registros de interceptación

Puede configurar el parámetro GUC analysis_options para ver el tiempo necesario para ejecutar sentencias normales con reglas de filtrado de consultas.

 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)

Después de crear una regla de filtrado de consultas, se interceptan muchas sentencias SQL incorrectas. Puede ver las sentencias SQL interceptadas mediante TopSQL. abort_info registra la información de interceptación, es decir, la información de error de consulta.

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)

Control de concurrencia

Al crear una regla de filtrado de consultas, puede establecer max_active_num para controlar la concurrencia de las sentencias que coinciden con la regla de filtrado. Tenga en cuenta que el valor máximo de max_active_num es 1,000.

gaussdb=# CREATE BLOCK RULE bl1 filter by sql('t1') WITH (max_active_num='2');
CREATE BLOCK RULE
-- \parallel Simulate a concurrency scenario.
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)

Se crea una regla de filtrado de consultas con un límite de concurrencia de 2. Las sentencias que excedan el límite máximo de concurrencia serán interceptadas. El control de concurrencia se aplica solo a la sentencia principal y no limita la concurrencia de las sub-sentencias.

Learn More