如何高效搜索多不同数据类型列的海量数据？

问题背景 #

表结构 #

DataElement表结构如下：

DataElement table 
ValueAsBoolean : bit nullable
ValueAsDatetime : datetime2 nullable
ValueAsDecimal : decimal(10,3) nullable
ValueAsInt : int nullable
ValueAsString : nvarchar(100) nullable

数据示例 #

搜索规则 #

• ValueAsDatetime支持按时区及UI展示格式（如Tue, 9 Sep 2022）搜索
• ValueAsBoolean可通过"TRUE"、"FALSE"或其部分文本搜索
• ValueAsString、ValueAsInt、ValueAsDecimal支持输入文本的模糊匹配

现有实现及问题 #

当前基于EF Core的查询逻辑是将所有列转字符串、格式化后检查是否包含关键词，生成的SQL如下：

SELECT *
FROM [DataElement]
WHERE 
    (([ValueAsBoolean] IS NOT NULL AND (('T' = N'')
    OR (CHARINDEX('T', LOWER( CASE WHEN ValueAsBoolean = 1 then 'true' else 'false' END)) > 0))) 
    OR ([ValueAsDateTime] IS NOT NULL AND (('T' = N'') 
    OR (CHARINDEX('T', LOWER( FORMAT(CONVERT(DATETIME, SWITCHOFFSET( [ValueAsDateTime], DATEPART(TZOFFSET, ValueAsDateTime AT TIME ZONE 'UTC' ))), 'ddd, d MMM yyyy H:mm:ss' ))) > 0)))) 
    OR ([ValueAsDecimal] IS NOT NULL AND (('T' = N'')
    OR (CHARINDEX('T', LOWER(CONVERT(VARCHAR(100), [ValueAsDecimal]))) > 0)))) 
    OR ([ValueAsInt] IS NOT NULL AND (('T' = N'')
    OR (CHARINDEX('T', LOWER(CONVERT(VARCHAR(11), [ValueAsInt]))) > 0)))) 
    OR ([ValueAsString] IS NOT NULL AND (('T' = N'')
    OR (CHARINDEX('T', LOWER([ValueAsString])) > 0)))

当数据量超过50万条时，查询耗时超30秒，超出超时限制。曾考虑新增NVARCHAR(100)列存储统一转换文本并建索引，但无法满足日期多格式搜索需求。

解决方案 #

1. 分字段针对性优化索引 #

针对不同字段的搜索特性，单独创建适合的索引，避免全表扫描：

• ValueAsBoolean：
  提前将bit值转换为固定字符串（'true'/'false'）存入计算列，并创建非聚集索引：

  ALTER TABLE DataElement ADD BoolText AS CASE WHEN ValueAsBoolean = 1 THEN 'true' ELSE 'false' END PERSISTED;
  CREATE NONCLUSTERED INDEX IX_DataElement_BoolText ON DataElement(BoolText);
  

  查询时直接对BoolText做模糊匹配，无需实时转换。

• ValueAsDateTime：
  创建计算列存储对应时区的UI格式日期字符串，并为计算列创建非聚集索引：

  -- 示例：存储UTC时区的UI格式字符串
  ALTER TABLE DataElement ADD UtcDateText AS FORMAT(SWITCHOFFSET(ValueAsDateTime, DATEPART(TZOFFSET, ValueAsDateTime AT TIME ZONE 'UTC')), 'ddd, d MMM yyyy') PERSISTED;
  CREATE NONCLUSTERED INDEX IX_DataElement_UtcDateText ON DataElement(UtcDateText);
  
  -- 若需支持多时区，可添加对应计算列及索引
  

  查询时根据用户当前时区选择对应计算列做模糊匹配。

• ValueAsString：
  创建全文索引，替代CHARINDEX的模糊匹配，全文搜索在海量数据下性能远高于通配符匹配：

  CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
  CREATE FULLTEXT INDEX ON DataElement(ValueAsString) KEY INDEX PK_DataElement;
  

  查询时使用CONTAINS或FREETEXT：

  WHERE CONTAINS(ValueAsString, 'T')
  

• ValueAsInt/ValueAsDecimal：
  创建计算列存储其字符串形式并建索引：

  ALTER TABLE DataElement ADD IntText AS LOWER(CONVERT(VARCHAR(11), ValueAsInt)) PERSISTED;
  CREATE NONCLUSTERED INDEX IX_DataElement_IntText ON DataElement(IntText);
  
  ALTER TABLE DataElement ADD DecimalText AS LOWER(CONVERT(VARCHAR(100), ValueAsDecimal)) PERSISTED;
  CREATE NONCLUSTERED INDEX IX_DataElement_DecimalText ON DataElement(DecimalText);
  

2. 拆分查询合并结果 #

将原单一大查询拆分为针对每个字段的独立查询，通过UNION ALL合并结果，每个子查询可利用对应字段的索引：

SELECT * FROM DataElement WHERE BoolText LIKE '%T%'
UNION ALL
SELECT * FROM DataElement WHERE UtcDateText LIKE '%T%'
UNION ALL
SELECT * FROM DataElement WHERE CONTAINS(ValueAsString, 'T')
UNION ALL
SELECT * FROM DataElement WHERE IntText LIKE '%T%'
UNION ALL
SELECT * FROM DataElement WHERE DecimalText LIKE '%T%'

使用UNION ALL而非UNION，避免去重带来的性能开销，若需去重可最后再处理。

3. 引入全文搜索统一索引（推荐） #

创建计算列统一存储各字段的可搜索文本，再为该列创建全文索引：

-- 先创建计算列统一存储各字段的可搜索文本
ALTER TABLE DataElement ADD SearchableText AS 
  COALESCE(BoolText + ' ', '') +
  COALESCE(UtcDateText + ' ', '') +
  COALESCE(IntText + ' ', '') +
  COALESCE(DecimalText + ' ', '') +
  COALESCE(ValueAsString, '') PERSISTED;

-- 创建全文索引
CREATE FULLTEXT INDEX ON DataElement(SearchableText) KEY INDEX PK_DataElement;

查询时直接使用全文搜索：

SELECT * FROM DataElement WHERE CONTAINS(SearchableText, 'T')

这种方式既统一了搜索入口，又能利用全文索引的高性能，同时通过计算列提前处理好日期、布尔等字段的格式转换，满足搜索需求。

4. 分页优化 #

若搜索结果量大，必须实现分页，避免一次性返回所有数据，结合上述索引优化，分页查询的性能会大幅提升：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNum FROM DataElement WHERE CONTAINS(SearchableText, 'T')
) AS Temp WHERE RowNum BETWEEN 1 AND 20

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