![新手](https://elasticsearch.cn/static/common/topic-mid-img.png)
新手
类比mysql查询,适合新手学习Elasticsearch的DSL查询语句
Elasticsearch • 森 发表了文章 • 0 个评论 • 7473 次浏览 • 2020-04-29 10:44
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 表结构
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 索引结构
{
"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
SELECT * FROM search_lexicon
dsl
GET search_lexicon/_search
{
}
或
GET search_lexicon/_search
{
"query": {
"match_all": {}
}
}
B、 查询一个条件且条件只有一个值(consumer_id=demo)的数据
mysql
SELECT * FROM search_lexicon WHERE consumer_id='demo'
dsl
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
SELECT * FROM search_lexicon WHERE consumer_id in('demo','demo2')
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"terms": {
"consumer_id": [
"demo",
"demo2"
]
}
}
]
}
}
}
D、 查询consumer_id=demo 且 state=1的数据
mysql
SELECT * FROM search_lexicon WHERE consumer_id ='demo' and state=1
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": "demo"
}
},
{
"term": {
"state": 1
}
}
]
}
}
}
E、 查询consumer_id=demo , state=1 且 is_del<>1的数据
mysql
SELECT * FROM search_lexicon WHERE consumer_id ='demo' and state=1 and is_del <>1
dsl
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
SELECT * FROM search_lexicon WHERE consumer_id ='demo' or (state=1 and is_del =0)
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"should": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
},
{
"bool": {
"filter": [
{
"term": {
"state": 1
}
},
{
"term": {
"is_del": 0
}
}
]
}
}
]
}
}
}
G、在F的基础上,查询指定字段
mysql
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
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
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
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
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
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
SELECT DISTINCT state FROM search_lexicon WHERE consumer_id = 'demo'
dsl
# 通过折叠去重查询
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
]
}
},
"collapse": {
"field": "state"
}
}
K、分组查询
mysql
SELECT * FROM search_lexicon WHERE consumer_id = 'demo' GROUP BY state
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
]
}
},
"size": 0,
"aggs": {
"aaa": {
"terms": {
"field": "state",
"size": 10
}
}
}
}
L、模糊匹配
mysql
SELECT * FROM search_lexicon WHERE consumer_id="demo" and keyword LIKE '%渴望%'
dsl
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
类比mysql查询,适合新手学习Elasticsearch的DSL查询语句
Elasticsearch • 森 发表了文章 • 0 个评论 • 7473 次浏览 • 2020-04-29 10:44
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 表结构
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 索引结构
{
"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
SELECT * FROM search_lexicon
dsl
GET search_lexicon/_search
{
}
或
GET search_lexicon/_search
{
"query": {
"match_all": {}
}
}
B、 查询一个条件且条件只有一个值(consumer_id=demo)的数据
mysql
SELECT * FROM search_lexicon WHERE consumer_id='demo'
dsl
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
SELECT * FROM search_lexicon WHERE consumer_id in('demo','demo2')
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"terms": {
"consumer_id": [
"demo",
"demo2"
]
}
}
]
}
}
}
D、 查询consumer_id=demo 且 state=1的数据
mysql
SELECT * FROM search_lexicon WHERE consumer_id ='demo' and state=1
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": "demo"
}
},
{
"term": {
"state": 1
}
}
]
}
}
}
E、 查询consumer_id=demo , state=1 且 is_del<>1的数据
mysql
SELECT * FROM search_lexicon WHERE consumer_id ='demo' and state=1 and is_del <>1
dsl
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
SELECT * FROM search_lexicon WHERE consumer_id ='demo' or (state=1 and is_del =0)
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"should": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
},
{
"bool": {
"filter": [
{
"term": {
"state": 1
}
},
{
"term": {
"is_del": 0
}
}
]
}
}
]
}
}
}
G、在F的基础上,查询指定字段
mysql
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
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
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
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
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
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
SELECT DISTINCT state FROM search_lexicon WHERE consumer_id = 'demo'
dsl
# 通过折叠去重查询
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
]
}
},
"collapse": {
"field": "state"
}
}
K、分组查询
mysql
SELECT * FROM search_lexicon WHERE consumer_id = 'demo' GROUP BY state
dsl
GET search_lexicon/_search
{
"query": {
"bool": {
"filter": [
{
"term": {
"consumer_id": {
"value": "demo"
}
}
}
]
}
},
"size": 0,
"aggs": {
"aaa": {
"terms": {
"field": "state",
"size": 10
}
}
}
}
L、模糊匹配
mysql
SELECT * FROM search_lexicon WHERE consumer_id="demo" and keyword LIKE '%渴望%'
dsl
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