有个棘手的MSSQL2005表结构及海量数据的查询性能问题

时间：2021-06-27 10:39:46
有个棘手的MSSQL2005表结构及海量数据的查询性能问题，一个月了，一直找不到解决方案，好急死人哦，请问高手：

UserInfo表结构： 


CREATE TABLE dbo.UserInfo(

	PkId int NOT NULL,

	ClassTypes nvarchar(50) NULL,  -- 分类编号(储存格式：20,21,32,34,56,…)

	AreaSysNos nvarchar(50) NULL  -- 地区编号(储存格式：440000,440001,440002,440003,…)

)

SQL查询语句：
当我在页面中，多选分类/地区后，如ClassTypes条件，我多选了(20,32,56)，找不到什么方法拼SQL，因为它是多对多的查询；

调整方案：
之后，我调整了表结构，并在页面中做了限制，ClassTypes/AreaSysNos只能多选5个： 


CREATE TABLE dbo.UserInfo(

	PkId int NOT NULL,

	ClassTypes_1 int NULL,  -- 分类编号

	ClassTypes_2 int NULL,  -- 分类编号

	ClassTypes_3 int NULL,  -- 分类编号

	ClassTypes_4 int NULL,  -- 分类编号

	ClassTypes_5 int NULL,  -- 分类编号

	AreaSysNos_1 int NULL,  -- 地区编号

	AreaSysNos_2 int NULL,  -- 地区编号

	AreaSysNos_3 int NULL,  -- 地区编号

	AreaSysNos_4 int NULL,  -- 地区编号

	AreaSysNos_5 int NULL,  -- 地区编号

)

SQL查询语句： 


Select Top 1000 * From UserInfo

Where 1=1

    And (ClassTypes_1 In (20,32,56) Or ClassTypes_2 In (20,32,56) Or ClassTypes_3 In (20,32,56) Or ClassTypes_4 In (20,32,56) Or ClassTypes_5 In (20,32,56))

    And (AreaSysNos_1 In (440001,440003) Or AreaSysNos_2 In (440001,440003) Or AreaSysNos_3 In (440001,440003) Or AreaSysNos_4 In (440001,440003) Or AreaSysNos_5 In (440001,440003))


现在这个表有500W数据，分别在ClassTypes_1到ClassTypes_5、AreaSysNos_1到AreaSysNos_5上建立索引，查询往往超时，因为使用In+Or，要全表扫描；

调整表结构/建立索引，我都想遍了，实在没招。请问各位大大，这种问题怎么办？就只有这么多分了，希望各位谅解。

6 个解决方案

#1

CREATE TABLE dbo.UserInfo(

    PkId int NOT NULL,

    ClassTypes int NULL,  -- 分类编号

    AreaSysNos int NULL  -- 地区编号)


拆分开不就好弄了!

#2

怎么拆分开?分别拆分成两个子表吗？

#3

楼主，经过你的调整，性能反而降低了

另外，我不明白，为什么这样写不行：
Select Top 1000 * From UserInfo
Where 1=1 And ClassTypes In (20,32,56) And AreaSysNos In (440001,440003)

#4

引用 3 楼 coleling 的回复:
楼主，经过你的调整，性能反而降低了

另外，我不明白，为什么这样写不行：
Select Top 1000 * From UserInfo
Where 1=1 And ClassTypes In (20,32,56) And AreaSysNos In (440001,440003)


英文ClassTypes和AreaSysNos字段,存储的格式是:20,30,40,50,60这样子的.

#5

结构设计不规划。。可能是八十年代的思路吧，有偿支持实现

#6

引用 4 楼 shuihong007 的回复:
英文ClassTypes和AreaSysNos字段,存储的格式是:20,30,40,50,60这样子的.


建议你将ClassTypes和AreaSysNos字段分别存放到一个新表中，
以ClassTypes为例，如pkid=1,ClassTypes=(20,30,40,50,60),则拆分成五条记录，放在新表UserInfo_ClassTypes(PKID,ClassTypes)中，即(1,20),(1,30),(1,40),(1,50),(1,60);

AreaSysNos处理方法同上。

查询时，就可以这样写：
Select Top 1000 * From UserInfo_ClassTypes a,UserInfo_AreaSysNos b
Where 1=1 And a.pkid = b.pkid And a.ClassTypes In (20,32,56) And b.AreaSysNos In (440001,440003)

#1

CREATE TABLE dbo.UserInfo(

    PkId int NOT NULL,

    ClassTypes int NULL,  -- 分类编号

    AreaSysNos int NULL  -- 地区编号)


拆分开不就好弄了!

#2

怎么拆分开?分别拆分成两个子表吗？

#3

楼主，经过你的调整，性能反而降低了

另外，我不明白，为什么这样写不行：
Select Top 1000 * From UserInfo
Where 1=1 And ClassTypes In (20,32,56) And AreaSysNos In (440001,440003)

#4

引用 3 楼 coleling 的回复:
楼主，经过你的调整，性能反而降低了

另外，我不明白，为什么这样写不行：
Select Top 1000 * From UserInfo
Where 1=1 And ClassTypes In (20,32,56) And AreaSysNos In (440001,440003)


英文ClassTypes和AreaSysNos字段,存储的格式是:20,30,40,50,60这样子的.

#5

结构设计不规划。。可能是八十年代的思路吧，有偿支持实现

#6

引用 4 楼 shuihong007 的回复:
英文ClassTypes和AreaSysNos字段,存储的格式是:20,30,40,50,60这样子的.


建议你将ClassTypes和AreaSysNos字段分别存放到一个新表中，
以ClassTypes为例，如pkid=1,ClassTypes=(20,30,40,50,60),则拆分成五条记录，放在新表UserInfo_ClassTypes(PKID,ClassTypes)中，即(1,20),(1,30),(1,40),(1,50),(1,60);

AreaSysNos处理方法同上。

查询时，就可以这样写：
Select Top 1000 * From UserInfo_ClassTypes a,UserInfo_AreaSysNos b
Where 1=1 And a.pkid = b.pkid And a.ClassTypes In (20,32,56) And b.AreaSysNos In (440001,440003)
标签：

相关文章