多表多列基于自由文本关键词的搜索实现方案咨询

1. 功能专业名称 #

这个功能叫做全局搜索（Global Search），也常被称为跨实体搜索（Cross-Entity Search）或统一搜索（Unified Search），核心是在无上下文场景下，跨多个业务实体完成关键词检索。

2. 当前思路的合理性 #

思路方向是对的：通过整合多实体的可搜索字段，返回统一格式的搜索结果，符合全局搜索的需求。但存在几个明显的性能问题：

• 重复查询InternetUsers表，额外增加数据库IO开销；
• 使用UNION会自动对结果集去重排序，消耗多余计算资源；
• NEWID()生成随机ID无实际业务意义，还会增加不必要的计算；
• 未提前过滤数据，会先全表UNION再过滤，数据量过大直接导致卡顿。

3. 更优实现方式 #

优化后的SQL示例 #

SELECT
  -- 用实体ID+类型作为唯一标识，替代无意义的NEWID()
  CONCAT(CompanyId, '_C') AS [Id],
  CompanyId AS [EntityId],
  NameE AS [Keyword],
  'C' AS [Type]
FROM dbo.Companies
WHERE NameE LIKE '%@Keyword%' -- 下推过滤条件，提前缩小数据集

UNION ALL -- 用UNION ALL替代UNION，避免去重排序开销

SELECT
  CONCAT(InternetUserId, '_IU_Name'),
  InternetUserId AS [EntityId],
  NameE AS [Keyword],
  'IU' AS [Type]
FROM InternetUsers
WHERE NameE LIKE '%@Keyword%'

UNION ALL

SELECT
  CONCAT(InternetUserId, '_IU_UserName'),
  InternetUserId AS [EntityId],
  UserName AS [Keyword],
  'IU' AS [Type]
FROM InternetUsers
WHERE UserName LIKE '%@Keyword%'

UNION ALL

SELECT
  CONCAT(ParkingCardId, '_P'),
  ParkingCardId AS [EntityId],
  ParkingCardId AS [Keyword],
  'P' AS [Type]
FROM ParkingCards
WHERE ParkingCardId LIKE '%@Keyword%'

额外优化建议 #

• 合并InternetUsers查询：用CROSS APPLY合并两次查询，减少表扫描次数：

SELECT
  CONCAT(iu.InternetUserId, '_IU_', up.FieldName),
  iu.InternetUserId AS [EntityId],
  up.KeywordValue AS [Keyword],
  'IU' AS [Type]
FROM InternetUsers iu
CROSS APPLY (
  VALUES
    ('NameE', iu.NameE),
    ('UserName', iu.UserName)
) up(FieldName, KeywordValue)
WHERE up.KeywordValue LIKE '%@Keyword%'

这样只需扫描InternetUsers表一次，大幅降低IO开销。

• 索引优化：给每个可搜索字段创建非聚集索引，比如：

CREATE NONCLUSTERED INDEX IX_Companies_NameE ON dbo.Companies(NameE) INCLUDE(CompanyId);
CREATE NONCLUSTERED INDEX IX_InternetUsers_NameE ON dbo.InternetUsers(NameE) INCLUDE(InternetUserId);
CREATE NONCLUSTERED INDEX IX_InternetUsers_UserName ON dbo.InternetUsers(UserName) INCLUDE(InternetUserId);
CREATE NONCLUSTERED INDEX IX_ParkingCards_ParkingCardId ON dbo.ParkingCards(ParkingCardId);

如果数据库支持全文索引（如SQL Server的全文索引），用全文检索替代LIKE，性能提升会更明显，尤其是数据量较大时。

• 前端配合：除了限制3个字符触发查询，还可添加防抖逻辑（比如用户输入停止500ms后再发起请求），避免频繁查询导致数据库压力过大。

内容的提问来源于stack exchange，提问作者DoomerDGR8