·调整查询涉及的表的 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: 0tdsql> 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: 0tdsql> 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: 0tdsql> 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: 0tdsql> 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: 0tdsql> analyze table t1;+———+———+———-+———-+| Table | Op | Msg_type | Msg_text |+———+———+———-+———-+| test.t1 | analyze | status | OK |+———+———+———-+———-+1 row in set (0.01 sec)# join 算法从 hash join 变更成了 bkatdsql> 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 jointdsql> 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: 0tdsql> 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: 0tdsql> 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)# 分区表只需要扫 p0tdsql> 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 做 jointdsql> 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)
以上方式本质上都是在两个维度上对优化器做干预：
·干预优化器搜索候选执行计划的空间，使得优化器能看到最优的候选执行计划。
·干预优化器评估候选执行计划，使得最优的候选执行计划能被选中。