1.问题背景

某局点出现如下业务场景：从存量清单表中，根据条码，合同号等条件，查询明细数据，表总数据量有3亿。一次业务请求包含10个并发的查询语句，需要1秒内返回结果集。但是多次优化之后并发性能依旧长达4s左右。

2.   硬件配置

硬件配置信息：集群 6个数据节点：每个节点都是RH5885服务器，配置1T内存、 144个CPU core、SSD 存储空间

3.   历史优化

对于这个业务场景需求，客户做了多种性能优化尝试，优化动作主要分为如下三个阶段

l  第一版本：调试分布键

分析业务数据特性，并测试验证选择最合适的分布键，既能保障数据不倾斜，又能利用分布键做为条件过滤数据

调整后单个查询响应从平均9秒降到4~5秒

l  第二版本：添加PCK&限制结果集

接口上线后，发现业务中会有几个高频条件字段，同时有些查询返回结果集过大，有几十万记录，不符合前端查询响应要求，所以添加了限制，单次最大只能返回1万条记录；同时对合同号添加了PCK

调整后单个查询响应从平均5秒降到2秒

l  第三版本：使用SMP，以资源换性能

从系统监控发现资源使用率低，建议使用SMP，通过提高CPU资源使用率的方式提升查询性能。根据现网CPU配置推荐设置query_dop=8

调整后单个查询响应从2秒降到了400MS

版本上线后，生产使用发现查询性能劣化到3~4秒，但是把SQL提取之后单独运行时性能达到400ms。分析平台日志发现下日志中会每10个请求发起时间点很接近，然后再找到下游调用方了解，才确认是会每此业务请求包含10个类似SQL语句的并发调用。后续测试确认并发查询时性能下降，导致性能不达标。

4.   优化分析

4.1. SQL语句分析

业务SQL是如下简单的单表查询语句，SQL语句未发现明显的不下推、不能提升子查询等明显低效性能点。需要进一步获取performance信息分析性能瓶颈。

SELECT DISTINCT
    SN::text AS SnNo ,
    ItemNo::text AS PartNumber
FROM dm_ioc.RPT_ServiceManagementBySN a
where 1=1
and POSITION(','||a.ItemNo||',' in ','||'0835TXWY'||',') > 0 
and (a.ServiceQuoteItem ='Y' or bids_ismeduimquoteitem=1)
and a.ContractCountry ='China' 
order by  ContractNo,SN
limit 10000

4.2. 性能瓶颈分析

执行并发查询，获取performance信息，典型信息如下，详细信息见附件

 发现耗时主要有两个

1)   数据收集（GATHER和LOCAL GATHER），这个算子主要是从相关线程收集数据，这个算子耗时一般分为两个场景

a)   数据量大，导致数据收集耗时特别长

b)  CN申请线程，DN上创建STREAM线程等计算相关线程初始化耗时长

2)   数据扫描(Scan)耗时长

分析Scan算子，发现顺序扫描（Cstore Scan）时，RoughCheck（根据稀疏索引排除查询不相关的数据）时排除掉的元组为0，即稀疏索引没有预过滤掉任何CU

根据上述分析可以看到当前查询性能主要损耗在计算相关线程初始化上，但是如果把SMP去掉（设置query_dop为1），底层数据扫描耗时会变长，同样导致性能不达标。和相关维护和业务人员也确认了这点

4.3. 性能优化

4.3.1. 优化思路

找到性能瓶颈点之后就可以针对性进行性能优化，我们把优化的重点放在Scan性能的提升上，期待在不设置SMP时通过Scan性能的大幅提升来优化性能。

根据表扫描信息分析，我们发现，表扫描的filter条件可以过滤掉376029639条记录，输出149条记录，可以过滤掉99.99996037%的元组，效果非常明显。

分析表的扫描条件，通过测试验证发现过滤效果最明显的条件为POSITION(','||a.ItemNo||',' in ','||'0835TXWY'||',') > 0，过滤效果在99.99%以上，其它表达式基本没有起到过滤效果。进一步分析RoughCheck，发现RoughCheck排除掉的元组为0，稀疏索引没有任何过滤效果

通常来讲，这类过滤效果非常明显的filter是典型的构建PCK和索引场景，但是这个filter条件为函数表达式，导致此filter条件即没有办法构建PCK又没办法构建索引。因此我们尝试跟业务人员沟通，看能够改写POSITION约束，使其支持索引和PCK

4.3.2. 业务分析

跟客户业务人员进一步沟通position函数的业务含义，发现

1. position函数的第二个参数如果个合同号通过’,’连接起来的子串

2. 函数功能是查询能跟匹配到第二个参数中任一合同号的记录

第二个参数通过如下的方式从客户端输入

