从SQL到DSL：手把手教你将熟悉的‘in’和‘not in’查询迁移到Elasticsearch 8

从SQL到DSL手把手教你将熟悉的‘in’和‘not in’查询迁移到Elasticsearch 8如果你是从MySQL或PostgreSQL这类关系型数据库转向Elasticsearch的开发者最头疼的问题之一可能就是如何用ES实现那些熟悉的SQL操作。特别是IN和NOT IN这种高频使用的查询条件在ES中该如何表达本文将带你从零开始通过三种不同的方式实现这一需求让你在技术栈迁移过程中少走弯路。1. 基础概念理解SQL与DSL的差异关系型数据库和Elasticsearch在查询语言设计上有着本质区别。SQL是声明式的你只需要告诉数据库要什么而DSLDomain Specific Language是描述性的你需要明确描述如何获取。这种思维转换是很多开发者初学ES时的最大障碍。以IN操作为例在MySQL中你可能这样写SELECT * FROM orders WHERE name IN (老万, 小明);而在Elasticsearch中对应的DSL查询使用terms{ query: { terms: { name: [老万, 小明] } } }两者的核心差异体现在特性SQLElasticsearch DSL查询方式声明式描述式语法结构表格式JSON嵌套执行计划优化器自动选择需要手动指定查询类型扩展性标准统一可灵活组合多种查询条件提示ES的terms查询默认是精确匹配类似于SQL中的比较而非LIKE模糊匹配。2. 原生DSL实现方案2.1 实现IN查询在Elasticsearch中terms查询是IN操作最直接的对应物。假设我们有一个订单索引需要查询特定客户的订单GET order_index/_search { query: { terms: { name: [老万, 小明] } } }这个查询会返回所有name字段值为老万或小明的文档。值得注意的是terms查询对大小写敏感被查询字段必须是keyword类型如果是text类型需要特殊处理默认情况下只要匹配任意一个值就会返回文档2.2 实现NOT IN查询NOT IN的DSL实现稍微复杂些需要结合bool查询的must_not子句GET order_index/_search { query: { bool: { must_not: [ { terms: { name: [老万, 小明] } } ] } } }这种查询结构可以理解为排除所有name为老万或小明的文档。实际项目中我们经常需要组合多种条件{ query: { bool: { must: [ { range: { amount: { gte: 100 } } } ], must_not: [ { terms: { name: [老万, 小明] } } ] } } }这个复杂查询表示查找金额大于等于100且姓名不是老万或小明的订单。3. 使用ES SQL接口如果你暂时无法适应DSL语法Elasticsearch提供了SQL接口作为过渡方案。这种方式特别适合快速验证查询逻辑临时数据分析从关系型数据库迁移的初期阶段3.1 直接执行SQL查询对于IN查询POST /_sql?formattxt { query: SELECT * FROM order_index WHERE name IN (老万,小明) }对于NOT IN查询POST /_sql?formattxt { query: SELECT * FROM order_index WHERE name NOT IN (老万,小明) }SQL接口支持大多数常见的SQL语法包括WHERE条件GROUP BY分组HAVING过滤ORDER BY排序LIMIT分页3.2 理解SQL接口的局限性虽然SQL接口用起来很顺手但它有一些重要限制功能不全不支持某些高级ES特性如聚合管道、脚本字段等性能考虑复杂查询可能不如原生DSL高效版本差异不同ES版本支持的SQL语法可能有差异注意生产环境中建议最终迁移到原生DSL以获得最佳性能和完整功能支持。4. 使用翻译端点学习DSLElasticsearch提供了一个强大的学习工具_sql/translate端点。它可以将SQL查询转换为等效的DSL是理解两种语法对应关系的绝佳途径。4.1 基本翻译示例将NOT IN查询转换为DSLPOST /_sql/translate { query: SELECT name FROM order_index WHERE name NOT IN (老万,小明) }返回结果会显示对应的DSL结构{ size: 1000, query: { bool: { must_not: [ { terms: { name: [老万,小明], boost: 1.0 } } ], boost: 1.0 } }, _source: { includes: [name], excludes: [] } }4.2 高级查询翻译翻译功能也支持复杂SQLPOST /_sql/translate { query: SELECT name, SUM(amount) as total FROM order_index WHERE name IN (老万,小明) GROUP BY name HAVING total 100 ORDER BY total DESC }通过观察翻译结果你可以逐步理解如何用terms实现IN如何用aggregations实现GROUP BY如何用bucket_selector实现HAVING如何用sort实现ORDER BY5. 实战案例订单查询系统迁移让我们通过一个完整的订单查询案例演示从SQL到DSL的迁移过程。假设我们需要实现以下业务需求查询特定客户群的订单IN排除某些客户的订单NOT IN组合金额过滤条件对结果排序和分页5.1 SQL实现方案-- 查询金额大于50的老万和小明的订单按金额降序排列 SELECT * FROM order_index WHERE name IN (老万, 小明) AND amount 50 ORDER BY amount DESC LIMIT 10;5.2 原生DSL实现GET order_index/_search { query: { bool: { must: [ { terms: { name: [老万, 小明] } }, { range: { amount: { gt: 50 } } } ] } }, sort: [ { amount: { order: desc } } ], size: 10 }5.3 使用SQL接口实现POST /_sql?formattxt { query: SELECT * FROM order_index WHERE name IN (老万, 小明) AND amount 50 ORDER BY amount DESC LIMIT 10 }5.4 三种方案的选择建议根据不同的场景可以选择最适合的实现方式场景推荐方案理由快速验证/临时分析SQL接口开发效率高适合熟悉SQL的开发者生产环境稳定运行原生DSL性能最优功能最全可维护性强学习DSL语法翻译端点通过对比学习加速理解SQL与DSL的对应关系复杂聚合分析原生DSLSQL接口对复杂聚合支持有限简单条件查询均可根据团队熟悉程度选择6. 性能优化与最佳实践当你在实际项目中使用terms查询时以下几点可以帮助提升性能控制值列表大小terms查询适合值列表较小的情况通常不超过几百个。对于大量值考虑使用terms_set查询将值存储在单独的索引中通过terms查询引用使用terms查询的lookup特性字段类型选择精确匹配使用keyword类型需要分词搜索使用text类型缓存利用terms查询结果默认会被缓存对频繁使用的查询可以设置更高的缓存权重索引设计对于高频查询的字段可以考虑设置为doc_values: true对基数高的字段使用eager_global_ordinalsPUT order_index { mappings: { properties: { name: { type: keyword, doc_values: true, eager_global_ordinals: true } } } }监控与调优使用_search接口的profile参数分析查询性能关注慢查询日志GET order_index/_search { profile: true, query: { terms: { name: [老万, 小明] } } }