数据库索引选择的探索（二）之直方图

数据库索引选择的探索（二）之直方图

引言

在《数据库索引选择的探索（一）》里，以SYBASE为例讲解现代数据库选择SQL执行计划特别是选择索引的基本原理和过程：SQL执行过程中有多条执行计划多个索引可供选择，数据库查询编译器会采用代价模式根据表的统计信息和直方图计算出代价最小效率最高的执行计划。其中简单的提到了直方图，这次以PostgreSQL数据库为例，深入探讨直方图的由来、作用和查看方法。

直方图的由来

直方图，又叫“质量分布图”。原本是产品生产过程中用来监控预测产品质量情况，反映产品质量分布情况的图表。是根据从生产过程中收集来的质量分布数据，画成以组距为横轴，以出现频数为纵轴一系列连起来的直方型矩形图（柱状图）。其中横轴通常是等宽的连续的范围分段。

在现代数据库系统中，直方图被引入元数据统计信息中，描述表中每列的数据分布。PostgreSQL在2002年发布的7.2版本中引入直方图。

数据库中直方图存在哪里

创建测试表

create t_test_histogram (
n_id int primary key,
c_name varchar(100)
);

--为了简化问题将c_id的statistics值设置为10，具体含义下文说明。
alter table t_test_histogram alter c_id set statistics 10;

插入测试数据

            PreparedStatement statement = conn.prepareStatement("insert into t_test_histogram values  (?,?)");
            for(int i=0;i<100000;i++){
                if(i<10000){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }
                if(i<20000 && i>10000 && i%2==0){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }
                if(i<30000 && i>20000 && (i%3==0||i%5==0||i%7==0) ){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }

                if(i<40000 && i>30000 &&  (i%3==0||i%5==0) ){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }

                if(i<50000 && i>40000  && i%3==0){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }

                if(i<60000 && i>50000  && (i%3==0 && i%5==0)){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }

                if(i > 60000  && (i%3==0 && i%5==0 && i%7==0)){
                    statement.setInt(1, i);
                    statement.setString(2, "test"+i);
                    statement.executeUpdate();
                }

查看直方图

在ABase数据库中，直方图信息存储在pg_statistic表中，通常通过其视图pg_stats来查看。
查看表”t_test_histogram”的”n_id”列的直方图的SQL如下：

SQL1:

abase=# select tablename,attname,histogram_bounds from pg_stats where tablename = 't_test_histogram' and attname = 'n_id';

结果1：

tablename	attname	histogram_bounds
t_test_histogram	n_id	{0,2947,5894,8841,13578,19472,24942,30438,36753,44295,99960}

- 查询结果含义：
histogram_bounds列结果是将n_id的分布情况分成了10组(桶)，每组占总数量的比例是相同的，即1/10。
即：

序号	n_id取值范围	数据占比
1	0至2947	0.1
2	2947至5894	0.1
3	5894至8841	0.1
4	8841至13578	0.1
5	13578至19472	0.1
6	19472至24942	0.1
7	24942至30438	0.1
8	30438至36753	0.1
9	36753至44295	0.1
10	44295至99960	0.1

• 其他范围数据分布怎么计算？

直方图假设每个组(桶)内的数据是按照线性分布(平均分布)，跨组的数据占比为所跨组的总占比加该数据范围在组内的占比

n_id中10至100范围数据占比
P(10,100) = （100-10)/(2947-0)*0.1
= 0.0030539

n_id中100至40000范围数据占比
P(100,6000)= (2947-100)/(2947-0)*0.1 +0.1*7+(40000-36753)/(44295-36753)*0.1
=0.0966067187+0.7+0.0430522408
=0.8396589

数据库如何使用直方图

• 数据库根据计算出来的数据范围占比(选择度)，进行代价估算中行数估算。

如SQL2:

select * from t_test_histogram where n_id > 100 and n_id <40000

第一步：根据pg_class得出t_test_histogram共有多少行

