Elasticsearch查询/结果优化
具有弹性搜索查询，需要找到其优化，因为它需要更多的CPU时间和内存，我的第一个想法是父母/子关系中所需的一些更改，我如何优化此Elasticsearch查询或更改映射以获得相同的结果。
GET trending/_search
{
"track_total_hits":true,
"size":-1,
"sort":[
{
"id":{
"order":"desc"
}
}
],
"query":{
"bool":{
"must":[
{
"term":{
"type":"post"
}
},
{
"terms":{
"post_type_id":[
1,
2,
5,
7
]
}
},
{
"has_parent":{
"parent_type":"user",
"query":{
"bool":{
"should":[
{
"bool":{
"must":{
"has_child":{
"type":"followers",
"query":{
"bool":{
"must":[
{
"term":{
"status":"A"
}
},
{
"term":{
"user_id":87
}
}
]
}
}
}
}
}
},
{
"bool":{
"must":[
{
"terms":{
"id":[
5,
14,
19,
30,
31,
60,
64,
72,
74,
75,
77,
80,
81,
85,
92,
101,
112,
138,
139,
189,
196,
201,
205,
210,
211,
224,
238,
239,
274,
275,
283,
336,
421,
434,
585,
633,
649,
687,
788,
836,
1442,
1479,
1607,
1699,
1775,
1779,
1784,
1823,
1863,
1868,
1899,
2131,
2170,
2329,
2376,
2389,
2401,
2405,
2508,
2568,
2802,
2892,
3074,
3082,
3196,
3312,
3315,
3326,
3391,
3520,
3765,
3853,
3983,
4037,
4436,
4533,
4936,
5018,
5116,
5131,
5353,
5653,
5673,
5674,
5699,
5713,
5789,
5837,
5889,
6391,
6586,
6641,
6819,
6872,
6942,
7302,
7427,
7765,
7828,
8204,
8205,
8402,
8608,
8625,
8655,
8695,
9026,
9116,
9365,
9430,
9600,
14080,
14594,
16543,
17115,
17118,
17825,
17914,
18323,
18368,
18371,
18636,
19071,
19415,
19418,
19632,
19712,
19727,
19978,
20000,
20433,
21132,
23015,
24514,
25266,
25601,
27300,
28493,
28658,
29433,
29441,
29460,
29604,
30104,
30176,
30525,
30965,
31072,
31130,
31497,
31915,
32004,
32184,
32294,
32337,
34053,
36019,
36246,
36986
]
}
}
]
}
}
]
}
}
}
},
{
"has_child":{
"type":"post_box",
"query":{
"bool":{
"must":[
{
"terms":{
"status":[
"A",
"F"
]
}
}
]
}
}
}
},
{
"range":{
"created_at":{
"lte":"2022-06-28T05:18:17Z"
}
}
}
]
}
}
}
这是查询的结果，
{
"took" : 2,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-1",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 1,
"name" : "London Restaurants",
"searchable_title" : "London Restaurants",
"user_id" : 3,
"status" : "A",
"created_at" : "2017-01-17T08:48:05Z",
"type" : {
"parent" : "u-3",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-2",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 2,
"name" : "Exploring England",
"searchable_title" : "Exploring England",
"user_id" : 3,
"status" : "A",
"created_at" : "2017-01-17T08:53:18Z",
"type" : {
"parent" : "u-3",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-5",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 5,
"name" : """Friends
having an elastic search query, need to find its optimization because it takes more CPU time and memory, my first thought is to some changes required in parent/child relationship, How can I optimize this elasticsearch query or change the mapping to get the same result.
GET trending/_search
{
"track_total_hits":true,
"size":-1,
"sort":[
{
"id":{
"order":"desc"
}
}
],
"query":{
"bool":{
"must":[
{
"term":{
"type":"post"
}
},
{
"terms":{
"post_type_id":[
1,
2,
5,
7
]
}
},
{
"has_parent":{
"parent_type":"user",
"query":{
"bool":{
"should":[
{
"bool":{
"must":{
"has_child":{
"type":"followers",
"query":{
"bool":{
"must":[
{
"term":{
"status":"A"
}
},
{
"term":{
"user_id":87
}
}
]
}
}
}
}
}
},
{
"bool":{
"must":[
{
"terms":{
"id":[
5,
14,
19,
30,
31,
60,
64,
72,
74,
75,
77,
80,
81,
85,
92,
101,
112,
138,
139,
189,
196,
201,
205,
210,
211,
224,
238,
239,
274,
275,
283,
336,
421,
434,
585,
633,
649,
687,
788,
836,
1442,
1479,
1607,
1699,
1775,
1779,
1784,
1823,
1863,
1868,
1899,
2131,
2170,
2329,
2376,
2389,
2401,
2405,
2508,
2568,
2802,
2892,
3074,
3082,
3196,
3312,
3315,
3326,
3391,
3520,
3765,
3853,
3983,
4037,
4436,
4533,
4936,
5018,
5116,
5131,
5353,
5653,
5673,
5674,
5699,
5713,
5789,
5837,
5889,
6391,
6586,
6641,
6819,
6872,
6942,
7302,
7427,
7765,
7828,
8204,
8205,
8402,
8608,
8625,
8655,
8695,
9026,
9116,
9365,
9430,
9600,
14080,
14594,
16543,
17115,
17118,
17825,
17914,
18323,
18368,
18371,
18636,
19071,
19415,
19418,
19632,
19712,
19727,
19978,
20000,
20433,
21132,
23015,
24514,
25266,
25601,
27300,
28493,
28658,
29433,
29441,
29460,
29604,
30104,
30176,
30525,
30965,
31072,
31130,
31497,
31915,
32004,
32184,
32294,
32337,
34053,
36019,
36246,
36986
]
}
}
]
}
}
]
}
}
}
},
{
"has_child":{
"type":"post_box",
"query":{
"bool":{
"must":[
{
"terms":{
"status":[
"A",
"F"
]
}
}
]
}
}
}
},
{
"range":{
"created_at":{
"lte":"2022-06-28T05:18:17Z"
}
}
}
]
}
}
}
and here is the result of the query,
{
"took" : 2,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-1",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 1,
"name" : "London Restaurants",
"searchable_title" : "London Restaurants",
"user_id" : 3,
"status" : "A",
"created_at" : "2017-01-17T08:48:05Z",
"type" : {
"parent" : "u-3",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-2",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 2,
"name" : "Exploring England",
"searchable_title" : "Exploring England",
"user_id" : 3,
"status" : "A",
"created_at" : "2017-01-17T08:53:18Z",
"type" : {
"parent" : "u-3",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-5",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 5,
"name" : """Friends????""",
"searchable_title" : """Friends????""",
"user_id" : 5,
"status" : "A",
"created_at" : "2017-10-02T04:56:57Z",
"type" : {
"parent" : "u-5",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-10",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 10,
"name" : "water",
"searchable_title" : "water",
"user_id" : 7,
"status" : "A",
"created_at" : "2017-01-20T06:11:21Z",
"type" : {
"parent" : "u-7",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-11",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 11,
"name" : "leggings ",
"searchable_title" : "leggings ",
"user_id" : 7,
"status" : "A",
"created_at" : "2017-01-20T06:12:55Z",
"type" : {
"parent" : "u-7",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-14",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 14,
"name" : "new tech",
"searchable_title" : "new tech",
"user_id" : 8,
"status" : "A",
"created_at" : "2017-01-23T04:04:05Z",
"type" : {
"parent" : "u-8",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-16",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 16,
"name" : "Adventure",
"searchable_title" : "Adventure",
"user_id" : 16,
"status" : "A",
"created_at" : "2017-01-26T11:18:56Z",
"type" : {
"parent" : "u-16",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-17",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 17,
"name" : "nights out",
"searchable_title" : "nights out",
"user_id" : 8,
"status" : "A",
"created_at" : "2017-01-27T05:03:22Z",
"type" : {
"parent" : "u-8",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-18",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 18,
"name" : "boxxx",
"searchable_title" : "boxxx",
"user_id" : 18,
"status" : "F",
"created_at" : "2017-01-27T05:03:35Z",
"type" : {
"parent" : "u-18",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-19",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 19,
"name" : "animals ",
"searchable_title" : "animals ",
"user_id" : 18,
"status" : "F",
"created_at" : "2017-01-27T05:07:27Z",
"type" : {
"parent" : "u-18",
"name" : "box"
}
}
}
]
}
}```
And here is the mappings of the index,
{
"trending" : {
"mappings" : {
"properties" : {
"box_id" : {
"type" : "integer"
},
"categories" : {
"type" : "text",
"fields" : {
"raw" : {
"type" : "keyword"
}
},
"fielddata" : true
},
"category_id" : {
"type" : "long"
},
"chat_channel" : {
"type" : "keyword"
},
"created_at" : {
"type" : "date"
},
"delete_one" : {
"type" : "long"
},
"delete_two" : {
"type" : "long"
},
"device_id" : {
"type" : "keyword"
},
"dob" : {
"type" : "date"
},
"email" : {
"type" : "keyword"
},
"friend_box" : {
"type" : "integer"
},
"friend_posts" : {
"type" : "integer"
},
"full_name" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
},
"gender" : {
"type" : "keyword"
},
"id" : {
"type" : "long"
},
"is_live" : {
"type" : "boolean"
},
"is_verified" : {
"type" : "boolean"
},
"item_type_number" : {
"type" : "integer"
},
"message_content" : {
"type" : "keyword"
},
"message_object" : {
"type" : "text"
},
"message_privacy" : {
"type" : "long"
},
"name" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
},
"object_id" : {
"type" : "long"
},
"phone" : {
"type" : "text",
"fields" : {
"raw" : {
"type" : "keyword"
}
},
"term_vector" : "yes"
},
"phone_post_fix" : {
"type" : "long"
},
"picture" : {
"type" : "text"
},
"post_id" : {
"type" : "integer"
},
"post_media" : {
"properties" : {
"bg_color" : {
"type" : "text"
},
"file" : {
"type" : "text"
},
"file_type_number" : {
"type" : "long"
},
"medium_file_height" : {
"type" : "long"
},
"medium_file_width" : {
"type" : "long"
}
}
},
"post_type_id" : {
"type" : "long"
},
"private_box" : {
"type" : "integer"
},
"private_posts" : {
"type" : "integer"
},
"public_box" : {
"type" : "integer"
},
"public_posts" : {
"type" : "integer"
},
"searchable_title" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
},
"source_key" : {
"type" : "keyword"
},
"status" : {
"type" : "keyword"
},
"type" : {
"type" : "join",
"eager_global_ordinals" : true,
"relations" : {
"post" : [
"discover_views",
"post_box"
],
"box" : "box_post",
"user" : [
"followers",
"post",
"blocked",
"followings",
"messages",
"box",
"block"
]
}
},
"uid" : {
"type" : "keyword"
},
"user_id" : {
"type" : "long"
},
"username" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
}
}
}
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将数据不合同。为识别连接记录的父母和孩子添加钥匙。将所有内容都放入一个索引中。您的搜索将变得更快，并且不会使用太多CPU。
停止完全使用父子映射。浪费时间和记忆。
Hth。
Denormalize the data. Add keys to the parents and children identifying the connecting record. Put everything into one index. Your search will become way faster and won't use so much CPU.
Stop using parent child mappings completely.Its a waste of time and memory.
HTH.
这是有点优化的查询，我完全删除了has_parent，并让孩子在查询上方加入以获取朋友，从数据库中获取它并以简单的术语查询提供ID，
Here is the somewhat optimized query , I totally remove the has_parent and has child join at above the query to get the friends, getting it from the db and providing the ids in simple terms query,