之前有位小伙伴期望在不改代码的情况下优化 elasticsearch 查询,( 简化 ) 查询语句如下:
JSON
{
"query": {
"term": {
"status": 1
}
}
}
以下数据来自测试环境,文档数据量:135999,status 值分布,数据均为新建立 index。
JSON
[
{
"key": 1,
"doc_count": 131371
},
{
"key": 3,
"doc_count": 206
},
{
"key": 2,
"doc_count": 33
}
]
不同的 type Profile 对比如下: long type
JSON
{
"mappings": {
"_doc": {
"properties": {
"status": {
// 类型 long
"type": "long"
},
"templateId": {
"type": "keyword"
}
}
}
}
}

从上图中可以看到 IndexOrDocValuesQuery。了解到对该字段仅有等值配对场景 ( 类似于 where status = ? ),对字段类型从 long 改为 keyword ( 手动创建 mapping 然后 reindex,当然也就意味着语义的改变 ),有如下优化结果:
keyword type
JSON
{
"mappings": {
"_doc": {
"properties": {
"status": {
// 类型 keyword
"type": "keyword"
},
"templateId": {
"type": "keyword"
}
}
}
}
}

SQL
Mapping numeric identifiers
Not all numeric data should be mapped as a numeric field data type. Elasticsearch optimizes numeric fields, such as integer or long, for range queries. However, keyword fields are better for term and other term-level queries.
Identifiers, such as an ISBN or a product ID, are rarely used in range queries. However, they are often retrieved using term-level queries.
Consider mapping a numeric identifier as a keyword if:
You don’t plan to search for the identifier data using range queries.
Fast retrieval is important. term query searches on keyword fields are often faster than term searches on numeric fields.
If you’re unsure which to use, you can use a multi-field to map the data as both a keyword and a numeric data type.
上线压测报告显示,RT减少50%,ES CPU 下降30%

讨论