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

问题：

有一张表TBAddress：

字段名

数据类型

说明

ID

int

主键

Parent

int

父ID

LevelNo

smallint

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

Name

nvarchar(50)

地址名称

 

 

 

 

 

 

 

 

表数据如下：

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

ID

AddressPath

26996

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

26998

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

44033

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

13623

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

13625

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

… …

… …

 

 

 

 

 

 

 

 

 

 

 

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

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

View Code

 Insert Data

 

附：【地址全路径】：

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

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

 

方法1：

---

 

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

方法1脚本(up_SearchAddressByNameV0)：

View Code

執行up_SearchAddressByNameV0，返回记录：

共195条记录。

客户端统计结果：

平均的执行耗时为294.6毫秒

 

方法2

---

 

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

建立全文索引：

use testgoif 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

【注】：在Name列上创建全文索引使用的语言是简体中文（Simplified Chinese）。

方法2脚本（up_SearchAddressByNameV1）：

View Code

执行存储过程up_SearchAddressByNameV1，返回结果：

 

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

客户端统计结果：

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

针对方法1，方法2的优化缩短了查询时间，提高了查询性能。

 

方法3：

---

 

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

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

 

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

use Testgoif 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);endgocreate 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.idgo

在列FullPath添加全文索引：

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

 

方法3脚本（up_SearchAddressByNameV2）：

View Code

 

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

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

客户端统计结果：

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

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

 

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

 

方法4

---

 

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

方法4脚本（up_SearchAddressByNameV3）：

View Code

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

返回195条记录。

客户端统计结果：

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

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

 

小结：

---

 

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