4.0 新特性前瞻：增强的 SQL Hint

作者： 张鱼小丸子 -PingCAP 原文来源：https://tidb.net/blog/f25b0701

前言

用户通过 SQL 查询告诉数据库想要得到什么结果，但得到这些结果的具体方式是由数据库自己决定的，这些方式就叫做执行计划，而优化器则是数据库中生成执行计划的模块。好的优化器需要在尽可能少的开销下找到尽可能好的执行计划，由于种种原因，如统计信息过期，独立性假设 / 均匀分布假设失效，未知的函数依赖等，要实现这两个在某种程度上互相矛盾的目标其实很难，就算是数据库学界对这个问题研究了这么多年也还是没有找到普适性的解法，所以我们经常看到由于优化器选错了执行计划，查询会有很高的延迟，或占用大量的系统资源。

针对这个问题，一方面我们需要通过提升统计信息的实时性以及详细程度减少优化器使用的假设，以及覆盖更多的查询场景等手段让优化器更智能，另一方面我们也需要认识到优化器总是会有选错执行计划的可能，而当这种情况发生时，我们需要有提供给用户的临时方案来强制优化器选择我们指定的更优执行计划，以便先把问题绕过，减少对业务的影响。

除了对用户提供控制执行计划的手段，在某些场景下，我们还需要让用户可以控制某个查询在执行过程中的行为，比如查询超过某个时间还未结束就取消，当然一般来说系统变量可以提供这种控制，但系统变量的生效范围至少是在 session 级别，用户如果只想控制这一条查询就需要额外在查询前后增加两个 set 命令，对业务来说不是很友好。

我们在 4.0 中新增了很多 SQL Hint 用来提高 DBA 在查询级别对优化器和执行引擎的控制力。

使用 SQL Hint

TiDB 的 SQL Hint 采用了和 MySQL Optimizer Hint 类似的 comment 语法，例如 /*+ HINT_NAME(arg1, arg2) */ ，需要跟在 SELECT , UPDATE 或 DELETE 关键字的后面。值得注意的是，MySQL 命令行客户端在 5.7.7 版本之前默认清除了 Optimizer Hints ，如果需要在这些早期版本的客户端中使用 SQL Hint 语法，需要在启动客户端时加上 --comments 选项，例如 mysql -h 127.0.0.1 -P 4000 -uroot --comments。

SQL Hint 和 EXPLAIN / EXPLAIN ANALYZE 语法是兼容的，可以通过这两个命令查看验证 SQL Hint 是否按照预期对查询产生了影响。如果 SQL Hint 部分包含语法错误，或者不适用于当前语句，查询会当作没有 SQL Hint 的情况运行，不会对 SQL Hint 部分报错，而是会记录 Warning ，用户可以在查询结束后通过 show warnings 命令查看具体信息。

SQL Hint 列表

在介绍 4.0 中具体的 SQL Hint 前，需要引入一个叫做查询块的概念：一条语句中每一个查询和子查询都对应着一个不同的查询块，每个查询块有自己对应的名字。以下面这条语句为例：

select * from (select * from t) t1, (select * from t) t2;

该查询语句有 3 个查询块，最外面一层 SELECT 所在的查询块的名字为 sel_1，两个 SELECT 子查询的名字依次为 sel_2 和 sel_3。其中数字序号根据 SELECT 出现的位置从左到右计数。如果分别用 DELETE 和 UPDATE 查询替代第一个 SELECT 查询，则对应的查询块名字分别为 del_1 和 upd_1。

引入查询块的一个目的是控制 SQL Hint 的生效范围，另一个目的是准确标识出查询里的每一个表（有可能表的名字或者别名相同），方便明确 SQL Hint 的参数指向。以如下查询为例：

select /*+ HASH_JOIN(@sel_1 t1@sel_1, t3) */ * from (select t1.a, t1.b from t t1, t t2 where t1.a = t2.a) t1, t t3 where t1.b = t3.b;

该 SQL Hint 在 sel_1 这个查询块中生效，参数分别为 sel_1 中的 t1 表（sel_2 中也有一个 t1 表）和 t3 表。

当语句是包含多层嵌套子查询的复杂语句时，识别出某个查询块的序号很可能会出错，这就引出了我们要介绍的第一个 TiDB 4.0 中的 SQL Hint: QB_NAME 。QB_NAME 是 Query Block Name 的缩写，用于为某个查询块指定新的名字，同时查询块原本默认的名字依然有效。例如：

select /*+ QB_NAME(QB1) */ * from (select * from t) t1, (select * from t) t2;

这条 SQL Hint 将最外层 SELECT 查询的 QB_NAME 设为 QB1，此时 QB1 和默认名称 sel_1 对于这个查询块来说都是有效的。值得注意的是，在上述例子中，如果指定的 QB_NAME 为 sel_2，并且不给原本 sel_2 对应的第二个查询块指定新的 QB_NAME，则第二个查询块的 QB_NAME 默认值 sel_2 会失效。

接下来我们将 4.0 TiDB 提供给用户的 SQL Hint 分五类分别介绍：

1. Table Hint

Table Hint 控制优化器对查询中涉及的表所选择的访问方式，包括如下四种：

• USE_INDEX：让优化器对指定的表使用限定的索引进行访问，等价于 MySQL 语法中的 use index() 。如果 USE_INDEX 中不指定索引部分的参数，表示让优化器使用全表扫描。

• IGNORE_INDEX：让优化器对指定的表忽略给出的索引，等价于 MySQL 语法中的 ignore index()。

