tencent cloud

TDSQL Boundless

DokumentasiTDSQL BoundlessKernel FeaturesFunctionality FeaturesOptimizer Hints

Optimizer Hints

Unduh
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-05-26 15:55:49

Overview

TDSQL extends specialized parallel Hints based on compatibility with MySQL's official Hint standards, tailored for distributed parallel execution characteristics. These Hints primarily serve as optimizer directives, enabling users to exercise finer-grained control over policies for parallel execution of queries and enhance the performance of complex queries.

Parallelism Control of the PARALLEL/NO_PARALLEL Hint

The PARALLEL Hint is used to specify the table for parallel scanning (Parallel Scan) and the degree of parallelism.

The PARALLEL Hint is a three-level Hint: Global, Query Block, and Table. An optional degree of parallelism (integer) can be specified at each level. The NO_PARALLEL Hint is used to disable parallelism. For MySQL standard Hint syntax, see Optimizer Hints.

Three Levels of Semantics

Level
Trigger Condition
Action
Global Level
Specified in the main query, and no query block name or table name is given.
integer serves as the default parallelism for the entire query. Subqueries can override this value with their own PARALLEL Hint.
Query Block level
Specified in a non-main query, or explicitly given a query block name but no table name.
integer serves as the default parallelism for this subquery.
Table level
A table name is specified in the Hint.
If only the table name is specified without integer, the parallelism is inherited upward (in the order of Query Block level, Global level, and the max_parallel_degree variable). If no parallelism is ultimately obtained (that is, max_parallel_degree is 0), no parallel execution occurs.
Note:
The Query Block level and the Global level (when the Query Block level is unspecified) determine whether the current Query Block uses a parallel plan. For example, when NO_PARALLEL is specified, that Query Block will not enter the parallel optimization phase. The table-level Hint only affects whether the current table undergoes parallel scanning. If no Hint is specified at the Query Block and Global levels and max_parallel_degree is greater than 0, the parallel optimizer will still select a table for parallel scanning, even if all tables use NO_PARALLEL.
The Hint forces or disables the use of parallelism, overrides the max_parallel_degree variable, and no longer checks whether the cost and table records meet the configured threshold.

Examples

-- Global level
EXPLAIN SELECT /*+ PARALLEL(4) */ * FROM t1 WHERE a > 4;

-- Query Block level (effective only for the top-level query)
EXPLAIN SELECT /*+ QB_NAME(q1) PARALLEL(@q1 4) */ * FROM t1 WHERE a > 4;

-- Query Block level (effective only in subqueries)
EXPLAIN SELECT * FROM t3 WHERE a = (SELECT /*+ PARALLEL(2) */ COUNT(a) FROM t3);

-- Table level
EXPLAIN SELECT /*+ PARALLEL(t1 4) */ * FROM t1 WHERE a > 4;
EXPLAIN SELECT /*+ PARALLEL(t3@q2 4) */ * FROM t3 WHERE a = (SELECT /*+ QB_NAME(q2) */ COUNT(a) FROM t3);
EXPLAIN SELECT /*+ PARALLEL(@q2 t3 4) */ * FROM t3 WHERE a = (SELECT /*+ QB_NAME(q2) */ COUNT(a) FROM t3);
By combining the PARALLEL Hint with a fixed query plan, you can enable parallelism for specific statements without modifying them:
CALL dbms_admin.statement_outline_add_rule('test', 'select /*+ PARALLEL(4) */ sum(c),b from t1 group by b');

PQ_DISTRIBUTE Hint Data Distribution Strategy of the Hint

The PQ_DISTRIBUTE Hint instructs the parallel query optimizer on how to add data redistribution operations to the query plan, as well as the parallel execution policy for GROUP BY, ORDER BY, and window functions (WINDOW). The syntax is as follows:


Semantics of dist_type

