【求助】大数据量下，对表字段GROUP BY排序效率问题

[luckdwj]

例如一个表保存一些网站的新闻标题和url记录等 表结构如下

id host title url ord(排序) time
1 www.sina.com **新闻标题 http://www.sina.com/aa.html 1 2011-1-1
2 www.sina.com **新闻标题2 http://www.sina.com/bb.html 2 2011-1-1
。。。
。。。。



0.目前数据量有1千万了，还在增加
1.站点数量不确定，至少上万个。
2.title中，同样的标题可能会产生几个不同的host和url，因为文章抄袭的还是很多的
3.同样的url也会对应几个不同的title和host


现在遇到查询显示的问题

按列数量排序的语句很慢，例如按host排序
select top 500 host,count(*) as tj from tb group by host order by tj desc

而且还想按title和url也统计排序
如果再加上条件的话，查询就更慢了


求优化方法

[luckdwj]

对于这个需求 这个表设计上有缺陷吗

[nzperfect]

仅仅是select top 500 host,count(*) as tj from tb group by host order by tj desc要多久？
贴一下结果：

set statistics io on
select top 500 host,count(*) as tj from tb group by host order by tj desc

[唐诗三百首]

[Quote=引用 10 楼 luckdwj 的回复:]
引用 9 楼 luckdwj 的回复:

ap0405140 如果想再根据title 或者url查询的话 也要再建索引吗? 前端的组合查询就不方便了


不是索引 是建视图 写错了
[/Quote]
可以建多个索引视图,针对不同的组合查询情况.

[luckdwj]

回15楼 1分钟....

[nzperfect]

[Quote=引用 17 楼 luckdwj 的回复:]

回15楼 1分钟....
[/Quote]
贴一下消息栏内容。

[luckdwj]

3分钟40秒 电脑配置烂也是一部分原因

(500 行受影响)
表 'tb'。扫描计数 3，逻辑读取 66582 次，物理读取 260 次，预读 30204 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

[nzperfect]

[Quote=引用 19 楼 luckdwj 的回复:]

3分钟40秒 电脑配置烂也是一部分原因

(500 行受影响)
表 'tb'。扫描计数 3，逻辑读取 66582 次，物理读取 260 次，预读 30204 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
[/Quote]

读66582个page需要1分钟的话，大概是66582*8/1024/60=8M/s
你是服务器还是pc ?

[luckdwj]

台机 E2180 2G
而且上面还跑着其他程序
资源有限 没辙

求建议 这个查询速度根本没法活

[nzperfect]

如果仅仅是这个一查询的话，可以每5分钟跑一个job，将结果存在一个表内，然后从该表读数据。
数据本身就是时时变化的，5分钟的差异看你能不能容忍了。
但如果你还有一些like条件的话，就基本无解。

[luckdwj]

嗯 这个是个方法 多谢


再看有没其他建议，明天结贴

[nzperfect]

主要是硬件太差了，就像一个BYD F3，怎么可能暴走？

[luckdwj]

[Quote=引用 24 楼 perfectaction 的回复:]

主要是硬件太差了，就像一个BYD F3，怎么可能暴走？
[/Quote]

资源有限 资源有限啊 .....

[NBDBA]

这个数据量，这个设计，硬件好也有顶不住地时候。

建议考虑：
1、至少要增加一个统计表，以你现在说到的需求，可以只有 host,tj，统计时间三个字段。
2、在数据插入过程，插入或者修改这个统计表。
3、如果觉得2有问题，增加膈定是作业定时统计这个表的数据，尽量是增量统计的。

4、这步与统计基本无关，你的原来的表，在数据量足够大的情况下，应该要考虑分表了，方法不少，比如历史表+当前表，或者年表、月表等方式。

[budong0000]

建议先group by出数据放临时表，再order by

或者每小时统计一次group by，放一统计表，然后从些统计表中查询。

[ziping]

修改下设计,多建立一个表UHostLst 记录HOST名称(HostName varchar类型)和HOST编号(HostID int 类型)
select * from
(select top 500 hostID,count(*) as tj from tb group by hostID order by tj desc) a inner join UHostLst b on a.HostID=b.HostID
不知是否会快点。

[NBDBA]

不可能变快，只可能比原来更慢

[luckdwj]

多谢各位关注 现在用的是 新建表 然后定时更新数据的方法

继续期待其他建议

[--小F--]

select top 500 host,count(*) as tj from tb group by host order by tj desc


这个语句已经很优化了 建议在host字段加索引

[luckdwj]

已经建了索引了 但是显示的速度比较慢 前端根本等不及

[langxingcs]

count(*) 改成count(主键)