SQl统计信息

统计信息在数据库当中是很重要的一个东西，华为云高斯GaussDB在这方面做持续的耕耘和持续的一个改进，在统计一些方面做了新的尝试和想法，已经在内部客户当中去推广和试用。今天主要分享一些基本功能和玩法

这次分享主要包含三部分内容，第一部分先介绍统计信息相关的一些知识，统计器到底是什么，统计器是如何被使用，如何被生成的，如何被使用的。

第二部分介绍统计信息究竟是如何收集的，如何做到自动收集。

第三部分是我在使用这个统计信息收集的时候遇到的一些常见的问题。

1. 统计信息简单介绍

SQL 语句的执行，它这个整个过程当中都发生了什么?第一步就是 的用户业务在发送一个 SQL 语句到数据库，它首先是要经过解析器，通过词法分析，语法分析生成一个语法树，拿到了语法树以后，把它交给这个 SQL 的优化器，根据语法树看你要是做要查询哪些表， 要想得要得到什么样的结果。 开始做这个内部的各种执行路径的一些计算，从当中选择一条最优的一个执行路径，生成一个执行计划，把这个执行计划交给这个执行引擎来生成一个查询结果。这个过程就很像 在这个出行打车当中去选择这个出行路径一样。

优化器以前是有一种叫基于规则的优化器，它就类似于以前在没有智能手机之前，拿着纸质地图去出行一样，只有具体的一些规则，就是从哪到哪，哪个路径最短，就只能就是这样去选择。它没有路况信息，也不知道哪个路发生了拥堵，哪个路是不是有这个事故发生， 这个选择就是只是基于一些最简单的规则，就是一个试试看有没有唯一索引，有没有这个主键， 优先选择这个索引来看。但是它由于没有收集这个统计信息， 它没有办法考虑大表大小，现在的优化器都是基于成本的优化器就类似于现在这个地图 APP 进行打车一样，它是根据实时路况以及司机的些数据， 就知道了这个路是不是哪些路段有拥堵发生，哪些路段有事故发生，从而 得到了一体，从而就会得到一个最优的一个执行路径。相对于优在优化期里面也是很差不多的一个东西。

优化器先进行这个列上的这个统计信息收集，知道了这个表大小，知道了列宽，知道了这些数据以后， 开始进行每条执行路径的成本的一个估算。数据库里面首先就是估算成本分两方面，一个是 CPU 的一个代价的估算，一个是 IO 代价的一个估算。基于这个统计信息进行每一条路径的一个代价计算，从而选择一个最终最优的。 现在的税务产品基本上全都是基于成本的优化器，这个优化器模型统计信息的收集也显得很重要，就像打车一样，如果没有这个实时路况信息，那出行就会造成很大的一个麻烦。

接下来看一下统计信息在思维语句当中的一个这个层次关系，因为 进行数据库操作都使用SQL 语言，它也是一个结构化查询语言，它是一个高度的，并且是非过程化的一个编程语言。用户是在这个高层次进行数据结构上的一个操作，用户只需要关心我需要干什么就行了，不需要关心具体你怎么干。 像这个路径的一些选取代价的一个估算，具体做什么样的操作，如何去排序，如何去进行数据扫描，这些完全都是由数据库内部自动完成的。既然是由于数据库内部自动完成了，因为 有很多不同的路径去选择，不同的操作方式去选择，那如何选择一个最优的？就是一个问题。 它分三个层面，第一个层面就是先进行一些统计信息的一个收集，它是基于这个随机采样的随机采集一些部分数据来生成这个统计信息。有了这个统计信息以后， 根据 SQL 语句里面要查询哪些表要做关联， 在这些算子上进行代价的一个估算，每条路径都这样去算代价,算完以后生成代价最小执行计划，作为最终的这个计划去执行。 统计信息是生成所最优执行计划的一个前提。

先举一个例子， 这是一个是TBC的一个查询语句， 它做两个表的关联，一个是 order 表，一个是 line item 表的一个关联。在没有收集统计信息的一个情况下，这个是生成的一个计划，首先就是这个是对两个表进行扫描，这个 Lion item 表是是在下面，它是先把这个 Line item 表，它实际上是一个比较大的一个表，占的条数要比这个 order 表要大很多。这个没有收统计信息的时候，这个它把这个大表放到了加载到了这个内存， 执行耗时是 2878 毫秒。 收集完统计信息之后，再看这个执行计划，order 表就在最下面了，把它加载到内存，就把这个小表加载到了这个内存，执行时间是 1971 毫秒， 有了统计信息之后， 正确地把小表这个加载到了内存，得到了一个更优的一个执行性能。 在基于代价估算的这优化启模型当中，统计信息会直接影响到这个执行计划的一个选择。

