ElasticSearch跨多行查询多列最佳匹配的技术方案咨询

核心思路 #

针对大量列的场景，放弃原宽表结构，将每行的Key-Val对拆分为独立文档，通过聚合查询实现按列（key_type）分组、计算自定义评分后取最优Val值，从根本上解决列数过多导致的查询瓶颈。

步骤1：数据模型重构 #

将原宽表中每个非NULL的Val对应的Key-Value组合拆分为独立文档，示例如下：

原行1（ID=1）转换为2个有效文档：

{
  "row_id": 1,
  "key_type": "A",
  "key_value": "abc",
  "key_prefix": null, // 完全匹配时设为null
  "val": "va0",
  "position": 1
},
{
  "row_id": 1,
  "key_type": "C",
  "key_value": "c*",
  "key_prefix": "c", // 前缀通配符时提取前缀
  "val": "vc0",
  "position": 3
}

原行2（ID=2）转换为2个有效文档：

{
  "row_id": 2,
  "key_type": "B",
  "key_value": "bcd",
  "key_prefix": null,
  "val": "vb1",
  "position": 2
},
{
  "row_id": 2,
  "key_type": "C",
  "key_value": "c*",
  "key_prefix": "c",
  "val": "vc1",
  "position": 3
}

注意：Val为NULL的Key-Value组合直接跳过，不生成文档（符合规则中"NULL不匹配"的要求）。

步骤2：创建索引Mapping #

设置字段类型以优化查询性能：

{
  "mappings": {
    "properties": {
      "row_id": {"type": "integer"},
      "key_type": {"type": "keyword"}, // 按列分组的标识
      "key_value": {"type": "keyword"}, // 存储原始Key值（含通配符）
      "key_prefix": {"type": "keyword"}, // 存储前缀通配符的前缀部分，优化查询
      "val": {"type": "keyword"}, // 根据实际Val类型调整（如text/integer）
      "position": {"type": "integer"} // 列的位置索引i
    }
  }
}

步骤3：编写查询DSL #

通过bool查询匹配所有符合条件的文档，script_score计算自定义评分，最后按key_type聚合取每个列的最高分Val：

{
  "size": 0, // 不需要返回原始文档，只取聚合结果
  "query": {
    "bool": {
      "should": [
        // 匹配Key A=abc的情况：完全匹配或前缀匹配
        {
          "bool": {
            "filter": [
              {"term": {"key_type": "A"}},
              {
                "bool": {
                  "should": [
                    {"term": {"key_value": "abc"}}, // 完全匹配
                    {"prefix": {"key_prefix": "abc"}} // 模式匹配（如果有前缀通配符文档）
                  ]
                }
              }
            ]
          }
        },
        // 匹配Key B=bcd的情况
        {
          "bool": {
            "filter": [
              {"term": {"key_type": "B"}},
              {
                "bool": {
                  "should": [
                    {"term": {"key_value": "bcd"}},
                    {"prefix": {"key_prefix": "bcd"}}
                  ]
                }
              }
            ]
          }
        },
        // 匹配Key C=cde的情况
        {
          "bool": {
            "filter": [
              {"term": {"key_type": "C"}},
              {
                "bool": {
                  "should": [
                    {"term": {"key_value": "cde"}},
                    {"prefix": {"key_prefix": "c"}}
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "script_score": {
    "script": {
      "source": """
        // 定义查询参数映射，支持扩展任意多列
        Map queryMap = params.queryMap;
        String keyType = doc['key_type'].value;
        String queryVal = queryMap.get(keyType);
        if (queryVal == null) return 0;
        
        String docVal = doc['key_value'].value;
        int position = doc['position'].value;
        
        // 计算评分
        if (docVal.equals(queryVal)) {
          return 2 * position; // 完全匹配
        } else if (docVal.endsWith('*')) {
          String prefix = docVal.substring(0, docVal.length()-1);
          if (queryVal.startsWith(prefix)) {
            return 1 + position; // 模式匹配
          }
        }
        return 0;
      """,
      "params": {
        "queryMap": {
          "A": "abc",
          "B": "bcd",
          "C": "cde"
        }
      }
    }
  },
  "aggs": {
    "group_by_key": {
      "terms": {"field": "key_type"},
      "aggs": {
        "top_val": {
          "top_hits": {
            "size": 1,
            "sort": [{"_score": {"order": "desc"}}],
            "_source": ["val"]
          }
        }
      }
    }
  }
}

步骤4：解析聚合结果 #

聚合结果中，每个key_type对应的top_val下的文档即为该列评分最高的非NULL Val值。针对示例查询，结果会返回：

• key_type=A → val=va0
• key_type=B → val=vb1
• key_type=C → val=vc1

优化点 #

1. 通配符扩展：如果存在中间带*的通配符（如b*c），将key_prefix替换为key_regex（存储转换后的正则表达式，如b.*c），查询时用regexp查询替代prefix，同时调整script中的匹配逻辑。
2. 性能优化：创建key_type + key_value的复合索引，加速查询；避免在script中做复杂字符串操作，提前预处理通配符规则。
3. 批量支持：queryMap可直接扩展至5k列，无需修改查询结构，完全适配大量列场景。

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