行动是治愈恐惧的良药,而犹豫、拖延将不断滋养恐惧。

term聚合查询很慢,涉及到50+个term,有建议吗?

Elasticsearch | 作者 printf_uck | 发布于2019年01月07日 | 阅读数:3440

size":100,"query":{"bool":{"should":[{"term":{"keyword_0":{"value":41898100,"boost":1.0}}},{"term":{"keyword_1":{"value":23905348,"boost":1.0}}},{"term":{"keyword_2":{"value":7080452,"boost":1.0}}},{"term":{"keyword_3":{"value":13522340,"boost":1.0}}},{"term":{"keyword_4":{"value":27310603,"boost":1.0}}},{"term":{"keyword_5":{"value":22271412,"boost":1.0}}},{"term":{"keyword_6":{"value":5075675,"boost":1.0}}},{"term":{"keyword_7":{"value":11519200,"boost":1.0}}},{"term":{"keyword_8":{"value":1515786,"boost":1.0}}},{"term":{"keyword_9":{"value":42558316,"boost":1.0}}},{"term":{"keyword_10":{"value":11160252,"boost":1.0}}},{"term":{"keyword_11":{"value":13642325,"boost":1.0}}},{"term":{"keyword_12":{"value":14349138,"boost":1.0}}},{"term":{"keyword_13":{"value":36430560,"boost":1.0}}},{"term":{"keyword_14":{"value":448,"boost":1.0}}},{"term":{"keyword_15":{"value":38481723,"boost":1.0}}},{"term":{"keyword_16":{"value":39843417,"boost":1.0}}},{"term":{"keyword_17":{"value":10156373,"boost":1.0}}},{"term":{"keyword_18":{"value":4647189,"boost":1.0}}},{"term":{"keyword_19":{"value":17676870,"boost":1.0}}},{"term":{"keyword_20":{"value":9273908,"boost":1.0}}},{"term":{"keyword_21":{"value":13225813,"boost":1.0}}},{"term":{"keyword_22":{"value":12681907,"boost":1.0}}},{"term":{"keyword_23":{"value":38659813,"boost":1.0}}},{"term":{"keyword_24":{"value":28756788,"boost":1.0}}},{"term":{"keyword_25":{"value":33069060,"boost":1.0}}},{"term":{"keyword_26":{"value":28323668,"boost":1.0}}},{"term":{"keyword_27":{"value":34271498,"boost":1.0}}},{"term":{"keyword_28":{"value":2623,"boost":1.0}}},{"term":{"keyword_29":{"value":37595988,"boost":1.0}}},{"term":{"keyword_30":{"value":529890,"boost":1.0}}},{"term":{"keyword_31":{"value":41846066,"boost":1.0}}},{"term":{"keyword_32":{"value":28699994,"boost":1.0}}},{"term":{"keyword_33":{"value":20725956,"boost":1.0}}},{"term":{"keyword_34":{"value":38023532,"boost":1.0}}},{"term":{"keyword_35":{"value":9616696,"boost":1.0}}},{"term":{"keyword_36":{"value":515565,"boost":1.0}}},{"term":{"keyword_37":{"value":3557528,"boost":1.0}}},{"term":{"keyword_38":{"value":1116888,"boost":1.0}}},{"term":{"keyword_39":{"value":29892408,"boost":1.0}}},{"term":{"keyword_40":{"value":14362535,"boost":1.0}}},{"term":{"keyword_41":{"value":37978956,"boost":1.0}}},{"term":{"keyword_42":{"value":5560,"boost":1.0}}},{"term":{"keyword_43":{"value":43045992,"boost":1.0}}},{"term":{"keyword_44":{"value":10093004,"boost":1.0}}},{"term":{"keyword_45":{"value":1628,"boost":1.0}}},{"term":{"keyword_46":{"value":36667242,"boost":1.0}}},{"term":{"keyword_47":{"value":27107756,"boost":1.0}}},{"term":{"keyword_48":{"value":37447731,"boost":1.0}}},{"term":{"keyword_49":{"value":39820,"boost":1.0}}},{"term":{"keyword_50":{"value":158922,"boost":1.0}}},{"term":{"keyword_51":{"value":12769166,"boost":1.0}}},{"term":{"keyword_52":{"value":3661254,"boost":1.0}}},{"term":{"keyword_53":{"value":19477442,"boost":1.0}}},{"term":{"keyword_54":{"value":27291173,"boost":1.0}}},{"term":{"keyword_55":{"value":9327288,"boost":1.0}}},{"term":{"keyword_56":{"value":22317172,"boost":1.0}}},{"term":{"keyword_57":{"value":40704092,"boost":1.0}}},{"term":{"keyword_58":{"value":42773306,"boost":1.0}}},{"term":{"keyword_59":{"value":23884951,"boost":1.0}}},{"term":{"keyword_60":{"value":21551804,"boost":1.0}}},{"term":{"keyword_61":{"value":18312844,"boost":1.0}}},{"term":{"keyword_62":{"value":21524035,"boost":1.0}}}],"adjust_pure_negative":true,"minimum_should_match":"30%","boost":1.0}},"_source":{"includes":[],"excludes":["keyword_*"]}}]
 
 
 
如上面的查询,确实很慢,后台五台物理机(15个节点)完全hold不住1000+QPS的该类查询,响应尤其的慢,系统负载飙升到150,请问这种垃圾查询有啥建议优化吗?一直靠扩容不行吧,分片30个,总数据量大小500G左右。
已邀请:

liubin

赞同来自:

你es6.x 数字类型的term 有坑,性能影响巨大,使用keyword,或者数据预处理,区间段,分类,比如0-10000 是一个区间,10000-20000 一个区间,。然后冗余一个keyword字段出来,用term查询

printf_uck - 1024

赞同来自:

          "keyword_12": {
            "type": "long"
这些keyword都是long类型的,刚才忘贴mapping了

rochy - rochy_he

赞同来自:

不推荐对数值类型做 term 操作,详情参考文章:https://elasticsearch.cn/article/446
 
建议使用 keyword 类型,这样使用 termQuery 效率高;
Long 类型适合 Range 类似的查询

laoyang360 - 《一本书讲透Elasticsearch》作者,Elastic认证工程师 [死磕Elasitcsearch]知识星球地址:http://t.cn/RmwM3N9;微信公众号:铭毅天下; 博客:https://elastic.blog.csdn.net

赞同来自:

楼上已中要害,你的宽表设计(60+字段)+多term 30%需要结合业务优化,看是否必要。

要回复问题请先登录注册