知道了统计信息对于优化器很重要，那么统计信息它是如何被优化器使用的？

先看一下统计信息都包含哪些东西。统计信息分两个层面，一个是表级的统计信息，一个是列级的一个统计信息。表级是描述表的一些大小，具体的有real pages 就是表在磁盘当中占用的这个物理的页面数，还有就是 real tuples 就是表的实际的数据条数。这样就知道表的一个规模。列级统计信息它包含很多值，比较典型的就是这 4 个值。第一个就是叫distinct，就是唯一值的个数，就是把这一列进行去重以后，最终剩的剩下的唯一值的一个个数有多少个。还有就是空值的一个占比。

最重要的是下面的两个，一个叫MCV，就是 most 的 common value，就是 把它叫做高频值，就是主要是它描述这个数据的一个重复情况。简单说就是要看一下这个列当中重复最多的数都有哪些。还有一个就是直方图，直方图用于来描述数据的一个分布情况，生成方式就进行数据采集以后，按一定的步长去打点，抽出一些按步长去抽出一些数据来。有了这些基本的统计信息以后，优化器就是拿一些这些信息来进行这个 SQL 代价的一个估算。

接下来 看一下优化器到底是如何去使用这些东西的。

第一个要看表集的统计信息是如何影响表大小估算的，刚才已经看了表集的统计信息，描述了这个表的数据规模，其实主要有这个页面数和条数这两个。比如说做一个查询叫 select sharing from 这个表，看一下它的查询计划， sequence scan 进行一些数据扫描，它估算出来的条数也是1万，和这个1万是相等的，但它实际是怎么计算的？就是它是在统计机器生成的时候，用这个条数乘以这个除以这个物理的页面数，把它就叫做页面的元组密度，就是平均每个页面大概有多少条数据。 为什么要乘以实际的页面数？就是因为生成统计信息以后，这个数据可能还会再变，这个表的页面可能还在增加，那么进行代价估算的时候，是基于前一步生成的这个统计信息， 就是拿前一步是这个元组密度乘以实际的页面数来估算出当前的一个大概的表大小，就是通过这样计算刚好也得到了是这个1万条。 如果这个原组页面密度估算得不准，那么它就会影响到这 表大小的一个估算会出现一个偏差。

接下来 看进行如何进行等值比较的一个估算。

等值比较就是用这个 SQL 语句，从这个表去查询这个 string 1的这个值，等于这个 CRA这个值，看它有这个多少条。打出来这个计划，也是因为这上面没有索引，先走的是顺序扫描，估算出来这个值是有 30 条，那么这个 30 怎么来的？就是基于上面的这个统计信息，这个是 查询出来的这个 MCV 也叫高频值的一个统计信息，它包含两方面的一个内容，第一方面就是这个 most common value 就是具体的值。

第二组就是每一个值的出现的一个频率， 每个值有出现的品类不一样，那再查这个 c r a ，进行等值比较，其实找到 c r a 在这个 个位置，它出现的这个频率是 0. 003， 后面进行估算的时，首先找到这个 MCF 是 3 的位置是 0. 003，拿它再去乘以上一步估算的这个表的这个大小，乘以1万。 最终得到这个30，就估算出这个数据，在这个表中大概有 30 条，也很简单，就是它是完全是基于这个统计信息的一一个状态来估算的这个表大小，如果这个高频值收集得不准的话，那就会直接导致条数估算的一个偏差。

接下来看一下范围比较，有了表大小， 有了等值比较，来看一下范围比较，它就用到了刚才说的另外一个统计信息，叫直方图。直方图也很简单，它跟 MCV 不一样， MCV 是有值和这个频率，直方图就直接是具体的值了。它的生成方式就是在样本里面， 把机型从小到大排个序， 按一定的步长去打点采集，生成的最终结果就是大概是这个样子，可以看到它的这个数据分布是从 0 到9995，大概是这样， 中间按相同的步长， 有这些值。 当 进行一个范围比较的时候，比如说 查这个表的，这叫 unique e 列，让它小于1000，看它有多少条计算方法，就看这个 1000 在这个哪，在大概是在哪个位置这里第一个是0，第二个是993，第三个是1997， 1000 就落在了 993 和 1997 之间。 那么有了它了，看如何去算小于 1000 的值大概是有多少的一个比例？实际估算的结果是小于 1000 的值们估算出来是有 1007 条，实际估算的时候，这个算法就是在这边也很简单，就是先就是说看这个值在前，在最前面，比如说 0- 993，这是第一个，叫这个桶占了 0- 993，这是完整的一个桶。 在目标值在当前桶中的范围，比如说 993 到 1997 之间，那么小于 1000 的大概占了多大的一个范围？ 把这两个进行相加以后，除以一个桶的总数。具体的算法就是因为 0- 993 占了完整的一个桶，就记为一， 用 1000 减去第二个桶的最小值，也就是993， 再除以第二个桶的这个范围，就是 1997 去减去993，也就是说小于 1000 在第二个桶当中它的占的一个比例， 跟前面桶的个数相加，再除以总的桶的个数。

