如何在不改动表结构和 SQL 的前提下，用 Elasticsearch 优化百万级复杂查询性能

面对海量数据、高复杂度查询与严格的业务限制，该如何借助 Elasticsearch 优化现有查询性能？本文提供一套不影响数据库结构、不改动 SQL 条件的完整落地方案。

一、背景介绍

在实际业务系统中，我们常常面临以下场景：

• 表数据量百万级，字段多达上百个；
• 查询条件复杂，常包含 LIKE、IN、范围等组合；
• 查询性能逐渐下降，严重影响页面加载；
• 不能修改原表字段、索引结构；
• 不允许调整现有 SQL 查询条件，甚至要保证业务透明迁移。

以上情况在某大型系统中真实存在，为了快速提升性能，我们引入了 Elasticsearch（以下简称 ES）作为查询加速引擎。

二、遇到的性能瓶颈

该系统核心查询 SQL 拥有以下典型特征：

• 主表数据量超百万条；
• 拥有 30+ 条件动态拼接；
• 多个字段支持模糊查询（如名称、标签等）；
• 数据来自多个关联表；
• 无法通过新增联合索引或字段重构解决。

传统关系型数据库在这种复杂 where 拼接下，即使加上分页，查询耗时仍达数秒甚至更久。

三、目标与约束

我们需要一个解决方案，满足以下几点：

四、整体解决方案设计

1. 架构概览

关系型数据库 (主表 + 扩展表)
   │
   ├──> Canal / DataX 同步数据至 Elasticsearch
   │
   ├──> Service 查询优先查 ES → 返回主键列表 → 关系型数据库回查详情

我们采用“ES 做条件过滤 + 关系型数据库回查详情”策略，不仅解决了查询速度，还保持了数据来源和权限逻辑不变。

2. 核心思路

• 所有查询条件统一映射为 ES DSL；
• 使用 ES 聚合条件筛选，返回主键列表；
• 保留分页参数，仅获取当前页主键；
• 最终由关系型数据库用 IN(id1, id2...) 回查全量字段；
• 不影响分页、权限、数据行精度。

五、技术实现细节

1. ES 映射设计（Mapping）

为了支持复杂字段类型，需合理设置字段类型和分词器：

{
  "mappings": {
    "properties": {
      "item_name": {
        "type": "text",
        "analyzer": "ik_max_word",
        "search_analyzer": "ik_smart"
      },
      "category_id": { "type": "keyword" },
      "event_date": { "type": "date" },
      "status_flag": { "type": "integer" }
    }
  }
}

• text + analyzer 组合解决 LIKE 查询；
• keyword 保证精确匹配；
• 范围查询字段（如日期、金额）设置为 date / float 类型。

2. 数据同步方案

• 方式一：Canal + Elasticsearch Sink 插件

  • 实时监听关系型数据库 binlog；
  • 增删改自动同步到 ES；
  • 适合需要准实时一致性的场景。

• 方式二：定时任务 + DataX

  • 周期性全量或增量同步；
  • 实现简单，但存在数据延迟；
  • 适用于容忍几分钟延迟的场景。

3. 查询封装逻辑（伪代码）

public List<Long> queryEsIds(QueryParam param) {
    BoolQueryBuilder boolQuery = QueryBuilders.boolQuery();

    if (StringUtils.isNotBlank(param.getItemName())) {
        boolQuery.must(QueryBuilders.matchQuery("item_name", param.getItemName()));
    }
    if (param.getCategoryId() != null) {
        boolQuery.filter(QueryBuilders.termQuery("category_id", param.getCategoryId()));
    }
    // ... 其他条件省略
    
    SearchSourceBuilder builder = new SearchSourceBuilder()
            .query(boolQuery)
            .from((page - 1) * size)
            .size(size)
            .fetchSource(false)
            .docValueField("id");

    SearchResponse response = esClient.search(new SearchRequest("main_index").source(builder), RequestOptions.DEFAULT);
    
    return extractIds(response);
}

然后再通过：

SELECT * FROM main_table WHERE id IN (es查询返回id集合)

4. 使用ID重新回表查询，通常称为“先ES查ID，再用ID回库查详情”或“反查”，它有几个明显优点：

• 避免全字段索引开销
  ES存储索引和倒排结构更适合快速定位匹配文档的ID，而不需要保存所有字段的数据。只用ES返回ID，减小ES负载。

• 保证数据准确和完整
  ES索引和主库数据存在同步延迟或不完全一致时，直接回库查主表详情保证最终展示数据准确、最新。

• 解决字段复杂和动态变化问题
  数据库字段多且频繁变更，维护ES全字段索引成本大。只用ES查ID，数据库字段结构变化对ES无影响。

• 支持复杂业务逻辑和权限控制
  业务逻辑、权限校验往往在数据库层，回表查询保证能应用完整逻辑，避免权限漏洞。

• 减少ES负载和响应时间
  ES只做条件过滤和ID定位，响应更快；数据库只查少量ID对应数据，压力分摊更合理。

• 分页和排序更精准
  数据库本身支持复杂排序和分页，回表后能精确控制返回数据顺序，避免ES排序不准的问题。

六、效果对比与优化收益

七、可拓展方向

• 多字段联合高亮展示；
• 热门查询缓存层引入（如 Redis）；
• 多租户数据隔离查询支持；
• 引入 Kibana 做运营数据分析。

八、总结

在不更改数据库结构、不重写 SQL 的情况下，通过引入 Elasticsearch 作为检索加速引擎，是一套兼顾性能、稳定性与成本投入的理想方案。

它适用于如下场景：

• 大字段、模糊匹配为主；
• 表结构复杂但数据变化不剧烈；
• 查询维度多但回查字段一致。