Mysql查询与Elasticsearch的DSL查询语句对照

作者：

小森同学，互联网公司搜索开发工程师。

前言

作为新入门的后端开发人员，一般对Mysql，SqlServer这类的关系型数据库或多或少都有了解。当入门Elasticsearch时，发现其DSL语句与关系型数据库的查询完全不一样，不再是那熟悉的语法，顿感门槛有点高。为了方便熟悉关系型数据库查询的同学，更加容易，快捷的理解并掌握DSL基础语法，本文将进行Mysql与DSL语句进行类比。

一、Mysql数据库与Elasticsearch的类比

| 关系型数据库（比如Mysql） | 非关系型数据库（Elasticsearch） |
| ------------------------- | ------------------------------- |
| 数据库 Database | 索引 Index |
| 表 Table | 类型 Type |
| 数据行 Row | 文档 Document |
| 数据列 Column | 字段 Field |
| 约束 Schema | 映射 Mapping |

二、Mysql查询语句与DSL查询类比

Mysql查询语句与Elasticsearch的DSL查询类比，主要通过mysql库中的search_lexicon表和es中的search_lexicon_v1索引进行比较。

2.1 search_lexicon 表结构

sql CREATE TABLE `search_lexicon` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id', `keyword` varchar(50) NOT NULL DEFAULT '' COMMENT '关键词', `keyword_crc32` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '关键词校验', `search_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '类型', `consumer_id` varchar(50) NOT NULL DEFAULT '' COMMENT '消费者ID', `num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '文档数', `views` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '搜索次数', `state` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态 0 关闭 1 开启', `is_del` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 0 正常 1 删除', `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据创建时间', `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据最后更新时间', PRIMARY KEY (`id`), KEY `idx_search_lexicon_views` (`views`), KEY `idx_search_lexicon_updatetime` (`updatetime`) USING BTREE, KEY `idx_search_lexicon_keyword_type` (`keyword_crc32`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='搜索词库';

2.2 search_lexicon_v1 索引结构

json { "search_lexicon_v1" : { "mappings" : { "_doc" : { "properties" : { "@timestamp" : { "type" : "date" }, "@version" : { "type" : "long" }, "consumer_id" : { "type" : "keyword" }, "createtime" : { "type" : "date", "format" : "yyyy-MM-dd HH:mm:ss||strict_date_optional_time||epoch_millis" }, "id" : { "type" : "integer" }, "is_del" : { "type" : "integer" }, "keyword" : { "type" : "text", "fields" : { "standard" : { "type" : "text", "analyzer" : "by_standard_no_synonym" } }, "analyzer" : "by_max_word_pinyin_no_synonym" }, "num" : { "type" : "long" }, "search_type" : { "type" : "integer" }, "state" : { "type" : "integer" }, "updatetime" : { "type" : "date", "format" : "yyyy-MM-dd HH:mm:ss||strict_date_optional_time||epoch_millis" }, "views" : { "type" : "long" } } } } } }

2.3 查询语句对照

注意：dsl查询，每次默认展示10（size默认为10）条

以下的查询条件，是为了写查询而构造的，无任何实质性的意义，仅供mysql查询与dsl查询对比用

布尔查询支持的子查询类型共有四种，分别是：must，should，must_not和filter：

| 查询字句 | 说明 | 类型 |
| -------- | ------------------------------------------------------------ | ---- |
| must | 文档必须符合must中所有的条件，会影响相关性得分 | 数组 |
| should | 文档应该匹配should子句查询的一个或多个 | 数组 |
| must_not | 文档必须不符合must_not 中的所有条件 | 数组 |
| filter | 过滤器，文档必须匹配该过滤条件，跟must子句的唯一区别是，filter不影响查询的score ,会缓存 | 字典 |

A、查询所有数据

mysql

sql SELECT * FROM search_lexicon 

dsl

shell GET search_lexicon/_search { } 或 GET search_lexicon/_search { "query": { "match_all": {} } } 

B、查询一个条件且条件只有一个值（consumer_id=demo）的数据

mysql

sql SELECT * FROM search_lexicon WHERE consumer_id='demo' 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "filter": { "term": { "consumer_id": "demo" } } } } } 或 GET search_lexicon/_search { "query": { "bool": { "filter": [ { "term": { "consumer_id": "demo" } } ] } } } 两者的区别在于前一个filter是一个对象，filter中只能放一个条件，后者filter是一个数组，里面可以放多个对象（多个查询条件）,后续都将按照第二种方式查询 

C、查询一个条件且条件有多个值（consumer_id的值为demo，demo2）的数据

mysql

sql SELECT * FROM search_lexicon WHERE consumer_id in('demo','demo2') 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "filter": [ { "terms": { "consumer_id": [ "demo", "demo2" ] } } ] } } } 

D、查询consumer_id=demo 且 state=1的数据

mysql

sql SELECT * FROM search_lexicon WHERE consumer_id ='demo' and state=1 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "filter": [ { "term": { "consumer_id": "demo" } }, { "term": { "state": 1 } } ] } } } 

E、查询consumer_id=demo , state=1 且 is_del<>1的数据

mysql

sql SELECT * FROM search_lexicon WHERE consumer_id ='demo' and state=1 and is_del <>1 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "filter": [ { "term": { "consumer_id": "demo" } }, { "term": { "state": 1 } } ], "must_not": [ { "term": { "is_del": { "value": 1 } } } ] } } } 

F、查询Sconsumer_id ='demo' or (state=1 and is_del =0)的数据

mysql

sql SELECT * FROM search_lexicon WHERE consumer_id ='demo' or (state=1 and is_del =0) 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "should": [ { "term": { "consumer_id": { "value": "demo" } } }, { "bool": { "filter": [ { "term": { "state": 1 } }, { "term": { "is_del": 0 } } ] } } ] } } } 

G、在F的基础上，查询指定字段

mysql

sql SELECT id,keyword,consumer_id,num,views,state,is_del FROM search_lexicon WHERE consumer_id ='demo' or (state=1 and is_del =0) 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "should": [ { "term": { "consumer_id": { "value": "demo" } } }, { "bool": { "filter": [ { "term": { "state": 1 } }, { "term": { "is_del": 0 } } ] } } ] } }, "_source": { "includes": [ "id", "keyword", "num", "is_del", "state", "consumer_id", "views" ] } } 

H、在G的基础上，增加排序

mysql

sql SELECT id,keyword,consumer_id,num,views,state,is_del FROM search_lexicon WHERE consumer_id ='demo' or (state=1 and is_del =0) ORDER BY state DESC,id DESC 

dsl

shell GET search_lexicon/_search { "query": { "bool": { "should": [ { "term": { "consumer_id": { "value": "demo" } } }, { "bool": { "filter": [ { "term": { "state": 1 } }, { "term": { "is_del": 0 } } ] } } ] } }, "_source": { "includes": [ "id", "keyword", "num", "is_del", "state", "consumer_id", "views" ] }, "sort": [ { "state": { "order": "desc" } }, { "id": { "order": "desc" } } ] } 

I、在H的基础上，添加分页

mysql

sql SELECT id,keyword,consumer_id,num,views,state,is_del FROM search_lexicon WHERE consumer_id ='demo' or (state=1 and is_del =0) ORDER BY state DESC,id DESC LIMIT 0,20 

dsl

```shell
GET search_lexicon/_search
{
"query": {
"bool": {
"should": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
},
{
"bool": {
"filter": [
{
"term": {
"state": 1
}
},
{
"term": {
"is_del": 0
}
}
]
}
}
]
}
},
"_source": {
"includes": [
"id",
"keyword",
"num",
"is_del",
"state",
"consumer_id",
"views"
]
},
"sort": [
{
"state": {
"order": "desc"
}
},
{
"id": {
"order": "desc"
}
}
],
"from": 0,
"size": 20
}

from 是一个偏移量，size为每页显示条数

 **J、去重查询** mysql  sql
SELECT DISTINCT state FROM search_lexicon WHERE consumer_id = 'demo'
 dsl  shell

通过折叠去重查询

GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
]
}
},
"collapse": {
"field": "state"
}
}
 **K、分组查询** mysql  sql
SELECT FROM search_lexicon WHERE consumer_id = 'demo' GROUP BY state
 dsl  shell
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
]
}
},
"size": 0,
"aggs": {
"aaa": {
"terms": {
"field": "state",
"size": 10
}
}
}
}

 **L、模糊匹配** mysql  sql
SELECT FROM search_lexicon WHERE consumer_id="demo" and keyword LIKE '%渴望%'
 dsl  shell
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
],
"must": [
{
"match": {
"keyword": "渴望"
}
}
]
}
}
}
```

三、总结

Mysql查询与DSL查询对照，用心体会二者之间，上下文之间，各查询条件的差异与相似，快速掌握DSL的语法结构，You can do it!

声明：

本文版权归作者所有，未经许可不得擅自转载或引用。
原文地址：https://elasticsearch.cn/article/13760