tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Usage specification recommendations
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Configuration Change
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
문서TDSQL BoundlessPerformance TuningSQL TuningSQL Tuning

SQL Tuning

PDF
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-03-23 16:02:31
After using the tools in the Understanding Execution Plans section to identify genuine slow queries and pinpoint their bottlenecks, users may need to take corresponding measures to optimize query execution efficiency. These measures generally fall into three categories:
Adjust the SCHEMA of the tables involved in the query.
Modify the executor-related parameters.
Intervene in the optimizer's selection of execution plans.

Adjust SCHEMA

When the execution plan selected by the optimizer is not optimal, users can first check whether there is a significant deviation between the estimated row counts and the actual values in each part of the execution plan. If so, further check whether there are corresponding statistics for the columns involved in the query, whether these statistics are outdated and cannot reflect the latest data distribution, or whether the statistics are not outdated but inherently inaccurate; in such cases, it is necessary to recollect statistics, or increase the parameters related to the statistics sampling rate and then recollect statistics, such as sample_sst_blocks, but note that increasing the sampling rate will result in higher overhead for ANALYZE statements. Example:
# Example 1: Missing Statistics
tdsql> set global tdsql_get_index_stats_from_tdstore = on;
Query OK, 0 rows affected (0.01 sec)

tdsql> create table t1(a int, b int);
Query OK, 0 rows affected (0.21 sec)

tdsql> insert into t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

tdsql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

# Column a lacks appropriate statistics, and the estimated row count of 1 is inaccurate
tdsql> explain select * from t1 where a >= 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

tdsql> analyze table t1 update histogram on a;
+---------+-----------+----------+----------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                     |
+---------+-----------+----------+----------------------------------------------+
| test.t1 | histogram | status   | Histogram statistics created for column 'a'. |
+---------+-----------+----------+----------------------------------------------+
1 row in set (0.01 sec)

# After collecting a histogram for Column a, the estimated row count 2 is accurate
tdsql> explain select * from t1 where a >= 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# Example 2: Expired Statistics
tdsql> create table t1(a int, b int);
Query OK, 0 rows affected (0.26 sec)

tdsql> insert into t1
    -> with recursive nrows(n) as (
    -> select 1 union all
    -> select n+1 from nrows where n < 200
    -> )
    -> select n, n from nrows;
Query OK, 200 rows affected (0.02 sec)
Records: 200  Duplicates: 0  Warnings: 0

tdsql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

tdsql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

tdsql> insert into t1
    -> with recursive nrows(n) as (
    -> select 1 union all
    -> select n+1 from nrows where n < 10
    -> )
    -> select n, n from nrows;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

# Inserting 10 new rows of data is insufficient to trigger auto analyze, and the statistics are outdated.
tdsql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

tdsql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

tdsql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  210 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Additionally, consider adding or removing indexes for the tables involved in the query: adding indexes can accelerate the execution of filter conditions, alter the JOIN algorithm to improve efficiency, or provide ordering properties required by the query to avoid Filesort operations; index removal is generally suitable when the index interferes with the optimizer's execution plan selection. Note that adding or removing indexes may affect other queries, so caution is advised. Pre-validate the effect by marking indexes as VISIBLE/INVISIBLE. Example:
tdsql> create table t1(a int, b int);
Query OK, 0 rows affected (0.19 sec)

tdsql> insert into t1 with recursive nrows(n) as ( select 1 union all select n+1 from nrows where n < 200 ) select n, n from nrows;
Query OK, 200 rows affected (0.03 sec)
Records: 200  Duplicates: 0  Warnings: 0

tdsql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

tdsql> create table t2(a int, b int);
Query OK, 0 rows affected (0.20 sec)

tdsql> insert into t2 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

tdsql> analyze table t2;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t2 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

tdsql> explain format=tree select * from t1, t2 where t1.a = t2.a;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.a = t2.a)  (cost=67.84 rows=60)
    -> Table scan on t1  (cost=2.33 rows=200)
    -> Hash
        -> Table scan on t2  (cost=2.84 rows=3)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

tdsql> alter table t1 add key(a);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

tdsql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

# The join algorithm changed from hash join to bka.
tdsql> explain format=tree select * from t1, t2 where t1.a = t2.a;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Batched key access inner join  (cost=1.33 rows=3)
    -> Batch input rows
        -> Filter: (t2.a is not null)  (cost=2.84 rows=3)
            -> Table scan on t2  (cost=2.84 rows=3)
    -> Multi-range index lookup on t1 using a (a=t2.a)  (cost=0.28 rows=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

tdsql> alter table t1 alter index a invisible;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Index a is invisible, and the join algorithm reverts to hash join.
tdsql> explain format=tree select * from t1, t2 where t1.a = t2.a;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.a = t2.a)  (cost=67.84 rows=3)
    -> Table scan on t1  (cost=1.70 rows=200)
    -> Hash
        -> Table scan on t2  (cost=2.84 rows=3)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
