利用双表技术解决多列日期查询性能问题
一个数据库老兵(10几年数据库+数据仓库+大数据经验)遇到了这样一个难题:
有个订单表有2个字段,一个是 order_date 订单日期,一个是 entry_date 入库日期,平时业务系统查询使用的是 order_date,表呢是根据order_date 按天分区,每天凌晨ETL工具会根据 entry_date 将昨天入库的数据抽到数据仓库,他抱怨抽数的过程非常慢,因为每天入库的订单有几百万到上千万单,因为表是根据 order_date 分区的,ETL工具抽数又是用的 entry_date 来进行的过滤,这显然不能分区裁剪,每次ETL抽数据都要对订单表全表扫描,扫描所有分区,这个表一共有20几亿数据,这当然慢了,也许有人会说,可以对 entry_date 建立本地(local)索引,是的,这是一个不错的办法,但是这也不是最优解,每天有几百万到上千万单,通过索引返回几百万到上千万数据也是非常慢的,会引起大量的单块读I/O。
其实听他描述到一半我就知道他要说什么了,这个问题我在2012年就遇到过。
如果数据库支持物化视图,可以创建on commit 且按照 entry_date 分区的物化视图,业务平时查询原始订单表,ETL抽数据查询物化视图就行,物化视图是根据 entry_date 分区的,这样就可以分区裁剪,大大滴提升性能。
如果数据库不支持物化视图,可以改造业务系统,每次新订单写双份数据,一个表按照 order_date分区,一个表按照 entry_date 分区。
其实这个问题在 OLAP 中经常遇到, 不管是 HADOOP ,还是列存储数据库,还是常见的关系数据库,都可以用 “双表” 这种技术来解决性能问题,数据写双份,表按不同查询列分区/分桶 就可以了