abase=# select reltuples from pg_class where relname = 't_test_histogram';
 reltuples 
-----------
     29472
(1 row)

第二步：根据100至40000的数据占比估算出where n_id > 100 and n_id <40000 的行数

rows = P(100,6000) * reltuples = 0.8396589*29472 = 24746

最后：验证结果，执行计划估算的rows值等于直方图估算的rows值。

abase=# explain select * from t_test_histogram where n_id > 100 and n_id <40000;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on t_test_histogram  (cost=0.00..769.08 rows=24746 width=13)
   Filter: ((n_id > 100) AND (n_id < 40000))
(2 rows)

更复杂的直方图计算

t_test_histogram例子中我们只演示计算了n_id的数据占比的情况，n_id是主键(唯一索引也类似)不涉及重复值和等值计算的问题（每个值出现的频率是一样的，P=1/reltuples）

• 等值的范围占比（where c_name = ‘xxx’）是怎么计算的呢？

等值计算是通过pg_stats 中的常用值和常用值出现频率计算的，不是通过直方图计算的。

• 普通列(非主键或唯一索引)的不等值占比范围（where c_name < ‘xxx’）是怎么计算的呢？

是结合计算出等值的占比范围和直方图占比范围共同计算出来的

直方图可能造成的问题

• 各种原因(关闭定时更新统计值或没有定时更新)造成的统计信息不及时准确，会造成数据范围占比估算错误

例如：
删掉n_id < 39999的行。

abase=# delete from t_test_histogram where n_id < 39999;
DELETE 25091

查看直方图信息

abase=# select tablename,attname,histogram_bounds from pg_stats where tablename = 't_test_histogram' and attname = 'n_id';
    tablename     | attname |                       histogram_bounds                       
------------------+---------+--------------------------------------------------------------
 t_test_histogram | n_id    | {0,2947,5894,8841,13578,19472,24942,30438,36753,44295,99960}
(1 row)

查看行占比范围估算

abase=# explain select * from t_test_histogram where n_id > 100 and n_id <40000;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on t_test_histogram  (cost=0.00..769.08 rows=24846 width=13)
   Filter: ((n_id > 100) AND (n_id < 40000))
(2 rows)

此处的行范围估算是错误的

更新统计值后，再看直方图和行估算

abase=# analyze t_test_histogram;
ANALYZE

abase=# select tablename,attname,histogram_bounds from pg_stats where tablename = 't_test_histogram' and attname = 'n_id';
    tablename     | attname |                          histogram_bounds                           
------------------+---------+---------------------------------------------------------------------
 t_test_histogram | n_id    | {39999,41313,42627,43941,45255,46569,47883,49197,52560,59130,99960}
(1 row)

abase=# explain select * from t_test_histogram where n_id > 100 and n_id <40000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Index Scan using t_test_histogram_pkey on t_test_histogram  (cost=0.28..8.30 rows=1 width=14)
   Index Cond: ((n_id > 100) AND (n_id < 40000))
(2 rows)

• 采样组(桶)太少，导致直方图不能精确反馈数据分布的变化

总结

• 直方图信息存储在pg_statistics 中，通常通过pg_stats查看
• 直方图是用来表述表中列数据分布的，数据库通过直方图可以计算出where条件中列的占比范围（选择度）
• 不准确的直方图信息会造成选择度计算不准确，进而导致数据占比范围代价估算不准确。
• 造成代价估算不准的原因是分析不及时和统计采样值太少

反思

排查SQL问题的同事经常会遇到这么一个情况，”公司环境的慢SQL在生产环境不慢，生产环境的慢SQL在公司不慢”，造成这个问题的重要原因是我们加压数据分布的还不够准确，不能够真实的模拟生产环境的数据。通过对直方图原理的了解，我们可以利用直方图改进我们的加压工具，在公司模拟出更真实的加压数据环境，提早发现问题，提高产品质量。