代码之家 › 专栏 › 技术社区 › Matthew Talbert

SQL Server搜索正确名称全文索引与like+soundex

lucene.net full-text-search fluent-nhibernate sql-server

Matthew Talbert · 技术社区 · 14 年前

我有一个拥有(目前)3500万行的人名数据库。我需要知道什么是快速搜索这些名字的最佳方法。当前的系统(不是由我设计的)只是将名字和姓氏列编入索引,并使用“like”查询和使用soundex的附加选项(尽管我不确定这是否实际被使用了很多)。性能一直是这个系统的一个问题,所以目前搜索的结果限制在200个(这仍然需要很长的时间才能运行)。所以,我有几个问题:

全文索引对正确的名称有效吗?
如果是这样,查询正确名称的最佳方法是什么?(包含、自由文本等)
有没有其他更好的系统(如lucene.net)?

仅供参考,我正在使用FluentNHibernate进行数据访问,因此使用该方法的方法将是首选的。我目前正在使用SQL Server 2008。

编辑我想补充一点,我非常感兴趣的解决方案,将处理像常见拼写错误的名称,如“smythe”、“smith”,以及名字,如“to m as”、“thomas”。

查询计划

  |--Parallelism(Gather Streams)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[Test].[Id], [Expr1004]) OPTIMIZED WITH UNORDERED PREFETCH)
            |--Hash Match(Inner Join, HASH:([testdb].[dbo].[Test].[Id])=([testdb].[dbo].[Test].[Id]))
            |    |--Bitmap(HASH:([testdb].[dbo].[Test].[Id]), DEFINE:([Bitmap1003]))
            |    |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([testdb].[dbo].[Test].[Id]))
            |    |         |--Index Seek(OBJECT:([testdb].[dbo].[Test].[IX_Test_LastName]), SEEK:([testdb].[dbo].[Test].[LastName] >= 'WHITDÃ¾' AND [testdb].[dbo].[Test].[LastName] < 'WHITF'),  WHERE:([testdb].[dbo].[Test].[LastName] like 'WHITE%') ORDERED FORWARD)
            |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([testdb].[dbo].[Test].[Id]))
            |         |--Index Seek(OBJECT:([testdb].[dbo].[Test].[IX_Test_FirstName]), SEEK:([testdb].[dbo].[Test].[FirstName] >= 'THOMARÃ¾' AND [testdb].[dbo].[Test].[FirstName] < 'THOMAT'),  WHERE:([testdb].[dbo].[Test].[FirstName] like 'THOMAS%' AND PROBE([Bitmap1003],[testdb].[dbo].[Test].[Id],N'[IN ROW]')) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([testdb].[dbo].[Test].[PK__TEST__3214EC073B95D2F1]), SEEK:([testdb].[dbo].[Test].[Id]=[testdb].[dbo].[Test].[Id]) LOOKUP ORDERED FORWARD)

以上SQL:

SELECT * FROM testdb.dbo.Test WHERE LastName LIKE 'WHITE%' AND FirstName LIKE 'THOMAS%'

根据米奇的建议,我创建了这样一个索引:

CREATE INDEX IX_Test_Name_DOB
ON Test (LastName ASC, FirstName ASC, BirthDate ASC)
INCLUDE (and here I list the other columns)

对于我的典型搜索(最后、第一和出生日期),我的搜索速度现在非常快。

3 回复 | 直到 14 年前

Mitch Wheat 14 年前

取决于您的查询是什么样子的。

如果你在寻找 LIKE '%abc%' 然后就不能使用索引,而当搜索 LIKE 'abc%' 可以使用索引。此外,如果名字和姓氏的索引没有“覆盖”发出的查询,那么将执行键查找(书签查找),并显著影响性能。

您的索引是否定期重建?

您有示例查询计划吗?

更新 :查询的覆盖索引可以用于执行Where条件,还具有满足查询其余部分所需的所有列,如选择列列表。

Using Covering Indexes to Improve Query Performance

更新 :即使在 (Lastname, Firstname) (由于lastname应该更具选择性),在表聚集索引中仍然需要查找所有其他列(“*”列列表)。

Donnie 14 年前

我不太喜欢Soundex。我认为新的算法迭代会更好,但是你将英语中的每个单词都散列到一个相当小的散列中。随着时间的推移,这往往会产生大量的错误匹配。我读过这个比喻,它的后继双比喻更好,但我没有直接的经验。

米奇的报道 like 很彻底,所以我不想再重复一遍。

Ken Bloom 14 年前

如果在“名字”和“姓氏”列上创建索引,则使用like的精确匹配搜索和前缀搜索将变得异常快速。

中 MySQL ,“如果like的参数不是以通配符开头的常量字符串,则索引也可用于like比较。”我认为MS SQL有类似的规则,但请检查MS SQL文档以确保。)

要加快soundex搜索速度,请存储first name和last name新列的soundex版本,并在这些列上创建索引。