Table target (table target)
Syntax
Meaning
PQ_DISTRIBUTE(t1 NONE)
Single dist_type: Controls the distribution of this table. NONE = No redistribution (keep local data).
PQ_DISTRIBUTE(t1 GATHER)
Single dist_type: Controls the distribution of this table. GATHER = Add a collector (gather to the leader).
PQ_DISTRIBUTE(t1 d1, d2)
Two dist_type: The first one = the peer table (the other side of the join), the second one = this table. Their values are the same as above.
Note:
Currently, only NONE and GATHER are actually effective. HASH / BROADCAST can be parsed but are treated as unspecified.
Operation target (operation target: AGGREGATE / SORT / WINDOW)
Syntax
Meaning
PQ_DISTRIBUTE(AGGREGATE GATHER)
Single-stage aggregation: gather all data to the leader first, then perform aggregation.
PQ_DISTRIBUTE(AGGREGATE NONE)
Push-down single-stage aggregation: aggregate directly on each worker, without redistributing data.
PQ_DISTRIBUTE(AGGREGATE NONE, GATHER)
Two-stage aggregation: workers perform partial aggregation → gather → the leader performs final aggregation.
PQ_DISTRIBUTE(SORT GATHER)
Sort not pushed down: gather all data to the leader first, then perform sorting.
PQ_DISTRIBUTE(SORT NONE, GATHER)
Sort pushed down: workers perform partial sorting → gather → the leader performs merge sorting.

Syntax

/*+ PQ_DISTRIBUTE([@query_block] target dist_type[, dist_type]) */
The target has three types:
Target Type
Syntax
Description
Table target
PQ_DISTRIBUTE([@qb] tbl_name dist_type[, dist_type])
Controls the data distribution method of tables in a Join.
Operation target
PQ_DISTRIBUTE([@qb] AGGREGATE｜SORT dist_type[, dist_type])
Controls the parallel execution policy for aggregation or sorting operations.
Named window target
PQ_DISTRIBUTE([@qb] WINDOW win_name dist_type[, dist_type])
Controls the parallel execution policy for specified named window functions.
@qb is an optional query block name (for example, @qb1) used to specify the query block on which the Hint acts.
The dist_type can be set to: NONE or GATHER. Currently, HASH and BROADCAST are not effective and are treated as unspecified.

Target Table Semantics

When target is a table name, the Hint controls the data distribution method of that table in Join operations.
Syntax
Meaning
PQ_DISTRIBUTE(t1 NONE)
The t1 table does not have a Collector added (data remains on each Worker).
PQ_DISTRIBUTE(t1 GATHER)
The t1 table has a Collector added (data is gathered to the Leader).
PQ_DISTRIBUTE(t1 d1, d2)
d1 controls the distribution method of the peer side in a Join, and d2 controls the distribution method of this table (t1).
When two dist_type values are specified, the first parameter d1 applies to the remote table in the Join, and the second parameter d2 applies to the local table. For example:
-- Do not add a Collector for t1 (it remains on the Worker), but add a Collector at the remote side of the Join (to aggregate to the Leader)
SELECT /*+ PQ_DISTRIBUTE(t1 GATHER, NONE) */ * FROM t1 JOIN t2 ON t1.id = t2.id;

Target Operation Semantics

When target is AGGREGATE or SORT, the Hint controls the parallel execution policy for aggregation or sorting operations.

Aggregation (AGGREGATE)

Syntax
Meaning
PQ_DISTRIBUTE(AGGREGATE GATHER)
Single-stage aggregation: gather data from all workers to the Leader first, then perform aggregation.
PQ_DISTRIBUTE(AGGREGATE NONE)
Push-down single-stage aggregation: each Worker performs the complete aggregation directly (no redistribution required).
PQ_DISTRIBUTE(AGGREGATE NONE, GATHER)
Two-stage aggregation: each Worker performs partial aggregation first, then the results are gathered to the Leader for final aggregation.
-- Fully pushed down single-phase aggregation
SELECT /*+ PQ_DISTRIBUTE(AGGREGATE NONE) */ 
       department, AVG(salary) 
FROM employees 
GROUP BY department;

-- Two-phase aggregation between Worker and Leader
SELECT /*+ PQ_DISTRIBUTE(AGGREGATE NONE, GATHER) */ 
       department, AVG(salary) 
FROM employees 
GROUP BY department;

-- Single-phase aggregation performed solely on the Leader
SELECT /*+ PQ_DISTRIBUTE(AGGREGATE GATHER) */ 
       department, AVG(salary) 
FROM employees 
GROUP BY department;

