产品动态
产品简介
产品概述
应用场景
产品架构
实例类型
兼容性说明
使用规范建议
自研内核
内核概述
内核版本更新动态
功能类特性
性能类特性
产品计费
计费概述
购买方式
价格详情
续费说明
欠费说明
退费说明
快速入门
创建实例
连接实例
用户指南
数据迁移
数据同步/订阅
实例管理
参数配置
账号管理
安全组
备份与恢复
数据库审计
标签管理
实践教程
Online DDL 的技术演进与使用实践
锁机制解析与问题排查实践
数据智能调度及相关性能优化实践
TDSQL Boundless 选型指南与实践教程
开发指南
开发指南（MySQL 兼容模式）
开发指南（HBase 兼容模式）
性能调优
性能调优概述
SQL 调优
DDL 调优
性能白皮书
性能概述
TPC-C 测试
Sysbench 测试
API 文档
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
通用参考
系统原理
SQL 参考
数据库参数说明
TPC-H 基准测试数据模型参考
错误码信息
安全与合规
常见问题
服务协议
服务等级协议
服务条款
隐私政策
数据处理和安全协议
联系我们
词汇表
SQL 调优

PDF
聚焦模式
字号
最后更新时间： 2026-03-23 16:01:10
在使用 理解执行计划 章节中的工具找出真正的慢查询以及它具体慢在哪里后，用户可能需要采取相应的措施来优化该查询的执行效率，这些措施大致可以分为三类：
调整查询涉及的表的 SCHEMA。
修改执行器相关参数。
干预优化器对执行计划的选择。
调整 SCHEMA
当优化器选中的执行计划不优时，用户可以首先检查执行计划中各个部分的行数估算和真实值是否有很大的偏差，如果是则进一步检查查询涉及的列是否存在相应的统计信息，这些统计信息是否已经过期不能体现最新的数据分布，或者统计信息没有过期但准确度本身就很低；这些情况下需要重新收集统计信息，或者调大统计信息采样率相关的参数再收集统计信息，比如 sample_sst_blocks，但需要注意调大采样率会导致 ANALYZE 语句开销更高。示例：
# 示例 1：统计信息缺失
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)
﻿
# a 列没有合适的统计信息，估算行数 1 有偏差
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)
﻿
# 对 a 列搜集直方图后，估算行数 2 准确
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)
﻿
# 示例 2：统计信息过期
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
﻿
# 新插入 10 条数据不足以触发 auto analyze，统计信息过期
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)
此外，可以考虑为查询涉及的表增加或者删除索引：增加索引可以用于加速查询中的过滤条件执行，或者改变 JOIN 的算法提升效率，或者提供查询需要的顺序属性避免 Filesort 操作；删除索引一般适用于该索引干扰了优化器对执行计划的选择这种情况。注意，增删索引可能会影响到其他的查询，需要谨慎操作，可以通过将索引标记为 VISIBLE / INVISIBLE 预先验证效果。示例：
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)
﻿
# join 算法从 hash join 变更成了 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
﻿
# 索引 a 不可见，join 算法变回 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)
然后，还可以考虑的是根据业务和查询特点，将查询涉及的表从普通表调整为分区表，或者从分区表调整为普通表，或者考虑调整分区键，目的都是为了充分利用查询的过滤条件，扫描更少的数据。示例：
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)
﻿
# 非分区表扫描全表
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)
﻿
# 分区表只需要扫 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)
修改执行器参数
在不变更执行计划的前提下，如果想要加速查询执行，可以考虑修改部分执行器相关的参数，比如适当调大 join_buffer_size 和 temptable_max_ram 等参数。注意这些参数的生效范围，避免对预期外的查询产生影响。
干预优化器
大部分慢查询情况下，用户需要的还是干预优化器过程使得查询可以选择预期中更高效的执行计划，可以用以下几种方式对优化器做干预：
控制优化器对 JOIN 顺序的穷举度：通过设置 optimizer_prune_level 和 optimizer_search_depth 这两个参数，用户可以控制优化器搜索的所有候选 JOIN 顺序的数量；如果慢查询选择的 JOIN 顺序较差，并且通过 Optimizer Trace 发现优化器并没有搜索到想要的更优 JOIN 顺序，则可以考虑调整这两个参数；注意更大的 JOIN 顺序搜索空间意味着更大的优化器开销，具体如何设置这两个参数可以参考 MySQL 文档，一般情况下这两个值保持默认值就好；
通过 optimizer_switch 或者其他系统参数控制是否开启某些优化操作：会存在一些优化操作，它们在某些情况下能导致更好的执行计划结果，但某些情况下也会导致执行计划变差，通常这些操作会对应 optimizer_switch 中的一个开关，常用的比如 subquery_to_derived 选项，它控制是否考虑将子查询转化为 derived table 再做 JOIN，这个操作默认是关闭的，但在某些查询下开启它能大幅度提升查询执行效率；更详细的 optimizer_switch 选项可以参考 MySQL 文档；示例：
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)
﻿
# 默认执行计划里，子查询是用 EXISTS 方式执行，会扫描 t2 多次
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)
﻿
# 打开 subquery_to_derived 功能后，t2 只需要扫描一次，中间结果用临时表 derived_1_2 保存，它和 t1 做 join
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)
通过 hint 干预候选执行计划的选择：TDSQL 和 MySQL 一样支持以注释形式使用的 Optimizer Hints 以及在查询中使用的 Index Hints ，用户可以通过它们控制优化器对索引的选择，对 JOIN 顺序和算法的选择，对子查询处理方式的选择，对 SEMI JOIN 的执行策略的选择等；此外，TDSQL 还支持用 Hint 控制优化器对 PARALLEL 执行方式的选择；推荐使用注释形式的 Optimizer Hints，它们可以搭配 TDSQL 的 Outline 功能一起使用，使得 DBA 可以通过添加 Outline 规则对查询自动添加 hint，而不用更改业务中的具体查询；示例：
# 上面示例里的 optimizer_switch 可以通过 hint 指定，只在单条语句上生效
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)
﻿
# 对这条语句的 hint 创建 outline 规则
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)
﻿
# 现在查询不再需要 hint 也能触发 subquery_to_derived 改写
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 会告知有 outline 规则被应用了
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)
以上方式本质上都是在两个维度上对优化器做干预：
干预优化器搜索候选执行计划的空间，使得优化器能看到最优的候选执行计划。
干预优化器评估候选执行计划，使得最优的候选执行计划能被选中。
帮助和支持

本页内容是否解决了您的问题？
您也可以联系销售或提交工单以寻求帮助。
填写满意度调查问卷，共创更好文档体验。
文档反馈
tencent cloud

TDSQL Boundless

SQL 调优

调整 SCHEMA

修改执行器参数

干预优化器

帮助和支持
