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

问题：

有一张表TBAddress：

字段名	数据类型	说明
ID	int	主键
Parent	int	父ID
LevelNo	smallint	地址所在的层（范围：0 至 5）
Name	nvarchar(50)	地址名称

 

 

 

 

 

 

 

 

表数据如下：

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

ID	AddressPath
26996	山西省/大同市/广灵县/梁庄乡
26998	山西省/大同市/广灵县/蕉山乡
44033	广东省/广州市/黄埔区/大沙街道
13623	广东省/广州市/番禺区/大石街道
13625	广东省/广州市/番禺区/大龙街道
… …	… …

 

 

 

 

 

 

 

 

 

 

 

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

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

 Insert Data

 

附：【地址全路径】：

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

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

 

方法1：

---

 

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

方法1脚本(up_SearchAddressByNameV0)：

執行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）：

执行存储过程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）：

 

执行存储过程up_SearchAddressByNameV2，结果为：

与方法1、方法2一样返回195条记录。

客户端统计结果：

平均的执行耗时为41.5毫秒。

这里我们明显可以看出，方法3查询性能比方法1、方法2都高。也就是我们想要的方法。

 

当然，我们下面还列一个方法4，应用到方法3中的新增字段FullPath，直接使用Like来查询。

 

方法4

---

 

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

方法4脚本（up_SearchAddressByNameV3）：

执行存储过程up_SearchAddressByNameV3，结果为：

返回195条记录。

客户端统计结果：

平均的执行耗时为76.2毫秒。

虽然方法4的平均耗时比方法1、方法2都小，但从最优的角度，我们优先选择方法3.

 

小结：

---

 

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