一个性能优化的问题

[lovaling]

前次发帖询问了一个查询“购买了A物品的用户也购买了××物品”的问题，帖子见：
http://community.csdn.net/Expert/topic/4440/4440680.xml?temp=.4613764

经 rardge(Rardge) 解答，已经写出了一个可以工作的版本。

不过在数据表中有20万条数据时，查询一次耗时400毫秒左右，不加LIMIT返回999行，实际可能不止这么多行，999可能是数据库的设置吧。


数据表的模式：
CREATE TABLE mm_view_scene (
scene_id BIGINT NOT NULL REFERENCES scene(id),
member_id BIGINT NOT NULL REFERENCES member(id),

PRIMARY KEY (scene_id, member_id)
);

查询语句：
SELECT scene_id, count(*) AS view_count FROM mm_view_scene WHERE member_id IN
(
SELECT member_id FROM mm_view_scene WHERE scene_id = 30196
)
AND scene_id <> 30196
GROUP BY (scene_id)
HAVING COUNT(*) >= 1
ORDER BY COUNT(*) DESC
LIMIT 5000;


我用explain的结果：
"Limit (cost=5396.39..5396.40 rows=5 width=8)"
" -> Sort (cost=5396.39..5396.89 rows=199 width=8)"
" Sort Key: count(*)"
" -> HashAggregate (cost=5385.31..5388.79 rows=199 width=8)"
" Filter: (count(*) >= 1)"
" -> Hash IN Join (cost=1197.10..5370.97 rows=1912 width=8)"
" Hash Cond: ("outer".member_id = "inner".member_id)"
" -> Seq Scan on mm_view_scene (cost=0.00..3581.21 rows=191216 width=16)"
" Filter: (scene_id <> 30196)"
" -> Hash (cost=1194.69..1194.69 rows=961 width=8)"
" -> Bitmap Heap Scan on mm_view_scene (cost=10.36..1194.69 rows=961 width=8)"
" Recheck Cond: (scene_id = 30196)"
" -> Bitmap Index Scan on mm_view_scene_pkey (cost=0.00..10.36 rows=961 width=0)"
" Index Cond: (scene_id = 30196)"


我想慢的原因应该是WHERE member_id IN...这个地方吧，IN需要一条一条去判断。不知道如何才能优化一下速度，实际使用时，表中记录可能达到千万条。

[lovaling]

SELECT member_id FROM mm_view_scene WHERE scene_id = 30196
这个子查询大概会返回1000条数据，用IN判断可能效率比较低。

不知道postgresql中有没有二分查找的函数？如果有的话我考虑把子查询排序，然后用二分来查找

[lovaling]

看样子这样也提高不了多少，把子查询直接换成一个数组(1,2)，20万条数据查询速度由406毫秒提高到312毫秒，如果使用二分查找，可能函数调用的开销就已经抵销了这点优化了。

主要问题每行都要遍历到，这样看来20万行400毫秒已经够少的了。如何才能有效提高查询速度呢？

目前数据是这样来生成的：
一共有10000本书和10000个会员，每个会员有10%的机会买过某本书，这样就生成了1000万条数据。实际情况当然不会有这么高的比例，但数量肯定会比这要多，即便速度提高10倍，我觉得还是偏低了些。

[lovaling]

修改了一下，速度提高了3倍，下面的查询1比查询2快3倍。

CREATE TABLE test (
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
PRIMARY KEY (id1, id2)
);

-- 插入测试数据
CREATE OR REPLACE FUNCTION
insert_data ()
RETURNS INTEGER AS
$$
DECLARE
v_temp1 INTEGER;
v_temp2 INTEGER;
v_temp3 INTEGER;
BEGIN
FOR v_temp1 IN 0 .. 999 LOOP
FOR v_temp2 IN 0 .. 999 LOOP
v_temp3 := (random() * 1000)::INTEGER % 1000;
IF v_temp3 < 200 THEN
INSERT INTO test (id1, id2) VALUES (v_temp1, v_temp2);
END IF;
END LOOP;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


SELECT insert_data();



-- 查询1
SELECT t.id1, COUNT(*) FROM test t, (SELECT id2 FROM test WHERE id1 = 500) AS r
WHERE t.id2 = r.id2
AND t.id1 <> 500
GROUP BY t.id1
ORDER BY COUNT(t.id1) DESC
LIMIT 10;


-- 查询2
SELECT id1, COUNT(*) FROM test WHERE id2 IN (
SELECT id2 FROM test WHERE id1 = 500
)
AND id1 <> 500
GROUP BY id1
ORDER BY COUNT(id1) DESC
LIMIT 10;

[lova1ing]

错了。。。

在id2上建了个索引，查询2的速度提高了近20倍，查询1则变化不大。

CREATE INDEX idx_test_id2 ON test(id2);

[lova1ing]

终于又优化了一点：

创建另一个表：
CREATE TABLE test1
(
id2 int4 NOT NULL,
id1 int4 NOT NULL,
PRIMARY KEY (id2, id1)
);
数据和test一模一样。
buy_count信息则放到相关产品的表格中，每次有购买时就更新，这样可以省去统计的开销。
CREATE TABLE book
(
id int4 NOT NULL,
name varchar(10) NOT NULL,
buy_count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (id1)
);

SELECT b.id, b.buy_count FROM book b, (
SELECT id1 FROM test1 WHERE id2 IN
( SELECT id2 FROM test WHERE id1 = 5000 ) GROUP BY id1) r
WHERE b.id = r.id1 AND b.id <> 5000
ORDERY BY buy_count DESC
LIMIT 10;

现在1000万条数据查询不足一秒。主要是因为id2排在test1的主键的前面，在作IN查询时速度极快，由于这个查询的结果记录已经不多，剩下的查询并ORDER都是很快的。

[ameny]

阿哥，都优化到不足一秒了，好结帖了。