TiDB 查询优化及调优系列（一）TiDB 优化器简介 tidb

与其它主流商业数据库一样，TiDB 的查询优化器负责用户及系统查询的优化，生成有效且高效的执行计划由执行器来执行。而优化器生成的执行计划的优劣直接影响查询的执行效率和性能。「TiDB 查询优化及调优」系列文章将通过一些具体的案例，向大家介绍 TiDB 查询及优化相关的原理和应用。本文为系列文章的第一篇，将简要介绍 TiDB 的查询优化器的优化流程。
TiDB 中常见的逻辑优化规则优化器的优化过程可以简单的看成在一个搜索问题，即针对一条查询，在由各种可能的执行计划构成的巨大搜索空间内寻找到该查询的最优执行计划。不同的数据库查询优化器根据架构不同，对应的优化流程也有所不同。TiDB 的查询优化流程主要分为逻辑优化和物理优化两部分。
在逻辑优化中，利用关系代数的变换规则进行查询语句表达式的等价变换，并在这个过程中不断增加或修剪可能的计划搜索空间（例如不同的 join order），最后选择生成最优的逻辑计划树。在之后的物理优化过程中，对逻辑计划树中的算子节点生成实际执行的物理计划，并评估不同物理计划的实现算法（例如不同的 join 方法）或对象（例如使用不同的索引）的代价，从中选取代价最小的物理计划。
下面分别对逻辑优化和物理优化做简介。
逻辑优化是针对逻辑计划中的逻辑算子进行的优化流程。在介绍逻辑优化规则之前，我们先简介一下 TiDB 中的几种主要逻辑算子：

DataSource：数据源，表示一个源表，如 select * from t 中的 t 。
Selection：代表了相应的过滤条件，select * from t where a = 5 中的 where a = 5 。
Projection：投影操作，也用于表达式计算， select c, a + b from t 里面的 c 和 a+b 就是投影和表达式计算操作。
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 种最基本的算子。
TiDB 的逻辑优化是基于规则的优化，通过对输入的逻辑执行计划按顺序应用优化规则，使整个逻辑执行计划变得更加高效。这些常用逻辑优化规则包括：

文章图片

部分逻辑优化规则示例如下：
规则 4：Max / Min 优化
Max/ Min 优化，会对Max/ Min 语句进行改写。如下面的语句：

select min(id) from t;

改成下面的写法，可以实现类似的效果：

select min(id) from t;

前一个语句生成的执行计划，是一个 TableScan 上面接一个 Aggregation，这是一个全表扫描的操作。后一个语句，生成执行计划是 TableScan + Sort + Limit。通常数据表中的 id 列是主键或者存在索引，数据本身有序，这样 Sort 就可以消除，最终变成 TableScan/IndexLookUp + Limit，这样就避免了全表扫描的操作，只需要读到第一条数据就能返回结果。
最大最小消除由优化器“自动”地做这个变换。
规则 5：外连接消除
外连接消除指的是将整个连接操作从查询中移除。外连接消除需要满足一定条件：

条件 1：LogicalJoin 的父亲算子只会用到 LogicalJoin 的 outer plan 所输出的列
条件 2：
- 条件 2.1：LogicalJoin 中的 join key 在 inner plan 的输出结果中满足唯一性
- 条件 2.2：LogicalJoin 的父亲算子会对输入的记录去重

条件 1 和条件 2 必须同时满足，但条件 2.1 和条件 2.2 只需满足一条即可。
满足条件 1 和条件 2.1 的一个例子：

select t1.a from t1 left join t2 on t1.b = t2.b;

可以被改写成：

select t1.a from t1;

TiDB 中常见的物理优化物理优化是基于代价的优化，这一阶段中，优化器会为逻辑执行计划中的每个算子选择具体的物理实现，以将逻辑优化阶段产生的逻辑执行计划转换成物理执行计划。逻辑算子的不同物理实现有着不同的时间复杂度、资源消耗和物理属性等。在这个过程中，优化器会根据数据的统计信息来估算不同物理实现的代价，并选择整体代价最小的物理执行计划。
物理优化需要做的决策有很多，例如：

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

