前记#

由于公司内部的研发团队越来越多的接触到复杂的查询需求，也越来越多的依赖大数据部门提供的es搜索引擎提供查询服务

特此整理一些es常用的查询语句用于培训，目的在于帮助其他不熟悉es的同学快速熟悉es的dsl语句的编写

数据初始化和准备工作#

es和kibana安装#

可以从 https://github.com/leriou/docker-env/tree/master/elasticsearch

直接使用docker编排文件构建基于docker的es本地服务

docker-compose up -d 启动服务,启动成功访问kibana命令控制台

es: elasticsearch 实例，主要存储和搜索引擎 kibana： elasticsearch的一个web层GUI客户端，可以方便的查询es里面的数据，这些年用了一大堆各种各样的第三方GUI，用来用去还是kibana最方便

准备测试数据#

先准备一部分数据用于演示

创建测试用的索引put test_idx

创建测试文档

1
put test_idx/_doc/1
2
{
3
  "name":"111",
4
  "tags":["a","c","d"]
5
}

以下是用于示范的数据文档

1
[
2
      {
3
        "_index": "test_idx",
4
        "_type": "_doc",
5
        "_id": "8",
6
        "_score": 1,
7
        "_source": {
8
          "name": "a888",
9
          "tags": [
10
            "ab"
11
          ],
12
          "age": 7,
13
          "content": "明天"
14
        }
15
      },
16
      {
17
        "_index": "test_idx",
18
        "_type": "_doc",
19
        "_id": "2",
20
        "_score": 1,
21
        "_source": {
22
          "name": "222",
23
          "tags": [
24
            "a"
25
          ],
26
          "age": 18
27
        }
28
      },
29
      {
30
        "_index": "test_idx",
31
        "_type": "_doc",
32
        "_id": "4",
33
        "_score": 1,
34
        "_source": {
35
          "name": "444",
36
          "tags": [
37
            "d",
38
            "i"
39
          ],
40
          "age": 0,
41
          "content": "明天北京的天气很好，是个大晴天 "
42
        }
43
      },
44
      {
45
        "_index": "test_idx",
46
        "_type": "_doc",
47
        "_id": "1",
48
        "_score": 1,
49
        "_source": {
50
          "name": "111",
51
          "tags": [
52
            "a",
53
            "c",
54
            "d"
55
          ]
56
        }
57
      },
58
      {
59
        "_index": "test_idx",
60
        "_type": "_doc",
61
        "_id": "3",
62
        "_score": 1,
63
        "_source": {
64
          "name": "333",
65
          "tags": [
66
            "e",
67
            "f"
68
          ],
69
          "age": 18,
70
          "content": "明天上海的天气不好，有小雨"
71
        }
72
      }
73
    ]

简单查询示范#

n对n查询#

1
# 1对1匹配查询：适用于 查询条件为1个值，被查询对象字段也为1个值的情况
2
# where name = 111
3
GET test_idx/_search
4
{
5
  "query": {
6
    "term": {
7
      "name": {
8
        "value": 111
9
      }
10
    }
11
  }
12
}

1
# 1对多查询：适用于 查询条件为一个，查询值为[]的情况
2
# where find_in_set(tags, "a")
3
GET test_idx/_search
4
{
5
  "query": {
6
    "terms": {
7
      "tags.keyword": ["a"]
8
    }
9
  }
10
}

1
# 多对1查询：适用于查询条件为[]，被查询字段为1个值的情况
2
# where age in (0,18)
3
GET test_idx/_search
4
{
5
  "query": {
6
    "terms": {
7
      "age": [
8
        0,
9
        18
10
      ]
11
    }
12
  }
13
}

1
# 多对多查询：适用于查询条件为 [], 查询值也为[] 的情况
2
GET test_idx/_search
3
{
4
  "query": {
5
    "terms": {
6
      "tags.keyword": ["a","e"]
7
    }
8
  }
9
}

万能匹配-match#

match查询可以用于多种查询用途，常见的全文检索，关键词匹配等都使用该方法，是es中最常用的查询

1
# where tags.contains("a")
2
GET test_idx/_search
3
{
4
  "query": {
5
    "match": {
6
      "tags": "a"
7
    }
8
  }
9
}
10
# where age = 18
11
GET test_idx/_search
12
{
13
  "query": {
14
    "match": {
15
      "age": 18
16
    }
17
  }
18
}
19
# where content like "%天气%"
20
GET test_idx/_search
21
{
22
  "query": {
23
    "match": {
24
      "content": "天气"
25
    }
26
  }
27
}

其他常用查询（range, exist, a and b等）#

1
# 范围查询  where a between 10 and 20
2
GET test_idx/_search
3
{
4
  "query": {
5
    "range": {
6
      "age": {
7
        "gte": 10,
8
        "lte": 20
9
      }
10
    }
11
  }
12
}
13
# 字段是否存在 where content not null
14
GET test_idx/_search
15
{
16
  "query": {
17
    "exists":{
18
      "field":"content"
19
    }
20
  }
21
}
22
# 多条件查询 where (age between 10 and 20) and content like "上海"
23
GET test_idx/_search
24
{
25
  "query": {
26
    "bool": {
27
      "must": [
28
        {
29
          "range": {
30
            "age": {
31
              "gte": 10,
32
              "lte": 20
33
            }
34
          }
35
        },
36
        {
37
          "match": {
38
            "content": "上海"
39
          }
40
        }
41
      ]
42
    }
43
  }
44
}

查询原理解析#

es文档字段的存储逻辑#

es中的字段看起来有多种数据结构，实际抽象出来只有一种数据结构就是 k-v

1
类似
2
`tags:["a","b","c"]`
3
的数组数据结构在es中实际上是
4
{
5
  tags.a : a
6
  tags.b : b
7
  tags.c : c
8
}
9
这样的分解成多个字段进行存储的

`match`为什么可以做到万能查询#

是因为match在查询时候会对查询条件进行分词

1
# 不分词的查询查不到 tags:["a","b"]的值， 只能查询到 tags:["ab"]的值
2
GET test_idx/_search
3
{
4
  "query": {
5
    "match": {
6
      "tags.keyword": "ab"
7
    }
8
  }
9
}

ps： es自带了一部分内容格式转换规则，类似 type = “VIDEO” 这种字段如果要使用term查询的话需要用 term:{type.keyword:“VIDEO”}, 因为大写的字段值会被默认分词，如果是type =“video”这种小写就可以用 term:{type:“video”}来进行匹配

分值相关查询#

有时候我们希望按照某种特殊的顺序对es的文档进行排序，这个时候往往需要自定义文档查询得分

filter过滤#

1
# filter使用布隆过滤器进行过滤所以没有分值，性能较好
2
GET test_idx/_search
3
{
4
  "query": {
5
    "bool": {
6
      "filter": {
7
        "range": {
8
          "age": {
9
            "gte": 10
10
          }
11
        }
12
      }
13
    }
14
  }
15
}

constant_score和boost提权#

constant_score用于指定查询命中的单位得分值，每个查询价值一个分值单位

boost 用于提升权重

1
# 该查询命中则价值 1.2分 且忽略tf/idf得分
2
GET test_idx/_search
3
{
4
    "query": {
5
        "constant_score" : {
6
          "filter": {
7
            "terms": {
8
              "tags": [
9
                "a",
10
                "d"
11
              ]
12
            }
13
          },
14
          "boost": 1.2
15
        }
16
    }
17
}
18

19
# boost进行提权
20
GET test_idx/_search
21
{
22
  "query": {
23
    "terms": {
24
      "tags": ["a"],
25
      "boost":3
26
    }
27
  }
28
}

function_score#

function_score自定义分值，可以根据文档内容进行得分定制

1
# 该查询根据文档的age字段 * 5 作为最终分值
2
GET test_idx/_search
3
{
4
  "query": {
5
    "function_score": {
6
      "query": {
7
        "match_all": {
8
        }
9
      },
10
      "script_score" : {
11
        "script" : {
12
          "source": "5*doc['age'].value"
13
        }
14
      }
15
    }
16
  }
17
}

查询示例：根据用户关注的标签匹配数量计算得分#

1
# 假设用户关注了["a","b"] 标签，根据用户的关注标签匹配数量进行分数计算，a标签价值1.3分，b标签1.1
2
GET test_idx/_search
3
{
4
  "query": {
5
    "bool": {
6
      "should": [
7
        {
8
         "constant_score": {
9
           "filter": {
10
             "terms": {
11
               "tags": [
12
                 "a"
13
               ]
14
             }
15
           },
16
           "boost": 1.3
17
         }
18
        },
19
        {
20
          "constant_score": {
21
            "filter": {
22
              "terms": {
23
                "tags": [
24
                  "d"
25
                ]
26
              }
27
            },
28
            "boost": 1.1
29
          }
30
        }
31
      ],
32
      "minimum_should_match": 1
33
    }
34
  }
35
}

聚合查询#

terms求count值#

1
# 等价于 group by tags
2
GET test_idx/_search
3
{
4
  "size": 0,
5
  "aggs": {
6
    "t": {
7
      "terms": {
8
        "field": "tags.keyword",
9
        "size": 10
10
      }
11
    }
12
  }
13
}

avg求平均值#

1
GET test_idx/_search
2
{
3
  "size": 0,
4
  "aggs": {
5
    "t": {
6
      "avg": {
7
        "field": "age"
8
      }
9
    }
10
  }
11
}

其他查询和dsl#

原子更新文档#