这样写的原因是从在客户界面上操作的方便性以及常规的操作习惯上来讲，在外部输入时不会在合同号上引号，因此业务人员希望把界面输入的所有内容作为字符串，然后定义一个函数position，实现满足指定合同号的记录筛选。导致在SQL拼接时，此约束没有写成常见的索引支持的表达式itemno in ('0AFR2C', ‘0A672C’, ’0A902C', '0AY72C')

4.3.3. 最终优化方案

在了解这些原因之后，我们和业务人员沟通，采取以下优化手段

1) 在业务层在接受客户界面输入的合同号之后，内部在拼接SQL之前，进行数据预处理，把函数的逻辑转化为itemno in ('0AFR2C', ‘0A672C’, ’0A902C', '0AY72C')式的in约束

2) 在itemno上建PCK以及索引，通过PCK和索引的双重优化来提升性能

采取这两个措施之后，标准并发测试场景下，SQL查询性能提升到90ms，端到端的性能从4s提升到300ms

5. 优化总结

5.1. 关于列存点查性能

通常场景下，列存表的点差性能比行存表的点查性能要差，但是在如下场景下列存表的索引查询性能也非常好

1）宽表的少量列查询

列存是按照列存储的，宽表的少量列查询时，列存表只需要读取少量查询相关列即可，这可能会导致列存表扫描的数据量小于行存表的数据量，体现列存表扫描的性能优势

2）索引列上局部聚簇

通过在索引列上做PCK，通过PCK的聚簇效果减小扫描的CU个数，导致列存表扫描的数据量小于行存表的数据量。

注：行存表有全局聚簇的手段，详见产品文档的CLUSTER命令

5.2. 关于优化思路

SQL语句优一些建议：

1)  梳理性能问题业务，找出执行性能差的SQL语句

2) 分析业务流程，确认SQL全生命周期的耗时分布，如果数据库执行时间长，那么进入下一步

3) 分析是否存在不下推场景。如果存在，通过改写SQL消除不下推因素，如果SQL语句可以下推，进入下一步

4) 通过performance找性能瓶颈点：根据performance显示的算子耗时，查询耗时最长的算子，这个算子就是导致执行耗时时间比较长的最基本因素

5) 针对性能瓶颈点，给出优化思路和方案

优化不要局限在SQL语句本身，要和具体硬件配置和业务背景相结合

名词解释

1.  【CU】

列存的基本存储单元，同时也是列存表扫描时基本的读取单元。列存表读取数据时会把全部CU的数据读上来，而不能读取CU中的一部分数据。

2.  【PCK】

Partial Cluster Key（局部聚簇）的简称，是列存表的一种局部聚簇技术，该技术可以在批量数据导入时，把数据按照PCK指定的列(单列或多列)进行局部排序，实现不同值区间的数据存储到不同的CU上。这种局部聚簇结合列存表CU的内置min/max稀疏索引，可以大幅提升表在PCK列上简单filter的过滤效果

使用PCK需要注意的几点

1)  只有列存表支持PCK

2) 一个表只能定义一个PCK

3)  PCK列上的fliter要满足以下约束

     a)  简单的<、>、≤、≥、=表达式

     b)  一侧是PCK列，而不能是列相关表达式、函数；

  c) 列的数据类型比如是字符串(text/varchar/nvarchar2)、时间(timestamp/timestamptz/date)、整型(int/bigint)

     d)  另一侧是常量

     e)  表达式计算时，PCK列不需要进行隐士类型转换

4)  上个步骤的filter条件要可以过滤掉较多的元组，减轻Scan以及相关投影计算量

5)   表数据是批量数据入库，且单次入库数据不小于DN的个数*6w * 5条记录；如果单次入库记录数不满足上述约束，建议把表建成分区表，定期对增强数据所在分区进行VACUUM FULL操作增强聚簇效果

6) 通过ALTER语法增加PCK时，只有后续新增数据才会有局部聚簇效果。可以通过VACUUM FULL全表让已有数据恢复聚簇

3.  【SMP】

SMP特性通过算子并行(可以理解为把一个线程的任务拆分为多个线程并行执行)来提升性能，本质上是一种以资源换取时间的方式，在合适的场景以及资源充足的情况下，能够起到较好的性能提升效果；但是如果在不合适的场景下，或者资源不足的情况下，反而可能引起性能的劣化。GaussDB A中可以通过参数query_dop设置算子并行度

4.  【RoughCheck】

粗过滤检查，列存表扫描的特殊的优化手段。在列存表简单filter条件（单列 op 常量， op为>、<、=、≤、≥）过滤时，通过判断CU的内置min/max跟filter条件进行快速比较，如果此CU内是不满足filter条件，那么直接跳过此CU的扫描。GaussDB A的列存表通过PCK +RoughCheck+Latter Read(先读取filter条件列进行过滤、再读取非filter条件列数据) 组合技术，可以典型场景下节省大量的数据扫描，提升查询性能