愚者求师之过,智者从师之长。

类比mysql查询,适合新手学习Elasticsearch的DSL查询语句

Elasticsearch | 作者 | 发布于2020年04月29日 | | 阅读数:1185

Mysql查询与Es的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": "渴望"
          }
        }
      ]
    }
  }
}

[尊重社区原创,转载请保留或注明出处]
本文地址:http://elasticsearch.cn/article/13760


0 个评论

要回复文章请先登录注册