PostgreSQL-海量数据存储和查询优化(问题升级)

项目合作 项目合作 主题:1029 回复:2134

PostgreSQL-海量数据存储和查询优化(问题升级)

想挽留 发布于 2017-03-30 字数 1055 浏览 1621 回复 7

我现在为一个程序做了触发器,将数据进入分区,数据总量超过4.7亿条,数据库大小60多GB,后续还会有很多数据进入,每个月的数据超过3000万条。储存不是问题,问题在于查询。

这些数据有一个主键 fld_index ,时间字段 fld_time
现在在前台实现数据可视化功能,既绘制成折线图,比如抽取一天的气温绘制图像(70200个点,既同等数量的记录)。这个查询非常慢,检索条件是时间和观测站点,开启约束排除后每个查询约需要70到80秒,个别月份数据采集频率较高,所以记录上亿条,查询非常缓慢,需要好几分钟。

有没有方案能优化这种海量数据的查询

图中每一行最后的数字是该表的记录条数

表结构:

CREATE TABLE tbl_value
(
fld_index bigserial NOT NULL,
fld_variable_id integer NOT NULL,
fld_time timestamp without time zone NOT NULL,
fld_value real NOT NULL,
fld_evaluation character varying(20),
fld_site_id integer NOT NULL,
CONSTRAINT index PRIMARY KEY (fld_index )
)
WITH (
OIDS=FALSE
);

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

支持 Markdown 语法,需要帮助?

评论(7

瑾兮 2017-10-21 7 楼

对PostgreSQL不熟悉,如果是oracle的话,可以采用分区表的方式来实现,具体要看查询是不是每次只是查询单个站点了。如果是这样的话,在oracle中可以采用按站点建立列表分区,再按时间建范围子分区的方式,这样插入和查询都不用使用动态SQL的方式就可以实现。
当然就算是用PostgreSQL,按照你目前按月分开建表的方式,如果每次只是查询单个站点的数据,也可以采用按站点同时按月分开建表(即每个站点每个月建一张表)的方式,这样只是在插入数据及查询的时候多一点逻辑判断,并不增加多少复杂性,而一个表中的数据可以缩小很多倍,查询的速度应该可以得到很大提升。

甜柠檬 2017-06-21 6 楼

刚才解决了php中执行慢的问题,原因在于execute函数,我用的方法是

$sql = "...";
$sth = $this->db->prepare($sql);
$sth ->execute(array(...));
$rows = $sth->fetchAll();

这个过程中,我var_dump($sth),发现它对sql又进行了大量包装,也没时间细看,将其修改为直接执行查询的语句:

$sql = "...";
$sth = $this->db->query($sql);
$rows = $sth->fetchAll();

就解决了不能使用分区查询的问题

通过大家的回答和自己的实验,现在问题已经解决,每天的数据曲线绘制的Ajax响应不超过300ms 可以说到极致了。总结了几点经验如下:
1.合理的索引,必要的联合索引
2.分区设计,按照需求进行分区,除了按照时间分区之外,如果已知表内固定值的某些字段,还可以按他们进行分区
3.修改服务器设置,增加系统缓存
4.优化查询语句,比如尽可能将条件锁定在要查询的表内,如果有join查询,使用精确的join
5.查看程序中执行的方法是否正确

虐人心 2017-06-02 5 楼

PostgreSQL我不熟悉啊同学……
你这个情况是一定要分表的了,至于怎么分表,你可以自己百度……
现在提供一个解决方案,你可以用guzz……这个框架貌似可以实现自动分表什么的,所以用他实现会很方便……
但是他不知道支持PostgreSQL不……

虐人心 2017-05-31 4 楼

我同样对PostgreSQL不太了解,所以单就针对查询语法的本身给你一些建议:

查询中Join了一张表,从查询中来看此表只是为了过滤数据使用,你自己可以评估一下这过滤的范围,如果可以的话能不能把数据取回来以后自己在内存中过滤,这样会比在语法中过滤来的快。
查询中除了 tabl_data_logger 表外还有三个字段用做查询条件, 如果可以的话是不是可以针 tab_value_XXXXXX 这个表增加一个这三个字段为组合的索引? 不能就这三个字段单独建三个索引,那样的话至少有两个索引是无法使用上的。

以上两点如果可以使用的话,那么此查询将会快上非常多。
同时特别提醒一下,对表建上索引的确可以加速查询时的速度(如果查询能用上索引的话),但也不能无限制的建索引,因为索引本身多了,那么此表的平常增删改的速度将会下降。

晚风撩人 2017-05-22 3 楼

现在分析下你的业务特点,看看我没有有理解错误你的意思。

数据量要求:现有数据共4.7亿条,每月3000万左右新数据条目
实时性要求:以时间点、日期、观测站为条件,要求相对较快的从这些数据中找出符合条件的条目,用于绘图。但是对数据的写入实时性要求不高。

你目前的数据分区处理为:按照月度建表,利用触发器将新进入的数据转移到对应的月度分表里面,按照你提供的截图和总数据量,每个分表的数据量都在1亿条左右。

目前你遇到的问题:在单个分表内查询十分缓慢,耗时太长。

因为你没有把表结构截图出来,查询的SQL也没有,我没有使用过PostgreSQL,都在用MySQL。所以我这里也有些盲目的拍脑袋成分。

你这里虽然已经对表进行了分区,但是我没有看到分区的方式,不知道数据分区的方式是否与你的sql语句相匹配,使得查询尽量都集中在某几个分区里面。

另外从表结构上面审视一下,主要关心where条件涉及到的字段。是否存在字符串类型的比较,这个通常很慢。是否建立了正确的索引,如果有联合索引的话,索引的字段顺序是否对你的sql查询起效你的单条sql中是否出现了多次的子查询。这些应该都可以通过类似MySQL中的explain语句看出来。我学到的经验告诉我,能够程序来做的子查询,尽量不要偷懒放到DB里面去做,尤其是很长很恶心的3次以上的in、join之类的。即使程序一次过select多一点出来,放到内存再来过滤也远比在db中快。

按照你这样的数据量来看,你目前的查询有很大的优化空间。

以上是我的一些小建议,不知道有没有理解错你的意思。

偏爱自由 2017-05-20 2 楼

为什么不用Solr呢

想挽留 2017-04-05 1 楼

我看了你的提问,因为对你的业务和数据库结构不了解,所以我难以做出很全面的建议:
1 不要单存的考虑分表来处理大数据,这个对你的数据查询速度影响不会非常大. 可以建议考虑类似于oracle或者sql server 的水平分区.每几个分区划分到不同的磁盘上. 这样可以从物理IO层面,最大量的提高数据库的读写性能. 水平分区最大的不好就是全表做查询的时候有点坑爹.但是这个都可以从业务角度来尽量避免.
2 如果数据库的读和写都很频繁,那么建议你使用读写分离,做复制数据,一个用于写,不加索引,尽量提高写的性能.一个用于读,可以按照自己的需要加很多很多的索引,尽量提高读的性能.