TiDB SQL优化（二）

最新推荐文章于 2023-04-29 12:18:11 发布

路漫漫路漫漫

最新推荐文章于 2023-04-29 12:18:11 发布

阅读量826

点赞数

分类专栏： Mysql 文章标签： mysql

原文链接：https://blog.csdn.net/phoenix9311/article/details/110152486

版权

Mysql 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

1.物理优化

物理优化是基于代价的优化，为上一阶段产生的逻辑执行计划制定物理执行计划。这一阶段中，优化器会为逻辑执行计划中的每个算子选择具体的物理实现。逻辑算子的不同物理实现有着不同的时间复杂度、资源消耗和物理属性等。在这个过程中，优化器会根据数据的统计信息来确定不同物理实现的代价，并选择整体代价最小的物理执行计划。

1.1索引的选择

从存储层读取数据是 SQL 计算过程中最为耗时的部分之一，TiDB 目前支持从不同的存储和不同的索引中读取数据，索引选择得是否合理将很大程度上决定一个查询的运行速度。

本章节将介绍 TiDB 如何选择索引去读入数据，以及相关的一些控制索引选择的方式。

读表

在介绍索引的选择之前，首先要了解 TiDB 有哪些读表的方式，这些方式的触发条件是什么，不同方式有什么区别，各有什么优劣。

读表算子

读表算子	触发条件	适用场景	说明
PointGet/BatchPointGet	读表的范围是一个或多个单点范围	任何场景	如果能被触发，通常被认为是最快的算子，因为其直接调用 kvget 的接口进行计算，不走 coprocessor
TableReader	无	任何场景	从 TiKV 端直接扫描表数据，一般被认为是效率最低的算子，除非在 _tidb_rowid 这一列上存在范围查询，或者无其他可以选择的读表算子时，才会选择这个算子
TableReader	表在 TiFlash 节点上存在副本	需要读取的列比较少，但是需要计算的行很多	TiFlash 是列式存储，如果需要对少量的列和大量的行进行计算，一般会选择这个算子
IndexReader	表有一个或多个索引，且计算所需的列被包含在索引里	存在较小的索引上的范围查询，或者对索引列有顺序需求的时候	当存在多个索引的时候，会根据估算代价选择合理的索引
IndexLookupReader	表有一个或多个索引，且计算所需的列不完全被包含在索引里	同 IndexReader	因为计算列不完全被包含在索引里，所以读完索引后需要回表，这里会比 IndexReader 多一些开销

注意：

TableReader 是基于 _tidb_rowid 的索引，TiFlash 是列存索引，所以索引的选择即是读表算子的选择。

索引的选择

TiDB 在选择索引时，会基于每个读表算子的代价估算，在此基础上提供了启发式规则 "Skyline-Pruning"，以降低错误估算导致选错索引的概率。

Skyline-Pruning

Skyline-Pruning 是一个针对索引的启发式过滤规则，评判一个索引的好坏需要从以下三个维度进行衡量：

选择该索引读表时，是否需要回表（即该索引生成的计划是 IndexReader 还是 IndexLookupReader）。不用回表的索引在这个维度上优于需要回表的索引。
选择该索引是否能满足一定的顺序。因为索引的读取可以保证某些列集合的顺序，所以满足查询要求顺序的索引在这个维度上优于不满足的索引。
索引的列涵盖了多少访问条件。“访问条件”指的是可以转化为某列范围的 where 条件，如果某个索引的列集合涵盖的访问条件越多，那么它在这个维度上更优。

对于这三种维度，如果某个索引 idx_a 在 三个维度上都不比 idx_b 差，且 有一个维度比 idx_b 好，那么就会优先选择 idx_a 。

基于代价选择

在使用 Skyline-Pruning 规则排除了不合适的索引之后，索引的选择完全基于代价估算，读表的代价估算需要考虑以下几个方面：

索引的每行数据在存储层的平均长度。
索引生成的查询范围的行数量。
索引的回表代价。
索引查询时的范围数量。

根据这些因子和代价模型，优化器会选择一个代价最低的索引进行读表。

代价选择调优的常见问题

估算的行数量不准确？

一般是统计信息过期或者准确度不够造成的，可以重新执行 analyze table 或者修改 analyze table 的参数。
统计信息准确，为什么读 TiFlash 更快，而优化器选择了 TiKV？

目前区别 TiFlash 和 TiKV 的代价模型还比较粗糙，可以调小 tidb_opt_seek_factor 的值，让优化器倾向于选择 TiFlash。
统计信息准确，某个索引要回表，但是它比另一个不用回表的索引实际执行更快，为什么选择了不用回表的索引？

碰到这种情况，可能是代价估算时对于回表的代价计算得过大，可以调小 tidb_opt_network_factor ，降低回表的代价。

控制索引的选择

通过 Optimizer Hints 可以实现单条查询对索引选择的控制。

USE_INDEX / IGNORE_INDEX 可以强制优化器使用/不使用某些索引。
READ_FROM_STORAGE 可以强制优化器对于某些表选择 TiKV/TiFlash 的存储引擎进行查询。

1.2错误索引的解决方案

在观察到某个查询的执行速度达不到预期时，可能是它的索引使用有误，这时就需要通过一些手段来解决。通常可以先使用表的健康度信息来查看统计信息的健康度。根据健康度可以分为以下两种情况处理。

健康度较低

这意味着距离 TiDB 上次 ANALYZE 很久了。这时可以先使用 ANALYZE 命令对统计信息进行更新。更新之后如果索引的使用上还是错误的，可以查看下一小节。

健康度接近 100%

这时意味着刚刚结束 ANALYZE 命令或者结束后不久。这时可能和 TiDB 对行数的估算逻辑有关。

对于等值查询，错误索引可能是由 Count-Min Sketch 引起的。这时可以先检查是不是这种特殊情况，然后进行对应的处理。

如果经过检查发现不是上面的可能情况，可以使用 Optimizer Hints 中提到的 USE_INDEX 或者 use index 来强制选择索引。同时也可以使用执行计划管理中提到的方式来非侵入地更改查询的行为。

其他情况

除去上述情况外，也存在因为数据的更新导致现有所有索引都不再适合的情况。这时就需要对条件和数据分布进行分析，查看是否有新的索引可以加快查询速度，然后使用 ADD INDEX 命令增加新的索引。

1.3Distinct 优化

简单 DISTINCT

通常简单的 DISTINCT 会被优化成 GROUP BY 来执行。例如：

聚合函数 DISTINCT

通常来说，带有 DISTINCT 的聚合函数会单线程的在 TiDB 侧执行。使用系统变量 tidb_opt_distinct_agg_push_down 或者 TiDB 的配置项 distinct-agg-push-down 控制优化器是否执行带有 DISTINCT 的聚合函数（比如 select count(distinct a) from t ）下推到 Coprocessor 的优化操作。

在以下示例中， tidb_opt_distinct_agg_push_down 开启前，TiDB 需要从 TiKV 读取所有数据，并在 TiDB 侧执行 disctinct 。 tidb_opt_distinct_agg_push_down 开启后， distinct a 被下推到了 Coprocessor，在 HashAgg_5 里新增了一个 group by 列 test.t.a 。

mysql > set session tidb_opt_distinct_agg_push_down = 1 ;

Query OK , 0 rows affected ( 0.00 sec )

2.控制执行计划

2.1 Optimizer Hints

iDB 支持 Optimizer Hints 语法，它基于 MySQL 5.7 中介绍的类似 comment 的语法，例如 /*+ HINT_NAME(t1, t2) */ 。当 TiDB 优化器选择的不是最优查询计划时，建议使用 Optimizer Hints。

注意：

MySQL 命令行客户端在 5.7.7 版本之前默认清除了 Optimizer Hints。如果需要在这些早期版本的客户端中使用 Hint 语法，需要在启动客户端时加上 --comments 选项，例如 mysql -h 127.0.0.1 -P 4000 -uroot --comments 。

语法

Optimizer Hints 不区分大小写，通过 /*+ ... */ 注释的形式跟在 SELECT 、 UPDATE 或 DELETE 关键字的后面。 INSERT 关键字后不支持 Optimizer Hints。

多个不同的 Hint 之间需用逗号隔开，例如：

SELECT /*+ USE_INDEX(t1, idx1), HASH_AGG(), HASH_JOIN(t1) */ count ( * ) FROM t t1 , t t2 WHERE t1 . a = t2 . b ;

可以通过 Explain / Explain Analyze 语句的输出，来查看 Optimizer Hints 对查询执行计划的影响。

如果 Optimizer Hints 包含语法错误或不完整，查询语句不会报错，而是按照没有 Optimizer Hints 的情况执行。如果 Hint 不适用于当前语句，TiDB 会返回 Warning，用户可以在查询结束后通过 Show Warnings 命令查看具体信息。

注意：

如果注释不是跟在指定的关键字后，会被当作是普通的 MySQL comment，注释不会生效，且不会上报 warning。

TiDB 目前支持的 Optimizer Hints 根据生效范围的不同可以划分为两类：第一类是在查询块范围生效的 Hint，例如 /*+ HASH_AGG() */ ；第二类是在整个查询范围生效的 Hint，例如 /*+ MEMORY_QUOTA(1024 MB)*/ 。

每条语句中每一个查询和子查询都对应着一个不同的查询块，每个查询块有自己对应的名字。以下面这条语句为例：

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 。

查询块范围生效的 Hint

这类 Hint 可以跟在查询语句中任意 SELECT 、 UPDATE 或 DELETE 关键字的后面。通过在 Hint 中使用查询块名字可以控制 Hint 的生效范围，以及准确标识查询中的每一个表（有可能表的名字或者别名相同），方便明确 Hint 的参数指向。若不显式地在 Hint 中指定查询块，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 ;

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

如上例所述，在 Hint 中使用查询块名字的方式有两种：第一种是作为 Hint 的第一个参数，与其他参数用空格隔开。除 QB_NAME 外，本节所列的所有 Hint 除自身明确列出的参数外都有一个隐藏的可选参数 @QB_NAME ，通过使用这个参数可以指定该 Hint 的生效范围；第二种在 Hint 中使用查询块名字的方式是在参数中的某一个表名后面加 @QB_NAME ，用以明确指出该参数是哪个查询块中的表。

注意：

Hint 声明的位置必须在指定生效的查询块之中或之前，不能是在之后的查询块中，否则无法生效。

QB_NAME

当查询语句是包含多层嵌套子查询的复杂语句时，识别某个查询块的序号和名字很可能会出错，Hint QB_NAME 可以方便我们使用查询块。 QB_NAME 是 Query Block Name 的缩写，用于为某个查询块指定新的名字，同时查询块原本默认的名字依然有效。例如：

SELECT /*+ QB_NAME(QB1) */ * FROM ( SELECT * FROM t ) t1 , ( SELECT * FROM t ) t2 ;

这条 Hint 将最外层 SELECT 查询块的命名为 QB1 ，此时 QB1 和默认名称 sel_1 对于这个查询块来说都是有效的。

注意：

上述例子中，如果指定的 QB_NAME 为 sel_2 ，并且不给原本 sel_2 对应的第二个查询块指定新的 QB_NAME ，则第二个查询块的默认名字 sel_2 会失效。

MERGE_JOIN(t1_name [, tl_name ...])

MERGE_JOIN(t1_name [, tl_name ...]) 提示优化器对指定表使用 Sort Merge Join 算法。这个算法通常会占用更少的内存，但执行时间会更久。当数据量太大，或系统内存不足时，建议尝试使用。例如：

SELECT /*+ MERGE_JOIN(t1, t2) */ * FROM t1，t2 WHERE t1 . id = t2 . id ;

注意：

MERGE_JOIN 的别名是 TIDB_SMJ ，在 3.0.x 及之前版本仅支持使用该别名；之后的版本同时支持使用这两种名称，但推荐使用 MERGE_JOIN 。

INL_JOIN(t1_name [, tl_name ...])

INL_JOIN(t1_name [, tl_name ...]) 提示优化器对指定表使用 Index Nested Loop Join 算法。这个算法可能会在某些场景更快，消耗更少系统资源，有的场景会更慢，消耗更多系统资源。对于外表经过 WHERE 条件过滤后结果集较小（小于 1 万行）的场景，可以尝试使用。例如：

SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1，t2 WHERE t1 . id = t2 . id ;

INL_JOIN() 中的参数是建立查询计划时内表的候选表，比如 INL_JOIN(t1) 只会考虑使用 t1 作为内表构建查询计划。表如果指定了别名，就只能使用表的别名作为 INL_JOIN() 的参数；如果没有指定别名，则用表的本名作为其参数。比如在 SELECT /*+ INL_JOIN(t1) */ * FROM t t1, t t2 WHERE t1.a = t2.b; 中， INL_JOIN() 的参数只能使用 t 的别名 t1 或 t2，不能用 t。

注意：

INL_JOIN 的别名是 TIDB_INLJ ，在 3.0.x 及之前版本仅支持使用该别名；之后的版本同时支持使用这两种名称，但推荐使用 INL_JOIN 。

INL_HASH_JOIN

INL_HASH_JOIN(t1_name [, tl_name]) 提示优化器使用 Index Nested Loop Hash Join 算法。该算法与 Index Nested Loop Join 使用条件完全一样，两者的区别是 INL_JOIN 会在连接的内表上建哈希表，而 INL_HASH_JOIN 会在连接的外表上建哈希表，后者对于内存的使用是有固定上限的，而前者使用的内存使用取决于内表匹配到的行数。

INL_MERGE_JOIN

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

HASH_JOIN(t1_name [, tl_name ...])

HASH_JOIN(t1_name [, tl_name ...]) 提示优化器对指定表使用 Hash Join 算法。这个算法多线程并发执行，执行速度较快，但会消耗较多内存。例如：

SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1，t2 WHERE t1 . id = t2 . id ;

注意：

HASH_JOIN 的别名是 TIDB_HJ ，在 3.0.x 及之前版本仅支持使用该别名；之后的版本同时支持使用这两种名称，推荐使用 HASH_JOIN 。

HASH_AGG()

HASH_AGG() 提示优化器对指定查询块中所有聚合函数使用 Hash Aggregation 算法。这个算法多线程并发执行，执行速度较快，但会消耗较多内存。例如：

SELECT /*+ HASH_AGG() */ count ( * ) FROM t1，t2 WHERE t1 . a > 10 GROUP BY t1 . id ;

STREAM_AGG()

STREAM_AGG() 提示优化器对指定查询块中所有聚合函数使用 Stream Aggregation 算法。这个算法通常会占用更少的内存，但执行时间会更久。数据量太大，或系统内存不足时，建议尝试使用。例如：

SELECT /*+ STREAM_AGG() */ count ( * ) FROM t1，t2 WHERE t1 . a > 10 GROUP BY t1 . id ;

USE_INDEX(t1_name, idx1_name [, idx2_name ...])

USE_INDEX(t1_name, idx1_name [, idx2_name ...]) 提示优化器对指定表仅使用给出的索引。

下面例子的效果等价于 SELECT * FROM t t1 use index(idx1, idx2); ：

SELECT /*+ USE_INDEX(t1, idx1, idx2) */ * FROM t1 ;

注意：

当该 Hint 中只指定表名，不指定索引名时，表示不考虑使用任何索引，而是选择全表扫

IGNORE_INDEX(t1_name, idx1_name [, idx2_name ...])

IGNORE_INDEX(t1_name, idx1_name [, idx2_name ...]) 提示优化器对指定表忽略给出的索引。

下面例子的效果等价于 SELECT * FROM t t1 ignore index(idx1, idx2); ：

SELECT /*+ IGNORE_INDEX(t1, idx1, idx2) */ * FROM t t1 ;

AGG_TO_COP()

AGG_TO_COP() 提示优化器将指定查询块中的聚合函数下推到 coprocessor。如果优化器没有下推某些适合下推的聚合函数，建议尝试使用。例如：

SELECT /*+ AGG_TO_COP() */ sum ( t1 . a ) FROM t t1 ;

READ_FROM_STORAGE(TIFLASH[t1_name [, tl_name ...]], TIKV[t2_name [, tl_name ...]])

READ_FROM_STORAGE(TIFLASH[t1_name [, tl_name ...]], TIKV[t2_name [, tl_name ...]]) 提示优化器从指定的存储引擎来读取指定的表，目前支持的存储引擎参数有 TIKV 和 TIFLASH 。例如：

SELECT /*+ READ_FROM_STORAGE(TIFLASH[t1], TIKV[t2]) */ t1 . a FROM t t1 , t t2 WHERE t1 . a = t2 . a ;

注意：

如果需要提示优化器使用的表不在同一个数据库内，需要显式指定数据库名。例如 SELECT /*+ READ_FROM_STORAGE(TIFLASH[test1.t1,test2.t2]) */ t1.a FROM test1.t t1, test2.t t2 WHERE t1.a = t2.a; 。

IGNORE_INDEX(t1_name, idx1_name [, idx2_name ...])

IGNORE_INDEX(t1_name, idx1_name [, idx2_name ...]) 提示优化器对指定表忽略给出的索引。

下面例子的效果等价于 SELECT * FROM t t1 ignore index(idx1, idx2); ：

SELECT /*+ IGNORE_INDEX(t1, idx1, idx2) */ * FROM t t1 ;

AGG_TO_COP()

AGG_TO_COP() 提示优化器将指定查询块中的聚合函数下推到 coprocessor。如果优化器没有下推某些适合下推的聚合函数，建议尝试使用。例如：

SELECT /*+ AGG_TO_COP() */ sum ( t1 . a ) FROM t t1 ;

READ_FROM_STORAGE(TIFLASH[t1_name [, tl_name ...]], TIKV[t2_name [, tl_name ...]])

SELECT /*+ READ_FROM_STORAGE(TIFLASH[t1], TIKV[t2]) */ t1 . a FROM t t1 , t t2 WHERE t1 . a = t2 . a ;

注意：

如果需要提示优化器使用的表不在同一个数据库内，需要显式指定数据库名。例如 SELECT /*+ READ_FROM_STORAGE(TIFLASH[test1.t1,test2.t2]) */ t1.a FROM test1.t t1, test2.t t2 WHERE t1.a = t2.a; 。

USE_INDEX_MERGE(t1_name, idx1_name [, idx2_name ...])

USE_INDEX_MERGE(t1_name, idx1_name [, idx2_name ...]) 提示优化器通过 index merge 的方式来访问指定的表，其中索引列表为可选参数。若显式地指出索引列表，会尝试在索引列表中选取索引来构建 index merge。若不给出索引列表，会尝试在所有可用的索引中选取索引来构建 index merge。例如：

SELECT /*+ USE_INDEX_MERGE(t1, idx_a, idx_b, idx_c) */ * FROM t1 WHERE t1 . a > 10 OR t1 . b > 10 ;

当对同一张表有多个 USE_INDEX_MERGE Hint 时，优化器会从这些 Hint 指定的索引列表的并集中尝试选取索引。

注意：

USE_INDEX_MERGE 的参数是索引名，而不是列名。对于主键索引，索引名为 primary 。

目前该 Hint 生效的条件较为苛刻，包括：

如果查询有除了全表扫以外的单索引扫描方式可以选择，优化器不会选择 index merge；
如果查询在显式事务里，且该条查询之前的语句已经涉及写入，优化器不会选择 index merge；

查询范围生效的 Hint

