数据库性能优化--地址搜索

于 2012-12-11 23:21:02 发布
阅读量678 收藏
点赞数
分类专栏： MSSQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/DVD_01/article/details/8284756
版权

问题：

有一张表TBAddress：

字段名

数据类型

说明

ID

int

主键

Parent

int

父ID

LevelNo

smallint

地址所在的层（范围：0 至 5）

Name

nvarchar(50)

地址名称

 

 

 

 

 

 

 

 





表数据如下：

问题：给出一个字符串如“广 大”，找出地址全路径中包含有“广” 和“大”的所有数据，結果如：

ID

AddressPath

26996

山西省/大同市/广灵县/梁庄乡

26998

山西省/大同市/广灵县/蕉山乡

44033

广东省/广州市/黄埔区/大沙街道

13623

广东省/广州市/番禺区/大石街道

13625

广东省/广州市/番禺区/大龙街道

… …

… …

 

 

 

 

 

 

 

 

 

 

 



要求查询耗时控制在秒级范围。

下面提供测试用的表和數據，创建表和插入数据脚本：

View Code 
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

 Insert Data

 

附：【地址全路径】：

“新疆维吾尔族自治区巴音郭楞蒙古自治州若羌县依吞布拉克镇”

“江西省吉安市吉水县阜田镇”

 

方法1：

 

  1. 先搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp。
  2. 再找出#tmp中各个地址到Level 1的全路径。
  3. 根据步骤2所得的结果，筛选出包含有“广”和“大”的地址路径。
  4. .根据步骤3筛选的结果，查询所有到Level n（n为没有子地址的层编号）的地址全路径。

方法1脚本(up_SearchAddressByNameV0)：

View Code 
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）：

View Code 
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）：

View Code 
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）：

View Code 
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来实现。不管哪一种方法，我们都是选择最优的方法。实际的工作经验告诉我们，在实际应用中，多选择和测试不同的方法来，选择其中一个满足我们环境的，而且是最优的方法。

 

 

 

 

Andy-W
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值