大数据量的处理（向SQL2005专家提问）

[lhsxsh]

有三个表（我只列出数据处理有用到的字段）
表一：--每天在产生新数据之前都会把该表的数据移到表三（[Net_Log）中后，删除该表所有数据（truncate table Net_User）
CREATE TABLE [dbo].[Net_User](
[AutoID] [bigint] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](12) COLLATE Chinese_PRC_CI_AS NULL,
[IF1] [bigint] NULL,
[IF2] [bigint] NULL,
[MsgID] [bigint] NULL,
) ON [PRIMARY]
-----------------------------------------------------
表二：信息内容(每天都会增加记录)
CREATE TABLE [dbo].[Net_Msg](
[Msg] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[IF1] [bigint] NULL,
[IF2] [bigint] NULL,
[MsgID] [bigint] NULL,
) ON [PRIMARY]
-----------------------------------------------------
表三：历史记录表
CREATE TABLE [dbo].[Net_Log](
[UserID] [varchar](12) COLLATE Chinese_PRC_CI_AS NULL,
[MsgID] [bigint] NULL,
) ON [PRIMARY]
业务逻辑：
表一的记录是：先有UserID,再与表二安条件匹配后才有MsgID（只有Net_User表中的UserID与MsgID同时不为空才是有效的一条记录）
每天表一（Net_User）的记录处理完成后都写到表三中（Net_Log）
代码如下：
步骤一
SELECT AutoID,
( SELECT top 1 MsgID
FROM Net_Msg AS m
WHERE IF1=u.IF1 AND IF2=u.IF2
and not exists ( select msgid from Net_MTLog where MsgID=m.MsgID and UserID=u.UserID )

) AS MsgID into #h10
FROM dbo.Net_User AS u
步骤二

通过dbo.Net_User与#h10匹配获得MsgID


表一每天都有20万左右的记录产品。
表三的记录目前有11491254记录。

前几天我试过在表三（Net_Log）改为UserID唯一的记录。多个MsgID（改为varchar(max)类型）用“，”（如：MsgID='，12，13，14，15，'）分串在一起，处理速度也没有现有的快。
全文搜索好像不支持字段匹配（只支持到变量）。
XML我也试过了
<Root>
<row ID="6" />
<row ID="9" />
<row ID="10" />
.
.
.
<row ID="3315" />
<row ID="3316" />
<row ID="3317" />
<row ID="3447" />
<row ID="3448" />
<row ID="3449" />
<row ID="3450" />
<row ID="3451" />
<row ID="3452" />
<row ID="3462" />
<row ID="3463" />
<row ID="3464" />
<row ID="3465" />
<row ID="3466" />
</Root>
处理速度很慢

这样下去我怕不用多久到时处理会很慢。哪位高人可以帮我解决该问题
比如：用何种方式记录用户的历史记录。怎样提高匹配速度。
我是用SQL2005数据库。

[dawugui]

很长很麻烦,先顶后看.

[dawugui]

表三的记录目前有11491254记录。

记录都上亿了,怕是快不了了.

[dawugui]

数错一位,是千万级,不是亿级.

[dawugui]

可对UserID+MsgID建立复合索引,这样应该能提高速度.

[w2jc]

看LZ的这3张表的定义，觉得效率不是很高：

CREATE TABLE [dbo].[Net_User](
[AutoID] [bigint] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](12) COLLATE Chinese_PRC_CI_AS NULL,
[IF1] [bigint] NULL,
[IF2] [bigint] NULL,
[MsgID] [bigint] NULL,
) ON [PRIMARY]

1) UserID的定义可以使用int型，然后有另外一张表存放字符类型的UserID，对int型的操作要比varchar快
2）所有的表都是bigint，可以考虑一下是否真的必要，int在多数情况下是足够的。
建议LZ查联机文档比较一下int和bigint的范围，bigint比int大了整整一倍。

每天20万记录，10年的记录有 200,000 x 365 x 10 = 7.3 亿记录
int 的最大范围是21亿，可以用接近30年，30年后再换成 bigint 都来得及。 :)

前几天我试过在表三（Net_Log）改为UserID唯一的记录。多个MsgID（改为varchar(max)类型）用“，”（如：MsgID='，12，13，14，15，'）分串在一起，处理速度也没有现有的快。
-------------------------------------------
这个办法不好，不能用到索引只会更慢

用XML也不好，操作XML也很慢

有一个倒出历史数据库的策略就可以了，比如历史数据表里面只保存最近2年的数据，其他的备份保存起来。

[ojuju10]

可以用sql 2005的分区表

[华芸智森]

分表,没什么好说的.并且每个表一个文件.如:表: DAT_200801 = > DAT_200801.NDF
统计用过程去统计.
如 DAT_200801,DAT_200802,DAT_200803 .....
每个月数据定到对应的表中.注意建立相应的主键.
历史数据一定要整理一遍. 表 => 设计 => 设置到的文件组.然后 数据库 => 属性.删除相应的旧文件.
过程里按时间统计相应的表.如 2008/1/1到 2008/5/30,则在 DAT_200801 到 DAT_200805 中统计.
用过程一个一个地统计.千成不要生成临时视图一类的.

[华芸智森]

这样.你处理几个亿都不成问题.

[lhsxsh]

潇洒老乌龟(爱新觉罗.毓华)
可对UserID+MsgID建立复合索引,这样应该能提高速度.
谢谢，但这样下去不是办法。
你们想想看有没有更好的处理方法。

[dawugui]

如此大的数据量进行的查询,更新或其他操作,
个人建议你转用oracle.

至于sql server,不知道有什么更好的方法了.
(试试你将数据按日期或地区或其他内容分表行不行?)

[lhsxsh]

公司现没有空的服务器，烦人啊。
曾经也想试过把UserID分表建，但这样的整个系统代码量改造惊人，所以该方法基础上不会考虑。

[flairsky]

sql2005自带的分组分区建表,可能会好点

代码改造不大.

[qiuming0306]

这个问题太高难了吧！

[JiangHongTao]

这种数据量必须分表了，建3个表
1、当天，你已经有了，目前的数据量在20万左右。
2、还要建一个月度表，应该与结构一致，避免与信息表的联合查询，按照你目前的数据量在600万左右。
3、你的历史表将不再用于查询，而是作为数据库仓库的数据源，采用数据库仓库来查询。

[JiangHongTao]

上述表结构的建立对于应用代码应该不需要大的改造，可以很好的提高日常操作的性能。
对于历史查询，还是建议用数据仓库，如果不想用数据仓库，
对于一般的查询还可以在夜间（你的业务应该有夜间空闲时间吧）建立面向userid和MSGID的各类查询统计冗余表，用于统计查询。
如果要查明细，只有加索引并严格限定查询的条件。

[JiangHongTao]

公司现没有空的服务器，烦人啊。
曾经也想试过把UserID分表建，但这样的整个系统代码量改造惊人，所以该方法基础上不会考虑。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
通过分区视图是不需要改造应用代码的，只是需要很多的服务器，并且分区视图需要经验，做的不好也可能达不到效果。

[armyman520]

一个有深度的问题，向各位高手学习一下。

[bkhm07]

顶一下

[lhsxsh]

本人采用“sql 2005的分区表”谢谢 ---ojuju10