数据库 PostgreSQL 接近 6 亿条记录,加了索引,查询效率优化问题?

ailen · 2015年01月22日 · 最后由 jimrokliu 回复于 2015年01月26日 · 19016 次阅读

问题:目前项目中使用 pg9.3,在一个单独的 DB server 上面,有一个表用来统计用户行为,目前接近 6 亿条数据,虽然加了索引,但是查询速度还是非常慢,目前正在调研如何解决性能瓶颈问。

参考解决方案:看了社区里面的帖子,能够想到的解决方案就是对数据做分表操作。

请问大家有没有好的解决方案,给点建议?XD

如果要问这样的问题,你需要说明白数据库表的结构,慢查询的 sql 语句,和 explain 的结果,别人才能给你建议怎么做. 不然我只能大概地告诉你两个字:拆表。

看看查询语句咯

@ch3n @hooopo 数据库执行这样一个操作 explain 出来的结果是这样的 查询出来花了 1878.3ms PS:user_id 和 timestamp 已经加了索引

#3 楼 @ailen 我怎么觉得很快啊,我上次 600W 数据,5 秒都忍受了,随着数据增加,响应 timeout 了,才来优化的。

@small_fish__ 单个查询需要两秒,因为要分析大概 2 个星期的数据,DB time 跟 CPU time 加起来大概 10 多秒,难以忍受啊,多以才要优化的 😄

优化方式有很多啦,也不用分表。

比如 summary 表,定期按需把聚合数据导入到一个新表,在新表里做统计分析。使用 group by + select into 或简单的 ETL 倒一次很快的。

PS. 一般用来分析的表,时间也不这么存,只存到天,甚至存 date_id。

user_id 和 timestamp 已经加了索引 是单独建索引还是联合索引,联合索引和单独索引差异很大。

如果建立 [user_id, timestamp] 的联合索引,应该不至于这么慢。

另外,为啥要 distinct 的,这个操作对数据库通常是要命的。

同意 6 楼

@hooopo @vincent 谢谢,我试试看

@Peter 我用的 DO 的$320/m 的服务,就是 SSD

做统计数据,数据表实在太大了,通常要组合下面的方法

  1. 正确的索引设计是根本,而且简单有效,立竿见影,无论后续方法是否采用,索引先弄好再说;
  2. 使用单独的统计表,使用统计表大幅加大数据粒度,利用业务低谷时间预先计算,再做数据查询就简单高效多了,而且减少对明细数据表的影响;
    1. 分表,按时间或者地区或者用户等分成多张表,这样有利有弊,好处是提升某些查询效率,坏处是使用方式要复杂不少,需要自己权衡。

6 亿条数据也该拆表了. 根据你的查询,可以根据 userid 把数据分布在多个表。也可以根据时间,每两周建一个新表存最近两周的数据。

现在都流行查询原始数据表吗?

兄弟,你修改方案使用实时计算统计系统了。DB 只做存储备份。

除了必要的优化外,hadoop 类工具离线分析吧。 #15 楼 @cloudqq 👍

@hooopo @ch3n 谢谢,目前在考虑拆表方面的设计

试试别再提供 Service 了。 就说不带这么玩的。

@gazeldx 哈哈,没办法,运营要看数据

@vincent 我看了分表的话好像是从空表开始的,如果是将这已经存在的 6 亿条数据分表,有什么好的建议呢?

可以考虑这样操作:先把数据导出成数据文件,建立好目标分区表,然后把数据导入,重命名表。

这个方法是 mysql 下的经验,重命名表在 mysql 下很快,我对 pg 不太了解,不知道在 pg 下怎样。

@cderong 被坑过,是从 mysql 坑过,就是从 mysql 切换到 pg 的

#23 楼 @ailen 什么坑?可以说说?

加了索引不一定快,要看数据的分布,你先看哪个语句慢,条件是什么,这个条件下,数据的分布如何,如果这个条件下,数据超过 10% 的分布,那这个索引基本也提高不了性能,因为筛选出的数据集还是很大。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号