网站导航
> 技术文档 > 如何在不改动表结构和 SQL 的前提下，用 Elasticsearch 优化百万级复杂查询性能

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

网友: 技术文档 2025-08-08 05:44:02

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

一、背景介绍

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

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

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

二、遇到的性能瓶颈

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

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

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

三、目标与约束

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

目标/限制 要求 性能 查询时间需控制在 500ms 内 数据一致性 与主库保持准实时同步 接入方式 封装在原有 Service 层，保持 SQL 接口不变 查询能力 支持多字段模糊、in、range 等多条件组合 技术选型 引入 ES，不影响关系型数据库主逻辑

四、整体解决方案设计

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排序不准的问题。

六、效果对比与优化收益

项目 优化前 优化后 查询耗时 数秒级 300~500ms 查询精度 全字段模糊 支持 可扩展性 差，SQL 难维护 高，DSL 可配置 数据一致性 即时 准实时（秒级） 业务侵入 高 低（不改 SQL）

七、可拓展方向

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

八、总结

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

它适用于如下场景：

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

相关问题