海量数据库相关处理问题

[limfungsuen]

1、因为之前没做过大数据处理项目，陆续在这里请教过不少类似的问题，很多大侠热心的帮助了我。
为了方便这里的大侠们了解，我尽量仔细的说明：
之前的三个帖子，比较新一点，回复过的大侠可以很快的了解我的问题所在：
http://topic.csdn.net/u/20090907/10/627bfeec-8bda-4d99-aded-e13435e7c4fe.html
http://topic.csdn.net/u/20090907/16/80f35fbd-3c6c-4ca0-80f7-d55806bf0ddf.html
http://topic.csdn.net/u/20090901/09/b3cd8e71-19be-4694-b43b-0e57cdf4bef2.html

2、针对举例的第二个贴子，为了方便描述，我在这里根据大侠的提议，提出我这边数据搜索的整个问题，分数100；如果系统允许的话，我可以加分数，这个系列问题困扰了我很久；本来我还在尝试用前面几楼提议的去优化，但是感觉真的怎么优化都不咋样····现在50多W的数据，搜索的时候已经很慢了，特别是关键字没有匹配的时候，会全表遍历2边，很慢。（一遍是获取相关总记录数：用于分页（用的是开源的AspNetPager），一遍是获取相关的记录）

3、简单介绍一下这个数据库和硬件，主要用来查询一些信息，目前50W左右数据，以后的话，至少是几百w的数据，甚至是上kw（这个目前先不考虑）；存放的是独立托管的一台至强系列服务器，4核，4G内存。数据库和程序放开存放不同的盘符（也可以做到分开存放到2台服务器--如果效率会快的话）；系统如果说要跟什么像，有点类似搜索引擎。

4、数据库表：相关的几张表简单描述： InfoTmp、InfoReal、KeyTmp、KeyReal、MyCounry、MyCategory、MyTopic
【InfoTmp】：用于存放临时数据，这些数据都是人工添加的，只有通过审核才会加入InfoReal表；
【InfoReal】：正式数据，用于前台显示；
【KeyTmp】：第3点说到有点类似搜索引擎，就是在这里，用户每搜索一次的关键字，会加入到这个KeyTmp，然后通过计划任务，每天24点，整理后（相同的关键字合并，统计数量；如果正式key表存在则加数量，不存在则新加记录）加入KeyReal；
【MyCounry、MyCategory、MyTopic】：分别存放的是国家，类别，主题信息。
表结构简单举例：
【InfoReal】
[CI_ID],[CI_CorpName],[Country_Continent],[Country_ID],[C_FatherID],[C_ChildID],[CI_MobilePhone],[CI_StateFlag],[CI_CreateTime],[CI_ModifyTime],[CI_ClickTimes],[CI_BuyTimes],[CI_Keywords],[CI_SearchKey]
,[topic_ID],[CI_Charge].......

主要问题所在的存放内容举例：
-----------------------------
CI_ID C_FatherID C_ChildID
10024 1024,1025,1056, 1045,1088,1045,1054,1063,
10025 1024,1025,1056, 1045,1088,1045,1054,1063,

-----------------------------
-----------------------------
【MyCategory】存放内容举例： C_FatherID：0表示为大类
C_ID C_FatherID
1024 0
1045 1024
1054 1025
-----------------------------
-----------------------------
【MyCounry】存放内容举例： Country_Continent:表示洲，1亚洲，2欧洲....
Country_ID Country_Continent
1024 1
1045 2
1054 3
-----------------------------
-----------------------------
问题描述：最初的主表设计，信息对于类别（国家，类别，主题是唯一对应的），后来项目已经做好了，领导要求修改：一条信息可以属于多个小类别（也可以属于不同的大类）。于是我把InfoReal的[C_FatherID],[C_ChildID]都改为了字符型，存放的内容见上面内容举例。
再则，最初是没有C_FatherID和Country_Continent；最初数据搜索，都是选择一个大类，然后选择相关小类；国家也一样，先选择洲，然后选择具体国家；后来需求改变，改成了，可以选择1个洲或者多个国家（可以属于不同洲）；1个大类或者多个小类（可以属于不同大类）；同时还有主题，价格，排序等一些附加搜索条件...于是我陆续在这里请教了一些问题。最初因为项目结构都搞好了，我实在不想改动，后来结合大家的意见和搜索速度实在慢，感觉非常有必要改动。于是我开了这个帖子。

下面我贴出取数据列表的存储过程并做简单说明：

