第一篇 - 常规过滤及分组汇总:SPL轻量级文件存储提速查询实践

我们以订单表为例实现 esProc SPL 数据外置，提速常规过滤及分组汇总计算。

我们使用 SPL 的 ETL 工具来生成脚本，实现数据的转储。在 [SPL 安装目录]\esProc\bin 找到 dft.exe，运行后选择文件 - 新建 ETL。打开工具 - 数据连接：

点击连接后，数据库的表可以拖拽到工作区：

工具 - 数据目录，设置数据文件存储的目录。

工具 - 生成 SPLX 代码：

导出后，记得这次新建的 etl 保存成 Q1.etl 文件。

SPL 代码 2：导出的 SPL 代码，是从 MYSQL 数据库中导出数据，转储成集文件 BTX。

例 1.1，按雇员分组统计运货费的 SQL 是这样：

select 
    employee_id,
    count(*) as order_count,
    sum(shipping_fee) as total,
    avg(shipping_fee) as average,
    max(order_date) as latest_order_date
from 
    orders
where 
    order_date between '2024-01-01' and '2024-10-31'
    and shipper_id<>1  
    and shipping_fee > 10
group by 
employee_id;

执行这个 SQL 需要 11 秒

SPL 代码 3：

订单表比较大不能全部读入内存，这里 A3 使用的是游标，是分批读入数据，边读边算的。 注意，游标只取出需要的字段，可以减少生成的对象，并减少内存占用，提高性能。

A4 中的 select 函数相当于 SQL 中的 WHERE 子句，用于过滤。

A5 则对过滤后的结果做分组，语法形式和 SQL 不同，但仔细看会发现涉及的要素都是一样的：分号前的部分是分组键，相当于 SQL 的 GROUP BY 部分，分号后是聚合值，相当于 SQL 中 SELECT 中的聚合运算。SPL 的分组会缺省将分组键和聚合值拼成结果集，不像 SQL 那样要在 SELECT 中把分组键再写一遍。

SPL 的执行时间是 2.2 秒。BTX 是行式存储，还不能最大程度发挥 SPL 的性能。

用 ETL 工具编辑 orders 表：

导出 SPL 代码 4：把数据转储成列存组表 CTX。

代码和 BTX 基本一样，只是 A3 产生游标的代码不同。CTX 需要先打开组表对象，然后建立游标。

执行时间是 1.9 秒，比 BTX 快。

接下来使用 CTX 游标过滤技术进一步提速：把过滤条件附加到游标上，SPL 先读出用于计算条件的字段值，如果条件不成立就放弃读取其他字段，条件成立才继续读出其它字段并创建这条记录。

这样可以减少硬盘读取，避免产生不必要的对象，提高性能。

SPL 代码 6：

A3 中的游标取数时，先读出 orders.ctx 的 order_date,shipper_id,shipper_fee 字段用于计算条件，如果条件不成立就放弃读取其他字段，如 employee_id。条件成立才继续读出其它需要的字段并创建这条记录。

游标过滤算法的执行时间是 1.8 秒。

继续用并行技术提高性能，SPL 能方便地写并行代码，只要配置一下并行数，和 CPU 核数一致即可，这里配置了 8 并行。

SPL 代码 7：BTX 上并行计算。

cursor 函数增加 m 选项就可以了，执行时间是 0.6 秒

SPL 代码 8：基于 CTX 并行计算。

也是给 cursor 函数增加 m 选项，执行时间是 0.5 秒

小结一下性能（单位 - 秒）：

MySQL 在并行方面似乎不够好，设置了并行参数后，性能也没显著提升。这不是本文的关注重点，也就不深究了。

后续的测试未加说明都是指 8 线程并行。

需要注意的是，SPL 文件存储有其特定的适用场景。因为要导出数据，所以更适合计算不变的历史数据，其实这种场景就很多了。

请动手练习一下：

1、按客户分组统计运货费，过滤条件要有 order_date 和 employee_id。

2、从自己熟悉的测试数据库中导出较大的表，生成 BTX、CTX，尝试前面讲到的计算。