取数实战

本文记录一次真实的取数经历，涉及到多张表的联合表。整体的规模，1个月的数据，最大的表有120亿数据，甚至更多。主表自联2次，订单表30亿（1个月），解析表（10天，10亿），引擎吐出表，2张，10亿或者更多，标记表，（从最近3、4个月，15亿更多的表，需要对同一个订单号、类型分组，并计算出合并的类型），其他小表若干，当然，出重贯穿整个步骤，并最后以主键进行联合。目标数据总量跟主表的总量相近。目前已处理360+个字段，导出数据5000万，数据csv大小120g左右

效果：每日定时跑数，从其他源取数，大概1小时多，脚本运行40分钟，导数3分钟。

总体思路

• 1、首先要确定，要取数的主表，一般都是有个主键，做好去重。后续步骤，都是为这张主表，添加各种关联的数据。
• 各种数据，做好按主键分布、按时间分区的方式存储。从其他数据库取数，或读csv，考虑使用外部表的形式。待做。
• 2、通用的过程是，先将每张要链接的表，处理（包括去重），在最后一步的时候，在进行合并。多表合并，gp性能也还不错。 使用通用的方式去重。
• 3、中间表，如果产生的大量的数据，可以考虑，是否，能用主表来剪切，使用INNER JOIN的方式，保留，跟主表有关系的数据。
• 5、对于同一张表，反复的进行自联，可以考虑
• 6、模型之间的关系，一般1对1这种，不会造成目标表的膨胀，而且还可以通过这种方式，反推出，中间某步，是否漏掉了去重。
• 7、做好日志记录。主要是：psql -e -f mysql.sql，记录查询的sql。另外，在sql的第一行，增加timing on，来记录每条sql的执行时长，方便长期运行，发现性能点卡在哪。对于其他的shell命令，通过logmsg(参见其他文章记录)，记录每个关键点的导数耗时。
• 虽然数据要去重，但是对于重复量很小的表，也可以等整体数据有了，再去重。（合理的安排去重的时机）
• 8、写取数脚本，虽然是为了取数效率，但是也要兼顾易读性，毕竟需求经常更改。

建议

• 计算字段，不一定在取数的时候，就计算。完全可以放到最后联表步骤时，再计算，当然，在这块的计算性能耗费也不大，根据情况而定。较早的写，可以避免跟后面的逻辑，揉合在一起，方便。

• 在最后联表时，尽量为每个表取个别名，主表我一般喜欢叫a，然后每个字段，最好写上来源表，防止字段太多，冲突。（遇到过好几次多表冲突，尤其是自联表，不写不行啊），另外，也方便知道，每个字段，是从哪来的。

  rd.psmdb_ft as i1l_psmdb_ft,
  rd.psmdb_loc as i1l_psmdb_loc,

原理

数据按分布进行处理，这样，相同的数据，会落到同一个segment上。对于5000万的数据，24个segment，每个seg上，相当于200万多的数据，这种规模，在mysql也能很好的处理。segment多，也有好处，这样，相当于天然的，为不同的数据分开了。

另外就是，按时间分区，一般来说，要处理的数据，都能使用上时间来裁剪。避免扫描全部的数据。这样，进一步为每个segment负担。而有的时候，每种数据可能还有类型，如果拿类型来作为二级分区，可能会更好的提升性能。但是，也会有不好的地方。

但是要处理的数据规模大了，还有会有卡性能的地方。如何较好的利用裁剪功能、或其他特性，这也是要进一步处理的地方。

优化记录

第一次优化

调整顺序，联表后获得总数据，再剔除数据，更改为，先剔除数据，再联表。

第二次优化

用主表对中间的临时表进行裁剪，去掉大部分无用的数据。整体时间，瞬间提升了很多倍。产生的中间数据也没有那么多了。

在第二次优化后，本来考虑的是，既然我需要对一张表进行3次自联表，为何我不能将数据先求出来，这样速度很快很多，但是，实践后发现，其实并不快。一是数据总量太多，二是，全部字段都取的话，将耗费掉全部的时间。同一张表自联3次，取3种不同类型，貌似也不靠谱。个人感觉，没有必要，在优化一张表，自联3次。

