【求助】过亿级大数据表查询优化问题

有这样一个数据库(sql server 2000)，之前因为没有考虑到会有那么多的数据，现在已有上亿条数据了，查询相当慢，问题比较多肯请各位看完，谢谢先，只对sql2000熟悉

产品表product 表结构如下，此表数据是会员通过web上传excel文件，直接批量导入，大的excel也有7万条左右产品
id：int 主键，自动编号
xh：varchar(50) 型号字段， 主要是由a-z，0-9及- / 这些组成的字符串，以a-z,0-9开头
rq：datetime 日期字段， 这个字段会频繁更新，供会员每天刷新发布已发布的产品日期为最新日期
uid：会员ID字段，与会员表中的UserID关联
pp：品牌 nvarchar(50)
fz：封装 nvarchar(50)
ph：批号 nvarchar(50)

请针对以下问题给出SQL 2005的解决办法
问题1、上传excel后，要判断上传的数据是否跟product已有数据相同
1.1、我是这样子做的，建两个中间表T_Old,T_New，表结构跟Product一样，每次导入入清空下这两个表，然后把当前会员已上传数据读取到T_Old表，把新上传的导入到T_New表
1.2、把表T_New中的与表T_Old相同的记录删除，按xh,pp,fz,ph这4个字段判断，一样的为相同记录
1.3、把T_Old再导入到product表
每个会员的数据都要生成静态的html页面，我把每个会员的数据存到access（每个会员都建一个结构一样的数据库）及sql server各一份，当要读取某一会员的数据时，直接到access读，会不会比在sql大表中用where uid=1这样更快呢？

问题2、查询字段主要按xh(型号字段)，按日期降序,这两个字段我都建立了非聚集索引
如有以下记录
行1：abcd
行2：abcde
行3：abcd123
行4：123abcd
行5：123abcd2
为了不引起全表搜索，查询时必须输入要检索的内容的前4位，如abcd则可以搜索到行1-3，搜索不到行4-5
语句：....xh like 'abcd%' order by rq desc
有没有好的能快速查询的方法呢？好像全文索引可以搜索到行4、行5的记录是吗？这么大的表经常有更新，建全文索引会不会开销很大呢？如果没办法就只好用上面的方法了[/color]

问题3、听说sql2005用分区表将大表分隔成多个小表会快很多，网上也看了些文章很多都是按日期字段做为分区列
3.1、按xh型号字段首字母作为分区列 a-z 分26个子表 0-9 分10个子表，这样就36个子表了，这样分区函数要怎么写呢？
3.2、每个子表指定单独文件组去，速度会更快些？服务器是万网的云主机，只有C，D两个分区
3.3、我在想，每个会员他自己的所有产品每条记录rq字段值都一样的(因为是通过excel一次性导入)，所以能不能把这个日期字段去掉，移到member表中的rq字段(保存最后一次刷新产品日期)，这样应该是省了好多空间了，但对查询速度不知道会不会影响，

select p.*,m.rq from product as p inner join member as m
 on p.uid=m.userid where xh like 'abcd%' order by m.rq desc

7 个解决方案

#1

问题1
你的处理没有什么不妥，关键是uid应该有索引提取数据的速度才能快
用access存储没有必要，如果uid有索引，直接读可能也不慢，需要测试来比较速度差别

#2

问题2
全文搜索解决不了你的问题，暂时没有更好方法，或许分表或者分区表能提高一些速度

问题3
3.1大概如下，可能需要多出中间一个区，如果保证不会出现可以不需要':'这个区
CREATE PARTITION FUNCTION [PF_Test](varchar(20)) AS RANGE RIGHT FOR VALUES (
'0','1','2','3','4','5','6','7','8','9',':'
,'a','b',...,'z'
)
GO

3.2那就不分驱动器，速度差别不会很大
3.3没必要吧，DATETIME字段就8字节，差不了多少，分开后导入过程以及查询都复杂不少，不值得

#3

1、直接左连接就能判断排除掉相同的数据
2、分区是增加管理灵活性，当然在正确的磁盘与数据文件设计下会并行提高性能，但通常来说主要增加管理灵活性，而非日常应用性能

#4

引用 2 楼 Haiwer 的回复:

问题3
3.1大概如下，可能需要多出中间一个区， 如果保证不会出现可以不需要':'这个区
CREATE PARTITION FUNCTION [PF_Test](varchar(20)) AS RANGE RIGHT FOR VALUES (
'0','1','2','3','4','5','6','7','8','9',':'
,'a','b',...,'z'）

这里不要用取字段首字母啥的操作吗？比如我如输入xh数据是 Haiwer这个就会分到h这个区是吗？对了不知道会不会有字母大小写问题，没有接触过分区，也就是最近临时上网找些文章看了看

引用 2 楼 Haiwer 的回复:

3.3没必要吧，DATETIME字段就8字节，差不了多少，分开后导入过程以及查询都复杂不少，不值得

DATETIME字段就8字节，如果是1亿条记录，这1亿条的DATETIME字段也不影响吗

麻烦大哥再回答我上面的疑问，再次感谢

#5

查询基准已xh和时间为主要条件，干脆将聚集索引改到时间上面，这样速度回提升很多。

#6

引用 3 楼 luckyrandom 的回复:

1、直接左连接就能判断排除掉相同的数据
2、分区是增加管理灵活性，当然在正确的磁盘与数据文件设计下会并行提高性能，但通常来说主要增加管理灵活性，而非日常应用性能

跟大表左连接排除相同数据，方法是可以，就不知道速度会不会慢

引用 5 楼 jack11430 的回复:

查询基准已xh和时间为主要条件，干脆将聚集索引改到时间上面，这样速度回提升很多。

主要是按xh字段搜索，日期只是排序

#7

1、理论上来说，分区函数可以用函数来确定分区，但是由于sql server的函数往往效率很差，实际使用尽量避免使用函数，字符串开头分区的话没有必要，我写的分区函数,'h','i'表示 >='h' and <'i'的全部放在一个区，那样所有h开头的字符串就在这个区，haiwer自然在这个区。
2、我的意思不是说增加字段没有影响，而是影响不大；关键是你分开之后查询和写入过程增加的复杂程度是否合算。