Then, consider converting the tables involved in the query from regular tables to partitioned tables, or from partitioned tables to regular tables, based on business and query characteristics; alternatively, adjust the partition keys to fully leverage the query's filter conditions and scan less data. Example:
tdsql> create table t1(a int, b int);
Query OK, 0 rows affected (0.19 sec)

tdsql> insert into t1 with recursive nrows(n) as ( select 1 union all select n+1 from nrows where n < 200 ) select n, n from nrows;
Query OK, 200 rows affected (0.02 sec)
Records: 200  Duplicates: 0  Warnings: 0

tdsql> analyze table t1 update histogram on a;
+---------+-----------+----------+----------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                     |
+---------+-----------+----------+----------------------------------------------+
| test.t1 | histogram | status   | Histogram statistics created for column 'a'. |
+---------+-----------+----------+----------------------------------------------+
1 row in set (0.02 sec)

# Non-partitioned table scans the entire table
tdsql> explain select * from t1 where a <= 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

tdsql> drop table t1;
Query OK, 0 rows affected (0.11 sec)

tdsql> create table t1(a int, b int) partition by range(a) (
    -> partition p0 values less than (51),
    -> partition p1 values less than (101),
    -> partition p2 values less than (151),
    -> partition p3 values less than (maxvalue));
Query OK, 0 rows affected (0.22 sec)

tdsql> insert into t1 with recursive nrows(n) as ( select 1 union all select n+1 from nrows where n < 200 ) select n, n from nrows;
Query OK, 200 rows affected (0.04 sec)
Records: 200  Duplicates: 0  Warnings: 0

tdsql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

tdsql> analyze table t1 update histogram on a;
+---------+-----------+----------+----------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                     |
+---------+-----------+----------+----------------------------------------------+
| test.t1 | histogram | status   | Histogram statistics created for column 'a'. |
+---------+-----------+----------+----------------------------------------------+
1 row in set (0.01 sec)

# The partitioned table only needs to scan p0
tdsql> explain select * from t1 where a <= 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | p0         | ALL  | NULL          | NULL | NULL    | NULL |   50 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Modify Executor Parameters

To accelerate query execution without altering the execution plan, consider modifying executor-related parameters such as increasing join_buffer_size and temptable_max_ram. Note the scope of effect for these parameters to avoid impacting unexpected queries.

Intervene Optimizer

In most slow query scenarios, users still need to intervene in the optimizer process to enable queries to select the expected more efficient execution plan. The following methods can be used to intervene in the optimizer:
Controlling the exhaustiveness of the optimizer for JOIN orders: By setting the two parameters optimizer_prune_level and optimizer_search_depth, users can control the number of all candidate JOIN orders searched by the optimizer; if the slow query selects a poor JOIN order and it is discovered through Optimizer Trace that the optimizer did not search for the desired better JOIN order, consider adjusting these two parameters; note that a larger JOIN order search space implies higher optimizer overhead. For specific settings of these two parameters, refer to the MySQL documentation. Generally, these two values should remain at their default values;
Controlling whether to enable certain optimization operations via optimizer_switch or other system parameters: There may be optimization operations that can lead to better execution plans in some scenarios but worse ones in others. These operations typically correspond to a switch in optimizer_switch, such as the commonly used subquery_to_derived option, which controls whether to consider converting subqueries into derived tables before performing JOIN. This option is disabled by default, but enabling it can significantly improve query execution efficiency in certain queries. For more detailed optimizer_switch options, refer to the MySQL documentation; Example:
tdsql> create table t1(a int, b int);
Query OK, 0 rows affected (0.21 sec)

tdsql> create table t2(a int, b int);
Query OK, 0 rows affected (0.19 sec)