1
# 由于es本身不支持源字形的更新文档，我们需要借助内置脚本的帮助来操作
2
POST test_idx/_update/1
3
{
4
 "script" : {
5
    "source": "ctx._source.age += params.count",
6
      "lang": "painless",
7
      "params" : {
8
          "count" : 4
9
      }
10
    }
11
}

explain#

explain 用于查看查询的执行过程和各部分的具体得分，一般用于排查问题

1
GET test_idx/_search
2
{
3
  "explain": true,
4
  "query": {
5
    "bool": {
6
      "must": [
7
        {
8
          "range": {
9
            "age": {
10
              "gte": 10,
11
              "lte": 20
12
            }
13
          }
14
        }
15
      ],
16
      "should": [
17
        {
18
          "match": {
19
            "content": "上海"
20
          }
21
        }
22
      ],
23
      "minimum_should_match": 1
24
    }
25
  }
26
}
27

28
#explain 结果示例
29
{
30
        "_explanation": {
31
          "value": 1.5753641,
32
          "description": "sum of:",
33
          "details": [
34
            {
35
              "value": 1,
36
              "description": "age:[10 TO 20]",
37
              "details": []
38
            },
39
            {
40
              "value": 0.5753642,
41
              "description": "sum of:",
42
              "details": [
43
                {
44
                  "value": 0.2876821,
45
                  "description": "weight(content:上 in 0) [PerFieldSimilarity], result of:",
46
                  "details": [
47
                    {
48
                      "value": 0.2876821,
49
                      "description": "score(doc=0,freq=1.0 = termFreq=1.0\n), product of:",
50
                      "details": [
51
                        {
52
                          "value": 0.2876821,
53
                          "description": "idf, computed as log(1 + (docCount - docFreq + 0.5) / (docFreq + 0.5)) from:",
54
                          "details": [
55
                            {
56
                              "value": 1,
57
                              "description": "docFreq",
58
                              "details": []
59
                            },
60
                            {
61
                              "value": 1,
62
                              "description": "docCount",
63
                              "details": []
64
                            }
65
                          ]
66
                        },
67
                        {
68
                          "value": 1,
69
                          "description": "tfNorm, computed as (freq * (k1 + 1)) / (freq + k1 * (1 - b + b * fieldLength / avgFieldLength)) from:",
70
                          "details": [
71
                            {
72
                              "value": 1,
73
                              "description": "termFreq=1.0",
74
                              "details": []
75
                            },
76
                            {
77
                              "value": 1.2,
78
                              "description": "parameter k1",
79
                              "details": []
80
                            },
81
                            {
82
                              "value": 0.75,
83
                              "description": "parameter b",
84
                              "details": []
85
                            },
86
                            {
87
                              "value": 12,
88
                              "description": "avgFieldLength",
89
                              "details": []
90
                            },
91
                            {
92
                              "value": 12,
93
                              "description": "fieldLength",
94
                              "details": []
95
                            }
96
                          ]
97
                        }
98
                      ]
99
                    }
100
                  ]
101
                },
102
                {
103
                  "value": 0.2876821,
104
                  "description": "weight(content:海 in 0) [PerFieldSimilarity], result of:",
105
                  "details": [
106
                    {
107
                      "value": 0.2876821,
108
                      "description": "score(doc=0,freq=1.0 = termFreq=1.0\n), product of:",
109
                      "details": [
110
                        {
111
                          "value": 0.2876821,
112
                          "description": "idf, computed as log(1 + (docCount - docFreq + 0.5) / (docFreq + 0.5)) from:",
113
                          "details": [
114
                            {
115
                              "value": 1,
116
                              "description": "docFreq",
117
                              "details": []
118
                            },
119
                            {
120
                              "value": 1,
121
                              "description": "docCount",
122
                              "details": []
123
                            }
124
                          ]
125
                        },
126
                        {
127
                          "value": 1,
128
                          "description": "tfNorm, computed as (freq * (k1 + 1)) / (freq + k1 * (1 - b + b * fieldLength / avgFieldLength)) from:",
129
                          "details": [
130
                            {
131
                              "value": 1,
132
                              "description": "termFreq=1.0",
133
                              "details": []
134
                            },
135
                            {
136
                              "value": 1.2,
137
                              "description": "parameter k1",
138
                              "details": []
139
                            },
140
                            {
141
                              "value": 0.75,
142
                              "description": "parameter b",
143
                              "details": []
144
                            },
145
                            {
146
                              "value": 12,
147
                              "description": "avgFieldLength",
148
                              "details": []
149
                            },
150
                            {
151
                              "value": 12,
152
                              "description": "fieldLength",
153
                              "details": []
154
                            }
155
                          ]
156
                        }
157
                      ]
158
                    }
159
                  ]
160
                }
161
              ]
162
            }
163
          ]
164
        }
165
}

参考资料

https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html

前记#