本文是分享 OBCE V3 培训的理论和实验实践的第六篇，主要介绍SQL优化相关的表的统计信息。个人理解，不当之处欢迎讨论。
1. 理论部分
1.1 统计信息概念
数据库系统中的统计信息是用于优化查询计划的重要工具。ORACLE 数据库是最早引入并广泛使用统计信息来优化查询计划的数据库系统之一。ORACLE 数据库中的优化器（Optimizer）使用统计信息，如表的行数、列的唯一值数量、索引的高、低值等，来估算查询计划的成本，并选择执行计划中最有效的路径。
统计信息在 SQL优化中的作用已经深入人心，开发碰到SQL性能问题总会想到是不是要收集一下统计信息。这倒不是觉得统计信息能治百病，而是因为收集统计信息这个操作比较简单容易为人所掌握且大部分时候效果还是很好的。然而统计信息对 SQL 性能实则是一把双刃剑。收集了表的统计信息有的 SQL 性能可能会变好，有的 SQL 性能可能会变坏。决定 SQL 执行性能的是执行计划和数据库资源，统计信息如果把 SQL 执行计划引导到符合预期的执行计划上，则性能会变好；反之，则性能变坏。在复杂的 SQL 上，就执行计划的好与坏这个问题，人与数据库，人与人之间的意见也并不是总那么容易统一的。不过大部分场景，大家都倾向于数据库优化器的判断还是好的。
当 SQL 中条件的值是具体的值时，结合统计信息，基于成本的优化器（CBO）就能计算出条件的选择率和返回行数，从而选出适合的执行计划。不过这种 SQL 对执行计划缓存影响很大，复用概率小。数据库更建议应用 SQL 使用绑定变量。然后优化器使用绑定变量窥探技术获取 SQL 第一次出现的时候的参数值，根据那个参数值生成执行计划。如果这个参数能代表大部分查询场景，这个执行计划对大部分 SQL 都是好的；如果代表不了，则这个执行计划会导致大部分场景 SQL 变慢。优化器也在改进，当发现实际执行计划读取的行数跟执行计划估算的差异很大的时候，也会重新进行绑定变量窥探，再生成一次执行计划替换之前的执行计划，或者生成一个新版本的执行计划。一个 SQL 可能经常会出现多个版本的执行计划（子游标）。同样的，这个窥探的参数是否具有代表性决定了对应的执行计划的通用性。
由此可见，SQL 优化器使用统计信息和基于成本的规则来为 SQL 中各种查询条件和参数组合来生成合适的执行计划，为了降低 SQL 硬解析对性能的影响使用了执行计划缓存这个机制，然后又使用绑定变量窥探来弥补使用执行计划缓存和绑定变量带来的问题。ORACLE 还推出了 SQL监视、 SQL 隔离技术来完善优化器的功能。无论优化器怎么发展，总有它不完美的地方。当客户碰到了，就可能是一次生产性能故障（可大可小）。ORACLE 从来就没有停止发展它的优化器技术。
以上是 ORACLE SQL 优化器的特点和实践经验。在国产数据库上包括 OB 也是一样的。国内数据库研发力量在发展，ORACLE SQL 功能的兼容现在好像也没什么门槛了，就算是存储过程，基于开源的数据库开发也能很好兼容 ORACLE 了。那么下一个门槛就是 SQL 优化器了（其他还有存储和事务引擎的高级功能）。
1.3 OB 的统计信息
OB 的优化器也在发展，可以看出基本功能有参考 ORACLE 优化器（还没有完全赶上），同时由于是分布式架构，所以也有很多独特之处（分布式执行计划、表分组等、SQL路由等）。单说统计信息功能，OB 2.2 的统计信息还不成熟，只在 OB 每日大合并（MAJOR FREEZE）的时候生成。这个统计信息有比没有好。不过也有客户的性能问题就出在合并之后因为统计信息的变化导致所有 SQL 执行计划重新生成，个别 SQL 的绑定变量窥探的参数（此处不严谨，后面文章更正）不具代表性导致 SQL 执行计划走偏。对于那些对事务和 SQL 延时很敏感的业务，这个容易产生故障。OB 支持 SQL 大纲（OUTLINE）功能，可以将好的执行计划在线绑定到 SQL，立即快速的修复这个问题。
OB 3.2 里统计信息相对比较完善了，有相应的视图可以查看统计信息，也有相应的手动收集统计信息的方法。不过视图名字还不稳定，后面 4.0 之后视图名字又换了（应该能稳定了）。统计信息收集方法有 ORACLE 和 MySQL 两种语法，4.2 版本支持在 MySQL 里开启 ORACLE SQL 扩展以使用 ORACLE 的语法。刚开始接触这些会有点混乱的感觉。
每日合并时还是会自动更新统计信息。推测可能因为大部分客户的心声还是统计信息的收集要自己可控，每日合并更新统计信息这个变数太大，以及为了缩短合并的时间，OB 4.0 后剥离了合并后的统计信息更新这个操作。统计信息有自动收集统计信息的作业去做，此外也可以 DBA 手动去收集。
除了这个统计信息外，OB 还有自己的创新，就是数据在写入到存储层后存储引擎会自动收集一些基础的数据。如 行数、大小等信息。对于索引，OB 存储引擎会自动收集索引第一列的统计信息（包括 NDV(Number of Distinct Values 、值的 MIN 和 MAX 值、值的分布特点等）。存储引擎层的这个统计信息还非常的精准，也有利于 OB 优化器生成合适的执行计划。这个存储引擎层列的统计信息目前没有找到视图或内部表可以看。
2.实验部分
尽管统计信息不是完美的，但是是必需的。所以还是有必要掌握它的规律。运用得当，还是能降低线上应用性能风险。
此次实验内容比较多，官方给了三个小时。
2.1 环境信息
本实验环境跟上次实验环境一样，是个3节点的 OB 集群。具体环境初始化方法介绍请参考上篇文章《OBCE V3 培训：应用到数据库全链路优化实验部分》。这里就略去了。
2.2 OB 3.2 测试数据准备
1) 创建非分区表并初始化数据。
create table chap41 (c1 int primary key, c2 int, c3 varchar(3));insert into chap41 select level, mod(level,10) ,'001' from dual connect by level<=10000;insert into chap41 values (99999,999,'999');commit;obclient [TPCC]> select c2,count(*) from chap41 group by c2;+------+----------+| C2 | COUNT(*) |+------+----------+| 1 | 1000 || 2 | 1000 || 3 | 1000 || 4 | 1000 || 5 | 1000 || 6 | 1000 || 7 | 1000 || 8 | 1000 || 9 | 1000 || 0 | 1000 || 999 | 1 |+------+----------+11 rows in set (0.016 sec)
这里刻意构造了一笔数据分布倾斜严重的参数值 999 。
2.3 统计信息查看方法
目前在 SYS 租户和 业务租户里都能查到不同的统计信息。
在 SYS 租户下表和列的统计信息主要是查看内部表 __all_virtual_meta_table 和 __all_virtual_column_statistic 。这个统计信息指 OB 在合并时收集的统计信息，不是指手动收集的统计信息。
所以新建的表或新初始化数据的表，在开始的时候这两个视图里关于行数和大小信息都是空的。先记录 SQL 如下。
SELECT /*+ use_nl(t t2) leading(t2) */ t.tenant_id, t.table_id, t2.table_name, t.partition_id, t.svr_ip, t.ROLE, t.row_count, t.data_sizeFROM oceanbase.__all_virtual_meta_table tRIGHT JOIN __all_virtual_table t2 ON (t.tenant_id = t2.tenant_id AND t.table_id = t2.table_id)WHERE t.tenant_id = 1001AND t2.table_name IN ('CHAP41')AND t.ROLE=1;-- 上面 SQL 中取出的 table_id 用于下面 SQL 中条件。SELECT /*+ use_nl(c c2) leading (c2) */ c.tenant_id, c.table_id, c.partition_id, c2.column_name, c.num_distinct, c.num_null, c.min_value, c.max_value, c.version, c.last_rebuild_versionFROM oceanbase.__all_virtual_column_statistic c RIGHT JOIN oceanbase.__all_virtual_column c2 ON (c.tenant_id=c2.tenant_id AND c.table_id=c2.table_id AND c.column_id=c2.column_id )WHERE c2.tenant_id = 1001AND c2.table_id IN (1100611139453777 );;
在 ORACLE 租户下表和列的统计信息主要是查看视图 sys.all_tab_statistics 、sys.all_tab_col_statistics 和 sys.all_tab_histograms 。这个统计信息指手动用 analyze 或 dbms_stats 命令收集的统计信息。
同样，上面表的统计信息都是空的。先记录查看 SQL 如下。
SELECT owner, table_name, partition_name, partition_position, object_type, num_rows, blocks, avg_row_len, sample_size, last_analyzedFROM sys.all_tab_statisticsWHERE owner IN ('TPCC')AND table_name IN ('CHAP41');SELECT owner, table_name, column_name, num_distinct, low_value, high_value, density, num_nulls, num_buckets, last_analyzed, sample_size,avg_col_len, histogramFROM sys.all_tab_col_statisticsWHERE owner IN ('TPCC')AND table_name IN ('CHAP41');SELECT owner, table_name, column_name, endpoint_number, endpoint_actual_value, endpoint_repeat_countFROM sys.all_tab_histogramsWHERE owner IN ('TPCC')AND table_name IN ('CHAP41');
官方文档还提供了第三种方法，这个估计是 OB 发展中的产物，后续某个版本可能就会禁用掉这个用法。在业务租户里查看内部表 __all_table_stat_v2 和 __all_column_stat_v2 。不过如果你直接去业务租户看这两个表，会报错（因为表不存在）。必须通过 SYS 租户登录，然后切换到业务租户。
[root@iZuf62kxllrryxtp95my2qZ t-oceanbase-antman]# obclient -h172.16.1.32 -uroot@sys#obce_test -P2883 -p -c -A oceanbaseEnter password:Welcome to the OceanBase. Commands end with ; or \g.Your OceanBase connection id is 12Server version: OceanBase 3.2.3.0 (r20220622171245-f15608aa0e19cdbf0179653199bca5d4b741b3ef) (Built Jun 22 2022 17:23:07)obclient [oceanbase]> alter system change tenant ob_pay;Query OK, 0 rows affected (0.001 sec)obclient [oceanbase]> SELECT t.table_id , t.table_name , partition_id , object_type , last_analyzed , sstable_row_cnt , sstable_avg_row_len , memtable_row_cnt , memtable_avg_row_len, row_cnt , avg_row_len-> FROM oceanbase.__all_table_stat_v2 s-> RIGHT JOIN oceanbase.__all_table_v2 t ON-> (s.table_id = t.table_id)-> WHERE t.table_name IN ('CHAP41')-> ;+----------+------------+--------------+-------------+---------------+-----------------+---------------------+------------------+----------------------+---------+-------------+| table_id | table_name | partition_id | object_type | last_analyzed | sstable_row_cnt | sstable_avg_row_len | memtable_row_cnt | memtable_avg_row_len | row_cnt | avg_row_len |+----------+------------+--------------+-------------+---------------+-----------------+---------------------+------------------+----------------------+---------+-------------+| 50001 | CHAP41 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |+----------+------------+--------------+-------------+---------------+-----------------+---------------------+------------------+----------------------+---------+-------------+1 row in set (0.014 sec)obclient [oceanbase]> SELECT /*+ use_nl(s c) leading (c) */ c.table_id , c.column_name ,s.distinct_cnt ,s.min_value ,s.max_value ,s.null_cnt ,s.last_analyzed-> FROM oceanbase.`__all_column_stat_v2` s-> RIGHT JOIN oceanbase.__all_column c ON (s.table_id=c.table_id AND s.column_id=c.column_id)-> WHERE c.table_id IN (50001)-> ;+----------+-------------+--------------+-----------+-----------+----------+---------------+| table_id | column_name | distinct_cnt | min_value | max_value | null_cnt | last_analyzed |+----------+-------------+--------------+-----------+-----------+----------+---------------+| 50001 | C1 | NULL | NULL | NULL | NULL | NULL || 50001 | C2 | NULL | NULL | NULL | NULL | NULL || 50001 | C3 | NULL | NULL | NULL | NULL | NULL |+----------+-------------+--------------+-----------+-----------+----------+---------------+3 rows in set (0.009 sec)obclient [oceanbase]>
这两个内部表的内容格式跟前面业务租户下的那两个内部表格式基本一致。
2.4 没有任何统计信息下的执行计划
在目前没有合并也没有手动收集统计信息的情况下，看看下面 3 个 SQL 的执行计划里估算行数信息。为节省篇幅，执行计划其他部分信息略去。
obclient [TPCC]> explain extended select * from chap41 where c2=1 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|100 |4081|=====================================Optimization Info:-------------------------------------CHAP41:table_rows:10003, physical_range_rows:10003, logical_range_rows:10003, index_back_rows:0, output_rows:99, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[CHAP41], estimation info[table_id:1100611139453778, (table_type:1, version:0-1699932710822297-1699932710822297, logical_rc:0, physical_rc:0), (table_type:0, version:1699932710822297-1699932710822297-9223372036854775807, logical_rc:10003, physical_rc:10003)]Parameters-------------------------------------1 row in set (0.002 sec)
从这个可以看出，优化器还是拿到了表具体的行数，应该是存储层收集的。后面每个 SQL 里都能看到这个行数信息，就略去不显示了。
至于选择率，优化器由于没有更多统计信息，默认按 1/100 计算。
obclient [TPCC]> explain select * from chap41 where c2=999 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|100 |4081|=====================================
即使更换了查询条件，选择率依然是 1/100 。优化器由于缺乏直方图信息，所以无法判断不同参数的区别。
但是，如果此时创建一个索引（c2,c3) ，上面执行计划又会发生变化。
obclient [TPCC]> create index c2_c3 on chap41(c2,c3);Query OK, 0 rows affected (0.703 sec)obclient [TPCC]> explain select * from chap41 where c2=1 \G*************************** 1. row ***************************Query Plan: ============================================|ID|OPERATOR |NAME |EST. ROWS|COST|--------------------------------------------|0 |TABLE SCAN|CHAP41(C2_C3)|1000 |387 |============================================obclient [TPCC]> explain select * from chap41 where c2=999 \G*************************** 1. row ***************************Query Plan: ============================================|ID|OPERATOR |NAME |EST. ROWS|COST|--------------------------------------------|0 |TABLE SCAN|CHAP41(C2_C3)|1 |46 |============================================obclient [TPCC]> explain select * from chap41 where c3= '999' \G*************************** 1. row ***************************Query Plan: ============================================|ID|OPERATOR |NAME |EST. ROWS|COST|--------------------------------------------|0 |TABLE SCAN|CHAP41(C2_C3)|100 |3929|============================================
可以看到，列 C2 的选择率发生变化，并且跟实际参数值有关，非常准确的区分了数据倾斜极端值。但是这个仅限于列 C2 。因为 C2 是索引里第一列。
上面最后一个执行计划 C3 的查询走了索引 C2_C3 这个比较奇怪，在 ORACLE 里这个算子是 INDEX SKIP SCAN ， OB 3.2 应该还没有实现，还不确认。这里的 EST. ROWS 估算依然使用的是默认的选择率 1/100 。
后面删除索引，继续实验。
2.5 只合并不收集统计信息下的执行计划
在 SYS 租户发起合并（major freeze）。合并结束后，查看 SYS 租户下那两个内部表。合并需要点时间。可以通过下面 SQL 查看合并进度。
SELECT gmt_create ,module,event ,name1 ,value1 ,name2,value2,name3,value3,name4,value4FROM `__all_rootservice_event_history`WHERE 1=1AND module NOT IN ('schema_recycler')AND module in ('daily_merge')ORDER BY gmt_create DESC LIMIT 10;
合并看到 global_merged 表示合并结束了。
查看 统计信息。这轮实验测试数据漏了一笔极端值 999 ，导致结果有些差异。下面取的是内部测试环境的测试记录。
obclient [oceanbase]> SELECT /*+ use_nl(t t2) leading(t2) */ t.table_id, t2.table_name, t.svr_ip, t.ROLE, t.row_count, t.data_size-> FROM oceanbase.__all_virtual_meta_table t-> RIGHT JOIN __all_virtual_table t2 ON (t.tenant_id = t2.tenant_id AND t.table_id = t2.table_id)-> WHERE t.tenant_id = 1001-> AND t2.table_name IN ('CHAP41')-> AND t.ROLE=1-> ;+------------------+------------+--------------+-----------+------+-----------+-----------+| table_id | table_name | partition_id | svr_ip | ROLE | row_count | data_size |+------------------+------------+--------------+-----------+------+-----------+-----------+| 1102810162710153 | CHAP41 | 0 | 10.0.0.61 | 1 | 10001 | 11868 |+------------------+------------+--------------+-----------+------+-----------+-----------+1 row in set (9.568 sec)obclient [oceanbase]> SELECT /*+ use_nl(c c2) leading (c2) */ c.table_id, c.partition_id, c2.column_name, c.num_distinct, c.num_null, c.min_value, c.max_value, c.version, c.last_rebuild_version-> FROM oceanbase.__all_virtual_column_statistic c RIGHT JOIN oceanbase.__all_virtual_column c2 ON (c.tenant_id=c2.tenant_id AND c.table_id=c2.table_id AND c.column_id=c2.column_id )-> WHERE c2.tenant_id = 1001-> AND c2.table_id IN (1100611139453777 );+------------------+--------------+-------------+--------------+----------+--------------------------+--------------------------+---------+----------------------+| table_id | partition_id | column_name | num_distinct | num_null | min_value | max_value | version | last_rebuild_version |+------------------+--------------+-------------+--------------+----------+--------------------------+--------------------------+---------+----------------------+| 1102810162710153 | 0 | C1 | 9632 | 0 | 0F053FFFC000000101000000 | 0F053FFFC00000019F860100 | 226 | 226 || 1102810162710153 | 0 | C2 | 11 | 0 | 0F053FFF80000000 | 0F053FFFC0000001E7030000 | 226 | 226 || 1102810162710153 | 0 | C3 | 2 | 0 | 16022EFF0330303100 | 16022EFF0339393900 | 226 | 226 |+------------------+--------------+-------------+--------------+----------+--------------------------+--------------------------+---------+----------------------+3 rows in set (0.058 sec)
可以看到关于行数、列的NDV值，最小和最大值等信息。
查看 SQL 的执行计划。
(TPCC@10.0.0.61:2883) [TPCC]> explain select * from chap41 where c2=1 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|910 |4080|=====================================
(TPCC@10.0.0.61:2883) [TPCC]> explain select * from chap41 where c2=999 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|910 |4080|=====================================
(TPCC@10.0.0.61:2883) [TPCC]> explain select * from chap41 where c3= '999' \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|5001 |3929|=====================================
分析结论：
获取了统计信息后，列 C2 的选择率用 1/11 估算。但是不区分列条件的实际值。 列 C3 同理，选择率用 1/2 估算，也没有区分条件的实际值。
此时，如果创建索引再合并，那存储层会自动收集索引列的统计信息（包括直方图），上面使用 C2 列的 执行计划就会变为前面的 2.4 展示的那种 。而列 C3 的执行计划变回 全表扫描了。并且优化器估算的选择率是 1/2 。这是合并后的列统计信息起作用了，但是不区分实际参数值。
obclient [TPCC]> explain select * from chap41 where c3= '001' \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|5001 |3929|=====================================obclient [TPCC]> explain select * from chap41 where c3= '999' \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|5001 |3929|=====================================
2.6 不合并先收集统计信息 下的执行计划
重新创建表并初始化数据，此时不合并，先收集普通的统计信息（指不收集直方图）。
obclient [TPCC]> analyze table chap41 compute statistics ;Query OK, 0 rows affected (0.175 sec)
查看业务租户里统计信息视图。两种方法都看看。
方法一：通过 SYS 租户切换到业务租户看。
obclient [oceanbase]> SELECT t.table_id , t.table_name , partition_id , object_type , last_analyzed , sstable_row_cnt , sstable_avg_row_len , memtable_row_cnt , memtable_avg_row_len, row_cnt , avg_row_len-> FROM oceanbase.__all_table_stat_v2 s-> RIGHT JOIN oceanbase.__all_table_v2 t ON-> (s.table_id = t.table_id)-> WHERE t.table_name IN ('CHAP41')-> ;+----------+------------+--------------+-------------+----------------------------+-----------------+---------------------+------------------+----------------------+---------+-------------+| table_id | table_name | partition_id | object_type | last_analyzed | sstable_row_cnt | sstable_avg_row_len | memtable_row_cnt | memtable_avg_row_len | row_cnt | avg_row_len |+----------+------------+--------------+-------------+----------------------------+-----------------+---------------------+------------------+----------------------+---------+-------------+| 50004 | CHAP41 | 0 | 1 | 2023-11-14 12:36:45.464826 | -1 | -1 | -1 | -1 | 10001 | 59 |+----------+------------+--------------+-------------+----------------------------+-----------------+---------------------+------------------+----------------------+---------+-------------+1 row in set (0.006 sec)obclient [oceanbase]> SELECT /*+ use_nl(s c) leading (c) */ c.table_id , c.column_name ,s.distinct_cnt ,s.min_value ,s.max_value ,s.null_cnt ,s.last_analyzed-> FROM oceanbase.`__all_column_stat_v2` s-> RIGHT JOIN oceanbase.__all_column c ON (s.table_id=c.table_id AND s.column_id=c.column_id)-> WHERE c.table_id IN (50004)-> ;+----------+-------------+--------------+-----------+-----------+----------+----------------------------+| table_id | column_name | distinct_cnt | min_value | max_value | null_cnt | last_analyzed |+----------+-------------+--------------+-----------+-----------+----------+----------------------------+| 50004 | C1 | 10191 | 1 | 99999 | 0 | 2023-11-14 12:36:45.479129 || 50004 | C2 | 11 | 0 | 999 | 0 | 2023-11-14 12:36:45.479129 || 50004 | C3 | 2 | '001' | '999' | 0 | 2023-11-14 12:36:45.479129 |+----------+-------------+--------------+-----------+-----------+----------+----------------------------+3 rows in set (0.007 sec)
方法二：直接看业务租户。
obclient [TPCC]> SELECT table_name, object_type, num_rows, blocks, avg_row_len, sample_size, last_analyzed-> FROM sys.all_tab_statistics-> WHERE owner IN ('TPCC')-> AND table_name IN ('CHAP41');+------------+-------------+----------+--------+-------------+-------------+---------------+| TABLE_NAME | OBJECT_TYPE | NUM_ROWS | BLOCKS | AVG_ROW_LEN | SAMPLE_SIZE | LAST_ANALYZED |+------------+-------------+----------+--------+-------------+-------------+---------------+| CHAP41 | TABLE | 10001 | NULL | 59 | NULL | 14-NOV-23 |+------------+-------------+----------+--------+-------------+-------------+---------------+1 row in set (0.040 sec)obclient [TPCC]> SELECT table_name, column_name, num_distinct, low_value, high_value, density, num_nulls, num_buckets, last_analyzed, sample_size, histogram-> FROM sys.all_tab_col_statistics-> WHERE owner IN ('TPCC')-> AND table_name IN ('CHAP41');+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+-----------+| TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | HISTOGRAM |+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+-----------+| CHAP41 | C1 | 10191 | 1 | 99999 | 0 | 0 | 0 | 14-NOV-23 | -1 | NULL || CHAP41 | C2 | 11 | 0 | 999 | 0 | 0 | 0 | 14-NOV-23 | -1 | NULL || CHAP41 | C3 | 2 | '001' | '999' | 0 | 0 | 0 | 14-NOV-23 | -1 | NULL |+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+-----------+3 rows in set (0.039 sec)obclient [TPCC]> SELECT table_name, column_name, endpoint_number, endpoint_actual_value, endpoint_repeat_count-> FROM sys.all_tab_histograms-> WHERE owner IN ('TPCC')-> AND table_name IN ('CHAP41');Empty set (0.027 sec)
可以看到 compute statistics 收集了表的记录数、列的 NDV 值，MIN 和 MAX 值等基础信息，但没有收集列的直方图信息（列 density 和 histogram 为 0 或空）。列 C2 的 NDV 值是 11，不是之前合并时得到的 10 。
此时查看 SQL 执行计划。
obclient [TPCC]> explain select * from chap41 where c2=1 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|910 |4080|=====================================obclient [TPCC]> explain select * from chap41 where c2=999 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|910 |4080|=====================================obclient [TPCC]> explain select * from chap41 where c3= '999'\G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|5001 |3930|=====================================
手动收集的统计信息跟合并自动收集的统计信息大体上是相同的，列C2的选择率有细微的差别，所以估算行数也变化了一些。由于列 C2 和 C3 都缺乏直方图信息，所以不同条件的选择率没有变化。
下面接着收集列 C2 和 C3 的直方图信息。
obclient [TPCC]> analyze table chap41 compute statistics for columns c2,c3 ;Query OK, 0 rows affected (0.153 sec)
查看列的统计信息。
obclient [TPCC]> SELECT table_name, column_name, num_distinct, low_value, high_value, density, num_nulls, num_buckets, last_analyzed, sample_size, histogram-> FROM sys.all_tab_col_statistics-> WHERE owner IN ('TPCC')-> AND table_name IN ('CHAP41');+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+---------------+-------------+-----------+| TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | HISTOGRAM |+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+---------------+-------------+-----------+| CHAP41 | C1 | 10191 | 1 | 99999 | 0 | 0 | 0 | 14-NOV-23 | -1 | NULL || CHAP41 | C2 | 11 | 0 | 999 | .000050000000000000002 | 0 | 11 | 14-NOV-23 | 10001 | FREQUENCY || CHAP41 | C3 | 2 | '001' | '999' | .000050000000000000002 | 0 | 2 | 14-NOV-23 | 10001 | FREQUENCY |+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+---------------+-------------+-----------+3 rows in set (0.003 sec)obclient [TPCC]> SELECT table_name, column_name, endpoint_number, endpoint_actual_value, endpoint_repeat_count-> FROM sys.all_tab_histograms-> WHERE owner IN ('TPCC')-> AND table_name IN ('CHAP41');+------------+-------------+-----------------+-----------------------+-----------------------+| TABLE_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_ACTUAL_VALUE | ENDPOINT_REPEAT_COUNT |+------------+-------------+-----------------+-----------------------+-----------------------+| CHAP41 | C2 | 1000 | 0 | 1000 || CHAP41 | C2 | 2000 | 1 | 1000 || CHAP41 | C2 | 3000 | 2 | 1000 || CHAP41 | C2 | 4000 | 3 | 1000 || CHAP41 | C2 | 5000 | 4 | 1000 || CHAP41 | C2 | 6000 | 5 | 1000 || CHAP41 | C2 | 7000 | 6 | 1000 || CHAP41 | C2 | 8000 | 7 | 1000 || CHAP41 | C2 | 9000 | 8 | 1000 || CHAP41 | C2 | 10000 | 9 | 1000 || CHAP41 | C2 | 10001 | 999 | 1 || CHAP41 | C3 | 10000 | '001' | 10000 || CHAP41 | C3 | 10001 | '999' | 1 |+------------+-------------+-----------------+-----------------------+-----------------------+13 rows in set (0.003 sec)
可以看出收集直方图信息时不指定类型，这里用了 频率（FREQUENCE) 直方图。
此时再看 SQL 执行计划的变化。
obclient [TPCC]> explain select * from chap41 where c2=1 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|1001 |4080|=====================================obclient [TPCC]> explain select * from chap41 where c2=999 \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|2 |4080|=====================================obclient [TPCC]> explain select * from chap41 where c3= '999' \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|2 |3930|=====================================obclient [TPCC]> explain select * from chap41 where c3= '001' \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|10001 |3930|=====================================obclient [TPCC]> explain select * from chap41 where c3= '002' \G*************************** 1. row ***************************Query Plan: =====================================|ID|OPERATOR |NAME |EST. ROWS|COST|-------------------------------------|0 |TABLE SCAN|CHAP41|1 |3930|=====================================
有了直方图信息后，列 C2 和 C3 面对不同的参数值选择率就不一样了。列 C3 对于不存在的记录估算行数给出 1 。
如果再创建索引后，估算行数有点微小变化。这个细节还需要再分析看看。
obclient [TPCC]> explain select * from chap41 where c2=1\G*************************** 1. row ***************************Query Plan: ============================================|ID|OPERATOR |NAME |EST. ROWS|COST|--------------------------------------------|0 |TABLE SCAN|CHAP41(C2_C3)|1000 |387 |============================================obclient [TPCC]> explain select * from chap41 where c2=999\G*************************** 1. row ***************************Query Plan: ============================================|ID|OPERATOR |NAME |EST. ROWS|COST|--------------------------------------------|0 |TABLE SCAN|CHAP41(C2_C3)|1 |1 |============================================obclient [TPCC]> explain extended select * from chap41 where c2=999\G*************************** 1. row ***************************Query Plan: ============================================|ID|OPERATOR |NAME |EST. ROWS|COST|--------------------------------------------|0 |TABLE SCAN|CHAP41(C2_C3)|1 |4 |============================================Outputs & filters:-------------------------------------0 - output([CHAP41.C1(0x7f35744c4960)], [CHAP41.C2(0x7f35744c45b0)], [CHAP41.C3(0x7f35744c4c50)], [CHAP41.C4(0x7f35744c4f40)]), filter(nil),access([CHAP41.C2(0x7f35744c45b0)], [CHAP41.C1(0x7f35744c4960)], [CHAP41.C3(0x7f35744c4c50)], [CHAP41.C4(0x7f35744c4f40)]), partitions(p0),is_index_back=true,range_key([CHAP41.C2(0x7f35744c45b0)], [CHAP41.C3(0x7f35744c4c50)], [CHAP41.C1(0x7f35744c4960)]), range(999,MIN,MIN ; 999,MAX,MAX),range_cond([CHAP41.C2(0x7f35744c45b0) = 999(0x7f35744c3e90)])Optimization Info:-------------------------------------CHAP41:table_rows:10001, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[C2_C3], unstable_index_name[CHAP41], estimation info[table_id:1100611139453786, (table_type:1, version:0-1699940414713963-1699940414713963, logical_rc:1, physical_rc:1), (table_type:0, version:1699936084302357-1699936084302357-9223372036854775807, logical_rc:0, physical_rc:0)]Parameters-------------------------------------1 row in set (0.002 sec)
在没有合并之前，最后一个SQL还是扫描了索引（索引并不包含表的全部字段）。这个后面再看看 。
3. OB 4.2 统计信息的变化
OB 4.2 里，统计信息可以从业务租户的表 __all_table_stat 和 __all_column_stat 里查看，也可以通过视图 DBA_*_STATISTICS 视图查看。
SELECT /*+ use_nl(s t) leading (t) */ t.table_id, t.table_name ,s.partition_id , s.last_analyzed , s.sstable_row_cnt , sstable_avg_row_len , memtable_row_cnt ,memtable_avg_row_lenFROM oceanbase.`__all_table_stat` s RIGHT JOIN oceanbase.`__all_table` t ON (s.table_id=t.table_id)WHERE t.table_name = 'chap41';SELECT /*+ use_nl(c s) leading(c) */ c.table_id , s.partition_id , c.column_name, s.last_analyzed , s.distinct_cnt , s.null_cnt , s.min_value ,s.max_value ,s.avg_len ,s.density , s.bucket_cnt , s.histogram_typeFROM oceanbase.`__all_column_stat` s RIGHT JOIN oceanbase.__all_column c ON (s.table_id=c.table_id AND s.column_id=c.column_id)WHERE c.table_id IN (500004);
SELECT owner,table_name,object_type,num_rows,blocksFROM oceanbase.DBA_TAB_STATISTICSWHERE table_name = 'CHAP41';SELECT owner, table_name, column_name, num_distinct, low_value,high_value, density, num_nulls,num_buckets, last_analyzed, sample_size,avg_col_len,histogramFROM oceanbase.DBA_TAB_COL_STATISTICSWHERE table_name='CHAP41';SELECT owner,table_name,column_name,endpoint_number,endpoint_actual_value,endpoint_repeat_count FROM oceanbase.DBA_TAB_HISTOGRAMSWHERE table_name='CHAP41';
统计信息收集命令主要是 analyze table 和 dbms_stats.gather_table_stats ，具体命令帮助可以看官网文档。
目前测试初步感觉 4.2 的 analyze table compute statistics 似乎会自动收集列的统计信息（NDV值，MIN/MAX值，直方图信息）。但具体收集哪些列的行为还不确定，多次测试观察的结果不一样。
4. 总结
OB 3.2 在统计信息方面的规律如下：
存储层会实时的收集表的统计信息（行数），不收集列的统计信息。默认列的选择率都是 1/100 . 存储层会收集表的索引的第一列的统计信息（NDV值，MIN/MAX值，值的分布特点），这个会改变这列作为等值条件时的选择率估算。 OB 合并后会收集表和列的统计信息（包括行数、列的NDV值，MIN/MAX 值，但是不会收集列的数据分布特点）。这个会影响列的选择率估算。不过索引的第一列的选择率依然是看存储层收集的统计信息（因为多了个数据分布的信息）。如果收集统计信息的同时收集了列的直方图信息，目前还没看出跟存储层索引头列收集的信息的差别。
优化器关于统计信息的实验还有一部分没有做完，下一篇再介绍。关于统计信息可以测试的内容还有很多，如分区表相关的，情形会更复杂。
原文作者：数据库技术闲谈；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
