SQLServer地址搜索性能優(yōu)化

這是一個(gè)很久以前的例子，現(xiàn)在在整理資料時(shí)無意發(fā)現(xiàn)，就拿出來再改寫分享。

1.需求

 1.1 基本需求： 根據(jù)輸入的地址關(guān)鍵字，搜索出完整的地址路徑，耗時(shí)要控制在幾十毫秒內(nèi)。

 1.2 數(shù)據(jù)庫地址表結(jié)構(gòu)和數(shù)據(jù)：

 表TBAddress

 表數(shù)據(jù)

 1.3 例子：

 e.g. 給出一個(gè)字符串如“廣 大”，找出地址全路徑中包含有“廣” 和“大”的所有地址，結(jié)果如下：

下面將通過4個(gè)方法來實(shí)現(xiàn)，再分析其中的性能優(yōu)劣，然后選擇一個(gè)比較優(yōu)的方法。

 2.創(chuàng)建表和插入數(shù)據(jù)

 2.1 創(chuàng)建數(shù)據(jù)表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 插入數(shù)據(jù)

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'奧依塔克鎮(zhèn)' Union All 
    Select 44741,932,4,N'巴音庫魯提鄉(xiāng)' Union All 
    Select 44742,932,4,N'吉根鄉(xiāng)' Union All 
    Select 44743,932,4,N'托云鄉(xiāng)'
  Commit Tran
End Try
Begin Catch
  throw 50001,N'插入數(shù)據(jù)過程中發(fā)生錯(cuò)誤.' ,1
Rollback Tran
End Catch
go

附件： insert Data

 Note: 數(shù)據(jù)有44700條，insert代碼比較長，所以采用附件形式。

3.測(cè)試，方法1

3.1 分析：

a. 先搜索出包字段Name中含有“廣”、“大”的所有地址記錄存入臨時(shí)表#tmp。

  b. 再找出#tmp中各個(gè)地址到Level 1的全路徑。

    c. 根據(jù)步驟2所得的結(jié)果，篩選出包含有“廣”和“大”的地址路徑。

      d. 根據(jù)步驟3篩選的結(jié)果，查詢所有到Level n（n為沒有子地址的層編號(hào)）的地址全路徑。

3.2 存儲(chǔ)過程代碼：

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 執(zhí)行查詢：

exec up_SearchAddressByNameV0 '廣 大'

共返回195行記錄。

3.4 客戶端統(tǒng)計(jì)信息：

平均的執(zhí)行耗時(shí):  244毫秒

4.測(cè)試，方法2

 方法2是參照方法1，并借助全文索引來優(yōu)化方法1中的步驟1。也就是在name列上建立全文索引，在步驟1中，通過全文索引搜索出包字段Name中含有“廣”、“大”的所有地址記錄存入臨時(shí)表#tmp，其他步驟保持不變。

 4.1 創(chuàng)建全文索引

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列上創(chuàng)建全文索引使用的語言是簡體中文（Simplified Chinese）

4.2 存儲(chǔ)過程代碼：

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測(cè)試存儲(chǔ)過程：

exec up_SearchAddressByNameV1 '廣 大'

共返回195行記錄。

4.4 客戶端統(tǒng)計(jì)信息：

平均的執(zhí)行耗時(shí):  166毫秒

5.測(cè)試，方法3

在方法2中，我們?cè)贜ame列上創(chuàng)建全文索引提高了查詢性能，但我們不僅僅局限于一兩個(gè)方法，下面我們介紹第3個(gè)方法。

第3個(gè)方法，通過修改表的結(jié)構(gòu)和創(chuàng)建全文索引。在表TBAddress增加多一個(gè)字段FullPath存儲(chǔ)各個(gè)地址到Level 1的全路徑，再在FullPath列上創(chuàng)建全文索引，然后直接通過全文索引來搜索FullPath列中包含“廣”和“大”的記錄。

5.1 新增加字段FullPath，并更新列FullPath數(shù)據(jù)：

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 存儲(chǔ)過程代碼：

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 測(cè)試存儲(chǔ)過程：

exec up_SearchAddressByNameV2 '廣 大'

共返回195行記錄。

5.5 客戶端統(tǒng)計(jì)信息：

平均的執(zhí)行耗時(shí):  20.4毫秒

6.測(cè)試，方法4

 直接使用Like對(duì)列FullPath進(jìn)行查詢。

 6.1存儲(chǔ)過程代碼：

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 測(cè)試存儲(chǔ)過程：

exec up_SearchAddressByNameV3 '廣 大'

 共返回195行記錄。

6.3 客戶端統(tǒng)計(jì)信息

平均的執(zhí)行耗時(shí):  34毫秒

7.小結(jié)

這里通過一個(gè)簡單的表格，對(duì)方法1至方法4作比較。

從平均耗時(shí)方面分析，一眼就知道方法3比較符合開始的需求（耗時(shí)要控制在幾十毫秒內(nèi)）。

當(dāng)然還有其他的方法，如通過程序?qū)崿F(xiàn)，把數(shù)據(jù)一次性加載至內(nèi)存中，再通過程序?qū)懙乃惴ㄟM(jìn)行搜索，或通過其他工具如Lucene來實(shí)現(xiàn)。不管哪一種方法，我們都是選擇最優(yōu)的方法。實(shí)際的工作經(jīng)驗(yàn)告訴我們，在實(shí)際應(yīng)用中，多選擇和測(cè)試不同的方法來，選擇其中一個(gè)滿足我們環(huán)境的，而且是最優(yōu)的方法。