TiDB 统计信息统计信息对于查询优化器来说是至关重要的输入信息，优化器将会利用统计信息来估算查询谓词的选择率，查询的各类基数，以及不同算子的代价，并利用这些估算来进行部分逻辑优化以及物理优化。如果统计信息存因为过时或缺失造成较大失真偏差，往往会对优化器的优化造成非常大的影响，从而影响到生成的查询计划。所以在此，我们会用较大篇幅介绍统计信息，以及相关的收集与维护，因为这是优化器在做查询优化的基石。
TiDB 收集的统计信息包括了表级别和列级别的信息，表的统计信息包括总行数和修改的行数。列的统计信息包括不同值的数量、NULL 的数量、直方图、列上出现次数最多的值 TOPN 等信息。
TiDB 的统计信息收集包括了手动收集和自动更新两种方式：

手动收集：

通过执行ANALYZE 语句来收集统计信息。以数据库中 person 表为例，使用 analyze 的试行语句如下：

analyze table person;

收集统计信息过程中，可以通过show analyze status 语句查询执行状态，该语句也可以通过where 子句对输出结果进行过滤，显示输出结果如下：

mysql> show analyze status where job_info = 'analyze columns'; +--------------+------------+-----------------+---------------------+----------+ | Table_schema | Table_name | Job_info| Start_time| State| +--------------+------------+-----------------+---------------------+----------+ | test| person| analyze columns | 2020-03-07 06:22:34 | finished | | test| customer| analyze columns | 2020-03-07 06:32:19 | finished | | test| person| analyze columns | 2020-03-07 06:35:27 | finished | +--------------+------------+-----------------+---------------------+----------+ 3 rows in set (0.01 sec)

自动更新：

在执行 DML 语句时，TiDB 会自动更新表的总行数以及修改的行数。这些信息会定期自动持久化，更新周期默认是 1 分钟（20 * stats-lease）
注意：stats-lease 的默认值是 3s，如果将其设定为 0，则关闭统计信息自动更新。
目前根据统计信息收集和使用的演进，TiDB 目前支持两个版本的统计信息，其中 Version 2 在 Version 1 的基础上做了更多的优化来改善统计信息的维护方式和精度，以及收集效率。具体的差异可以参考 TiDB 统计信息简介文档。
在统计信息收集之后，可以查看统计信息以及表的健康度来确认统计信息是否有较大失真。
查看表的统计信息 meta 信息：

mysql> show stats_meta where table_name = 'person'; +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time| Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test| person|| 2020-03-07 07:20:54 |0 |4 | +---------+------------+----------------+---------------------+--------------+-----------+ 1 row in set (0.01 sec)

查看表的健康度信息：

mysql> show stats_healthy where table_name = 'person'; +---------+------------+----------------+---------+ | Db_name | Table_name | Partition_name | Healthy | +---------+------------+----------------+---------+ | test| person||100 | +---------+------------+----------------+---------+ 1 row in set (0.00 sec)

可通过SHOW STATS_HISTOGRAMS 来查看列的不同值数量以及 NULL 值数量等信息：

mysql> show stats_histograms where table_name = 'person'; +---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time| Distinct_count | Null_count | Avg_col_size | Correlation | +---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+ | test| person|| name|0 | 2020-03-07 07:20:54 |4 |0 |6.25 |-0.2 | +---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+ 1 row in set (0.00 sec)

可通过SHOW STATS_BUCKETS 来查看直方图每个桶的信息：

mysql> show stats_buckets; +---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+ | Db_name | Table_name | Partition_name | Column_name | Is_index | Bucket_id | Count | Repeats | Lower_Bound | Upper_Bound | +---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+ | test| person|| name|0 |0 |1 |1 | jack| jack| | test| person|| name|0 |1 |2 |1 | peter| peter| | test| person|| name|0 |2 |3 |1 | smith| smith| | test| person|| name|0 |3 |4 |1 | tom| tom| +---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+ 4 rows in set (0.01 sec)

可通过执行DROP STATS 语句来删除统计信息。语句如下：

mysql> DROP STATS person;

TiDB 的统计信息可以导入导出，方便备份以及值班人员复现定位相关问题。

导出：通过以下接口可以获取数据库 ${db_name} 中的表${table_name}的 json 格式的统计信息：

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

示例：获取本机上 test 数据库中 person 表的统计信息：

curl -G "http://127.0.0.1:10080/stats/dump/test/person" > person.json

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

mysql> LOAD STATS 'file_name';

file_name 为被导入的统计信息文件名。
【TiDB 查询优化及调优系列（一）TiDB 优化器简介】本文为「TiDB 查询优化及调优」系列文章的第一篇，后续将继续对 TiDB 查询计划、慢查询诊断监控及排查、调整及优化查询执行计划以及其他优化器开发或规划中的诊断调优功能等进行介绍。如果您对 TiDB 的产品有任何建议，欢迎来到 https://internals.tidb.io/ 与我们交流。