首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

过亿级大数据表查询优化有关问题

2013-03-06 
【求助】过亿级大数据表查询优化问题有这样一个数据库(sql server 2000),之前因为没有考虑到会有那么多的数

【求助】过亿级大数据表查询优化问题
有这样一个数据库(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
sql2005? sql2005 分区表 大容量数据表 查询优化
[解决办法]
问题1
你的处理没有什么不妥,关键是uid应该有索引提取数据的速度才能快
用access存储没有必要,如果uid有索引,直接读可能也不慢,需要测试来比较速度差别


[解决办法]
问题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字节,差不了多少,分开后导入过程以及查询都复杂不少,不值得
[解决办法]
1、直接左连接就能判断排除掉相同的数据
2、分区是增加管理灵活性,当然在正确的磁盘与数据文件设计下会并行提高性能,但通常来说主要增加管理灵活性,而非日常应用性能
[解决办法]
查询基准已xh和时间为主要条件,干脆将聚集索引改到时间上面,这样速度回提升很多。
[解决办法]
1、理论上来说,分区函数可以用函数来确定分区,但是由于sql server的函数往往效率很差,实际使用尽量避免使用函数,字符串开头分区的话没有必要,我写的分区函数,'h','i'表示 >='h' and <'i'的全部放在一个区,那样所有h开头的字符串就在这个区,haiwer自然在这个区。
2、我的意思不是说增加字段没有影响,而是影响不大;关键是你分开之后查询和写入过程增加的复杂程度是否合算。

热点排行