tidb-analyze
锺霍英
一、背景
收集统计信息是mysql，tidb等数据库都有的，统计信息的准确能够使执行计划更准确，优化器更有效，而tidb的analyze更耗时间，更吃性能，不注意的情况下很容易被系统自动的analyze将系统打垮
二、相关变量
1.系统变量
| 变量名 | 意义 |
|---|---|
| tidb_auto_analyze_end_time | 允许自动 ANALYZE 更新统计信息的结束时间 |
| tidb_auto_analyze_ratio | 这个变量用来设置 TiDB 在后台自动执行 ANALYZE TABLE 更新统计信息的阈值。0.5 指的是当表中超过 50% 的行被修改时 |
| tidb_auto_analyze_start_time | 允许自动 ANALYZE 更新统计信息的开始时间 |
| tidb_enable_fast_analyze | 控制是否启用统计信息快速分析功能。默认值 0 表示不开启。（试验功能） |
| tidb_build_stats_concurrency | 设置 ANALYZE 语句执行时并发度(默认为4) |
| tidb_distsql_scan_concurrency | 控制一次读取的 Region 数量，其默认值是 15。AP 类应用适合较大的值，TP 类应用适合较小的值。对于 AP 类应用，最大值建议不要超过所有 TiKV 节点的 CPU 核数。 |
| tidb_index_serial_scan_concurrency | 控制一次读取的 Region 数量，其默认值是 1。AP 类应用适合较大的值，TP 类应用适合较小的值 |
| tidb_analyze_version | 用于控制 TiDB 收集统计信息的行为,5.1.0引入,在 v5.1.0 以前的版本中，该变量的默认值为 1。在 v5.1.0 中，该变量的默认值为 2 |
2.文件变量
| 变量名 | 意义 |
|---|---|
| performance.run-auto-analyze | 是否做自动的 Analyze, 默认值：true |
3.直方图
直方图是一种对数据分布情况进行描述的工具，它会按照数据的值大小进行分桶，并用一些简单的数据来描述每个桶，比如落在桶里的值的个数
4.Count-Min Sketch
Count-Min Sketch 是一种哈希结构，当查询中出现诸如 a = 1 或者 IN 查询（如 a in (1, 2, 3)）这样的等值查询时，TiDB 便会使用这个数据结构来进行估算。
哈希碰撞的解决办法：
- 修改手动收集统计信息中提到的 WITH NUM TOPN 参数。TiDB 会将出现频率前 x 大的数据单独储存，之后的数据再储存到
Count-Min Sketch 中。因此可以调大这个值来避免一个比较大的值和一个比较小的值被哈希到一起。在 TiDB
中，这个参数的默认值是 20，最大可以设置为 1024。 - 修改统计信息的收集-手动收集中提到的 WITH NUM CMSKETCH DEPTH 和 WITH NUM CMSKETCH WIDTH
两个参数，这两个参数会影响哈希的桶数和碰撞概率，可是适当调大来减少冲突概率，同时它会影响统计信息的内存使用，可以视具体情况来调整。在
TiDB 中，DEPTH 的默认值是 5，WIDTH 的默认值是 2048。
三、analyze的使用
1.全量收集
ANALYZE TABLE TableNameList [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH|SAMPLES];
# eg : analyze table abc;
- WITH NUM BUCKETS 用于指定生成直方图的桶数量上限。
- WITH NUM TOPN 用于指定生成 TOPN 数目的上限。
- WITH NUM CMSKETCH DEPTH 用于指定 CM Sketch 的长。
- WITH NUM CMSKETCH WIDTH用于指定 CM Sketch 的宽。
- WITH NUM SAMPLES 用于指定采样的数目。
2.收集索引统计信息
ANALYZE TABLE TableName INDEX [IndexNameList] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH|SAMPLES];
# IndexNameList 为空时会收集所有索引列的统计信息
# analyze table test_1 index idx_abc;
3.收集分区统计信息
ANALYZE TABLE TableName PARTITION PartitionNameList [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH|SAMPLES];
4.收集索引和分区统计信息
ANALYZE TABLE TableName PARTITION PartitionNameList INDEX [IndexNameList] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH|SAMPLES];
- 为了保证前后统计信息的一致性，在设置 tidb_analyze_version=2 时，ANALYZE TABLE TableName
INDEX 也会收集整个表而不是所给索引的统计信息。
5.增量收集
对于类似时间列这样的单调不减列，在进行全量收集后，可以使用增量收集来单独分析新增的部分，以提高分析的速度。
注意：
- 目前只有索引提供了增量收集的功能
- 使用增量收集时，必须保证表上只有插入操作，且应用方需要保证索引列上新插入的值是单调不减的，否则会导致统计信息不准，影响 TiDB
优化器选择合适的执行计划
ANALYZE INCREMENTAL TABLE TableName INDEX [IndexNameList] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH|SAMPLES];
ANALYZE INCREMENTAL TABLE TableName PARTITION PartitionNameList INDEX [IndexNameList] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH|SAMPLES];
四、查看状态
1.analyze状态
SHOW ANALYZE STATUS [ShowLikeOrWhere];
# show analyze status;
# show analyze status where table_name='abc';
| 字段名 | 意义 |
|---|---|
| table_schema | 数据库名 |
| table_name | 表名 |
| partition_name | 分区名 |
| job_info | 任务具体信息。如果分析索引则会包含索引名 |
| row_count | 已经分析的行数 |
| start_time | 任务开始执行的时间 |
| state | 任务状态，包括 pending（等待）、running（正在执行）、finished（执行成功）和 failed（执行失败） |
2.表的元信息查看
SHOW STATS_META [ShowLikeOrWhere];
| 字段名 | 意义 |
|---|---|
| db_name | 数据库名 |
| table_name | 表名 |
| partition_name | 分区名 |
| update_time | 更新时间 |
| modify_count | 修改的行数 |
| row_count | 总行数 |
- 在 TiDB 根据 DML 语句自动更新总行数以及修改的行数时，update_time 也会被更新，因此并不能认为 update_time
是最近一次发生 Analyze 的时间。
3.表的健康度信息
通过 SHOW STATS_HEALTHY 可以查看表的统计信息健康度，并粗略估计表上统计信息的准确度。当 modify_count >= row_count 时，健康度为 0；当 modify_count < row_count 时，健康度为 (1 - modify_count/row_count) * 100。
SHOW STATS_HEALTHY
# SHOW STATS_HEALTHY where db_name='abc'
| 字段名 | 意义 |
|---|---|
| db_name | 数据库名 |
| table_name | 表名 |
| partition_name | 分区名 |
| healthy | 健康度 |
4.列的元信息
SHOW STATS_HISTOGRAMS [ShowLikeOrWhere];
- 该语句会输出所有列的不同值数量以及 NULL 数量等信息，你可以通过 ShowLikeOrWhere 来筛选需要的信息。
| 字段名 | 意义 |
|---|---|
| db_name | 数据库名 |
| table_name | 表名 |
| partition_name | 分区名 |
| column_name | 根据 is_index 来变化：is_index 为 0 时是列名，为 1 时是索引名 |
| is_index | 是否是索引列 |
| update_time | 更新时间 |
| distinct_count | 不同值数量 |
| null_count | NULL 的数量 |
| avg_col_size | 列平均长度 |
| correlation | 该列与整型主键的皮尔逊系数，表示两列之间的关联程度直方图桶的信息 |
5.直方图每个桶的信息
SHOW STATS_BUCKETS [ShowLikeOrWhere];
| 字段名 | 意义 |
|---|---|
| db_name | 数据库名 |
| table_name | 表名 |
| partition_name | 分区名 |
| column_name | 根据 is_index 来变化：is_index 为 0 时是列名，为 1 时是索引名 |
| is_index | 是否是索引列 |
| bucket_id | 桶的编号 |
| count | 所有落在这个桶及之前桶中值的数量 |
| repeats | 最大值出现的次数 |
| lower_bound | 最小值 |
| upper_bound | 最大值 |
| ndv | 当前桶内不同值的个数。当 tidb_analyze_version = 1 时，该值恒为 0，没有实际意义 |
6.top-N 信息
SHOW STATS_TOPN [ShowLikeOrWhere];
| 字段名 | 意义 |
|---|---|
| db_name | 数据库名 |
| table_name | 表名 |
| partition_name | 分区名 |
| column_name | 根据 is_index 来变化：is_index 为 0 时是列名，为 1 时是索引名 |
| is_index | 是否是索引列 |
| value | 该列的值 |
| count | 该值出现的次数 |
五、统计信息的其他操作
1.删除统计信息
DROP STATS TableName;
# DROP STATS abc
2.导出统计信息
# 导出json格式统计信息
http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}
# 导出指定时间的json格式统计信息，指定的时间应在 GC SafePoint 之后。
http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}/${yyyyMMddHHmmss}
# 导出指定时间的json格式统计信息，指定的时间应在 GC SafePoint 之后
http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}/${yyyy-MM-dd HH:mm:ss}
3.导入统计信息
LOAD STATS 'file_name';
# file_name 为要导入的统计信息的文件名
官网地址： https://docs.pingcap.com/zh/tidb/v4.0/statistics
类似资料：