Sorting (SORT)

Syntax
Meaning
PQ_DISTRIBUTE(SORT GATHER)
Sort not pushed down: data from all Workers is gathered to the Leader before sorting is performed.
PQ_DISTRIBUTE(SORT NONE, GATHER)
Sort pushed down: each Worker first performs partial sorting, then gathers to the Leader for merge sorting.
-- Partial sorting by Workers + Merge sort by the Leader
SELECT /*+ PQ_DISTRIBUTE(SORT NONE, GATHER) */ *
FROM large_table 
ORDER BY create_time DESC;


-- Global sorting performed solely on the Leader
SELECT /*+ PQ_DISTRIBUTE(SORT GATHER) */ *
FROM large_table 
ORDER BY create_time DESC;

Target Semantics of Named Windows

When target is WINDOW win_name, the Hint controls the parallel policy for the specified named window function, and its semantics are similar to those of SORT.
SELECT /*+ PQ_DISTRIBUTE(WINDOW w NONE, GATHER) */ SUM(val) OVER w
FROM t1 WINDOW w AS (ORDER BY id);
Examples
-- Table target: Prevent a Collector from being added to t1 during the Join.
SELECT /*+ PQ_DISTRIBUTE(t1 NONE) */ * FROM t1 JOIN t2 ON t1.id = t2.id;

-- Table target (specified query block): Add a Collector to t1, but do not add one to the remote side.
SELECT /*+ PQ_DISTRIBUTE(@qb1 t1 NONE, GATHER) */ * FROM t1 JOIN t2 ON t1.id = t2.id;

-- Operation target: Use two-phase aggregation.
SELECT /*+ PQ_DISTRIBUTE(AGGREGATE NONE, GATHER) */ dept, SUM(salary) FROM emp GROUP BY dept;

-- Operation target: Push down sorting.
SELECT /*+ PQ_DISTRIBUTE(SORT NONE, GATHER) */ * FROM t1 ORDER BY col1;

-- Combined use: Specify both table distribution and aggregation policies simultaneously.
SELECT /*+ PQ_DISTRIBUTE(t1 NONE) PQ_DISTRIBUTE(AGGREGATE NONE, GATHER) */ dept, COUNT(*) FROM t1 GROUP BY dept;
Note:
1. HASH and BROADCAST are currently not supported as values for dist_type. If they are used, they are treated as unspecified, which is equivalent to omitting the Hint.
2. When only one dist_type is specified for a table target, it determines the distribution method for the local table, while the remote side of the JOIN is not constrained.
3. When two dist_type values are specified for a table target, the first parameter controls the remote side, and the second parameter controls the local table.
4. The Hint takes effect only when parallel query (PQ) is enabled.
5. Multiple PQ_DISTRIBUTE Hints can be combined within a single SQL statement to control different targets respectively.

SUBQUERY Hint Policy for Parallel Subqueries

The MySQL SUBQUERY Hint for parallel queries introduces two additional parallelization strategies that can be used in combination with MySQL's native strategies.
PQ_PRE_EVALUATION: Specifies that a subquery is executed in advance before the parent query that references it runs, enabling parallel workers to directly read the subquery result.
PQ_INLINE_EVALUATION: Enforces non-advance execution of subqueries, that is, executes on demand according to the parent query's requirements following MySQL's logic.
Example:
-- Specify that this IN subquery uses the MATERIALIZATION policy and does not employ the pre-evaluation policy in the parallel plan.
EXPLAIN FORMAT=TREE 
SELECT * FROM t1 
WHERE t1.a IN (
    SELECT /*+ SUBQUERY(MATERIALIZATION, PQ_INLINE_EVALUATION) */ a 
    FROM t2
);

-- Specify that the Derived Table subquery t uses the pre-evaluation policy.
EXPLAIN FORMAT=TREE
SELECT /*+ NO_MERGE(t) PQ_DISTRIBUTE(t1 NONE) */   t1.a
FROM  t1, (
        SELECT /*+ SUBQUERY(PQ_INLINE EVALUATION) */  a
        FROM t2
) t
WHERE t1.a = t.a;
Topik sebelumnya: Distribution PolicyTopik selanjutnya: Binlog Subscription

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan