数据库系列之TiDB的SQL优化流程

最新推荐文章于 2023-04-30 15:04:41 发布
最新推荐文章于 2023-04-30 15:04:41 发布
阅读量1.3k 收藏 4
点赞数 1
分类专栏： 数据库系列 # tidb 文章标签： 数据库 tidb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/solihawk/article/details/119270226
版权

上一篇介绍到TiDB中的执行计划，本文将继续介绍TiDB中的SQL优化流程，从逻辑优化和物理优化两个阶段了解TiDB优化器的实现逻辑。

1、SQL优化流程

TiDB中的SQL优化分为逻辑优化和物理优化两个阶段：在逻辑优化过程中，依次遍历内部定义实现的优化规则，不断地调整SQL的逻辑执行计划，对查询做一些逻辑上的等价变化，称为基于规则的优化RBO（rule based optimization）；物理优化则是将改写后逻辑执行计划变成可以执行的物理执行计划，需要用到一些统计信息，这一过程会决定执行操作的具体方法，比如用什么索引读表，用什么算法做Join操作等，称为基于代价的优化CBO（cost based optimization）。

1.1 逻辑优化
1.1.1 基本逻辑算子介绍

TiDB 中的逻辑算子主要有以下几个：

  1. DataSource：数据源，如SQL中的from t中的t
  2. Selection：代表了相应的过滤条件，如SQL中where谓词语句的where a = 5
  3. Projection：投影操作，也用于表达式计算， 如select c, a + b from t里面的c和a + b就是投影和表达式计算操作
  4. Join：两个表的连接操作，如select t1.b, t2.c from t1 join t2 on t1.a = t2.a 中的t1 join t2 on t1.a = t2.a就是两个表t1和t2的连接操作。Join有内连接、左连接、右连接等多种连接方式。

Selection，Projection，Join（简称SPJ）是3种最基本的算子。

1.1.2 常见逻辑优化规则

逻辑优化是基于规则的优化，通过对输入的逻辑执行计划按顺序应用优化规则，使整个逻辑执行计划变得更加高效。这些常用逻辑优化规则包括：

在这里插入图片描述

1）列裁剪

列裁剪的基本思想在于：对于算子中实际用不上的列，优化器在优化的过程中删除这些列以减少I/O资源的占用。假设表t里面有a b c d四列，执行如下语句：

select a from t where b > 5

在该查询的过程中，t表实际上只有a, b两列会被用到，c,d的数据没有被使用。在该语句的查询计划中Selection算子会用到b列、接下去的DataSource算子会用到a, b两列，而剩下的c, d列在读数据时不需要将它们读进来。

出于上述考量，TiDB会在逻辑优化阶段进行自上而下的扫描，裁剪不需要的列，减少资源浪费。该扫描过程称作 “列裁剪”，对应逻辑优化规则中的columnPruner。

2）分区裁剪

分区裁剪是针对分区表的优化，通过分析查询语句中的过滤条件，只选择可能满足条件的分区，进而减少计算的数据量。

  • Hash分区表上可以使用分区裁剪的场景

只有等值比较的查询条件能够支持Hash分区表的裁剪，如下所示：

mysql> create table t1 (x int) partition by hash(x) partitions 4;

mysql> explain select * from t1 where x = 1;
+-------------------------+----------+-----------+------------------------+--------------------------------+
| id                      | estRows  | task      | access object          | operator info                  |
+-------------------------+----------+-----------+------------------------+--------------------------------+
| TableReader_8           | 10.00    | root      |                        | data:Selection_7               |
| └─Selection_7           | 10.00    | cop[tikv] |                        | eq(tango.t1.x, 1)              |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+------------------------+--------------------------------+

以上SQL中由条件x = 1可以知道所有结果均在一个分区上。数值 1 在经过 Hash 后，可以确定其在分区 p1 中。因此只需要扫描分区 p1 ,而无需访问一定不会出现相关结果的p2 、p3 、p4分区。从执行计划来看，其中只出现了一个TableFullScan算子，且在 access object 中指定了p1分区，确认partition pruning生效了。

  • Hash分区上不能使用分区裁剪的场景

场景一：使用in, between, > < >= <=等查询条件，不能确定查询结果只在一个分区上，不能使用分区裁剪的优化

mysql> explain select * from t1 where x >2;
+------------------------------+----------+-----------+------------------------+--------------------------------+
| id                           | estRows  | task      | access object          | operator info                  |
+------------------------------+----------+-----------+------------------------+--------------------------------+
| Union_10                     | 13333.33 | root      |                        |                                |
| ├─TableReader_13             | 3333.33  | root      |                        | data:Selection_12              |
| │ └─Selection_12             | 3333.33  | cop[tikv] |                        | gt(tango.t1.x, 2)              |
| │   └─TableFullScan_11       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo |
| ├─TableReader_16             | 3333.33  | root      |                        | data:Selection_15              |
| │ └─Selection_15             | 3333.33  | cop[tikv] |                        | gt(tango.t1.x, 2)              |
| │   └─TableFullScan_14       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
| ├─TableReader_19             | 3333.33  | root      |                        | data:Selection_18              |
| │ └─Selection_18             | 3333.33  | cop[tikv] |                        | gt(tango.t1.x, 2)              |
| │   └─TableFullScan_17       | 10000.00 | cop[tikv] | table:t1, partition:p2 | keep order:false, stats:pseudo |
| └─TableReader_22             | 3333.33  | root      |                        | data:Selection_21              |
|   └─Selection_21             | 3333.33  | cop[tikv] |                        | gt(tango.t1.x, 2)              |
|     └─TableFullScan_20       | 10000.00 | cop[tikv] | table:t1, partition:p3 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+------------------------+--------------------------------+

场景二：由于分区裁剪的规则优化是在查询计划的生成阶段，对于执行阶段才能获取到过滤条件的场景，无法利用分区裁剪的优化。比如以下场景：

mysql> explain select * from t2 where x = (select * from t1 where t2.x = t1.x and t2.x < 2);
+--------------------------------------+----------+-----------+------------------------+------------------------------------------------+
| id                                   | estRows  | task      | access object          | operator info                                  |
+--------------------------------------+----------+-----------+------------------------+------------------------------------------------+
| Projection_20                        | 39960.00 | root      |                        | tango.t2.x                                     |
| └─Apply_22                           | 39960.00 | root      |                        | inner join, equal:[eq(tango.t2.x, tango.t1.x)] |
|   ├─Union_23(Build)                  | 39960.00 | root      |                        |                                                |
|   │ ├─TableReader_26                 | 9990.00  | root      |                        | data:Selection_25                              |
|   │ │ └─Selection_25                 | 9990.00  | cop[tikv] |                        | not(isnull(tango.t2.x))                        |
|   │ │   └─TableFullScan_24           | 10000.00 | cop[tikv] | table:t2, partition:p0 | keep order:false, stats:pseudo                 |
|   │ ├─TableReader_29                 | 9990.00  | root      |                        | data:Selection_28                              |
|   │ │ └─Selection_28                 | 9990.00  | cop[tikv] |                        | not(isnull(tango.t2.x))                        |
|   │ │   └─TableFullScan_27           | 10000.00 | cop[tikv] | table:t2, partition:p1 | keep order:false, stats:pseudo                 |
|   │ ├─TableReader_32                 | 9990.00  | root      |                        | data:Selection_31                              |
|   │ │ └─Selection_31                 | 9990.00  | cop[tikv] |                        | not(isnull(tango.t2.x))                        |
|   │ │   └─TableFullScan_30           | 10000.00 | cop[tikv] | table:t2, partition:p2 | keep order:false, stats:pseudo                 |
|   │ └─TableReader_35                 | 9990.00  | root      |                        | data:Selection_34                              |
|   │   └─Selection_34                 | 9990.00  | cop[tikv] |                        | not(isnull(tango.t2.x))                        |
|   │     └─TableFullScan_33           | 10000.00 | cop[tikv] | table:t2, partition:p3 | keep order:false, stats:pseudo                 |
|   └─Selection_36(Probe)              | 0.80     | root      |                        | not(isnull(tango.t1.x))                        |
|     └─MaxOneRow_37                   | 1.00     | root      |                        |                                                |
|       └─Union_38                     | 2.00     | root      |                        |                                                |
|         ├─TableReader_41             | 2.00     | root      |                        | data:Selection_40                              |
|         │ └─Selection_40             | 2.00     | cop[tikv] |                        | eq(tango.t2.x, tango.t1.x), lt(tango.t2.x, 2)  |
|         │   └─TableFullScan_39       | 2500.00  | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo                 |
|         ├─TableReader_44             | 2.00     | root      |                        | data:Selection_43                              |
|         │ └─Selection_43             | 2.00     | cop[tikv] |                        | eq(tango.t2.x, tango.t1.x), lt(tango.t2.x, 2)  |
|         │   └─TableFullScan_42       | 2500.00  | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo                 |
|         ├─TableReader_47             | 2.00     | root      |                        | data:Selection_46                              |
|         │ └─Selection_46             | 2.00     | cop[tikv] |                        | eq(tango.t2.x, tango.t1.x), lt(tango.t2.x, 2)  |
|         │   └─TableFullScan_45       | 2500.00  | cop[tikv] | table:t1, partition:p2 | keep order:false, stats:pseudo                 |
|         └─TableReader_50             | 2.00     | root      |                        | data:Selection_49                              |
|           └─Selection_49             | 2.00     | cop[tikv] |                        | eq(tango.t2.x, tango.t1.x), lt(tango.t2.x, 2)  |
|             └─TableFullScan_48       | 2500.00  | cop[tikv] | table:t1, partition:p3 | keep order:false, stats:pseudo                 |
+--------------------------------------+----------+-----------+------------------------+------------------------------------------------+
  • Range分区表上可以使用分区裁剪的场景
  1. 等值比较的查询条件，如x=1或x in (1,2)
  2. 区间比较的查询条件如between, > < = >= <=
  3. 分区表达式为fn(col)的简单形式，查询条件是> < = >= <=之一，且fn是单调函数

以第二种情况为例：

mysql> create table t_r1 (x int) partition by range (x) (
    ->     partition p0 values less than (5),
    ->     partition p1 values less than (10),
    ->     partition p2 values less than (15)
    ->     );
Query OK, 0 rows affected (1.11 sec)

mysql> explain select * from t_r1 where x between 7 and 14;
+-----------------------------+----------+-----------+--------------------------+-------------------------------------------+
| id                          | estRows  | task      | access object            | operator info                             |
+-----------------------------+----------+-----------+--------------------------+-------------------------------------------+
| Union_8                     | 500.00   | root      |                          |                                           |
| ├─TableReader_11            | 250.00   | root      |                          | data:Selection_10                         |
| │ └─Selection_10            | 250.00   | cop[tikv] |                          | ge(tango.t_r1.x, 7), le(tango.t_r1.x, 14) |
| │   └─TableFullScan_9       | 10000.00 | cop[tikv] | table:t_r1, partition:p1 | keep order:false, stats:pseudo            |
| └─TableReader_14            | 250.00   | root      |                          | data:Selection_13                         |
|   └─Selection_13            | 250.00   | cop[tikv] |                          | ge(tango.t_r1.x, 7), le(tango.t_r1.x, 14) |
|     └─TableFullScan_12      | 10000.00 | cop[tikv] | table:t_r1, partition:p2 | keep order:false, stats:pseudo            |
+-----------------------------+----------+-----------+--------------------------+-------------------------------------------+
  • Range分区表不能使用分区裁剪的场景

由于分区裁剪的规则优化是在查询计划的生成阶段，对于执行阶段才能获取到过滤条件的场景，无法利用分区裁剪的优化。

mysql> create table t_r2 (x int);
mysql> explain select * from t_r2 where x < (select * from t_r1 where t_r2.x < t_r1.x and t_r2.x < 2);
+--------------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------+
| id                                   | estRows  | task      | access object            | operator info                                                   |
+--------------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------+
| Projection_14                        | 9990.00  | root      |                          | tango.t_r2.x                                                    |
| └─Apply_16                           | 9990.00  | root      |                          | CARTESIAN inner join, other cond:lt(tango.t_r2.x, tango.t_r1.x) |
|   ├─TableReader_19(Build)            | 9990.00  | root      |                          | data:Selection_18                                               |
|   │ └─Selection_18                   | 9990.00  | cop[tikv] |                          | not(isnull(tango.t_r2.x))                                       |
|   │   └─TableFullScan_17             | 10000.00 | cop[tikv] | table:t_r2               | keep order:false, stats:pseudo                                  |
|   └─Selection_20(Probe)              | 0.80     | root      |                          | not(isnull(tango.t_r1.x))                                       |
|     └─MaxOneRow_21                   | 1.00     | root      |                          |                                                                 |
|       └─Union_22                     | 2.00     | root      |                          |                                                                 |
|         ├─TableReader_25             | 2.00     | root      |                          | data:Selection_24                                               |
|         │ └─Selection_24             | 2.00     | cop[tikv] |                          | lt(tango.t_r2.x, 2), lt(tango.t_r2.x, tango.t_r1.x)             |
|         │   └─TableFullScan_23       | 2.50     | cop[tikv] | table:t_r1, partition:p0 | keep order:false, stats:pseudo                                  |
|         ├─TableReader_28             | 2.00     | root      |                          | data:Selection_27                                               |
|         │ └─Selection_27             | 2.00     | cop[tikv] |                          | lt(tango.t_r2.x, 2), lt(tango.t_r2.x, tango.t_r1.x)             |
|         │   └─TableFullScan_26       | 2.50     | cop[tikv] | table:t_r1, partition:p1 | keep order:false, stats:pseudo                                  |
|         └─TableReader_31             | 2.00     | root      |                          | data:Selection_30                                               |
|           └─Selection_30             | 2.00     | cop[tikv] |                          | lt(tango.t_r2.x, 2), lt(tango.t_r2.x, tango.t_r1.x)             |
|             └─TableFullScan_29       | 2.50     | cop[tikv] | table:t_r1, partition:p2 | keep order:false, stats:pseudo                                  |
+--------------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------+

3）Max/Min优化

TiDB中SQL有语句包含max/min函数并且聚合函数没有相应的group by语句的时候时，查询优化器尝试将max/min聚合函数转换为TopN算子，从而有效利用索引进行查询。比如：

select max(a) from t

这时max/min消除优化规则会将其重写为：

select max(a) from (select a from t where a is not null order by a desc limit 1) t

这个新的SQL语句在a列存在索引（或 a 列是某个联合索引的前缀）时，能够利用索引只扫描一行数据来得到最大或者最小值，从而避免对整个表的扫描。

mysql> create table tt1 (a int(11),b float,key idx1(a));
mysql> explain select max(a) from tt1;
+------------------------------+---------+-----------+--------------------------+-------------------------------------+
| id                           | estRows | task      | access object            | operator info                       |
+------------------------------+---------+-----------+--------------------------+-------------------------------------+
| StreamAgg_13                 | 1.00    | root      |                          | funcs:max(tango.tt1.a)->Column#4    |
| └─Limit_17                   | 1.00    | root      |                          | offset:0, count:1                   |
|   └─IndexReader_27           | 1.00    | root      |                          | index:Limit_26                      |
|     └─Limit_26               | 1.00    | cop[tikv] |                          | offset:0, count:1                   |
|       └─IndexFullScan_25     | 1.00    | cop[tikv] | table:tt1, index:idx1(a) | keep order:true, desc, stats:pseudo |
+------------------------------+---------+-----------+--------------------------+-------------------------------------+
5 rows in set (0.01 sec)

如果使用到group by语句：

mysql> explain select b,max(a) from tt1 group by b;
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                                                                 |
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------+
| Projection_4                | 8000.00  | root      |               | tango.tt1.b, Column#4                                                                         |
| └─HashAgg_9                 | 8000.00  | root      |               | group by:tango.tt1.b, funcs:max(Column#5)->Column#4, funcs:firstrow(tango.tt1.b)->tango.tt1.b |
|   └─TableReader_10          | 8000.00  | root      |               | data:HashAgg_5                                                                                |
|     └─HashAgg_5             | 8000.00  | cop[tikv] |               | group by:tango.tt1.b, funcs:max(tango.tt1.a)->Column#5                                        |
|       └─TableFullScan_8     | 10000.00 | cop[tikv] | table:tt1     | keep order:false, stats:pseudo                                                                |
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------+

4）谓词下推

谓词下推是将查询语句中的过滤表达式的计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销。

  • 谓词下推到存储层
mysql> CREATE TABLE `tt01` (`id` int(11) NOT NULL AUTO_INCREMENT,a int,`city` varchar(20) NOT NULL DEFAULT '',PRIMARY KEY (`id`));
mysql> explain select * from tt01 where a<10 ;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 3323.33  | root      |               | data:Selection_6               |
| └─Selection_6           | 3323.33  | cop[tikv] |               | lt(tango.tt01.a, 10)           |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:tt01    | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+

在该查询中，将谓词id < 10下推到TiKV上对数据进行过滤，可以减少由于网络传输带来的开销。

  • 谓词下推到join下方
mysql> explain select * from tt01 join tt02 on tt01.a= tt02.a where tt01.a < 1;
+------------------------------+----------+-----------+---------------+----------------------------------------------------+
| id                           | estRows  | task      | access object | operator info                                      |
+------------------------------+----------+-----------+---------------+----------------------------------------------------+
| HashJoin_8                   | 4154.17  | root      |               | inner join, equal:[eq(tango.tt01.a, tango.tt02.a)] |
| ├─TableReader_15(Build)      | 3323.33  | root      |               | data:Selection_14                                  |
| │ └─Selection_14             | 3323.33  | cop[tikv] |               | lt(tango.tt02.a, 1), not(isnull(tango.tt02.a))     |
| │   └─TableFullScan_13       | 10000.00 | cop[tikv] | table:tt02    | keep order:false, stats:pseudo                     |
| └─TableReader_12(Probe)      | 3323.33  | root      |               | data:Selection_11                                  |
|   └─Selection_11             | 3323.33  | cop[tikv] |               | lt(tango.tt01.a, 1), not(isnull(tango.tt01.a))     |
|     └─TableFullScan_10       | 10000.00 | cop[tikv] | table:tt01    | keep order:false, stats:pseudo                     |
+------------------------------+----------+-----------+---------------+----------------------------------------------------+

在该查询中，将谓词tt01.a < 1下推到join前进行过滤，可以减少join时的计算开销。

1.2 物理优化

物理优化是基于代价的优化CBO（cost based optimization），这一阶段中，优化器会为逻辑执行计划中的每个算子选择具体的物理实现，以将逻辑优化阶段产生的逻辑执行计划转换成物理执行计划。逻辑算子的不同对应的物理实现在时间复杂度、资源消耗和物理属性等方面也有不同。在这个过程中，优化器会根据数据的统计信息来确定不同物理实现的代价，并选择整体代价最小的物理执行计划。物理优化需要做的决策有很多，比如说：

  • 读取数据的方式是使用全表扫描还是通过索引读取数据
  • 如果存在多个索引，选择哪个索引
  • 逻辑算子的物理实现，即实际使用的算法
  • 是否可以将算子下推到存储层执行，以提升执行效率
1.2.1 索引的选择

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

在这里插入图片描述

1）Skyline-Pruning

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

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

2）基于代价的选择

读表的代价估算需要考虑以下方面：

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

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

1.2.2 统计信息的收集和维护

在TiDB中维护的统计信息统计信息包括表的总行数、列的等深直方图、Count-Min Sketch，Null值的个数、平均长度、不同值的数目等等。

1）手动收集

TiDB中使用ANALYZE语句来收集统计信息，分为全量收集和增量收集。

  • 全量收集
#收集TableNameList中所有表的统计信息：
ANALYZE TABLE TableNameList
#收集TableName中所有的IndexNameList中的索引列的统计信息
ANALYZE TABLE TableName INDEX [IndexNameList]
#收集TableName中所有的PartitionNameList中分区的统计信息
ANALYZE TABLE TableName PARTITION PartitionNameList
#收集TableName中所有的PartitionNameList中分区的索引列统计信息
ANALYZE TABLE TableName PARTITION PartitionNameList [IndexNameList]
  • 增量收集

在进行全量收集后，可以使用增量收集来单独分析新增的部分，以提高分析的速度。目前只有索引提供了增量收集的功能。

#增量收集 TableName 中所有的 IndexNameList 中的索引列的统计信息
ANALYZE INCREMENTAL TABLE TableName INDEX [IndexNameList]
#增量收集 TableName 中所有的 PartitionNameList 中分区的索引列统计信息
ANALYZE INCREMENTAL TABLE TableName PARTITION PartitionNameList INDEX [IndexNameList]

2）自动更新

在执行DML语句时，TiDB会自动更新表的总行数以及修改的行数。这些信息会定期自动持久化，更新周期默认是1分钟（20*stats-lease）。stats-lease的默认值是3s，如果将其指定为0，那么将不会自动更新。和统计信息自动更新相关的三个系统变量如下：

mysql> show variables like 'tidb_auto_analyze%';
+------------------------------+-------------+
| Variable_name                | Value       |
+------------------------------+-------------+
| tidb_auto_analyze_ratio      | 0.5         |
| tidb_auto_analyze_start_time | 00:00 +0000 |
| tidb_auto_analyze_end_time   | 23:59 +0000 |
+------------------------------+-------------+

当某个表tbl的修改行数与总行数的比值大于tidb_auto_analyze_ratio，并且当前时间在tidb_auto_analyze_start_time和tidb_auto_analyze_end_time之间时，TiDB会在后台执行ANALYZE TABLE tbl语句自动更新这个表的统计信息。

在这里插入图片描述
3）控制analyze并发度

执行ANALYZE语句的时候，可以通过一些参数来调整并发度，以控制对系统的影响

在这里插入图片描述

4）查看Analyze的状态

mysql> show analyze status;
+--------------+------------+----------------+--------------------------+----------------+---------------------+----------+
| Table_schema | Table_name | Partition_name | Job_info                 | Processed_rows | Start_time          | State    |
+--------------+------------+----------------+--------------------------+----------------+---------------------+----------+
| bikeshare    | trips      |                | analyze index start_date |         340000 | 2021-03-14 13:59:38 | finished |
| bikeshare    | trips      |                | analyze columns          |         340000 | 2021-03-14 13:59:38 | running  |
+--------------+------------+----------------+--------------------------+----------------+---------------------+----------+
2 rows in set (0.00 sec)

5）统计信息的查看

  • 查看表的统计信息meta信息：
mysql> show stats_meta where table_name = 'trips';
+-----------+------------+----------------+---------------------+--------------+-----------+
| Db_name   | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+-----------+------------+----------------+---------------------+--------------+-----------+
| bikeshare | trips      |                | 2021-03-14 22:23:19 |            0 |    340000 |
+-----------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.08 sec)
  • 表的健康度信息

通过SHOW STATS_HEALTHY可以查看表的统计信息健康度，并粗略估计表上统计信息的准确度。当modify_count >= row_count时，健康度为0；当modify_count < row_count时，健康度为(1 - modify_count/row_count) * 100。

mysql> show stats_healthy where table_name = 'trips';
+-----------+------------+----------------+---------+
| Db_name   | Table_name | Partition_name | Healthy |
+-----------+------------+----------------+---------+
| bikeshare | trips      |                |     100 |
+-----------+------------+----------------+---------+
1 row in set (0.00 sec)
  • 通过SHOW STATS_HISTOGRAMS来查看列的不同值数量以及NULL数量等信息
mysql> show stats_histograms where table_name = 'trips';
+-----------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name   | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation |
+-----------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| bikeshare | trips      |                | trip_id     |        0 | 2021-03-14 13:59:45 |         340000 |          0 |            8 |           0 |
| bikeshare | trips      |                | start_date  |        1 | 2021-03-14 13:59:41 |         167954 |          0 |            0 |           0 |
+-----------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
2 rows in set (0.00 sec)
  • 通过SHOW STATS_BUCKETS来查看直方图每个桶的信息
mysql> show stats_buckets where table_name='tt03';
+---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Bucket_id | Count | Repeats | Lower_Bound | Upper_Bound |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+
| tango   | tt03       |                | a           |        0 |         0 |     1 |       1 | 1           | 1           |
| tango   | tt03       |                | idx_a       |        1 |         0 |     1 |       1 | 1           | 1           |
| tango   | tt03       |                | idx_b       |        1 |         0 |     1 |       1 | 1           | 1           |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+
3 rows in set (0.00 sec)

6）删除统计信息

通过执行DROP STATS语句来删除统计信息

mysql>  show stats_meta where table_name = 'tt03';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| tango   | tt03       |                | 2021-03-13 23:44:43 |            1 |         2 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.01 sec)
mysql> drop stats tango.tt03;
Query OK, 0 rows affected (0.18 sec)
mysql>  show stats_meta where table_name = 'tt03';
Empty set (0.00 sec)

7）统计信息的导入导出

  • 统计信息导出

通过以下接口可以获取数据库 d b n a m e 中 的 表 {db_name}中的表 dbn​ame中的表{table_name}的json格式的统计信息：

http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}

例如获取bikeshare数据库中trips表的统计信息：

curl -G "http://192.168.112.101:10080/stats/dump/bikeshare/trips" > trips.json
  • 统计信息导入

将统计信息导出接口得到的json文件导入数据库中：

mysql> LOAD STATS 'file_name';
1.3 控制执行计划

当确定执行计划存在问题时，可以通过以下方法控制执行计划的生成：

  • Optimizer Hints，使用Hint来指导TiDB生成执行计划，Hint会侵入性地更改SQL
  • 执行计划管理，通过SQL BINDING来控制执行计划的生成以及Baseline Evolution在后台自动对执行计划进行演进来减轻诸如版本升级等原因造成的执行计划不稳定，而导致集群性能下降的问题。
  • 优化规则和表达式下推黑名单，手动禁用一些优化规则和表达式
1.3.1 使用Hint调整TiDB的执行计划

当优化器选择了不当的执行计划的时候，需要使用hint进行执行计划的绑定。TiDB目前支持的hint语法列表：

在这里插入图片描述

1）USE INDEX,FORCE INDEX,IGNORE INDEX

mysql> explain select count(*) from trips where trip_id>1;
+-----------------------------+-----------+-----------+---------------+-----------------------------------+
| id                          | estRows   | task      | access object | operator info                     |
+-----------------------------+-----------+-----------+---------------+-----------------------------------+
| StreamAgg_17                | 1.00      | root      |               | funcs:count(Column#13)->Column#11 |
| └─TableReader_18            | 1.00      | root      |               | data:StreamAgg_9                  |
|   └─StreamAgg_9             | 1.00      | cop[tikv] |               | funcs:count(1)->Column#13         |
|     └─TableRangeScan_16     | 339999.00 | cop[tikv] | table:trips   | range:(1,+inf], keep order:false  |
+-----------------------------+-----------+-----------+---------------+-----------------------------------+
4 rows in set (0.01 sec)

mysql> explain select count(*) from trips use index(start_date) where trip_id>1 ;
+------------------------------+-----------+-----------+-------------------------------------------+-----------------------------------+
| id                           | estRows   | task      | access object                             | operator info                     |
+------------------------------+-----------+-----------+-------------------------------------------+-----------------------------------+
| StreamAgg_20                 | 1.00      | root      |                                           | funcs:count(Column#16)->Column#11 |
| └─IndexReader_21             | 1.00      | root      |                                           | index:StreamAgg_9                 |
|   └─StreamAgg_9              | 1.00      | cop[tikv] |                                           | funcs:count(1)->Column#16         |
|     └─Selection_19           | 339999.00 | cop[tikv] |                                           | gt(bikeshare.trips.trip_id, 1)    |
|       └─IndexFullScan_18     | 340000.00 | cop[tikv] | table:trips, index:start_date(start_date) | keep order:false                  |
+------------------------------+-----------+-----------+-------------------------------------------+-----------------------------------+
5 rows in set (0.03 sec)

2）MAX_EXECUTION_TIME(N)

在SELECT语句中可以使用MAX_EXECUTION_TIME(N)，它会限制语句的执行时间不能超过N毫秒，否则服务器会终止这条语句的执行。比如设置了1秒超时：

mysql> SELECT /*+ MAX_EXECUTION_TIME(100) */  count(*)  FROM trips;
ERROR 1317 (70100): Query execution was interrupted

另外，环境变量MAX_EXECUTION_TIME也会对语句执行时间进行限制。对于高可用和时间敏感的业务， 建议使用MAX_EXECUTION_TIME，以免错误的查询计划或bug影响整个TiDB集群的性能甚至稳定性。

1.3.2 执行计划管理SPM

执行计划管理，又称SPM (SQL Plan Management)，是执行计划进行人为干预的一系列功能，包括执行计划绑定、自动捕获绑定、自动演进绑定等。相比较Hint而言，SPM可以在不修改SQL语句的前提下干预执行计划的选择。

1）执行计划绑定SQL Binding

SQL Binding是SPM的基础，当出现执行计划不优时，可以使用SQL Bind在不更改业务的情况下快速地对执行计划进行修复。

  • 创建绑定
CREATE [GLOBAL | SESSION] BINDING FOR SelectStmt USING SelectStmt;

该语句可以在GLOBAL或者SESSION作用域内为SQL绑定执行计划。在不指定作用域时，默认作用域为SESSION。被绑定的SQL会被参数化，然后存储到系统表中。在处理SQL查询时，只要参数化后的SQL和系统表中某个被绑定的SQL匹配即可使用相应的优化器Hint。

#创建一个binding
mysql> create binding for select count(*) from trips where trip_id>1 using select count(*) from trips use index(start_date) where trip_id>1;
Query OK, 0 rows affected (0.11 sec)
  • 查看绑定bindings
mysql> show bindings;
+-------------------------------------------------+------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-----------------+
| Original_sql                                    | Bind_sql                                                         | Default_db | Status | Create_time             | Update_time             | Charset | Collation       |
+-------------------------------------------------+------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-----------------+
| select count ( ? ) from trips where trip_id > ? | select count(*) from trips use index(start_date) where trip_id>1 | bikeshare  | using  | 2021-03-15 18:49:43.069 | 2021-03-15 18:49:43.069 | utf8    | utf8_general_ci |
+-------------------------------------------------+------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-----------------+
  • 删除bindings
mysql> drop binding for select count(*) from trips where trip_id>1;
Query OK, 0 rows affected (0.00 sec)

2）自动捕获绑定（Baseline Capturing）

将tidb_capture_plan_baselines的值设置为on（其默认值为 off）可以打开自动捕获绑定功能。自动绑定功能依赖于Statement Summary，因此在使用自动绑定之前需打开Statement Summary开关。开启自动绑定功能后，每隔bind-info-lease（默认值为3s）会遍历一次Statement Summary中的历史SQL语句，并为至少出现两次的SQL语句自动捕获绑定。

# 开启 statement summary
mysql> set tidb_enable_stmt_summary = 1;
Query OK, 0 rows affected (0.00 sec)
# 开启自动绑定功能
mysql> set tidb_capture_plan_baselines = 1; 
Query OK, 0 rows affected (0.01 sec)

mysql>  select count(*) from trips where trip_id>1;
+----------+
| count(*) |
+----------+
|   339999 |
+----------+
1 row in set (0.94 sec)

# 连续跑两遍如下查询即可自动为其创建一条绑定
mysql>  select count(*) from trips where trip_id>1;
+----------+
| count(*) |
+----------+
|   339999 |
+----------+
1 row in set (0.33 sec)

# 查看 global bindings 即可发现自动创建的 binding
mysql> show global bindings;
+-------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-----------+
| Original_sql                                    | Bind_sql                                                                                                            | Default_db | Status | Create_time             | Update_time             | Charset | Collation |
+-------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-----------+
| select count ( ? ) from trips                   | SELECT /*+ use_index(@`sel_1` `bikeshare`.`trips` `start_date`), stream_agg(@`sel_1`)*/ COUNT(1) FROM `trips`       | bikeshare  | using  | 2021-03-15 18:09:35.184 | 2021-03-15 18:09:35.184 |         |           |
| select count ( ? ) from trips where trip_id > ? | SELECT /*+ use_index(@`sel_1` `bikeshare`.`trips` ), stream_agg(@`sel_1`)*/ COUNT(1) FROM `trips` WHERE `trip_id`>1 | bikeshare  | using  | 2021-03-15 18:09:49.584 | 2021-03-15 18:09:49.584 |         |           |
| select * from trips limit ?                     | SELECT /*+ use_index(@`sel_1` `bikeshare`.`trips` )*/ * FROM `trips` LIMIT 1                                        | bikeshare  | using  | 2021-03-15 18:09:36.134 | 2021-03-15 18:09:36.134 |         |           |
+-------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-----------+
3 rows in set (0.00 sec)

3）自动演进绑定（Baseline Evolution）

由于某些数据变更后，原先绑定的执行计划可能是一个不优的计划。为了解决该问题，TiDB引入Baseline Evolution功能来自动优化已经绑定的执行计划。另外Baseline Evolution还可以在一定程度上避免统计信息改动后，对执行计划带来的抖动。

通过设置global tidb_evolve_plan_baselines = 1开启自动演进功能，如果优化器选出的最优执行计划不在之前绑定的执行计划之中，会将其记录为待验证的执行计划。每隔bind-info-lease（默认值为 3s），会选出一个待验证的执行计划，将其和已经绑定的执行计划中代价最小的比较实际运行时间。如果待验证的运行时间更优的话，会将其标记为可使用的绑定。

在实际执行过程中，为了减少自动演进对集群的影响，可以通过tidb_evolve_plan_task_max_time来限制每个执行计划运行的最长时间，其默认值为十分钟；通过tidb_evolve_plan_task_start_time和tidb_evolve_plan_task_end_time可以限制运行演进任务的时间窗口，默认的时间窗口为全天。

参考资料：

  1. https://book.tidb.io/session3/chapter1/optimizer-summary.html
  2. https://docs.pingcap.com/zh/tidb/stable/sql-logical-optimization
  3. https://docs.pingcap.com/zh/tidb/stable/sql-physica3-optimization

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/119270226
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！
在这里插入图片描述

solihawk
关注
专栏目录
【TiDB理论知识 07】SQL执行流程
DBA之路的博客
07-26 1219
如果不是点查 ，则还需要走后面的优化流程，分为逻辑优化 （主要是根据关系代数，等价交换的一些规则对SQL语句进行逻辑的变换，比如外连接转换为内连接），逻辑优化之后就是 物理优化 主要是基于逻辑优化的结果 结合表的统计信息（表的行数，列的选择度 ，直方图等等）选择最优的算子，从Compile模块出来的就是物理执行计划 ，就可以去TiKV中取数。，在TIDB 启动的时候 ，information schema是存储了元数据的，已经被载入到Tidb Server的缓存中了，所以直接读缓存就可以了。
TiDB数据库
Mr.xing的博客
07-17 2231
TiDB分布式数据库
一个小操作，SQL查询速度翻了1000倍。
TiDB 社区干货传送门
05-07 632
背景介绍 某一天早上来到公司，接到业务同学反馈，线上某个SQL之前查询速度很快，从某个时间点开始查询速度突然变慢了，希望DBA帮忙查看下。业务同学反馈的原话如下： 看到这个问题，我第一时间询问了业务对这个表的基本操作，得到的反馈如下： 这个表的SQL语法没有发生过变化 这个表的表结构近期未发生变更 这个表是个日志表，近期只有写入insert，没有大量delete、update操作 分析过程 1、SQL分析 首先，我们来看下这条SQL（脱敏之后）： SELEC
TiDB 执行计划概览
weixin_42241611的博客
07-19 2312
算子是为返回查询结果而执行的特定步骤。全表扫描。带有范围的表数据扫描。根据上层传递下来的RowID扫描表数据。时常在索引读操作后检索符合条件的行。另一种“全表扫描”，扫的是索引数据，不是表数据。带有范围的索引数据扫描操作。将TiKV上底层扫表算子TableFullScan或TableRangeScan得到的数据进行汇总。将TiKV上底层扫表算子IndexFullScan或IndexRangeScan得到的数据进行汇总。RowID或类型的算子。和。...
sql优化之逻辑优化
weixin_34406796的博客
12-12 1317
在单机数据库系统中进行优化。面临的问题，比如说给定一个要query的sql语句，查询优化算法的目标就是找到查询的一个具有最小执行花费的执行计划，如果找到了，那么这样的执行计划一定具有最快的响应时间。 查询语句可表示成一颗二叉树，其中叶子代表关系，内部结点是运算符，表示左右子树的连接关系，子树是sql片段或子表达式。根节点是最后运算的操作符...
Tidb SQL优化（一）
qq_40274514的博客
02-01 1588
1.SQL 优化流程 在 TiDB 中，从输入的查询文本到最终的执行计划执行结果的过程可以见下图。 在经过了  parser  对原始查询文本的解析以及一些简单的合法性验证后，TiDB 首先会对查询做一些逻辑上的等价变化， 通过这些等价变化，使得这个查询在逻辑执行计划上可以变得更易于处理。在等价变化结束之后，TiDB 会得到一个与原始查...
TIDB-SQL优化经验
apply_bomb的博客
04-24 657
tidb sql 优化经验总结
深入了解 TiDB SQL 优化器
DataFun_Hoh的博客
04-25 2582
分享嘉宾：张建 PingCAP TiDB优化器与执行引擎技术负责人 编辑整理：Druid中国用户组第6次大数据MeetUp 出品平台：DataFunTalk 导读： 本次报告张老师主要从原理上带大家深入了解 TiDB SQL 优化器中的关键模块，比如应用一堆逻辑优化规则的逻辑优化部分，基于代价的物理优化部分，还有和代价估算密切相关的统计信息等。 本文将从以下几个方面介绍：首先讲一下TiDB的整体架构，接下来就是优化器的两个比较重要的模块，一个是SQL优化，做执行计划生成；另一个模块就是统计信息模块，其作用
【TiDB】一些很有意思的sql调优案例分享
在数字化道路无限探索
09-21 812
依据于TiDB执行计划
TiDB SQL性能优化：执行流程与慢查询分析
在"6-苗青利-TiDB 应用开发实践（二）1"这门课程中，讲师深入探讨了TiDB数据库的SQL性能问题及其优化方法。TiDB作为一款分布式通用数据库，可能会遇到各种SQL性能问题，这些问题可能影响单个SQL的执行速度，严重时...
4.Tidb SQL优化（一）
ex-huh001的博客
11-26 682
1.SQL 优化流程 在 TiDB 中，从输入的查询文本到最终的执行计划执行结果的过程可以见下图。 在经过了parser对原始查询文本的解析以及一些简单的合法性验证后，TiDB 首先会对查询做一些逻辑上的等价变化， 通过这些等价变化，使得这个查询在逻辑执行计划上可以变得更易于处理。在等价变化结束之后，TiDB 会得到一个与原始查询等价的查询计划结构，之后根据数据分布、以及一个算子具体的执行开销，来获得一个最终的执行计划， 同时，TiDB 在执行PREPARE语句时，可以选择开启缓存来降低...
TiDB的SQL优化技巧
TiDB是一款分布式SQL数据库，由PingCAP开发和维护。它具备了传统关系型数据库的易用性和功能，同时也支持水平扩展和强一致性特性，可以满足大规模数据存储和高并发访问的需求。 TiDB的核心特性包括： - 分布式架构...
依据TIdb执行计划的sql调优案例分享
TiDB 社区干货传送门
09-13 356
原文来源： https://tidb.net/blog/7d5f6656 ...
TIDB SQL优化
麦香鸡翅的博客
08-28 3239
 两处标红的需要注意下：  如果去除第一个红框框里面的语句 DESC SQL语句可以发现执行过程发生改变： 可以发现 t3,t4两张表的userId关联没有走索引。  /*+ TIDB_INLJ(t3,t4) */ 上面这句其实是 TiDB Index Lookup Join 算法 详情：https://segmentfault.com/a/1190000015411838 如果去...
TiDB实战篇-基于索引的SQL优化
S1124654的博客
04-30 540
TiDB实战篇-基于索引的SQL优化
MPP and SMP in TiDB（TiDB的SQL 性能优化）
weixin_33701251的博客
03-29 1022
2019独角兽企业重金招聘Python工程师标准>>> ...
TiDB 源码阅读系列文章（七）基于规则的优化
weixin_33752045的博客
04-11 161
作者：毛康力 在 TiDB 里面，SQL 优化的过程可以分为逻辑优化和物理优化两个部分。逻辑优化主要是基于规则的优化，简称 RBO（rule based optimization）。物理优化会为逻辑查询计划中的算子选择某个具体的实现，需要用到一些统计信息，决定哪一种方式代价最低，所以是基于代价的优化 CBO（cost based optimization）。 本篇将主要关注逻辑优化。先介绍 T...
5.TiDB SQL优化（二）
ex-huh001的博客
11-26 1005
1.物理优化 物理优化是基于代价的优化，为上一阶段产生的逻辑执行计划制定物理执行计划。这一阶段中，优化器会为逻辑执行计划中的每个算子选择具体的物理实现。逻辑算子的不同物理实现有着不同的时间复杂度、资源消耗和物理属性等。在这个过程中，优化器会根据数据的统计信息来确定不同物理实现的代价，并选择整体代价最小的物理执行计划。 1.1索引的选择 从存储层读取数据是 SQL 计算过程中最为耗时的部分之一，TiDB 目前支持从不同的存储和不同的索引中读取数据，索引选择得是否合理将很大程度上决定一个查询的运...
TiDB数据库 v5.4.1.zip
最新发布
04-05
综上所述，TiDB v5.4.1是一个针对大规模并发事务和实时分析场景设计的数据库系统，具备分布式、强一致、MySQL兼容、高可用、HTAP等一系列特性，适用于各种企业级应用，特别是那些需要处理大量实时数据的场景。...
TiDB查询优化
qq_37480069的博客
04-07 963
TiDB的正确使用姿势，查询优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值