达梦数据库系列—39.统计信息详解

目录

达梦统计信息

举例

统计信息的缺点

---

达梦统计信息

达梦数据库的统计信息分为表统计信息，列统计信息，索引统计信息。

1. 表：计算表的行数、所占的页数目、平均记录长度。
2. 列：统计列数据的分布特征。
3. 索引：统计索引列的数据分布特征。

直方图（Histogram）是列级别统计信息的一种，它主要用来描述数据库中列值的分布情况，适用于数据分布不均匀的场景。有了直方图，数据库可以针对不同的参数值准确地计算出选择率，保证计划的正确性。

列和索引将采样的数据按照不同的分布特征生成相应的直方图。有两种类型的直方图：频率直方图和等高直方图。生成直方图时，如果不同值少于 1 万个则用频率直方图，否则用等高直方图。

频率直方图

频率直方图为列中的每个不同值维护一个行数信息，这种直方图适用于NDV（不同值个数）比较小的场景。可以想象，如果有一张表格记录了一家公司的全部员工信息（每一个人一行信息），对于“籍贯”这一栏，不同值的个数很小（全国约有40个省、直辖市和行政区)，我们可以为该列建立一个频率直方图，优化器通过该直方图可以知道该公司每个省或者直辖市的员工有多少人。

等高直方图

等高直方图收集的是一个数值分布的信息，适用于NDV比较大的场景。一般建立的过程是将全部的记录根据该列进行排序，然后把排好序的记录分成N个桶，其中每个桶内行数大约为M/N(M是总行数)。继续考虑上面的员工信息表，如果表中有一列是“年纳税总额”，我们可以对该列收集等高直方图，从而很容易准确的估计出该公司“年纳税总额超过10万”的人数。

举例

--构造测试环境

CREATE TABLE TEST_TJ(ID INT,AGE INT);

BEGIN    FOR I IN 1..100000 LOOP

                  INSERT INTO TEST_TJ VALUES(MOD(I,9700),TRUNC(RAND * 120));

               END LOOP;

               COMMIT;

END;

--创建系统包

SP_CREATE_SYSTEM_PACKAGES(1);

--更新统计信息

DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_TJ',null,100,false, 'FOR ALL COLUMNS SIZE AUTO'); --更新所有列

SP_COL_STAT_INIT_EX(USER,'TEST_TJ','ID',100); --更新单列

--查看统计信息：频率直方图

 DBMS_STATS.COLUMN_STATS_SHOW(USER, 'TEST_TJ','AGE');

--1.类型：频率直方图

--2.ENDPOINT_VALUE样本值： 1

--3.ENDPOINT_HEIGHT 样本值的个数:70

SELECT COUNT(*) FROM TEST_TJ WHERE AGE=1; --70

--查看统计信息：等高直方图

DBMS_STATS.COLUMN_STATS_SHOW(USER, 'TEST_TJ','ID');

--解读统计信息

--1.类型：等高直方图

--2.ENDPOINT_VALUE样本值： 30

--3.ENDPOINT_HEIGHT小于样本值大于前一个样本值的个数:329

  SELECT COUNT(*) FROM TEST_TJ WHERE ID<30;  --329

--4.ENDPOINT_KEYGHT样本值的个数:11

  SELECT COUNT(*) FROM TEST_TJ WHERE ID=30;  --11

--5.ENDPOINT_DISTINCT小于样本值大于前一个样本值之间不同样本的个数: 30

  SELECT COUNT(DISTINCT ID) FROM TEST_TJ WHERE ID<30;  --30

统计信息的缺点

1. 对于等高直方图，CBO会认为每个区间中的数据都是近似均匀分布的，实际上数据可能分布非常崎岖。
2. 等高直方图区间越多，统计越精确，而实际上数量有限，最大为1000。

例：数据分布不均匀导致范围扫描时CBO估算不准确

drop table t1;

--插入10万条，C1数据分布均匀

create table t1 as select level c1,level c2 from dual connect by level<=100000;

--插入2万条C1=100，使该值数量很大，造成C1分布不均匀

insert into t1 select 100,level from dual connect by level<=20000;

--给C1列创建索引

create index idx01 on t1(c1);

--搜集统计信息

dbms_stats.gather_index_stats(user,'IDX01');

--查看统计信息，100这个值有20001行

dbms_stats.index_stats_show(user,'IDX01');

--测试=值查询，估算正确

select * from t1 where c1=100;

--测试范围查询

select * from t1 where c1 between 90 and 110;

--此时估算行数错误，应该有20001行，不该走索引，计划不对

达梦技术社区：达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台