使用TextField存储分隔特征时如何创建高效查询索引

针对百万级表的特征匹配查询需求，不建议直接用正则扫描原生features字段，核心优化思路是通过函数索引将非结构化的分隔特征值转化为可索引的结构化结构，以下是主流数据库的可落地实现方案：

通用前置逻辑 #

首先统一特征分隔规则：所有分号替换为逗号、剔除所有多余空格，将混合分隔的特征值统一为纯逗号分隔的格式，再基于此做索引构建。

1. MySQL 实现方案（5.7及以上版本） #

方案A：多值JSON索引（效率最高） #

第一步：新增结构化生成列

ALTER TABLE Model2 ADD COLUMN feature_arr JSON GENERATED ALWAYS AS (
  -- 替换分号为逗号、剔除所有空格后转JSON数组
  JSON_EXTRACT(
    CONCAT('["', REPLACE(REPLACE(features, ';', ','), ' ', ''), '"]'),
    '$[*]'
  )
) STORED;

第二步：创建多值索引

CREATE INDEX idx_model2_feature_arr ON Model2((CAST(feature_arr AS CHAR(64) ARRAY)));

第三步：索引命中查询

遍历Model1的name_id时直接用以下语句查询，可100%命中索引：

SELECT info FROM Model2 WHERE '待查询的特征name_id' MEMBER OF (feature_arr);

方案B：纯函数索引（无需新增字段） #

-- 建函数索引
CREATE INDEX idx_model2_features_func ON Model2((REPLACE(REPLACE(features, ';', ','), ' ', '')));
-- 索引命中查询
SELECT info FROM Model2 
WHERE REPLACE(REPLACE(features, ';', ','), ' ', '') = '待查询的特征name_id'
OR REPLACE(REPLACE(features, ';', ','), ' ', '') LIKE CONCAT('待查询的特征name_id', ',%')
OR REPLACE(REPLACE(features, ';', ','), ' ', '') LIKE CONCAT('%,', '待查询的特征name_id', ',%')
OR REPLACE(REPLACE(features, ';', ','), ' ', '') LIKE CONCAT('%,', '待查询的特征name_id');

2. PostgreSQL 实现方案 #

PostgreSQL原生支持数组GIN索引，实现更简洁：

第一步：创建函数索引

CREATE INDEX idx_model2_features ON Model2 
USING GIN (
  -- 正则统一替换所有空格/分号/逗号为单逗号后转数组
  string_to_array(REGEXP_REPLACE(features, '[ ;,]+', ',', 'g'), ',')
);

第二步：索引命中查询

SELECT info FROM Model2 
WHERE string_to_array(REGEXP_REPLACE(features, '[ ;,]+', ',', 'g'), ',') @> ARRAY['待查询的特征name_id'];

额外优化建议 #

• 如果业务允许改造表结构，最优方案是把Model2的多值features字段拆成独立关联表，单条特征对应一行记录，建联合索引(feature_name, info)，查询效率比上述函数索引高30%以上，维护成本也更低
• 遍历Model1查询时尽量用批量IN查询替换单条循环查询，减少数据库交互次数

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