MySQL查询转ES查询
目录
前言
MySQL查询遇到了性能瓶颈,无奈改为用ES查询,但sql语句转为es查询没那么简单,网上的资料浩繁杂乱,错多对少,终于在官方文档中找到了自己的答案,故记录下来方便日后使用。
ES版本:7.13.2
es自带的sql转换
POST http://127.0.0.1:9200/_xpack/sql/translate
请求内容:
{
"query":"SELECT * FROM goods_stock WHERE store_id=10"
}
这里goods_stock是es的索引,where条件必须是该索引中存储的字段
返回内容:
{
"size": 1000,
"query": {
"term": {
"store_id": {
"value": 10,
"boost": 1.0
}
}
},
"_source": {
"includes": [
"store_id",
...
],
"excludes": []
},
"sort": [
{
"_doc": {
"order": "asc"
}
}
]
}
返回的内容就是sql转换为es查询语句的样子。不过呢,这个转换的api只能转换简单的sql,聚合的操作就转换不了,而且啊,这个转换的查询有挺多没用的冗余信息。
复杂sql
如果需求是复杂的sql该咋办,那么只能自己参考官方文档,自己写es查询了呀😥
查询方法
上面的es查询中,有个term的查询,term是个什么玩意儿?
es查询中,有层层递进的关系,你先告诉它,你要做什么查询,是精确的呢,还是模糊的呢
TermQuery
term表示的就是精确查询
{
"query": {
"term" : {
"name": "spring"
}
}
}
比如这里搜索的就是name的精确值为spring的文档
TermsQuery
terms其实跟term差不多,都是精确搜索
"query": {
"terms": {
"title": ["blog","first"]
}
}
只不过terms支持搜索多个值
BoolQuery
像sql中有一堆and、 or之类的关系,es中当然也有啦。
bool就是为了将多个查询用and / or这样的关系连起来的查询方法
比如这样一个查询:
SELECT * FROM student WHERE name='spring' and age <> 18;
{
"query": {
"bool": {
"must": [
{
"term": {
"name": "spring"
}
}
],
"must_not": [
{
"term": {
"age": 18
}
}
]
}
}
}
bool查询过滤的子句有四种
must 返回的文档必须满足must子句的条件,并且参与计算分值。
must_not 返回的文档必须不满足must_not定义的条件。
filter 先判断是否满足查询条件,如果不满足,会缓存查询过程(记录该文档不满足结果);满足的话,就直接缓存结果。另外不进行分值计算
should 返回的文档可能满足should子句的条件。
MatchQuery
match query 和term query的区别是match query会在搜索前先将关键字分词,再拿分好的词去索引中搜索。
注意:全文检索字段如果用termQuery或wildcardQuery将不能查询成功。因为全文索引字段建立索引时已经被分词工具分成了单个单词了。
例如,我搜索National Basketball,National Basketball会被分成National 和Basketball,分别和文档中分词后的结果进行匹配。
{
"query" : {
"match" : {
"title" : "National Basketball"
}
}
}
// 最后搜索出的结果
National Basketball Association
basketball player
范围查询
比如 age > 10 and age < 18
在es的查询中等于
{
"range":{
"age":{
"gt":10,
"lt":18
}
}
}
range表示范围查询,age是具体的字段
gt 表示大于
gte 表示大于等于
lt 表示小于
lte 表示小于等于
多个范围查询
{
"range":{
"age":{
"gt":10,
"lt":18
},
"score":{
"gte":90,
"lte":95
}
}
}
时间范围查询
比如
birthday >= '2000-05-01 00:00:00' and birthday <= '2020-07-01 23:59:59'
{
"range":{
"birthday":{
"gte": "2021-05-01 00:00:00",
"lte": "2021-07-01 23:59:59",
"format": "yyyy-MM-dd HH:mm:ss"
}
}
}
format 表示标准化日期
yyyy 表示年
MM 表示月
dd 表示日
HH 表示24小时制的小时
mm 表示分
ss 表示秒
模糊查询
类似于mysql的like查询要怎么做呢
SELECT * FROM student WHERE name LIKE "张%";
对于没有做分词的模糊查询,使用wildcardQuery就行了
{
"query":{
"wildcard": {
"name": "张*"
}
}
}
通配符*跟sql的%一样
聚合统计
count
原本我以为统计文档数量是需要调用ES的count API的
http://localhost:9200/index_name/_count
实际上查询文档的时候呢,是可以返回符合条件的文档数量的
"hits": {
"total": {
"value": 5986,
"relation": "eq"
},
...
}
这里的value就是文档总数。不过需要注意一点,ES查询时,默认只返回9999条,这意味着当超过1万条的数据时,这个统计值只有9999。
需要在查询时加一个参数:
"track_total_hits": true // 与query平级
这个参数表示返回所有符合条件的文档
count + distinct
{
"query": {
...
},
"aggs": {
"count": {
"cardinality": {
"field": "goods_id"
}
}
}
}
aggs与query平级,field填写需要做distinct操作的列。
SUM
{
"query": {
...
},
"my_lock_total": {
"sum": {
"field": "lock_num"
}
}
}
复杂的SUM
比如一家店的仓库里有很多商品,现在要统计一下总库存的售价是多少
sql : SELECT SUM(stock_num * tag_price) FROM store_stock;
{
"tag_amt": {
"sum": {
"script": {
"source": "doc['stock_num'].value * doc['tag_price'].value"
}
}
}
}
排序
因为业务需求,需要按照可用库存排序,但可用库存 = 总库存 - 占用库存 计算得到的
用es做这种排序,只能靠script来写了
{
"query": {
...
},
"aggs": {
...
},
"sort": {
"_script": {
"script": {
"source": "return doc['stock_num'].value - doc['lock_num'].value;",
"lang": "painless"
},
"type": "number",
"order": "desc"
}
}
}
查询分页限制
由于导出的数据量非常多,达到几十万,通过es做分页,居然会报错
Result window is too large, from + size must be less than or equal to:[10000] but was [11000].
es的index默认深度分页的文档数是1w条,如果要查询到更深的文档,就得改index的max_result_window参数
后话
有更复杂的sql,等我遇到了再补充吧,一般再复杂一些的,就是用script来实现了