ElasticSearch踩坑记录，对应Mysql用法总结

curl localhost:9200 查看版本，当前版本6.7.2

"=" in mysql 相等查询

字段类型是ingeger,keyword

{
    "term": { "field":111 }
}

字段类型是text

{
    "match": { "field":"value" }
}

"like" in mysql 多关键词模糊查询

   {
        "multi_match": {
          "query": "搜索的一句话，或一个关键词",
          "type": "best_fields",
          "fields": [
            "field1^3",
            "field2"
          ],
          "tie_breaker": 0.3
        }
      },

Normally the best_fields type uses the score of the single best matching field, but if tie_breaker is specified, then it calculates the score as follows:

the score from the best matching field plus tie_breaker *_score for all other matching fields

通常，best_fields类型使用单个最佳匹配字段的分数，但是如果指定tie_breaker，则它将按以下方式计算分数：

最佳匹配领域的分数 + tie_breaker *_score（用于所有其他匹配字段）

Es利用ik中文分词插件，可以实现分词查询、近义词查询等功能。

"in" in mysql 固定值范围查询

    {
        "terms": {
          "username": ["张三","李四"]
        }
    }

"distance" in mysql 经纬度距离计算

    "geo_distance": {
        "distance": "20km",
        "distance_type": "plane",
        "location": {
          "lat": "41.024124",
          "lon": "-12.212352"
        }
    }

查询20km内的数据，需要字段location的类型为geo_point

mysql没有现成函数，需要自己实现，单位都是米

    select 6378.138 * 2 * ASIN(
                    SQRT(
                        POW(
                            SIN( ( :lat * PI() / 180 - shop_lat * PI() / 180 ) / 2  ),
                            2
                        ) + COS(:lat * PI() / 180) * COS(shop_lat * PI() / 180) * POW(
                            SIN( (  :lng * PI() / 180 - shop_lng * PI() / 180 ) / 2  ),  2
                        )
                    )
                ) * 1000
               AS distance

Aggregation聚合

Es的aggregation很强大，可以通过嵌套聚合实现很多复杂的查询

    {
        "aggs":{
            "aggName":{
                ...
                "aggs":{
                    "subAggName1":{
                        ...
                    },
                    "subAggName2":{
                        ...
                    }
                }
            }
        }
    }

"group by" 分组查询

terms aggregation写法

    {
        "group1":
              "terms": {
                  "field": "group1",
                  "size": 10,
                  "order": {
                    "score_title": "desc",
                    "score_price": "asc"
                  }
                }
    }

这种写法不支持分页，如要分页，需要用composite aggregation

    {
        "group1":
            "composite":{
              "sources" : [
                { "group1": { "terms" : { "field": "group1" } } }
              ],
              "size": 1, # 相当于limit
              "after": 0 # 相当于offset
              "aggs":{
                    "items": {
                        "top_hits": { # 利用top hits aggregation 获取分组的数据
                        "_source": ["*"], # 显示全部字段
                        "size": "5",
                        "sort": { # 每项items内的排序
                            "_score": {
                              "order": "desc"
                            },
                            "price": {
                              "order": "asc"
                            }
                        }
                    }
                }
              }
        }
    }

"count|sum|max" in mysql 统计查询

可以通过给aggregation添加子aggregation的方式统计各种数据，如统计价格总数

    "score_price": {
        "sum": {
          "field": "price"
        }
    }

统计关键词score

      "score_title": {
        "max": {
          "script": {
            "source": "_score"
          }
        }
      }

统计距离(todo 获取距离值)

      "distance": {
        "geo_distance": {
          "field": "location",
          "origin": "todo",
          "distance_type" : "plane",
          "ranges": [
            {
              "to": 100000
            }
          ]
        },
        "aggs": {
          "distance_value": {
            "max": {
              "script": {
                "source": "_score"
              }
            }
          }
        }
      }

"order by" in mysql 排序

通过子aggregation+bucket_sort实现

    "agggs":{
        ...
        "my_sort": {
            "bucket_sort": { 
              "sort": {
                "score_title": "desc", # 上文定义的aggration
                "score_price": "asc"
              }
        }
      },
    }