ALTER PROCEDURE [dbo].[corpListRealUser]
	-- Add the parameters for the stored procedure here
	@myContinent varchar(10),	--传入洲的值
	@myCountrys varchar(50), --传入国家的值；已处理成sql条件语句，后面说明
	@myCategoryB varchar(10), --传入大类的值
	@myCategorys varchar(50), --传入小类的值；已处理成sql条件语句，后面说明
	@myTopic decimal,  --传入主题的ID
	@myPriceL int,  --传入价格范围的起始
         @myPriceH int,  --传入价格范围的终点
	@myOrderType int,  --传入排序类型（按时间，价格，点击量，购买量）
         @myOrderFlag int,  --传入排序标志（升序还是降序）
	@mySerKeyword varchar(500),  --传入关键字
	@CI_StartRecordIndex int,  --传入分页起始点
	@CI_EndRecordIndex int   --传入分页终结点
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Insert statements for procedure here
	declare @str varchar(max),@strOrder varchar(200)
	set @str=''	
	if @myTopic>0
		begin
		set @str=@str+'and topic_ID='+rtrim(@myTopic)
		end
	if @myPriceH<500
		begin
		set @str=@str+' and CI_Charge BETWEEN '+rtrim(@myPriceL)+' AND '+rtrim(@myPriceH)
		end
	if @myContinent<>'0'
		begin
		set @str=@str+'and Country_Continent='+rtrim(@myContinent)
		end
	if @myCountry<>'0'
		begin
		set @str=@str+@myCountrys
		end
	if @myCategoryB<>'0'
		begin
		set @str=@str+'and C_FatherID='+rtrim(@myCategoryB)
		end
	if @myCategoryS<>'0'
		begin
		set @str=@str+@myCategorys
		end
