054、查询优化之统计信息

统计信息原理

统计信息简介

执行计划是需要统计信息辅助的

统计信息的组成

• 表级别统计信息

  • 总行数
  • 表的监控情况

• 列级别统计信息

  • 直方图
  • Count-Min Stetch
  • 不通知的分布和数量
  • 空值的数量
    

直方图

用于判单数据分布的情况

数据分布情况
例如1000万行数据，999万行是2 1万行是1，则此时肯定不能通过选择性来判断，因为选择性只有2，但1这个数据是1万可以走索引.（当然2肯定是不行，会走全表）这个时候就可以用直方图来估算 数据范围。

TiDB用的是等深直方图。没个桶的高度是一样的，假设1.6 1.9 1.9 放一个桶里，接着3个值2.0 2.4 2.6也放一个桶 ，可以看到它的深度是一样的，但宽度不一样，
这个时候可以看到 1.6-1.9 的区间范围虽然没有2.0-2.6 大，但里面的值的个数是一样的，所以数据在1.6-1.9当中数据是更密集的。

对于范围扫描，尽量落在一个桶里扫描

Count-Min Sketch

用于判断数据的使用频率
判断某个值在列中使用的频率，更重要的是 它能统计一个实时的数据，例如 where nam=’tom’ ,则能大概判断出tom会出现的次数 。

它的思路是，tom 通过一个hash函数得到一个hash值 ，例如2
可能有个问题hash 碰撞， 其他值 jack可能也是得到 2
所以可能有多个hash函数

统计信息的收集

收集方法

它的收集方法类似MySQL

analyze table 表名 [index 索引名 | partition 分区名 索引名] [with num buckets | with num topn | with num cmsketch depth | witch num cmsketch width | with num samples]

• num buckets： 直方桶
• num topn: 最大值
• num cmsketch depth/width : hash函数相关,这两个值越大，发生哈希碰撞的概率越小，频率统计越精准
• samples: 采样率，如果100% 则是最准，所有数据都采样

控制analyze的并行度

系统变量名	默认值	功能
tidb_build_stats_concurrency	4	analyze执行的时候会切分成一个个小的任务，每个任务只负责某一个列或者索引。 这个参数可以控制同时执行的任务数量
tidb_distsql_scan_concurrency	15	在执行分析普通列任务的时候，这个参数用于控制一次读取的Region数量
tidb_index_serial_scan_concurrency	1	在执行分析索引列任务的时候，这个参数用于控制一次读取的region数量

mysql> show variables like 'tidb_build%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| tidb_build_stats_concurrency | 4     |
+------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'tidb_distsql%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| tidb_distsql_scan_concurrency | 15    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'tidb_index_serial%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| tidb_index_serial_scan_concurrency | 1     |
+------------------------------------+-------+
1 row in set (0.00 sec)

自动更新统计信息

每60秒会自动触发一次自动更新的任务。
tidb_auto_analyze_start_time/tidb_auto_analyze_end_time: 指定的时间范围内，才可能触发的自动更新任务。
tidb_auto_analyze_ratio : 当表的修改行数/总行数 大于0.5的时候，且在上面设置的时间范围内，自动更新任务会将这些符合条件的表进行处理。

在发生增删改的时候，自动更新任务可能会将这些对象进行统计信息的重新收集。

查看自动收集任务的详细信息

show analyze status;

查看表的统计信息

查看表的元信息

mysql> show stats_meta where db_name='universe' and table_name='planets';
+----------+------------+----------------+---------------------+--------------+-----------+
| Db_name  | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+----------+------------+----------------+---------------------+--------------+-----------+
| universe | planets    |                | 2023-06-06 23:22:08 |           10 |        10 |
+----------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

查看表的健康状态

mysql> show stats_healthy where db_name='universe' and table_name='planets';
+----------+------------+----------------+---------+
| Db_name  | Table_name | Partition_name | Healthy |
+----------+------------+----------------+---------+
| universe | planets    |                |       0 |
+----------+------------+----------------+---------+
1 row in set (0.00 sec)

Healthy 低于tidb_auto_analyze_ratio 的时候，且处于指定之间范围内，会自动收集统计信息。

查看列的元信息

mysql> show stats_histograms where db_name='universe' and table_name='planets';
+----------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name  | Table_name | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation |
+----------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| universe | planets    |                | name        |        1 | 2023-06-06 23:21:17 |              0 |          0 |            0 |           0 |
+----------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
1 row in set (0.00 sec)

查看直方图图信息

show stats_buckets where …

导入导出统计信息

实验

通过参数控制analyze table(统计信息收集)的速度并使用统计信息的导入导出功能
1、造数

mysql> create table t1(a int,b int ,index idx(b));
Query OK, 0 rows affected (0.16 sec)

[root@tidb2 ~]# for i in `seq 10000`; do mysql -uroot -P4000 -h192.168.16.13 -pAa123ab! -e "insert into test.t1 values ($i,floor(rand()*10000000))"; done;

[root@tidb2 ~]# for i in `seq 10`; do mysql -uroot -P4000 -h192.168.16.13 -pAa123ab! -e "insert into test.t1 select * from test.t1 limit 100000"; done;

2、收集表的统计信息

mysql> analyze table test.t1;

3、继续造数

[root@tidb2 ~]# for i in `seq 20`; do mysql -uroot -P4000 -h192.168.16.13 -pAa123ab! -e "insert into test.t1 select * from test.t1 limit 100000"; done;

4、查看表的健康状态

mysql> show stats_healthy where db_name= 'test' and table_name= 't1';
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test    | t1         |                |      61 |
+---------+------------+----------------+---------+
1 row in set (0.07 sec)

灌入大量数据后，表的健康状态可能会发生改变，因为开启了自动表分析功能。

5、重新收集统计信息

mysql> analyze table t1;
Query OK, 0 rows affected, 1 warning (15.02 sec)

6、调整analyze相关参数

set global tidb_build_stats_concurrency = 16;
set global tidb_distsql_scan_concurrency = 64;

7、重新收集统计信息

mysql> analyze table t1;
Query OK, 0 rows affected, 1 warning (9.89 sec)

实验二： 导入导出统计信息

mysql> analyze table t1;
Query OK, 0 rows affected, 1 warning (13.54 sec)

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

导出统计信息

[root@tidb2 ~]# curl http://192.168.16.13:10080/stats/dump/test/t1 > t1_static.json

删掉表上的统计信息

mysql> drop table test.t1;
Query OK, 0 rows affected (0.28 sec)

导入统计信息

mysql> load stats 't1_static.json';