# In the default execution plan, the subquery is executed using the EXISTS method, which scans t2 multiple times.
tdsql> explain format=tree select * from t1 where t1.a > (select count(1) from t2 where t2.b = t1.b);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.a > (select #2))  (cost=2.61 rows=1)
    -> Table scan on t1  (cost=2.61 rows=1)
    -> Select #2 (subquery in condition; dependent)
        -> Aggregate: count(1)  (cost=2.84 rows=1)
            -> Filter: (t2.b = t1.b)  (cost=2.61 rows=1)
                -> Table scan on t2  (cost=2.61 rows=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

tdsql> set optimizer_switch='subquery_to_derived=on';
Query OK, 0 rows affected (0.00 sec)

# After enabling the subquery_to_derived feature, t2 only needs to be scanned once, with intermediate results stored in the temporary table derived_1_2, which is then joined with t1.
tdsql> explain format=tree select * from t1 where t1.a > (select count(1) from t2 where t2.b = t1.b);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.a > coalesce(derived_1_2.`count(1)`,0))  (cost=3.21 rows=1)
    -> Nested loop left join  (cost=3.21 rows=1)
        -> Table scan on t1  (cost=2.61 rows=1)
        -> Index lookup on derived_1_2 using <auto_key0> (b=t1.b)  (cost=0.70 rows=2)
            -> Materialize  (cost=10.07..10.07 rows=1)
                -> Table scan on <temporary>  (cost=5.12..5.12 rows=1)
                    -> Aggregate using temporary table  (cost=9.14..9.14 rows=1)
                        -> Table scan on t2  (cost=2.61 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Controlling candidate execution plan selection via hints: Similar to MySQL, TDSQL supports Optimizer Hints used in comment form and Index Hints used within queries. Users can employ these to control the optimizer's selection of indexes, JOIN orders and algorithms, subquery processing methods, and SEMI JOIN execution strategies. Additionally, TDSQL supports using hints to control the optimizer's selection of PARALLEL execution methods. We recommend using comment-style Optimizer Hints, which can be combined with TDSQL's Outline feature. This allows DBAs to automatically add hints to queries by creating Outline rules without modifying specific queries in the business; Example:
# In the example above, optimizer_switch can be specified via hint, taking effect only on the single statement.
tdsql> set optimizer_switch='subquery_to_derived=off';
Query OK, 0 rows affected (0.00 sec)

tdsql> explain format=tree select * from t1 where t1.a > (select count(1) from t2 where t2.b = t1.b);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.a > (select #2))  (cost=2.61 rows=1)
    -> Table scan on t1  (cost=2.61 rows=1)
    -> Select #2 (subquery in condition; dependent)
        -> Aggregate: count(1)  (cost=2.84 rows=1)
            -> Filter: (t2.b = t1.b)  (cost=2.61 rows=1)
                -> Table scan on t2  (cost=2.61 rows=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

tdsql> explain format=tree select/*+set_var(optimizer_switch='subquery_to_derived=on')*/ * from t1 where t1.a > (select count(1) from t2 where t2.b = t1.b);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.a > coalesce(derived_1_2.`count(1)`,0))  (cost=3.21 rows=1)
    -> Nested loop left join  (cost=3.21 rows=1)
        -> Table scan on t1  (cost=2.61 rows=1)
        -> Index lookup on derived_1_2 using <auto_key0> (b=t1.b)  (cost=0.70 rows=2)
            -> Materialize  (cost=10.07..10.07 rows=1)
                -> Table scan on <temporary>  (cost=5.12..5.12 rows=1)
                    -> Aggregate using temporary table  (cost=9.14..9.14 rows=1)
                        -> Table scan on t2  (cost=2.61 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

# Create an outline rule for the hint of this statement
tdsql> call dbms_admin.statement_outline_add_rule("test", "select/*+set_var(optimizer_switch='subquery_to_derived=on')*/ * from t1 where t1.a > (select count(1) from t2 where t2.b = t1.b)");
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

# Queries can now trigger the subquery_to_derived rewrite without requiring hints
tdsql> explain format=tree select * from t1 where t1.a > (select count(1) from t2 where t2.b = t1.b);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.a > coalesce(derived_1_2.`count(1)`,0))  (cost=3.21 rows=1)
    -> Nested loop left join  (cost=3.21 rows=1)
        -> Table scan on t1  (cost=2.61 rows=1)
        -> Index lookup on derived_1_2 using <auto_key0> (b=t1.b)  (cost=0.70 rows=2)
            -> Materialize  (cost=10.07..10.07 rows=1)
                -> Table scan on <temporary>  (cost=5.12..5.12 rows=1)
                    -> Aggregate using temporary table  (cost=9.14..9.14 rows=1)
                        -> Table scan on t2  (cost=2.61 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.02 sec)

# warnings will indicate that outline rules have been applied
tdsql> show warnings;
+-------+------+---------------------------------------------------------------------------+
| Level | Code | Message                                                                   |
+-------+------+---------------------------------------------------------------------------+
| Note  | 8579 | Statement outline rule 1 was applied, it may have changed the query plan. |
| Note  | 1276 | Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1     |
+-------+------+---------------------------------------------------------------------------+
2 rows in set (0.01 sec)
Essentially, all the above methods intervene in the optimizer across two dimensions:
Intervene in the optimizer's search space for candidate execution plans, expanding the scope to enable the optimizer to see the optimal candidate execution plan.
Intervene in the optimizer's evaluation of candidate execution plans, enabling the selection of the most optimal candidate execution plan.
이전 주제: Understand Execution Plan다음 주제: Preemptive DDL

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백