实战

去重

一般的去重逻辑，可以按下面的方式处理。

DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS SELECT
    *
FROM
    (
        SELECT
            ship_id,
            ROW_NUMBER () OVER (
                PARTITION BY ship_id
                ORDER BY
                    scan_tm DESC
            ) AS rn
        FROM
            tb_scan
        WHERE
            ins_db_tm >= '2021-07-01 00:00:00'
        AND ins_db_tm < '2021-08-01 00:00:00'
        AND scan_typ = '14'
    ) AS T
WHERE
    T.rn = 1
DISTRIBUTED BY (ship_id);

优化思考

学习新内容最好的方式，遇到问题并成功解决问题。

• 优化取数，先一次查找目标的运单号，并存到表中，下次就不用再从全表中关联运算，除非有需要。
  有没有一次查找，插入到两张表的函数。
  可以继续优化，按结束日期进行分区存放。一次扫描。相当于之前的，自定义函数，一次处理相关的数据，并存入到json格式数组，其实发现并没有必要。

• 能否写个脚本，实现一次扫描，将数据从原有的格式，转换到新的要求分区呢？新要求，相同签收日期的按天存放，并且所有相同单号的其他形式信息，也存在同一张表内。另外，按扫描类型分区。
  感觉可以倒序扫描，这样，就能知道按哪个日期排序呢，实在不行，先降级，先按相同的单号，放到一起，然后，再整理一次，但是，要求要耗时短一些，不然，没有任何优势。因为原来根据某一天签收的单号扫描一个月，貌似时间也不长。
  （不能达到写到几个表中的效果，但是可以写分区表中，相当于写到不同的表中。取数，写到不同的文件，一样道理，分区表）
  所以，由此可见，数据的组织形式，对查找效率有很大的影响。
  关键如何实现？不管是自定义函数，还是写SQL。

  实践证明，不行。数据量太多，没有必要对自联表这样操作

  思考：分区裁剪，分区的意义，能满足场景需求，连续取值，集中一块。裁剪。所以计算出来的分区值，要有意义，能达到集中热点数据。不是瞎分区。

• 另外一种思想，一个月集体计算，然后根据情况帅选。其实就是筛选那个订单

• 反向操作，将一个月的数据都保存一个单独的分区表里面。然后以签收为唯独，将已签数据直接按表清空。目的，就是让工作区的数据最少。貌似可操作性不强。
  单独维护一个超过7天没有签收的数据？每次select+truncate？以5天为一个窗口，算出没有签收的单子。经过4次计算，得到一个大的窗口，再进行合并计算？
  更简单的方式，每天都表拆分，分成5-8张表？（这不就是二级子分区嘛？）

• 主表数据+索引，再查一个月内的其他的数据。

  此路不通，貌似提速并不快。反而会导致内存太大，计算失败。

• 内存表，临时表、无日志表

  with好像是临时表，试过了，貌似效果并不大。（临时表直接在内存里面？ ）

  无日志表，无任何作用。

  内存表，貌似按分级存储热点数据，/dev/shm ，去掉日志，插入速度更快？

  貌似用处也不大，直接从硬盘上读取批量的表到新表中，共2秒左右。

• 滑动窗口，不知道有没有这个概念，滑动窗口去重？

• 数据放到更快的redis？

  群友的思路：我遇到类似过的问题。有两个思路。1 写一个Bool 类C函数，is_exist(ID) ,这个函数读一个字典。这个字典在pg_init时定义，通过preload 在初始化加载到内存。 性能很好。 2 把数据订单号集合放到redis，通过pg函数，比如plpython写is_exists函数来访问判断。性能比上一个稍差。差了网络的开销。 但比 /dev/shm快得多 方法一，就相当于5000万订单号码，是数据库的一个runtime变量，在数据库启动时就一直在内存了。 第二个方法，注意利用GD保存访问redis的连接，保证一次查询会话建立tcp只有一次。

  自己测试结果，可能因为redis本身的性能问题，读100万需要200秒，而原有的计算用到了hash join，貌似这样折腾，性能反而会下降。