复杂SQL转ES

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来实现了