• USE_INDEX_MERGE：让优化器通过 Index Merge 的方式来访问指定的表。Index Merge 是在 TiDB 4.0 中引入的对表的访问方式，简单来说，查询执行时可以先选用一张表的多个索引分别做过滤，再将各个索引返回的结果汇总起来。该 Hint 中索引列表为可选参数，若显式地指出索引列表，优化器会尝试在索引列表中选取索引来构建 Index Merge。若不给出索引列表，优化器会尝试在该表所有可用的索引中选取索引来构建 Index Merge。

• NO_INDEX_MERGE：让优化器对当前查询忽略所有可能的 Index Merge 访问方式。

2. Join Hint

Join Hint 控制优化器对实现连接算子所选择的算法，包括如下五种：

• SM_JOIN：提示优化器对指定表选用 Sort-Merge-Join 算法。这个算法通常会占用更少的内存，但执行时间会更久，一般建议在系统内存不足时使用。SM_JOIN 的别名是 TIDB_SMJ，在 3.0.x 及之前版本仅支持使用该别名，4.0 中同时支持使用这两种名称。

• HASH_JOIN：提示优化器对指定表选用 Hash-Join 算法。这个算法适用于连接的内表较小且连接命中率很高的场景。HASH_JOIN 的别名是 TIDB_HJ，在 3.0.x 及之前版本仅支持使用该别名，4.0 中同时支持使用这两种名称。

• INL_JOIN：提示优化器对指定表选用 Index-Nested-Loop-Join 算法。这个算法适用于连接的外表过滤后结果集较小，且内表上索引过滤性很好的场景。INL_JOIN 的别名是 TIDB_INLJ，在 3.0.x 及之前版本仅支持使用该别名，4.0 中同时支持使用这两种名称。

• INL_HASH_JOIN：提示优化器选用 Index-Nested-Loop-Hash-Join 算法。这个 Hint 的适用场景和 INL_JOIN 一致，两者的区别是 INL_JOIN 会在连接的内表上建哈希表，而 INL_HASH_JOIN 会在连接的外表上建哈希表，后者对于内存的使用是有固定上限的，而前者的内存使用取决于内表匹配的行数。

• INL_MERGE_JOIN：提示优化器使用 Index-Nested-Loop-Merge-Join 算法。这个 Hint 的适用场景和 INL_JOIN 一致，相比于 INL_JOIN 和 INL_HASH_JOIN 会更节省内存，但使用条件会更苛刻：join keys 中的内表列集合是内表使用的索引的前缀，或内表使用的索引是 join keys 中的内表列集合的前缀。

3. Aggregation Hint

Aggregation Hint 控制优化器对实现聚合算子所选择的算法，以及是否将聚合算子下推到 TiKV 的 Coprocessor 中执行，包括如下三种：

• HASH_AGG：提示优化器对查询中的聚合函数使用 Hash Aggregation 算法。这个算法多线程并发执行，执行速度较快，但会消耗较多内存。

• STREAM_AGG：提示优化器对查询中的聚合函数使用 Stream Aggregation 算法。这个算法要求输入按照 Group By 的列值有序，执行过程会占用更少的内存，但一般执行时间会更久。索引能够提供所需顺序，数据量太大或系统内存不足时，建议尝试使用。

4. General Optimizer Hint

这一类 Hint 用于控制优化器中的其他选择行为，目前包括如下两种：

• READ_FROM_STORAGE：4.0 TiDB 支持了对 TiFlash 存储引擎的访问，优化器会根据代价估算对某张表决定从 TiKV 还是 TiFlash 读取数据。该 Hint 可以强制优化器从指定的存储引擎来读取指定的表。

• USE_TOJA：这个 Hint 会稍微复杂一点，TOJA 是 To Join and Aggregation 的缩写。对于像

这样包含 in (uncorrelated-subquery) 的查询，目前的优化器会将该子查询转化为 Distinct Aggregation + InnerJoin ，这样可以方便应用后续对于 join 的一系列优化；但这个转化并不一定是一个好的选择，不过优化器目前并没有将这个决策纳入代价估算的模型里（新的 Cascades 优化器适合处理这种场景），而是通过一个系统变量控制是否启用这个转化。USE_TOJA 这个 SQL Hint 的作用就是在语句级别控制是否应用这个转化。

5. Executor Hint

Executor Hint 控制查询在执行过程中的行为，目前包括如下三种：

• MAX_EXECUTION_TIME：把查询的执行时间限制在指定的 N 毫秒以内，超时后服务器会终止这条语句的执行。

• MEMORY_QUOTA：限制查询执行时的内存使用，内存使用超过该限制时会根据当前设置的内存超限行为来打出一条 log 或者终止语句的执行。

• READ_FROM_REPLICA：4.0 TiDB 引入了 Follower Read 的功能，可以从数据一致的 TiKV follower 节点读取数据，降低 leader 节点的读写压力。该 Hint 用于在查询级别开启这项特性。

和其他模块的关系

上面提到过，对于 SQL Hint 提供的这些控制功能，有一部分也可以通过系统变量实现，比如 tidb_enable_index_merge, tidb_opt_insubq_to_join_and_agg, max_execution_time, tidb_mem_quota_query, tidb_replica_read 等，值得一提的是，当 SQL Hint 和系统变量发生冲突时，SQL Hint 拥有更高的优先级。

此外，SQL Hint 可以被看作是 SQL Plan Management 功能的基础，两者结合起来可以让 DBA 在业务不修改代码的情况下实现对优化器和执行引擎的语句级别控制。

总结

本文介绍了 4.0 TiDB 中包含的 SQL Hint 功能，旨在提高用户对优化器和执行引擎的控制力，进而增强数据库系统的稳定性。当然，上面介绍的 SQL Hint 功能也并不全面，并不足以覆盖优化器的各个方面，我们也在开发更多的 SQL Hint（比如 Join Order Hint），欢迎大家尝鲜使用 4.0 TiDB 并提出反馈意见。