海量数据存储和查询优化(问题升级)
我现在为一个程序做了触发器，将数据进入分区，数据总量超过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
);
触发器：
CREATE OR REPLACE FUNCTION insert_values()
RETURNS trigger AS
$BODY$
declare
val_timestamp TIMESTAMP;
val_month TEXT;
val_year TEXT;
val_month_start TEXT;
val_month_end TEXT;
table_name TEXT;
q TEXT;
BEGIN
val_timestamp := NEW.fld_time;
val_month := EXTRACT(MONTH FROM val_timestamp);
val_year := EXTRACT(YEAR FROM val_timestamp);
table_name := 'tbl_value_' || to_char(val_timestamp, 'YYYYMM') ;
IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname = table_name) THEN
val_month_start = date_trunc('month',val_timestamp) ;
val_month_end = date_trunc('month',val_timestamp) + interval '1 month';
q := 'CREATE TABLE ' || table_name ||' (
CHECK (fld_time >= ' || quote_literal(val_month_start) || ' AND fld_time < ' || quote_literal(val_month_end) || ')
) INHERITS (tbl_value)
WITH (OIDS=FALSE);';
EXECUTE q;
q := 'alter table ' || table_name || ' add primary key (fld_index);';
EXECUTE q;
END IF;
EXECUTE 'INSERT INTO ' || table_name || ' (fld_variable_id,fld_time,fld_value,fld_evaluation,fld_site_id)
VALUES (' || NEW.fld_variable_id || ',
' || quote_literal(NEW.fld_time) || ',
' || NEW.fld_value || ',
' || NEW.fld_evaluation || ',
' || NEW.fld_site_id || ')';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insert_values()
OWNER TO postgres;
单变量可视化数据查询
SELECT
v.fld_time as time,
v.fld_value as value
FROM tbl_value_201208 v
LEFT JOIN tbl_data_logger l ON v.fld_site_id=l.fld_site_id
WHERE v.fld_variable_id=69
AND v.fld_time>='2012-08-01 00:00'
AND v.fld_time<='2012-08-02 00:00'
AND v.fld_site_id=332
AND fld_data_logger_id=331
ORDER BY v.fld_time
这些是2012/10/17 对问题的补充
现在采取了按日分表，直接在pgadmin中，开启过约束排除，执行单日数据查询不超过200ms，但是如果用php执行同样的查询还是和之前花费的时间一样，这是为什么？我在执行查询前加了
$this->db->exec("set constraint_exclusion=on");
可是后面的语句执行的时候还是没有遵循我的约束排除
憨得有个性
12 years, 8 months ago
Answers
现在分析下你的业务特点，看看我没有有理解错误你的意思。
数据量要求：
现有数据共4.7亿条，每月3000万左右新数据条目
实时性要求：
以时间点、日期、观测站为条件，要求相对较快的从这些数据中找出符合条件的条目，用于绘图。但是对数据的写入实时性要求不高。
你目前的数据分区处理为： 按照月度建表，利用触发器将新进入的数据转移到对应的月度分表里面，按照你提供的截图和总数据量，每个分表的数据量都在1亿条左右。
目前你遇到的问题： 在单个分表内查询十分缓慢，耗时太长。
因为你没有把表结构截图出来，查询的SQL也没有，我没有使用过PostgreSQL，都在用MySQL。所以我这里也有些盲目的拍脑袋成分。
你这里虽然已经对表进行了分区，但是我没有看到分区的方式，不知道数据分区的方式是否与你的sql语句相匹配，使得查询尽量都集中在某几个分区里面。
另外从表结构上面审视一下，主要关心where条件涉及到的字段。 是否存在字符串类型的比较 ，这个通常很慢。 是否建立了正确的索引 ，如果有联合索引的话， 索引的字段顺序是否对你的sql查询起效 。 你的单条sql中是否出现了多次的子查询 。这些应该都可以通过类似MySQL中的explain语句看出来。我学到的经验告诉我， 能够程序来做的子查询，尽量不要偷懒放到DB里面去做，尤其是很长很恶心的3次以上的in、join之类的 。即使程序一次过select多一点出来，放到内存再来过滤也远比在db中快。
按照你这样的数据量来看，你目前的查询有很大的优化空间。
以上是我的一些小建议，不知道有没有理解错你的意思。
蛋蛋有只笨狮子
answered 12 years, 8 months ago
