标记索引

Question

在我的应用程序中，某些项目将有标签--非常类似于这里的问题标签。

我使用Azure SQL数据库将我的标记存储在主“标记”表中。我认为标签表将只有三个列，即Id、Tag、IsApproved。

我应该如何索引我的标签，以便我可以对它们运行查询？我确信，在某个时候，我可能需要缓存它们，但同时，我应该使用什么来索引呢？我主要担心的是，用户可能会键入中间的内容。例如，我可能有以下标记：

• 写作
• 创造性写作
• 技术写作
• 脚本编写者

当用户开始键入字母时

令状

我想建议以上四项建议。我不能在我的LIKE查询中使用select来扫描整个表。我该怎么处理？

Answer 1

在这种情况下，您可以采取许多不同的方法。我在下文详细解释了这一点。你可以试试看哪一个更适合你的情况。

在这里解释更多涉及的方法之前，您可以使用下面的查询来使用简单的方法；如果标记表不包含太多行，则应该首选这种方法，因为那样的话，即使是表扫描也足够快。

SELECT DISTINCT t.Tag FROM Tags t where t.Tag LIKE '%writ%'

如果标记表可以包含数以百万计的标记，那么可以使用下面三种方法中的一种。

方法1

根据标记列在标记表上创建全文索引。

这可以很容易地在SSMS中通过右键单击标签表和选择全文索引和进一步的选项。您将得到一个向导，该向导将指导您创建全文索引。

使用这种方法，您将使用如下所示的查询。请注意，您只能在搜索子字符串的末尾使用一个通配符*，这意味着它只返回那些标记starting with writ，而不返回那些以令状开头有第二个或第三个单词的标记。如果您的要求是搜索标签中以搜索子字符串开头的所有单词，这可能是这种方法的一个缺点。

SELECT DISTINCT t.Tag FROM Tags t WHERE FREETEXT( t.Tag, 'writ*')

方法2

由于标记列仅限于50个左右的字符，所以每个标签的字数将有限，而不是100或200个左右。

• 创建一个名为TagFragments的新表，其列如(ID int, TagID int, TagFragment varchar(50))下面所示。您可以使用下面的查询创建此表。 创建表dbo.TagFragments( IDENTITY(1,1) NULL主键，TagId int，TagFragment VARCHAR(50) ) )
• 此表将以规范化的方式包含标记中的所有单词。您可以在填充标记表的同时轻松地填充它。
• 因此，如果您的ID为4的标记是Creative writing，那么这个表将包含这个单个标记记录的2行。假设1和2是TagFragments表中自动生成的in，这些片段记录将如下所示。 (1，4，“创意”) ( 2，4，“写作”)
• 在TagFragment列上为TagFragments表创建索引。这应该是一张非唯一的桌子。此索引现在将用于标记搜索查询。您应该创建一个包容性索引，通过包含列TagID，而不是在索引中包含它，从而进一步优化搜索查询。

与方法1相比，这种方法具有优势，因为它搜索标记中以搜索子字符串开头的所有单词。

使用下面的查询将匹配标记作为用户类型获取到textbox中。这里的关键是始终使用外卡搜索，比如writ%，它将使用一个索引。如果您使用%writ或%writ%和LIKE，那么您将不会使用任何索引，并且将完成表扫描。

标签搜索查询

SELECT DISTINCT t.Tag
FROM Tags t
INNER JOIN TagFragments tf ON t.ID = tf.TagID
WHERE tf.TagFragment LIKE 'writ%';

创建包容性索引查询

CREATE NONCLUSTERED INDEX TagFragmentsSearchIndex ON dbo.TagFragments
(
    TagFragment ASC
)
INCLUDE (TagID)

注意:包容性索引的优点是，在使用上面的查询时，可以通过主键ID保存一个额外的表查找步骤，以便从TagID表中查找TagFragments值。

混合方法

这是可以使用的另一种方法，它使用TableFragments表Approach 2和TableFragment列的全文索引。您不需要创建方法2中提到的索引，而是在TagID表中的TagFragments列上创建一个非唯一索引。

对此的搜索查询如下。

SELECT distinct t.Tag  FROM Tags t INNER JOIN TagFragments tf
  ON t.ID =  tf.TagID WHERE FREETEXT( tf.TagFragment, 'writ*');

这种混合方法的优点是，它搜索标记中的所有单词，从搜索子字符串开始，当有多个标记时，搜索速度会更快。