--这里的myCountrys和myCategorys都已在前台程序处理成：and (charindex(',195,',','+InfoReal.C_ChildID)> 0 or charindex(',81,',','+InfoReal.C_ChildID)> 0 or charindex(',64,',','+InfoReal.C_ChildID)> 0-----这种语句，结合我最上面举例到的三个帖子，大侠的给我建议是把charindex改为like；然后把or，改为union all模式-----因为语句的问题，还有其他条件，改成union all我也是比较的懵，不太会改；然后第二个帖子提到了，结构问题，很关键，让我意识到我不改结构，其实速度是无法改变慢的局面的。
	if @mySerKeyword<>''
		begin	
		set @str=@str+'and (CI_SearchKey like ''%'+ @mySerKeyword +'%'')'
		end
--以下部分是排序条件
	set @strOrder=''
	declare @orderPX varchar(10)
	if @myOrderFlag=1
		set @orderPX=' asc'
	else
		set @orderPX=' desc'
	if @myOrderType=1
		begin			
			set @strOrder=@strOrder+'order by CI_ModifyTime'+@orderPX	
		end
	else if @myOrderType=2
		begin			
			set @strOrder=@strOrder+'order by CI_Charge'+@orderPX	
		end
	else if @myOrderType=3
		begin			
			set @strOrder=@strOrder+'order by CI_ClickTimes'+@orderPX	
		end
	else if @myOrderType=4
		begin			
			set @strOrder=@strOrder+'order by CI_BuyTimes'+@orderPX	
		end
	else
		begin			
			set @strOrder=@strOrder+'order by CI_ModifyTime'+@orderPX	
		end
	

--下面部分简单说明：1、infoReal表为了加快速度，最初关键字搜索匹配是跟CI_CorpName和CI_Keywords和CI_Profile用or连接；后来加了CI_SearchKey这个字段，取值是把前面三个字段值结合，然后截取250个字符做的搜索关键字。2、为了加快速度，我最大也只取600条数据；如果数据库匹配的数据多，速度是相对快点，如果没有匹配或者没有达到600条，就会表全部扫描一遍(加上分页读取记录总数，相当于2边)，速度就相对比较慢。3、搜索相关的条件字段我都有做索引，CI_ID是主键唯一，聚集索引我是做在了CI_SearchKey这个字段上（就是搜索关键字）4、CI_StateFlag=1 or CI_StateFlag=3这里我用了OR结构是不是索引也不起效果了？？这个字段是用来判断数据是否在前台显示的判断标识符。

	declare @sql varchar(max)
	set @sql = 'select * from 
	(select top 600 row_number() over ('+rtrim(@strOrder)+',CI_ID desc)as Row,
	CI_ID,CI_CorpName,CI_Profile,CI_StateFlag,CI_Charge,CI_ModifyTime,CI_ClickTimes,CI_BuyTimes,Country_ID from TE_CorpInfoReal where (CI_StateFlag=1 or CI_StateFlag=3) '+@str+') t       
	WHERE 1=1 AND Row BETWEEN '+rtrim(@CI_StartRecordIndex)+' AND '+rtrim(@CI_EndRecordIndex)+' order by row'
	exec(@sql)
END 

------------------------------------------------
------------------------------------------------
综上；我希望大虾们在尽量不改变需求下给个系列的修改建议，包括：硬件方面的建议【因为小公司，希望提建议到时候这个方面理性一点】；分页问题；表结构问题；数据搜索显示问题。（或者说向类似的这种需求，会怎么去设计相关的一系列内容）

我很诚恳地希望知道的大侠赐教，如果不太熟悉的人也可以一起研究学习下，我感觉这个是大数据处理必然遇到的问题。

[chendi1985]

看了一下，首先我看了个最不能见到的问题varchar(max)设置那么大干吗？这不是很很占内存，降低使用效率吗？还有聚集索引肯定要建的，一些搜索条件我个人觉得为了防止传入为空，直接在程序后台控制，,排序一样的.这样传入到sql中的有效sql查询语句都是有效语句。这样有助于提高查询效率。

[yp395234871]

顶,有时间再看

[limfungsuen]

[Quote=引用 134 楼 chendi1985 的回复:]
看了一下，首先我看了个最不能见到的问题varchar(max)设置那么大干吗？这不是很很占内存，降低使用效率吗？还有聚集索引肯定要建的，一些搜索条件我个人觉得为了防止传入为空，直接在程序后台控制，,排序一样的.这样传入到sql中的有效sql查询语句都是有效语句。这样有助于提高查询效率。
[/Quote]

1、感谢提醒，max这个问题我本地已经改掉了，之前数据传入值因为需求变更一直增多，老是会截断数据，我偷懒暂时设置了MAX。
2、“还有聚集索引肯定要建的”--这个我也是知道的，只是在某个时期，我不确定建在什么字段上好了，我从最初的建立在CI_ID上，然后改到SEARCH_KEY上，最后又有人建议改到CI_ID上。
3、“一些搜索条件我个人觉得为了防止传入为空，直接在程序后台控制，,排序一样的.这样传入到sql中的有效sql查询语句都是有效语句。”--貌似我就是这样做的啊，传入类空的，我都没有写SQL语句，只操作真实传值的语句。

再次感谢所有回答和顶贴的人，我会不时的上来，一起讨论有意义的建议和回复；然后----继续等大侠的解答和建议。（如果我有好的解决方法也会发上来跟大家分享）
--------------------------------------------------------
--------------------------------------------------------
PS：请晚来的大侠，除了看下这个帖子外（因为帖子不允许更改），也仔细看下这个帖子我的那些回复，因为针对大侠们的提议，我有针对的会更加详细的说明问题和提出自己的想法.这样方便大侠们了解我真正的需求，或者暴露我的问题所在。
我的开发环境：Microsoft Visual Studio 2005(.NET C#) + MS SQL 2005 + WIN SERVER 2003
--------------------------------------------------------
--------------------------------------------------------

[Mr_lihai]

学习

[zjh222]

微软一向是以垄断为发展方向,我们在以前知道,Java在Windows上的运行性能远远差于Linux等OS,有时在Windows上还会出现一些这样或那样的问题.....
现在有些大的软件产品都强调在Lunix等其它操作系统上的性能与稳定,如Oracle,Sybase,DB2....太多的产品支持Linux 了.......虽然有的产品也发现了Windows版,但是许多企业也不敢在Windows上跑它们的核心业务.......
离了微软的阵营,外面的世界很精彩............
通过以上说明...如果你用微软的产品,如楼主的C#+.NET,SQL Server当然是最好的...为了产生不必要的麻烦浪费你的青春...你最好就用微软的产品.....如果你喜欢Mysql就用Linux....
这个世界是竞争的世界...不同的产品间,不同的平台间,不同的公司间,不同的种族间....呵呵...
竞争就会要手段...Java在Windows上,微软就用了手段(有大量的微软粉丝不承认这点)...现在Oracle的访问组件以后微软也不提供了由Oracle提供ODAC
...也说不清那天微软又在.Net上使手段,让你访问Oracle超慢这是有可能的...
楼主三思

[luats]

标记下...

[QQ153984069]

学习了，并关注。

[i8013]

50万条记录普通电脑搜索需要一秒以上都是表结构有问题。

[p2bl]

所有使用like模糊查询的列上建全文索引并且使用contains关键字查询，还有的你的sql语句太复杂
建议把sql语句拆分执行，看哪部分最慢

[LIANGQIAN1984]

mark

[limfungsuen]

不好意思啊~最近又有新项目做~~烦啊，这个问题一直搁浅了~~暂时也没时间自己去尝试一些大家好的建议~~我也一直在吸收大家的建议~~~想了下，这个帖子暂时还是不结~~~继续等待~~~


再次感谢所有回答和顶贴的人，我会不时的上来，一起讨论有意义的建议和回复；然后----继续等大侠的解答和建议。（如果我有好的解决方法也会发上来跟大家分享）
--------------------------------------------------------
--------------------------------------------------------
PS：请晚来的大侠，除了看下这个帖子外（因为帖子不允许更改），也仔细看下这个帖子我的那些回复，因为针对大侠们的提议，我有针对的会更加详细的说明问题和提出自己的想法.这样方便大侠们了解我真正的需求，或者暴露我的问题所在。
我的开发环境：Microsoft Visual Studio 2005(.NET C#) + MS SQL 2005 + WIN SERVER 2003
--------------------------------------------------------
--------------------------------------------------------

[urdoom]

这位兄弟，我对这种分类有现成的模型可以用，你就舍弃原来的一个大类项目里存放好多个大类，一个小类项目里存放多个小类的做法吧，这是你慢的根源啊

简单描述一下模型，你应该能看懂

数据表
序号 数据内容略 数据ID
1 aaa 0000000000000000000123
2 bbb 0000000000000000000124

数据类别对应关系表
数据ID 大类 小类
0000000000000000000123 1001 200001
0000000000000000000123 1002 200003
0000000000000000000124 1001 200005
0000000000000000000124 1002 200006

类别表
类ID 类名称 类分类
1001 大1 1
1002 大2 1
200001 小1 2
200003 小3 2
200005 小5 2
200006 小6 2

这样的表结构才能根本解决问题，我也在工作，时间不多
另外给你点提示
大数据处理，不要尝试用任何语言逐条去reader里读取结果处理
尽量都写在存储过程里
我之前用VB.net处理800w记录，输出为二进制文件，用时1小时40分钟
后来改为BCP，全部用数据库服务器处理，只用了1分30秒！

[urdoom]

[Quote=引用 139 楼 zjh222 的回复:]
微软一向是以垄断为发展方向,我们在以前知道,Java在Windows上的运行性能远远差于Linux等OS,有时在Windows上还会出现一些这样或那样的问题.....
现在有些大的软件产品都强调在Lunix等其它操作系统上的性能与稳定,如Oracle,Sybase,DB2....太多的产品支持Linux 了.......虽然有的产品也发现了Windows版,但是许多企业也不敢在Windows上跑它们的核心业务.......
离了微软的阵营,外面的世界很精彩............
通过以上说明...如果你用微软的产品,如楼主的C#+.NET,SQL Server当然是最好的...为了产生不必要的麻烦浪费你的青春...你最好就用微软的产品.....如果你喜欢Mysql就用Linux....
这个世界是竞争的世界...不同的产品间,不同的平台间,不同的公司间,不同的种族间....呵呵...
竞争就会要手段...Java在Windows上,微软就用了手段(有大量的微软粉丝不承认这点)...现在Oracle的访问组件以后微软也不提供了由Oracle提供ODAC
...也说不清那天微软又在.Net上使手段,让你访问Oracle超慢这是有可能的...
楼主三思
[/Quote]

这位兄弟虽然说得在理，不过对于大多数无法决定开发及运行环境的程序员来说，还是远了些
很多公司是买不起Oracle的...

[cnnbtop]

进来学习～～

[lingjoin]

光在数据库架构内肯定是不能解决楼主所述问题的；尤其是一旦牵涉到文本字符串类型的操作，系统性能基本上不可用，如果是在线服务的话，往往会因为延时导致服务中断。解决的思路是：在数据库基础上外加一套索引搜索系统，类似地搭建一个全文搜索系统，可以达到的效果相当于在数据库系统基础上构架了一个搜索引擎，索引的搜索都可以在毫秒级别解决，搜索引擎支持TB数据实际上是很轻松的事情。整体方案，我们已经整合成了数据库全文搜索引擎DBTSearch。
具体的技术方案可以参见本人的论坛帖子http://www.lingjoin.com:8080/bbs/forumdisplay.php?fid=6

[xuexinghen]

MARK一下，学习了~

[xfpx]

mark

[yongfa365]

推荐使用SQL Server CLR，扫描表一次，其它全是逻辑运算，速度会快很多

SQL Server CLR 极速入门，启用、设计、部署、运行

http://www.yongfa365.com/Item/SQL-Server-CLR.html

[haa17]

学习中

[lautju]

进来学习一下