SQLServer地址搜索性能优化

这是一个很久以前的例子，现在在整理资料时无意发现，就拿出来再改写分享。

1.需求

 1.1 基本需求： 根据输入的地址关键字，搜索出完整的地址路径，耗时要控制在几十毫秒内。

 1.2 数据库地址表结构和数据：

 表TBAddress

 表数据

 1.3 例子：

 e.g. 给出一个字符串如“广 大”，找出地址全路径中包含有“广” 和“大”的所有地址，結果如下：

下面将通过4个方法来实现，再分析其中的性能优劣，然后选择一个比较优的方法。

 2.创建表和插入数据

 2.1 创建数据表TBAddress

use test;
go
/* create table */
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

create table

2.2 插入数据

use test
go
/*insert data*/
set nocount on
Begin Try
  Begin Tran
  Insert Into TBAddress ([ID],[Parent],[LevelNo],[Name])
    Select 1,0,0,N'中国' Union All 
    Select 2,1,1,N'直辖市' Union All 
    Select 3,1,1,N'辽宁省' Union All 
    Select 4,1,1,N'广东省' Union All 
    ... ...
    Select 44740,930,4,N'奥依塔克镇' Union All 
    Select 44741,932,4,N'巴音库鲁提乡' Union All 
    Select 44742,932,4,N'吉根乡' Union All 
    Select 44743,932,4,N'托云乡'
  Commit Tran
End Try
Begin Catch
  throw 50001,N'插入數據過程中發生錯誤.' ,1
Rollback Tran
End Catch
go

附件： insert Data

 Note: 数据有44700条，insert代码比较长，所以采用附件形式。

3.测试，方法1

3.1 分析：

a. 先搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp。

  b. 再找出#tmp中各个地址到Level 1的全路径。

    c. 根据步骤2所得的结果，筛选出包含有“广”和“大”的地址路径。

      d. 根据步骤3筛选的结果，查询所有到Level n（n为没有子地址的层编号）的地址全路径。

3.2 存储过程代码：

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

procedure:up_SearchAddressByNameV0

 3.3 执行查询：

exec up_SearchAddressByNameV0 '广 大'

共返回195行记录。

3.4 客户端统计信息：

平均的执行耗时:  244毫秒

4.测试，方法2

 方法2是参照方法1，并借助全文索引来优化方法1中的步骤1。也就是在name列上建立全文索引，在步骤1中，通过全文索引搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp，其他步骤保持不变。

 4.1 创建全文索引

use test
go
/*create fulltext index*/
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   
alter fulltext index on dbo.TBAddress add(Fullpath language 2052)
go

Note:  在Name列上创建全文索引使用的语言是简体中文（Simplified Chinese）

4.2 存储过程代码：

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

procedure:up_SearchAddressByNameV1

4.3测试存储过程：

exec up_SearchAddressByNameV1 '广 大'

共返回195行记录。

4.4 客户端统计信息：

平均的执行耗时:  166毫秒

5.测试，方法3

在方法2中，我们在Name列上创建全文索引提高了查询性能，但我们不仅仅局限于一两个方法，下面我们介绍第3个方法。

第3个方法，通过修改表的结构和创建全文索引。在表TBAddress增加多一个字段FullPath存储各个地址到Level 1的全路径，再在FullPath列上创建全文索引，然后直接通过全文索引来搜索FullPath列中包含“广”和“大”的记录。

5.1 新增加字段FullPath，并更新列FullPath数据：

use test;
go
/*alter table */
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
/*update TBAddress */
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

5.2 在列FullPath添加全文索引：

alter fulltext index on dbo.TBAddress add(Fullpath language 2052)

5.3 存储过程代码：

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

procedure:up_SearchAddressByNameV2

5.4 测试存储过程：

exec up_SearchAddressByNameV2 '广 大'

共返回195行记录。

5.5 客户端统计信息：

平均的执行耗时:  20.4毫秒

6.测试，方法4

 直接使用Like对列FullPath进行查询。

 6.1存储过程代码：

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

procedure:up_SearchAddressByNameV3

6.2 测试存储过程：

exec up_SearchAddressByNameV3 '广 大'

 共返回195行记录。

6.3 客户端统计信息

平均的执行耗时:  34毫秒

7.小结

这里通过一个简单的表格，对方法1至方法4作比较。

