tencent cloud

Feedback

Supported Statement Scenarios and Restricted Scenarios

Last updated: 2024-10-09 10:46:16
    This document describes the supported statement scenarios and restricted scenarios of the parallel query.

    Supported statement scenarios

    TDSQL-C for MySQL has implemented the parallel query feature for SQL statements with the following characteristics, with more to come.
    Single-table scan: Full-table scan, index scan, index range scan, and index REF query in ascending or descending order are supported.
    Multi-table join: The nested-loop join (NLJ) algorithm as well as semi join, anti join, and outer join are supported.
    Data type: Different data types can be queried, such as integer, string, floating point, time, and overflow (with a runtime size limit).
    There are no restrictions on common operators and functions.
    Supported aggregate functions include COUNT, SUM, AVG, MIN, MAX, STD, VARIANCE, VAR_SAMP, and STDDEV_SAMP.
    UNION and UNION ALL queries are supported.
    Traditional (default), JSON, and tree EXPLAIN formats are supported.
    Parallel execution is supported for the Prepared Statement (PS) mode. This mode allows a statement to be executed multiple times after syntax analysis is performed on it only once. Prepared statements can be used just by modifying variable values, so as to improve the performance.
    Parallel execution is supported for Hash Join (in-memory) statements.
    Derived table subqueries can be separately executed in parallel, independent of main queries.
    Parallel execution is supported for ROLL UP statements.
    Parallel execution is supported for EXPLAIN ANALYZE statements.
    Partitioned tables can be used as parallel tables for parallel query.
    Global aggregation optimization is supported.
    HAVING condition pushdown is supported for parallel query.
    Statement performance improvement If SF is 100 and Dop is 16, the acceleration ratio is as follows.
    SUM, AVG, or COUNT aggregate function.
    SELECT l_returnflag, l_linestatus,
    Sum(l_quantity) AS sum_qty,
    Sum(l_extendedprice) AS sum_base_price,
    Sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    Sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    Avg(l_quantity) AS avg_qty,
    Avg(l_extendedprice) AS avg_price,
    Avg(l_discount) AS avg_disc,
    Count(*) AS count_order FROM
    lineitem WHERE l_shipdate <= date '1998-12-01' - INTERVAL '93' day
    GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus ;
    The execution takes 1,376.96 seconds and 107.25 seconds before and after parallel query is enabled, indicating an acceleration ratio of 12.84.
    ORDER BY or GROUP BY statement
    SELECT l_returnflag, l_linestatus,
    Sum(l_quantity) AS sum_qty,
    Sum(l_extendedprice) AS sum_base_price,
    Sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    Sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    Avg(l_quantity) AS avg_qty,
    Avg(l_extendedprice) AS avg_price,
    Avg(l_discount) AS avg_disc,
    Count(*) AS count_order FROM
    lineitem WHERE l_shipdate <= date '1998-12-01' - INTERVAL '93' day
    GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus ;
    The execution takes 1,376.96 seconds and 107.25 seconds before and after parallel query is enabled, indicating an acceleration ratio of 12.84.
    JOIN, BETWEEN, or IN statement.
    select
    sum(l_extendedprice* (1 - l_discount)) as revenue
    from
    lineitem,
    part
    where
    (
    p_partkey = l_partkey
    and p_brand = 'Brand#12'
    and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and l_quantity >= 6
    and l_quantity <= 6 + 10
    and p_size between 1 and 5
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
    p_partkey = l_partkey
    and p_brand = 'Brand#13'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and l_quantity >= 10 and l_quantity <= 10 + 10
    and p_size between 1 and 10
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
    p_partkey = l_partkey
    and p_brand = 'Brand#24'
    and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and l_quantity >= 21
    and l_quantity <= 21 + 10
    and p_size between 1 and 15
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
    );
    The execution takes 20.55 seconds and 1.87 seconds before and after parallel query is enabled, indicating an acceleration ratio of 11.

    Restricted scenarios

    The parallel query feature of TDSQL-C for MySQL is not supported in the following scenarios.
    Restriction
    Description
    Statement compatibility restriction
    Parallel query is not supported for non-query statements, including INSERT ... SELECT and REPLACE ... SELECT.
    Parallel query is not supported for statements in a stored program.
    Parallel query is not supported for statements in serial isolation-level transactions.
    Parallel query is not supported for locking reads, such as SELECT FOR UPDATE and SELECT ... FOR SHARE.
    Parallel query is not supported for CTEs.
    Table/Index compatibility restriction
    Parallel query is not supported for system, temp, and non-InnoDB tables.
    Parallel query is not supported for space index.
    Parallel query is not supported for full-text index.
    Parallel query is not supported for tables in `index_merge` scan mode.
    Expression/Field compatibility restriction
    Parallel query is not supported for SQL statements containing the Generated Column, BIT, or GEOMETRY field.
    Note:
    Parallel query is supported for SQL statements containing the BLOB, TEXT, or JSON field when the TXSQL 8.0 engine kernel version is 3.1.14 or later, but the prerequisite is that the runtime value of the txsql_parallel_lob_enabled parameter is set to ON. For the operation method, see Setting Instance Parameters.
    Parallel query is not supported for aggregate functions of the BIT_AND, BIT_OR, or BIT_XOR type.
    Parallel query is not supported for DISTINCT aggregations, such as SUM(DISTINCT) and COUNT(DISTINCT).
    Parallel query is not supported for GIS functions such as SP_WITHIN_FUNC and ST_DISTANCE.
    Parallel query is not supported for custom functions.
    Parallel query is not supported for JSON functions such as JSON_LENGTH, JSON_TYPE, and JSON_ARRAYAGG.
    Parallel query is not supported for XML functions such as XML_STR.
    Parallel query is not supported for user-lock functions such as IS_FREE_LOCK, IS_USED_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS, and GET_LOCK.
    Parallel query is not supported for SLEEP, RANDOM, GROUP_CONCAT, SET_USER_VAR, and WEIGHT_STRING functions.
    Parallel query is not supported for window functions.
    Besides the above examples in Supported statement scenarios, you can also check the parallel query execution plan and thread working status to see whether a statement can be queried parallelly. For more information, see Viewing Parallel Query.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support