问题:
有一张表TBAddress:
字段名 | 数据类型 | 说明 |
ID | int | 主键 |
Parent | int | 父ID |
LevelNo | smallint | 地址所在的层(范围:0 至 5) |
Name | nvarchar(50) | 地址名称 |
表数据如下:
问题:给出一个字符串如“广 大”,找出地址全路径中包含有“广” 和“大”的所有数据,結果如:
ID | AddressPath |
26996 | 山西省/大同市/广灵县/梁庄乡 |
26998 | 山西省/大同市/广灵县/蕉山乡 |
44033 | 广东省/广州市/黄埔区/大沙街道 |
13623 | 广东省/广州市/番禺区/大石街道 |
13625 | 广东省/广州市/番禺区/大龙街道 |
… … | … … |
要求查询耗时控制在秒级范围。
下面提供测试用的表和數據,创建表和插入数据脚本:
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
use test go if object_id('TBAddress') Is not null Drop Table TBAddress go create table TBAddress ( ID int, Parent int not null, LevelNo smallint not null, Name nvarchar(50) not null, constraint PK_TBAddress primary key(ID) ) go create nonclustered index ix_TBAddress_Parent on TBAddress(Parent,LevelNo) include(Name) with(fillfactor=80,pad_index=on) create nonclustered index ix_TBAddress_Name on TBAddress(Name)include(LevelNo)with(fillfactor=80,pad_index=on) go
附:【地址全路径】:
“新疆维吾尔族自治区巴音郭楞蒙古自治州若羌县依吞布拉克镇”
“江西省吉安市吉水县阜田镇”
方法1:
- 先搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp。
- 再找出#tmp中各个地址到Level 1的全路径。
- 根据步骤2所得的结果,筛选出包含有“广”和“大”的地址路径。
- .根据步骤3筛选的结果,查询所有到Level n(n为没有子地址的层编号)的地址全路径。
方法1脚本(up_SearchAddressByNameV0):
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
Use test Go if object_ID('[up_SearchAddressByNameV0]') is not null Drop Procedure [up_SearchAddressByNameV0] Go create proc up_SearchAddressByNameV0 ( @Name nvarchar(200) ) As set nocount on declare @sql nvarchar(max) declare @tmp Table (Name nvarchar(50)) set @Name=@Name+' ' while patindex('% %',@Name)>0 begin set @Name=replace(@Name,' ',' ') end set @sql ='select ''' +replace(@Name,' ',''' union all select ''')+'''' insert into @tmp(Name) exec(@sql) if object_id('tempdb..#tmp') is not null drop table #tmp if object_id('tempdb..#') is not null drop table # create table #tmp(ID int ) while @Name>'' begin insert into #tmp(ID) select a.ID from TBAddress a where a.Name like '%'+substring(@Name,1,patindex('% %',@Name)-1)+'%' set @Name=Stuff(@Name,1,patindex('% %',@Name),'') end ;with cte_SearchParent as ( select a.ID,a.Parent,a.LevelNo,convert(nvarchar(500),a.Name) as AddressPath from TBAddress a where exists(select 1 from #tmp x where a.ID=x.ID) union all select a.ID,b.Parent,b.LevelNo,convert(nvarchar(500),b.Name+'/'+a.AddressPath) as AddressPath from cte_SearchParent a inner join TBAddress b on b.ID=a.Parent --and b.LevelNo=a.LevelNo -1 and b.LevelNo>=1 ) select a.ID,a.AddressPath into # from cte_SearchParent a where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%'+x.Name+'%' having count(1)=(select count(1) from @tmp)) ;with cte_result as ( select a.ID,a.LevelNo,b.AddressPath from TBAddress a inner join # b on b.ID=a.ID union all select b.ID,b.LevelNo,convert(nvarchar(500),a.AddressPath+'/'+b.Name) As AddressPath from cte_result a inner join TBAddress b on b.Parent=a.ID --and b.LevelNo=a.LevelNo+1 ) select distinct a.ID,a.AddressPath from cte_result a where not exists(select 1 from TBAddress x where x.Parent=a.ID) order by a.AddressPath Go
執行up_SearchAddressByNameV0,返回记录:
共195条记录。
客户端统计结果:
平均的执行耗时为294.6毫秒
方法2
方法2是参照方法1,并借助全文索引来优化方法1中的步骤1。也就是在name列上建立全文索引,在步骤1中,通过全文索引搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp,其他步骤保持不变。
建立全文索引:
use test go if not exists(select 1 from sys.fulltext_catalogs a where a.name='ftCatalog') begin create fulltext catalog ftCatalog As default; end go --select * From sys.fulltext_languages create fulltext index on TBAddress(Name language 2052 ) key index PK_TBAddress go
【注】:在Name列上创建全文索引使用的语言是简体中文(Simplified Chinese)。
方法2脚本(up_SearchAddressByNameV1):
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
Use test Go if object_ID('[up_SearchAddressByNameV1]') is not null Drop Procedure [up_SearchAddressByNameV1] Go create proc up_SearchAddressByNameV1 ( @Name nvarchar(200) ) As set nocount on declare @sql nvarchar(max),@contains nvarchar(500) declare @tmp Table (Name nvarchar(50)) while patindex('% %',@Name)>0 begin set @Name=replace(@Name,' ',' ') end set @sql ='select ''' +replace(@Name,' ',''' union all select ''')+'''' set @contains='"'+replace(@Name,' ','*" Or "')+'*"' insert into @tmp(Name) exec(@sql) if object_id('tempdb..#') is not null drop table # ;with cte_SearchParent as ( select a.ID,a.Parent,a.LevelNo,convert(nvarchar(2000),a.Name) as AddressPath from TBAddress a where exists(select 1 from TBAddress x where contains(x.Name,@contains) And x.ID=a.ID) union all select a.ID,b.Parent,b.LevelNo,convert(nvarchar(2000),b.Name+'/'+a.AddressPath) as AddressPath from cte_SearchParent a inner join TBAddress b on b.ID=a.Parent --and b.LevelNo=a.LevelNo -1 and b.LevelNo>=1 ) select a.ID,a.AddressPath into # from cte_SearchParent a where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%'+x.Name+'%' having count(1)=(select count(1) from @tmp)) ;with cte_result as ( select a.ID,a.LevelNo,b.AddressPath from TBAddress a inner join # b on b.ID=a.ID union all select b.ID,b.LevelNo,convert(nvarchar(2000),a.AddressPath+'/'+b.Name) As AddressPath from cte_result a inner join TBAddress b on b.Parent=a.ID --and b.LevelNo=a.LevelNo+1 ) select distinct a.ID,a.AddressPath from cte_result a where not exists(select 1 from TBAddress x where x.Parent=a.ID) order by a.AddressPath Go
执行存储过程up_SearchAddressByNameV1,返回结果:
与方法1一样返回195条记录。
客户端统计结果:
平均的执行耗时为180.6毫秒。
针对方法1,方法2的优化缩短了查询时间,提高了查询性能。
方法3:
在方法2中,我们在Name列上创建全文索引提高了查询性能,但我们不仅仅局限于一两个方法,下面我们介绍第3个方法。
第3个方法,通过修改表的结构和创建全文索引。在表TBAddress增加多一个字段FullPath存储各个地址到Level 1的全路径,再在FullPath列上创建全文索引,然后直接通过全文索引来搜索FullPath列中包含“广”和“大”的记录。
新增加字段FullPath,并更新列FullPath数据:
use Test go if not exists(select 1 from sys.columns a where a.object_id=object_id('TBAddress') and a.name='Fullpath') begin alter table TBAddress add Fullpath nvarchar(200); end go create nonclustered index IX_TBAddress_FullPath on dbo.TBAddress(Fullpath) with(fillfactor=80,pad_index=on) go ;With cte_fullPath As ( Select Id,Parent,LevelNo,Convert(nvarchar(500),Isnull(Name,'')) As FPath,Fullpath From dbo.TBAddress Where LevelNo=1 Union All Select A.Id,A.Parent,A.LevelNo,Convert(nvarchar(500),B.FPath+'/'+Isnull(A.Name,'')) As FPath,a.Fullpath From TBAddress As A Inner Join cte_fullPath As B On A.Parent=B.id ) update a set a.Fullpath=isnull(b.FPath,a.Name) from dbo.TBAddress a left join cte_fullPath b on b.id=a.id go
在列FullPath添加全文索引:
alter fulltext index on dbo.TBAddress add(Fullpath language 2052) go
方法3脚本(up_SearchAddressByNameV2):
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
Use test Go if object_ID('[up_SearchAddressByNameV2]') is not null Drop Procedure [up_SearchAddressByNameV2] Go create proc up_SearchAddressByNameV2 ( @name nvarchar(200) ) As declare @contains nvarchar(500) set nocount on set @contains='"'+replace(@Name,' ','*" And "')+'*"' select id,FullPath As AddressPath from TBAddress a where contains(a.FullPath,@contains) and not exists(select 1 from TBAddress x where x.Parent=a.ID) order by AddressPath Go
执行存储过程up_SearchAddressByNameV2,结果为:
与方法1、方法2一样返回195条记录。
客户端统计结果:
平均的执行耗时为41.5毫秒。
这里我们明显可以看出,方法3查询性能比方法1、方法2都高。也就是我们想要的方法。
当然,我们下面还列一个方法4,应用到方法3中的新增字段FullPath,直接使用Like来查询。
方法4
直接使用Like对列FullPath进行查询。
方法4脚本(up_SearchAddressByNameV3):
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
Use test Go if object_ID('[up_SearchAddressByNameV3]') is not null Drop Procedure [up_SearchAddressByNameV3] Go create proc up_SearchAddressByNameV3 ( @name nvarchar(200) ) As set nocount on declare @sql nvarchar(max) declare @tmp Table (Name nvarchar(50)) set @Name=rtrim(rtrim(@Name)) while patindex('% %',@Name)>0 begin set @Name=replace(@Name,' ',' ') end set @sql='select id,FullPath As AddressPath from TBAddress a where not exists(select 1 from TBAddress x where x.Parent=a.ID) ' set @sql +='And a.FullPath like ''%' +replace(@Name,' ','%'' And a.FullPath Like ''%')+'%''' exec (@sql) Go
执行存储过程up_SearchAddressByNameV3,结果为:
返回195条记录。
客户端统计结果:
平均的执行耗时为76.2毫秒。
虽然方法4的平均耗时比方法1、方法2都小,但从最优的角度,我们优先选择方法3.
小结:
在这篇我只列出在数据库中实现的4个方法,当然还有其他的方法,如通过程序实现,把数据一次性加载至内存中,再通过程序写的算法进行搜索,或通过其他工具如Lucene来实现。不管哪一种方法,我们都是选择最优的方法。实际的工作经验告诉我们,在实际应用中,多选择和测试不同的方法来,选择其中一个满足我们环境的,而且是最优的方法。