从平均耗时方面分析，一眼就知道方法3比较符合开始的需求（耗时要控制在几十毫秒内）。

当然还有其他的方法，如通过程序实现，把数据一次性加载至内存中，再通过程序写的算法进行搜索，或通过其他工具如Lucene来实现。不管哪一种方法，我们都是选择最优的方法。实际的工作经验告诉我们，在实际应用中，多选择和测试不同的方法来，选择其中一个满足我们环境的，而且是最优的方法。

# sql  # server  # 性能优化  # sqlserver性能优化  # sqlserver  # 在SQL Server 2005所有表中搜索某个指定列的方法  # 在SQL Server中实现最短路径搜索的解决方法  # sqlserver中在指定数据库的所有表的所有列中搜索给定的值  # SQL Server 全文搜索功能介绍  # 存储过程  # 统计信息  # 客户端  # 时要  # 来实现  # 最优  # 都是  # 辽宁省  # 是在  # 简体中文  # 也就  # 其他的  # 中含有  # 这是一个  # 广东省  # 工作经验  # 告诉我们  # 种方法  # 其中一个  # 方法来 

相关文章： 香港服务器租用费用高吗？如何避免常见误区？  如何在局域网内绑定自建网站域名？  ,制作一个手机app网站要多少钱？  网页制作模板网站推荐,网页设计海报之类的素材哪里好？  网站app免费制作软件,能免费看各大网站视频的手机app？  手机网站制作平台,手机靓号代理商怎么制作属于自己的手机靓号网站？  广州网站制作的公司,现在专门做网站的公司有没有哪几家是比较好的，性价比高，模板也多的？  建站之星在线客服如何快速接入解答？  如何获取免费开源的自助建站系统源码？  建站上市公司网站建设方案与SEO优化服务定制指南  香港网站服务器数量如何影响SEO优化效果？  广平建站公司哪家专业可靠？如何选择？  沈阳制作网站公司排名,沈阳装饰协会官方网站？  如何做静态网页,sublimetext3.0制作静态网页？  建站之星后台密码遗忘如何找回？  制作ppt免费网站有哪些,有哪些比较好的ppt模板下载网站？  建站之星代理商如何保障技术支持与售后服务？  如何在阿里云服务器自主搭建网站？  ,如何利用word制作宣传手册？  如何制作网站标识牌,动态网站如何制作（教程）？  实惠建站价格推荐：2025年高性价比自助建站套餐解析  建站主机核心功能解析：服务器选择与网站搭建流程指南  沈阳个人网站制作公司,哪个网站能考到沈阳事业编招聘的信息？  韩国网站服务器搭建指南：VPS选购、域名解析与DNS配置推荐  股票网站制作软件,网上股票怎么开户？  子杰智能建站系统｜零代码开发与AI生成SEO优化指南  建站之星logo尺寸如何设置最合适？  攀枝花网站建设,攀枝花营业执照网上怎么年审？  如何快速搭建高效香港服务器网站？  无锡营销型网站制作公司,无锡网选车牌流程？  品牌网站制作公司有哪些,买正品品牌一般去哪个网站买？  如何配置WinSCP新建站点的密钥验证步骤？  香港服务器网站测试全流程：性能评估、SEO加载与移动适配优化  番禺网站制作公司哪家值得合作,番禺图书馆新馆开放了吗？  如何在建站之星绑定自定义域名？  兔展官网 在线制作,怎样制作微信请帖？  北京网站制作的公司有哪些,北京白云观官方网站？  网站建设制作需要多少钱费用,自己做一个网站要多少钱，模板一般多少钱？  如何快速搭建安全的FTP站点？  威客平台建站流程解析：高效搭建教程与设计优化方案  如何配置支付宝与微信支付功能？  购物网站制作费用多少,开办网上购物网站，需要办理哪些手续？  建站之星如何一键生成手机站？  表情包在线制作网站免费,表情包怎么弄？  C#怎么使用委托和事件 C# delegate与event编程方法  专业型网站制作公司有哪些,我设计专业的，谁给推荐几个设计师兼职类的网站？  单页制作网站有哪些,朋友给我发了一个单页网站，我应该怎么修改才能把他变成自己的呢，请求高手指点迷津？  建站之星上传入口如何快速找到？  可靠的网站设计制作软件,做网站设计需要什么样的电脑配置？  完全自定义免费建站平台：主题模板在线生成一站式服务