這是一個很久以前的例子，現在在整理資料時無意發現，就拿出來再改寫分享。
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;gocreate table TBAddress( ID int , Parent int not null , LevelNo smallint not null , Name nvarchar(50) not null , constraint PK_TBAddress primary key ( ID ));gocreate 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);gocreate table
2.2 插入數據
use testgo/*insert data*/set nocount onBegin 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 TranEnd TryBegin Catch throw 50001,N'插入數據過程中發生錯誤.' ,1Rollback TranEnd Catchgo
附件： 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 testGoif object_ID('[up_SearchAddressByNameV0]') is not null Drop Procedure [up_SearchAddressByNameV0]Gocreate proc up_SearchAddressByNameV0 ( @Name nvarchar(200))Asset nocount ondeclare @sql nvarchar(max) declare @tmp Table (Name nvarchar(50)) set @Name=@Name+' ' while patindex('% %',@Name)>0begin 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 #tmpif 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 Goprocedure:up_SearchAddressByNameV0
3.3 執行查詢：
exec up_SearchAddressByNameV0 '廣 大'
共返回195行記錄。
3.4 客戶端統計信息：
平均的執行耗時: 244毫秒
4.測試，方法2
方法2是參照方法1，并借助全文索引來優化方法1中的步驟1。也就是在name列上建立全文索引，在步驟1中，通過全文索引搜索出包字段Name中含有“廣”、“大”的所有地址記錄存入臨時表#tmp，其他步驟保持不變。
4.1 創建全文索引
use testgo/*create fulltext index*/if not exists(select 1 from sys.fulltext_catalogs a where a.name='ftCatalog')begincreate fulltext catalog ftCatalog As default;endgo--select * From sys.fulltext_languages create fulltext index on TBAddress(Name language 2052 ) key index PK_TBAddressgo alter fulltext index on dbo.TBAddress add(Fullpath language 2052)go
Note: 在Name列上創建全文索引使用的語言是簡體中文（Simplified Chinese）
4.2 存儲過程代碼：
Use testGoif object_ID('[up_SearchAddressByNameV1]') is not null Drop Procedure [up_SearchAddressByNameV1]Gocreate proc up_SearchAddressByNameV1 ( @Name nvarchar(200))Asset nocount ondeclare @sql nvarchar(max),@contains nvarchar(500) declare @tmp Table (Name nvarchar(50)) while patindex('% %',@Name)>0begin 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 Goprocedure: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;gocreate 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;go5.2 在列FullPath添加全文索引：
alter fulltext index on dbo.TBAddress add(Fullpath language 2052)
5.3 存儲過程代碼：
Use testGoif object_ID('[up_SearchAddressByNameV2]') is not null Drop Procedure [up_SearchAddressByNameV2]Gocreate proc up_SearchAddressByNameV2( @name nvarchar(200))Asdeclare @contains nvarchar(500)set nocount onset @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 AddressPathGoprocedure:up_SearchAddressByNameV2
5.4 測試存儲過程：
exec up_SearchAddressByNameV2 '廣 大'
共返回195行記錄。
5.5 客戶端統計信息：
平均的執行耗時: 20.4毫秒
6.測試，方法4
直接使用Like對列FullPath進行查詢。
6.1存儲過程代碼：
Use testGoif object_ID('[up_SearchAddressByNameV3]') is not null Drop Procedure [up_SearchAddressByNameV3]Gocreate proc up_SearchAddressByNameV3( @name nvarchar(200))Asset nocount ondeclare @sql nvarchar(max) declare @tmp Table (Name nvarchar(50)) set @Name=rtrim(rtrim(@Name)) while patindex('% %',@Name)>0begin 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) Goprocedure:up_SearchAddressByNameV3
6.2 測試存儲過程：
exec up_SearchAddressByNameV3 '廣 大'
共返回195行記錄。
6.3 客戶端統計信息
平均的執行耗時: 34毫秒
7.小結
這里通過一個簡單的表格，對方法1至方法4作比較。
從平均耗時方面分析，一眼就知道方法3比較符合開始的需求（耗時要控制在幾十毫秒內）。
當然還有其他的方法，如通過程序實現，把數據一次性加載至內存中，再通過程序寫的算法進行搜索，或通過其他工具如Lucene來實現。不管哪一種方法，我們都是選擇最優的方法。實際的工作經驗告訴我們，在實際應用中，多選擇和測試不同的方法來，選擇其中一個滿足我們環境的，而且是最優的方法。
新聞熱點
疑難解答