这类 Hint 只能跟在语句中 第一个 SELECT 、 UPDATE 或 DELETE 关键字的后面，等同于在当前这条查询运行时对指定的系统变量进行修改，其优先级高于现有系统变量的值。

注意：

这类 Hint 虽然也有隐藏的可选变量 @QB_NAME ，但就算指定了该值，Hint 还是会在整个查询范围生效。

NO_INDEX_MERGE()

NO_INDEX_MERGE() 会关闭优化器的 index merge 功能。

下面的例子不会使用 index merge：

SELECT /*+ NO_INDEX_MERGE() */ * FROM t WHERE t . a > 0 or t . b > 0 ;

除了 Hint 外，系统变量 tidb_enable_index_merge 也能决定是否开启该功能。

注意：

NO_INDEX_MERGE 优先级高于 USE_INDEX_MERGE ，当这两类 Hint 同时存在时， USE_INDEX_MERGE 不会生效。

USE_TOJA(boolean_value)

参数 boolean_value 可以是 TRUE 或者 FALSE 。 USE_TOJA(TRUE) 会开启优化器尝试将 in (subquery) 条件转换为 join 和 aggregation 的功能。相对地， USE_TOJA(FALSE) 会关闭该功能。

下面的例子会将 in (SELECT t2.a FROM t2) subq 转换为等价的 join 和 aggregation：

SELECT /*+ USE_TOJA(TRUE) */ t1 . a , t1 . b FROM t1 WHERE t1 . a in ( SELECT t2 . a FROM t2 ) subq ;

除了 Hint 外，系统变量 tidb_opt_insubq_to_join_and_agg 也能决定是否开启该功能。

MAX_EXECUTION_TIME(N)

MAX_EXECUTION_TIME(N) 把语句的执行时间限制在 N 毫秒以内，超时后服务器会终止这条语句的执行。

下面的 Hint 设置了 1000 毫秒（即 1 秒）超时：

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 inner join t2 WHERE t1 . id = t2 . id ;

除了 Hint 之外，系统变量 global.max_execution_time 也能对语句执行时间进行限制。

MEMORY_QUOTA(N)

MEMORY_QUOTA(N) 用于限制语句执行时的内存使用。该 Hint 支持 MB 和 GB 两种单位。内存使用超过该限制时会根据当前设置的内存超限行为来打出一条 log 或者终止语句的执行。

下面的 Hint 设置了 1024 MB 的内存限制：

SELECT /*+ MEMORY_QUOTA(1024 MB) */ * FROM t ;

除了 Hint 外，系统变量 tidb_mem_quota_query 也能限制语句执行的内存使用。

READ_CONSISTENT_REPLICA()

READ_CONSISTENT_REPLICA() 会开启从数据一致的 TiKV follower 节点读取数据的特性。

下面的例子会从 follower 节点读取数据：

SELECT /*+ READ_CONSISTENT_REPLICA() */ * FROM t ;

除了 Hint 外，环境变量 tidb_replica_read 设为 'follower' 或者 'leader' 也能决定是否开启该特性。

IGNORE_PLAN_CACHE()

IGNORE_PLAN_CACHE() 提示优化器在处理当前 prepare 语句时不使用 plan cache。

该 Hint 用于在 prepare-plan-cache 开启的场景下临时对某类查询禁用 plan cache。

以下示例强制该 prepare 语句不使用 plan cache：

prepare stmt FROM 'SELECT /*+ IGNORE_PLAN_CACHE() */ * FROM t WHERE t.id = ?' ;

路漫漫路漫漫

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
TiDB SQL优化（二）

1.物理优化物理优化是基于代价的优化，为上一阶段产生的逻辑执行计划制定物理执行计划。这一阶段中，优化器会为逻辑执行计划中的每个算子选择具体的物理实现。逻辑算子的不同物理实现有着不同的时间复杂度、资源消耗和物理属性等。在这个过程中，优化器会根据数据的统计信息来确定不同物理实现的代价，并选择整体代价最小的物理执行计划。   ...
复制链接

扫一扫

专栏目录