|
|
51CTO旗下网站
|
|
移动端

PostgreSQL查询优化器详解(物理优化篇)

继《PostgreSQL查询优化器详解(逻辑优化篇)》,本文将另以物理优化角度,继续深入PostgreSQL数据库查询优化器的细枝末节。为了让大家通过通俗易懂的方式更好地理解消化其中的晦涩概念,作者别出心裁地撰写成趣味故事,虽然篇幅稍长,但细细品读定将收获匪浅。

作者:张树杰来源:DBAplus社群|2018-05-25 15:04

PostgreSQL查询优化器详解(物理优化篇)

《PostgreSQL查询优化器详解(逻辑优化篇)》,本文将另以物理优化角度,继续深入PostgreSQL数据库查询优化器的细枝末节。为了让大家通过通俗易懂的方式更好地理解消化其中的晦涩概念,作者别出心裁地撰写成趣味故事,虽然篇幅稍长,但细细品读定将收获匪浅。

关于统计信息与选择率

“咚咚咚……”门外传来了敲门声,大明打开门一看,原来是同事牛二哥。牛二哥是专门从事数据库查询优化开发的码农,也有十几年从业经验了,大明感到非常happy,因为这两天给小明讲查询优化器讲得有些吃力,今天牛二哥来了正好可以帮上忙:“牛二同志,我弟弟小明最近学校要做数据库原理实践,总来问我优化器的问题,可我对优化器也是一知半解,这下你来了可以帮帮忙不?”

牛二哥痛快地说:“这难不倒我,随时都可以讲。”

小明对牛二哥早有耳闻,接到大明电话后速速赶到,见面不久便吐起了苦水:“我最近正在查看基于代价的优化,感觉付出了很多代价,但收获甚微,期望今天能得到牛二哥的指导。”

牛二哥说:“说到代价,我觉得有个东西是绕不过去的,就是统计信息和选择率,PostgreSQL的物理优化需要计算各种物理路径的代价,而代价估算的过程严重依赖于数据库的统计信息,统计信息是否能准确地描述表中的数据分布情况是决定代价准确性的重要条件之一。”

小明说:“大明和我说过,数据库有很多物理路径,这些物理路径也叫物理算子。和逻辑算子不同,物理算子是查询执行器的执行方法,我们只需要计算物理算子每个步骤的代价,汇总起来就是路径的代价了,那要统计信息有什么用呢?”

牛二哥说:“是的,我们就是要计算一个物理算子的代价,但是物理算子的计算量并不是一成不变的。”说着他从旁边的书桌上拿来纸和笔,写了两个SQL语句。

  1. SELECT A+B FROM TEST_A WHERE A > 1;  
  2. SELECT A+B FROM TEST_A WHERE A > 100000000; 

然后说:“你看,这两个语句可以用同样的物理算子来完成,但是他们的计算量一样的吗?”

小明心想:A > 1和A > 1000000000都是过滤条件,经过过滤之后,他们产生的数据量就不同了,这样投影中的A+B的计算次数就不同了,所以它们的代价应该是不同的,那它和统计信息有什么关系呢?小明灵光一闪,马上说:“我知道了,我在计算物理算子的代价的时候,要知道A > 1之后还剩下多少数据或者A > 1000000000之后还剩下多少数据,如果我们提前对表上的数据内容做了统计,剩下多少数据就不难计算了,所以必须要有统计信息。”

牛二哥点了点头说:“嗯,通过统计信息,代价估算系统就可以了解一个表有多少行数据、用了多少个数据页面、某个值出现的频率等等,然后就能根据这些信息计算出一个约束条件能过滤掉多少数据,这种约束条件过滤出的数据占总数据量的比例称之为‘选择率’,所谓选择率就是一个比例,它的公式是这样的。”说着牛二哥继续在纸上写下了选择率的公式:

“不过上面的示例有点简单了,实际应用中通常约束条件会比较多,而且比较复杂,通常我们会计算每个子约束条件的选择率,然后就可以根据AND运算符和OR运算符计算它们的综合的选择率,AND运算符和OR运算符的选择率计算是基于概率的,你看这里的概率公式。”说着,牛二哥又继续在纸上写了起来。

  1. P(A+B)=P(A)+P(B)-P(AB)  
  2. P(AB)=P(A)×P(B) 

“有了这些,我们就可以求解多种类型的约束条件的选择率了,比如……”牛二哥继续写出: 

  1. P(ssex IS NOT NULL OR sno > 5)   
  2. = P(ssex IS NOT NULL) + P(sno > 5) – P(ssex IS NOT NULL AND sno > 5)  
  3. = P(ssex IS NOT NULL) + P(sno > 5) – P(ssex IS NOT NULL) × P(no > 5) 

小明觉得牛二哥讲解的进展有点快,赶紧问:“那么统计信息是什么形式的呢?”

牛二哥挠挠头说:“这个还真是有点麻烦,我们说常用的统计信息的形式就是distinct率、NULL值率、高频值、直方图、相关系数这些,它们分别有不同的作用。比如说distinct率,你可以获知某一列有多少个独立值,这种信息对于像性别这种列就显得特别有用。NULL值率呢,在统计的过程中,NULL值是不好处理的,因此把它独立出来,形成NULL值率,这样在高频值、直方图这些里面就不用考虑NULL值的情况了。高频值属于奇异值,顾名思义,就是出现得比较多的一些列值。去掉了NULL值,再去掉高频值,剩下的值可以用来做一个等频的直方图。”

大明看小明有点跟不上,过来说:“统计信息嘛,主要的还是高频值、直方图和相关系数,实际上我建议还是不要纠结于统计信息有哪些形式,只要知道它是用来算代价的就可以了。”

牛二哥对大明说:“这怎么可以,我还没有说统计信息是如何生成的呢,比如它通过了两阶段采样,然后对样本进行统计时使用的统计方法,哪些值可以作为高频值,直方图有几个桶,相关系数是怎么计算的,相关系数在计算索引扫描路径代价的时候怎么用的……而且我和你说,PostgreSQL还出了基于多列的扩展统计信息,多列统计信息分成了哪些类型,分别是什么含义,各自是怎么计算的,还有选择率是怎么结合统计信息计算的,这些我还没说呢……”

大明忍不住说:“像你这样讲优化器,岂不是要出一本书了?”

牛二哥做痛苦状:“那好吧,统计信息我们就说到这里,但是它确实是代价计算的基石,小明同学,你理解了它的作用就可以了。”

大明继续神秘地说:“实际上统计信息往往也不准,你想想本来就是采样的结果嘛,样本是否显著压根就不好说,而且随着应用程序对表的更新,统计信息可能更新不及时,那就更会出现偏差。更严重的是,如果我们遇到a > b这样的约束条件,使用统计信息计算选择率也很不好计算,即使算出来,也不准嘛。”

牛二哥说:“是的,统计信息确实也有不准确的问题。我听说有个DBA,他家后院出了一口泉水,他爸爸觉得是吉兆,去找风水大师看。风水大师掐指一算说:你儿子是个DBA,每次数据库性能慢就知道更新统计信息,可是统计信息太水了,都从你家后院冒出来了。”

三个人顿时笑做一团。

关于物理路径

玩笑过后,小明说:“