接下来看一下join，就是多表进行关联的时候，又是如何进行这个单价结算的。

现在有两张表，一个是 T1 表，一个 T2 表， 条件 t 表小于等于50， 1 和 2 进行关联，我T2 表扫描出来，进行这个进行小于 50 的这个单元，估算出来这个数据条数也是50， 进行关联关联这个 T2 表进行按这个等值关联，它估算出了一条最终 join 出来的结果也是 50 条。这样的话其实要考虑，有两个表，两边的统计信息都要进行一个这个考虑。右边查出来的是这个 T1 表和 T2 表这个控制比例统计信息，进行代价估算的时候，方法就是首先两边的这个数据条数进行一个笛卡尔积的一个相乘， 再乘以这个整体的选择率，也是进行一个组合的一个计算。首先是左边一张表的这个非控制的一个条数，乘以右边表的一个非控制的一个条数，最终乘以这个 根据 detail 值估算的一个选择率，最终得到了这个 50 条。

这个统计信息重要性 主要 3 这个三个页面来介绍， 得到了就是，这个统计期的调，这个非常重要，统计期也是 SQL 调优的一个前提。好，接下来 看一下这个统计息，究竟是 用了很大的篇幅来铺垫一下这个统计信息的本身的一些东西，主要是为了让大家知道 的一用户在真正使用过程当中， 为什么非要收集统计信息啊？这个统计信息跟我业务又不是很相关，我要花好长时间，花好好花这个好大代价去做这个事情，主要是给大家解释一下这个事情。 好，

2. 统计信息如何收集

接下来看一下统计在这个产品里面是如何进行这个收集的。

首先 拿一个页面来介绍一下 产品的一个架构， 绿色的节点就是 一些这个管理节点， 不去重点介绍，它主要是 两部分，一个是黄色的节点和这个浅蓝色的一个节点，黄色的节点是 接收 SQL 语句的这个节点， 接受到思科语句， 进行词法分析、语法分析， 生成一个最优的计划，把计划下发到各个这个数据节点。 数据集数据节点真正地去执行，结果返回到这个计算节点，最终再返回给应用的这个业务。 这是如果是复杂的一个场景，复杂 SQL 是这样计算的，如果是一个简单的SQL，比如说这个 SQL 我已经确定到，确定根据这个统计信息确定数据落在某一个 DN 上，就可以把这个 SQL 语句直接发到具体的DN，由它进行这个词法分析， 生成最优的计划， 生成结果最终返回。

统计一分两个层面，第一个层面就是每个数据节点要计算自己的一个统计信息， 每个计算节点要计算一个全技术的统计信息。那么 的 airlines 做样本采集的时候，也就当然也就分了两个阶段。第一个阶段就是 DN 进行这个样本采集，生成自己本地的一个统计信息， 把这些样本发送给 的支线节点，由 来计算全局的一个统计信息。

先介绍一下基本的 统计些如是如何收集的。 有这几种方式。第一种方法，第一种方式是按库来收集，它适用于这个整库迁移的这个修改场景，我就把所有的表， 整个去收集一遍统计信息，当然这个肯定是耗时最长的。第二就是按表来收集，我一张表的来收集这个统计信息。第三种就是按模式来收紧，因为 这个模式是一个逻辑概念， 需要把这个所有表这个模式下面的所有表查出来， 整体地去做这个统计新的一个收集。第四种 可以案例来收集，比如说我是一个特别宽的一个表， 并不是所有列都需要进行查询，所有列都要用到， 我只需要对个别的列进行收集统计信息就可以了，这样可以提升这个 align 的一个性能。最后就是 一个组合信组合的统计信息一个收集，比如说我要进行多列的一起进行查询，一起进行关联，或者说整体多个列进行 out by，这个时候如果通过单列的统计信息估算不准的时候， 就可以进行多列组合的一个统计收集。比如说 把 column 1， column 2 这两个视为一个整体，它整体的去算这个diff、 team 值，m，c， v 这些这些信息，这样的话会比单列估算的会准一点。