Elasticsearch 的 SQL 与 DSL 转换

井九

发布于 2025-10-31 08:38:39

700

代码可运行

文章被收录于专栏：四楼没电梯四楼没电梯

运行总次数：0

代码可运行

一、前言

Elasticsearch（简称 ES）是基于 Lucene 的分布式搜索与分析引擎，其强大之处在于灵活的 DSL（Domain Specific Language）查询语法。但对于习惯使用 SQL 的开发者来说，DSL 语法初期学习成本不低——尤其是在复杂的聚合、嵌套、分页等场景中。

幸运的是，ES 从 6.x 开始支持了 SQL 查询接口，让我们可以像操作数据库一样使用 SQL。更妙的是，ES 还提供了 SQL → DSL 的转换接口，让开发者可以编写 SQL，自动生成底层 DSL，从而兼顾可读性与性能优化。

二、为什么要理解 SQL ↔ DSL 转换？

角色	关注点
数据分析师	想用 SQL 快速查询
后端开发者	需要生成或优化 DSL 查询
架构师	希望统一查询接口、可视化查询构建
调试/排错	需要确认 SQL 翻译出的 DSL 是否高效、正确

理解 SQL 与 DSL 的对应关系，可以让我们：

🚀 快速从 SQL 过渡到原生 DSL；
🔍 精准控制查询性能（例如字段分词、聚合层级）；
🧩 动态构建查询（在代码中使用 JSON 模板）；
🛠️ 排查 SQL 查询与结果不符的问题。

三、基础示例：SQL → DSL

我们先通过官方 _sql/translate 接口来看看 SQL 是如何被翻译成 DSL 的。

示例 SQL

SELECT title, author, publish_date
FROM books
WHERE author = '鲁迅' AND publish_date >= '1920-01-01'
ORDER BY publish_date DESC
LIMIT 5

转换为 DSL

POST /_sql/translate
{
  "query": "SELECT title, author, publish_date FROM books WHERE author = '鲁迅' AND publish_date >= '1920-01-01' ORDER BY publish_date DESC LIMIT 5"
}

返回结果：

{
  "size": 5,
  "query": {
    "bool": {
      "filter": [
        { "term": { "author.keyword": "鲁迅" } },
        { "range": { "publish_date": { "gte": "1920-01-01" } } }
      ]
    }
  },
  "_source": ["title", "author", "publish_date"],
  "sort": [{ "publish_date": { "order": "desc" } }]
}

🧠 解读：

WHERE → bool.filter
精确匹配字段（author.keyword）使用 term；
时间条件转为 range；
ORDER BY 对应 sort；
LIMIT 对应 size；
SELECT 对应 _source。

四、聚合查询示例

SQL 示例

SELECT category, COUNT(*) AS total
FROM products
WHERE price > 100
GROUP BY category
ORDER BY total DESC

转换结果 DSL

{
  "size": 0,
  "query": {
    "range": {
      "price": { "gt": 100 }
    }
  },
  "aggs": {
    "groupby_category": {
      "terms": {
        "field": "category.keyword",
        "order": { "_count": "desc" }
      }
    }
  }
}

🧩 说明：

GROUP BY 转换为 terms 聚合；
COUNT(*) 是默认的 doc_count；
ORDER BY total DESC 对应 order；
SQL 层的结果相当于 DSL 中的 aggregations。

五、分页与排序

SQL 中的：

SELECT * FROM books ORDER BY publish_date DESC LIMIT 10 OFFSET 20

对应 DSL：

{
  "from": 20,
  "size": 10,
  "sort": [{ "publish_date": { "order": "desc" } }]
}

📘 注意：

LIMIT n OFFSET m → size + from
ES 查询结果默认最多返回 10,000 条，可通过 search_after 或 scroll 机制分页更大结果集。

六、模糊匹配与全文搜索

SQL

SELECT title FROM articles WHERE title LIKE '%AI%'

DSL

{
  "query": {
    "wildcard": {
      "title": {
        "value": "*AI*"
      }
    }
  },
  "_source": ["title"]
}

💡 或者使用更自然的 match 查询（适合中文分词字段）：

{
  "query": {
    "match": {
      "title": "AI"
    }
  }
}

七、嵌套查询与复杂条件

SQL

SELECT name, age
FROM users
WHERE (city = '北京' OR city = '上海') AND age BETWEEN 20 AND 30

DSL

{
  "query": {
    "bool": {
      "must": [
        { "range": { "age": { "gte": 20, "lte": 30 } } }
      ],
      "should": [
        { "term": { "city.keyword": "北京" } },
        { "term": { "city.keyword": "上海" } }
      ],
      "minimum_should_match": 1
    }
  },
  "_source": ["name", "age"]
}

🧠 must 表示 AND，should 表示 OR。当有多个 should 条件时，记得加上 "minimum_should_match": 1。

八、实战技巧：从 JDBC 到 DSL 的双向使用

在 Java、Python 等语言中，我们可以通过 JDBC 驱动 执行 ES SQL 查询。

Connection conn = DriverManager.getConnection("jdbc:es://http://localhost:9200");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT title, price FROM products WHERE price > 100");

但在生产系统中，很多团队会采用以下模式：

✅ 优点：

SQL 层面可读；
DSL 层面可调优；
两者解耦，可灵活缓存和日志记录。

九、常见坑与注意事项

问题	原因	解决方式
field not found	未指定 .keyword 字段	对精确匹配用 field.keyword
LIKE 语句性能差	wildcard 查询开销大	尽量使用 match_phrase 或 prefix
COUNT 出现不一致	SQL 聚合默认去重	在 DSL 中明确 cardinality
OFFSET 太大性能差	from 超过 10000	使用 search_after

十、总结

维度	SQL	DSL
可读性	✅ 高	❌ 较低
灵活性	⚠️ 中等	✅ 非常高
性能调优	❌ 受限	✅ 可完全控制
使用场景	快速查询 / 可视化分析	后端系统、复杂过滤、聚合统计